Due: 26/05/2020

At: 11.55pm

Case study

1. List five possible issues with the above database design. (5 marks) 2. Briefly explain two issues with the Customer Records table. (2 marks) 3. Identify the repeating group in the Movie Rental Records table, and briefly justify your choice. (2 marks) 4. List the business rules that can result in this database. (4 marks) 5. Use the following diagram to create the dependency diagram for the Movie Rental Records table showing all types of dependencies that are functional, partial, and transitive. (8 marks)

6. Using the initial dependency diagram you have given above, convert your table structure to 2NF, and draw the new dependency diagram. (6 marks) 7. Using the table structures, you created above, convert your table structure to 3NF, and draw the new dependency diagram. (5 marks) 8. Redesign the above database and write the relational schema showing the structure of all tables in 3NF. (15 marks) 9. Briefly explain the steps you took to redesign the database and how it fits with the normalization process. (6 marks) 10. Create the ERD for the database showing the entities, relationship between the entities (show the connectivity, cardinality, and strength of relationship), primary and foreign keys, and other attributes along with the data types. (17 marks) 11. Without introducing data redundancy, how can we modify this database to allow us to record the correct total amount for each movie rental transaction? At present, for some records, the total amount does not add up to the actual total for the rental transaction. This indicates an error by the user or a discount/fine that is not correctly handled by the database. Please discuss in detail any new business rule(s), the structure of any new table that you plan to introduce, and the changes (if any) to be made to existing tables. (5 marks)

SKYnet is a movie rental company situated in Nadi, Fiji. They hired a database designer who designed the following three tables for the company to allow it to manage customer, movie, and movie rental records. Study these tables and answer the questions that follow. Rent Customer Mobile Date Movie Rental Records Movie Code Movie Movie Total Copy Charge Amount # M3456 3 $3.50 $7.50 Date Return 1 1 W1234 3 $4.00 $7.50 2 M3456 2 $3.50 $4.00 3 R2345 1 $4.00 $4.00 4 S4567 2 $3.00 $7.00 Leona Dunne Leona Dunne Jim Matai Alfred Ramas Alfred Ramas Alfred Ramas Alfred Ramas Leona Dunne Alfred Ramas 894- 1238 894 1238 894- 2285 844- 2573 844- 2573 844 2573 844- 2573 894- 1238 844- 2573 15 17- Mar-04 Mar-04 15 16- Mar-04 Mar-04 15 17- Mar-04 Mar-04 15- Mar-04 16- Mar-04 18- Mar-04 Mar 04 16 18- Mar-04 Mar-04 17 19- Mar-04 Mar-04 19- Mar-04 Mar-04 4 W1234 4 $4.00 $7.00 16 4 W1234 5 $7.00 5 R2345 $4.00 $4.00 1 1 6 W1234 $4.00 $4.00 17 Movie Code Movie Records Movie Movie Copy # 1 2 3 M3456 M3456 M3466 R2345 R2345 S4567 S4567 S4667 W1234 W1234 W1234 W1234 W1234 1 Ramblin' Tulip Ramblin' Tulip Ramblin' Tulip Once Upon a Midnight Breezy Once Upon a Midnight Breezy Tulips and Threelips Tulips and Threelips Tulips and Threelips Bright Stars and Doodle Berries Bright Stars and Doodle Berries Bright Stars and Doodle Berries Bright Stars and Doodle Berries Bright Stars and Doodle Berries Movie Charge $3.50 $3.50 $3.50 $4.00 $4.00 $3.00 $3.00 $3.00 $4.00 $4.00 $4.00 $4.00 $4.00 Customer Records Number Name Mobile 1001 | Alfred Ramas 844-2573 1002 Leona Dunne 894-1238 1003 Jim Matai 894-2285 1004 Paul Olowski 894-2180 1005 Myron Orlando 222 1672 1006 Savita Devi 442-3381 1007 Jone Yalo 297-1229 2 1 2 3 1 2 3 4 5 Customer Mobile Movie Code Date Rent # Date Movie Movie Total Copy Charge Amount # Return