Database Tutorial

Course Tutorial Site

Site Admin

2NF Modeling

without comments

Week 3: Reading

The following are the reading articles:

2nd Normal Form (2NF)/p>

Learning Outcomes

  • Learn how to define and apply Second Normal Form (2NF).

Lesson Materials

Second Normal Form (2NF) exists when a table is already in first normal form and all non-key columns depend on all of the key columns, where the list of key columns goes from 1 to n. The list of key columns makes up the natural key of a table, or the list of columns that makes any row unique in a table. A table that has only a single column as the natural key, like a Vehicle Identification number, is automatically in second normal form.

A table that has two or more columns as a natural key may contain one or more non-key columns that has a partial dependency on one or a set of columns less than all the columns in the key (or primary key). This typically means you have created a table that contains two subjects. The following illustrations shows the architecture of the 1NF table before converting it to 2NF.

2rdnfdependencyproblem

You divide the table into two tables to fix this problem. Second normal form modeling finds and moves columns from a base table when they have a partial dependence on part of a primary key. They are generally made into an independent table. A foreign key replaces the column or columns in the base table, and it points to or has a functional dependency on the primary key in the new table.

2ndnfdependencydetail

The foreign key in the base table becomes part of the primary key. The remaining non-key columns depend wholly on the natural key of the original or base table. The following drawing represents the simplified outcome of creating second normal form table.

2ndnfdependency

Examples & Solutions: →

A table that has one or more columns with a partial dependency on one or more columns of a compound primary key isn’t in second normal form.

Problem Example →

You’ll notice that the following example table has a compound (or composite) primary key made up of the rental_id and item_id that is highlighted in yellow. There is also a group of columns highlighted in cyan that have a partial dependency on only the item_id column of the primary key.

2ndnfproblemtable

This partial dependency violates the rule of second normal form that all of the non-key columns are functionally dependent on the whole key. The whole key is the primary key. This violation indicates that the rental_item table contains two subjects.

Problem Solution →

You should break the table into two tables where each focuses on a single subject. The following shows two new tables.

2ndnfsolved

The item_id column in the rental_item table becomes a foreign key in the base table and a primary key in the new table. The new item table contains all the non-key columns that had a partial dependency on the item_id. You can see that the column or set of columns that have a partial dependency become their own table. This repeats how you resolved a repeating group to ensure a table met the first normal form rules. Both of these new tables are in at least second normal form.

Setup Scripts →

You run this script to create a table with a partial dependency that violates second normal form.

-- Conditionally drop the table.
BEGIN
  FOR i IN (SELECT   TABLE_NAME
            FROM     user_tables
            WHERE    TABLE_NAME = 'RENTAL_ITEM') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
END;
/
 
-- Create the 1st normal form table.
CREATE TABLE rental_item
( rental_id                   NUMBER
, item_id                     NUMBER
, customer_id                 NUMBER        CONSTRAINT nn_ri2  NOT NULL
, check_out_date              DATE          CONSTRAINT nn_ri3  NOT NULL
, return_date                 DATE          CONSTRAINT nn_ri4  NOT NULL
, item_barcode                VARCHAR2(20)  CONSTRAINT nn_ri5  NOT NULL
, item_type                   NUMBER        CONSTRAINT nn_ri6  NOT NULL
, item_title                  VARCHAR2(60)  CONSTRAINT nn_ri7  NOT NULL
, item_subtitle               VARCHAR2(60) 
, item_rating                 VARCHAR2(8)   CONSTRAINT nn_ri8  NOT NULL
, item_rating_agency          VARCHAR2(4)   CONSTRAINT nn_ri9  NOT NULL
, item_release_date           DATE          CONSTRAINT nn_ri10 NOT NULL
, CONSTRAINT pk_ri PRIMARY KEY (rental_id, item_id));

You run this script to create a tables that meet second normal form rules. The previous partial dependency is eliminated by taking the columns out of one table and creating a new table. This script also serves as your starting point in the 3rd normal form example.

-- 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                 VARCHAR2(8)   CONSTRAINT nn_i4  NOT NULL
, item_rating_agency          VARCHAR2(4)   CONSTRAINT nn_i5  NOT NULL
, item_release_date           DATE          CONSTRAINT nn_i6  NOT NULL);
 
-- Add foreign key constraints.
ALTER TABLE rental ADD CONSTRAINT fk_r1 FOREIGN KEY(item_id)
  REFERENCES item(item_id);

Written by michaelmclaughlin

August 11th, 2018 at 4:53 pm

Posted in