Case Description: Tom\'s Equipment Rental is a small company that rents equipmen

Question

Case Description: 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 Question:

Part 1.      Define the term "repeating group" and identify the attributes in the repeating group from the table above.

Part 2.      Convert to 1NF: Draw a dependency diagram showing the primary key attribute(s) and identifying all dependencies.

Part 3.      Convert to 2NF: Remove all partial dependencies and show the resulting table structure(s). You may use dependency diagrams or shorthand table structures.

Part 4.      Convert to 3NF: Remove all transitive dependencies and show the resulting table structure(s). You may use dependency diagrams or shorthand table structures.

Part 5.      Using the results of Question 4, draw a complete Crow's Foot ERD.

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

Explanation / Answer

Answer for Question:

There are 3 repeating groups are identified in given relation.

1. Agreement
2. Customer
3. Item

Agreement have the following attributes in it's relation
Agreement(Agee_no(primary key), Agee_date)

Customer have the following attributes in it's relation
Customer(cust_no(primary key), cust_name, cust_phone)

Item have the following attributes in it's relation
Item(Serial_no(primary key), item_name, item_rate)

After resolving the all the normal forms the resulting resolving are

Agreement and customer have the relation:
Customer_Agreement(cust_no(foreign key), Agee_no(foreign key)

Customer and Item have the relation
Customer_Item(cust_no(foreign key), Serial_no(foreign key))

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.