Database assignment – sql + oracle

a. Teams of 2: Draw up one new report that may be used by ABC Bank (see note below c ) that uses your own fictional data. Some of your data should overlap with the report above and your report must introduce one significant new entity (table).
b. Teams of 3: Draw up two new reports that may be used by ABC Bank (see note below c ) that use your own fictional data. Some of your data should overlap with the report above and your reports must introduce a total of two significant new entities.
c. The reports for each group will be specified by the lecturer when groups register their team. It will be up to each team to develop appropriate data for the reports. The reports will be selected from:
a. Branch Report. Bank branch number, manager name etc.
b. BPay Payee Report. A customer may pay bills using BPay. The Payee report shows who each customer has set up for payment via BPay and the payees BPay account data and customer comments.
c. Interest Rates Report. The report shows tiered interest rates and and the associated balances to achieve the specified interest rates for different account types.
d. Online Accounts Report. Online account details e.g. username, and links to customers. An online account may link to multiple customers. For example a person may have multiple customer numbers as they can be different customers; individually, with their partner and with their business.
Question 2
a. Normalise the above report and the teams report(s) together using the methodology described in lectures.
i. Show all of the steps involved.
ii. Clearly highlight primary keys and foreign keys.
iii. Include brief comments and a rationale for all of the decisions made during normalization, such as: why a business rule was created, what business rule was used (by number), partial dependencies, transitive dependencies, why relations were combined, why a relation was not changed going from one normal form to the next etc.
iv. Specify your own business rules as necessary, numbering each rule. Ensure the rules are reasonable!
Question 3
Draw an Entity-Relationship Diagram (as covered in the lecture14) for your database based on your solution to question 2. Show entities and relationships, clearly showing cardinality (multiplicity). Attributes are not required.
Questions 4-7
Solutions to questions 4 to 7 should include thoughtfully commented SQL scripts. Your answers should include the SQL and the comments from your scripts.
Question 4
Develop the database tables in Oracle on dbfben based on your answer to questions 2 and 3 within your database as specified above. Enter about 5-10 rows in each table which, as a minimum, MUST contain the data specified in the reports (which include team member names). The data should be well thought out, providing meaningful examples that usefully demonstrate the type of data and relationships that can occur.
a. Give all of the SQL statements to create the tables. Note: Include integrity constraints in your definitions as applicable.
b. Give the SQL statements to create any of the secondary indexes applicable.
c. Include the output from the SQL*Plus DESCRIBE commands for the tables.
d. Provide the SQL statements used to insert data into the tables.
e. List the contents of each table in ascending primary key order.
Question 5
Create the following report(s).
a. Give the SQL statements to create your view for the report shown below plus a listing of the data.
b. Write a report using SQL/SQL*Plus to recreate, as best as possible, statements for all customers (see the report shown below). Assume that each account statement is on a single page, and that page numbering is overall for all customers rather than for each account. The report output can be text or HTML.
c. For the report provide the report script and the report output.
d. Teams of 3 are to do an additional report based on one of their reports from question 1; providing (a)-(c) above.
Page 1
ABC Bank
Monthly Account Statement

Customer Number 258393
Name Mr. Frederick Smitherick
Address 45 High Street, BENDIGO 3550
Account Number 1234 5678
Type Cheque
Opening Balance $260.45

Tran
Num Date Ref Type Description Amount Balance
234 03 Aug 2012 015 Cash Deposit 50.00 $310.45
235 15 Aug 2012 1577 130 Cheque Withdrawal 100.00- $210.45
236 26 Aug 2012 1578 130 Cheque Withdrawal 20.00- $190.45
237 30 Aug 2012 301 Bank Fee 20.00- $170.45
238 31 Aug 2012 1900 140 Cheque Deposit 80.00 $250.45
Page 2

ABC Bank
Monthly Account Statement

Customer Number 258393
Name Mr. Frederick Smitherick
Address 45 High Street, BENDIGO 3550
Account Number 8765 4321
Type Credit
Opening Balance $233.00-

Tran
Num Date Ref Type Description Amount Balance
311 11 Aug 2012 015 Cash Deposit 150.00 $83.00-
312 15 Aug 2012 8263 411 EFTPOS 250.00- $333.00-
313 15 Aug 2012 5600 411 EFTPOS 100.00- $433.00-
Page 3
ABC Bank
Monthly Account Statement

Customer Number 261001
Name Ms Jemima Jones
Address 12 Main Rd, EPSOM 3551
Account Number 3776 5544
Type Cheque
Opening Balance $1751.29

Tran
Num Date Ref Type Description Amount Balance
238 02 Aug 2012 015 Cash Deposit 500.00 $2251.29
239 20 Aug 2012 1578 130 Cheque Withdrawal 200.00- $2051.29
Question 6
Provide commands that give only the following access to your database:
a. Each member of your group full access to each of your tables.
b. The user tutor read access only to each table and view.
Question 7
a. Describe an insertion and/or update and/or deletion transaction for your database that references at least 2 base tables. (The transaction should be comprised of many SQL statements.)
b. Provide a SQL script for your transaction.
c. Run your script and provide before and after listings of the tables affected by the transaction.
(15 + 35 + 10 + 20 + 10 + 3 + 7 = 100 marks)
Assignment Submission
• The name of the database account the data is in.
• The answers to each question.
? Clearly label each question.
? Format SQL code.
? Query/queries in a monospaced font such as Courier New
? The query should be followed by either (a) the text results produced by Oracle in a monospaced font (8, 9 or 10pt) or (b) HTML tables produced by Oracle. Where relevant the results should include meaningful column headings, the resultant data and the number of rows.
Email the following electronic documentation
Email the following to the lecturer, clearly identifying the team
• The SQL script(s) for questions 4 to 7.
Database
• Do NOT modify the database on dbfben after you have submitted the assignment as this will be checked against your answers