Third Normal Form (3NF)

Question

I have these two tables.

If I make sure that they are in 3NF is this the correct way? My answer:

StaffDetails(StaffID, SName, DOB, DivisionNo*)
Division(DivisionNo, DivName, DivSupervisorID)
StaffProject(StaffId*, ProjectNo*, SName, ProjectName, HoursAssigned)
Project(ProjectNo, ProjectName)

The primary keys are bolded but what is the reason behind an asterisk?

Answer 1

No, its not quite 3NF as you duplicate SName and StaffProject in the StaffProject table.

The tables should probably be:

StaffDetails

Column	Data Type	Constraints
StaffDetails	VARCHAR2	Primary Key
SName	VARCHAR2
DOB	DATE
DivisionNo	INT	Foreign Key(Division)

Division

Column	Data Type	Constraints
DivisionNo	INT	Primary Key
DivName	VARCHAR2	(Probably Unique)
DivSupervisorID	VARCHAR2	Foreign Key(StaffDetails)

StaffProject

Column	Data Type	Constraints
StaffID	VARCHAR2	Composite Primary Key, Foreign Key(StaffDetails)
ProjectNo	VARCHAR2	Composite Primary Key, Foreign Key(Project)
HoursAssigned	NUMBER	Check > 0

Project

Column	Data Type	Constraints
ProjectNo	INT	Primary Key
ProjectName	VARCHAR2	(Probably unique)

---

As for your notation questions, that is a convention your tutor appears to be using but is not necessarily globally recognised so you would be best asking them; however it appears that primary keys are underlined and foreign keys have an asterix next to them.

StaffProject would have columns that are both part of a composite primary key and a foreign key so would have both notations.

Answer 2

A column or columns on the primary key of a table can also be a foreign key of another one.

In your example it seems that:

• (StaffId, ProjectNo) is the PK of StaffProject.
• StaffId is also an FK against the table StaffDetails; that's why it has an asterisk.
• ProjectNo is also an FK against the table Project; that's why it has an asterisk on itself.

If you want your model to be 3NF you'll need to remove extra dependencies:

• In this case DivName and DivSupervisorID are redundant and should be removed from the table StaffDetail.
• ProjectName and SName are also redundant in StaffProject. You must remove them for the model to be compliant with 3NF.