Database design and Implementation (EER Modelling and SQL)

UNIVERSITY:
Faculty of Computing, Engineering and Media (CEM) – Course work
Specification 2019/20
Module name: Database Systems and Design
Module code: IMAT5103
Title of the Assignment: Database design and Implementation (EER
Modelling and SQL)
Date & Time Due: 10th January, 2020, @ 4pm
Student details:
Student Name Student Number

Table of Contents
Stage 1: Scenario and Conceptual Database Design...............................................................3
Task 1.1: Selected case study......................................................................................... 3
Task 1.2: The conceptual database design for the Natural Therapy Centre database system.......4
Stage 2: Logical Database Design and Oracle SQL Implementation/querying............................7
Task 2.1: The Logical Database Design the Natural Therapy Centre database system..............7
Task 2.2: Create the tables using Oracle DBMS.................................................................8
Task 2.3: Create the four most useful indexes on your tables..............................................18
Task 2.4: Data Population........................................................................................... 19
Task 2.5: SQL Query writing....................................................................................... 23
Bibliography................................................................................................................ 26

Stage 1: Scenario and Conceptual Database Design
Task 1.1: Selected case study.

Task 1.2: The conceptual database design for the Natural Therapy Centre database system.

Business Rules (relationships).
The patient must have patient card.
The medical practioner has to prescribe drugs.
The medical practioner has to treat patient.
The medical practioner requires medical history about patient.
Patient has to make payment and is issued with receipt
Patient must fill in patient form if he/her is the first time patient
The patient must take drugs.
Assumptions.
The patient has to visit the health Centre.
The medical practioner are always available to attend to patient.
Strong and Weak Entities.
Strong entities include;
Patient,Medical practioner,family doctor,Naturo Path,Drug,Medical History and Medical
Condition
Weak entities.
Patient form, Receipt and patient card
Generalisation/Specialisation Structures.
There is generalization and specialization where entity medical practioner is the super entity. The
family doctor and Naturo Path are subentities of the entity medical practioner.There is optional
or where medical praction can be either family doctor or Naturo path.
Fantraps and Chasms.
There no fan traps and chasms.

Entity and Attribute Listing.
The entities include
Patient,Medical practioner,family doctor,Naturo Path,Drug,Medical History , Medical
Condition , Patient form, Receipt and patient card.
The attributes for entity patient include
FName,LName DateOfBirth,Address,PatientNO,Occupation,MarialStatus
EthnicBackground/Race,PostCode,Gender,PhoneNumber and Email.
The attributes for entity Receipt include
RecieptNumber,Amount,Name and Date.
The attributes for entity PatientCard include;
PatientCardNo, VisitDate,serviceRendered,AmountPaid and PatientNo.
The attributes for entity Drug/Medication include;
Drug Number,Name, Description, ExpiryDate and ManufacturingDate
The attributes for entity MedicalPractioner include;
FirstName,LastName,ContactNumber and MedicalPractioner_Id
The attributes for entity FamilyDoctor include;
FirstName,LastName,ContactNumber and FamilyDoctor_Id
The attributes for entity NaturoPath include;
FirstName,LastName,MailingAddress,OfficeHours,Direct contact,Service and NaturoPath_Id.
The attributes for entity MedicalHistory include;
DrugAllergic,DailyHabit,PhysicalExerciseRoutine,PregnancyTest,HospitilizedStatus,Medicatio
nStatus and MedicalHistoryNo.
The attributes for entity Medical Condition include;
Name,Symptom , status,Effect,Cause,Treatement,ChronicDisorders,AcuteProblems
The attributes for entity PatientForm include;
FormName and FormSerialNumber.