Create entity-relationship model

Create entity-relationship model

Consider the two tables below and answer the questions:

Customers

Orders

a. Write an SQL query to find the total amount of orders grouped by the customer ID.

b. Write an SQL query to create orders table

c. Write an SQL query to add an email attribute to customers table

d. Write an SQL query to find to drop age attribute from the customers table

e. Write an SQL query to find the IDs and names of customers along with the amounts and date of orders for the same customer. (customer IDs are the same in both tables)

Question Two
2 Marks

Learning Outcome(s):

Create entity-relationship model, relational model, and write SQL queries.

Consider the following Table:

Employee (id, name, job_name, dept_name, salary)

a. Create a view named dept_total_salary from the above table that shows the department names and the total salary of each department that exceeds 1000000.

b. Can you update or insert into this view? Why or Why not?

Question Three
1 Mark

Learning Outcome(s):

Apply principles and concepts of information integrity, security and confidentiality.

Write SQL authorization statements on the employee table as per the following requirements:

· You have three managers with manager1, manager2 and manager3 user ids. All of them are members of the role manager.

· All managers have the same privileges: select, insert, update, delete.

· No managers can transfer his privileges on the employee table.