21 May IT DB Normalization
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.
You may find this resource helpful: … http://www.orafaq.com/wiki/Normalization
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.
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Writedemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.