Home » Education » Pros and Cons of Normalization

Pros and Cons of Normalization

by Suleman
96 views

To achieve the requirements of the 1NF, I introduced the primary key to make sure that data is atomic. To achieve 2NF, the primary key must establish each non-primary key attribute. I achieved 3NF by resolving all transitive dependencies.

Entity Relation Diagram

Pros and Cons of Normalization

SQL Statements

Create DATABASE store;

CREATE TABLE IF NOT EXISTS `customer` (

`customer_id` int(11) NOT NULL,

`customer_name` varchar(50) NOT NULL,

`email` varchar(50) NOT NULL,

`phone_number` int(11) NOT NULL,

PRIMARY KEY (`customer_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `employee` (

`social_sec_number` int(11) NOT NULL,

`fname` varchar(20) NOT NULL,

`lname` varchar(20) NOT NULL,

`address` varchar(50) NOT NULL,

`place_id` int(11) NOT NULL,

`birth_date` date NOT NULL,

`fulltime_parttime` char(5) NOT NULL,

`salary` int(11) NOT NULL,

`date_of_hire` date NOT NULL,

PRIMARY KEY (`social_sec_number`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `order` (

`order_id` int(11) NOT NULL,

`sale_date` date NOT NULL,

`price` int(11) NOT NULL,

`customer_id` int(11) NOT NULL,

`product_code` varchar(20) NOT NULL,

`social_sec_number` int(11) NOT NULL,

PRIMARY KEY (`order_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `place` (

`place_id` int(11) NOT NULL,

`city` varchar(50) NOT NULL,

`state` varchar(50) NOT NULL,

`zip_code` int(11) NOT NULL,

PRIMARY KEY (`place_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Screen Shot

Pros and Cons of Normalization

ACID (Atomicity, Consistency, Isolation, and Durability) ensures that database transactions are processed efficiently and correctly.

Atomicity states that any modification to the database must follow the all or nothing rule. Conversely, Consistency says that the only data to be written into the database must be validated. Equally, Isolation states that the concurrent occurrence of multiple transactions should not impact each other’s operations. Finally, Durability guarantees that any modifications to the database are not lost.

Normalization is a method of reducing or removing data redundancies in the database. Four goals are attained after normalization. First, there is a reduction in the number of duplicated data in the database. Additionally, there is an ordering of data into logical groupings in such a way that every group describes a small part of the complete data. Thirdly, there is the building of a database in which manipulation and access to data are rapid and proficient, while still maintaining the integrity of the stored data. Lastly, the data is organized such that one can edit and make changes in one part of the data without affecting other parts.

Pros and Cons of Normalization

In this exercise, normalization of the database up to the third standard form is achieved. Besides, there is an ERD (entity-relation diagram) and an Oracle database with the execution of SQL statements using PHPMYADMIN.

Pros and Cons of Normalization

The advantages of normalization are the reduction of relational inconsistencies and improved performance since a normalized database is faster to write.

The disadvantages of normalization include difficulty and increased costs required to implement, and individual skill requirement to offer functional views and maintain the database. Besides, new skills are needed to create OLAP views.

References
  • Churcher, C. (2012).Beginning database design from novice to professional (2nd ed.). New York: Apress:

You may also like

Leave a Comment