-- Create Patients table
CREATE TABLE Patients (
PatientID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
Gender VARCHAR(10),
PhoneNumber VARCHAR(15)
);
-- Create Doctors table
CREATE TABLE Doctors (
DoctorID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Specialty VARCHAR(100),
PhoneNumber VARCHAR(15)
);
-- Create Departments table
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);
-- Create Appointments table
CREATE TABLE Appointments (
AppointmentID INT PRIMARY KEY,
PatientID INT,
DoctorID INT,
AppointmentDate DATETIME,
DepartmentID INT,
PRIMARY KEY (PatientID, DoctorID, AppointmentDate),
FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID),
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
-- Sample data insertion (you can add more data as needed)
INSERT INTO Patients VALUES (1, 'John', 'Doe', '1990-01-01', 'Male', '123-456-7890');
INSERT INTO Doctors VALUES (1, 'Dr. Sarah', 'Smith', 'Cardiology', '987-654-3210');
INSERT INTO Departments VALUES (1, 'Cardiology');
INSERT INTO Appointments VALUES (1, 1, 1, '2023-01-15 10:00:00', 1);
-- Retrieve patient details along with appointment information
SELECT
Patients.PatientID,
Patients.FirstName AS PatientFirstName,
Patients.LastName AS PatientLastName,
Patients.DateOfBirth,
Patients.Gender,
Patients.PhoneNumber,
Appointments.AppointmentID,
Appointments.AppointmentDate,
Doctors.FirstName AS DoctorFirstName,
Doctors.LastName AS DoctorLastName,
Departments.DepartmentName
FROM
Patients
JOIN Appointments ON Patients.PatientID = Appointments.PatientID
JOIN Doctors ON Appointments.DoctorID = Doctors.DoctorID
JOIN Departments ON Appointments.DepartmentID = Departments.DepartmentID;
Python
import pandas as pd
# Sample data for Patients
patients_data = {
'PatientID': [1, 2, 3],
'FirstName': ['John', 'Jane', 'Bob'],
'LastName': ['Doe', 'Smith', 'Johnson'],
'DateOfBirth': ['1990-01-01', '1985-03-15', '1978-07-20'],
'Gender': ['Male', 'Female', 'Male'],
'PhoneNumber': ['123-456-7890', '987-654-3210', '555-123-4567']
# Sample data for Doctors
doctors_data = {
'DoctorID': [1, 2, 3],
'FirstName': ['Dr. Sarah', 'Dr. John', 'Dr. Emily'],
'LastName': ['Smith', 'Johnson', 'Brown'],
'Specialty': ['Cardiology', 'Orthopedics', 'Pediatrics'],
'PhoneNumber': ['111-222-3333', '444-555-6666', '777-888-9999']
}
# Sample data for Departments
departments_data = {
'DepartmentID': [1, 2, 3],
'DepartmentName': ['Cardiology', 'Orthopedics', 'Pediatrics']
# Sample data for Appointments
appointments_data = {
'AppointmentID': [1, 2, 3],
'PatientID': [1, 2, 3],
'DoctorID': [1, 2, 3],
'AppointmentDate': ['2023-01-15 10:00:00', '2023-02-20 14:30:00', '2023-03-10 09:15:00'],
'DepartmentID': [1, 2, 3]
# Create DataFrames
patients_df = pd.DataFrame(patients_data)
doctors_df = pd.DataFrame(doctors_data)
departments_df = pd.DataFrame(departments_data)
appointments_df = pd.DataFrame(appointments_data)
# Merge DataFrames to get patient details along with appointment information
merged_df = pd.merge(patients_df, appointments_df, on='PatientID')
merged_df = pd.merge(merged_df, doctors_df, on='DoctorID')
merged_df = pd.merge(merged_df, departments_df, on='DepartmentID')
# Display the result
print(merged_df[['PatientID', 'FirstName_x', 'LastName_x', 'DateOfBirth', 'Gender', 'PhoneNumber_x',
'AppointmentID', 'AppointmentDate', 'FirstName_y', 'LastName_y', 'DepartmentName']])
plt.figure(figsize=(10, 6))
plt.bar(patients_df['FirstName'], patients_df['DateOfBirth'])
plt.xlabel('Patient Name')
plt.ylabel('Date of Birth')
plt.title('Patients and Their Date of Birth')
plt.show()
