Improving Database Design through Normalization.

Question:

DATABASE NORMALIZATION

Improving Database Design through Normalization. Continue improving the database (tables) you designed Here are your tasks: Discuss whether the tables you designed have satisfied 1NF, 2NF, and 3NF. Explain why or why not. Make changes if the tables do not meet the requirement of the 3NF. Write Oracle SQL statements to create the tables you have designed and populate the tables with data.

Expectations

Fulfill the following requirements in this assignment:

Discuss the requirement for the first, second, and third normal forms. Present reasons why or why not the tables in the database you designed have satisfied the three levels of normal form. Discuss what you are going to change so that all your tables satisfy at least the third normal form standard. Write SQL statements to create the structure of the tables, specify primary and foreign keys, and populate the data.

Improving Database Design through Normalization.

Summary:

Normalization:

 It is a process used to reduce redundancy in database tables. Redundancy is formed because of insertion,   deletion, and updating anomalies

Normalization is mainly classified into 3 types:

1) First Normal Form(1NF):

A relation is said to be in 1NF if every attribute in the relation is single-valued. From the example given below, we can clearly understand about 1NF:

TABLE 1:

ID NAME COURSE
1 RAM C, C++
2 ADI PYTHON
3 VICKY C, PYTHON

In the above table, the course is a multi-valued attribute, so it does not follow the first normal form. so we must decompose the above table in the following way,

TABLE 2:

ID NAME COURSE
1 RAM C
1 RAM C++
2 ADI PYTHON
3 VICKY C
3 VICKY PYTHON

Now in the above table, there are no multi-valued attributes, so it follows the first normal form.

 

2) Second Normal Form(2NF):

A relation is said to be in 2NF if there are no partial dependencies.

EG:

TABLE STUDPRO:

SID PID S_NAME P_NAME
45 P01 RAM JAVA
56 P02 ADI C++
65 P03 VICKY PYTHON

In the above table, s_name is partially dependent by the sid, and p_name is partially dependent on the pid. so to remove partial dependencies we need to decompose the table in the following way.

TABLE STUDINFO:

SID PID S_NAME
45 P01 RAM
56 P02 ADI
65 P03 VICKY

TABLE PROINFO:

PID P_NAME
P01 JAVA
P02 C++
P03 PYTHON

Now there are no partial dependencies in the above table, so it follows 2NF.

 

3) THIRD NORMAL FORM(3NF):

A relation is said to be in 2NF if there are no transitive dependencies.

EG:

TABLE BOOK:

BOOK_ID GENRE_ID GENRE_TYPE PRICE
1 1 TRAVEL $50
2 2 GARDENING $70
3 1 TRAVEL $60
4 2 GARDENING $80

Here, book_id is dependent on genre_id, genre_id is dependent on genre_type so, to remove transitive dependencies, we need to decompose the table into,

TABLE 1:

BOOK_ID GENRE_ID PRICE
1 1 $50
2 2 $70
3 1 $60
4 2 $80

TABLE 2:

GENRE_ID

GENRE_TYPE
1 TRAVEL
2 GARDENING
1 TRAVEL
2

GARDENING

Now there are no transitive dependencies, so it follows 3NF.

EXPLANATION:

The relation in the above database follows all three types of normalization because there is no multi-valued attribute. But there is a composite attribute Name. So, we can split that attribute into two attributes. Thus we can say that the given database is in 1 NF.

Next, the relations are not partially dependent on each other. And there is no transitive dependency as all the attributes of the individual table do not affect other table attributes. So, the relation follows both  2-NF and 3-NF.

SQL (Structured Query Language) is a standard language for dealing with relational database management systems. It is used to perform operations like C(Create). R(Retrieve). U(Update). D(Delete) data. SQL is used to design and create database SQL tables and schema. It also creates and manages the databases.

SOURCE CODE:

The normalized database design is:

CREATE TABLE PDT ( PID int primary key, PName varchar[10], UPrice Int ); 

CREATE TABLE ORDERS ( ONo int primary Key, ODate date ); 

CREATE TABLE CUSTOMER ( CID int primary key, Fname varchar[8], Lname varchar[8], Address varchar[20], phn varchar[10] ); 

CREATE TABLE TRANSACTIONS ( TID int primary Key, TCode int, ONo int, CID int, PID int, foreign key (ONo) references ORDERS(ONo), foreign key (CID) references CUSTOMER(CID), foreign key (PID) references PDT(PID) );

 

Output:

Improving Database Design through Normalization.

 

Also read, Python3 How can I improve my code follow the instruction?

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *