Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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,

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

  1. 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.

  1. 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.

  1. 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:

  1. 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.

  1. Execute the two queries shown below.

  1. 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.

  1. 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.

  1. 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.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Formal SQL Tuning For Oracle Databases Practical Efficiency Efficient Practice

Authors: Leonid Nossov ,Hanno Ernst ,Victor Chupis

1st Edition

3662570564, 978-3662570562

More Books

Students also viewed these Databases questions