This work strictly reflects my own efforts. I have not discussed these questions or collaborated with anyone. Name: _________________________________________________________________ (Please PRINT) MMIS 630: Database Management and Applications Mid Term Exam, Fall 2015 Maximum Score: 25 Points Please answer all questions in this document and submit your work on Blackboard by 11:59 p.m. on October 12. Penalty for late submission: 20 points Penalty for collaboration: 25 points You may use reference material. Your submission should reflect your individual effort. There should be no consultations or collaboration. Please let me know in class on October 7 if you need any clarifications. Answer both questions with reference to the database specified for homework 2. A copy of the database can be found in the last two pages of this document. Assume that all referential integrity constraints are enforced as \"restrict on delete\". Your SQL queries should work for every valid instance of the database (and not just the specified instance). Question 1. Relational Model and Integrity Constraints. [1 5 = 5 Points] (a) Can the following record be inserted into the BRANCH table? Why? BRANCHNUM BER BRANCHNAM E BRANCH STREET BRANCHCI TY BRANCHSTATE BRANCHZIP REVENUETARGET BRANCHMANAGER b7 Branch7 E 349 49 St New York NY 11010 800000 e32 Answer: No, since the operation will violate the following integrity constraint Referential integrity: branchManager is a foreign key in the BRANCH table that references the EMPLOYEE table and no record with branchmanager ='e32' appears in the EMPLOYEE table. (b) Can the following record be inserted into the EMPLOYEE table? Why? EMPLOYEEID EMPLNAM E EM PFNAME e37 Joe Smith EM PTITLE EMPSTARTDATE EMPBRANCH EM PSALARY EM PSUPERVI SOR 450000 06/23/2004 e2 Answer: No, since the operation will violate the following integrity constraint Operation is not permitted because it would violate entity integrity: the attribute EMPBRANCH is NULL and EMPBRANCH is a primary key in the BRANCH table. (c) Can the following record be deleted from the PRODLINE table? Why? ORDERNUMBER PROD CODE QUANTI TY o1 p1 1000 Answer: No, since the operation will violate the following integrity constraint Referential integrity: prodcode is a primary key in the PRODUCT table and a record with prodcode ='p1' appears in the PRODUCT table. (d) Can the following record be deleted from the EMPLOYEE table? Why? EMPLOYEEID EMPLNAME EMPFNAME e4 Day Daisy EMPTITLE branch_manager EMPSTARTDATE EMPBRANCH EMPSALARY EMPSUPERVISOR 17-AUG-03 b4 480000 e1 Answer: No, since the operation will violate the following integrity constraint Referential integrity: empsupervisor is a foreign key in the EMPLOYEE table that references the EMPLOYEE table and a record with empsupervisor ='e1' appears in the EMPLOYEE table. (e) Can the following record be deleted from the ORDERS table? Why? ORDERNUMBER ORDDATE SALESPERSON CUSTOMERID o1 12-AUG-07 e5 c1 Answer: No, since the operation will violate the following integrity constraint Referential integrity: customerid is a primary key in the CUSTOMER table and a record wit customerid ='c1' appears in the CUSTOMER table. Question 2: SQL [4 5 = 20 Points] 2 Formulate SQL queries for the following (a - d) with reference to the specified database. Ensure that your SQL queries will produce a correct response for every instance of the database (and not just for the database instance specified). a. [5 points] For each supervisor, list the supervisor's employeeid and salary. SELECT FROM GROUP BY empsupervisor , empsalary employee empsupervisor b. [5 points] List the ordernumber of orders that include both products as well as installation services. SELECT FROM INTERSECT SELECT FROM ordernumber prodline ordernumber instline 3 c. [5 points] List the customerid of customers who have placed at least 10 orders with salespersons located in the same state as the customer's state. d. [5 points] List the employeeid of supervisors whose supervisees, collectively, are responsible for at least $800,000 in revenue from the sale of products. 4 Database instance for question 1. The Primary key (PK) and Foreign Keys (FK) are identified for each table. Table: EMPLOYEE PK: employeeID; FK: empBranch references BRANCH; FK: empSupervisor references EMPLOYEE EMPLOYEEID EMPLNAM E EM PFNAME EMPSTARTDATE EMPBRANCH EM PSALARY EM PSUPERVI SOR e1 Adam Alan CEO EM PTITLE 11-JAN-02 b1 600000 - e2 Bryson Brad branch_manager 01-FEB-03 b2 400000 e1 e3 Clay Cedric branch_manager 21-JUN-01 b3 450000 e1 e4 Day Daisy branch_manager 17-AUG-03 b4 480000 e1 e5 Engle Eva salesperson 01-JAN-04 b2 120000 e2 e6 Falcon Fred salesperson 01-JAN-02 b2 80000 e2 e7 Gandhi Gagan salesperson 01-JAN-03 b3 90000 e3 e8 Hee Hwang salesperson 01-JUN-04 b3 95000 e3 e9 Ingram Irene salesperson 24-SEP-02 b4 110000 e4 e10 Jerome John salesperson 25-AUG-02 b4 75000 e4 Table: BRANCH PK: branchNumber FK: branchManager references EMPLOYEE BRANCHNUM BER BRANCHNAM E BRANCH STREET BRANCHCI TY BRANCHSTATE BRANCHZIP REVENUETARGET BRANCHMANAGER b1 branch1 9700 NW 41 St Miami FL 33178 800000 e1 b2 branch2 8700 SW 24 St Miami FL 33170 600000 e2 b3 branch3 E 200 47 St New York NY 11010 1000000 e3 b4 branch4 300 Park Avenue New York NY 10010 1200000 e4 Table: CUSTOMER PK: customerID CUSTOM ERI D CUSTNAME CUSTSTREET CUST CITY CUST STATE CUSTZIP CUSTPHONE c1 cust1 - Miami FL 33164 - c2 cust2 - Miami FL 33120 - c3 cust3 - Miami FL 33110 - c4 cust4 - Miami FL 33178 - c5 cust5 - New York NY 11021 - c6 cust6 - New York NY 11001 - Table: PRODUCT PK: productCode PROD UCT CODE PR ODDESCRIPTI ON PRI CE STOCKLEVEL p1 carpet 40 10000 p2 tile 20 100000 p3 pergo 50 50000 5 Table: INSTALLATION PK: installationType I NSTALLTYPE INSTALLDESCRIPTI ON RATE i1 carpet installation 40 i2 tile installation 50 i3 pergo installation 60 Table: ORDERS PK: orderNumber FK: customerID references CUSTOMER; FK: salesPerson references EMPLOYEE ORDERNUMBER ORDDATE SALESPERSON CUSTOMERID o1 12-AUG-07 e5 c1 o2 14-DEC-07 e5 c2 o3 04-NOV-07 e5 c3 o4 15-AUG-07 e5 c4 o5 22-NOV-07 e10 c5 o6 01-JUL-07 e10 c6 o7 12-DEC-07 e6 c6 o8 30-NOV-07 e9 c2 Table: PRODLINE PK: orderNumber + prodCode FK: orderNumber references ORDERS; FK: prodCode references PRODUCT ORDERNUMBER PROD CODE QUANTI TY o1 p1 1000 o1 p2 500 o2 p3 200 o3 p1 600 o3 p3 100 o4 p2 1000 o5 p2 800 Table: INSTLINE PK: orderNumber + instType FK: orderNumber references ORDERS; FK: instType references INSTALLATION ORDERNUM BER INSTTYPE HOUR S o1 i1 20 o1 i2 30 o1 i3 10 o2 i1 10 o2 i2 20 o6 i1 20 o6 i2 10 o7 i3 10 o8 i2 20 6