Database Modeling & Design Fourth Edition- P54: Database technology has evolved rapidly in the three decades since the rise and eventual dominance of relational database systems. While many specialized database systems (object-oriented, spatial, multimedia, etc.) have found substantial user communities in the science and engineering fields, relational systems remain the dominant database technology for business enterprises. | 252 Exercises Transformation of the Conceptual Model to SQL Problem 5-1 1. Transform your integrated ER diagram from Problem 4-1 into a SQL database with five to ten rows per table of data you make up to fit the database schema. 2. Demonstrate your database by displaying all the queries below a. Which video store branches have Shrek in stock available now b. In what section of the store film category can you find Terminator c. For customer Anika Sorenstam what titles are currently being rented and what are the overdue charges if any d. Any query of your choice. Show me what your system can really do Normalization and Minimum Set of Tables Problem 6-1 Given the table R1 A B C with FDs A - B and B - C 1. Is A a superkey for this table 2. Is B a superkey for this table 3. Is this table in 3NF BCNF or neither Problem 6-2 Given the table R A B C D with FDs AB- C and BD- A 1. What are all the superkeys of this table 2. What are all the candidate keys for this table 3. Is this table in 3NF BCNF or neither Exercises 253 Problem 6-3 1. From the ER diagram given below and the resulting tables and candidate key attributes defined what are all the functional dependencies FDs you can derive just by looking at the diagram and list of candidate keys Table customer order department salesperson item order-dept-sales order-item-sales Candidate Key s cid orderno deptno sid itemno orderno sid AND orderno deptno orderno itemno Table FDs customer order department salesperson item order-dept-sales order-item-sales 2. What is the level of normalization for each of these tables based on the information given Problem 6-4 The following functional dependencies FDs represent a set of airline reservation system database constraints. Design a minimum set of BCNF tables preserving all FDs and express your solution in terms of the code letters given below a time-saving device for your analysis . Is the set of tables you derived also BCNF reservation_no - agent_no agent_name airline_name flight_no
