Normalization
Name:
Course:
Tutor:
Date:
To convert database tables to first normal for, you first of all attribute value in a relation has a single value only. Second is to ensure that all values for a given attribute are of similar dada types. Third step is to ensure that each attribute name is unique. Finally, you ensure that no two or more tuples (rows) in a relation are similar. All these steps ensures that the definition of a relation is met hence first normal form is achieved (Teorey et al., 2011, Singh, 2011).
To convert a database to second normal form, you list down the functional dependencies (FD) which may be FD1 and FD2 if a given relation is to be split up in to two relations. Then find out whether all non-key attributes are dependent on the entire key. If knowing one attribute in a relation can help you figure out other attributes, and then split the table further in to two relations. Then sample the data and functional dependencies for the two relations. You finally confirm whether the resultant two relations meet the definition of first normal form where each relation has well defined unique key field in addition to checking whether the relations meet the definition of second normal form where there is no partial key dependencies (Teorey et al., 2011, Singh, 2011).
To convert a database in to third normal form, you first check whether the existing relations are in second normal form. If they are, then check whether there is any transitive dependency existing between or among the relations. Next is to find out where there are anomalies in the second normal form relations. The split the relation in to functional dependencies, say FD1 and FD2, resulting to another two relations. Sample the data again and functional dependencies created. Finally, you check the resulting relations to find out if they indeed meet the definition of first normal and second normal forms. If they do, and there is no transitive dependency between or among the relations, then the relations are in third normal form (Teorey et al., 2011, Singh, 2011).
The illustration of bellow uses particular and course tables to explain why conversion of database to first, second and third normal form is appropriate in a college environment.
Particular (Name, course_Code->course_Number, department)
Course (course_Number, department->Lecturer_Room, Tutor)
In terms of first normal form, the two relations above must meet the definition of a relation as explained above in order for then to be safely stored in the database. In the second normal form, the two relations must meet the definition of a relation where each relation has well defined unique keys with no partial key dependencies. As an example, course_Number has partial dependency on Course_Code which in turn has a partial dependency on Name within particular table. A gainLecturer_Room has a partial dependency on department which in turn has a partial dependency on Course_Number within Course table. These dependencies must be removed in order to attain second normal form for easy creation of rules in the database for storage of student details. Assuming that the second normal form relation for the first relation is Particular (Name, course Code-> department), a transitive dependency is created between name and department which is again normalized too third normal form, resulting in to two other relations.
Denormalization table is accepted in situations where the process of updating a fact in the database is to be optimized by storing it in one place.. It is also accepted where performance is of the database is to be maximized as well as storing the past history. This can be justified in situation where accompany stores information of customers in project table and customer table where the company management may want to require customer name from the time a project was started. In this case, the database administrator will just maintain the current name only in the customer table. However he will have to add the customer_Name attribute to the project table in addition to the date itself when the name was valid. As a result more fields are added to the project table which optimizes future update process as well as maintaining past history of the customer in the customer table (Teorey et al., 2011, Bagui & Earp, 2011).
Business rules impact both database normalization and the decision to denormalize database tables in a significant manner in that normalization helps to create rules and patterns that can be applied to any changes in the database. This is very significant in business where the same rules are required to manage business changes in trends and patterns of seasonal sales. Denormalization helps to optimally update the day to day business transactions stored as facts in one place within the database.
References
Teorey, T. J., Lightstone, S. S., Nadeau, T., & Jagadish, H. V. (2011). Database modeling and design: Logical design. Elsevier.
Singh, S. K. (2011). Database systems: Concepts, design and applications. Pearson Education India.
Bagui, S., & Earp, R. (2011). Database design using entity-relationship diagrams. CRC Press.
