Following Question is related to Table Normalization and Normal Forms Is the fol
ID: 3575833 • Letter: F
Question
Following Question is related to Table Normalization and Normal Forms
Is the following Supplier-Part-Warehouse Table in 1NF, 2NF and in 3NF? Explain your answer. If not in 3NF then normalize (decompose) the table such that the decomposed tables are in 3NF and the decomposition should be a lossless join and depending preserving decomposition. Describe the meaning of lossless join and dependency preserving decomposition. (If necessary assume necessary FDs and mention it with your answer)
Supplier-Part-Warehouse Table
SID
SName
Scity
PID
PName
PColor
WID
WCity
PQty
S1
Sams
New York
P1
Car Seat cover
White
W1
New York
10
S1
Sams
New York
P2
Car Seat cover
Silver
W1
New York
20
S1
Sams
New York
P1
Car Seat cover
White
W2
Boston
15
S2
Nams
Boca Raton
P1
Car Seat cover
White
W5
Miami
25
S2
Nams
Boca Raton
P1
Car Seat cover
Yellow
W6
Orlando
30
S2
Nams
Boca Raton
P3
Car headlights
Silver
W20
Tampa
15
S3
Hans
Atlanta
P3
Car headlights
Silver
W25
Atlanta
21
Primary Key Fields: {SID, PID, WID}
Following table shows the Field Description of the various fields mentioned in the above "Supplier-Part-Warehouse" table
Field Name
Description
SID
Supplier Id
SNAME
Supplier name
SCity
Supplier's City
PID
Part Id
PNAME
Part Name
PColor
Part Color
PQty
Part's available Quantity
WID
Warehouse Id
WCity
City were the Warehouse is located
SID
SName
Scity
PID
PName
PColor
WID
WCity
PQty
S1
Sams
New York
P1
Car Seat cover
White
W1
New York
10
S1
Sams
New York
P2
Car Seat cover
Silver
W1
New York
20
S1
Sams
New York
P1
Car Seat cover
White
W2
Boston
15
S2
Nams
Boca Raton
P1
Car Seat cover
White
W5
Miami
25
S2
Nams
Boca Raton
P1
Car Seat cover
Yellow
W6
Orlando
30
S2
Nams
Boca Raton
P3
Car headlights
Silver
W20
Tampa
15
S3
Hans
Atlanta
P3
Car headlights
Silver
W25
Atlanta
21
Explanation / Answer
Supplier-Part-Warehouse table is in 1NF as there are atomic values in the table.
But the table is not in 2NF as the non key attributes are not fully functional dependent on the Primary Key Fields: {SID, PID, WID}. There are partial dependencies .
The functional dependencies are :
Tables in 3NF
underlined attributes are Primary keys of the corresponding tables.
Properties of decomposition:
1. Lossless Join Decomposition
By joining the three tables SUPPLIERS,PARTS and WAREHOUSES we should get the original table Supplier-Part-Warehouse.
ie SUPPLIERS join PARTS join WAREHOUSES = Supplier-Part-Warehouse
which holds true.
2. Dependency preserving property
All the decomposed tables should preserve all functional dependencies which is true here.
So our decomposed tables fulfill the properties of decomposition.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.