Normalization table and, Assignments of Database Management Systems (DBMS)

Normalization in the simplest term can be explained as technique used in database to reduce data redundancy. Insertion, Update and Deletion Anomalies which are considered as undesirable characteristics are also eliminated by normalization. The complex tables are broken down into simpler form by the process of normalization which are later linked using relationships. The sole purpose of using Normalization in SQL is to eliminate such data that are repetitive and make sure that the data is logical

Typology: Assignments

2020/2021

Uploaded on 04/21/2021

sun-grg
sun-grg 🇳🇵

5

(1)

5 documents

1 / 6

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Un-normalized table:
Book table
Book_id Genre Book_Name Author Price_of_book
(Rs.)
1 Novel The Tragic Muse
The Way It Came
Daisy Miller
Henry James 1500
1500
1500
2 Autobiography I Know Why the Caged
Birds Sing
And Still I Rise
Letter to My Daughter
Maya Angelou 1500
2000
2000
Table: Un-normalized Book table
As we can clearly see in the above given table that genre consist of more than one book and it is
an un-normalized table. For this example, I have made an assumption that the author for a book
is only one. For humans to understand the above table won’t be much of a big deal. If the look at
the above table, they will be able to read and understand the table as the book id of genre Novel
is one and it has three books all written by Author Henry James. Likewise, the book id of genre
Autobiography is 2 and has three books written by Author Maya Angelou. However, if a
machine happens to read the table, it will read and understand it as the book id of genre Novel is
1 and it only contains one book; The Tragic Muse and the author of the book is Henry James.
Because the corresponding values of the other rows are empty, it will be considered as null
values by the computer.
pf3
pf4
pf5

Partial preview of the text

Download Normalization table and and more Assignments Database Management Systems (DBMS) in PDF only on Docsity!

Un-normalized table:

Book table

Book_id Genre Book_Name Author Price_of_book (Rs.) 1 Novel The Tragic Muse The Way It Came Daisy Miller Henry James 1500 1500 1500 2 Autobiography I Know Why the Caged Birds Sing And Still I Rise Letter to My Daughter Maya Angelou 1500 2000 2000

Table: Un-normalized Book table

As we can clearly see in the above given table that genre consist of more than one book and it is

an un-normalized table. For this example, I have made an assumption that the author for a book

is only one. For humans to understand the above table won’t be much of a big deal. If the look at

the above table, they will be able to read and understand the table as the book id of genre Novel

is one and it has three books all written by Author Henry James. Likewise, the book id of genre

Autobiography is 2 and has three books written by Author Maya Angelou. However, if a

machine happens to read the table, it will read and understand it as the book id of genre Novel is

1 and it only contains one book; The Tragic Muse and the author of the book is Henry James.

Because the corresponding values of the other rows are empty, it will be considered as null

values by the computer.

We won’t be able to obtain the desired outcome if this problem arises in our program. Due to this

reason, we occasionally may not be able to get the information that are meant to be. This

problem can be solved by converting the above given table into First Normal Form.

1. First Normal Form (1NF)

First Normal Form, also known as 1NF should have a single value in each cell. The repeating

groups of an attribute in the same row are either removed or decomposed in different rows.

Should the values be same in some rows, those values must be given unique identification by

using ‘Primary Key’.

The table shown below is the first normal form of above un-normalized table:

Book_id Genre Book_Name Author Price_of_book (Rs.) 1 Novel The Tragic Muse Henry James 1500 1 Novel The Way It Came Henry James 1500 1 Novel Daisy Miller Henry James 1500 2 Autobiography I Know Why the Caged Birds Sing Maya Angelou 1500 2 Autobiography And Still I Rise Maya Angelou 2000 2 Autobiography Letter to My Daughter Maya Angelou 2000 Table: 1NF of Book table

2. Second Normal Form (2NF)

On the entire primary key, all the attributes of second normal form are dependent functionally. In

second normal form, the partial dependencies existing in first normal form are eliminated. For

example; Genre depends on Book_id and Book_name. Book_name depends on Author.

Price_of_book depends on Book_name but not the name of Author.

Book_id Genre Book_Name

Letter to My Daughter 2000

Table: 3rd^ 2NF table

3. Third Normal Form(3NF)

For a table to be in Third Normal Form, it must already be in Second Normal Form and every

attribute depends on the primary key. The attribute must be removed from the table if it is not

dependent on the primary key. Hence, it states that 3NF eliminated transitive dependency.

Book_Id Genre Book_Name 1 Novel The Tragic Muse 1 Novel The Way It Came 1 Novel Daisy Miller 2 Autobiography I Know Why the Caged Birds Sing 2 Autobiography And Still I Rise 2 Autobiography Letter to My Daughter

Table: 1st^ 3NF album table

Book_Name Author The Tragic Muse Henry James The Way It Came Henry James Daisy Miller Henry James I Know Why the Caged Birds Sing Maya Angelou And Still I Rise Maya Angelou Letter to My Daughter Maya Angelou

Table: 2nd^ 3NF table

Book_Name Price_of_book (Rs.) The Tragic Muse 1500 The Way It Came 1500 Daisy Miller 1500 I Know Why the Caged Birds Sing 1500 And Still I Rise 2000 Letter to My Daughter 2000

Table: 3rd^ 3NF table