code

-- Patient Table
drop database hospital;
create database hospital;
use hospital;

CREATE TABLE Patient (
patient_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
date_of_birth DATE,
gender VARCHAR(10),
contact_number VARCHAR(15)
);

-- Doctor Table
CREATE TABLE Doctor (
doctor_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
specialization VARCHAR(50),
contact_number VARCHAR(15)
);

-- Appointment Table
CREATE TABLE Appointment (
appointment_id INT PRIMARY KEY,
patient_id INT,
doctor_id INT,
appointment_date TIMESTAMP,
reason VARCHAR(255),
FOREIGN KEY (patient_id) REFERENCES Patient(patient_id),
FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id)
);

-- Insert data into Patient table
INSERT INTO Patient (patient_id, first_name, last_name, date_of_birth, gender, contact_number)
VALUES
(1, 'John', 'Doe', '1990-05-15', 'Male', '123-456-7890'),
(2, 'Jane', 'Smith', '1985-08-22', 'Female', '987-654-3210'),
(3, 'Robert', 'Johnson', '1978-03-10', 'Male', '555-123-4567'),
(4, 'Emily', 'Taylor', '1995-11-28', 'Female', '777-888-9999'),
(5, 'Michael', 'Williams', '1982-07-04', 'Male', '111-222-3333');

-- Insert data into Doctor table
INSERT INTO Doctor (doctor_id, first_name, last_name, specialization, contact_number)
VALUES
(1, 'Dr. Smith', 'Johnson', 'Cardiology', '555-111-2222'),
(2, 'Dr. Amanda', 'Jones', 'Pediatrics', '888-444-9999'),
(3, 'Dr. David', 'Miller', 'Orthopedics', '777-888-5555'),
(4, 'Dr. Rachel', 'Anderson', 'Dermatology', '333-666-1111'),
(5, 'Dr. Christopher', 'Moore', 'Internal Medicine', '222-999-7777');

-- Insert data into Appointment table
INSERT INTO Appointment (appointment_id, patient_id, doctor_id, appointment_date, reason)
VALUES
(1, 1, 2, '2023-01-10 10:00:00', 'Routine Checkup'),
(2, 3, 1, '2023-02-15 14:30:00', 'Chest Pain'),
(3, 2, 4, '2023-03-20 11:45:00', 'Skin Allergy'),
(4, 4, 3, '2023-04-25 09:15:00', 'Bone Fracture'),
(5, 5, 5, '2023-05-30 16:00:00', 'General Health Consultation');

-- View to display appointment information
CREATE VIEW AppointmentDetails AS
SELECT
A.appointment_id,
P.first_name AS patient_first_name,
P.last_name AS patient_last_name,
D.first_name AS doctor_first_name,
D.last_name AS doctor_last_name,
A.appointment_date,
A.reason
FROM
Appointment A
JOIN Patient P ON A.patient_id = P.patient_id
JOIN Doctor D ON A.doctor_id = D.doctor_id;

-- Index on contact_number column in Patient table
CREATE INDEX idx_patient_contact_number ON Patient(contact_number);

-- Sequence for appointment IDs
alter table appointment auto_increment=1;

-- Check Constraint to ensure a valid gender in Patient table
ALTER TABLE Patient
ADD CONSTRAINT chk_valid_gender CHECK (gender IN ('Male', 'Female', 'Other'));

-- Unique Constraint to ensure unique appointment IDs
ALTER TABLE Appointment
ADD CONSTRAINT unique_appointment_id UNIQUE (appointment_id);