Fundamentals of Analytics and Business Intelligence Maximum Score: 25 Points Please answer all questions in the space provided and submit your solutions on Blackboard by 11:59 pm on October 10. Late submissions will be penalized by 20 Points. Please ask me in class if you need any clarifications on the interpretation of the questions. Please present your work clearly and concisely so that I can follow your approach and arguments easily. You may use reference material, but your submission should strictly reflect your individual effort. There should be no consultations or collaboration. Any collaboration carries a penalty of 20 Points. 1 Question 1. Constrained Optimization [12 points] A company produces and sells four grades of industrial solvents - A, B, C, and D. The selling price per gallon of each grade of solvent is $6.40, $5.00, $4.20, and $3.50 respectively. Because of demand limitations, the company can sell at most 100,000 gallons of solvent A; 300,000 gallons of solvent B; 360,000 gallons of solvent C; and 220,000 gallons of solvent D. The solvents are produced by blending two types of liquid ingredients: Ingredient1 and Ingredient2. The cost price per gallon for the ingredients are $3.20 for Ingredient1 and $2.40 for Ingredient2. At most 400,000 gallons of Ingredient1 and 600,000 gallons of Ingredient2 are available. Regulations require a minimum percentage by volume of Ingredient1 in each grade of solvent: 60% for A, 50% for B, 40% for C, and 10% for D. For your convenience, the information presented above is summarized in the tables below: Solvent grade Selling price per gallon Maximum quantity allowed (gallons) Minimum % of Ingredient1 required Ingredient1 Ingredient2 Availability (gallons) 400,000 600,000 A $ 6.40 100,000 60% B $ 5.00 300,000 50% C $ 4.20 360,000 40% D $ 3.50 220,000 10% Price per gallon $ 3.20 $ 2.40 The company must determine an optimal production plan so as to maximize their profits subject to the applicable constraints. 2 (a) Formulate the problem as a linear program (4 points) Define the decision variables: Specify the objective function: Specify the constraints: 3 (b) Solve the linear program and report your optimal solutions (4 points) i. What is the maximum profit attainable under an optimal plan? (2 points) Maximum Profit = ii. $ How many gallons of each ingredient should be used to produce each grade of solvent under this optimal plan? (1 point) Quantity (in gallons) A B C D Ingredient1 Ingredient2 iii. How many gallons of each ingredient is used up under this optimal plan? (1 point) Quantity (in gallons) Used Ingredient1 Ingredient2 Available 400,000 600,000 (c) At most how much should the company be willing to pay per gallon for additional quantities of the ingredients? Justify your answer. (4 points) The maximum amount that the company should be willing to pay for each additional gallon: Ingredient1 : Ingredient2 : $ $ per gallon. per gallon. Reasoning: 4 Question 2: Decision Analysis and Bayes Rule [13 points] Two trained classifiers - A and B - are available to classify tissue samples as benign or malignant. Each classifier is prone to two types of errors. The table below summarizes the probability of these errors: Classifier s A B False Positive Error Probability 0.06 0.04 False Negative Error Probability 0.01 0.02 False Positive error probability is defined as the conditional probability of classifying a healthy tissue sample as malignant. False Negative error probability is defined as the conditional probability of classifying an infected tissue sample as benign. Historical data suggests that 10 percent of the tissue samples are infected. a. Based on the information specified above, what is the conditional probability that: (i) A tissue sample classified as benign by classifier B is actually infected? (2 Points) (ii) A tissue sample classified as malignant by classifier A is actually healthy? (2 Points) b. If the cost of classifying an infected tissue sample as benign is 100 times the cost of classifying a healthy tissue as malignant, which classifier should a risk neutral rational decision maker use? Why? (3 Points) 5 c. We assumed that 10% of the tissue samples are infected. At least how low should the percentage of infected tissues be for a risk neutral rational decision maker to prefer classifier B? Assume that all other parameters remain as specified in (a) and (b). (3 Points) d. We assumed that the ratio of the cost of classifying an infected tissue sample as benign to the cost of classifying a healthy tissue as malignant is 100. At least how low must this ratio be for a risk neutral rational decision maker to prefer classifier B? Assume that all other parameters remain as specified in (a) and (b). (3 Points) 6 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 BRANCHSTREET BRANCH CITY BRANCH STATE BRANCHZI P REVENUE TARGET BRANCHMANAGER b7 Branch7 E 349 49 St New York NY 11010 800000 e32 No, since the operation will violate the following integrity constraint: branchManager is a foreign key that references the primary key employeeID in the EMPLOYEE table. The EMPLOYEE table does not contain a record with employeeID = 'e32'. (b) Can the following record be inserted into the EMPLOYEE table? Why? EMPLOYEEID EMPLNAM E EMPFNAM E e37 Joe Smith EM PTITLE EMPSTARTDATE EM PBRAN CH EMPSALARY EMPSUPERVI SOR 450000 06/23/2004 e2 No, since the operation will violate the following integrity constraint: empSupervisor is a foreign key in the EMPLOYEE table that references the EMPLOYEE table and no record with employeeID ='e2' appears in the EMPLOYEE table. (c) Can the following record be deleted from the PRODLINE table? Why? ORDERNUMBER PR ODCODE QUANTITY o1 p1 1000 Yes, since the operation will not violate any integrity constraints. (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 No, since the operation will violate the following integrity constraint: BRANCH table contains a record with branchManager = 'e4'. branchManager in the BRANCH table is a foreign key that references the primary key employeeID in the EMPLOYEE table. (e) Can the following record be deleted from the ORDERS table? Why? ORDERNUM BER ORDD ATE SALE SPERSON CUSTOM ERI D o1 12-AUG-07 e5 c1 No, since the operation will violate the following integrity constraint: PRODLINE table contains a record with orderNumber = 'o1'. orderNumber is a foreign key in the PRODLINE table that references the ORDER table. Question 2: SQL [4 5 = 20 Points] 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, employeeID, empsalary employee empSupervisor; b. [5 points] List the ordernumber of orders that include both products as well as installation services. select from c. ordernumber instline, prodline; [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. select from where and and and group by having c.customerID, orders o, employee e, branch b, customer c o.salesperson = e.employeeID o.customerID = c.customerID e.empbranch = b.branchnumber b.branchstate = c.custstate c.customerID count (*) >= 10; 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. select from where and and group by having e.empSupervisor, employee e, orders o, product p, prodline pl e.employeeID = o.salesperson o.ordernumber = pl.ordernumber p.productcode = pl.prodCode e.empSupervisor count (quantity*price) >= $800,000; 2 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 EMPSUPERVI 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 BRANCHSTREET BRANCH CITY BRANCH STATE BRANCHZI P REVENUE TARGET 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 CUSTOMERID CUSTNAME CUSTSTREET CUSTCI TY CUST STATE CUSTZIP CUSTPH ONE 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 3 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 ORDERNUM BER ORDD ATE SALE SPERSON CUSTOM ERI D 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 PR ODCODE QUANTITY 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 ORDERNUMBER 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 4 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 BRANCHSTREET BRANCH CITY BRANCH STATE BRANCHZI P REVENUE TARGET BRANCHMANAGER b7 Branch7 E 349 49 St New York NY 11010 800000 e32 No, since the operation will violate the following integrity constraint: branchManager is a foreign key that references the primary key employeeID in the EMPLOYEE table. The EMPLOYEE table does not contain a record with employeeID = 'e32'. (b) Can the following record be inserted into the EMPLOYEE table? Why? EMPLOYEEID EMPLNAM E EMPFNAM E e37 Joe Smith EM PTITLE EMPSTARTDATE EM PBRAN CH EMPSALARY EMPSUPERVI SOR 450000 06/23/2004 e2 No, since the operation will violate the following integrity constraint: empSupervisor is a foreign key in the EMPLOYEE table that references the EMPLOYEE table and no record with employeeID ='e2' appears in the EMPLOYEE table. (c) Can the following record be deleted from the PRODLINE table? Why? ORDERNUMBER PR ODCODE QUANTITY o1 p1 1000 Yes, since the operation will not violate any integrity constraints. (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 No, since the operation will violate the following integrity constraint: BRANCH table contains a record with branchManager = 'e4'. branchManager in the BRANCH table is a foreign key that references the primary key employeeID in the EMPLOYEE table. (e) Can the following record be deleted from the ORDERS table? Why? ORDERNUM BER ORDD ATE SALE SPERSON CUSTOM ERI D o1 12-AUG-07 e5 c1 No, since the operation will violate the following integrity constraint: PRODLINE table contains a record with orderNumber = 'o1'. orderNumber is a foreign key in the PRODLINE table that references the ORDER table. Question 2: SQL [4 5 = 20 Points] 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, employeeID, empsalary employee empSupervisor; b. [5 points] List the ordernumber of orders that include both products as well as installation services. select from c. ordernumber instline, prodline; [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. select from where and and and group by having c.customerID, orders o, employee e, branch b, customer c o.salesperson = e.employeeID o.customerID = c.customerID e.empbranch = b.branchnumber b.branchstate = c.custstate c.customerID count (*) >= 10; 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. select from where and and group by having e.empSupervisor, employee e, orders o, product p, prodline pl e.employeeID = o.salesperson o.ordernumber = pl.ordernumber p.productcode = pl.prodCode e.empSupervisor count (quantity*price) >= $800,000; 2 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 EMPSUPERVI 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 BRANCHSTREET BRANCH CITY BRANCH STATE BRANCHZI P REVENUE TARGET 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 CUSTOMERID CUSTNAME CUSTSTREET CUSTCI TY CUST STATE CUSTZIP CUSTPH ONE 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 3 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 ORDERNUM BER ORDD ATE SALE SPERSON CUSTOM ERI D 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 PR ODCODE QUANTITY 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 ORDERNUMBER 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 4