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).
2. Examine the table shown below.
StaffBranchAllocation
(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).
3. Examine the table shown below.
BranchManager
(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).
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
StaffNo
Branch No
BranchAddress
Name
Position
HoursPer 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
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
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,
CourseId, Year, Day, Time -> RoomId }
(a) Find all candidate keys of this table.
(b) Decompose this table into a design into BCNF.
CourseName -> CourseId,
CourseId, Year -> Enrollment, RoomId, Year, Day, Time -> CourseId,