I have following relation. A company has several employees. Each employee is defined by its employee number ENr and he is living on an address EAddress with a ZipCode ZZipCode. The City with the ZipCode is an own table because otherwise there is redundancy in table Employee. Therefore ZZipCode is a foreign key in Employee.
A Group is defined by its GGroupId, therefore that is the primary key. Each group has one group leader which can be any employee. Therefore ENr is a foreign key.
Each employee can work on none, one or more groups. For this reason the table GroupMember exists where the the tuple ENr and GGroupID define the primary key and both are foreign keys (I cannot do both, bold and italic).
And last, a product is defined by its product id PId and is associated to a group GGroupID.
Well here are the relations for that written description.
Employe(ENr, EName, EGender, EAddress, ZZipCode, ESocNr, ESalery)
Group(GGroupId, GName, GCostNr, ENr)
GroupMember(ENr, GGroupID) #both members are foreign keys too!
Product(PId, PName, PPrice, GGRoupId)
Zip(ZZipCode, ZCityName, SStateID)
State(SStateID, SStateName)
For clarification: bold members are primary keys and italic members are foreign keys.
I tried to put that relation into 3NF. Can anyone confirm that this is right?
This seems to be good and normalised. I dont see any further division of the tables.