FacultyID: A005 Dept_ID: 35
Faculty_name: FSIT Dep_name: SWE
Faculty_Location: Knowledge Tower Dept_Location: Knowledge Tower
Dept_Floor :6th, 7th
Tec_id Tec_name Tec_Hire Course_Code
389 Nusrat Tasnim 01-01-2023 SE231
447 Mujahidul Islam 01-01-2024 SE111
489 Suprovo Sarkar 01-01-2024 SE121
Step 1:
First, we need to create a unnormalized table from the above table. The complete table is given below.
FacultyID Faculty_name Faculty_Location Dept_ID Dep_name Dept_Location Dept_Floor Tec_id Tec_name Tec_Hire Course_Code
In the above table it is shown in unnormalized form, to normalized the table from 1NF to BCNF is given below:
1NF form:
The rules for 1NF are,
1. There are only Single Valued Attributes.
2. Attribute Domain does not change.
3. There is a unique name for every Attribute/Column.
4. The order in which data is stored does not matter.
After following these rules the 1NF table will be,
DIU
FacultyID Faculty_name Faculty_Location Dept_ID Dep_name Dept_Location Dept_Floor Tec_id Tec_name Tec_Hire Course_Code
As we are only showing the attribute for the DIU table, we can say that the first the table is in 1NF form as we can see that if follows all the 1NF
rules. The above table attribute domain does not change, and each attribute has unique name. So the above table is in 1NF form.
2NF:
To make the table to 2NF we need to follow few rules. They are,
1. In the 2NF, relational must be in 1NF.
2. In the second normal form, all non-key attributes are fully functional dependent on the primary key.
DIU
FacultyID Faculty_name Faculty_Location Dept_ID Dep_name Dept_Location Dept_Floor Tec_id Tec_name Tec_Hire Course_Code
The above table is already in 1NF, as it follows all the rules that 1NF has. Now, we need to check for partial dependency. A partial dependency
would occur whenever a non-prime attribute depends functionally on a part of the given candidate key.
In the DIU table, FacultyID, Dept_ID, Tec_id is candidate key for the DIU table. That means, we can find faculty information using FacultyID,
Teacher information using Tec_id, and department information using Dept_ID. Thus, it shows that the DIU table has partial dependency. To
remove the partial dependency, we divide the DIU table into smaller tables.
Faculty
FacultyID Faculty_name Faculty_Location Dept_ID (FK)
(PK)
Department
Dept_ID(PK) Dep_name Dept_Location Dept_Floor Tec_id(FK)
Teacher
Tec_id(PK) Tec_name Tec_Hire Course_code(FK)
Course
Course_code(PK) Tec_id(FK)
In the above table, we have divided the DIU table into three different tables. Faculty, Department, Teacher table. Now if we check then we can see
that not a single table has any partial dependency. So we can say that the table is in second normalized form.
3NF:
To make the table 3NF we need to follow few rules, and they are
1. A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency.
2. 3NF is used to reduce the data duplication. It is also used to achieve the data integrity.
3. If there is no transitive dependency for non-prime attributes, then the relation must be in third normal form.
Here, a transitive dependency is when a non-primary attribute is dependent on another non primary key attribute then it is transitive dependency.
But if we see closely to our 2NF table, there are no transitive dependency. So we can say the table are already in 3NF.
Faculty
FacultyID Faculty_name Faculty_Location Dept_ID (FK)
(PK)
Department
Dept_ID(PK) Dep_name Dept_Location Dept_Floor Tec_id(FK)
Teacher
Tec_id(PK) Tec_name Tec_Hire Course_code(FK)
Course
Course_code(PK) Tec_id(FK)
So, we do not need to make any changes to our table.
BCNF:
To make the table BCNF we need to follow few rules, and they are
1. BCNF is the advance version of 3NF. It is stricter than 3NF.
2. A table is in BCNF if every functional dependency X → Y, X is the super key of the table.
3. For BCNF, the table should be in 3NF, and for every FD, LHS is super key.
FacultyID→ Faculty_name, Faculty_location
Dept_ID→ Dept_name, Dept_Location, Dept_Floor
Tec_id→ Tec_name, Tec_Hire
Course_code→ Tec_id
Faculty
FacultyID Faculty_name Faculty_Location Dept_ID (FK)
(PK)
Department
Dept_ID(PK) Dep_name Dept_Location Dept_Floor Tec_id(FK)
Teacher
Tec_id(PK) Tec_name Tec_Hire Course_code(FK)
Course
Course_code(PK) Tec_id(FK)
In the course table, we can see that BCNF requires that for any functional dependency 𝑋→𝑌, 𝑋 must be a superkey. The functional dependency
can be presented here in Course table. In the functional dependency Course_code -> Tec_id, Course_code is a candidate key, thus a superkey.
Here, each of them satisfies the BCNF condition. So there is no need to do BCNF.
Thus, this will be the final normalized form of the DIU table. It shows 1NF to BCNF of the DIU Table.