Data Model Development and Implementation Project - University Name

This assignment presents a comprehensive solution to a data model development project. It begins by outlining the creation of various entities, including doctors, patients, and pharmacies, along with their respective attributes. The assignment then details the relationships between these entities, visualized through an Entity Relationship Diagram (ERD). Furthermore, it addresses functional dependencies and normalizes the database tables to ensure data integrity. The solution includes SQL queries to retrieve and manipulate data, showcasing practical application of database concepts. The assignment covers key aspects of data modeling, including ER diagrams, SQL queries, and normalization techniques, to ensure data integrity and efficient database design. The project offers a practical guide to data modeling concepts, providing a complete and practical overview of data modeling principles.

Running head: MN405 - Data model development and implementation
MN405 - Data model development and implementation
Name of the Student
Name of the University
Author Note

1
MN405 - DATA MODEL DEVELOPMENT AND IMPLEMENTATION
Table of Contents
Task 1.............................................................................................................................2
A.................................................................................................................................2
B.................................................................................................................................2
C.................................................................................................................................2
D.................................................................................................................................3
E.................................................................................................................................3
Task 2.............................................................................................................................4
Task 3.............................................................................................................................4
Task 4.............................................................................................................................6

2
MN405 - DATA MODEL DEVELOPMENT AND IMPLEMENTATION
Task 1
A
Entities to be created as per the requirement are as follows:
1. doctor
2. patient
3. allocation
4. prescription
5. drug
6. pharmacySale
7. pharmacy
B
Attributes to be added to the entities are as follows:
1. doctor(doctorID, name, specialty, experience)
2. patient(patientID, name, address, age, phoneNumber)
3. allocation(allID, patientID, doctorID)
4. prescription(presID, patientID, drugID, doctorID)
5. drug(drugID, tradeName, formula)
6. pharmacySale(psID, pharmacyID, drugID, price)
7. pharmacy(pharmacyID, name, address, phoneNumber)
C
The relationships among the tables has been shown below:
1. doctor to prescription – one to many

3
MN405 - DATA MODEL DEVELOPMENT AND IMPLEMENTATION
2. doctor to allocation – one to many
3. patient to allocation – one to many
4. patient to prescription – one to many
5. pharmacy to pharmacySales – one to many
6. drug to prescription – one to many
7. drug to pharmacySales – one to many
D
The following are the functional dependency for the tables created:
1. doctorID  name, specialty, experience
2. patientID  name, address, age, phoneNumber
3. drugID  tradeName, formula
4. pharmacyID  name, address, phoneNumber
5. allID  patientID, doctorID
6. presID  patientID, drugID, doctorID
7. psID  pharmacyID, drugID, price
E
Normalization of the tables:
1. doctor(doctorID, name, specialtyID, experience)
2. specialty(specialtyID, name) (functional dependency no 1)
3. patient(patientID, name, address, age, phoneNumber)
4. allocation(allID, patientID, doctorID)
5. prescription(presID, patientID, drugID, doctorID)
6. drug(drugID, tradeName, formula)
7. pharmacySale(psID, pharmacyID, drugID, price)

4
MN405 - DATA MODEL DEVELOPMENT AND IMPLEMENTATION
8. pharmacy(pharmacyID, name, address, phoneNumber)
Task 2
Figure 2: Entity Relationship Diagram
(Source: Created By Author)
Task 3
1. doctor
2. specialty
3. patient

5
MN405 - DATA MODEL DEVELOPMENT AND IMPLEMENTATION
4. allocation
5. prescription
6. drug
7. pharmacySale

6
MN405 - DATA MODEL DEVELOPMENT AND IMPLEMENTATION
8. pharmacy
Task 4
A. select * from doctor;
B. select name, age, address from patient where age >= 60;

7
MN405 - DATA MODEL DEVELOPMENT AND IMPLEMENTATION
C. select p.name, d.tradeName, do.name from patient p, drug d, doctor do,
prescription pr where p.patientID = pr.patientID and d.drugID = pr.drugID and
do.doctorID=pr.doctorID;