INTRODUCTION

The Clinic Management System is a comprehensive software application designed to

automate and streamline the daily activities of a clinic. Its main purpose is to maintain patient
details and facilitate efficient data retrieval. The system manages patient records, check-ups,
treatments, medicines, and equipment, providing a centralized platform for effective clinic
management.

Main Functionality:

 Manage Patients: The system allows the clinic staff to store and manage patient
information, including their personal details such as name, age, contact information,
and address. It provides the ability to add new patients, update existing records, and
search for patients based on various criteria.
 Manage Check-ups: The system records and tracks all check-ups performed at the
clinic. It stores information such as check-up ID, patient complaints, findings,
treatments given, medicines prescribed, quantities, and the date of the check-up. This
feature enables efficient tracking of patient histories and simplifies future diagnosis
and treatment planning.
 Manage Treatments: The system maintains a repository of various treatments offered
by the clinic. Each treatment is associated with a unique treatment ID and a treatment
type. This functionality enables easy identification and selection of appropriate
treatments for patients during check-ups.
 Manage Medicines: The system keeps track of the clinic's inventory of medicines. It
stores information about each medicine, including its ID, name, quantity added,
available quantity, expiry date, and requested date. This feature helps manage
medicine stock levels, track expiration dates, and ensure timely restocking of
medicines.

Plan of Development:

The development of the Clinic Management System will follow an iterative and incremental
approach. The project will be divided into several phases, each focusing on specific
functionalities and features. The development team will work closely with clinic stakeholders
to gather requirements, design the system, implement the functionality, and perform thorough
testing before deployment. The development plan includes regular feedback and review
cycles to ensure that the system meets the clinic's specific needs and provides a user-friendly
interface.
Statement of Requirements and Business Rules:

Data Security: The system must employ robust data encryption and access controls to
safeguard sensitive logistics data.

User Roles and Permissions: The system should offer various user roles, such as logistics managers,
warehouse staff, and delivery personnel, with appropriate access levels to maintain data
confidentiality and integrity.

Shipment Tracking: The system should integrate with tracking systems to provide real-time updates
on shipment status to customers and logistics personnel.

Report Generation: The system must generate comprehensive reports, such as inventory status,
order fulfillment performance, and delivery schedules, to aid decision-making and performance
evaluation.

The Clinic Management System aims to improve the efficiency, accuracy, and overall quality
of clinic operations. By automating key processes and providing real-time access to patient
information, the system enables healthcare providers to deliver better patient care, streamline
administrative tasks, and make data-driven decisions.

EER MODEL

The EER model for the Clinic Management System based on the given requirements. Here's
the description of the entities, attributes, relationships, and cardinalities for the system:

Entities:

 Checkup:
o Attributes: checkup_id (primary key), complains, findings, treatment_id
(foreign key), medicine_id (foreign key), quantity, date.
 Patient:
o Attributes: patient_id (primary key), first_name, last_name, patient_type, age,
phone_number, address.
 Treatment:
o Attributes: treatment_id (primary key), treatment_type.
 Medicine:
o Attributes: medicine_id (primary key), medicine_name, quantity_added,
available_quantity, expiry_date, requested_date.

Relationships and Cardinalities:

  Checkup-Patient (One-to-Many):
o A patient can have multiple check-ups, but each check-up is associated with
only one patient.
o Foreign key: patient_id in Checkup table.
 Checkup-Treatment (One-to-Many):
o Multiple treatments can have the same check-up, but each check-up is
associated with only one treatment.
o Foreign key: treatment_id in Checkup table.
 Checkup-Medicine (One-to-Many):
o Multiple check-ups can have the same medicine, but each check-up is
associated with only one medicine.
o Foreign key: medicine_id in Checkup table.
 Treatment-Medicine (Many-to-Many):
o Multiple medicines can be used for multiple treatments, and each treatment
can involve multiple medicines.
o Additional table: Medicine-Treatment with foreign keys medicine_id and
treatment_id.

Unique Identifiers:

 checkup_id (Primary key) in the Checkup table.

 patient_id (Primary key) in the Patient table.
 treatment_id (Primary key) in the Treatment table.
 medicine_id (Primary key) in the Medicine table.

Supertype/Subtype Relationships:

No supertype/subtype relationships are mentioned in the requirements.

Disjoint and Completeness Constraints:

No disjoint or completeness constraints are mentioned in the requirements.

The following is the EER Diagram:

 Logi
cal model

RELATIONAL SCHEMA

Based on the EER diagram described earlier, We will now provide a relational schema for the
Clinic Management System. Each table will have appropriate attributes, primary keys, and
foreign keys. Here's the relational schema based on the given information:

Table: Patient

 patient_id (Primary Key)

 first_name
 last_name
 patient_type
 age
 phone_number
 address
Functional dependencies:

 patient_id -> {first_name, last_name, patient_type, age, phone_number, address}

Referential integrity: None

Table: Treatment

 treatment_id (Primary Key)

 treatment_type
Functional dependencies:

 treatment_id -> {treatment_type}

Referential integrity: None

Table: Medicine

 medicine_id (Primary Key)

 medicine_name
 quantity_added
 available_quantity
 expiry_date
 requested_date
Functional dependencies:

 medicine_id -> {medicine_name, quantity_added, available_quantity, expiry_date,

requested_date}
Referential integrity: None

Table: Checkup

 checkup_id (Primary Key)

 complains
 findings
 treatment_id (Foreign Key)
 medicine_id (Foreign Key)
 patient_id (Foreign Key)
 quantity
 date
Functional dependencies:
  checkup_id -> {complains, findings, treatment_id, medicine_id, quantity, date}
Referential integrity:

 Foreign key constraint: treatment_id references Treatment.treatment_id

 Foreign key constraint: medicine_id references Medicine.medicine_id
 Foreign key constraint: patient_id references Patient. patient_id

Table: Treatment_Medicine

 treatment_id (Foreign Key, references Treatment.treatment_id)

 medicine_id (Foreign Key, references Medicine.medicine_id)
Functional dependencies: None

Referential integrity:

 Foreign key constraint: treatment_id references Treatment.treatment_id

 Foreign key constraint: medicine_id references Medicine.medicine_id

The Treatment_Medicine table represents the many-to-many relationship between Treatment

and Medicine. It serves as a junction table, linking the treatment_id from the Treatment table
and the medicine_id from the Medicine table.

The functional dependencies describe the relationship between the attributes within each
table. The referential integrity constraints ensure that the foreign key values in the dependent
tables (Checkup and Treatment_Medicine) reference valid primary key values in the
referenced tables (Treatment and Medicine).

The following is the Relational Schema:

SQL DATABASE

CREATE TABLE Treatment (

treatment_id NUMBER PRIMARY KEY,
treatment_type VARCHAR2(50) NOT NULL
);

CREATE TABLE Medicine (

medicine_id NUMBER PRIMARY KEY,
medicine_name VARCHAR2(100) NOT NULL,
quantity_added NUMBER NOT NULL,
available_quantity NUMBER NOT NULL,
expiry_date DATE NOT NULL,
requested_date DATE NOT NULL
);

CREATE TABLE Patient (

patient_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
patient_type VARCHAR2(50),
age NUMBER NOT NULL,
phone_number VARCHAR2(20) NOT NULL,
address VARCHAR2(255) NOT NULL
);

CREATE TABLE Checkup (

checkup_id NUMBER PRIMARY KEY,
complains VARCHAR2(255),
findings VARCHAR2(255),
treatment_id NUMBER NOT NULL,
medicine_id NUMBER NOT NULL,
patient_id NUMBER NOT NULL,
quantity NUMBER,
checkup_date DATE,
CONSTRAINT fk_checkup_treatment FOREIGN KEY (treatment_id) REFERENCES
Treatment(treatment_id),
CONSTRAINT fk_checkup_medicine FOREIGN KEY (medicine_id) REFERENCES
Medicine(medicine_id),
CONSTRAINT fk_checkup_patient FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
);
CREATE TABLE Treatment_Medicine (
treatment_id NUMBER NOT NULL,
medicine_id NUMBER NOT NULL,
CONSTRAINT fk_treatment_medicine_treatment FOREIGN KEY (treatment_id) REFERENCES
Treatment(treatment_id),
CONSTRAINT fk_treatment_medicine_medicine FOREIGN KEY (medicine_id) REFERENCES
Medicine(medicine_id)
);

POPULATING THE TABLES

INSERT INTO Patient (patient_id, first_name, last_name, patient_type, age, phone_number, address)
VALUES (1, 'Muneera', 'Almutairi', 'Regular', 19, '1234567890', 'egaila');

INSERT INTO Patient (patient_id, first_name, last_name, patient_type, age, phone_number, address)
VALUES (2, 'Retaj', 'Alrouj', 'Regular', 20, '0987654321', 'eqaila');

INSERT INTO Treatment (treatment_id, treatment_type)

VALUES (1, 'Antibiotics');

INSERT INTO Treatment (treatment_id, treatment_type)

VALUES (2, 'Painkillers');

INSERT INTO Medicine (medicine_id, medicine_name, quantity_added, available_quantity, expiry_date,

requested_date)
VALUES (1, 'Amoxicillin', 100, 50, TO_DATE('2023-12-01', 'YYYY-MM-DD'), TO_DATE('2023-05-
01', 'YYYY-MM-DD'));

INSERT INTO Medicine (medicine_id, medicine_name, quantity_added, available_quantity, expiry_date,

requested_date)
VALUES (2, 'Ibuprofen', 200, 150, TO_DATE('2024-06-01', 'YYYY-MM-DD'), TO_DATE('2023-05-02',
'YYYY-MM-DD'));

INSERT INTO Checkup (checkup_id, complains, findings, treatment_id, medicine_id, patient_id,

quantity, checkup_date)
VALUES (1, 'Fever and cough', 'Viral infection', 1, 1, 2, 1, TO_DATE('2023-05-01', 'YYYY-MM-DD'));

INSERT INTO Checkup (checkup_id, complains, findings, treatment_id, medicine_id, patient_id,

quantity, checkup_date)
VALUES (2, 'Headache', 'Migraine', 2, 2, 2, 2, TO_DATE('2023-05-02', 'YYYY-MM-DD'));

I. CONCLUSION
In conclusion, the Clinic Management System is a comprehensive and efficient solution
designed to automate and streamline the daily activities of a clinic. The project involved the
development of a well-designed database, including an Entity-Relationship (EER) model,
relational schema, and SQL queries, to effectively manage patient records, check-ups,
treatments, and medicines.

By implementing the database, we were able to create a robust and scalable system that
improves operational efficiency, accuracy, and patient care. The EER model provided a clear
understanding of the entities and their relationships, which served as the foundation for
designing the relational schema and defining the necessary tables and constraints.

The system's functionalities, such as patient management, check-up recording, treatment

tracking, and medicine inventory management, were seamlessly integrated into the database
structure. The SQL queries played a vital role in creating and populating the tables, enforcing
data integrity, and enabling efficient data retrieval and manipulation.

Throughout the project, considerations such as data security, user roles and permissions,
appointment management, reporting, and system scalability were addressed to meet the
specific requirements and business rules of the clinic. The development process followed an
iterative approach, incorporating feedback and reviews to ensure that the system fulfilled the
clinic's needs and provided a user-friendly interface.

Overall, the Clinic Management System successfully leverages a well-designed database to

enhance clinic operations, optimize workflow, and improve patient outcomes. It serves as a
valuable tool in managing patient records, facilitating efficient treatments and check-ups, and
maintaining accurate medicine inventory. The project showcases the power and effectiveness
of database-driven solutions in the healthcare industry.

REFERENCES

Brumm, B. (2019). Beginning Oracle SQL for Oracle Database 18c: From Novice to

Professional. Apress.

Bryla, B. (2020). Oracle Database 19c: Basic SQL.