The purpose of this assignment is to practice the design of Data-marts using the
ID: 3719401 • Letter: T
Question
The purpose of this assignment is to practice the design of Data-marts using the Star-Schema.
Directions
In this assignment, we will use the same database design we had for Assignment 4 (copied below). This design depicts the data transactions taking place in a small College. The assignment is to design two DataMarts to help the administration to keep data about Student Performance and Faculty Performance. They should capture the following information:
Student Performance
The college is interested in keeping track of the performance of their students thru time. The time dimension can be obtained by the Semester/Section attributes, since Offerings is the only file that provides certain chronology. For example, Spring 2016 is followed by Summer 2016, that is also followed by Fall 2016. The designer may select a coding of these attributes to enforce the chronology.
Student information should include ID and names.
Students performance can be measured in various ways, and it is up to the designer to decide the one s/he considers the best. Student grades in classes could be used directly as a measure of performance, or an aggregate like GPA could also be used for the same purpose.
The College is interested in following performance per Major and School.
Faculty Performance
The college is also interested in keeping track of faculty performance through time in the second DataMart. The time dimension would be the same as with Student Performance, using information from the Semester/Section attributes.
Faculty information should include ID and names.
The DataMart should be able to answer the question of how many courses each Faculty member taught per semester, and if needed, the names of the courses with the average grade per class.
COURSE CourselDName Department CreditHours FACULTY FirstName LastName FacultylD Position Years of Tenure Salary OFFERINGS OfferinglD Course Semester Section acultylDStuden Grade AssociationlD AssociationName Advisor MEMBER LIST STUDENT Classification School Major AdvisorOOExplanation / Answer
Student Performance
DimTime (Date, Day, Month, Year, Semester)
DimStudent (ID, FirstName, LastName, Classification, School, Major)
StudentMarks (ID,CourseID,Grades)
StudentPerformance (ID, GPA, Semester, Course)
COURSE (CourseID, Name, DEpartment,CreditHours)
FacultyPerformance
Faculty(ID, FirstName, LastName, Position, Tenure, Salary)
FactFacultyPerformance(ID, Semester, Section, CourseID, FacultyID)
Assuming same offerings table is used to fill these data marts
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.