Database Tutorial

There are two major types of insertion anomalies. They both occur when a table contains more than one subject. The following example table and data support the discussion of insertion anomalies in the context of a first normal form table.

The table includes two subjects. One is a rental and the other an item. The item information is highlighted in cyan, and item columns have a partial dependence on the compound primary key.

• If you want to insert a new item, like a just released DVD, you must create a row that leaves all non-item related columns empty. However, the database level NOT NULL constraints prevent such an insertion. Disabling the database constraints would compromise the integrity of the model to support row with rental and item data.
• If you want to insert a new rental but don’t have the item available, you must first create the item information to insert a row. Like the first anomaly, disabling the constraints makes the insert possible but it disables the model’s integrity.

The potential update anomalies may occur when you make any change to an item related column because more than one rental row may hold a copy of the item data. You must therefore update all rows that contain the same item data. This means there is a likelihood of modifying rows incorrectly, and creating inconsistent copies of item data between rows in the sample table.

If you decompose the table into two tables (rental and item), the lossless-join property ensures that any row of the rental table can identify its relationship to the smaller item table. Also, the dependency preservation property ensures that you can maintain a constraint on the original relationship. This means that you can put a foreign key in the rental table that is fully dependent on the item table’s primary key.

A deletion anomaly could occur with this table when you archive rentals that occurred more than six months ago and certain items have not been rental since that time. The deletion of the rental information would remove these items from your inventory. You would decompose the table (also known as a relation) into two tables as describe in the update anomalies section above. It would fix this deletion anomaly too.

First Normal Form (1NF) exists when all columns, or attributes, of a table have a single data type and there are no repeating rows of data, which means rows are unique. This requires that:

• Column data types should be atomic, which means columns shouldn’t have repeating groups, comma delimited groups, or other subatomic parts. This raises a question whether compund variables like Oracle’s arrays, lists, and objects violate first normal form.
• Column names are unique in tables and arbitrarily ordered, which means their order doesn’t impose any constraint on the table.
• Rows in a table have no implicit or explicit ordering required for their access and use.
• Columns shouldn’t contain a repeating group in the context of a candidate key. This means that when you repeat all columns but one or a subset of columns, your model fails to meet the definition of first normal form.

Moreover, first normal form modeling finds the non-repeating rows and moves them from a base table to their own table. It removes all trace of the non-repeating rows from the base table. It also puts a foreign key in the new table that points back to the primary key of the base table. The foreign key in the new table is functionally dependent on the primary key in the base table.

SELECT   a.contact_id
,        a.address_id
,        sa.address_line_id
,        sa.street_address
,        a.city
,        a.state_province
,        a.postal_code
FROM     address a INNER JOIN street_address sa
ON       a.address_id = sa.address_id;

CREATE TYPE street_list IS TABLE OF VARCHAR2(30);
/

CREATE TABLE address_book
( address_book_id NUMBER
, street_address  STREET_LIST
, city            VARCHAR2(30)
, state           VARCHAR2(2)
, postal_code     VARCHAR2(10))
NESTED TABLE street_address STORE AS street_table;

INSERT INTO address VALUES
( 1, street_list('203 North La Salle','Suite 1400'), 'Chicago', 'IL', '60601');
INSERT INTO address VALUES
( 2, street_list('203 North La Salle','Suite 1800'), 'Chicago', 'IL', '60601');

-- Conditionally drop the table.
BEGIN
  FOR i IN (SELECT   TABLE_NAME
            FROM     user_tables
            WHERE    TABLE_NAME IN ('ADDRESS')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
END;
/
 
-- Create the address table.
CREATE TABLE address
( address_id                  NUMBER        CONSTRAINT pk_ad_1  PRIMARY KEY
, contact_id                  NUMBER        CONSTRAINT nn_ad_1  NOT NULL
, address_type                NUMBER        CONSTRAINT nn_ad_2  NOT NULL
, street_address              VARCHAR2(60)  CONSTRAINT nn_ad_3  NOT NULL
, city                        VARCHAR2(30)  CONSTRAINT nn_ad_4  NOT NULL
, state_province              VARCHAR2(30)  CONSTRAINT nn_ad_5  NOT NULL
, postal_code                 VARCHAR2(20)  CONSTRAINT nn_ad_6  NOT NULL);
 
-- Insert a row with a comma delimited street address.
INSERT INTO address VALUES
( 2001, 2001, 1008,'1111 Broadway, Suite 500, MS-5045','Oakland','CA','94604');
 
-- Query the data.
SELECT   street_address
,        city
,        state_province
,        postal_code
FROM     address;

-- Conditionally drop the table.
BEGIN
  FOR i IN (SELECT   TABLE_NAME
            FROM     user_tables
            WHERE    TABLE_NAME IN ('ADDRESS')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
END;
/
 
-- Create the address table.
CREATE TABLE address
( address_id                  NUMBER
, address_line_id             NUMBER
, contact_id                  NUMBER        CONSTRAINT nn_ad_1  NOT NULL
, address_type                NUMBER        CONSTRAINT nn_ad_2  NOT NULL
, street_address              VARCHAR2(60)  CONSTRAINT nn_ad_3  NOT NULL
, city                        VARCHAR2(30)  CONSTRAINT nn_ad_4  NOT NULL
, state_province              VARCHAR2(30)  CONSTRAINT nn_ad_5  NOT NULL
, postal_code                 VARCHAR2(20)  CONSTRAINT nn_ad_6  NOT NULL
, CONSTRAINT pk_ad_1 PRIMARY KEY (address_id,address_line_id));
 
-- Insert data into table.
INSERT INTO address VALUES
( 2001, 1, 2001, 1008,'1111 Broadway','Oakland','CA','94604');
INSERT INTO address VALUES
( 2001, 2, 2001, 1008,'Suite 500','Oakland','CA','94604');
INSERT INTO address VALUES
( 2001, 3, 2001, 1008,'MS-5045','Oakland','CA','94604');
 
-- Query the table.
SELECT   contact_id
,        address_id
,        address_line_id
,        street_address
,        city
,        state_province
,        postal_code
FROM     address;

-- Conditionally drop the table.
BEGIN
  FOR i IN (SELECT   TABLE_NAME
            FROM     user_tables
            WHERE    TABLE_NAME IN ('ADDRESS','STREET_ADDRESS')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
END;
/
 
-- Create address and street_address tables.
CREATE TABLE address
( address_id                  NUMBER        CONSTRAINT pk_ad1  PRIMARY KEY
, contact_id                  NUMBER        CONSTRAINT nn_ad1  NOT NULL
, address_type                NUMBER        CONSTRAINT nn_ad2  NOT NULL
, city                        VARCHAR2(30)  CONSTRAINT nn_ad4  NOT NULL
, state_province              VARCHAR2(30)  CONSTRAINT nn_ad5  NOT NULL
, postal_code                 VARCHAR2(20)  CONSTRAINT nn_ad6  NOT NULL);
 
CREATE TABLE street_address
( street_address_id           NUMBER        CONSTRAINT pk_sa1  PRIMARY KEY
, address_id                  NUMBER        CONSTRAINT nn_sa1  NOT NULL
, address_line_id             NUMBER        CONSTRAINT nn_sa2  NOT NULL
, street_address              VARCHAR2(60)  CONSTRAINT nn_sa3  NOT NULL
, CONSTRAINT fk_sa1 FOREIGN KEY(address_id) REFERENCES address(address_id));
 
-- Insert data.
INSERT INTO address VALUES
( 2001, 1, 1008,'Oakland','CA','94604');
INSERT INTO street_address VALUES
( 3001, 2001, 1,'1111 Broadway');
INSERT INTO street_address VALUES
( 3002, 2001, 2,'Suite 500');
INSERT INTO street_address VALUES
( 3003, 2001, 3,'MS-5045');
 
-- Query the data set to demonstrate repeating and non-repeating rows.
SELECT   a.contact_id
,        a.address_id
,        sa.address_line_id
,        sa.street_address
,        a.city
,        a.state_province
,        a.postal_code
FROM     address a INNER JOIN street_address sa
ON       a.address_id = sa.address_id;

-- Conditionally drop the table.
BEGIN
  FOR i IN (SELECT   TABLE_NAME
            FROM     user_tables
            WHERE    TABLE_NAME IN ('ADDRESS')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
END;
/
 
-- Create the address table.
CREATE TABLE address
( address_id                  NUMBER        CONSTRAINT pk_ad_1  PRIMARY KEY
, contact_id                  NUMBER        CONSTRAINT nn_ad_1  NOT NULL
, address_type                NUMBER        CONSTRAINT nn_ad_2  NOT NULL
, street_address1             VARCHAR2(20)  CONSTRAINT nn_ad_3  NOT NULL
, street_address2             VARCHAR2(20)  CONSTRAINT nn_ad_4  NOT NULL
, street_address3             VARCHAR2(20)  CONSTRAINT nn_ad_5  NOT NULL
, city                        VARCHAR2(30)  CONSTRAINT nn_ad_6  NOT NULL
, state_province              VARCHAR2(30)  CONSTRAINT nn_ad_7  NOT NULL
, postal_code                 VARCHAR2(20)  CONSTRAINT nn_ad_8  NOT NULL);
 
-- Insert a row with atomic column values.
INSERT INTO address VALUES
( 2001, 2001, 1008,'1111 Broadway','Suite 500','MS-5045','Oakland','CA','94604');
 
-- Query the data.
SELECT   street_address1
,        street_address2
,        street_address3
,        city
,        state_province
,        postal_code
FROM     address;

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.

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.

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.

-- 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));

-- 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);

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);