Database Normalization

primary key: A column uniquely identify any record.

foreign key:A column of a table that is used as primary key to another table and thus make a link between them.

Table Name - Users:

UserID UserName UserRoleID(foreign key)

1 JohnD 1

2 CourtneyC 1

3 Benjamin 2

Table Name - UserRoles

UserRoleID Desc

1 Admin

2 Moderator

composite primary key:More than one column of a table work as primary key.

non-prime(non-key):যে attribute/column কোনও candidate key এর অংশ নয় তাকে non-prime attribute অথবা non-key attribute বলে।

as easy as childhood

Normalization:To avoid redundancy and maintain integrity we do normalization.It simply means to make a table normal without redundancy and integrity.

Redundancy defines that existence of repetition of the same data and integrity makes sure that everywhere in the database we get the same result for the same scenario.If a students total mark is 384,no matter from which tables or method we apply to our tables the mark have to be 384.

first-form normalization(1NF):

->every column must hold atomic values means not more than one value.

2NF:

->do 1NF

->Every non-prime or non key must be fully dependent on the composite primary key(if any).If there is a column or columns that depends on a subset of the composite primary key they should be seperated into another table with the subset of primary key which will act as a foreign key in the first table.

(https://medium.com/omarelgabrys-blog/database-normalization-part-7-ef7225150c7f)

3NF:

->do 2NF

->There should be no transitive functional dependency between the non-prime keys. It simply means that if after second normalization form we find that there is a column or columns that have a dependency on a non prime key which is not the primary key(second table which is seperated in 2NF) we again seperate them. In this case the non prime key on which the other non primes dependent, act as a foreign key.

(note in 3NF we also remove the derived columns ie:age/birthdate cases)

Boyce–Codd normal form:

Also known as 3.5NF which is a updated stronger version of 3NF. It removes the errors that are not removed in 3NF in rare cases.In BCNF it is guaranteed that all redundancy based on functional dependency has been removed.