There are 3 primary reasons why a database should be normalized:
Apparent in the Orders entity is the repeating data in the ITEM_NAME, ITEM_DESCRIPT, QUANTITY, and PRICE attributes. We break the entity out to have no repeating data in any attribute while leaving the structure of the entity the same.
Each attribute now has a single value and there are now no repeating groups, or multiple data entries in any attribute.
Convert the un-normalized Orders entity to the First Normal Form.
We will need to have a combination of ORDER_ID and CUST_ID to uniquely identify the row. These will be our primary keys. We break down the Orders entity into a new Order entity, by extracting the information related to the product that was ordered. We now have 2 entities; Order, and Orders. We are now in 1st normal form. (1NF) where all key attributes are identified and there are no repeating groups. Our goal it to heave every entity act as a Noun, with the Noun's attributes only.
Convert to the Second Normal Form and provided the entity name, column names, and appropriate key types.
We further break out the Orders entity by creating an Items entity. Here we effectively remove the products being sold from the actual sales order information.
Obvious at this point the Items entity can now be updated at any time in the future without disturbing the entity relationships. For example, we could add a SKU Number attribute to the Items entity, or an End Of Life product designation.
Orders entity is now cleaned up once the product attributes were removed and moved to the Items entity.
Converted the entities to the Third Normal Form and provided the entity name, column names, and appropriate key type.
The primary advantage should be apparent; if a need arises at some point in the future to:
Here we now have our original Orders entity broken out into 3NF. Where each entity represents a single subject or noun and the primary keys are clearly defined. Attributes are now of a single value and simple (atomic data). However, this comes with a price as the additional entities will cause additional IO operations and processing logic to join them.
There are numerous articles on the advantages and disadvantages of normalization. Certainly the price of hard drives over time has changed and IO may not be the issue that it was in the past. Yet with the advent of tablet and mobile devices, the need for speed is at a premium once again.
- It brings data integrity to the tables (entities). This facilitates proper inserting, updating, and deleting of data thereby preventing data anomalies. If a table is not normalized it runs the risk of inconsistent data, data redundancies, and inconsistent result sets.
- To create entity relationships thereby providing referential integrity. Referential integrity ensures that relationships between tables remain consistent when entities are altered.
- To avoid having one set of users with a biased view of the data. For example a marketing department may view the data in line with their revenue needs rather than having an objective view of the data as it applies to the entire organization.
Apparent in the Orders entity is the repeating data in the ITEM_NAME, ITEM_DESCRIPT, QUANTITY, and PRICE attributes. We break the entity out to have no repeating data in any attribute while leaving the structure of the entity the same.
Each attribute now has a single value and there are now no repeating groups, or multiple data entries in any attribute.
Convert the un-normalized Orders entity to the First Normal Form.
We will need to have a combination of ORDER_ID and CUST_ID to uniquely identify the row. These will be our primary keys. We break down the Orders entity into a new Order entity, by extracting the information related to the product that was ordered. We now have 2 entities; Order, and Orders. We are now in 1st normal form. (1NF) where all key attributes are identified and there are no repeating groups. Our goal it to heave every entity act as a Noun, with the Noun's attributes only.
Convert to the Second Normal Form and provided the entity name, column names, and appropriate key types.
We further break out the Orders entity by creating an Items entity. Here we effectively remove the products being sold from the actual sales order information.
Obvious at this point the Items entity can now be updated at any time in the future without disturbing the entity relationships. For example, we could add a SKU Number attribute to the Items entity, or an End Of Life product designation.
Orders entity is now cleaned up once the product attributes were removed and moved to the Items entity.
Converted the entities to the Third Normal Form and provided the entity name, column names, and appropriate key type.
The primary advantage should be apparent; if a need arises at some point in the future to:
- Add additional attributes to the items entity
- Alter the DATE attribute's time stamp to MM-DD-YYYY in the ORDER entity.
Here we now have our original Orders entity broken out into 3NF. Where each entity represents a single subject or noun and the primary keys are clearly defined. Attributes are now of a single value and simple (atomic data). However, this comes with a price as the additional entities will cause additional IO operations and processing logic to join them.
Comments