Final ASsignment
Microsoft Word - Final Exam.docx Instructions 1- Please read instructions carefully. 2- The exam time is 120 minutes. 3- The exam is an open book written exam. You are allowed to use any printed resources during the exam. No electronic resources or access to MySQL is allowed during the exam. 4- The exam has 9 questions and 12 pages, including this page. Check your packet and make sure that you have all 12 pages. 5- Questions have varying number of points. Start by looking through the whole exam to decide on plan. 6- Write solutions in the designated spaces. You can use the back of each page as scratch, if needed. Make sure you write neatly. 7- Try to provide the simplest solutions. Correct answers that are unnecessarily complex will not receive full grades. 8- Attempt all questions and show your work for partial credit. 9- Do not flip page except when instructed to do so. Grade Question Points Score 1: MC 4 2: Integrity 3 3: ER 7 4: Dependencies 4 5: Normalization 12 6: ER 2 7: MySQL 6 8: MySQL 8 9: MySQL and ER 10 Total 56 Question 1 (4 points) Choose the answer that best suits each of the following statements/phrases. 1) Relationship degree is a) The number of rows in the relationship b) The number of columns in the relationship c) The number of entities involved in the relationship d) None of the above 2) If all the tables in your database follow the algebraic definition of a relation, then your database is a) Not necessarily in any normal form b) In first normal form c) In second normal form d) In third normal form 3) The following makes two relations not Union-compatible: a) Different number of attributes b) Corresponding attributes not having compatible types c) Both a and b d) Neither a or b 4) The _______ returns every row from one table joined with every row from the second table. a) NATURAL JOIN b) SEMI JOIN c) CROSS JOIN d) None of these Question 2 (3 points) Very briefly explain entity integrity and referential integrity Question 3 (7 points) Consider the following ER diagram and select for each phrase in the left column below the diagram the option that suits it best in the right column The degree of Product a) Undefined in diagram The degree of Customer/Order relationship b) Many-to-many The cardinality of Order/Product relationship c) Required The cardinality of Customer/Order relationship d) Optional The cardinality of Order e) One-to-One The participation of Order in Order/Product relationship f) 2 The participation of Customer in Customer/Product relationship g) 3 h) 9 Question 4 (4 points) Consider the following rule for functional dependencies If A à B and B,C à D then A,C à D a) Is this rule correct? Circle the correct answer Yes No b) If your answer to (a) is Yes, explain the reasoning behind your answer (i.e. provide a simple informal proof to your claim). If your answer is No, give a counter example using a simple relation and a set of tuples where the if part is true but the then part is false (i.e. prove by counter example). 1 1 1 5 (12 points) Consider the following relational schema Sale(clerk, store, city, date, itemNum, size, color) Item(itemNum, size, color, price) The following assumptions (and only these assumptions) are true · Sale records that a clerk sold an item on a particular day · Item records prices and available sizes and colors · Each clerk work in one store · Each store is in one city · A given item number has the same price regardless of size or color · Each item comes in one or more sizes and one or more colors, and each item is available (can be sold) in all combinations of sizes and colors for that item · All data is atomic Answer the following: a) Based on the assumptions above, specify all keys (primary, alternate (if any), and foreign) for relations Sale and Item b) Identify functional dependencies for relations Sale and Item. Of the set of functional dependencies you list, identify any partial or transitive dependencies Question 5 – cont. c) Are Sale and Item in 3NF? Justify. d) If not, normalize Sale and/or Item, as needed into 3NF 5 – cont. e) Draw the crow’s foot ER diagram for the resulting relational schema. Show primary and foreign keys on your diagram. Question Question 1 1 1 6 (2 points) Consider the following ER diagram a) Identify/describe the error in the given ER diagram b) Redraw the ER diagram, fixing the error you identified 7 (6 points) Given the following relational schema instance Note: price and quantity are numeric; ignore the $ in price What is the output of the the MySQL commands in a and b a) SELECT * FROM Items WHERE brand = “Gap” ORDER BY price; b) SELECT DISTINCT i.brand from Items i WHERE i.product IN (SELECT o.product FROM Orders o WHERE o.quantity > 1); c) Verbalize the query (write it in English) that each of the above MySQL commands answers 8 (8 points) For the same relational schema in question 7, write MySQL statements for the following a) Find the names of customers who bought both wipes and books. b) Get the products and brands that Ann bought, ordered by (default ordering) the brand of these products. c) Generate a list of customer names who bought products with price > 20, along with these products and their prices. The list should be ordered by customer. d) Find the total price of merchandize bough by each customer 9 (10 points) Given the following relational schema Staff (sID, sName, DOB, address, position, salary, dID) Department(dID, dName) where · sID is the primary key for Staff · Every tuple in Staff must have all fields defined, except DOB and address (which can be defined or can be null) · The minimum salary for any staff member is 60k and the maximum salary is 850k · dID is the primary key for Department · Every tuple in Department must have all fields defined · dID is the foreign key that connects Staff to Department · A staff is assigned to a specific department, and a department can have multiple staff members. It is possible to have a tuple in Department that is not associated with any Staff tuples. · A department tuple can’t be deleted if there are any employees assigned to this department. If the department name or ID is changed, then tuples of staff in this departments must be updated accordingly. a) Write the MySQL commands to create the database schema described above. Make sure that the commands you write enforce as many constraints as possible from the constraints described. Question Question Question 1 1 1 b) Draw the ER diagram the represents the database schema described above. Model as many details/constraints as possible on the diagram. 1 1 1 Database System Concepts, Seventh Edition Database System Concepts S E V E N T H E D I T I O N Abraham Silberschatz Henry F. Korth S. Sudarshan DATABASE SYSTEM CONCEPTS S I X T H E D I T I O N Abraham Silberschatz Yale University Henry F. Korth Lehigh University S. Sudarshan Indian Institute of Technology, Bombay TM silberschatz6e_fm_i-ii.indd Page i 12/3/09 2:51:50 PM user /Users/user/Desktop/Temp Work/00November_2009/24:11:09/VYN/silberschatz S E V E N T H E D I T I O N DATABASE SYSTEM CONCEPTS, SEVENTH EDITION Published by McGraw-Hill Education, 2 Penn Plaza, New York, NY 10121. Copyright © 2020 by McGraw-Hill Education. All rights reserved. Printed in the United States of America. Previous editions © 2011, 2006, and 2002. No part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written consent of McGraw-Hill Education, including, but not limited to, in any network or other electronic storage or transmission, or broadcast for distance learning. Some ancillaries, including electronic and print components, may not be available to customers outside the United States. This book is printed on acid-free paper. 1 2 3 4 5 6 7 8 9 LCR 21 20 19 ISBN 978-0-07-802215-9 (bound edition) MHID 0-07-802215-0 (bound edition) ISBN 978-1-260-51504-6 (loose-leaf edition) MHID 1-260-51504-4 (loose-leaf edition) Portfolio Manager: Thomas Scaife Ph.D. Product Developers: Tina Bower & Megan Platt Marketing Manager: Shannon O’Donnell Content Project Managers: Laura Bies & Sandra Schnee Buyer: Susan K. Culbertson Design: Egzon Shaqiri Content Licensing Specialists: Shawntel Schmitt & Lorraine Buczek Cover Image: © Pavel Nesvadba/Shutterstock Compositor: Aptara®, Inc. All credits appearing on page or at the end of the book are considered to be an extension of the copyright page. Library of Congress Cataloging-in-Publication Data Names: Silberschatz, Abraham, author. | Korth, Henry F., author. | Sudarshan