Normalization
Data: Data means raw facts.
Database: A database is an organized collection of facts. In other words we can say
that it is a collection of information arranged and presented to serve an assigned
purpose
Database Management System (DBMS): Database Management System is a
software package that allows data to be effectively stored, retrieved and manipulated.
Relational model is the one of the database management system.
DBMS benefits
Improved strategic use of corporate data
Reduced complexity of the organization’s information systems environment
Reduced data redundancy and inconsistency
Enhanced data integrity
Application-data independence
Reduced application development and maintenance costs
Improved flexibility of information systems
Increased access and availability of data and information
Logical & Physical data independence
Concurrent access anomalies.
Facilitates atomicity problem.
Provides central control on the system through DBA.
Relational database management system:
In this system data is organized in the form of rows and columns
similar to a table a relational model is defined as database that allows you to group its
data items into one or more independent tables that can be related to one another by
using fields common to each related to table.
Field Names or Attributes
Feild
Emp_Code Name Salary
Tuples 101 Xyz 45000
102 Abc 26000
103 Lmn 10000
The table’s rows called tuples, contain records and columns, called attributes,
contain fields. The heading of the column is called field or field name.
Advantages:
The relational database structure can be efficiently used even with a PC that
has limited main memory and processing capability.
Relational database is much more easier to use
Relational database is only concerned with data not with the structures, which
improves the performance.
Relational model is very useful for representing most of the real world objects
and the relationships among them.
Disadvantages:
RDBMS does not allow nested structure
RDBMS supports only limited data types
In some cases it is impossible to represent every information in table form.
What is Normalization?
Normalization is the name given to the process of simplifying the relationship among
data elements in a record. Normalization replaces a collection of data in a record
structure by another record design, which is simpler, more predictable and therefore
more manageable.
The goal of a relational database design is to generate a set of relation schemes that
allow us to store information without any redundant (repeated) data. It also allow us
to retrieve information easily and more efficiently.
The first step towards normalization is to convert ER model into Tables or Relations.
The next step is to examine the tables for redundancy and if necessary, change them
to non-redundant model is then converted to a database definition, which achieves the
objective of the database design phase.
Need for Normalization
Normalization reduces redundancy. Redundancy is the unnecessary repetition of a
field. It can cause problems with storage, retrieval and updation of data. Redundancy
can lead to:
1. Inconsistencies-errors are more likely to occur when facts are repeated
2. Update anomalies inserting, modifying and deleting data may cause
inconsistencies. Inconsistency occurs when we perform updation or deletion of
data , while forgetting to make corresponding changes in other relations.
Salesperson Sales
Employee Employee Store Department Item Item Sale
Number Name Branch Number Description Price(Rs.)
21130680 Anand K Downtown Hardware TR10 Router 35.00
21130680 Anand K Downtown Hardware SA1 Saw 19.00
21130680 Anand K Downtown Hardware PT6 Drill 21.00
21130680 Anand K Downtown Hardware AB16 Lawn 245.00
mover
30142101 Saxena Dabeland Home TT1 Humidifier 114.00
Appliances
30142101 Saxena Dabeland Home DS10 Dishwasher 262.00
Appliances
41984620 Murti Cutter Auto parts MC16 Snow tire 85.00
Point
41984620 Murti Cutter Auto parts AC146 Alternator 65.00
Point
41984620 Murti Cutter Auto parts BB100 Battery 49.50
Point
61204721 Pandit Fashion Men’s HS10 Suit 215.00
spot clothing
Unnormalized file for sale
First Normal Form (1NF):
A table is in the first normal form when it contains no repeating groups. The
repeating columns or fields presenting an unnormalized table are removed from the
table and put into separate table or tables. When table has no repeating groups or have
primary key, it is said to be in first normal form. It means, for each cell in a table (one
row or one column), there can be one value. This value should be atomic in the sense
that it cannot be decomposed into smaller pieces.
In the unnormalized form the first four attributes (Employee number, employee name,
store branch, Department) are virtually constant. The remaining three attributes (Item
number, Item description, Sale price) contain data that change and repeated with
different sales persons. Therefore the repeating group should be separated from the
entity “Salesperson”.
* Employee * Item Item Sale
Number Number Description Price(Rs.)
* Employee Employee Store Department
21130680 TR10 Router 35.00
Number Name Branch
21130680 SA1 Saw 19.00
21130680 Anand K Downtown Hardware
21130680 PT6 Drill 21.00
30142101 Saxena Dab eland Home Appliances
21130680 AB16 Lawn mover 245.00
41984620 Murti Cutter Point Auto parts
30142101 TT1 Humidifier 114.00
61204721 Pandit Fashion spot Men’s clothing
30142101 DS10 Dishwasher 262.00
41984620 MC16 Snow tire 85.00
Salesperson
41984620 AC146 Alternator 65.00
data file
41984620 BB100 Battery 49.50
61204721 HS10 Suit 215.00
Salesperson Item file
* = Key
(a) The salesperson data file with employee number as the primary key.
(b) The salesperson item file with employee number and item number as new
attributes. These two attributes added to relate the records in this file to the
salesperson data file. The two attributes are used together for accessing data.
Second Normal Form (2NF):
A table is in the Second Normal Form if all its non-key fields are fully dependent on
the whole key. This means that each field in table must depend on the entire key or
primary key (not just part of it). Those that do not depend upon the combination key,
are moved to another table on whose key they depend on. Structure which do not
contain combination keys are automatically in the second normal form.
* Employ Store Department * Item
*Employee
Employee ee * Branch
Item Sale Item Description
Number
Number Name Number Price(Rs.) Number
21130680
21130680 AnandTR10Downtown 35.00
Hardware TR10 Router
21130680 K SA1 19.00 SA1 Saw
21130680
30142101 SaxenaPT6 Dab eland 21.00
Home PT6 Drill
21130680 AB16 245.00
Appliances AB16 Lawn mover
30142101
41984620 MurtiTT1 Cutter 114.00
Auto parts TT1 Humidifier
1 30142101 DS10Point 262.00 DS10 Dishwasher
41984620
61204721 PanditMC16Fashion 85.00 clothing
Men’s MC16 Snow tire
41984620 AC146
spot 65.00 AC146 Alternator
41984620 BB100 49.50 BB100 Battery
61204721 HS10 215.002 HS10 Suit
The second normalization makes sure that each non-key attribute depend on a key
attribute or on a composite key. Non-key attributes that do not meet this condition are
split into simpler entities. In the salesperson data file depends on the primary key
“Employee Number”. In the salesperson item file, the attribute “sale price” depends
on composite key (Employee number and item number). Note that the sales price is
firmly related to the salesperson number and the item number of the sale. The
attribute “Item Description” tags to “Item Number” which is the part of composite
key. “Item Description” is not related in any way to the “Employee Number” field.
This cause several concerns. An employee transfer would make it difficult to maintain
records because the sales information would be dropped when the salesperson leaves
department. This because sales information (item number, sale price) is linked with
“employee number’ in the salesperson item file.
To solve such problem we create new independent tables for “Item Description” and
“Sales price”. In one file, we create the item description attribute with item number
keys from the salesperson item file. The remaining attributes (Employee number, item
number, and sales price) become second table or file.
The creation of the second table offers several benefits. These are:
(a) Sales items can be added without being tagged to a specific salesperson.
(b) If the item changes we need to change only the item file.
(c) If a salesperson leaves the department, it would have no direct effect o the
status of the items sold.
Third Normal Form (3NF):
A table is said to be in the Third normal form, if all the non-key fields of the table are
independent of all other non-key fields of the [Link] means you have to remove
transitive dependency (ex. If a=b b=c therefore a=c) this you have to remove.
* Employee Employee Store * Store Department
Number Name Branch Branch
21130680 Anand K Downtown Downtown Hardware
30142101 Saxena Dab eland Dab eland Home
41984620 Murti Cutter Point Appliances
61204721 Pandit Fashion spot Cutter Point Auto parts
1 *
Fashion spot Item
Men’s clothing
* Employee * Item Sale Item Description
Number Number Price(Rs.) Number
21130680 TR10 35.00 TR10 Router
21130680 SA1 19.00 SA1 Saw
21130680 PT6 21.00 PT6 Drill
21130680 AB16 245.00 2 AB16 Lawn mover
30142101 TT1 114.00 TT1 Humidifier
30142101 DS10 262.00 DS10 Dishwasher
41984620 MC16 85.00 MC16 Snow tire
41984620 AC146 65.00 AC146 Alternator
41984620 BB100 49.50 BB100 Battery
61204721 HS10 215.00 HS10 Suit
We can observe that there is further room for improvement. In the salesperson data
file the attribute “store branch” is tagged to the primary key “Employee number”
while the attribute “department” which is non-key attribute is related to “store
branch”, which is another non-key attribute. Making “store branch” a key attribute
requires isolating “Department” along with “store branch” and placing them in a new
table.
Denormalization
In some exceptional cases, database designers use the redundancy for specific
applications. They select such a schema that is redundant information that means it is
not normalized.
For example, suppose that the name of an account holder has to display along with the
account number and cash balance, every time the account accessed. In our
normalized schema, this requires a join of account with transaction. One alternative is
to create a relation containing all the attributes of account and transaction. This
makes displaying the account information faster. However, the balance information
for an account is repeated for every person who owns the account, and all copies must
be updated by the application, whenever the account balance is updated. This process
of taking a normalized schema and making it non-normalized is called
denormalization. Designers of database use it to tune performance of systems that
require time-critical operations.