CSCI585 Midterm exam June 15th 2017 Last Name First Name Student ID Email Signature Duration 2 hours CLOSED book and notes No electronic devices DO YOUR OWN WORK If you are discovered to have cheated in any manner you will get a 0 and be reported to SJACS If you continue working on the exam after time is up you will get a 0 Problem Set Number of Points Your Score Q1 Q2 Q3 Q4 Q5 Q6 Total 1 4 5 1 1 1 2 5 2 2 1 5 1 2 3 1 2 3 2 1 1 4 25 Q1 5 points total ER Modeling a 1 point What is weak entity What is weak relationship Give short example for each Answer A weak entity is an entity that displays existence dependence and inherits the primary key of its parent entity Example An EMPLOYEE might or might not have a DEPENDENT but a DEPENDENT cannot exist without an EMPLOYEE A weak relationship is a relationship in which the primary key of the related entity does not contain a primary key component of the parent entity Example A course may have zero or many classes A CLASS may have COURSE ID as just a foreign key b 4 points Draw ER Diagram based on the following description A company has multiple departments Each department has multiple employees An employee could not be in two departments at the same time In each department one employee is the manager that manages other employees Each department is located at a specific office of the company An office may have multiple departments varying from one to three Answer Q2 5 points total SQL a 1 point What s the difference between inner and outer joins Explain using example Answer Inner join is a join operation in which only rows that meet selected criterion are selected In outer join all unmatched pairs are retained Unmatched values in the related table are left null Example if joining product and vendor outer join will also include products that aren t sold by vendors and vendors that aren t selling any products b 1 point Consider the following two query results SELECT count AS total FROM books SELECT count AS author1 total FROM books WHERE authorId 1 Total 100 author1 total 15 Given the above query results what will be the result of the query below Circle below SELECT count AS author not 1 total FROM orders WHERE authorId 1 A 50 B 85 C 15 D Insufficient information Answer It will not necessarily be 85 because we do not know if there are any authorId s that are NULL If there are 2 NULL authorId s then the answer would be 83 Hence the answer is d c 1 point Which relational operation does the following SQL query implement SELECT name FROM driver WHERE vehicle IN SELECT vehicle FROM vehicles GROUP BY name HAVING COUNT SELECT COUNT FROM vehicles Answer Division d 2 points Given the following enrollment table write a SQL query to list number of students enrolled in each course ClassID StudentID ClassID CSCI495 321 564 CSCI110 CSCI585 321 564 CSCI495 EE101 789 321 EE101 Grade B C A A F B Answer SELECT ClassID COUNT AS Total FROM enrollment GROUP BY ClassID Q3 5 points total NORMALIZATION We are starting to design a new chocolate rating platform We have asked experts to taste different sweet products sweets and express their overall rating 0 5 and their professional comments Each expert taster ID may try different products from different companies We are only focused on US based companies and record the state of the headquarters of the company in our reports Each product has a specific type e g confection candy bar etc and also a unique price Even though each taster can rate different types of products we only record their top preference type for further reference Moreover basic personal information of each taster is available in our records Our sample data is in the following table Brand ID 29001 Hershey Reese s Name State PA Type Confection 1 29 3 Price Rate Comm 29001 Hershey NutRageous PA Bar 29001 Hershey Pieces 29001 Hershey York Confection 0 99 3 PA PA WA WA WA Candy Bar Bar Candy 1 39 4 0 99 2 1 45 3 1 45 5 1 79 3 29001 Mars Snickers 29001 Mars Twix 29001 Mars M M 1202 Hershey York PA Confection 0 99 3 1202 Mars Bounty WA Bar 1 55 3 1202 Mars Twix WA Bar 1 45 3 1202 Mars M M WA Candy 1 79 5 1202 Hershey Kisses PA Chips 0 23 2 Good peanut butter Packaging isn t great Worst candy ever Too sweet Not a fan of almonds Best product ever The coating is too hard Just a failed cop of PBCs Too hard Too much coconut Too much nuts The best taste ever Poor quality chocolate Bar Bar Bar Pref Bar Info Michael Mast Michael Mast Michael Mast Michael Mast Michael Mast Michael Mast Michael Mast Candy Paul Bar Bar Bar Bulcke Bulcke Bulcke Bulcke Bulcke Bulcke Bulcke Candy Paul Candy Paul Candy Paul Candy Paul 1202 Hershey Snack Barz PA Bar 0 89 3 Candy Paul 1202 Mars 3Musketeers WA Bar 1 69 2 Best quality Candy Paul a 2 points Identify dependencies and draw dependency diagram b 2 points Normalize our record table Show resulting tables in 3NF c 1 point What s the purpose of DEnormalization Give example when completely normalized table could be undesirable Answer A process by which a table is changed from a higher level normal form to a lower level normal form usually to increase processing speed Q4 3 points TRANSACTION MANAGEMENT a 1 point What are the names of the four ACID properties Answer Atomicity Consistency Isolation and Durability b 2 points Given below is the transaction log TRL ID TRX NUM Table ROW ID Attribute BEFORE VALUE AFTER VALUE 341 101 Start Transaction 352 101 PRODUCT 1558 QW1 PROD QOH 25 23 363 101 CUSTOMER 10001 CUST BALANCE 525 75 615 73 365 101 End Transaction Assuming the system crashed somewhere between TRL ID 353 364 i e the last two rows are not in the logs what should be the values of the two fields that were updated in that transaction after recovery A B C D 25 525 75 23 525 75 23 615 73 25 525 75 Answer D Briefly explain your answer The transaction hadn t committed before the database crashed After recovery none of its changes should be presented Therefore the answer is 25 525 75 Q5 3 points OPTIMIZATION a 1 point Given below are two queries that perform the same function Which one do you think would be more efficient and why SELECT id name FROM viterbi Students WHERE branch Computer Science AND courseTaken CS 585 SELECT id name FROM viterbi Students WHERE courseTaken CS 585 AND branch Computer Science Answer The second one is more optimized as in AND we should write the condition that is more likely to be false first Clearly the number of students taking CS 585 would be …