CS3402 Database Systems Tutorials
CS3402 Tutorial 4:
1. Examine the table shown below.
Branch
(a) Why this table is not in 1NF?
(b) Describe and illustrate the process of normalizing the data shown in this table
to third normal form (3NF).
Answer:
(a) TelNo is not an attribute with atomic values, but with multi-values. So, the
table is NOT in 1NF.
(b) Create another relation specifically for TelNo with BranchNo as a foreign key
Branch
BranchTel
Branch No
BranchAddress
TelNo
B001
8 Jefferson Way, Portland, OR 97201
503-555-3618, 503-555-2727, 503-555-6534
B002
City Center Plaza, Seattle, WA 98122
206-555-6756, 206-555-8836
B003
14 – 8th Avenue, New York, NY 10012
212-371-3000
B004
16 – 14th Avenue, Seattle, WA 98128
206-555-3131, 206-555-4112
BranchNo
BranchAddress
B001
8 Jefferson Way, Portland, OR 97201
B002
City Center Plaza, Seattle, WA 98122
B003
14 – 8th Avenue, New York, NY 10012
B004
16 – 14th Avenue, Seattle, WA 98128
BranchNo
TelNo
B001
503-555-3618
B001
503-555-2727
B001
503-555-6534
B002
206-555-6756
B002
206-555-8836
B003
212-371-3000
B004
206-555-3131
B004
206-555-4112
2. Examine the table shown below.
StaffBranchAllocation
StaffNo
BranchNo
BranchAddress
Name
Position
HoursPer
CS3402 Database Systems Tutorials
Week
S4555
B002
City Center Plaza, Seattle, WA 98122
Ellen Layman
Assistant
16
S4555
B004
16 – 14th Avenue, Seattle, WA 98128
Ellen Layman
Assistant
9
S4612
B002
City Center Plaza, Seattle, WA 98122
Dave Sinclair
Assistant
14
S4612
B004
16 – 14th Avenue, Seattle, WA 98128
Dave Sinclair
Assistant
10
(a) Why this table is not in 2NF?
(b) Describe and illustrate the process of normalizing the data shown in this table
to third normal form (3NF).
Answer:
(a) The primary key of StaffBranchAllocation table is
There exist the partial functional dependencies: StaffNo → Name, Position and BranchNo → BranchAddress. The non-key attributes are not fully dependent on the key. So, the table is NOT in 2NF.
(b) Remove BranchAddress, Name, Position from StaffBranchAllocation relation to capture the partial functional dependencies separately.
Branch
Staff
StaffBranchAllocation
3. Examine the table shown below.
BranchManager
BranchNo
BranchAddress
B002
City Center Plaza, Seattle, WA 98122
B004
16 – 14th Avenue, Seattle, WA 98128
StaffNo
Name
Position
S4555
Ellen Layman
Assistant
S4612
Dave Sinclair
Assistant
StaffNo
BranchNo
HoursPerWeek
S4555
B002
16
S4555
B004
9
S4612
B002
14
S4612
B004
10
CS3402 Database Systems Tutorials
Branch No
BranchAddress
TelNo
MgrStaff No
MgrName
B001
8 Jefferson Way, Portland, OR 97201
503-555-3618
S1500
Tom Daniels
B002
City Center Plaza, Seattle, WA 98122
206-555-6756
S0010
Mary Martinez
B003
14 – 8th Avenue, New York, NY 10012
212-371-3000
S0145
Art Peters
B004
16 – 14th Avenue, Seattle, WA 98128
206-555-3131
S2250
Sally Stern
(a) Why this table is not in 3NF?
(b) Describe and illustrate the process of normalizing the data shown in this table
to third normal form (3NF).
1. Answer:
(a) There exists a non-key attribute transitively dependent on the key, i.e.,
MgrName depends on MgrStaffNo and MgrStaffNo depends on BranchNo.
(b) Create another relation which specifically captures the dependency
MgrStaffNo → MgrName
Branch
ManagerStaff
BranchNo
BranchAddress
TelNo
MgrStaffNo
B001
8 Jefferson Way, Portland, OR 97201
503-555-3618
S1500
B002
City Center Plaza, Seattle, WA 98122
206-555-6756
S0010
B003
14 – 8th Avenue, New York, NY 10012
212-371-3000
S0145
B004
16 – 14th Avenue, Seattle, WA 98128
206-555-3131
S2250
MgrStaffNo
MgrName
S1500
Tom Daniels
S0010
Mary Martinez
S0145
Art Peters
S2250
Sally Stern
4. Examine the table shown below and the set of functional dependency on its attributes:
CourseRmAlloc (CourseId, CourseName, Year, Lecturer, Enrollment, RoomId, RoomCapacity, Day, Time)
FD = {CourseId -> CourseName, CourseId, Year -> Lecturer, RoomId -> RoomCapacity,
CourseName -> CourseId,
CourseId, Year -> Enrollment, RoomId, Year, Day, Time -> CourseId,
CS3402 Database Systems
Tutorials
CourseId, Year, Day, Time -> RoomId }
(a) Find all candidate keys of this table.
(b) Decompose this table into a design into BCNF. Answer:
(a) There are three candidate keys in this table: (Year, Day, Time, CourseId)
(Year, Day, Time, CourseName)
(Year, Day, Time, RoomId)
(b) This table can be decomposed into the following in BCNF (so also in 3NF): CourseTeaching (CourseId, Year, Lecturer, Enrollment)
Room (RoomId, RoomCapacity)
CourseRoomAlloc (CourseId, Year, Day, Time, RoomId)
Course (CourseId, CourseName)