Data Base
Problem
Tom’s Equipment Rental is a small company that rents equipment to individuals. Each piece of equipment has a unique serial number, so even two identical pieces of equipment will have different serial numbers. Over time, pieces of equipment will be rented many times, possibly even by the same customer. When a customer rents equipment, a rental agreement is created that lists each item being rented during that visit. Each rental agreement has a unique agreement number and can include the rental of one or more pieces of equipment. Each customer has a unique customer number and may have more than one rental agreement (even on the same day). The table below shows sample data for this problem in a report format.
Agree_No
Agree_Date
Cust_No
Cust_Name
Cust_Phone
Serial_No
Item_Name
Item_Rate
100365
01/25/04
10534
Joe Brown
555-234-1212
S6345
Circular Saw
$10.00
S5489
Jig Saw
$5.00
100366
01/25/04
11937
Jill Jones
555-364-1892
J1254
Jack Hammer
$25.00
C0012
Cement Mixer
$15.00
100598
02/10/04
10534
Joe Brown
555-234-1212
S6345
Circular Saw
$10.00
100603
02/11/04
11937
Jill Jones
555-364-1892
S5489
Jig Saw
$5.00
100658
02/17/04
11321
Bob Hicks
555-231-7856
C0012
Cement Mixer
$15.00
100659
02/17/04
11321
Bob Hicks
555-231-7856
J1254
Jack Hammer
$25.00
Case Questions
1. Define the term “repeating group” and identify the attributes in the repeating group from the table above.
2. Convert to 1NF – Draw a dependency diagram showing the primary key attribute(s) and identifying all dependencies.
3. Convert to 2NF – Remove all partial dependencies and show the resulting table structure(s). You may use dependency diagrams or shorthand table structures (see page 125 in the text).
4. Convert to 3NF – Remove all transitive dependencies and show the resulting table structure(s). You may use dependency diagrams or shorthand table structures (see page 125 in the text).
5. Using the results of Question 4, draw a complete Crow’s Foot ERD.
Requirements: