build me a SQL project | SQL

Get Custom Essay on build me a SQL project | SQL

Let Our Team of Pofessional Writers Take Care of Your Paper.

Building and Populating the Schema (9 marks)

Develop a database in Oracle to implement the Erehwon Bank scenario. Code the SQL DDL to build all the tables, complete with constraints and default values (except for lookup of the Ref_Nbr for a Transaction … that will require procedural code in step 4). Choose appropriate data types for each of the columns. Add those indexes that you believe would be useful. Include comments in your SQL script(s) to document any decisions you made.

Populate your database with the test data.

In your SQL INSERT statements, you must make use of sequences for generating key values for the Client, Account, and Transaction tables.

You must use the TO_DATE function to get proper date and time values for the transactions.

Note that trigger code to be written in step 4 will be used to validate a Transaction Ref_Nbr against either a Bank_Nbr or Merchant_Nbr as well as update the account balance according to the type and amount of the transaction. The test data already has entries for the Owns and Transaction INSERTS that should be rejected due to invalid FK lookups. Take a screen shot of running those scripts to show that is the case.

It is not required to demonstrate NOT NULL constraints.

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 client’s 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)

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)

PL/SQL code (10 marks)

Code the PL/SQL module for each of the following:

Trigger to enforce the referential integrity for the Transaction Ref_Nbr: (3 marks)

Deposit or Withdrawal transaction to Bank Branch

Bill Payment, Debit Purchase, or Return transaction to Merchant

Trigger to update the Account balance for each new transaction entered (assume that a transaction will never be updated or deleted). (3 marks)

A procedure that displays a nicely formatted audit statement for a given account number (as a parameter). This will show each transaction in date / time sequence along with the running balance. (4 marks)

To test that the triggers are correctly implemented, do the following:

Truncate the Transaction table

Reset the Tx_Nbr sequence back to 1

Update the Account table, setting the Balance back to zero

Re-run the INSERT statements for the transactions

Use simple queries to demonstrate that the results in the Transaction and Account tables are as expected

Skills: SQL

See more:
build xhtml project, setup sql project, simple sql project, access sql project, sql project registration, biztalk access sql project, need project called hotel reservation system developed j2ee, free searches build fake project mission 10×10, build fish project, sql project class, free insurance company management sql project report software, gprs sql project, build medical project, cnet ftp sql project, build exe project, build sql filter html page






(
0 reviews

)




Kamloops, Canada



Project ID: #27677434

Get 20% Discount on This Paper
Pages (550 words)
Approximate price: -

Try it now!

Get 20% Discount on This Paper

We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

Best Quality Essays has stood as the world’s leading custom essay writing services providers. Once you enter all the details in the order form under the place order button, the rest is up to us.

Essays

Essay Writing Services

At Best Quality Essays, we prioritize on all aspects that bring about a good grade such as impeccable grammar, proper structure, zero-plagiarism and conformance to guidelines. Our experienced team of writers will help you completed your essays and other assignments.

Admissions

Admission and Business Papers

Be assured that you’ll definitely get accepted to the Master’s level program at any university once you enter all the details in the order form. We won’t leave you here; we will also help you secure a good position in your aspired workplace by creating an outstanding resume or portfolio once you place an order.

Editing

Editing and Proofreading

Our skilled editing and writing team will help you restructure you paper, paraphrase, correct grammar and replace plagiarized sections on your paper just on time. The service is geared toward eliminating any mistakes and rather enhancing better quality.

Coursework

Technical papers

We have writers in almost all fields including the most technical fields. You don’t have to worry about the complexity of your paper. Simply enter as much details as possible in the place order section.