INFO20003 Database Systems
Dr Renata Borovica-Gajic
Lecture 16 Hands-on Normalization
Week 8
Example #1
Report (reportNo, editor, deptNo, deptName, deptAddr, authorId, authName, authAddr)
reportNo → editor, deptNo deptNo → deptName, deptAddr authorId → authName, authAddr
reportNo
editor
deptNo
deptName
deptAddr
authorId
authName
authAddr
4216
woolf
15
design
argus1
53
mantel
cs-tor
4216
woolf
15
design
argus1
44
bolton
mathrev
4216
woolf
15
design
argus1
71
koenig
mathrev
5789
koenig
27
analysis
argus2
26
fry
folkstone
5789
koenig
27
analysis
argus2
38
umar
prise
5789
koenig
27
analysis
argus2
71
koenig
mathrev
• Is the Report table in 2NF? If not, put the table in 2NF.
• Are there any insert, update or delete anomalies with these 2NF relations?
INFO20003 Database Systems © University of Melbourne 2
Example #2
Class (courseNumber, roomNumber, instructorName, studentNumber, workshopNumber, grade, tutor)
workshopNumber → tutor
studentNumber, courseNumber → grade, workshopNumber courseNumber → roomNumber, instructorName
• Normalise the relation into 3NF.
INFO20003 Database Systems © University of Melbourne 3
Example #3
OrderItem
CustomerID → CustomerPostcode
OrderID → CustomerID
OrderID, ItemID → ItemQuantity, CanDispatchFrom
• Normalize the relation to 3NF.
OrderID
ItemID
CustomerID
CustomerPostcode
ItemQuantity
CanDispatchFrom
4018
161
191
3053
6
Truganina, Hallam
4022
228
196
3212
1
Somerton
4033
525
25
3124
2
Somerton, Hallam
INFO20003 Database Systems © University of Melbourne 4
Next Lecture
• Transactions
INFO20003 Database Systems © University of Melbourne 5 —