Faculty of Computing: Database Design and Implementation Assignment

This assignment focuses on database design and implementation for a Natural Therapy Centre database system, covering both conceptual and logical design phases. The assignment begins with a case study and the creation of an Entity-Relationship (EER) model to represent the database's structure, including business rules, strong and weak entities, and generalization/specialization structures. The logical database design involves creating tables using Oracle DBMS, defining data types, and establishing relationships between tables. The assignment also includes creating four useful indexes to optimize query performance and populating the tables with sample data. Finally, it requires writing several SQL queries to retrieve and manipulate data, demonstrating proficiency in SQL syntax and database querying techniques. The solution provides detailed SQL code for table creation, data population, and query writing, showcasing a comprehensive understanding of database design principles and practical implementation using Oracle 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.

Stage 2: Logical Database Design and Oracle SQL Implementation/querying
Task 2.1: The Logical Database Design the Natural Therapy Centre database
system.

Task 2.2: Create the tables using Oracle DBMS.
Drug Table
Family doctor table.

Medical Condition table
Medical History table
Medical Practioner table.

Naturopath table
Patient Table

Patient card table

Patient Form table