Database Design 1: Normalization

First normal form

- Tables without repeating groups.

Functional Dependence

- a column (attribute) B is functionally dependent on another column A (or possibly a collection of columns) when each value for A in the database is associated with exactly one value of B.

Candidate key

- a column or a collection of columns on which all columns in the table are functionally dependent. - the definition for primary key also defines a candidate key.

Functional Dependence

- a formal name for what is basically a simple idea.

Normalization

- a progression in which a table that is in first normal form is better (freer from problems) than a table that is not in first normal form, a table that is in second normal form is better than one that is in first normal form, and so on.

Unnormalized relation

- a relation (table) that contains a repeating group (or multiple entries for a single record)

To correct update anomalies

- convert tables to various types of normal forms.

Normalization Process

- enables you to identify the existence of potential problems, called update anomalies, in the design of a relational database.

Update Anomalies

- identify the existence of potential problems

Alternate keys

- the candidate keys that are not chosen as the primary key.

Goal of Normalization

- to take a table or collection of tables and produce a new collection of tables that represents the same information but that is free of update anomalies.

Functional dependence Keys

2 Crucial Concept

Note:

A table (relation) is in first normal form (1NF) when it does not contain repeating groups.

Key

Column A (or a collection of columns) is the primary key for a relation (table) R, if: Property 1. All columns in R are functionally dependent on A. Property 2. No subcollection of the columns in A (assuming A is a collection of columns and not just a single column) also has Property 1.

Question: On which columns is QuotedPrice functionally dependent?

For any combination of an order number and a part number, there can be only one row in the OrderLine table. Thus, any combination of an order number and part number in the OrderLine table is associated with exactly one quoted price. Consequently, QuotedPrice is functionally dependent on the combination (formally called the concatenation) of OrderNum and PartNum.

Note

In general, when converting a table that is not in first normal form to first normal form, the primary key will usually include the original primary key concatenated with the key to the repeating group, which is the column that distinguishes one occurrence of the repeating group from another on a given row in the table

First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Fourth Normal Form (4NF)

Most common normal forms

Question: Is OrderNum the primary key for the OrderLine table?

No, because it does not uniquely determine NumOrdered or QuotedPrice. The order number 21617, for example, appears on a row in the OrderLine table in which the number ordered is 2 and the quoted price is $794.95 and on a row in which the number ordered is 4 and the quoted price is $150.00.

Question: Is Class the primary key for the Part table?

No, because the other columns are not functionally dependent on the class. The item class HW, for example, appears on a row in the Part table in which the part number is AT94, a row in which the part number is DL71, and a row in which the part number is FD21. The item class HW is associated with three part numbers, so the part number is not functionally dependent on the class.

Question: Is QuotedPrice functionally dependent on PartNum?

No. A given part number, such as DR93, can occur on more than one row in the OrderLine table. The quoted price can be different on each row. With current data, the two rows on which the part number is DR93 actually have the same price, $495.00. W

Question: Is the combination of PartNum and Description the primary key for the Part table?

No. It is true that this combination functionally determines all columns in the Part table. PartNum alone, however, also has this property.

Question: In the OrderLine table, is QuotedPrice functionally dependent on OrderNum?

No. Order number 21617, for example, occurs on a row in which the quoted price is $794.95 and on another row in which the quoted price is $150.00. Thus, an order number can be associated with more than one quoted price.

Question: In the Customer table, is CustomerName functionally dependent on RepNum?

No. Rep number 20, for example, occurs on a row in which the customer name is Al's Appliance and Sport, on a row in which the customer name is Kline's, and on a row in which the customer name is All Season. Thus, a rep number can be associated with more than one customer name.

Note

The primary key is often called the key in other studies on database management and the relational model. This text will continue to use the term primary key to distinguish between the different definitions of a key that you will encounter throughout this text.

Question: Is the combination of OrderNum and PartNum the primary key for the OrderLine table?

Yes, because all columns are functionally dependent on this combination. Any combination of an order number and a part number occurs on only one row in the OrderLine table and is associated with only one value for NumOrdered and only one value for QuotedPrice. Further, neither OrderNum nor PartNum alone has this property. For example, order number 21617 appears on more than one row, as does part DR93.

Question: Is CustomerNum the primary key for the Customer table?

Yes, because customer numbers are unique. A given customer number cannot appear on more than one row. Thus, each customer number is associated with a single name, a single street, a single city, a single state, a single zip code, a single balance, a single credit limit, and a single rep number. In other words, all columns in the Customer table are functionally dependent on CustomerNum.