NORMALIZATION AND SQL DDL STATEMENTS

Normalize the previous module’s tables. Explain 1NF, 2NF, and 3NF, as well as if your tables satisfy each level of normal forms. Explain why all of your tables met the 3NF requirements.

Drop any tables that are no longer required, and construct new tables as needed in your new design, using SQL in DBMS.

The data in the table above is likely to be duplicated and redundant. For example, if there are more than one publisher, the process of recording data in a single database becomes more involved. Therefore for the data to be stored in a more organized manner, and this can only be done mainly through running the table through a series of normalization whereby related entries are grouped together.

First Normal Form (1NF)

In this stage, data is taken through a series of steps so as till conform to progressive normal forms. This stage must meet all the required procedures for the success of the next level of database normalization.

The basic of normalization is First normal form values in each column of a table must be atomic i.e unique.

Figure 2 Bookstore table of the first normal form

Figure 3 Database showing First Normal Form of table “customer”

Figure 4 Table showing the Author table

Second normal form (2NF)

This level of normalization groups data with almost similar characteristics in a new table by selecting from an unnormalized one and establishing relations among them.

This level of normalization 2NF relies on the relations between the composite key columns and non-key columns which means that the non-key columns depends on the whole composite key.

For our tables above, our primary key is going to act as composite so as to rule out the possibility of the same person writing more than one review of the book. Therefore for our case, the tables above already qualifies for the second normalization and therefore we will move to the next stage of normalization. (Baram, 2011)

Third Normal Form (3NF)

It is a requirement under this level for all columns to depend directly on the primary key. Tables violate the 3NF when one column depends on another column which, in turn, depends on the primary key – a transitive dependency. (Baram, 2011)

In the common table below, the address_ID and warehouse_ID are dependent on the Book_ID, therefore the customer_ID is moved outside this table so as to comply with the with 3NF.

A table should have a field that uniquely identifies each of its records, and each field in the table should describe the subject that the table represents. Michael J. HernandezDatabase Design for Mere Mortals

Figure Third normal form table

References

Ronen Baram (2011) Database normalization in MySQL: Four quick and easy steps

Retrieved from (http://www.computerweekly.com/tutorial/Database-normalization-in-MySQL-Four-quick-and-easy-steps)

David Adams & Dan Beckett(1997) Normalization Is a Nice Theory

Michael J. Hernandez (1997) Database Design for Mere Morals, A Hands-On Guide to Relational Database Design