Nova Southeastern University _MSIT630_Summer18_solutions2

Document Content and Description Below

Solutions#2 MSIT 630 Database Systems (Summer, 2018) 1. Consider the relation, r, shown below. Give the result of the following query: (4 points) building room_number time_slot_id course_id sec... _id SELECT building, room_number, time_slot_id, count(*) FROM r GROUP BY ROLLUP (building, room_number, time_slot_id); In MySQL, use: SELECT building, room_number, time_slot_id, count(*) FROM r GROUP BY building, room_number, time_slot_id with rollup; Building Room_number Time_slot_id Count(*) 2. Consider an employee database with two relations employee(employee-name, street, city) works(employee-name, company-name, salary) where the primary keys are underlined. Write a query to find companies whose employees earn a lower salary, on average, than the average salary at “First Bank Corporation”. Use user defined SQL functions (create function command) as appropriate to answer the above query, the function takes the company name as the input and returns the average salary of the given company. (6 points) 3. Write the following queries in relational algebra, using the university schema. (Appendix A, page 1271) (16 points, 4 points each) 4. Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted.(6 points) 5. Explain the distinction between disjoint and overlapping constraints. (3 points) 6. Explain the distinction between total and partial constraints. (3 points) 7. Consider the following set F of functional dependencies on the relation schema a. c. Prove (using Armstrong’s axioms) that AF is a superkey. [Show More]

Last updated: 1 year ago

Preview 1 out of 5 pages