CREATE DATABASE StudentDW;
USE StudentDW;
Location Dimension
CREATE TABLE Dim_Location
(
Location_ID INT PRIMARY KEY,
City VARCHAR(50),
State VARCHAR(50),
Country VARCHAR(50)
);
Department
Dimension
CREATE TABLE Dim_Department
(
Department_ID INT PRIMARY KEY,
Department_Name VARCHAR(50),
Location_ID INT,
FOREIGN KEY (Location_ID)
REFERENCES Dim_Location(Location_ID)
);
Student Dimension
CREATE TABLE Dim_Student
(
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(100),
Gender VARCHAR(10),
Age INT,
Department_ID INT,
FOREIGN KEY (Department_ID)
REFERENCES Dim_Department(Department_ID)
);
Course Dimension
CREATE TABLE
Dim_Course
(
Course_ID INT PRIMARY KEY,
Course_Name VARCHAR(100),
Department_ID INT,
Credits INT,
FOREIGN KEY (Department_ID)
REFERENCES Dim_Department(Department_ID)
);
Semester Dimension
CREATE TABLE Dim_Semester
(
Semester_ID INT PRIMARY KEY,
Semester_Name VARCHAR(20),
Academic_Year VARCHAR(20)
);
Create Fact Table
CREATE TABLE
Fact_Result
(
Result_ID INT PRIMARY KEY,
Student_ID INT,
Course_ID INT,
Semester_ID INT,
Marks INT,
Grade CHAR(2),
FOREIGN KEY (Student_ID)
REFERENCES Dim_Student(Student_ID),
FOREIGN KEY (Course_ID)
REFERENCES Dim_Course(Course_ID),
FOREIGN KEY (Semester_ID)
REFERENCES Dim_Semester(Semester_ID)
);
Insert
Sample Data
Location
INSERT INTO
Dim_Location VALUES
(1,'Hyderabad','Telangana','India'),
(2,'Vijayawada','Andhra
Pradesh','India'),
(3,'Bangalore','Karnataka','India');
Department
INSERT INTO
Dim_Department VALUES
(101,'Computer
Science',1),
(102,'Information
Technology',2),
(103,'Electronics',3);
Student
INSERT INTO
Dim_Student VALUES
(1001,'Rahul','Male',20,101),
(1002,'Sneha','Female',21,102),
(1003,'Arjun','Male',22,101),
(1004,'Priya','Female',20,103);
Course
INSERT INTO
Dim_Course VALUES
(201,'Data
Mining',101,4),
(202,'Machine
Learning',101,4),
(203,'Database
Systems',102,3),
(204,'Digital
Electronics',103,4);
Semester
INSERT INTO
Dim_Semester VALUES
(1,'Semester-1','2024-25'),
(2,'Semester-2','2024-25');
Fact Table
INSERT INTO
Fact_Result VALUES
(1,1001,201,1,89,'A'),
(2,1001,202,2,91,'A'),
(3,1002,203,1,82,'B'),
(4,1003,201,1,95,'A'),
(5,1004,204,2,76,'C');
Fact Constellation
Schema
CREATE DATABASE
HospitalDW;
USE HospitalDW;
Hospital Dimension
CREATE TABLE
Dim_Hospital
(
Hospital_ID INT PRIMARY KEY,
Hospital_Name VARCHAR(100),
City VARCHAR(50),
State VARCHAR(50)
);
Department
Dimension
CREATE TABLE
Dim_Department
(
Department_ID INT PRIMARY KEY,
Department_Name VARCHAR(50),
Hospital_ID INT,
FOREIGN KEY(Hospital_ID)
REFERENCES Dim_Hospital(Hospital_ID)
);
Doctor
Dimension
CREATE TABLE
Dim_Doctor
(
Doctor_ID INT PRIMARY KEY,
Doctor_Name VARCHAR(100),
Specialization VARCHAR(50),
Department_ID INT,
FOREIGN KEY(Department_ID)
REFERENCES Dim_Department(Department_ID)
);
Patient Dimension
CREATE TABLE
Dim_Patient
(
Patient_ID INT PRIMARY KEY,
Patient_Name VARCHAR(100),
Gender VARCHAR(10),
Age INT,
Blood_Group VARCHAR(5)
);
Date Dimension
CREATE TABLE
Dim_Date
(
Date_ID INT PRIMARY KEY,
Full_Date DATE,
Month VARCHAR(20),
Quarter VARCHAR(10),
Year INT
);
Create Fact Tables
Fact Appointment
CREATE TABLE
Fact_Appointment
(
Appointment_ID INT PRIMARY KEY,
Patient_ID INT,
Doctor_ID INT,
Date_ID INT,
Consultation_Fee DECIMAL(10,2),
FOREIGN KEY(Patient_ID)
REFERENCES Dim_Patient(Patient_ID),
FOREIGN KEY(Doctor_ID)
REFERENCES Dim_Doctor(Doctor_ID),
FOREIGN KEY(Date_ID)
REFERENCES Dim_Date(Date_ID)
);
Fact Billing
CREATE TABLE
Fact_Billing
(
Bill_ID INT PRIMARY KEY,
Patient_ID INT,
Doctor_ID INT,
Date_ID INT,
Total_Amount DECIMAL(10,2),
Insurance_Amount DECIMAL(10,2),
FOREIGN KEY(Patient_ID)
REFERENCES Dim_Patient(Patient_ID),
FOREIGN KEY(Doctor_ID)
REFERENCES Dim_Doctor(Doctor_ID),
FOREIGN KEY(Date_ID)
REFERENCES Dim_Date(Date_ID)
);
Fact Treatment
CREATE TABLE
Fact_Treatment
(
Treatment_ID INT PRIMARY KEY,
Patient_ID INT,
Doctor_ID INT,
Department_ID INT,
Date_ID INT,
Treatment_Cost DECIMAL(10,2),
FOREIGN KEY(Patient_ID)
REFERENCES Dim_Patient(Patient_ID),
FOREIGN KEY(Doctor_ID)
REFERENCES Dim_Doctor(Doctor_ID),
FOREIGN KEY(Department_ID)
REFERENCES Dim_Department(Department_ID),
FOREIGN KEY(Date_ID)
REFERENCES Dim_Date(Date_ID)
);
Insert
Sample Data
Hospital
INSERT INTO
Dim_Hospital VALUES
(1,'City
Hospital','Hyderabad','Telangana'),
(2,'Care
Hospital','Vijayawada','Andhra Pradesh');
Department
INSERT INTO
Dim_Department VALUES
(101,'Cardiology',1),
(102,'Neurology',1),
(103,'Orthopedics',2);
Doctor
INSERT INTO
Dim_Doctor VALUES
(201,'Dr.
Sharma','Cardiologist',101),
(202,'Dr.
Rao','Neurologist',102),
(203,'Dr.
Kumar','Orthopedic',103);
Patient
INSERT INTO
Dim_Patient VALUES
(301,'Rahul','Male',25,'O+'),
(302,'Sneha','Female',30,'A+'),
(303,'Priya','Female',28,'B+');
Date
INSERT INTO
Dim_Date VALUES
(1,'2025-01-10','January','Q1',2025),
(2,'2025-02-15','February','Q1',2025),
(3,'2025-03-20','March','Q1',2025);
Fact Appointment
INSERT INTO
Fact_Appointment VALUES
(1001,301,201,1,500),
(1002,302,202,2,600),
(1003,303,203,3,700);
Fact
Billing
INSERT INTO
Fact_Billing VALUES
(2001,301,201,1,2500,500),
(2002,302,202,2,3200,1000),
(2003,303,203,3,1800,300);
Fact Treatment
INSERT INTO
Fact_Treatment VALUES
(3001,301,201,101,1,2000),
(3002,302,202,102,2,2600),
(3003,303,203,103,3,1500);
Roll-up
Operation
SELECT
Dept.Department_Name,
SUM(T.Treatment_Cost)
AS Total_Cost
FROM
Fact_Treatment T
JOIN
Dim_Department Dept
ON
T.Department_ID=Dept.Department_ID
GROUP
BY Dept.Department_Name;
Drill-down
Operation
SELECT
Dept.Department_Name,
D.Doctor_Name,
P.Patient_Name,
T.Treatment_Cost
FROM
Fact_Treatment T
JOIN
Dim_Doctor D
ON
T.Doctor_ID=D.Doctor_ID
JOIN
Dim_Department Dept
ON
T.Department_ID=Dept.Department_ID
JOIN
Dim_Patient P
ON
T.Patient_ID=P.Patient_ID
ORDER
BY Dept.Department_Name;
Slice Operation
SELECT
P.Patient_Name,
D.Doctor_Name,
T.Treatment_Cost
FROM
Fact_Treatment T
JOIN
Dim_Patient P
ON
T.Patient_ID=P.Patient_ID
JOIN
Dim_Doctor D
ON
T.Doctor_ID=D.Doctor_ID
JOIN
Dim_Department Dept
ON
T.Department_ID=Dept.Department_ID
WHERE
Dept.Department_Name='Cardiology';
Dice Operation
SELECT
P.Patient_Name,
D.Doctor_Name,
Dt.Full_Date,
T.Treatment_Cost
FROM
Fact_Treatment T
JOIN
Dim_Patient P
ON
T.Patient_ID=P.Patient_ID
JOIN
Dim_Doctor D
ON
T.Doctor_ID=D.Doctor_ID
JOIN
Dim_Department Dept
ON
T.Department_ID=Dept.Department_ID
JOIN
Dim_Date Dt
ON
T.Date_ID=Dt.Date_ID
WHERE
Dept.Department_Name='Cardiology'
AND
Dt.Year=2025
AND
T.Treatment_Cost>1800;
Pivot Operation
SELECT
H.Hospital_Name,
SUM(CASE
WHEN
Dept.Department_Name='Cardiology'
THEN
T.Treatment_Cost END) AS Cardiology,
SUM(CASE
WHEN
Dept.Department_Name='Neurology'
THEN
T.Treatment_Cost END) AS Neurology,
SUM(CASE
WHEN
Dept.Department_Name='Orthopedics'
THEN
T.Treatment_Cost END) AS Orthopedics
FROM
Fact_Treatment T
JOIN
Dim_Department Dept
ON
T.Department_ID=Dept.Department_ID
JOIN
Dim_Hospital H
ON
Dept.Hospital_ID=H.Hospital_ID
GROUP
BY H.Hospital_Name;