The National Bank of Erehwon handles money and maintains bank accounts on behalf of clients.A client is a person who does business with the bank.A client may have any number of accounts, and an account may belong to multiple clients (e.g., spouses, business partners).The client record is used for identification and contact data.For each account, the bank maintains the current balance on hand.Clients are identified by a five digit number starting with 10001.Accounts are identified by a seven digit number starting with 1000001.When an account is first opened, its balance is set to zero. During the course of day-to-day business, Erehwon Bank applies transactions to accounts, including deposits, withdrawals, bill payments, and debit purchases or returns. For each transaction, the date and time, amount, and account are recorded, along with reference data applicable to that type of transaction:Deposits and withdrawals require the branch number to be recorded.Bill payments, and debit purchases or returns require the merchant number.Queries:List of only the Accounts that have multiple Clients associated Provide an alphabetic list by last name of all Clients showing their full name (e.g., Bob Barlow), with the number of Accounts they hold and the total balance of those Accounts Provide a count and total amount of Transactions for each Type description List of each Account showing the first Transaction date, type, and amount. (Hint: This is a correlated sub-query.) Count and Total Amount for each Transaction Type within each Account (Hint: This is an extended GROUP BY.) DATABASE INFO BELOW AND ATTACHEDTX_TYPE CodeDescription DDeposit WWithdrawal BBill Payment PPurchase RReturn BRANCH NbrName 101… make up your own name 102… make up your own name 103… make up your own name 104… make up your own name MERCHANT NbrName 301… make up your own name 302… make up your own name 303… make up your own name 304… make up your own name 10001 has no account, so no transactions10002 account will have deposits and withdrawals only spread over 2 months10003, 10004 will have all types of transactions10005 will have deposits, then bill payments only10006 account will have no transactionsTRANSACTIONTxNbr TxCode AccountNbr Amount Date12345678910111213141516171820212223242526XDDDDDWWDDWDWDPBPRDDDBBBB1000001100000010000011000001100000110000011000001100000110000011000002100000210000031000003100000310000031000002100000210000021000004100000510000051000005100000510000051000005123.45 May 1 2019234.56 May 1 2019345.67 May 1 2019100.00 May 1 2019200.00 May 11 2019300.00 May 21 201950.00 May 29 200975.00 May 29 2009123.45 Jun 15 20191000.00 May 15 2019456.78 May 15 2019456.78 May 15 2019500.00 May 18 2019100.00 May 20 201965.78 May 20 2019100.00 May 21 2019200.00 May 21 201950.00 May 26 20192000.00 Jun 1 20192000.00 Jun 1 20192000.00 Jun 1 20193456.78 Jun 10 2019432.10 Jun 15 2019100.00 Jun 20 201980.00 Jun 25 2019Time12:0012:0012:0010:0011:0012:0010:0011:0013:009:009:059:1014:0013:0014:509:0010:0012:3413:0013:0014:0012:0014:3015:5516:56RefNbr101101111101101101102103101104104104104104304301302301101101102301302303304Balance***100.00300.00600.00550.00475.00598.451000.00543.22456.78-43.2256.78-9.00443.22243.22293.222000.002000.004000.00543.22111.1211.12-68.88NotesERROR no such TxTypeERROR non-existing AccountERROR no such BranchTwo tx same day diff timeTwo tx same day diff timeTwo tx same day diff timeTwo tx same day diff time CLIENTClientNbr FNameLNameStreet10001... your own names goes here10002 ... make up other names, etc.10003 ... make up other names, etc.10004 ... make up other names, etc.10005 ... make up other names, etc.10006 ... make up other names, etc.ACCOUNTAccountNbr Balance100000110000021000003100000410000051000006000000OWNSClientNbr AccountNbr100021000310003100041000410005100051000610007100000110000021000003100000210000031000004100000510000061000001CityNotesowner 10002owner 10003, 10004owner 10003, 10004owner 10005owner 10005owner 10006Notes1st shared account2nd shared account1st shared account2nd shared account1st account2nd accountProvPostCd Phone EmailERROR join non-existing client to accountNotesNo accountSingle accountShare accounts with 10004Share accounts with 10003Two accountsSingle account but no transactions

THIS IS THE ANSWER AS PER BARTLEBY GUIDELINES, WE ARE NOT ALOWED TO ANS MORE THAN 3 SUBPARTS OF A…

The National Bank of Erehwon handles money and maintains bank accounts on behalf of clients. A client is a person who does business with the bank. A client may have any number of accounts, and an account may belong to multiple clients (e.g., spouses, business partners). The client record is used for identification and contact data. For each account, the bank maintains the current balance on hand. Clients are identified by a five digit number starting with 10001. Accounts are identified by a seven digit number starting with 1000001. When an account is first opened, its balance is set to zero. During the course of day-to-day business, Erehwon Bank applies transactions to accounts, including deposits, withdrawals, bill payments, and debit purchases or returns. For each transaction, the date and time, amount, and account are recorded, along with reference data applicable to that type of transaction: Deposits and withdrawals require the branch number to be recorded. Bill payments, and debit purchases or returns require the merchant number. Queries: List of only the Accounts that have multiple Clients associated Provide an alphabetic list by last name of all Clients showing their full name (e.g., Bob Barlow), with the number of Accounts they hold and the total balance of those Accounts Provide a count and total amount of Transactions for each Type description List of each Account showing the first Transaction date, type, and amount. (Hint: This is a correlated sub-query.) Count and Total Amount for each Transaction Type within each Account (Hint: This is an extended GROUP BY.)

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Question

The National Bank of Erehwon handles money and maintains bank accounts on behalf of clients.

A client is a person who does business with the bank.
A client may have any number of accounts, and an account may belong to multiple clients (e.g., spouses, business partners).
The client record is used for identification and contact data.
For each account, the bank maintains the current balance on hand.
Clients are identified by a five digit number starting with 10001.
Accounts are identified by a seven digit number starting with 1000001.

When an account is first opened, its balance is set to zero. During the course of day-to-day business, Erehwon Bank applies transactions to accounts, including deposits, withdrawals, bill payments, and debit purchases or returns. For each transaction, the date and time, amount, and account are recorded, along with reference data applicable to that type of transaction:

Deposits and withdrawals require the branch number to be recorded.
Bill payments, and debit purchases or returns require the merchant number.

Queries:

List of only the Accounts that have multiple Clients associated
Provide an alphabetic list by last name of all Clients showing their full name (e.g., Bob Barlow), with the number of Accounts they hold and the total balance of those Accounts
Provide a count and total amount of Transactions for each Type description
List of each Account showing the first Transaction date, type, and amount. (Hint: This is a correlated sub-query.)
Count and Total Amount for each Transaction Type within each Account (Hint: This is an extended GROUP BY.)

DATABASE INFO BELOW AND ATTACHED

TX_TYPE
Code	Description
D	Deposit
W	Withdrawal
B	Bill Payment
P	Purchase
R	Return


BRANCH
Nbr	Name
101	… make up your own name
102	… make up your own name
103	… make up your own name
104	… make up your own name


MERCHANT
Nbr	Name
301	… make up your own name
302	… make up your own name
303	… make up your own name
304	… make up your own name

10001 has no account, so no transactions
10002 account will have deposits and withdrawals only spread over 2 months
10003, 10004 will have all types of transactions
10005 will have deposits, then bill payments only
10006 account will have no transactions
TRANSACTION
TxNbr TxCode AccountNbr Amount Date
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
20
21
22
23
24
25
26
X
D
D
D
D
D
W
W
D
D
W
D
W
D
P
B
P
R
D
D
D
B
B
B
B
1000001
1000000
1000001
1000001
1000001
1000001
1000001
1000001
1000001
1000002
1000002
1000003
1000003
1000003
1000003
1000002
1000002
1000002
1000004
1000005
1000005
1000005
1000005
1000005
1000005
123.45 May 1 2019
234.56 May 1 2019
345.67 May 1 2019
100.00 May 1 2019
200.00 May 11 2019
300.00 May 21 2019
50.00 May 29 2009
75.00 May 29 2009
123.45 Jun 15 2019
1000.00 May 15 2019
456.78 May 15 2019
456.78 May 15 2019
500.00 May 18 2019
100.00 May 20 2019
65.78 May 20 2019
100.00 May 21 2019
200.00 May 21 2019
50.00 May 26 2019
2000.00 Jun 1 2019
2000.00 Jun 1 2019
2000.00 Jun 1 2019
3456.78 Jun 10 2019
432.10 Jun 15 2019
100.00 Jun 20 2019
80.00 Jun 25 2019
Time
12:00
12:00
12:00
10:00
11:00
12:00
10:00
11:00
13:00
9:00
9:05
9:10
14:00
13:00
14:50
9:00
10:00
12:34
13:00
13:00
14:00
12:00
14:30
15:55
16:56
RefNbr
101
101
111
101
101
101
102
103
101
104
104
104
104
104
304
301
302
301
101
101
102
301
302
303
304
Balance
*
*
*
100.00
300.00
600.00
550.00
475.00
598.45
1000.00
543.22
456.78
-43.22
56.78
-9.00
443.22
243.22
293.22
2000.00
2000.00
4000.00
543.22
111.12
11.12
-68.88
Notes
ERROR no such TxType
ERROR non-existing Account
ERROR no such Branch
Two tx same day diff time
Two tx same day diff time
Two tx same day diff time
Two tx same day diff time

CLIENT
ClientNbr FName
LName
Street
10001... your own names goes here
10002 ... make up other names, etc.
10003 ... make up other names, etc.
10004 ... make up other names, etc.
10005 ... make up other names, etc.
10006 ... make up other names, etc.
ACCOUNT
AccountNbr Balance
1000001
1000002
1000003
1000004
1000005
1000006
0
0
0
0
0
0
OWNS
ClientNbr AccountNbr
10002
10003
10003
10004
10004
10005
10005
10006
10007
1000001
1000002
1000003
1000002
1000003
1000004
1000005
1000006
1000001
City
Notes
owner 10002
owner 10003, 10004
owner 10003, 10004
owner 10005
owner 10005
owner 10006
Notes
1st shared account
2nd shared account
1st shared account
2nd shared account
1st account
2nd account
Prov
PostCd Phone Email
ERROR join non-existing client to account
Notes
No account
Single account
Share accounts with 10004
Share accounts with 10003
Two accounts
Single account but no transactions

Expert Solution

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here