Assignment -
Objectives
The objective of this assignment is:
- to modify and to extend the structures of a sample relational database;
- to implement the modifications of database contents;
- to implement the simple and complex queries;
- to implement view for queries;
- to implement stored procedure and function.
Tasks
Download a2create.sql and a2drop.sql from the Moodle, execute the script file a2create.sql to create tables before task 1 implemented, and execute the script file a2drop.sql at the end of this assignment to save your space.
Task 1: Data definitions, modifications and manipulations
Implement SQL script in a file a2task1.sql that performs the following tasks. Add the tasks' number in the comment lines for each sub-task in the script file a2task1.sql. For example,
/* Task 1.1 */
1. The value of "phone#" in the table Bank must be unique.
2. Each manager of a bank should be an employee of that bank.
3. The value of "sex" must be either ‘M' or ‘F'.
4. Add a new column "total_staff_number" in the table Bank. Set the correct values for the new column. (Note: The correct values should be got from the corresponded table by using one SQL statement).
5. The value of account "type" must be one of "SAVING", "CREDIT", "HOME LOAN", "PERSONAL LOAN", "TERM DEPOSIT", "CHECK", "ISAVER" and "SHARE".
6. The value of "DOB" cannot be NULL.
7. The manager ‘00101' of the bank ‘082886' has been swapped with the manager ‘01010' of the bank ‘082237'. Update the correspondent records in the tables. (Note: The employees' number should keep the same as before. The constraints of tables have been changed by previous tasks.)
8. Implement a parameterised SQL script that prompts about full information describing a dependent and inserts a new row into the table Dependent.
Task 2: Data retrieval operations
By using the tables modified in Task 1, implement the following data retrieval operations using SELECT statement (one statement for each question) of SQL in a file a2task2.sql. Add the tasks' number in the comment lines for each sub-task in the script file a2task2.sql. For example,
/* Task 2.1 */
1. Find names of all banks and their total number of customers.
2. Find all customers' name, address and total amount of balances. Display 0 (zero) if a customer has no account balance.
3. Find all the employees' name, DOB and salary that have no dependent by using "NOT EXISTS".
4. Find all the banks' name and total amount of "HOME LOAN" for each of them.
5. Find all the managers' name and date of birth.
6. Find all the employees' number and name that directly supervised by the manager of the bank ‘NAB UoW'.
7. Find all the customers' number, name, date of birth and address who have the most debts (total amount of balance is the smallest).
8. Find all the customers' number, name and date of birth that have at least three accounts.
9. Find all the customers' number, name and address that their credit accounts are in debt. (Balance less than zero).
10. Find the average age of customers that have "Saving" account.
Task 3: Views, stored procedure and function
By using the tables modified in Task 1, implement the following data retrieval operations using VIEW of SQL in a file a2task3.sql. Add the tasks' number in the comment lines for each sub-task in the script file a2task3.sql. For example,
/* Task 3.1 */
1. Create a view CUSTOMERACCOUNTS that contains customer BSB#, customer#, name, account#, type and balance. Display the results from the view like following:
| BSB# |
CUSTOMER#NAME |
ACCOUNT#
|
TYPE |
BALANCE |
|
012878 123456
|
Mike
|
32345678 |
HOME LOAN
|
-453234.52
|
|
|
|
22345678 |
CREDIT
|
-1534.52
|
|
|
|
12345678 |
SAVING
|
1234.52
|
|
012878 123458
|
Jean
|
11001234 |
SAVING
|
3213.54
|
|
|
|
21001234 |
CREDIT
|
-120.34
|
|
|
|
31001234 |
PERSONAL LOAN
|
-63121.23
|
|
012878 123460
|
Alex
|
30224321 |
HOME LOAN
|
-329131.76
|
|
|
|
20224321 |
CREDIT
|
-131.23
|
|
|
|
10224321 |
SAVING
|
131.23
|
|
|
|
40224321 |
TERM DEPOSIT
|
5500
|
|
082886 123456
|
Ben
|
12345678 |
SAVING
|
567.32
|
|
|
|
42345678 |
TERM DEPOSIT
|
12000
|
|
|
|
52345678 |
ISAVER
|
2312.11
|
|
|
|
32345678 |
PERSONAL LOAN
|
-32123.32
|
|
|
|
22345678 |
CHECK
|
567.32
|
|
082886 123457
|
Duke
|
13214567 |
SAVING
|
1234.56
|
|
|
|
23214567 |
SHARE
|
21234.56
|
|
|
|
43214567 |
TERM DEPOSIT
|
32000
|
|
082886 123472
|
Josef
|
24314567 |
SHARE
|
1323.45
|
|
|
|
14314567 |
SAVING
|
323.45
|
|
082886 123475
|
Harry
|
|
|
|
|
082886 123483
|
Will
|
|
|
|
2. You will allow SCOTT to read the information of Employee name, dependent name and relationship. Display the access privilege on the required information.
Attachment:- Assignment.rar