Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • NORMALIZATION IN DBMS

    • 0
    • 1
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 302
    Comment on it

    NORMALIZATION

    Database is a collection of meaningful data by which we can easily upload,update,delete and manage data. while creating a database the main problem which encounter is data redundancy. Data redundancy is condition in which same piece of data is located in different places with in same database. In other words whenever data is repeated in database that is called data redundancy. The main problem comes by redundancy is data storage space .

    The main problem arises by data redundancy are following:

    • Data take extra memory or extra storage space.
    • If same data is store in differen places than during data modification we have to modify it from all location ,that process is very timeconsuming and hard to perform.
    • During deletion process we have to delete same data from all location.

      so to eliminate redundancy with in database Normalization technique is used.

    Normalisation is a technique of removing redundancy. Normalization is a approach in which tables are decompose to eliminate data redundancy. It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.

    NORMALIZATION RULE

    Normalization rule are divided into following normal form.

    • First Normal Form
    • Second Normal Form
    • Third Normal Form

    First Normal Form (1NF)

    In First Normal Form, their will be no column in which more then two values must be stored Instead of that we should separate such data into multiple rows.

    eg :- Employee Table :

    employee emp_id Department
    suresh 001 PHP, Python
    rohit 002 ios
    pankaj 003 anroid

    Employee Table following 1NF will be :

    employee emp_id Department
    suresh 001 Python
    suresh 001 PHP, Python
    rohit 002 ios
    pankaj 003 anroid

    Second Normal Form (2NF)

    In example of First Normal Form there are two rows for suresh, to include its multiple department . While it is an inefficient use of space. Also in the above Table in First Normal Form, while the candidate key is {employee, department}, emp_id of employee only depends on employee column, which is incorrect as per Second Normal Form. To achieve second normal form, it would be helpful to split out the department into an independent table, and match them up using the employee names as foreign keys.

    New Employee Table following 2NF will be :

    employee emp_id
    suresh 001
    rohit 002
    pankaj 003

    New Employee Table introduced for 2NF will be :

    employee Department
    suresh Python
    suresh PHP, Python
    rohit ios
    pankaj anroid

    Third Normal Form (3NF)

    A table design is said to be in 3NF if following condition get satisfied:

    • Table must be in 2NF
    • every non-prime attribute of table must be dependent on primary key.

      For example, consider a table with following fields.

    Employee_Detail Table :

    Employee_id Employee_name DOB address city State pincode
    001 suresh 1-1-1992 mandakni rishikesh uttrakhand 249204
    001 rohit 1-2-1992 doonvihar dehradun uttrakhand 248001
    001 pankaj 12-2-1992 neshvilla selaqui uttrakhand 255001

    In this table employee_id is Primary key, but address, city and state depends upon pincode. The dependency between pincode and other fields is called transitive dependency. Hence to apply 3NF, we need to make new table containing address, city and state, with pincode as primary key.

    New Employee_Detail Table :

    Employee_id Employee_name DOB pincode

    001 suresh 1-1-1992 mandakni rishikesh uttrakhand 249204

    002 rohit 1-2-1992 doonvihar dehradun uttrakhand 248001

    003 pankaj 12-2-1992 neshvilla selaqui uttrakhand 255001

    Address Table :

    address city State Zip
    mandakni rishikesh uttrakhand 249204
    doonvihar dehradun uttrakhand 248001
    neshvilla selaqui uttrakhand 255001

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: