CS614 Data Warehousing Assignment 2 Solution Fall 2012

Analyze the scenario given below and draw a Star Schema.Virtual University of Pakistan is going to develop a DWH for its Accounts branch. The officials of accounts branch want the reports regarding ‘deposited fees amount ‘and ‘fine’ on the basis of following.


Student (Student-ID, Name)

Campus (Campus -ID, Name)

Degree Program (Degree Program-ID, Name, Duration, Credit Hours)

Time (Time-ID, Date, Week, Month, Quarter, Year)

Bank  (Bank-ID, Name, Branch Name, Branch Code)
Location (Location-ID, Zone, City, Province)


Download Solution here solutionfile


A dimensional modelling technique in which a detail fact table is linked to dimension tables.

The data in data warehouses and data marts is accessed by end-users.  The information contained in the data warehouse/data mart must be easy for the end-user to use and access.  Denormalized designs are easier for end-users to use than highly normalized designs, however these designs are more difficult to design and maintain.

The Star Schema diagram graphically models the end-user’s view (i.e., the denormalized view) of how the information is accessed.

Components of a Star Schema Diagram

The diagram has three main components:

·          Fact Table and its contents:  metric attributes and the foreign keys necessary to join to the dimension tables,

·          Dimension Tables and their contents:  reference attributes, hierarchical attributes, and metric attributes. The dimension tables are highly denormalized,

·          the lines that link the Dimension Tables to the Fact Table.

Star Schema Model

This diagram is a model of a star schema diagram.


Star Schema Example


The following is an example of a star schema for sales items.

Student is the fact table.

The other tables are dimension tables.