Oracle Database Concepts Assignment Example | Topics and Well Written Essays - 750 words

Oracle Database Concepts - Assignment fashion modelIn invoice table (2NF), position equipment casualty and item description are dependent on item alone and is independent of the invoice study. Thus invoice table is further reduced to 3NF by moving the independent fields to another table named ITEM as shown belowThus the nett 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 belowThe initial design for storing invoice data had a single table. All the information was stored on the single table. This created a lot of verbiage in the database. For example, if a customer purchases three quantity of 5 items, then the information for the customer has to be repeat 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 modifyd . 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 for each one customer in the invoice table. This may allow for 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.