1. Consider the PROSPECT table that supports a large online university.

CREATE TABLE PROSPECT

AS (SELECT STUDENT_ID AS PROSPECT_ID,

SALUTATION,

FIRST_NAME,

LAST_NAME ,

STREET_ADDRESS,

ZIP,

PHONE,

EMPLOYER,

REGISTRATION_DATE,

CREATED_BY,

CREATED_DATE,

MODIFIED_BY,

MODIFIED_DATE,

FIRST_NAME||'.'||LAST_NAME||'@UMUC.EDU' AS EMAIL FROM STUDENT);

ALTER TABLE PROSPECT ADD CONSTRAINT PROS_PK PRIMARY KEY (PROSPECT_ID);

2. Next, consider the resulting tables from denormalizing PROSPECT. The PROSPECT table:

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

The resulting tables from normalizing PROSPECT are small tables that can enable the fast update of individual data items. These small tables consist of a large number of narrow tables that 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.

3. Study the construction of CREATE TABLE statements listed above. Observe that statements use a subquery construction to create the new tables.

Yes these are observed.

4. Create and populate the PROSPECT table from the STUDENT table of the STUDENT database. You can the constructions above.

1. Insert one or more screen clipping in the space below to show evidence you created and populated the tables: PROSPECT, ProspectName , ProspectEmail, ProspectAddress, ProspectPhone, ProspectEmployer, and ProspectAudit

Screen clippings:

5. Add a primary key constraint to all the tables as necessary. Add a foreign key constraint to the small tables. The foreign key references the PROSPECT_ID attribute in the PROSPECT table.

6. Execute the two queries shown below.

7. Compare the performance of the two queries by analyzing their Execution Plans. Explain the result set of each query are they the same or are they different. Explain how you used the execution plan of each query in order to evaluate the performance of each query.

8. Select at least two topics from resources given in this document (or any other resource) and discuss if you can use them to further compare the performance of the two queries.

9. Create one or more indexes on the tables used in queries 1 and 2. Determine if they had an effect on the performance or execution of the queries. Describe the indexes and describe your findings. Explain how you used the execution plan of each query in order to evaluate the effect of the indexes.