Computer Science homework help

Computer Science homework help.

Annual Report
 
    
 
Introduction to Database
IT244

 

Instructions:
·       You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on Blackboard via the allocated folder. These files must not be in compressed format.
·       It is your responsibility to check and make sure that you have uploaded both the correct files.
·       Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between words, hide characters, use different character sets or languages other than English or any kind of manipulation).
·       Email submission will not be accepted.
·       You are advised to make your work clear and well-presented. This includes filling your information on the cover page.
·       You must use this template, failing which will result in zero mark.
·       You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.
·       Late submission will result in ZERO mark.
·       The work should be your own, copying from students or other resources will result in ZERO mark.
·       Use Times New Roman font for all your answers.
 

     
Name: ###
 
CRN: ###		   ID: ###
 
     

 

 

2 Marks

Learning Outcome(s):
Create entity-relationship model, relational model, and write SQL queries.
 
 
 
 
 
 
 

Question One

Consider the two tables below and answer the questions:
Customers
 
 
 
Orders
 
 
 
 

  1. Write an SQL query to find the total amount of orders grouped by the customer ID.      
  2. Write an SQL query to create orders table
  3. Write an SQL query to add an email attribute to customers table
  4. Write an SQL query to find to drop age attribute from the customers table
  5. 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)

 
 
 
 

2 Marks

Learning Outcome(s):
Create entity-relationship model, relational model, and write SQL queries.
 
 
 
 
 
 

Question Two

Consider the following Table:
Employee (id, name, job_name, dept_name, salary)

  1. 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.

 
 
 
 
 
 
 
 

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

 
 

1 Mark
Learning Outcome(s):
Apply principles and concepts of information integrity, security and confidentiality.
 
 
 
 
 
 

Question Three

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.

 

Computer Science homework help