The Happy Valley Food Coop (HVFC) keeps a database in which its members’ balance
ID: 3704083 • Letter: T
Question
The Happy Valley Food Coop (HVFC) keeps a database in which its members’ balance, their orders and possible suppliers and prices are recorded. The tables below show some sample data. Table (a) gives data about members; Table (b) shows the ORDERS relationship and (c) gives data about suppliers, their addresses and the prices they charge for the items they are willing to supply.
NAME
ADDRESS
Item
Quantity
BALANCE
Brooks, B.
7 Apple Rd.
Granola
5
+10.5
Unbleached flour
10
Field, W.
43 Cherry Lane
NULL
NULL
0
Robin, R
12 Banana Cove
Granola
3
-123.45
Sunflower Seeds
2
Lettuce
8
Hart, W.
65 Peach St
Whey
5
-43.00
Members Table
SName
SAddress
ITEM
Price(per pound)
Sunshine Produce
16 River St
Granola
1.29
Lettuce
.89
Sunflower Seeds
1.09
Purity Foodstuffs
180 Sea Wall
Whey
.70
Curds
.80
Granola
1.25
Unbleached Flour
.65
Tasti Supply Co.
17 River St.
Lettuce
.79
Whey
.79
Sunflower Seeds
1.19
Supplier Table
Normalize the tables in the above database
Please answer Clearly and Legible
NAME
ADDRESS
Item
Quantity
BALANCE
Brooks, B.
7 Apple Rd.
Granola
5
+10.5
Unbleached flour
10
Field, W.
43 Cherry Lane
NULL
NULL
0
Robin, R
12 Banana Cove
Granola
3
-123.45
Sunflower Seeds
2
Lettuce
8
Hart, W.
65 Peach St
Whey
5
-43.00
Explanation / Answer
The given tables has been converted to 3rd normal form. A table is said to be in 3rd normal form if-
1. All records in the table should be atomic and table contains unique value. (1 NF)
2. Table should not have any partial dependency. (2 NF)
3. Table should not have any transitive dependency.
The normalized data based contains 4 tables Member, Supplier, Order, Supply. No assumption has been made to convert tables to 3 NF.
Table- Member
Name
Address
Balance
Brooks B.
7 Apple Rd
+10.05
Field, W.
43 Cherry Lane
0
Robin R
12 Banana Love
-123.45
Table- Supplier
SName
SAddress
Sunshine Produce
16 River St
Purity Food Stuffs
180 Sea Wall
Tasti Supply Co
17 River St
Table - Supply
Item
Price
SName
Granola
1.29
Sunshine Produce
Lettuce
0.89
Sunshine Produce
Sunflower Seed
1.09
Sunshine Produce
Whey
0.70
Purity Food Stuffs
Curd
0.80
Purity Food Stuffs
Granola
1.25
Purity Food Stuffs
Unbleached Flower
0.65
Purity Food Stuffs
Lettuce
0.79
Tasti Supply Co
Whey
0.79
Tasti Supply Co
Sunflower Seed
1.19
Tasti Supply Co
Table – Order
Item
Quantity
MemberName
Granola
5
Brooks B.
Unbleached Flour.
10
Brooks B.
Granola
3
Robin R
Sunflower Seed
2
Robin R
Lettuce
8
Robin R
Name
Address
Balance
Brooks B.
7 Apple Rd
+10.05
Field, W.
43 Cherry Lane
0
Robin R
12 Banana Love
-123.45
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.