DATABASE NORMALIZATION

If you have any queries please leave a message here
Your Message
×


Primary Key - A primary key is a column (or columns) in a table that uniquely identifies the rows in that table.
For example -
Employee(Employee_id, Employee_name, Department_id)
In this table Emp_id is the primary key.
The value placed in primary key columns must be unique for each row : no duplicates can be tolerated. In addition, nulls are not allowed in primary key columns.

Foreign Key - Foreign keys are columns that point to primary key columns.
For example -
Employee(Employee_id, Employee_name, Department_id)
Department(Department_id, Department_name, Total_Employee)
Employee_id
is the primary key of the table Employee and Department_id is the primary key of the table Department but in Employee table Department_id is the foreign key that points to the primary key in the Department table.




FIRST NORMAL FORM (1NF)

Example -

Table Name : Office
Department_idDepartment_nameEmployee_idEmployee_nameSalary
D-100PHPE-1001
E-1005
E-1008
Mr. A
Mr. B
Mr. C
30000
28000
25000
D-200JAVAE-1002
E-1004
E-1006
Mr. X
Mr. Y
Mr. Z
25000
30000
20000
D-300AdminE-1003
E-1007
Mr. D
Mr. E
20000
30000

Table "office" not in first normal form, because in this table many data field contain multiple values, so it is require to convert into first normal form.

Table Name : Office
Department_idDepartment_nameEmployee_idEmployee_nameSalary
D-100PHPE-1001Mr. A30000
D-100PHPE-1005Mr. B28000
D-100PHPE-1008Mr. C25000
D-200JAVAE-1002Mr. X25000
D-200JAVAE-1004Mr. Y30000
D-200JAVAE-1006Mr. Z20000
D-300AdminE-1003Mr. D20000
D-300AdminE-1007Mr. E30000




SECOND NORMAL FORM (2NF)

A relation is in 2NF if it is in 1NF and every non-key attribute is fully dependent on each candidate key of the relation.
That means in second normal form each table have only one entity which uniquely identify other entities. This particular entity contain only primary key value. In another way we can say that if there is more than one primary key then the table is required to convert into second normal form.

Example -
The "Office" table which shown in First Normal Form is require to convert into Second Normal Form.

Functional Dependecy in "Office" Table
(Department_id, Employee_id) → (Department_name, Employee_name, Salary)

Partial Dependecy in "Office" Table
Department_id → Department_name
Employee_id → (Employee_name, Salary)

After 2NF the "Office" table is divided into two tables which are :

Table Name : Employee
Employee_idEmployee_nameSalaryDepartment_id
E-1001Mr. A30000D-100
E-1005Mr. B28000D-100
E-1008Mr. C25000D-100
E-1002Mr. X25000D-200
E-1004Mr. Y30000D-200
E-1006Mr. Z20000D-200
E-1003Mr. D20000D-300
E-1007Mr. E30000D-300

Table Name : Department
Department_idDepartment_name
D-100PHP
D-200JAVA
D-300Admin




THIRD NORMAL FORM (3NF)

A relation is in third normal form if it is in 2NF and every non-key attribute of the relation is non-transitively dependent on each candidate key of the relation.

Example -
Library(Book_id, Book_name, Author_name, Bookshelf_number, Book_category)

Functional Dependecy
book_id → (Book_name, Author_name, Bookshelf_number, Book_category)

Transitive Dependecy
Bookshelf_number → Book_category

Table Name : Library
Book_idBook_nameAuthor_nameBookshelf_numberBook_category
B-100
Hacking Sectrets ExposedSrikanth Ramesh
10
Hacking
B-105
The Complete Reference C++Herbert Schildt
30
C and C++
B-200
Linux Shell Scripting with BashKen O. Burtch
40
Linux
B-250
The Basics of Web HackingJosh Pauli
10
Hacking
B-350
Database System ConceptsSilberschatz Korth Sudarshan
20
DBMS
B-480
Shell ScriptingSteven Parker
40
Linux
B-610
The Complete Reference JavaHerbert Schildt
50
JAVA
B-750
Penetration Testing with the Bash ShellKeith Makan
40
Linux

Table "Library" not in third normal form, because a transitive dependency present in this table.

Table Name : Book
Book_idBook_nameAuthor_nameBookshelf_number
B-100
Hacking Sectrets ExposedSrikanth Ramesh
10
B-105
The Complete Reference C++Herbert Schildt
30
B-200
Linux Shell Scripting with BashKen O. Burtch
40
B-250
The Basics of Web HackingJosh Pauli
10
B-350
Database System ConceptsSilberschatz Korth Sudarshan
20
B-480
Shell ScriptingSteven Parker
40
B-610
The Complete Reference JavaHerbert Schildt
50
B-750
Penetration Testing with the Bash ShellKeith Makan
40

Table Name : Bookshelf
Bookshelf_numberBook_category
10
Hacking
20
DBMS
30
C and C++
40
Linux
50
JAVA




BOYCE CODE NORMAL FORM (BCNF)

A table is in BCNF when every determinant in the table is a candidate key. Clearly when a table contains only one candidate key the 3NF and the BCNF are equivalent. Putting that proposition another way, BCNF can be violated only when the table contains more than one candidate key.

Notice that this structure has two candidate keys : (A,B) and (A,C). The table structure shown in above figure has no partial dependencies, nor does it contain transitive dependencies. (The condition C → B indicated that a non-key attribute determines part of the primary key - and that dependency is not transitive or partial because the dependent is a prime attribute!). Thus the table structure in the above figure is in 3NF but not in BCNF.



Example -
Class_Test(Student_id, Professor_id, Class_code, Student_grade)

Functional Dependecy
(Student_id, Professor_id) → (Class_code, Student_grade)
Class_code → Professor_id>

Table Name : Class_Test
Student_idProfessor_idClass_codeStudent_grade
S-10001P-100C-1001A
S-10001P-300C-1002B
S-10002P-100C-1001C
S-10002P-200C-1003C
S-10003P-100C-1005A
S-10004P-100C-1001B
S-10004P-200C-1003A
S-10005P-100C-1004A


The table reflects the following conditions :

Table Name : Grade
Student_idClass_codeStudent_grade
S-10001C-1001A
S-10001C-1002B
S-10002C-1001C
S-10002C-1003C
S-10003C-1005A
S-10004C-1001B
S-10004C-1003A
S-10005C-1004A

Table Name : Class
Class_codeProfessor_id
C-1001P-100
C-1002P-300
C-1003P-200
C-1004P-100
C-1005P-100




FOURTH NORMAL FORM (4NF)

Fourth Normal Form is related to Multi-value Dependency. Under fourth normal form, a record type should not contain two or more independent multi-value facts about an entity. In addition the record must satisfy third normal form.

A multi-value dependency exists when

If a table in 4NF then -

Example -
Course(Course_code, Professor, Reference_book)

Multivalue Dependency
Course_code → → Professor
Course_code → → Reference_book

Table Name : Course
Course_codeProfessorReference_book
C-100
Mr. X
The TCP/IP Guide
(Charles M. Kozierok)
C-100
Mr. Y
The TCP/IP Guide
(Charles M. Kozierok)
C-200
Mr. A
Linux Shell Scripting with Bash
(Ken O. Burtch)
C-200
Mr. A
Shell Scripting
(Steven Parker)
C-200
Mr. A
Penetration Testing with the Bash Shell
(Keith Makan)
C-200
Mr. B
Linux Shell Scripting with Bash
(Ken O. Burtch)
C-300
Mr. C
Hacking Sectrets Exposed
(Srikanth Ramesh)
C-300
Mr. D
Hacking Sectrets Exposed
(Srikanth Ramesh)
C-300
Mr. D
The Basics of Web Hacking
(Josh Pauli)
C-400
Mr. E
Database System Concepts
(Silberschatz Korth Sudarshan)

The table reflects the following conditions :

Table Name : Instructor
Course_codeProfessor
C-100Mr. X
C-100Mr. Y
C-200Mr. A
C-200Mr. B
C-300Mr. C
C-300Mr. D
C-400Mr. E

Table Name : Textbook
Course_codeReference_book
C-100
The TCP/IP Guide
(Charles M. Kozierok)
C-200
Linux Shell Scripting with Bash
(Ken O. Burtch)
C-200
Shell Scripting
(Steven Parker)
C-200
Penetration Testing with the Bash Shell
(Keith Makan)
C-300
Hacking Sectrets Exposed
(Srikanth Ramesh)
C-300
The Basics of Web Hacking
(Josh Pauli)
C-400
Database System Concepts
(Silberschatz Korth Sudarshan)




FIFTH NORMAL FORM (5NF)

A table is in Fifth Normal Form (5NF) or Project-Join Normal Form (PJNF) if it is in 4NF and it can not have a lossless decomposition into any number of smaller tables.
The fifth normal form deals with join-dependencies, which is a generalisation of the multi-value dependency.

Table Name : Project_details
Employee_idProject_nameprogramming_Language
E-100GoogleLaravel
E-100FacebookPHP
E-200FacebookPHP
E-200GoogleCakePHP
E-200YahooCakePHP
E-300YahooLaravel


Table Name : Project
Employee_idProject_name
E-100Google
E-100Facebook
E-200Facebook
E-200Google
E-200Yahoo
E-300Yahoo

Table Name : Language
Employee_idprogramming_Language
E-100Laravel
E-100PHP
E-200PHP
E-200CakePHP
E-300Laravel

Table Name : Project_Language
Project_nameprogramming_Language
GoogleLaravel
FacebookPHP
GoogleCakePHP
YahooCakePHP
YahooLaravel


ABOUT US

QuestionSolves.com is an educational website that helps worldwide students in solving computer education related queries.

Also, different software like Visual Studio, SQL Server, Oracle etc. are available to download in different versions.

Moreover, QuestionSolves.com provides solutions to your questions and assignments also.


MORE TOPIC


Windows Command

UNIX Command

IGNOU Assignment Solution

IGNOU Question Paper Solution

Solutions of Different Questions


WHAT WE DO


Website Devlopment

Training

Home Learning

Provide BCA, MCA Projects

Provide Assignment & Question Paper Solution


CONTACT US


Follow Us