A data warehouse of a bus company contains information about bus segments. It consists of six dimensions, namely, departure station, arrival station, trip, bus, arrival time, and departure time. It also has three measures, namely, number of passengers, duration, and distance (number of kilometers). Draw a Multi-Dimensional schema for the bus data warehouse.

ANS

Multi-Dimensional Schema for Bus Data Warehouse

Dimensions:

  • Departure Station: (StationID, StationName, City, State)
  • Arrival Station: (StationID, StationName, City, State)
  • Trip: (TripID, RouteID, Date, StartTime, EndTime)
  • Bus: (BusID, Model, Capacity)
  • Arrival Time: (Hour, Minute)
  • Departure Time: (Hour, Minute)

Measures:

  • Number of Passengers: (Integer)
  • Duration: (Minutes)
  • Distance: (Kilometers)

Relationships:

  • Departure Station: Related to Trip (One-to-Many)
  • Arrival Station: Related to Trip (One-to-Many)
  • Trip: Related to Bus (Many-to-One)
  • Trip: Related to Departure Time (One-to-One)
  • Trip: Related to Arrival Time (One-to-One)

Facts Table:

Fact_BusSegments (
    TripID INT PRIMARY KEY,
    DepartureStationID INT,
    ArrivalStationID INT,
    BusID INT,
    NumberPassengers INT,
    Duration INT,
    Distance INT,
    FOREIGN KEY (DepartureStationID) REFERENCES Dim_DepartureStation (StationID),
    FOREIGN KEY (ArrivalStationID) REFERENCES Dim_ArrivalStation (StationID),
    FOREIGN KEY (TripID) REFERENCES Dim_Trip (TripID),
    FOREIGN KEY (BusID) REFERENCES Dim_Bus (BusID)
)

Dimension Tables:

  • Dim_DepartureStation: (StationID INT PRIMARY KEY, StationName VARCHAR(50), City VARCHAR(50), State VARCHAR(50))
  • Dim_ArrivalStation: (StationID INT PRIMARY KEY, StationName VARCHAR(50), City VARCHAR(50), State VARCHAR(50))
  • Dim_Trip: (TripID INT PRIMARY KEY, RouteID INT, Date DATE, StartTime TIME, EndTime TIME)
  • Dim_Bus: (BusID INT PRIMARY KEY, Model VARCHAR(50), Capacity INT)
  • Dim_ArrivalTime: (Hour INT, Minute INT)
  • Dim_DepartureTime: (Hour INT, Minute INT)

Key features of the schema:

  • Star Schema: This schema uses a central fact table surrounded by multiple dimension tables, simplifying data queries and analysis.
  • Normalized Dimensions: Each dimension table is normalized to avoid data redundancy and improve data integrity.
  • Foreign Keys: Foreign keys enforce relationships between the tables, ensuring data consistency.

This multi-dimensional schema effectively organizes the bus data warehouse, allowing for efficient analysis of bus segments based on various dimensions and measures.

Leave a Comment