perkthim1

With this table schema, all data relevant to an order is stored in one row (assuming orders of up to nine items only - an unreasonable restriction). Because all data is in one row, data can be very easily and quickly accessed. For example, it is very easy to calculate the total amount by adding all ItemXPrice values. Now consider an alternative schema shown below:

Note on reading notation: The notation used to link the tables together is UML notation, although other notation types can be used. Irrespective of what notation type is used, reading the diagram is the same. To read the notation: start with the table name, then any symbols attached to the connecting line or any description attached to the connecting line, then any multiplicity attached to the end of the connecting line, then the end table name. In other words: [StartTableName] + Symbol | Description + Multiplicity + [EndTableName]. The following examples illustrate: • [Order2NF] and [OrderItem2NF]: An order has 1 or more order items. • [OrderItem2NF] and [Item2NF]: An order item relates to only item only • [Order2NF] and [ContactInformation]: An order is billed to contact information. • [Order2NF] and [ContactInformation]: An order is shipped to contact information. • [Order2NF] and [OrderPayment]: An order is made against zero or more payment. • etc. To calculate the total amount you need to read data from a row in the [Order] table, data from all the rows in the [OrderItem] table and data from the corresponding rows in the [Item] table for each order item. For this kind of query the previous schema provides better performance. Note on redundancy It is important to be clear about the meaning of redundancy. The redundancy that should be avoided is the repeated representation of the same fact. For example, in the following table which is 1NF, the fact that warehouse 1 is located in Stree1 is repeated twice (attributes in green are keys). This means that any change to a warehouse address must be made to all records that store the address:

Note: formally, the concept of facts is knows as functional dependence. If a table has two columns A and B, then B is functionally dependent on A if values of A uniquely determine values of B (in other words, the same value of A necessarily means the same value of B). Let's look at another example that is not so obvious. Consider the [OrderItem] table which represents items for any given order:

The redundancy here is the price for each item. In many businesses, an item will always have one price and the preceding table contains redundant item prices. But what if item prices were negotiated on each order or there were special promotions? Then it would be possible for the relation above to have the following contents:

For the business model that this relation represents, there is no fact redundancy. The price of an item must be explicitly stored with each item. This example presents a crucial point in understanding normal forms: If you want to unambiguously represent various facts in a database, you must structure the relations so that they have no redundant representations of facts. The obvious consequence is that there is no mechanical method of normalizing relations. You must first know which facts you want to represent and then define the relations accordingly.