Normalization is a process for evaluation and correcting table structures to minimize data redundancies. - Good relational database implementation requires that all tables be at least in third norm form (3NF).
Denormalization produces a lower normal form - i.e. Denormalized table structures in 3NF are converted to a 2NF.
Consider the PROSPECT table that supports a large online university. Then consider the resulting tables from normalizing PROSPECT.
The PROSPECT table:
CREATE TABLE PROSPECT
( PROSPECT_ID NUMBER(8,0),
SALUTATION VARCHAR2(5 BYTE),
FIRST_NAME VARCHAR2(25 BYTE),
LAST_NAME VARCHAR2(25 BYTE),
STREET_ADDRESS VARCHAR2(50 BYTE),
ZIP VARCHAR2(5 BYTE),
PHONE VARCHAR2(15 BYTE),
EMAIL VARCHAR2(25 BYTE),
EMPLOYER VARCHAR2(50 BYTE),
REGISTRATION_DATE DATE,
CREATED_BY VARCHAR2(30 BYTE),
CREATED_DATE DATE,
MODIFIED_BY VARCHAR2(30 BYTE),
MODIFIED_DATE DATE) ;
ALTER TABLE PROSPECT ADD CONSTRAINT PROS_PK PRIMARY KEY (PROSPECT_ID);
The resulting tables from normalizing PROSPECT:
CREATE TABLE ProspectName AS (SELECT PROSPECT_ID, SALUTATION, FIRST_NAME, LAST_NAME, FROM Prospect);
CREATE TABLE ProspectEmail AS (SELECT PROSPECT_ID, EMAIL FROM Prospect);
CREATE TABLE ProspectAddress AS (SELECT PROSPECT_ID, STREET_ADDRESS, ZIP FROM Prospect);
CREATE TABLE ProspectPhone AS (SELECT PROSPECT_ID, PHONE FROM Prospect);
CREATE TABLE ProspectEmployer AS (SELECT PROSPECT_ID, EMPLOYER FROM Prospect);
CREATE TABLE ProspectAudit AS (SELECT PROSPECT_ID, REGISTRATION_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY,
MODIFIED_DATE FROM Prospect);
ALTER TABLE ProspectName ADD CONSTRAINT PROS_PK PRIMARY KEY (PROSPECT_ID);
ALTER TABLE ProspectEmail ADD CONSTRAINT PROS_PK PRIMARY KEY (PROSPECT_ID);
ALTER TABLE ProspectAddress ADD CONSTRAINT PROS_PK PRIMARY KEY (PROSPECT_ID);
ALTER TABLE ProspectPhone ADD CONSTRAINT PROS_PK PRIMARY KEY (PROSPECT_ID);
ALTER TABLE ProspectEmployer ADD CONSTRAINT PROS_PK PRIMARY KEY (PROSPECT_ID);
ALTER TABLE ProspectAudit ADD CONSTRAINT PROS_PK PRIMARY KEY (PROSPECT_ID);
The resulting tables from normalizing PROSPECT are small tables that can enable fast update of individual data items. These small tables consist of a large number of narrow tables maybe become fragmented on a disk. Joining data from several small tables can incur a noticeable I/O overhead while disk heads seek from one location to another.
Partial Denormalization of tables (combining them together) can help to alleviate some of these concerns. Data that is modified rarely but is read often will benefit from being denormalized, while data that is subject to frequency changes may be better left normalized.
As you complete the tasks below, place your answers or comments on this document. After you complete all the tasks, write a summary of your answers and comments and post the summary to the discussion. Attach this document containing your answers and comments to your discussion posting.
1. Explain how the normalized tables resulted from the PROSPECT table.
Example: Query 1: CREATE TABLE ProspectName AS (SELECT PROSPECT_ID, SALUTATION, FIRST_NAME, LAST_NAME, FROM Prospect);
Query 1 uses a subquery construction to create a new table name ProspectName. ProspectName has the attributes characterizing the "name" of a student prospect. All attributes are atomic and the primary key PROSPECT_ID uniquely identifies the name of the student prospect.
2. Suggest an alternative to the normalized tables given and provide an explanation for your alternative. Consider how the data will be used.
3. List at least three resources in APA style that you used to complete the tasks.