Prepare for your exams
Get points
Guidelines and tips
Sell on Docsity
Docsity AI

Prepare for your exams

Study with the several resources on Docsity

Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips

Sell on Docsity

Docsity AI

Log in Sign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents

20 Points

For each uploaded document

Answer questions

5 Points

For each given answer (max 1 per day)

All the ways to get free points

Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

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

Kathmandu University 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 🇳🇵

5

(1)

5 documents

1 / 6

This page cannot be seen from the preview

Don't miss anything!

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

2 Autobiography I Know Why the Caged

Birds Sing

And Still I Rise

Letter to My Daughter

Maya Angelou 1500

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.

Discover Assignments of Database Management Systems (DBMS) Kathmandu University

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

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

Related documents

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

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:

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.

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.

Table: 1st^ 3NF album table

Table: 2nd^ 3NF table

Table: 3rd^ 3NF table