Question

Select a Database of your own choice and apply the first three normalization processes. (1NF, 2NF & 3NF)

• The minimum number of records in the table must be 10
• Make sure you carry the same table from 1NF to 2NF and 3NF, do not use separate tables from scratch for all the three forms.
• Brief explanation of the normalization processes must be specified

Answer 1

is a database design technique that reduces data redundancy and eliminates undesirable characteristics like deletion, update and insert anomaies. it turns the table into smalle one and binds them with relations.The main purpose is to remove redundancy and ensure data is stored logically.

Assume we have a video library maintains a databse of movies rented out without any normalization.

all 3 normalization will performed in this.

FULL NAME	PHYSICAL ADDRESS	MOVIES RENTED	SALUTATION
Janet jones	First street plot no 4	pirates of the carribian,clash of titans	Ms.
Robert fill	3rd street 34	forgetting sarah marshal,daddys little girl	Mr.
Robert fill	5th avenue	Clash of titans	Mr.
Siddhu	6th avenue	so high	Mr.
Bohimea	7th avenue	jaguar	Mr.
Jaura	8th avenue	sarkar	Mr.
Deep	10th avenue	bombay	Mr.
Divine	11th avenue	kohinoor	Mr.
Bhantai	12th avenue	bhut hard	Mr.
Raftar	13th avenue	baby	Mr.

Rules-

1. each table cell should contain a single value.
2. each records need to be unique.

so the 1nf of the above table will be

FULL NAME	PHYSICAL ADDRESS	MOVIES RENTED	SALUTATION
Janet jones	First street plot no 4	pirates of the carribian	Ms.
Janet jones	First street plot no 4	,clash of titans	Ms.
Robert fill	3rd street 34	Forgetting sarah marshal	Mr.
Robert fill	3rd street 34	daddys little girl	Mr.
Robert fill	5th avenue	Clash of titans	Mr.
Siddhu	6th avenue	so high	Mr.
Bohimea	7th avenue	jaguar	Mr.
Jaura	8th avenue	sarkar	Mr.
Deep	10th avenue	bombay	Mr.
Divine	11th avenue	kohinoor	Mr.
Bhantai	12th avenue	bhut hard	Mr.
Raftar	13th avenue	baby	Mr.

Rules-

1. table should be in 1 nf.
2. single coloumn primary key.

A primary is a single column value used to identify a database record uniquely.

it is not possible to make above tabe in 2nf without partitoning it.

MEMBERSHIP ID	FULL NAMES	PHYSICAL ADDRESS	SALUTATION
1	Janet jones	first street plot no 4	Ms.
2	Robert fill	3RD STREET 34	Mr.
3	Robert fill	5th avenue	Mr.
4	Siddhu	6th avenue	Mr.
5	Bohimea	7th avenue	Mr.
6	Jaura	8th avenue	Mr.
7	Deep	9th avenue	Mr.
8	Divine	10th avenue	Mr.
9	Bhantai	11th avenue	Mr.
10	Raftar	12th avenue	Mr.

MEMBERSHIP ID	MOVIES RENTED
1	pirates of the carribian
2	clash of titans
3	Forgetting sarah marshal
4	daddys little girl
5	Clash of titans
6	so high
7	jaguar
8	sarkar
9	bombay
10	kohinoor
11	bhut hard

We have introduced a new column called Membership_id which is the primary key for table 1. Records can be uniquely identified in Table 1 using membership id,

Rules-

1. be in 2nf
2. Has no 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

this time also , we need to divide the table into multiple tables.

FULL NAME	PHYSICAL ADDRESS	MOVIES RENTED	SALUTATION
Janet jones	First street plot no 4	pirates of the carribian	2
Janet jones	First street plot no 4	,clash of titans	2
Robert fill	3rd street 34	Forgetting sarah marshal	1
Robert fill	3rd street 34	daddys little girl	1
Robert fill	5th avenue	Clash of titans	1
Siddhu	6th avenue	so high	1
Bohimea	7th avenue	jaguar	1
Jaura	8th avenue	sarkar	1
Deep	10th avenue	bombay	1
Divine	11th avenue	kohinoor	1
Bhantai	12th avenue	bhut hard	1
Raftar	13th avenue	baby	1

MEMBERSHIP ID	MOVIES RENTED
1	pirates of the carribian
2	clash of titans
3	Forgetting sarah marshal
4	daddys little girl
5	Clash of titans
6	so high
7	jaguar
8	sarkar
9	bombay
10	kohinoor
11	bhut hard

SALUTATION ID	SALUTATION
1	Mr
2	Ms
3	Mrs
5	Dr

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.