Here’s the SQL script for creating the Patients, Doctors, and OPD Bookings tables in a hospital management system using MS SQL Server. This script includes primary keys and foreign key relationships.

19 December Class Discussion

SQL Script

-- Create Patients Table
CREATE TABLE Patients (
    PatientID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    DateOfBirth DATE NOT NULL,
    Gender NVARCHAR(10) NOT NULL,
    PhoneNumber NVARCHAR(15) NOT NULL,
    Email NVARCHAR(100),
    Address NVARCHAR(250),
    City NVARCHAR(50),
    State NVARCHAR(50),
    PostalCode NVARCHAR(10),
    CreatedAt DATETIME DEFAULT GETDATE(),
    UpdatedAt DATETIME DEFAULT GETDATE()
);
-- The IDENTITY(1,1) property means that the id column will start from 1 and increase by 1 for each new row. You can --also use negative values for the increment to create a descending sequence. 


-- Create Doctors Table
CREATE TABLE Doctors (
    DoctorID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Specialty NVARCHAR(100) NOT NULL,
    PhoneNumber NVARCHAR(15) NOT NULL,
    Email NVARCHAR(100),
    RoomNumber NVARCHAR(10),
    CreatedAt DATETIME DEFAULT GETDATE(),
    UpdatedAt DATETIME DEFAULT GETDATE()
);

-- Create OPD_Bookings Table
CREATE TABLE OPD_Bookings (
    BookingID INT IDENTITY(1,1) PRIMARY KEY,
    PatientID INT NOT NULL,
    DoctorID INT NOT NULL,
    BookingDate DATETIME NOT NULL DEFAULT GETDATE(),
    VisitDate DATE NOT NULL,
    VisitTime NVARCHAR(10) NOT NULL,
    Symptoms NVARCHAR(500),
    Status NVARCHAR(50) DEFAULT 'Scheduled',
    CreatedAt DATETIME DEFAULT GETDATE(),
    UpdatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
    FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID)
);

Explanation:

1. Patients Table:
   • Contains information about the patients, including personal details like name, DOB, gender, and contact information.
   • Primary key: PatientID.
   • Difference between VARCHAR and NVARCHAR
2. Doctors Table:
   • Contains information about the doctors, including their specialty, contact details, and room assignment.
   • Primary key: DoctorID.
3. OPD_Bookings Table:
   • Links patients and doctors to manage outpatient department (OPD) bookings.
   • Contains references (PatientID and DoctorID) to the Patients and Doctors tables through foreign key constraints.
   • Includes additional details like booking date, visit date and time, symptoms, and booking status.

This setup ensures data integrity and enforces relationships between the tables.