Assignment Description

AppleEye College wants you, Michael Jones, a senior database analyst of CompanyEliteOne, to help them design a solution for recording grades for courses completedby students.

1. You are tasked to come up with a solution to this problem by addressing the

following (at a minimum) in a formal memo [Word document] (that adheres to

proper APA standards) and PowerPoint presentation [with speaker notes or

notes at bottom of each slide] to the database supervisor, Joe Craft, and the IT

manager, Lauren Swisher, both of AppleEye College:

a) Discuss how this situation specifically calls for the use of a data warehouse,

explaining the main characteristics of a data warehouse and how each applies to thisscenario specifically.

b) Discuss how using a star schema will address this task by defining what a star schemais, including the use of facts, dimensions, and attributes. Identify the facts,

dimensions and attributes for this situation specifically. There are to be four

dimension tables in the data model with attributes and data estimates as follows:

 COURSE_SECTION ( Crs_Sec_ID, Course_ID, Section_Number,

Course_Number, Course_Name, Units, Room_ID, Room_Capacity )

- During a given semester the college offers an average of 500 course

sections.

 PROFESSOR ( Professor_ID, Prof_Name, Title, Department_ID, Dept_Name )

 STUDENT ( Student_ID, Student_Name, Major )

- Each course section has an average of 40 students

 PERIOD ( Period_ID, Semester_ID, Year )

- The database will contain data for 30 periods (a total of 10 years)

The only fact that is to be recorded in the fact table is Course_Grade. Design a

specific star schema for this situation. See Figure 13.10 on page 564 in your text

for the format you should follow.

c) Estimate the number of rows in the fact table, using the assumptions stated

above.

d) Estimate the total size of the fact table (in bytes), assuming that each field has

an average of 5 bytes.

e) Various characteristics of course sections, professors, and students will change

over time. How do you propose designing the star schema to allow for these

changes? Why?

f) Discuss attribute hierarchies and how attributes within dimensions can be

ordered to provide a top-down data organization. Specify such a hierarchy in one

or more of the dimensions for this scenario.

g) Discuss the four most common performance improvement techniques used in

star schemas and how one or more of these applies to this scenario in detail.

2. Submit your formal memo (Word document) and PowerPoint presentation in a zipfile to this learning object by the deadline stated above.