The document outlines the design of data warehouse schemas for recording student grades across various courses and semesters. It includes a detailed explanation of star and snowflake schemas, as well as fact and factless fact tables, which are essential for organizing and analyzing educational data. The focus is on creating efficient structures for querying and reporting, making it suitable for university administrators and data analysts. Key components include dimensions such as course-section, professor, student, and period, along with the fact table that records course grades.

Key Points

  • Explains star schema design for student grade tracking in universities.
  • Details snowflake schema with normalized dimension tables for data efficiency.
  • Includes fact and factless fact tables for comprehensive attendance tracking.
  • Covers dimensions like course-section, professor, student, and period.
Deepmalika Das
7 pages
Language:English
Type:Study Guide
Accounting
Deepmalika Das
7 pages
Language:English
Type:Study Guide
Accounting
205
/ 7
ASSIGNMENT 4
Deepmalika Das/T11/21
AIM:
Design a DWH Schema for recording grades of students enrolled in various courses of
various semesters for a university. Design Star schema, Snowflake schema and
Factless Fact table for recording their attendance.
THEORY:
A data warehouse is a centralized system used to store and organize large amounts of
data for analysis and reporting. It supports decision-making by separating analytical
processing from daily transactions. Data in a warehouse is structured using tables, such
as fact and dimension tables, to make querying efficient and meaningful.
Star Schema
Star Schema is a commonly used multidimensional model in data warehousing. It
consists of a central fact table connected directly to multiple dimension tables, forming a
star-like structure. The fact table stores quantitative data, while dimension tables store
descriptive attributes. This schema uses fewer foreign-key joins, making queries simpler
and faster. Due to its simple design and high query performance, star schema is widely
used for reporting and analytical
purposes.
Snowflake Schema
Snowflake Schema is a multidimensional model that is an extension of the star schema.
In this schema, dimension tables are further normalized into subdimension tables,
creating a snowflakeshaped structure. It includes fact tables, dimension tables, and
sub-dimension tables. While this design reduces data redundancy and saves storage
space, it requires more joins, which can make queries more complex and slower
compared to star schema.
Fact Table
A fact table is the central table in a data warehouse that stores measurable and
quantitative business data. It contains numerical values such as sales amount, quantity
sold, or profit, along with foreign keys that reference dimension tables. Fact tables
support calculations, trend analysis, and performance measurement across different
dimensions like time, product, or location.
Factless Fact Table
A factless fact table is a special type of fact table that does not contain numerical
measures. Instead, it stores only foreign keys to dimension tables to record the
occurrence or non occurrence of events. The presence of a record indicates that an
event happened, while the absence indicates it did not. Factless fact tables are
commonly used to track activities such as attendance, enrollment, eligibility, or
participation. star schema and snowflake schema provide structured approaches for
organizing data in a data warehouse, each serving different analytical needs. Fact
tables and factless fact tables further support data analysis by capturing measurable
values and event-based information. Together, these components form the foundation of
efficient data storage, querying, and decision support in modern data warehousing
systems.
QUERY:
The Mumbai University wants you to help design a star schema to record grades for
course completed by students. There are four dimensional tables namely
course-section, professor, student, period with attributes as:-
1. Course-section - course_id, section_no, course_name, units, room_id,
room_capacity. During a given semester the college offers an average of 500 course
sections.
2. Professor : prof_id, prof_name, title, dept_id, dept_name.
3. Student : student_id, student_name, major. Each course section has an average of
60 students.
4. Period : semester_id, year. The database will contain data for 30 months periods.
The only fact that is to be recorded in the fact table is course grade.
/ 7
End of Document
205

You May Also Like

FAQs

What is a star schema in data warehousing?
A star schema is a multidimensional model used in data warehousing that consists of a central fact table connected directly to multiple dimension tables. This structure allows for simpler and faster queries, making it ideal for reporting and analytical purposes. The fact table contains quantitative data, while dimension tables store descriptive attributes, creating a star-like formation. This schema is widely used in various applications, including educational institutions for tracking student performance.
How does a snowflake schema differ from a star schema?
A snowflake schema is an extension of the star schema that normalizes dimension tables into sub-dimension tables, resulting in a more complex structure. While it reduces data redundancy and saves storage space, it requires more joins, which can make queries slower compared to the star schema. This design is beneficial for organizations that prioritize data integrity and storage efficiency over query performance. In educational contexts, it can be used to manage detailed student and course information.
What are fact and factless fact tables?
Fact tables are central tables in a data warehouse that store measurable and quantitative business data, such as student grades or attendance records. They contain numerical values along with foreign keys referencing dimension tables. In contrast, factless fact tables do not contain numerical measures; instead, they record the occurrence or non-occurrence of events, such as attendance. Both types of tables are crucial for comprehensive data analysis and reporting in educational settings.
What dimensions are included in the student grade tracking schema?
The student grade tracking schema includes several key dimensions: course-section, which details the course ID, section number, and room capacity; professor, which contains information about the instructor's ID and department; student, which tracks student IDs and majors; and period, which records semester and year data. These dimensions provide a structured approach to analyzing student performance across various courses and semesters.

Related