Relational Database Design Project: Organic Pastures Clinic Case Study

Desklib provides past papers and solved assignments. This project showcases database design for Organic Pastures Clinic.

Contents
Assessment Item Number 2.......................................................................................................2
Business Rules.......................................................................................................................2
Crow’s Foot Design...............................................................................................................3
Conclusion..................................................................................................................................4
References..................................................................................................................................5
Figures
Figure 1: Crow's Foot Design....................................................................................................3

Assessment Item Number 2
Business Rules
 Record entity contains a Primary Key named as RCRD_ID; it helps in identifying
each record uniquely.
 Therapist entity contains a Primary Key named as THRPST_ID; it helps in identifying
each therapist uniquely.
 Appointment entity is the other entity which also contains a Primary key given the
name APPNTMNT_ID; it also helps in finding each appointment uniquely.
 Payment entity is the next entity that contains a Primary Key named as
PAYMNT_ID; this also helps in uniquely identifying each of the payments.
 Client entity also contains a Primary Key as CLNT_ID; it also facilitates in providing
different clients data.
 Treatment entity has an attribute named TRTMNT_CODE that is also a Primary Key;
it identifies each treatment uniquely.
 Many to many relationship has also been encountered in the Relational Data Model
between two entities named as Treatment and the Appointment, that is not acceptable.
In order to remove this relation, a new entity is being introduced as
Treatments_Booked.
 The Treatments_Booked entity that is created also contains a Primary Key named as
TBOOKED_ID; it also helps in identifying each booked treatment uniquely.
 One to Many relationship has been shown between record and appointment as a single
appointment can have more than one record.
 One to Many relationship has also been shown between Client and the
Treatment_Booked as a single client can have many booked treatments.
 One to Many relationship has also been shown between Treatment_booked and the
Appointment entity as a single booked treatment can have many appointments.
 One to Many relationship has also been shown between Treatment_booked and the
payments as a single booked treatment can have many payments.
 One to Many relationship has also been shown between Treatment_booked and the
Treatment as a single treatment can have many booked treatments.
 One to Many relationship has also been shown between Therapist and the
Appointment as a single therapist can have many appointments (Foster, E. C., &
Godbole, S. 2016).
 THRPST_ID, TBOOKED_ID, TRTMNT_CODE, CLNT_ID and APPNTMNT_ID
are the foreign keys mentioned.

Crow’s Foot Design
Figure 1: Crow's Foot Design

Conclusion
The above-made report is concluded by providing the overall scenario of the Clinic having
the name OPC ((Organic Pastures Clinic). The clinic facilitates the clients by providing them
a lot of options for the treatment for various age groups (Goelman, D., & Dietrich, S. W.
2018, February). The treatments include skin treatments and many more facilities that
provide skin relaxations to the clients. The whole scenario is made and explained properly
with the help of a Crow’s Foot design. Each of the entity is provided with the Primary Key
attributes and the Foreign Keys are also added wherever required. One to Many relationships
is also defined in the above-made design. The design is made on draw.io.

References
 Foster, E. C., & Godbole, S. (2016). The Relational Model. In Database Systems (pp. 37-
72). Apress, Berkeley, CA.
 Goelman, D., & Dietrich, S. W. (2018, February). A Visual introduction to conceptual
database design for all. In Proceedings of the 49th ACM Technical Symposium on
Computer Science Education (pp. 320-325). ACM.