The primary key column(s) must be declared NOT NULL, to maintain the integrity of data which is entered in the primary key column.
A NULL values is treated by mathematically operators, such as average, like any numeric value. These functions operate on the NULL without any problem, but produce an answer which has a value NULL.
(4)
The following are the entities that will be tested for normalisation, using the 1NF, 2NF and 3NF:
SUPPLIER (CODE, NAME, CONTACT, ADDRESS 1, ADDRESS 2, POST CODE, TOWN, PHONE, FAX).
INVENTORY (ITEM_CODE, STORE_LOCATION, QUANTITY).
1st Normalisation Form (1NF) basically states that the entity is in the 1NF if it consists of a Primary Key.
By analysing the two tables, SUPPLIER and INVENTORY, it is evident that they both consist of a primary key attribute.
In the SUPPLIER table the primary key attribute is the CODE attribute, where as in the INVENTORY table it is the ITEM_CODE attribute.
A table is in the 2nd Normalisation Form (2NF) if it is in the 1NF and no attribute is dependent on only a portion of the primary key.
The SUPPLIER table has a single attribute primary key. Thus by default, this table is in the 2NF.
On the other hand, the INVENTORY table has a primary key that is made up of two attributes, ITEM_CODE and STORE_LOCATION. But as the QUANTITY attribute depends on both of these attributes, consequently this table is in the 2NF.
3rd Normalisation Form says that a table is in this state, if it is in 2NF, and if it does not contain transitive dependency.
The following is the SUPPLIER’S table:
SUPPLIER (CODE, NAME, CONTACT, ADDRESS 1, ADDRESS 2, POST CODE, TOWN, PHONE, FAX).
As you can see that the SUPPLIER entity is not in the 3NF because the Town attribute is transitively dependent on Supplier. In order to transform this table to ensure that this table is in the 3NF we have to carry out the following processes:
- Break down the transitive dependency;
- Make another table consisting of the attributes which were involved in the dependency.
By breaking down the transitive dependency and creating a new table for them, the SUPPLIER entity will now consist of the following attributes:
SUPPLIER (CODE, NAME, CONTACT, ADDRESS 1, ADDRESS 2, PHONE, FAX).
The SUPPLIER table is now in 3NF, as it does not consist of any transitive dependencies.
The new table is called SUPPLIER_ADDRESS and it contains attributes that were involved in the transitive dependency in SUPPLIER table.
SUPPLIER_ADDRESS(SUPPLIER, POST_CODE, TOWN)
The new table is normalised because it complies with the rules of the 1NF, 2NF and 3NF.
The INVENTORY table is free of any transitive dependencies, so it is in the 3NF.
(5)
The many-to-many (m:m) relationship between the STUDENT entity and the MODULE entity is broken down into two, one-to-many (1:m) relationships, with a creation of a new entity called STUDENT_MODULE.
