| Author: Dilip Kumbhar 28 Aug 2008 | Member Level: Gold | Rating:  Points: 4 |
Normalization is simplification of database to remove redundancy and mainatain consistency of the database.When the database is normalized every record is unique.
The following are the types of normalization-
1NF
2NF
3NF
Primary key is used for uniquely identifying the field while foreign key is used for establishing permanent relationship between two tables.
|
| Author: Appukuttan 28 Aug 2008 | Member Level: Diamond | Rating: Points: 6 |
Primary Key won't allow and in one table only one primary key will be there.
Foreign key will allow null and one table we can have more than one foreign keys..
Normailization:
Database normalization can essentially be defined as the practice of optimizing table structures. Optimization is accomplished as a result of a thorough investigation of the various pieces of data that will be stored within the database, in particular concentrating upon how this data is interrelated. An analysis of this data and its corresponding relationships is advantageous because it can result both in a substantial improvement in the speed in which the tables are queried, and in decreasing the chance that the database integrity could be compromised due to tedious maintenance procedures.
First Normal Form
Converting a database to the first normal form is rather simple. This first rule calls for the elimination of repeating groups of data through the creation of separate tables of related data. Obviously, the original table contains several sets of repeating groups of data, namely classID, className, classTime, classLocation, professorID, professorName. Each attribute is repeated three times, allowing for each student to take three classes. However, what if the student takes more than three classes? This, and other restrictions on this table should be obvious.
Therefore, let’s break this mammoth table down into several smaller tables. The first table contains solely student information (Student):
studentID
studentName
Major
college
collegeLocation
The second table contains solely class information (Class):
studentID
classID
className
The third table contains solely professor information (Professor):
professorID
professorName
Second Normal Form
Once you have separated the data into their respective tables, you can begin concentrating upon the rule of Second Normal Form; that is, the elimination of redundant data. Referring back to the Class table, typical data stored within might look like:
studentID
classID
className
134-56-7890
M148
Math 148
123-45-7894
P113
Physics 113
534-98-9009
H151
History 151
134-56-7890
H151
History 151
While this table structure is certainly improved over the original, notice that there is still room for improvement. In this case, the className attribute is being repeated. With 60,000 students stored in this table, performing an update to reflect a recent change in a course name could be somewhat of a problem. Therefore, I’ll create a separate table that contains classID to className mappings (ClassIdentity):
classID
className
M148
Math 148
P113
Physics 113
H151
History 151
The updated Class table would then be simply:
studentID
classID
134-56-7890
M148
123-45-7894
P113
534-98-9009
H151
134-56-7890
H151
Revisiting the need to update a recently changed course name, all that it would take is the simple update of one row in the ClassIdentity table! Of course, substantial savings in disk space would also result, due to this elimination of redundancy.
Third Normal Form
Continuing on the quest for complete normalization of the school system database, the next step in the process would be to satisfy the rule of the Third Normal Form. This rule seeks to eliminate all attributes from a table that are not directly dependent upon the primary key. In the case of the Student table, the college and collegeLocation attributes are less dependent upon the studentID than they are on the major attribute. Therefore, I’ll create a new table that relates the major, college and collegeLocation information:
major
college
collegeLocation
The revised Student table would then look like:
studentID
studentName
Major
|