Database Design: Physician, Patient, Pediatrician

This document presents two database design scenarios: one for a physician-patient relationship and another for a pediatrician-child relationship. Each scenario includes business rules, identified entities, entity relationships, optionality and plurality constraints, and a conceptual entity-relationship diagram. The physician-patient model covers diagnoses, appointments, and prescriptions, while the pediatrician-child model focuses on appointments, bills, and responsible adults. The document provides a detailed analysis of the relationships between entities and their associated constraints.

Physician and Patient
Business rules are as follows.
 A diagnosis is given to one or more patients.
 A patient is given one or more diagnoses.
 An attending physician sees at least 20 patients.
 Each diagnosis is given a diagnosis code.
 A patient can make many appointments with one or more Physician
in the clinic.
 Doctor can prescribe many diagnosis to many patients.
Entities
Physician
Patient

Diagnosis
Entities Relation
Physician to Patient
Patient to Physician
Diagnosis to Patient
Patient to Diagnosis
Diagnosis to Physician
Physician to Diagnosis
Optionality and Plurality constraints
Physician to Patient
One physician can see many patient.
Patient to Physician
One patient can see many optional physician.
Diagnosis to Patient
Many diagnosis to one patient.
Patient to Diagnosis
One patient can prescribed to many diagnosis.
Diagnosis to Physician
One physician can check many diagnosis
report.
Physician to Diagnosis
Physician can prescribed many diagnosis to Patient.

Entity Relation Diagram

Pediatricians and Children
Business rules are as follows.
 Every child has a responsible adult.
 Responsible adults have one or more children.
 A child may have multiple appointments with multiple pediatricians in
the clinic.
 One pediatrician attends each appointment.
 Each appointment results in a bill for the responsible adult.
 Each adults have multiple bills
 Bills are made on the basis of pediatrician consulted.
Entities
Adult
Child
Appointment

Pediatrician
Bill
Entities Relations
Adult – Child
Child – Adult
Appointment - Child
Child – Appointment
Pediatrician - Child
Child - Pediatrician
Bill - Pediatrician
Pediatrician - Bill
Appointment – Bill

Bill - Appointment
Adult - Bill
Bill - adult
Optionality and Plurality Constraint.
Adult - Child
Many optional adult have many optional child
Child - Adult
Many optional child have many optional adult
Child - Appointment
Each appointment having many optional child
Appointment – Child
One optional appointment on many optional child.
Child - Pediatrician
One optional child having many optional pediatrician.
Pediatrician – child
One pediatrician can see many child.
Pediatrician - Bill
One optional pediatrician having multiple bill creation.
Bill – Pediatrician
Many optional bill can be made on one optional pediatrician.
Appointment - Bill
One appointment having one Bill.
Bill – Appointment
One bill on one appointment
Adult - Bill
One adult having multiple optional bill.
Bill - Adult
Multiple optional bill for one optional adult.
Entity Relation Diagram