Oracle Database Concepts - Assignment Example

In invoice table (2NF), item price and item description are dependent on item alone and is independent of the invoice information. Thus invoice table is further reduced to 3NF by moving the independent fields to another table named ITEM as shown below:Thus the final design has 3 tables: CUSTOMER, ITEM, and INVOICE. Customer and Item are related to INVOICE using customer id and item id respectively as foreign keys. The new tables (reduced to 3NF) are shown below:The initial design for storing invoice information had a single table.

All the information was stored on the single table. This created a lot of redundancy in the database. For example, if a customer purchases three quantity of 5 items, then the information for the customer has to be repeated for all 15 (3 * 5) rows. This not only creates redundant data but also increases the database size. Further, if the customer information changes, then all the rows have to be updated. This might create a lot of update anomalies. Though this sort of anomalies can be avoided by updating all rows by using customer id in the WHERE clause, there are chances for the presence of huge records for each customer in the invoice table.

This may result in heavy updates and lowers the overall efficiency and throughput.After reduction to 2NF, though the data redundancy and update anomaly is eliminated to some extent, it still exists for item information. The information for items like item description and item price is purely dependent on ITEM id and not on invoice. This may again create redundancy and update anomalies. Hence the item information is moved to a new table thereby storing each business entity in separate tables. Thus, the new design that is reduced to 3NF eliminates redundancy and update anomalies and increases the overall efficiency.