Ask Computer Engineering Expert

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

Computer Engineering, Engineering

  • Category:- Computer Engineering
  • Reference No.:- M92261404
  • Price:- $100

Guranteed 48 Hours Delivery, In Price:- $100

Have any Question?


Related Questions in Computer Engineering

Does bmw have a guided missile corporate culture and

Does BMW have a guided missile corporate culture, and incubator corporate culture, a family corporate culture, or an Eiffel tower corporate culture?

Rebecca borrows 10000 at 18 compounded annually she pays

Rebecca borrows $10,000 at 18% compounded annually. She pays off the loan over a 5-year period with annual payments, starting at year 1. Each successive payment is $700 greater than the previous payment. (a) How much was ...

Jeff decides to start saving some money from this upcoming

Jeff decides to start saving some money from this upcoming month onwards. He decides to save only $500 at first, but each month he will increase the amount invested by $100. He will do it for 60 months (including the fir ...

Suppose you make 30 annual investments in a fund that pays

Suppose you make 30 annual investments in a fund that pays 6% compounded annually. If your first deposit is $7,500 and each successive deposit is 6% greater than the preceding deposit, how much will be in the fund immedi ...

Question -under what circumstances is it ethical if ever to

Question :- Under what circumstances is it ethical, if ever, to use consumer information in marketing research? Explain why you consider it ethical or unethical.

What are the differences between four types of economics

What are the differences between four types of economics evaluations and their differences with other two (budget impact analysis (BIA) and cost of illness (COI) studies)?

What type of economic system does norway have explain some

What type of economic system does Norway have? Explain some of the benefits of this system to the country and some of the drawbacks,

Among the who imf and wto which of these governmental

Among the WHO, IMF, and WTO, which of these governmental institutions do you feel has most profoundly shaped healthcare outcomes in low-income countries and why? Please support your reasons with examples and research/doc ...

A real estate developer will build two different types of

A real estate developer will build two different types of apartments in a residential area: one- bedroom apartments and two-bedroom apartments. In addition, the developer will build either a swimming pool or a tennis cou ...

Question what some of the reasons that evolutionary models

Question : What some of the reasons that evolutionary models are considered by many to be the best approach to software development. The response must be typed, single spaced, must be in times new roman font (size 12) an ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As