Database Tutorial

Week 3: Reading

The following are the reading articles:

• Definitions
• Data Modeling
  • 1NF Modeling
  • 2NF Modeling
  • 3NF Modeling
• Common Lookup
• Who Audit Security

3^rd Normal Form (3NF)

Learning Outcomes

• Learn how to define and apply Third Normal Form (3NF).

Lesson Materials

Third Normal Form (3NF) exists when a table is already in second normal form and there are no transitive dependencies. A transitive dependency means a non-key column or set of columns are dependent on another column that generally isn’t part of the natural key.

Since all non-key columns should be wholly dependent on the primary key, a transitive dependency exists when a column’s functional dependency routes through another column or set of columns on its way to the primary key. This type of relationship indicates that the there are at least two subjects in a table, and appears as follows conceptually:

You should generally make the column or set of columns that acts as the intermediary column the natural primary key in a new table. The column or columns that had a transitive dependency on the intermediary column should also move to the new table. A foreign key to the new table should replace those columns in the original table, as shown in the following conceptual drawing:

Some exceptions qualify when a transitive dependency doesn’t disqualify a table from being in third normal form. The exceptions are:

• When the transitive dependency is on a column that is part of the natural key.
• When the transitive dependency is on a superkey, like a gender column.
• When the transitive dependency is trivial, which typically means the dependency doesn’t compel the addition of an extra row to a table.

Moreover, you break up the table into two parts to fix any transitive dependencies when moving from second to third normal form. This mirrors what you did to fix partial dependencies when moving from first to second normal form. The only difference between these two exercises is that a partial dependency relates to a part of the primary key, while a transitive dependency relates to a non-key column. The non-key column acts as a portal to the primary key of the second normal form table.

Therefore, third normal form modeling finds and moves columns from a base table when they have a transitive dependence on a column outside the primary key. These columns are generally made into an independent table. A foreign key replaces the columns removed from the base table. The foreign key depends wholly on the primary key of the new table.

-- Conditionally drop the table.
BEGIN
  FOR i IN (SELECT   TABLE_NAME
            FROM     user_tables
            WHERE    TABLE_NAME IN ('ITEM','RENTAL')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
END;
/
 
-- Create the 2nd normal form tables.
CREATE TABLE rental
( rental_id                   NUMBER        CONSTRAINT pk_r1  PRIMARY KEY
, item_id                     NUMBER        CONSTRAINT nn_r1  NOT NULL
, customer_id                 NUMBER        CONSTRAINT nn_r2  NOT NULL
, check_out_date              DATE          CONSTRAINT nn_r3  NOT NULL
, return_date                 DATE          CONSTRAINT nn_r4  NOT NULL);
 
CREATE TABLE item
( item_id                     NUMBER        CONSTRAINT pk_i1  PRIMARY KEY 
, item_barcode                VARCHAR2(20)  CONSTRAINT nn_i1  NOT NULL
, item_type                   NUMBER        CONSTRAINT nn_i2  NOT NULL
, item_title                  VARCHAR2(60)  CONSTRAINT nn_i3  NOT NULL
, item_subtitle               VARCHAR2(60)
, item_rating_id              NUMBER        CONSTRAINT nn_14  NOT NULL 
, item_rating                 VARCHAR2(8)   CONSTRAINT nn_i5  NOT NULL
, item_rating_agency          VARCHAR2(4)   CONSTRAINT nn_i6  NOT NULL
, item_release_date           DATE          CONSTRAINT nn_i7  NOT NULL);
 
CREATE TABLE item_rating
( item_rating_id              NUMBER        CONSTRAINT pk_ir1 PRIMARY KEY
, item_rating                 VARCHAR2(8)   CONSTRAINT nn_ir1  NOT NULL
, item_rating_agency          VARCHAR2(4)   CONSTRAINT nn_ir2  NOT NULL);
 
-- Add foreign key constraints.
ALTER TABLE rental ADD CONSTRAINT fk_r1 FOREIGN KEY(item_id)
  REFERENCES item(item_id);
 
ALTER TABLE item ADD CONSTRAINT fk_r1 FOREIGN KEY(item_rating_id)
  REFERENCES item_rating(item_rating_id);