Thread: Help needed: Normalizing 1NF to 2NF,3NF with functional dependencies

1. Registered User
Join Date
Jan 2014
Posts
1

Help needed: Normalizing 1NF to 2NF,3NF with functional dependencies

Hello guys,

I have translated a document for a "cleaning company" to UNF and then to 1NF with atomic values (attached with this post)

I have identified a "Superkey" for 1NF: building, apartment, day, cNumber
I think this is the minimal set required for identifying a unique tupel
building, apartment, day, cNumber -> sNumber, Sname, cName, hourlyWage

sNumber,Sname is the supervisor/responsible for the cleaners
cNumber, cName stands for cleanerNumber,cleanerName

Functional dependencies:
FD1: sNumber -> sName (name can be dupe)
FD2: cNumber -> cName (name can be dupe)
FD3: building -> sNumber
FD4: cNumber, building, day -> hourlyWage (unsure about this one, there are som dupes, 5,B,Friday and 5,B,Tuesday all leads to 10.7 in hourlyWage)

2NF:
I can only use the attributes + FD that is building, apartment, day, cNumber?
FD2: cNumber -> cName
FD3: building -> sNumber
FD4: cNumber, building, day -> hourlyWage

Example:

2NF_Cleaners:
cNumber, cName

1: Anette Torres
3: Richard Howard
4: Louise Lewis
5: John Toney
6: Carol Connel
7: Janet Hagan

2NF_Responsible
building, sNumber, sName

A:2:Lillian Weber
B:5:Billy Collins
C:5:Billy Collins
D:7:Lillian Weber

2NF_Wage
cNumber, building, day, hourlyWage
1:A:11:Friday:10.3
.... (cut to save space)
Where cNumber is referenced to 2NF_Cleaners and building is referenced from 2NF_Responsible

How would you design the relations so they are in 2NF and 3NF? i have tried to solve this one for about 2 weeks

Thanks

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
I'd suggest that you start with Marc Rettig's Normalization Poster, then post any specific questions that you have. We'll help you with specific questions like "I could split the widgets entity into sprockets and dinguses, or I could split widgets into multiple thingies with timestamps... Which of those meets 3NF?" but its very unlikely that anyone at DBForums will take your design from 2NF to 3NF for you.

-PatP

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•