Scribd
Upload
60 views

IP Database Tables

The document contains SQL commands to create tables for patients, doctors, departments, and appointments in a healthcare database. Sample data is inserted into the tables. A SQL query then retrieves patient details and merges it with appointment information from the other tables. Python code is also provided to generate sample DataFrames from the table data and merge them to display the patient and appointment details.

Uploaded by

Vikram
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
60 views

IP Database Tables

The document contains SQL commands to create tables for patients, doctors, departments, and appointments in a healthcare database. Sample data is inserted into the tables. A SQL query then retrieves patient details and merges it with appointment information from the other tables. Python code is also provided to generate sample DataFrames from the table data and merge them to display the patient and appointment details.

Uploaded by

Vikram
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 5

-- 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()

You might also like