The Relational Model in C#

Generation QR Code in C# The Relational Model

3

Imagine a real-world scenario with customers that order products. Customers, orders, and products are entities you discovered when you got the description of the business scenario. Initially, you model everything in a single table called Orders. Table 3-1 shows an imaginary Orders table. Columns that are part of the key are shaded (OrderId only in this example).

TABLE 3-1

1 1 2

1 2 3

2008-10-22 2008-10-24 2008-09-15

This design is, of course, problematic. Some possible data manipulation anomalies are the following:

How do you insert a customer without an order (By the way, can you see the incompleteness problem )

If item Ba is renamed, how do you perform an update You can easily miss some row you should update. This occurs because of redundancy.

How do you calculate the total quantity of bananas This is the problem with a nonscalar column. The Items column is a collection.

The rst normal form (1NF) says that a table is in rst normal form if all columns are atomic. No multivalued columns are allowed. Note that the 1NF de nition simply states that a table must represent a relation. Decomposition has to start with the Items column. You need a single row per item in an order, and every atomic piece of data of a single item (ProductId, ProductName, Quantity) must get its own column. However, after the decomposition, you get multiple rows for a single order. OrderId by itself cannot be the key anymore. The new key is composed of the OrderId and ProductId columns. If you allow multiple products on a single order for example, each time with a different discount you would not be able to use the ProductId as a part of the key. You would probably add ItemId attribute and use it as a part of the new key. A decomposed table in 1NF would look like Table 3-2.

TABLE 3-2

1 1 2 3 3 1 1 1 2 2 Company ABC Company ABC Company ABC XYZ XYZ 2008-10-22 2008-10-22 2008-10-24 2008-09-15 2008-09-15

Before I start with 2NF, let me point out one common misconception with 1NF. You ll often read about repeating group of columns. Take, for example, the Employees table design shown in Figure 3-11.

You probably feel uncomfortable with this table. It has a repeating group of columns with a similar name ChildXName. Child1Name means the name of the oldest child, Child2Name means the name of the second oldest, and Child3Name means the name of the third oldest (disregarding twins). Of course, the question is, what if an employee has more than three children You d probably create a new table. You might think that you are normalizing the Employees table. You know that the relational model does not depend on names. Let s rename the table and all of the columns and get a table shown in Figure 3-12.

FIGURE 3-12 The Orders table (the Employees table renamed)

You probably feel more comfortable with this design, and this table seems perfectly normalized. The Employees table was in 1NF as well, but the problem is that a constraint is built into both tables. The rst constraint says we have employees with three (or at most three if the columns allow NULLs) children; the second constraint says an order has three

3

dates. Of course, the rst constraint makes no sense in real world, and the rst design was bad anyway. However, it was normalized. Remember that you can constrain with the data model itself with table design. Often a repeating group of columns with similar names really represents a hidden collection; however, don t decompose such groups automatically. Check the business rules the constrained predicates rst.