Performance Tuning   «Prev  Next»

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.


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+


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.