Main Menu

Normalization PDF Print E-mail


*  In relational database design, the process of organizing data to minimize duplication.
*  Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.
*  The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just
one table and then propagated through the rest of the database via the defined relationships.”

"Normalization" refers to the process of creating an efficient, reliable, flexible, and appropriate "relational" structure for storing information. Normalized data must be in a "relational" data structure.

1st Normal Form

1st Normal Form ==> There should be no repeating groups.

For instance consider the following structure

Student Table

StudentID (PK) NameDobAdvisorAdvisor TelephoneAdvisor AddressCourseCourse Instructor

Problem to Fix : A student can take multiple courses and hence repeating groups.

Soultion : Make a separate table for course & link it to student table using StudentID. This will results in the below design

Student Table
StudentID (PK) NameDobAdvisorAdvisor TelephoneAdvisor Address

Courses table
ID (Course ID) (Primary Key -> Composite Key ID + StudentID)CourseCourse InstructorStudentID

2nd Normal Form ==> No Non-Key field should depend on the part of the primary key.

Problem to Fix : Here in Courses table the primary key is a composite key ID + StudentID. (student ID is not unique in itself, as one student may take multiple courses; similarly, course ID is not unique in itself as many students may take the same course; however, each student will only be taking a particular course once at any one time, so the combination of student ID + course ID gives us a unique primary key). And a non-key field Course Instructor is depending on the Course ID alone, which is only a part of the primary key. Solution : To fix this we create a third table, to get the below structure for our database.

Student Table
StudentID (PK) NameDobAdvisorAdvisor TelephoneAdvisor Address

Courses table
ID (Course ID) (PK)CourseCourse Instructor

Student Courses table
Student ID (Primary Key ==> Composite Key)Course ID

3rd Normal Form ==> No fields may depend on other non-key fields. Ie each field in a record should contain information about the entity that is defined by the primary key.

Problem to Fix : Here in student table, the primary key is Student ID & the non-key fields like Advisor Telephone & Advisor Address cannot be defined with the Primary key. Solution : To fix this we need to create a 4th table Advisor table, to get the below structure.

Student Table
StudentID (PK) NameDobAdvisor ID

Courses table
ID (Course ID) (PK)CourseCourse Instructor

Student Courses table
Student ID (Primary Key ==> Composite Key)Course ID

Advisor table
ID (Advisor ID) (Primary Key)AdvisorAdvisor TelephoneAdvisor Address


The database is in 3rd normal form now
Comments (19)
  • |122.248.163.xxx |2009-11-02 12:20:00 Rupa
    Useful and informative
    ReplyQuote
  • |203.112.82.xxx |2011-07-13 10:09:25 Anonymous
    :no-comments:



    ReplyQuote
  • |205.209.140.xxx |2011-08-17 08:08:32 uggs sale  - uggs sale
    By all means, they look dangerously comfy,uggs sale, that I wouldn’t be surprised if you caught me accidentally walking down to belstaff jacket. Even so, I am feeling stress-fully torn between my belstaff jackets for comfort and their link to my Grandmother’s ugg canada. Surely, if I pop these on it doesn’t mean I’ve crossed the line between stylish and uggs sale? As long as Countryfile isn’t on that ugg canada.Perhaps it’s just my own irrational fear of picturing these slippers with ugg classic tall? If, however, these luxurious bailey button ugg were teamed with your favourite check pyjama trousers and an oversized t-shirt ugg bailey button, and accessorized with some bailey button uggs then I reckon we’d have an indoor-shoe winner.clw817
    ReplyQuote
  • |205.209.143.xxx |2011-08-24 06:23:01 ugg boots sale  - ugg boots sale
    Located in Canada,we provide you with best quality luxurious ugg boots sale. belstaff jackets are selling all around the globe. Choose from our belstaff jacket,and Lace Up boots as well as our enormously popular uggs canada. Kids and baby boots are also available, as well as slippers and scuffs and our uggs canada range and Desert boots. To complement our ugg boots canada we also offer top quality Pashmina shawls. Don't forget to visit our specials page and grab yourself a ugg classic tall boots!At ugg bailey button you will not only receive the best quality products, but highly competitive prices and excellent customer service. If you can't find what you are looking for or have questions on any of our bailey button uggs, please visit uggs bailey button or contact us for more information.clw824
    ReplyQuote
Write comment
Your Contact Details:
Comment:
[b] [i] [u] [url] [quote] [code] [img]   
:D:angry::angry-red::evil::idea::love::x:no-comments::ooo::pirate::?::(
:sleep::););)):0
Security
Please input the anti-spam code that you can read in the image.
 
Copyright 2009 SQL Server.in, Powered by Joomla!; Joomla templates by SG web hosting