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.  

1.  List five possible issues with the above database design.

2. Briefly explain two issues with the “Customer Records” table.  

3. Identify the repeating group in the “Movie Rental Records” table, and briefly justify your choice.

4. List the business rules that can result in this database.

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.

6. Using the initial dependency diagram you have given above, convert your table structure to 2NF, and draw the new dependency diagram.

7. Using the table structures, you created above, convert your table structure to 3NF, and draw the new dependency diagram.

8. Redesign the above database and write the relational schema showing the structure of all tables in 3NF.

9. Briefly explain the steps you took to redesign the database and how it fits with the normalization process.

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.

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. 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.

Transcribed Image Text:

Rent Customer Mobile # 1 1 2 3 4 4 4 5 6 Movie Code M3456 M3456 M3456 R2345 R2345 S4567 $4567 S4567 W1234 W1234 W1234 W1234 W1234 Leona Dunne Leona Dunne Jim Matai Alfred Ramas Alfred Ramas Alfred Ramas Alfred Ramas Leona Dunne Alfred Ramas 894- M3456 1238 894- 1238 894- 2285 844- 2573 844- 2573 844- 2573 844- 2573 894- 1238 Movie Rental Records Movie Code Movie 844- 2573 W1234 M3456 R2345 S4567 W1234 W1234 R2345 W1234 Movie Records Movie 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 Total Movie Copy Charge Amount $3.50 $7.50 $4.00 $7.50 $3.50 $4.00 $4.00 $4.00 $3.00 $7.00 $4.00 $7.00 $4.00 $7.00 $4.00 $4.00 $4.00 $4.00 3 3 2 1 2 4 5 1 1 # 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 Movie Copy # 1 2 3 1 2 1 2 3 1 2 3 4 5 Number 1001 1002 Date Date Return 15- 17- Mar-04 Mar-04 16- Mar-04 17- Mar-04 15- Mar-04 15- Mar-04 15- Mar-04 16- Mar-04 16- 18- Mar-04 Mar-04 16- Mar-04 18- Mar-04 19- 17- Mar-04 Mar-04 17- 19- Mar-04 Mar-04 Customer Records Name Alfred Ramas Leona Dunne 1003 Jim Matai 1004 Paul Olowski 1005 Myron Orlando 1006 Savita Devi 1007 Jone Yalo Mobile 844-2573 894-1238 894-2285 894-2180 222-1672 442-3381 297-1228 Rent Customer Mobile # 1 1 2 3 4 4 4 5 6 Movie Code M3456 M3456 M3456 R2345 R2345 S4567 $4567 S4567 W1234 W1234 W1234 W1234 W1234 Leona Dunne Leona Dunne Jim Matai Alfred Ramas Alfred Ramas Alfred Ramas Alfred Ramas Leona Dunne Alfred Ramas 894- M3456 1238 894- 1238 894- 2285 844- 2573 844- 2573 844- 2573 844- 2573 894- 1238 Movie Rental Records Movie Code Movie 844- 2573 W1234 M3456 R2345 S4567 W1234 W1234 R2345 W1234 Movie Records Movie 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 Total Movie Copy Charge Amount $3.50 $7.50 $4.00 $7.50 $3.50 $4.00 $4.00 $4.00 $3.00 $7.00 $4.00 $7.00 $4.00 $7.00 $4.00 $4.00 $4.00 $4.00 3 3 2 1 2 4 5 1 1 # 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 Movie Copy # 1 2 3 1 2 1 2 3 1 2 3 4 5 Number 1001 1002 Date Date Return 15- 17- Mar-04 Mar-04 16- Mar-04 17- Mar-04 15- Mar-04 15- Mar-04 15- Mar-04 16- Mar-04 16- 18- Mar-04 Mar-04 16- Mar-04 18- Mar-04 19- 17- Mar-04 Mar-04 17- 19- Mar-04 Mar-04 Customer Records Name Alfred Ramas Leona Dunne 1003 Jim Matai 1004 Paul Olowski 1005 Myron Orlando 1006 Savita Devi 1007 Jone Yalo Mobile 844-2573 894-1238 894-2285 894-2180 222-1672 442-3381 297-1228

Answer rating: 100% (QA)

Solution 1 Five possible issues 1 Movie rental record database do not cover for any details related to period of movie rental 2 Movie rental record database should show possible extension of time by u... View the full answer