Homework Assignment 2CS425 – Database OrganizationInstructionsTry to answer all the questions using what you have learned in classWhen writing a query, write the query in a way that it would work over all possible database instances and not just for the given example instance!After creating the relations, you can make up some data and insert them into the relationsPlease submit the homework (as a single sql file your_name.sql) electronically using blackboardIn the your_name.sql file, make sure you have marked the answer for each question using commentsAll the sql scripts should be executable in Oracle 11g (e.g., on fourier) or recent versions Consider the following database schema and example instance:EmployeecidcnamecitystateA101ChaseNew YorkNYA102CitiNew YorkNYA103TD BankBostonMAA104MotoChicagoILA105DisneyOrlandoFL. . .. . .. . .. . .Customereidenamedobsalary0011Chris01/03/1986100,0000012Edward07/24/1973120,0000013Anna08/12/199280,0000014Kim08/26/198695,0000015Carol04/15/198897,000. . .. . .. . .. . .Productpidpnameunit_priceC17811Desk500C17812Chair200C23453PC1,200C34451Mac1,300. . .. . .. . .Saleseidcidpidquantity0011A101C178117000011A102C178122000012A102C234531000013A101C3445150. . .. . .. . .. . .Manageseid1eid2001100130013001700120013. . .. . .Hints:Underlined attribute(s) form the primary key of a relation.Relation Sales stores the sales information: the salesman (employee ID) who is in charge of the ordered product, the product ID, the customer ID, and the quantities of ordered products.The attributes eid, cid and pid of relation Sales are the foreign keys to relations Employee (salesman),Customer and Product, respectively.Relation Manages stores the “managing” information in the company (e.g., the employee with eid1 is the direct manager of the employee with eid2 ).All the IDs’ are strings.Part 2.1 SQL DDL (Total: 20 Points) Question 2.1.1 (15 Points)Write the SQL statements for all the five relations: Employee, Customer, Product, Sales, and Manages. Note:An appropriate data type should be used for each attribute, and (2) All the foreign keys should be created.Question 2.1.2 (5 Points)Write an SQL statement that adds a constraint to the Employee relation to make sure that the salary attribute cannot be NULL, and the value of this attribute has to be between 40,000 and 500,000. Furthermore, the default value for this attribute should be 80,000.Part 2.2 SQL Queries (Total: 56 Points) Question 2.2.1 (7 Points)Write an SQL query that returns the products (pid and pname) which are sold by the direct manager(s) of employee ‘0013’ and the quantity for any customer (not aggregated) is greater than 300. Hint: relation Manages stores the “managing” information in the company (e.g., the employee with eid1 is the direct manager of the employee with eid2 ).Question 2.2.2 (7 Points)Write an SQL query that returns the IDs of all the products, with the total ordered quantity in ‘Chicago’ greater than the total ordered quantity in ‘Boston’.Question 2.2.3 (7 Points)Write an SQL query that returns the name and unit price of all the products (pname and unit_price) which are never ordered by ‘Disney’.Question 2.2.4 (7 Points)Write an SQL query that returns all the employees and their average sales amount (of all the customers) for each of the products (query result: eid, pid and avg_sales).Question 2.2.5 (7 Points)Write an SQL query that returns all the employees and the total number of employees directly managed by each employee (if no one is directly managed, then return 0). Hint: relation Manages stores the “managing” information in the company (e.g., the employee with eid1 is the direct manager of the employee with eid2 ).Question 2.2.6 (7 Points)Write an SQL query that returns the number of products where the average ordered quantity (of all the employees and customers) is lower than 1000.Question 2.2.7 (7 Points)Write an SQL query that returns the product names and the name of the customer(s) who ordered the highest quantity (from all the employees). Query result: pname and cname.Question 2.2.8 (7 Points)Write an SQL query which returns all the employees’ IDs and their managed employees’ IDs. Hint: the result should include not only directly managed employees but also indirectly managed employees.Part 2.3 SQL Updates (Total: 24 Points) Question 2.3.1 (5 Points)Delete all the customers without placing any order.Question 2.3.2 (4 Points)A new product Telephone is added to the warehouse (available for sale). The unit price is $100. Add the information to the Product relation. Assume that pid is automatically maintained by the system.Question 2.3.3 (8 Points)Update the unit_price in the Product relation according to this rule:if it is negative, set it to 0if it is larger than 10,000, then set it to 2,500if it is NULL, set it to 1,000if none of the above applies do not change the unit_priceNote that we expect you to write a single statement that implements this.Question 2.3.4 (7 Points)
Update the salaries of employees as his/her current salary + 0.02 · (his/her total sales amount).
