Database Normalization Database normalization is the process of organizing the attributes and tables of a relational database to minimize data redundancy. Normalization involves refactoring a table into less redundant (and smaller) tables but without losing information, defining foreign keys in the old table referencing the primary keys of the new ones. The objective is to isolate data so that additions, deletions, and modifications of an attribute can be made in just one table and then propagated through the rest of the database using the defined foreign keys. Types of Normalization 1NF First normal form deals with the "shape" of a record type. Under first normal form, all occurrences of a record type must contain the same number of fields. First normal form excludes variable repeating fields and groups. This is not so much a design guideline as a matter of definition. Relational database theory doesn't deal with records having a variable number of fields. 2NF Second and third normal forms deal with the relationship between non-key and key fields. Under second and third normal forms, a non-key field must provide a fact about the key, us the whole key, and nothing but the key. In addition, the record must satisfy first normal form. 3NF Third normal form is violated when a non-key field is a fact about another non-key field, as in | EMPLOYEE | DEPARTMENT | LOCATION | ============------------------------ The EMPLOYEE field is the key. If each department is located in one place, then the LOCATION field is a fact about the DEPARTMENT -- in addition to being a fact about the EMPLOYEE. The problems with this design are the same as those caused by violations of second normal form:  The department's location is repeated in the record of every employee assigned to that department.  If the location of the department changes, every such record must be updated.  Because of the redundancy, the data might become inconsistent, with different records showing different locations for the same department.  If a department has no employees, there may be no record in which to keep the department's location. To satisfy third normal form, the record shown above should be decomposed into the two records: | EMPLOYEE | DEPARTMENT | | DEPARTMENT | LOCATION | ============------------- ==============-----------

 Task 1: Normalized this table into 2NF and 3NF. 

 

Task 1: Normalized this table into 2NF and 3NF. ISBN Book Title Author Num LastName Publisher Royalty Edition Task 2: Which normal form is the following table? Draw dependence diagram and give the steps to normalize the table in to 3NF. StudNO SubjectNO 1000 BCO2149 StudentName Bill Smith CourseNO BBIS CourseName Information Systems SubjectName Database Systems Result D 1000 BCO1147 Bill Smith BBIS Information Systems Programming C 1000 BCO3150 Bill Smith BBIS Information Systems Implementation HD 2000 BCO2149 2000 BCO1147 Anne Jones BBEC ECommerce Anne Jones BBEC ECommerce Database Systems Programming HD D Task 3: Which normal form is the following table? Draw dependence diagram and give the steps to normalize the table in to 3NF Shipment# Origin 409 Melbourne Destination Sydney Distance 1,100 618 Adelaide Brisbane 1,500 723 Tasmania Melbourne 300 824 Perth Adelaide 1,005 629 Melbourne Sydney 1,100