Question
Rental ID Rental Date Cust ID Customer Name Customer Phone Clerk ID Clerk Name Disk ID Title ID Title Rating Rating Description Genre ID Genre
| Rental ID | Rental Date | Cust ID | Customer Name | Customer Phone | Clerk ID | Clerk Name | Disk ID | Title ID | Title | Rating | Rating Description | Genre ID | Genre Name | Format Code | Format Descr | Fee Code | Fee Amt |
| 1 | 9/2/2017 | 23 | Jean Mackey | 972-555-1143 | 15 | Mary Jones | 215 | 92 | Hacksaw Ridge | R | Restricted Under 17 | AC | Action | D | DVD | A | $ 3.00 |
|
|
|
|
|
|
|
| 191 | 76 | Wonder Woman | PG-13 | Restricted Under 17 | AC | Action | D | DVD | B | $ 2.00 |
| 2 | 9/2/2016 | 102 | Jack Hughes | 214-555-2014 | 15 | Mary Jones | 259 | 119 | Divergent | PG-13 | Inappropriate Under 13 | AC | Action | B | BLU-RAY |
A | $ 3.00 |
| 3 | 9/2/2016 | 154 | Alicia Moore | 817-555-1919 | 21 | Mark White | 89 | 12 | Casa Blanca | PG | Parental Guidance | CL | Classics | D | DVD | B | $ 2.00 |
|
|
|
|
|
|
|
| 96 | 29 | Despicable Me 2 | PG | Parental Guidance | FM | Family | D | DVD | B | $ 2.00 |
|
|
|
|
|
|
|
| 152 | 42 | Frozen | PG | Parental Guidance | FM | Family | D | DVD | B | $ 2.00 |
| 4 | 9/3/2016 | 83 | Carrie Brown |
| 30 | Renee Smith | 86 | 58 | The Specialist | R | Restricted Under 17 | AC | Action | B | BLU-RAY |
A | $ 3.00 |
| 5 | 9/3/2016 | 23 | Jean Mackey | 972-555-1143 | 15 | Mary Jones | 260 | 119 | Divergent | PG-13 | Inappropriate Under 13 | AC | Action | D | DVD |
A | $ 3.00 |
| 6 | 9/4/2016 | 83 | Carrie Brown |
| 12 | Tim Shore | 301 | 230 | Lone Survivor | R | Restricted Under 17 | AC | Action | B | BLU-RAY |
A | $ 3.00 |
| 7 | 9/4/2016 | 154 | Alicia Moore | 817-555-1919 | 15 | Mary Jones | 376 | 245 | Rio 2 | G | General | FM | Family | B | BLU-RAY | A | $ 3.00 |
|
|
|
|
|
|
|
| 153 | 42 | Frozen | PG | Parental Guidance | FM | Family | D | DVD | B | $ 2.00 |
| 8 | 9/5/2016 | 53 | Ashleigh Hayes | 972-555-0628 | 12 | Tim Shore | 202 | 159 | Boss Baby | PG | Parental Guidance | FM | Family | D | DVD | B | $ 2.00 |
|
|
|
|
|
|
|
| 402 | 240 | Sleepy Hollow | R | Restricted Under 17 | HR | Horror | B | BLU-RAY |
A | $ 3.00 |
| 9 | 9/5/2016 | 68 | Anthony Smith |
| 15 | Mary Jones | 381 | 218 | Gone With The Wind | PG | Parental Guidance | CL | Classics | D | DVD | A | $ 3.00 |
|
|
|
|
|
|
|
| 216 | 92 | Hacksaw Ridge | R | Restricted Under 17 | AC | Action | B | BLU-RAY |
A | $ 3.00 |
| 10 | 9/5/2016 | 23 | Jean Mackey | 972-555-1143 | 21 | Mark White | 203 | 159 | Boss Baby | PG | Parental Guidance | FM | Family | D | DVD | B | $ 2.00 |
|
|
|
|
|
|
|
| 97 | 29 | Despicable Me 2 | PG | Parental Guidance | FM | Family | D | DVD | A | $ 3.00 |
| 11 | 9/5/2016 | 72 | Shane Wesley | 817-555-0112 | 42 | Nicole Walker | 376 | 245 | Rio 2 | G | General | FM | Family | B | BLU-RAY |
B | $ 2.00 |
|
|
|
|
|
|
|
| 421 | 296 | The Hurt Locker | R | Restricted Under 17 | AC | Action | D | DVD | A | $ 3.00 |
|
|
|
|
|
|
|
| 299 | 35 | Minions | PG | Parental Guidance | FM | Family | D | DVD | A | $ 3.00 |
Additional Information:
At least one disk must be rented in each rental transaction, but some disks may not have been rented, yet.
Each rental transaction belongs to a specific customer, but a customer may exist without any rentals (i.e., we may have customers on file who have not yet rented from us).
Each title belongs to a specific genre, and all genres have at least one corresponding title.
Every title has a rating, but some ratings may not have any corresponding titles.
Each fee code has a specific fee amount, which does not change over time.
The fee code associated with a specific disk may vary over time, and some fee codes may not have been used yet in any transactions.
Each rental transaction is entered into the system by a clerk, but some clerks may not have entered any rental transactions.
Based on the information provided above, complete the following:
1. FIRST NORMAL FORM (1NF) 20 points:
Decompose the composite attributes into simple attributes.
Convert the table above to 1NF (eliminate repeating groups of data and select an appropriate PK).
Show the table structure format (table name with PK and all dependent attributes in parentheses).
Create a dependency diagram for the table above.
2. SECOND NORMAL FORM (2NF) 20 points:
Show the table structure format for each table in 2NF.
Create the dependency diagrams for the resulting tables.
3. THIRD NORMAL FORM (3NF) - 25 points:
Convert to 3NF and show the table structure format for each table.
Create the dependency diagrams for the resulting tables.
4. ENTITY-RELATIONSHIP MODEL - 35 points:
Using Chen notation, create an ERD showing all of the 3NF tables above. You must show the entities, relationships, connectivity, participation, and cardinality (it is not necessary to show the attributes on the ERD).
Based on the ERD created in step 4a, create a relational schema showing the entities, their attributes, and the connectivity of the relationships. This relational schema should show each entity with its attributes listed inside a rectangle, similar to Crows Foot notation (i.e., do not show the attributes inside ovals stemming from the entities). This may be done in Access.
This assignment is due at the beginning of class on 10/2/16. Please staple or bind all pages and include a cover sheet listing each group members names, listed alphabetically by last name. Microsoft Visio or similar software is recommended to create the required diagrams. HAND-WRITTEN ASSIGNMENTS WILL NOT BE ACCEPTED.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Nested Relations And Complex Objects In Databases Lncs 361
Authors: Serge Abiteboul ,Patrick C. Fischer ,Hans-Jorg Schek
1st Edition
3540511717, 978-3540511717
