CISC437/637, Fall 2014 CISC437/637, Database Systems HW #3 Due October 14 2014, 2:00pm, In Class ; Note: NO LATE SUBMISSION! 1. DSC Textbook, P. 105, 3.1 a-e. (CISC 437: 20% CISC 637: 15%) (Wherever the question says “Autumn” it should be “Fall”.) NOTE: You are not required to run the queries using MySQL. If you would like to try and run them, you can use the database data provided at: http://codex.cs.yale.edu/avi/db-book/db6/lab-dir/sample_tables-dir/index.html To run MySQL you need to log into the machine: cisc437.acad.cis.udel.edu for which you should already have an account and a password. To start MySQL use the command: mysql -u userid -p (use your own userid, which you have received) This will prompt you for a password, which again, you should have received by now. 2. DSC Textbook, P. 108, 3.11 CISC 437: a-c; CISC 637: a-d (15%) 3. DSC Textbook, P. 109, 3.12 a-e (CISC 437: 20%, CISC 637: 15%) (Wherever the question says “Autumn” it should be “Fall”.) 4. DSC Textbook, P. 109, 3.13 (15%) 5. DSC Textbook, P. 155, 4.12 (15%.) Hint: For writing the query with no outer join, use a nested, subquery structure as shown in the examples in class. 6. (15%) Consider the following relation instance with attributes A,B,C : A a1 a1 a1 a1 B b1 b1 b2 b2 © Hagit Shatkay, 2014 C c1 c2 c1 c3 Page 1 of 2 For each of the following functional dependecies say whether it is imposed (as far as you can tell based on the available instances) – or violated – by the relational schema. Provide a short (no longer than 2 lines!) justification for each answer. a. AgB b. AgC c. BgA d. CgA e. CgAB f. BCgA CISC637 only 7. DSC Textbook, P. 155, 4.13. (10%) The question refers to the university database schema that is displayed in Chapter 2, page 48, Figure 2.9 and is later translated into a full SQL declerations in Chapters 3 and 4. © Hagit Shatkay, 2014 Page 2 of 2
© Copyright 2024