Suppose you are given the form below. Assume that a video cannot be rented twice
ID: 3589776 • Letter: S
Question
Suppose you are given the form below. Assume that a video cannot be rented twice on one receipt. Show your work.
Moore Videos
Person ID: 226
Name: Lindsey Moore
Receipt: 68395
Phone: 734 668-4385
Date: 9/1/08
City, ST: Ann Arbor, MI
Video Information
ID
Name
Type
Days
Date Due
Cost
325
Shrek
N
1
9/2/08
3.00
548
Remember the Titans
H
2
9/3/08
2.50
6437
The Replacement Killers
H
2
9/3/08
2.50
Total
8.00
1. Write the 1NF relation.
2. Write the 2NF relations. Identify the identifiers of these relations.
3. Identify any transitive dependencies.
4. Write the 3NF relations.
Moore Videos
Person ID: 226
Name: Lindsey Moore
Receipt: 68395
Phone: 734 668-4385
Date: 9/1/08
City, ST: Ann Arbor, MI
Video Information
ID
Name
Type
Days
Date Due
Cost
325
Shrek
N
1
9/2/08
3.00
548
Remember the Titans
H
2
9/3/08
2.50
6437
The Replacement Killers
H
2
9/3/08
2.50
Total
8.00
Explanation / Answer
I NF will be
Let us first list all the attributes
Receipt
Date
PersonID
PersonName
Phone
City
State
VideoID
VideoName
Type
Days
Date Due
Cost
Total
Derived Data
We can calculate Date Due from Date and Days.
Total can be calculated from cost.
repeating groups
NA
Multi value attributes
City adn state should be separated into two differnt attributes
Primary identifier
Receipt and VID
So 1 NF
[Receipt, VideoID]
...>
Date
PersonID
PersonName
Phone
City
State
VideoName
Type
Days
Cost
Total
2 NF
Let us identify Primary key
Receipt —> Date: On the receipt, you know what date the receipt was given.
Receipt —> [PersonID, PersonName, Phone, City, State]: On the receipt, you know information about the user details
VideoID —> [VideoName, Type, Days, Cost]:On the video id, video info is known.
This brings to
Receipt —> [Date, PersonID, PersonName, Phone, City, State]
VideoID —> [VideoName, Type, Days, Cost]
Receipt, VideoID
first relation identifier is Receipt; for the second is VideoID; for the third is [Receipt, VID].
Since the relations are in 1NF
Find trasitive dependancies
PersonID —> [PersonName, Phone, City, State]
Type —> [Days, Cost]
3NF
To get to 3NF, we have to remove the transitive dependencies from the 2NF relations.
Receipt —> [Date, PersonID]
PersonID —> [PName, Phone, City, State]
ViseoID —> [VideoName, Type]
Type —> [Days, Cost]
Receipt, VideoID
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.