Prof tirup parmar video lectures

Prof. Tirup Parmar
Video lectures - Page 80
Why do you need a foreign key?
Suppose an idiot inserts a record in Table B such as
You will only be able to insert values into your foreign key that exist in the unique key in the parent table.
This helps in referential integrity.

Prof. Tirup Parmar
Video lectures - Page 81
The above problem can be overcome by declaring membership id from Table2 as foreign key of
membership id from Table1
Now, if somebody tries to insert a value in the membership id field that does not exist in the parent table,
an error will be shown!
What are transitive functional dependencies?
A transitive functional dependency is when changing a non-key column, might cause any of the other
non-key columns to change
Consider the table 1. Changing the non-key column Full Name may change Salutation.
Let's move into 3NF

Prof. Tirup Parmar
Video lectures - Page 82
3NF (Third Normal Form) Rules

Rule 1- Be in 2NF

Rule 2- Has no transitive functional dependencies
To move our 2NF table into 3NF, we again need to again divide our table.
3NF Example
TABLE 1
Table 2
Table 3
We have again divided our tables and created a new table which stores Salutations.
There are no transitive functional dependencies, and hence our table is in 3NF
In Table 3 Salutation ID is primary key, and in Table 1 Salutation ID is foreign to primary key in
Table 3
Now our little example is at a level that cannot further be decomposed to attain higher forms of
normalization. In fact, it is already in higher normalization forms. Separate efforts for moving

Prof. Tirup Parmar
Video lectures - Page 83
into next levels of normalizing data are normally needed in complex databases. However, we
will be discussing next levels of normalizations in brief in the following.
Boyce-Codd Normal Form (BCNF)
Even when a database is in 3 rd Normal Form, still there would be anomalies resulted if it has
more than one Candidate Key.
Sometimes is BCNF is also referred as 3.5 Normal Form.
4NF (Fourth Normal Form) Rules
If no database table instance contains two or more, independent and multivalued data describing
the relevant entity, then it is in 4 th Normal Form.
5NF (Fifth Normal Form) Rules
A table is in 5
th
Normal Form only if it is in 4NF and it cannot be decomposed into any number
of smaller tables without loss of data.
6NF (Sixth Normal Form) Proposed
6
th
Normal Form is not standardized, yet however, it is being discussed by database experts for
some time. Hopefully, we would have a clear & standardized definition for 6 th Normal Form in
the near future...
That's all to Normalization!!!
Summary

Database designing is critical to the successful implementation of a database management
system that meets the data requirements of an enterprise system.