9-28. Millennium College wants you to help design a star schema to record grades
ID: 3788247 • Letter: 9
Question
9-28. Millennium College wants you to help design a star schema to record grades for courses completed by students. There are four dimension tables, with attributes as follows:
CourseSection Attributes: CourseID, SectionNumber, CourseName, Units, RoomID, and RoomCapacity. During a given semester, the college offers an average of 500 course sections.
Professor Attributes: ProfID, ProfName, Title, DepartmentID, and DepartmentName. There are typically 200 professors at Millennium at any given time.
Student Attributes: StudentID, StudentName, and Major. Each course section has an average of 40 students, and students typically take five courses per period.
Period Attributes: SemesterID and Year. The database will contain data for 30 periods (a total of 10 years).
1. The only fact that is to be recorded in the fact table is CourseGrade.
a. Design a star schema for this problem. See Figure 9-10 for the format you should follow below
b. Estimate the number of rows in the fact table, using the assumptions stated previously.
c. Estimate the total size of the fact table (in bytes), assuming that each field has an average of 5 bytes.
d. If you didn’t want to or didn’t have to stick with a strict star schema for this data mart, how would you change the design? Why?
e. Various characteristics of sections, professors, and students change over time. How do you propose designing the star schema to allow for these changes? Why?
f. Having mastered the principles of normalization you recognize immediately that the star schema you developed for Millennium College is not in third normal form. Using these principles, convert the star schema to a snowflake schema. What impact (if any) does this have on the size of the fact table for this problem?
Explanation / Answer
Solutions
a.)
b) Estimate the number of rows in the fact table, using the assumptions stated previously
Course: 500 rows
Professor: 200 rows
Student: 40 x 5 = 200
Period: 2 rows x 30 = 60
Total rows=960
c) Course: 500 rows x 6 colums = 3,000 fields
Professor: 200 rows x 5 columns = 1000 fields
Student: 200 rows x 3 columns = 600 fields
Period: 2 rows x 2 columns x 30 = 120 fields
Total Fields = 4720 x 5 bytes = 23,600
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.