Scribd
Upload
31 views2 pages

Database Design: Healthcare Entities & Relationships

The document outlines a database design for a healthcare system, detailing six tables: Patients, Doctors, Visits, Prescriptions, PharmacyStocks, and PharmacySales, along with their attributes and primary/foreign keys. It describes the relationships between these entities, including one-to-many relationships between Patients and Visits, Doctors and Visits, Visits and Prescriptions, and PharmacyStocks and PharmacySales, as well as a many-to-one relationship between Prescriptions and PharmacyStocks. The document emphasizes the constraints and participation requirements for each relationship.

Uploaded by

Kale'ab Lemma
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
31 views2 pages

Database Design: Healthcare Entities & Relationships

The document outlines a database design for a healthcare system, detailing six tables: Patients, Doctors, Visits, Prescriptions, PharmacyStocks, and PharmacySales, along with their attributes and primary/foreign keys. It describes the relationships between these entities, including one-to-many relationships between Patients and Visits, Doctors and Visits, Visits and Prescriptions, and PharmacyStocks and PharmacySales, as well as a many-to-one relationship between Prescriptions and PharmacyStocks. The document emphasizes the constraints and participation requirements for each relationship.

Uploaded by

Kale'ab Lemma
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

DATA BASE DESIGN: Entities, Attributes, Keys and Relationships

Tables:

1. Patients
Attributes:
 PatientID (Primary key, Atomic)
 Name (Composite)
 Gender (Atomic)
 Age ( Stored)
 Phone (Atomic)
2. Doctors
Attributes:
 DoctorID (Primary key, Atomic)
 Name (Composite)
 Specialty (Atomic)
 Phone (Atomic)
3. Visits
Attributes:
 VisitID (Primary key, Atomic)
 VisitDate (Atomic)
 Diagnosis (Atomic)
 PatientID (Foreign key referencing Patients)
 DoctorID (Foreign key referencing Doctors)
 Composite Primary key ( PatientID, DoctorID)
4. Prescriptions
Attributes:
 PrescriptionID ( Primary key, Atomic)
 MedicationName ( Atomic)
 Dosage ( Atomic)
 VisitID (Foreign key referencing Visits)
5. PharmacyStocks
Attributes:
 MedicationID ( Primary key, Atomic)
 MedicineName (Atomic)
 QuantityAvailable (Atomic)
 Price (Atomic)
6. PharmacySales
Attributes:
 SalesID (Primary key, Atomic)
 MedicationID (Foreign key referencing pharmacyStocks)
 QuantitySold (Atomic)
 SalesDate (Atomic)

ENTITY RELATIONSHIPS AND CONSTRAINTS

Relationship between Patients and Visits


 Type: One to many
 Foreign key: PatientID in visits table referencing Patients table
 Cardinality ratio: One patient can have many visits
 Participation: Total from Visits to Patients (every visit must have patient)

Relationship between Doctors and Visits

 Type: One to many


 Foreign key: DoctorID in Visits table referencing Doctors table
 Cardinality ratio: One doctor can treat many patients( visits)
 Participation: Total from Visits to Doctors( A visit cannot exist without a doctor)

Relationship between Visits and Prescriptions

 Type: One to many


 Foreign key: VisitID in Prescription table referencing visits table
 Cardinality ratio: One visit can result in many prescriptions
 Participation: Total from Prescriptions to Visits( Prescriptions cannot exist without Visits)

Relationship between Prescriptions and PharmacyStocks

 Type: Many to one ( based on medicine name)


 Logical relationship between the Prescription and The PharmacyStock tables based on the
name of the medicine ([Link] referencing
[Link])
 Participation: Optional (incase stocked is not tracked)

Relationship between PharmacyStocks and PharmacySales

 Type: One to many


 Foreign Key: MedicineID in PharmacySales referencing PharmacyStocks table
 Cardinality ratio: One medicine can have many sales
 Participation: Total from PharmacySales to PharmacyStocks

You might also like