Search code examples
databasedatabase-designdatabase-normalizationthird-normal-form

Third Normal Form (3NF)

I have these two tables.

table

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?

Solution

  • 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.