Studio 2 : Blog Entry Week2
Task 2.1
This first task require me to create a new version of database that is normalized and transfer all the data from the old schema into the new schema. In order to complete the task, all I need to do is :
· Document the schema of the old database.
· Normalize that schema (to at least 3NF).
· Create physical version of that database design.
The default database can be obtain through the moodle as the instruction told me to download the Access version database. And this is the default schema :
To open the schema, I use Microsoft Access 2007 as the main tool because file that given to me is in Access’ form.
At first sight, It seems that the schema already in the normalized form, but the fact is the schema is still unnormalized. This is based on the the data that still have repetitive value in certain fileds within the schema. In order to normalized it, I need to search which tables that can be sorted out in order to make the schema normalized.
First of all, I need to bring the schema to the first normal form. The first normal form, as the textbook said, defines a structural constraint on table rows. Repeating fields are not allowed within any table in a relational database, as well as repeating groups of fields. Therefore, to erase the repeating elements is essential in this matter
Then in second normal form, we need to determine whether it’s in the first normal form or not. Since I already made it to first form, I can skip it and proceed to the next step. In this step to make it to second normal form, I have to determine whether every non-key field is functionally dependent on the prim ary key. If a non-key field is functionally dependent on only part of the primary key, I need to remove the non-key field from its present table and place it in another table.
Finally, in the third normal form. Based on our textbook, the third normal form is a relational database table structure in which no non-key field is functionally dependent on any other non-key field(s). The third normal form also concentrated to the other type of problem which is computable fields, for example : Date, Month, Year etc.
To normalized the schema is a difficult task. Especially in the second normal form because I need to classify the non-key field to the following primary key. But there’s some certain points where I find it easy to normalized the schema and It’s when I detect the repeating fields , in the first normal form. The first normal form is relatively easier because the repeating fields it easier to find rather than finding the non-key – primary key dependency.
In this matter, almost all of the tasks are realistic. Except if I want to change the primary key. It’s not possible if I want to make the other primary key that contain related table has different names than the other primary key in the other table. The Microsoft Access would detect an error, then it would not continue the process as it doesn’t understand why the primary key has the different name with the other primary key within the related table.
