Organic Pastures Clinic Database Design

This assignment focuses on the database design of an 'Organic Pastures Clinic'. It includes an Entity Relationship Diagram (ERD) outlining relationships between entities like clients, therapists, treatment details, and payment information. The document details business rules, assumptions regarding primary and foreign keys, and a logical design schema for each entity in the database. The assignment references relevant literature on database systems and ERD extraction.

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.