Normalization in database management system - 1

Normalization

Ø  Normalization is a process of simplifying relationship between data elements in a record

Ø  With the help of normalization a record structure is replaced with a new record structure which is more simper and more manageable.

Ø  Normalization is performed for following reason

Ø  To provide structured data to represent relationship easily

Ø  To provide simple retrieval of data in response of query and

Ø  To simplify various operations like update, deletion, and insertions

Ø  To reduce the amount of restructure or reorganize activity when any changes occurs

Un-Normalized Data

PRODUCT REPORT

PROD. NO.	PRODUCT NAME	PROD. COST	PART NO.	PART NAME	QTY. USE	SUP NO.	SUP NAME	SUP CITY
101	Generator	10000	201	WHEELS	4	301	STEELCO	BARODA
			202	BOLTS	100	302	TISCO	TATANAGAR
			203	STEEL BAR	10	301	STEELCO	BARODA
102	ENGINE	20000	202	BOLTS	200	302	TISCO	TATANAGAR
			203	STEEL BAR	20	301	STEELCO	BARODA
			204	PIPES	10	303	SAIL	INDORE

How to achieve First Normal Form?

Ø  Variable length of records creates no. of problems as system has to keep track of when record starts and when records ends. Thus we have to provide a table with fixed length records

PRODUCT REPORT

PROD. NO.	PRODUCT NAME	PROD. COST	PART NO.	PART NAME	QTY. USE	SUP NO.	SUP NAME	SUP CITY
101	Generator	10000	201	WHEELS	4	301	STEELCO	BARODA
101	Generator	10000	202	BOLTS	100	302	TISCO	TATANAGAR
101	Generator	10000	203	STEEL BAR	10	301	STEELCO	BARODA
102	ENGINE	20000	202	BOLTS	200	302	TISCO	TATANAGAR
102	ENGINE	20000	203	STEEL BAR	20	301	STEELCO	BARODA
102	ENGINE	20000	204	PIPES	10	303	SAIL	INDORE

Ø  First normal form is achieved when all the repeating groups are removed so that a record can be of fixed length and creating a separate file or relation containing repeating group.

Ø  Here the original record and new records are interrelated by a common data item.

Ø  In this example we will remove repeating fields and will create a separate table.

Ø  Thus in Product table we keep PROD-NO, PRODUCT NAME and PROD.COST, while in PRODUCT_PART table we keep all the remaining columns with PROD_NO(key field) column to preserve relationship.

FIRST NORMAL FORM

PRODUCT

PROD. NO.	PRODUCT NAME	PROD. COST
101	Generator	10000
102	ENGINE	20000

PRODUCT-PART

PROD. NO.	PART NO.	PART NAME	QTY. USE	SUP NO.	SUP NAME	SUP CITY
101	201	WHEELS	4	301	STEELCO	BARODA
101	202	BOLTS	100	302	TISCO	TATANAGAR
101	203	STEEL BAR	10	301	STEELCO	BARODA
102	202	BOLTS	200	302	TISCO	TATANAGAR
102	203	STEEL BAR	20	301	STEELCO	BARODA
102	204	PIPES	10	303	SAIL	INDORE

Second Normal Form

Ø  Second normal form is achieved when a record is in first normal form and each item in the record is fully dependent on the primary record for identification

Ø  Second normal form applies Functional Dependency on record structure.

 

Functional Dependency

A data item is functionally dependent if its value is uniquely associated with a specific data item. Means each data item should be fully dependent of some key attribute for access.

Ø  To achieve second normal form every data item in a record that is not dependent on the primary key of the record should be removed and used to form a separate table or relation

Ø  In PRODUCT-PART table we are having composite primary key (PROD_ NO + PART_NO). Here field QTY_USE full dependent upon the primary key where as other fields are not fully dependent on this composite primary key thus we keep (PROD_ NO, PART_NO, QTY_USE) in PRODUCT_PART table.

Ø  Remaining columns (PARTNAME,SUP NO.,SUP NAME,SUP CITY) will be kept in other  new table that is PART table.

SECOND NORMAL FORM

PRODUCT

PROD. NO.	PRODUCT NAME	PROD. COST
101	Generator	10000
102	ENGINE	20000

PRODUCT-PART

PROD. NO.	PART NO.	QTY. USE
101	201	4
101	202	100
101	203	10
102	202	200
102	203	20
102	204	10

PART

PART NO.	PART NAME	SUP NO.	SUP NAME	SUP CITY
201	WHEELS	301	STEELCO	BARODA
202	BOLTS	302	TISCO	TATANAGAR
203	STEEL BAR	301	STEELCO	BARODA
204	PIPES	303	SAIL	INDORE

Third Normal Form

Ø  Third normal form is achieved when transitive dependency are removed from a record design

Ø  Transitive Dependency Example

Ø  For example there are 3 data items in a record A,B,C

Ø  If C is functionally dependent on B

Ø  If B is functionally dependent on A

Ø  Then C is functionally Dependent on A

Ø  But this type of indirect dependency is known as transitive dependency

Ø  In PART table we are having transitive dependency lies.

PART

PART NO.

PART NAME

SUP NO.

SUP NAME

SUP CITY

Ø  Here SUP_NAME,SUP_CITY are dependent upon the SUP_NO field. Again SUP_NO field dependent upon the PART_NO.(C=>B=>A)

Ø  Thus we have to remove this transitive dependency by breaking this PART table into two new tables PART_SUPPLIER & SUPPLIER tables.

THIRD NORMAL FORM

PRODUCT

PROD. NO.	PRODUCT NAME	PROD. COST
101	Generator	10000
102	ENGINE	20000

PRODUCT-PART

PROD. NO.	PART NO.	QTY. USE
101	201	4
101	202	100
101	203	10
102	202	200
102	203	20
102	204	10

To remove transitive dependency the fields like (SUP_NAME,SUP_CITY) transitively dependent upon the SUPNO=>PARTNO have to be removed and to be place in separate table called SUPPLIER.

 

 

PART-SUPPLIER

PART NO.	PART NAME	SUP NO.
201	WHEELS	301
202	BOLTS	302
203	STEEL BAR	301
204	PIPES	303

SUPPLIER

SUP NO.	SUP NAME	SUP CITY
301	STEELCO	BARODA
302	TISCO	TATANAGAR
303	SAIL	INDORE

Posted in: Database Management Systems-I, sql