De-normalizing Database - Exercise

Implementing Logical Database Performance Design

Objective: Create a redundant database design from a non-redundant design.

Exercise Scoring

You will receive 5 points for this exercise. The exercise is auto-scored; when you have completed the exercise, click the OK, I'm done button to receive full credit.

Overview

In this exercise we will take a third-normal form database design for a University and duplicate data columns to improve SQL query performance. The entity-relationship model for Whattsamatt U is shown below:

entity-relationship model — Entity Relationship Model

Here we see tables for students, courses, professors, and registrations. During your analysis you have discovered that more than 80% of the queries against this database will be producing student course grade sheets. The listing looks like this:

Student name	course number	course name	credit hours	instructor	grade
Juarez	cs-101	Intro to CS	4	Barton	A
Smith	psy-101	Intro Psych	3	Dennison	C
Martin	psy-101	Intro Psych	3	Dennison	B+

Instructions

Create a new set of table definitions that copy information such that Oracle would be able to provide this report without incurring any table joins. Describe the new set of table definitions in the textbox below.