Saturday 30 March 2013

Database Normalization

Database Normalization is the process of organizing data in a database by eliminating redundancy and inconsistent dependency. it divides large tables into smaller tables ,which are less redundant , and defines relationships between them.The Main motive is to isolate data so that additions, deletions, and modifications of a field is made in just one table and then propagated through the rest of the database using the defined relationships.

Database normalization uses few rules . Each rule is called a “normal form.” If the first rule is observed, the database is said to be in “first normal form.” If the first three rules are observed, the database is considered to be in “third normal form.” Although other levels of normalization are possible, third normal form is considered the highest level necessary for most applications.

Normalization Form
Description
Example
First normal
form
(1NF)
It is a property of a relation in a relational database. A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain
For example, to track an inventory item that may come from two
possible sources, an inventory record may contain fields for Vendor Code 1
and Vendor Code 2.
Rules
i) Eliminate repeating groups in individual tables.
ii) Create a separate table for each set of related data.
iii) Identify each set of related datawith a primary key.
Second
Normal Form(2NF)
A table is in 2NF if and only if it is in 1NF and no non-prime
attribute is dependent on any proper subset of any  candidate key of the table. A non-prime attribute of a table is an attribute that is not a part of any candidate key of the table.
For example, consider a customer’s address in an accounting system. The address is needed by the Customers table, but also by the Orders, Shipping, Invoices, Accounts Receivable, and Collections tables. Instead of storing the customer’s address as a separate entry in each of these tables,
store it in one place, either in the Customers table or in a separate Addresses table.
Rules
i) Create separate tables for sets of values that apply to multiple records.
ii) Relate these tables with a foreign key.
Third Normal
Form(3NF)
a table is in 3NF if and only if both of the following conditions hold:
i)The relation R (table) is in second normal form
(2NF)
ii)Every non-prime attribute of R is non-transitively dependent (i.e. directly dependent) on every superkey of R.
For example, in an Employee Recruitment table, a candidate’s university name and address may be included. But you need a complete list of universities for group mailings. If university information is stored in the
Candidates table, there is no way to list universities with no current candidates. Create a separate Universities table and link it to the
Candidates table with a university code key.
Rules
Eliminate fields that do not depend on the key

No comments:

Post a Comment