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.

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:

 

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