Relational Model and Normalization

Tags: #◇Problem Solving #◇CCINFOM #◇Normalization #◇Database Design #◇Relational Model #◇M7

---

◇Key Definitions

Relation: A 2-dimensional table with columns (attributes) and rows (tuples). Domain: The allowable values for one or more attributes. Degree: The number of attributes (columns) in a relation. Cardinality: The number of tuples (rows) in a relation. Functional Dependency: The value of one attribute (the determinant) determines the value of another attribute.

◇Theoretical Framework

Properties of a Relation

For a table to be considered a relation:

1. It must have a unique name.
2. Every attribute value must be atomic (not multi-valued or composite).
3. Every tuple must be unique.
4. Attributes must have unique names.
5. Order of columns is irrelevant.
6. Order of rows is irrelevant.

Integrity Constraints Domain Constraints: Validates the correct data format/range. Entity Integrity: No primary key attribute may be null. Referential Integrity: A foreign key value must match a primary key value in the parent relation (or be completely null). Deletion rules include Restrict, Cascade, and Set-to-Null.

Data Anomalies

Poorly structured relations cause data anomalies: Insertion Anomaly: Cannot add new data without entering redundant information or being forced to add unrelated data. Deletion Anomaly: Deleting a record inadvertently results in the loss of other important information. Modification Anomaly: Updating a value requires changes to multiple redundant rows, increasing the risk of inconsistencies.

---

◇Important Formulas / Normal Forms

Normalization Process

Data Normalization is a technique used to validate and improve a logical design, reducing data redundancy and avoiding anomalies by decomposing relations.

First Normal Form (1NF) Requirement: No multi-valued attributes and every attribute value must be atomic. Action: Flatten the table by ensuring each cell contains only one value.

Second Normal Form (2NF) Requirement: Must be in 1NF PLUS every non-key attribute must be fully functionally dependent on the ENTIRE primary key. Action: Identify and remove partial functional dependencies. If a non-key attribute depends on only part of a composite primary key, move it to a new table along with that part of the key.

Third Normal Form (3NF) Requirement: Must be in 2NF PLUS no transitive dependencies. Action: Identify and remove transitive dependencies (where a non-key attribute is determined by another non-key attribute). Move the dependent attributes to a new table where the determinant becomes the primary key, and leave the determinant in the original table as a foreign key.

---

◇Worked Examples

Normalizing a Purchase Order

Unnormalized Form (UNF):
PO (PO#, PODate, ClientName, ClientAddress, ClientEmail, ClientPhone, {Prod#, ProdName, Price, Qty, Size, Color, Total}) The brackets {} denote a multi-valued group.

Step 1: 1NF (Remove multi-valued attributes)
Flatten into a single relation:
PO_1NF (PO#, PODate, ClientName, ClientAddress, ClientEmail, ClientPhone, Prod#, ProdName, Price, Qty, Size, Color, Total)
(Primary Key is composite: PO#, Prod#)

Step 2: 2NF (Remove Partial Dependencies) PODate, ClientName, ClientAddress, ClientEmail, ClientPhone depend ONLY on PO#. ProdName, Price, Size, Color depend ONLY on Prod#. Qty, Total depend on BOTH PO# and Prod#.

Resulting 2NF Relations:

1. PODetail (PO#, Prod#, Qty, Total)
2. Product (Prod#, ProdName, Price, Size, Color)
3. PO (PO#, PODate, ClientName, ClientAddress, ClientEmail, ClientPhone)

Step 3: 3NF (Remove Transitive Dependencies) In the PO table, ClientAddress, ClientEmail, ClientPhone depend on ClientName (assuming ClientName uniquely identifies a client in this context).

Resulting 3NF Relations:

1. PODetail (PO#, Prod#, Qty, Total)
2. Product (Prod#, ProdName, Price, Size, Color)
3. PO (PO#, PODate, ClientName)
4. Customer (ClientName, ClientAddress, ClientEmail, ClientPhone)

◇References & Navigation

• Previous Topic: ◇Enhanced ER Model and Transformation
• Related Notes: ◇Introduction to Databases