Database Design Project: Organic Pastures Clinic (Fall 2024)

This assignment presents a comprehensive database design project for the Organic Pastures Clinic. It begins with an overview, followed by an Entity Relationship Diagram (ERD) illustrating the relationships between clients, therapists, and payment details. The project outlines business rules, such as the one-to-many relationship between clients and treatment details, and the one-to-one or many relationship between clients and payment details. The document then moves into a logical design, defining the database schema with tables for clients, therapists, treatment details, and payment details. Each table includes attributes such as client ID, therapist ID, treatment code, and payment ID, along with their respective data types and foreign key constraints. Finally, the assignment includes a bibliography of relevant resources, such as articles and books on database design, requirements engineering, and relational database schema, to support the design and implementation of the database.

Running head: DATABASE DESIGN OF ORGANIC PASTURES CLINIC
Database Design of Organic Pastures Clinic
Name of Student-
Name of University-
Author’s Note-

1DATABASE DESIGN OF ORGANIC PASTURES CLINIC
Entity Relationship Diagram of Organic Pastures Clinic
Figure 1: Entity Relationship Diagram of OPC

2DATABASE DESIGN OF ORGANIC PASTURES CLINIC
Assumptions and Business Rules:
Business Rules:
Entities Relationship
clients - treatment_details One - many relationship
clients - pay_details One – one and many relationship
therapist - treatment_details One – zero or many relationship
therapist - clients One – zero or many relationship
Assumptions:
The assumptions that are made for this case study is:
The clients entity has the client_ID as a primary key, and therapist_ID is the foreign key.
In therapist entity, the primary key is the therapist_ID.
The entity treatement_details has primary key as treatment_code, and client_ID and therapist_ID
is the foreign key.
In pay_details entity, the pay_ID is the primary key, and client_ID is the foreign key.
Logical Design
clients (clientID, client_fname, client_lname, client_address, client_phnum, client_emailID,
description, therapist_ID)

3DATABASE DESIGN OF ORGANIC PASTURES CLINIC
Foreign key (therapist_ID) references therapist (therapist_ID)
therapist (therapist_ID, therapist_fname, therapist_lname, therapist_phnum, therapist_emailID,
specialization, visiting_time, num_of_session, num_of_session)
treatment_details (treatment_code, client_ID, therapist_ID, treatment_type, num_of_visits,
date_of_appointment, payment_ID, price_per_session)
Foreign key (client_ID) references clients (client_ID)
Foreign key (therapist_ID) references therapist (therapist_ID)
pay_details (pay_ID, client_ID, num_of_session, amount_paid, total_amount,
type_of_payment)
Foreign key (client_ID) references clients (client_ID)

4DATABASE DESIGN OF ORGANIC PASTURES CLINIC
Bibliography
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational
database schema. International Journal of Database Theory and Application, 8(3), 15-26.
Barokati, N., Wajdi, N., & Barid, M. (2017). Application Design Library With gamification
concept. Jurnal Lentera: Kajian Keagamaan, Keilmuan dan Teknologi, 3(1), 93-102.
Dick, J., Hull, E., & Jackson, K. (2017). Requirements engineering. Springer.
Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to databases and
data warehouses. Prospect Press.
Yang, L., & Cao, L. (2016). The Effect of MySQL Workbench in Teaching Entity-Relationship
Diagram (ERD) to Relational Schema Mapping. International Journal of Modern
Education and Computer Science, 8(7), 1.