This table includes generic information about every course. It does not include information about offerings, only...

 

Transcribed Image Text:

This table includes generic information about every course. It does not include information about offerings, only courses. 2. Time Dimension: This table uses semesters to mark the pass of time. Because semesters may have names that are not sequential (the database may not know that "Spring 2020" comes before "Fall 2020"), a surrogate TimelD attribute is created with consecutive values to mark the sequence (first semester is 1, second 2, etc.). Assume this table was already created for you and it exists. 3. Faculty Dimension: This table includes all relevant information about a Faculty member. 4. Fact table: contains all primary keys of the dimensions tables and the average grades of all courses offered with the same CourselD during the same semester by the same faculty member The star-schema for this data-mart is as follows: TIME-DIMENSION TimeID Semester FACULTY-DIMENSION FacultyID LastName FirstName FACT-TABLE TimelD CourselD FacultyID Class Grade Average COURSE-DIMENSION CourselD Name CreditHours Directions Answer the following questions: 1. Write a SQL SELECT command to create the Faculty dimension table for the data-mart. You may only use the tables from the original transactional database for the small college. 2. Write a SQL SELECT command to create the Course dimension table for the data-mart. You may only use the tables from the original transactional database for the small college. 3. Write a SQL SELECT command to create the Fact-Table for the data-mart. You may only use the tables from the original transactional database for the small college and the Time dimension from the data-mart (assume it was already created). 4. Design and draw a star-schema for a new data-mart that will keep track of the grades made by students in courses. This data-mart will have the same Time and Course Dimensions that the first data-mart shows, but it will also have a new Student dimension with information about a Student instead of the Faculty dimension. This information includes StudentID. names, major, and school. The Fact-Table will also change and it will hold the grade obtained by a student in a course in a semester. 5. Write a SQL SELECT command to create the Student dimension table for the new data-mart you designed. You may only use the tables from the original transactional database for the small college. 6. Write a SQL SELECT command to create the Fact-Table for the new data-mart you designed. You may only use the tables from the original transactional database for the small college and the Time dimension from the data-mart (assume it was already created). This table includes generic information about every course. It does not include information about offerings, only courses. 2. Time Dimension: This table uses semesters to mark the pass of time. Because semesters may have names that are not sequential (the database may not know that "Spring 2020" comes before "Fall 2020"), a surrogate TimelD attribute is created with consecutive values to mark the sequence (first semester is 1, second 2, etc.). Assume this table was already created for you and it exists. 3. Faculty Dimension: This table includes all relevant information about a Faculty member. 4. Fact table: contains all primary keys of the dimensions tables and the average grades of all courses offered with the same CourselD during the same semester by the same faculty member The star-schema for this data-mart is as follows: TIME-DIMENSION TimeID Semester FACULTY-DIMENSION FacultyID LastName FirstName FACT-TABLE TimelD CourselD FacultyID Class Grade Average COURSE-DIMENSION CourselD Name CreditHours Directions Answer the following questions: 1. Write a SQL SELECT command to create the Faculty dimension table for the data-mart. You may only use the tables from the original transactional database for the small college. 2. Write a SQL SELECT command to create the Course dimension table for the data-mart. You may only use the tables from the original transactional database for the small college. 3. Write a SQL SELECT command to create the Fact-Table for the data-mart. You may only use the tables from the original transactional database for the small college and the Time dimension from the data-mart (assume it was already created). 4. Design and draw a star-schema for a new data-mart that will keep track of the grades made by students in courses. This data-mart will have the same Time and Course Dimensions that the first data-mart shows, but it will also have a new Student dimension with information about a Student instead of the Faculty dimension. This information includes StudentID. names, major, and school. The Fact-Table will also change and it will hold the grade obtained by a student in a course in a semester. 5. Write a SQL SELECT command to create the Student dimension table for the new data-mart you designed. You may only use the tables from the original transactional database for the small college. 6. Write a SQL SELECT command to create the Fact-Table for the new data-mart you designed. You may only use the tables from the original transactional database for the small college and the Time dimension from the data-mart (assume it was already created).