SQL Database Design Examples for Common Systems

This document provides practical SQL Data Definition Language (DDL) examples for creating and modifying database schemas across various common applications. Each section details the necessary tables and their relationships, offering a clear foundation for building robust relational databases.

Library Management System Database

The following SQL DDL statements define the core tables for a library management system, enabling the tracking of books, members, and loan transactions.

Table Creation

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Author VARCHAR(100) NOT NULL,
    ISBN VARCHAR(20) UNIQUE,
    PublishedYear INT CHECK (PublishedYear >= 1500 AND PublishedYear <= 2025)
);

CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    FullName VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    JoinDate DATE NOT NULL,
    Age INT CHECK (Age >= 12)
);

CREATE TABLE Loans (
    LoanID INT PRIMARY KEY,
    BookID INT,
    MemberID INT,
    LoanDate DATE NOT NULL,
    ReturnDate DATE,
    FOREIGN KEY (BookID) REFERENCES Books(BookID),
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
    CHECK (ReturnDate IS NULL OR ReturnDate >= LoanDate)
);

Modifying the Members Table

This section demonstrates how to alter the Members table by adding a new column for phone numbers and removing the 'Age' column.

ALTER TABLE Members
ADD PhoneNumber VARCHAR(15);

ALTER TABLE Members
DROP COLUMN Age;

University Database Schema

This section outlines the DDL for a university database, designed to manage student, course, and enrollment information effectively.

Database and Table Creation

CREATE DATABASE UniversityDB;
USE UniversityDB;

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100) UNIQUE,
    Major VARCHAR(50)
);

CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    Credits INT
);

CREATE TABLE Enrollment (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    Grade VARCHAR(2),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Hospital Management Database

Explore the SQL DDL for a hospital management system, encompassing tables for patients, doctors, appointments, and billing, crucial for healthcare operations.

Database and Table Creation

CREATE DATABASE HospitalDB;
USE HospitalDB;

CREATE TABLE Patient (
    PatientID INT PRIMARY KEY,
    Name VARCHAR(100),
    DOB DATE,
    Gender VARCHAR(10),
    Phone VARCHAR(15) UNIQUE
);

CREATE TABLE Doctor (
    DoctorID INT PRIMARY KEY,
    Name VARCHAR(100),
    Specialization VARCHAR(50),
    Phone VARCHAR(15) UNIQUE
);

CREATE TABLE Appointment (
    AppointmentID INT PRIMARY KEY,
    PatientID INT,
    DoctorID INT,
    AppointmentDate DATE,
    Diagnosis VARCHAR(200),
    FOREIGN KEY (PatientID) REFERENCES Patient(PatientID),
    FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID)
);

CREATE TABLE Bill (
    BillID INT PRIMARY KEY,
    AppointmentID INT UNIQUE,
    Amount DECIMAL(10, 2),
    PaymentStatus VARCHAR(20),
    FOREIGN KEY (AppointmentID) REFERENCES Appointment(AppointmentID)
);

These examples serve as a foundational resource for understanding and implementing SQL DDL for various real-world database applications. They highlight best practices in defining primary keys, foreign keys, unique constraints, and check constraints to ensure data integrity.