Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Narrative Description The National Bank of Erehwon handles money and maintains bank accounts on behalf of clients. A client is a person who does business

Narrative Description

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.

The text in the Notes columns in the spreadsheet is for reference only, not to be attributes in the tables.

Tab Table Notes
Client Client Make up your own names, etc. for the 6 clients.
Account
Owns
Ref Tx_Type
Branch Make up your own names
Merchant Make up your own names
Tx Transaction

  1. Views (4 marks)

    To facilitate queries, three views are required. Submit your SQL script(s) as well as a SELECT * FROM listing of each view.

    • Join of Transaction to Type description (1 mark)

    • Join of Client to Account via the Owns table, containing the clients number, name, the account number, and balance. (1 mark) (Hint: Use an outer join as one client has no accounts.)

    • Join of Deposit and Withdraw transactions to Bank Branch UNION with join of Bill Payment and Debit Purchase, or Return transactions to Merchant (i.e., all transactions with appropriate reference name.) (2 marks)

  2. Queries (7 marks)

    There are five queries to be developed and demonstrated. Submit your SQL script(s) as well as the query listings for each.

    • List of only the Accounts that have multiple Clients associated (1 mark)

    • 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 (1 mark)

    • Provide a count and total amount of Transactions for each Type description (1 mark)

    • List of each Account showing the first Transaction date, type, and amount. (Hint: This is a correlated sub-query.) (2 marks)

    • Count and Total Amount for each Transaction Type within each Account (Hint: This is an extended GROUP BY.) (2 marks)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

More Books

Students also viewed these Databases questions