In today's digital age, databases are the backbone of nearly every major system we interact with daily. From online banking to healthcare systems, databases enable structured data storage, retrieval, and processing at an unprecedented scale.
Understanding how to design and implement database solutions for real-world applications is a critical skill for database professionals. This lecture bridges the gap between theoretical database concepts and practical implementation.
The systematic approach to designing and implementing a database system:
Essential for maintaining data quality and consistency:
Critical considerations for growing applications:
Define the real-world scenario and identify stakeholders.
Stakeholders: Doctors, Patients, Admin Staff, Pharmacists
Key Requirements: Patient records, Appointments, Billing, Doctor schedules
Gather both functional and non-functional requirements.
Create an Entity-Relationship diagram to represent the data model.
Convert the ER model into a relational schema.
-- Patient Table
CREATE TABLE Patient (
PatientID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
DOB DATE,
Contact VARCHAR(50),
Address TEXT,
InsuranceID VARCHAR(50)
);
-- Doctor Table
CREATE TABLE Doctor (
DoctorID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Specialization VARCHAR(100),
Contact VARCHAR(50),
Department VARCHAR(50)
);
-- Appointment Table
CREATE TABLE Appointment (
AppID INT PRIMARY KEY,
PatientID INT,
DoctorID INT,
AppDate DATE NOT NULL,
AppTime TIME NOT NULL,
Status VARCHAR(20) DEFAULT 'Scheduled',
FOREIGN KEY (PatientID) REFERENCES Patient(PatientID),
FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID)
);
Make implementation decisions for performance and scalability.
-- Customer Table
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
JoinDate DATE DEFAULT CURRENT_DATE,
LastLogin DATETIME
);
-- Product Table
CREATE TABLE Product (
ProductID INT PRIMARY KEY,
Name VARCHAR(200) NOT NULL,
Description TEXT,
Price DECIMAL(10,2) NOT NULL,
StockQuantity INT DEFAULT 0,
CategoryID INT
);
-- Order Table
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATETIME DEFAULT CURRENT_TIMESTAMP,
Status VARCHAR(50),
TotalAmount DECIMAL(12,2),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
-- Order_Item Table
CREATE TABLE Order_Item (
OrderID INT,
ProductID INT,
Quantity INT NOT NULL,
UnitPrice DECIMAL(10,2) NOT NULL,
PRIMARY KEY (OrderID, ProductID),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);
-- Add a new product
INSERT INTO Product (ProductID, Name, Description, Price, StockQuantity, CategoryID)
VALUES (1, 'Wireless Mouse', 'Ergonomic wireless mouse with 2.4GHz connectivity', 29.99, 100, 5);
-- Create a new order
INSERT INTO Orders (OrderID, CustomerID, Status, TotalAmount)
VALUES (1001, 42, 'Processing', 89.97);
-- Add items to order
INSERT INTO Order_Item (OrderID, ProductID, Quantity, UnitPrice)
VALUES (1001, 1, 3, 29.99);
-- Update stock
UPDATE Product
SET StockQuantity = StockQuantity - 3
WHERE ProductID = 1;
-- Student Table
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
EnrollmentDate DATE,
Program VARCHAR(100)
);
-- Course Table
CREATE TABLE Course (
CourseID VARCHAR(20) PRIMARY KEY,
Title VARCHAR(200) NOT NULL,
Credits INT,
DepartmentID INT,
Description TEXT
);
-- Enrollment Table
CREATE TABLE Enrollment (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID VARCHAR(20),
Semester VARCHAR(20),
Year INT,
Grade VARCHAR(2),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);
-- Instructor Table
CREATE TABLE Instructor (
InstructorID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
DepartmentID INT,
Email VARCHAR(100) UNIQUE
);
-- Course_Section Table
CREATE TABLE Course_Section (
SectionID INT PRIMARY KEY,
CourseID VARCHAR(20),
InstructorID INT,
Semester VARCHAR(20),
Year INT,
Room VARCHAR(20),
Schedule VARCHAR(100),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID),
FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);
-- Find all courses a student is enrolled in
SELECT c.Title, e.Semester, e.Year, e.Grade
FROM Enrollment e
JOIN Course c ON e.CourseID = c.CourseID
WHERE e.StudentID = 12345
ORDER BY e.Year,
CASE e.Semester
WHEN 'Fall' THEN 1
WHEN 'Spring' THEN 2
WHEN 'Summer' THEN 3
ELSE 4
END;
-- Get class roster for a course section
SELECT s.Name, s.Email
FROM Enrollment e
JOIN Student s ON e.StudentID = s.StudentID
WHERE e.CourseID = 'CS101'
AND e.Semester = 'Fall'
AND e.Year = 2023;
-- Calculate student GPA
SELECT
s.StudentID,
s.Name,
ROUND(SUM(
CASE
WHEN e.Grade = 'A' THEN 4.0
WHEN e.Grade = 'A-' THEN 3.7
WHEN e.Grade = 'B+' THEN 3.3
WHEN e.Grade = 'B' THEN 3.0
WHEN e.Grade = 'B-' THEN 2.7
WHEN e.Grade = 'C+' THEN 2.3
WHEN e.Grade = 'C' THEN 2.0
WHEN e.Grade = 'C-' THEN 1.7
WHEN e.Grade = 'D+' THEN 1.3
WHEN e.Grade = 'D' THEN 1.0
ELSE 0
END * c.Credits) / SUM(c.Credits), 2) AS GPA
FROM Student s
JOIN Enrollment e ON s.StudentID = e.StudentID
JOIN Course c ON e.CourseID = c.CourseID
WHERE s.StudentID = 12345
AND e.Grade IS NOT NULL
GROUP BY s.StudentID, s.Name;
-- Example: Patient Billing View
CREATE VIEW PatientBillingView AS
SELECT
p.PatientID,
p.Name AS PatientName,
a.AdmissionDate,
r.RoomNumber,
r.RatePerDay,
DATEDIFF(day, a.AdmissionDate, COALESCE(a.DischargeDate, GETDATE())) AS DaysStayed,
DATEDIFF(day, a.AdmissionDate, COALESCE(a.DischargeDate, GETDATE())) * r.RatePerDay AS RoomCharges,
(SELECT SUM(Amount) FROM MedicalProcedures WHERE AdmissionID = a.AdmissionID) AS ProcedureCharges,
(SELECT SUM(Quantity * UnitPrice) FROM Prescriptions WHERE PatientID = p.PatientID) AS PharmacyCharges
FROM Patients p
JOIN Admissions a ON p.PatientID = a.PatientID
LEFT JOIN Rooms r ON a.RoomID = r.RoomID
WHERE a.DischargeDate IS NULL OR a.DischargeDate >= DATEADD(month, -1, GETDATE());
-- Example: Service Request Tracking
CREATE TABLE ServiceRequests (
RequestID BIGSERIAL PRIMARY KEY,
CitizenID BIGINT NOT NULL,
ServiceType VARCHAR(100) NOT NULL,
RequestDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
Status VARCHAR(50) DEFAULT 'Submitted',
Priority VARCHAR(20) DEFAULT 'Normal',
AssignedTo BIGINT,
CompletionDate TIMESTAMP,
ResolutionNotes TEXT,
FOREIGN KEY (CitizenID) REFERENCES Citizens(CitizenID),
FOREIGN KEY (AssignedTo) REFERENCES GovernmentEmployees(EmployeeID)
);
-- Index for performance
CREATE INDEX idx_service_requests_status ON ServiceRequests(Status);
CREATE INDEX idx_service_requests_citizen ON ServiceRequests(CitizenID);
CREATE INDEX idx_service_requests_date ON ServiceRequests(RequestDate);
-- Example: Inventory Management
CREATE TABLE Products (
ProductID BIGSERIAL PRIMARY KEY,
SKU VARCHAR(50) UNIQUE NOT NULL,
Name VARCHAR(255) NOT NULL,
Description TEXT,
Price DECIMAL(10,2) NOT NULL,
Cost DECIMAL(10,2),
CategoryID INT,
IsActive BOOLEAN DEFAULT true,
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Inventory (
InventoryID BIGSERIAL PRIMARY KEY,
ProductID BIGINT NOT NULL,
WarehouseID INT NOT NULL,
QuantityOnHand INT NOT NULL DEFAULT 0,
QuantityReserved INT NOT NULL DEFAULT 0,
ReorderPoint INT,
LastStockUpdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
UNIQUE (ProductID, WarehouseID)
);
-- Function to check stock availability
CREATE OR REPLACE FUNCTION check_stock_availability(
p_product_id BIGINT,
p_warehouse_id INT,
p_quantity INT
) RETURNS BOOLEAN AS $$
DECLARE
v_available INT;
BEGIN
SELECT (QuantityOnHand - QuantityReserved) INTO v_available
FROM Inventory
WHERE ProductID = p_product_id AND WarehouseID = p_warehouse_id;
RETURN COALESCE(v_available, 0) >= p_quantity;
END;
$$ LANGUAGE plpgsql;
Objective: Design a database for a Library Management System
This concludes Lecture 22: Real-World Database Applications
Feel free to review any section using the navigation tabs above.