MiniProjectDB

Table of Contents

• Title Page
• Introduction
• ERD and DSD Diagrams
• Design Decisions
• Create Table Commands
• Data Insertion Methods
• Backup and Restore

Title Page

Project Title: MiniProjectDB
Contributors: Noam Benisho 213200496, Yair Lasry 214085987 System: Medical data management system
Selected Unit: Database unit

Introduction

MiniProjectDB is a database project for a medical system. The project is designed to manage data related to patients, doctors, appointments, medical records, treatments, and billing. The main functionalities include:

• Storing patient information
• Storing doctor information
• Scheduling and managing appointments
• Maintaining medical records
• Documenting treatments
• Managing billing and invoices

ERD and DSD Diagrams

ERD Diagram

DSD Diagram

Design Decisions

Design Choices:

1. Normalization: The database schema was normalized to the third normal form (3NF) to eliminate redundancy and ensure data integrity.
2. Indexing: Indexes were added on primary and foreign keys to improve query performance.
3. Data Types: Appropriate data types were chosen for each field to optimize storage and performance.

Justifications:

• Normalization: By normalizing the schema, we reduce redundancy and prevent anomalies during data operations.
• Indexing: Indexing significantly improves the speed of data retrieval operations, which is crucial for a responsive medical system.
• Data Types: Choosing the right data types ensures efficient use of storage and enhances query performance.

Create Table Commands

CREATE TABLE Patients (
    patient_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    dob DATE,
    gender VARCHAR(10),
    contact_info VARCHAR(255)
);

CREATE TABLE Doctors (
    doctor_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    specialty VARCHAR(100),
    contact_info VARCHAR(255)
);

CREATE TABLE Appointment (
    appointment_id SERIAL PRIMARY KEY,
    patient_id INT REFERENCES Patients(patient_id),
    doctor_id INT REFERENCES Doctors(doctor_id),
    appointment_date DATE,
    reason VARCHAR(255)
);

CREATE TABLE MedicalRecord (
    record_id SERIAL PRIMARY KEY,
    patient_id INT REFERENCES Patients(patient_id),
    record_date DATE,
    diagnosis TEXT,
    treatment TEXT
);

CREATE TABLE DocPat (
    docpat_id SERIAL PRIMARY KEY,
    doctor_id INT REFERENCES Doctors(doctor_id),
    patient_id INT REFERENCES Patients(patient_id)
);

CREATE TABLE Treatment (
    treatment_id SERIAL PRIMARY KEY,
    appointment_id INT REFERENCES Appointment(appointment_id),
    treatment_description TEXT,
    cost DECIMAL(10, 2)
);

CREATE TABLE Billing (
    billing_id SERIAL PRIMARY KEY,
    patient_id INT REFERENCES Patients(patient_id),
    amount DECIMAL(10, 2),
    billing_date DATE,
    paid BOOLEAN
);

Screenshot of DESC Command

Data Insertion Methods

Method 1: Using mockaroo

INSERT INTO Patients (name, dob, gender, contact_info) VALUES ('John Doe', '1980-05-15', 'Male', '123 Main St');
INSERT INTO Doctors (name, specialty, contact_info) VALUES ('Dr. Smith', 'Cardiology', '456 Elm St');
INSERT INTO Appointment (patient_id, doctor_id, appointment_date, reason) VALUES (1, 1, '2024-06-01', 'Routine Checkup');

Method 2: Using Python Script

Method 3: Using Data generator

Backup and Restore

Backup

Restore