Guys i have these two tables if i wanna make sure that they are in 3NF is this the correct way?
Also may i know the purpose of the primary key that has being underline but denoted with an asterisks as well, what is the reason behind that?
Table
My Answer
Third normal form
StaffDetails(StaffID,SName,DOB,DivisionNo*)
Division(DivisionNo,DivName,DivSupervisorID)
StaffProject(StaffId*, ProjectNo*,SName, ProjectName,HoursAssigned)
Project(ProjectNo,ProjectName)
Please assume that the attributes that has been bolded is underline. thank you so much.
CodePudding user response：
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.
CodePudding user response：
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. StaffIdis also an FK against the tableStaffDetails; that's why it has an asterisk.ProjectNois also an FK against the tableProject; 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
DivNameandDivSupervisorIDare redundant and should be removed from the tableStaffDetail. ProjectNameandSNameare also redundant inStaffProject. You must remove them for the model to be compliant with 3NF.