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

Receipt table

Create database HospitalDatabase;
use HospitalDatabase;
CREATE TABLE drug (
DrugName varchar(100) NOT NULL,
DrugNO varchar(23) NOT NULL,
ExpiryDate date NOT NULL,
Description varchar(25) NOT NULL,
ManufacturingDate date NOT NULL,
PRIMARY KEY (DrugNO)
) ;
CREATE TABLE family doctor (
FamilyDoctor_Id varchar(20) NOT NULL,
FirstName varchar(20) NOT NULL,
LastName varchar(20) NOT NULL,
ContactNumber varchar(15) NOT NULL,
Email varchar(40) NOT NULL,
PRIMARY KEY (FamilyDoctor_Id)
);
CREATE TABLE medicalcondition(
MedicalConditionName varchar(20) NOT NULL,
symptoms_Signs text NOT NULL,
Effect text NOT NULL,
Cause text NOT NULL,
ChronicDisorders text NOT NULL,
AcuteProblems varchar(100) NOT NULL,
Treatment varchar(200) NOT NULL,
PRIMARY KEY (MedicalConditionName)
);

CREATE TABLE medicalhistory (
Medical_History_No varchar(40) NOT NULL,
DrugAllergic varchar(6) NOT NULL,
medicationStatus varchar(20) NOT NULL,
PhysicalExerciseRoutine varchar(30) NOT NULL,
HospitalizedStatus varchar(30) NOT NULL,
DailyHabit varchar(50) NOT NULL,
PregnancyTest varchar(50) NOT NULL
) ;
CREATE TABLE medicalpractioner (
MedicalPractioner_Id varchar(20) NOT NULL,
FirstName varchar(20) NOT NULL,
LastName varchar(20) NOT NULL,
ContactNumber varchar(15) NOT NULL,
Email varchar(40) NOT NULL,
PRIMARY KEY (MedicalPractioner_Id)
) ;
CREATE TABLE naturopath (
NaturoPath_Id varchar(25) NOT NULL,
FirstName varchar(35) NOT NULL,
LastName varchar(35) NOT NULL,
DirectContact varchar(15) NOT NULL,
OfficeHours time NOT NULL,
servicerendered varchar(60) NOT NULL,
Email varchar(40) NOT NULL,
PRIMARY KEY (NaturoPath_Id
) ;

CREATE TABLE patient (
PatientNo varchar(20) NOT NULL,
fName varchar(45) NOT NULL,
lName varchar(45) NOT NULL,
Occupation varchar(45) NOT NULL,
Gender varchar(4) NOT NULL,
DOB date NOT NULL,
MaritualStatus varchar(20) NOT NULL,
Race varchar(20) NOT NULL,
Postcode varchar(20) NOT NULL,
Address varchar(30) NOT NULL,
PhoneNumber varchar(100) NOT NULL,
`Email varchar(30) NOT NULL,
PRIMARY KEY (PatientNo)
) ;
CREATE TABLE patientcard (
PatientCardNo varchar(10) NOT NULL,
VisitDate date NOT NULL,
Service Rendered varchar(200) NOT NULL,
AmountPaid varchar(100) NOT NULL
PRIMARY KEY (PatientNo)
) ;

CREATE TABLE patientform (
PatientFormSerialNo varchar(25) NOT NULL,
FormName varchar(20) NOT NULL,
PRIMARY KEY (PatientFormSerialNo)
) ;
CREATE TABLE receipt (
ReceiptNo varchar(20) NOT NULL,
ReceiptName varchar(45) NOT NULL,
Amount varchar(45) NOT NULL,
RecieptDate date NOT NULL,
PRIMARY KEY (ReceiptNo)
) ;

Task 2.3: Create the four most useful indexes on your tables.
CREATE UNIQUE INDEX idx_FirstName
On Patient(FirstName);
CREATE UNIQUE INDEX idx_FirstName
On familydoctor (FirstName);
CREATE UNIQUE INDEX idx_DrugName
On drug (DrugName);
DROP INDEX index_ids_FirstName.
SELECT
Index_FirstName,
FROM
ALL_indexes
Where table_name=patient;

Task 2.4: Data Population.
INSERT INTO drug VALUES
('quinne', 'P2556527', '2019-12-04', 'QQQQQQQQQQQQQ', '2017-03-23'),
('TOBAA', 'DN2', '2019-12-11', 'it cures malaria', '2018-09-23'),
('xyz', 'DN3', '2021-02-23', 'dose 4*4 ', '2019-08-23'),
('usususus', 'DN4', '2020-02-23', 'take correct dose', '2018-11-23'),
('rqwqeq', 'DN5', '2020-10-23', 'take correct dose', '2018-03-23'),
('dpdpdsaks', 'DN6’, '2020-05-23', 'take correct dose', '2017-10-23'),
('quinne', 'DN7', '2022-02-23', 'take correct dose', '2018-02-23'),
('uzc', 'DN8', '2022-12-23', 'take correct dose', '2018-07-25'),
('bbvbvbbcbcbcb', 'DN9', '2021-02-23', 'take correct dose', '2018-03-23'),
('NADASFDD', 'DN10', '2023-02-23', 'dose 4*4', '2018-02-23');
INSERT INTO familydoctor VALUES
(' P2556527', 'einstein ', 'Quincy', '[phone]', 'emmyemmodumo@@gmail.com'),
('F002', 'Tabitha', 'sweety', '[phone]', '[e-mail]'),
('F003', 'mesharck', 'Terence', '[phone]', '[e-mail]'),
('1004', 'Blessed', 'vera', '[phone]', '[e-mail]'),
('Foo5', 'Joy', 'Amish', '[phone]e', '[e-mail]');
INSERT INTO medicalcondition VALUES
('asthma', 'COUGHING\r\npoor breathing', 'COUGHING BLOOD\r\n', 'smoking', 'yes',
'flu', 'ON TREATMENT'),
('arthritis', 'leg pain', 'no walking', 'eating affected cow meat', 'yes', 'cold', 'on treatment'),
('skin disorder', 'rashes on skin', 'skin peeling off\r\nblood on skin', 'fungus', 'yes', 'flu', 'on
treatmennt'),
('digestive problems. ', 'running stomach', 'reduce weight', 'food poison', 'yes', 'cold', 'on
treatmment'),
('malaria', 'headache\r\nhigh temperatures', 'vomiting', 'mosquito bites', 'yes', 'flu',
'cured');

INSERT INTO medicalhistory VALUES
(' P2556527', 'not', 'on medication', 'once daily', 'last two months', 'smoker', 'negative'),
('MHN2', 'not', 'yes', 'once daily', 'last month', 'takes alcohol', 'negative'),
('MHN3', 'yes', 'on medication', 'once daily', 'admitted', 'smoker', 'negative'),
('MHN4', 'yes', 'no', 'thrice a week', 'admitted', '', 'positive'),
('MHN5', 'yes', 'on medication', 'no', 'admitted', '', 'negative');
INSERT INTO medicalpractioner VALUES
(' P2556527', 'stanely', 'stood', '[phone]', '[e-mail]'),
('MP002', 'NEWTON', 'STAUT', '[phone]', '[e-mail]'),
('MP003', 'winny', 'nelly', '[phone]', '[e-mail]'),
('MP004', 'glorious', 'armstrong', '[phone]', '[e-mail]'),
('MP005', 'judy', 'new', '[phone]', '[e-mail]');
INSERT INTO naturopath VALUES
(' P2556527', 'sTEVE', 'WEE', '+27272', '08:00:00', 'Administer drugs', '[e-mail]'),
('NP002', 'Emma', 'winno', '+27272', '10:00:00', 'Nursing patient', '[e-mail]'),
('NP003', 'TABS', 'PIOPO', '[phone]', '20:33:00', 'councelling', '[e-mail]'),
('NP004', 'stanely', 'own', '[phone]', '11:00:00', 'administering drug',
'[e-mail]'),
('NP005', 'winny', 'oprah', '[phone]', '08:00:00', 'clean wounds',
'[e-mail]');

INSERT INTO patient VALUES
('P255656527', 'EMMANUEL', 'WOOW', 'STUDENT', 'MALE', '1988-08-23',
'MARRIED', 'WHITE', 'XTAVA', 'INDIANA USA', '+2626252',
'emmyemmodumo@@gmail.com'),
(' P255656523', 'EINSTIEN', 'ALBERT', 'PROFESSOR', 'MALE', '1980-11-23',
'MARRIED', 'RED INDIAN', 'EXTA W100', 'REDBUL PLOT I10292', '[phone]',
'[e-mail]'),
(' P2556565234', 'TABITHA', 'NOOW', 'TEACHER', 'F', '1985-12-23', 'SINGLE', 'RED
INDIAN', 'UUSHHA100', 'PLOT UZA ', '[phone]', '[e-mail]'),
('P233333', 'NELLY', 'JUDY', 'TEACHER', 'F', '1990-12-03', 'SINGLE', 'BLACK',
'2828S', 'ROAD I1121 VICTORIA', '[phone]', '[e-mail]'),
('P2342342322', 'BLESSED', 'VERA', 'LAWYER', 'F', '1995-12-02', 'SINGLE', 'BLACK',
'SIWIW1212', 'RIVER ROAD WINN19191', '+2999222', '[e-mail]');
INSERT INTO patientcard VALUES
(' P2556527', '2019-12-12', 'TREAT MALARIA', '$100'),
('PCN2', '2019-12-12', 'TREAT ', '$200'),
('PCN3', '2020-01-23', 'surgery', '$500'),
('PCN4', '2020-03-25', 'Surgery', '$600'),
('PCN5', '2020-04-23', 'delivery', '$400'),
('PCN6', '2020-01-23', 'Prescribe drugs', '$30'),
('PCN7', '2019-12-06', 'PRESCRIBE DRUGS', '$500'),
('PCN8', '2020-02-23', 'SURGERY', '$600'),
('PCN9', '2020-01-23', 'EYE TREATMENT', '$500'),
('PCN10', '2019-12-12', 'EAR TREATMENT', '$600');

INSERT INTO patientform VALUES
(' P2556527', 'SWWW'),
('PSNO2', 'AAAUX'),
('PSNO3', 'UACACA'),
('PSNO4', 'ABABBA'),
('PSNO5', 'SSBSBSB'),
('PSNO6', 'SBSVSVS'),
('PSNO7', 'UAZAS'),
('PSN07', 'EQTQTQT'),
('PSNO8', 'DGDGDG'),
('PSN08', 'SQUACACA'),
('PSNO9', 'AGAGQ9UE'),
('PSNO10', 'UAWA');
INSERT INTO receipt VALUES
(' P2556527', 'wxw', '$100', '2019-12-11'),
('p200', 'wyzz', '$200', '2019-12-05'),
('p300', 'ayayay', '$150', '2019-12-21'),
('p400', 'qyqyqy100', '$350', '2019-11-05'),
('p500', '121uaasas', '$700', '2019-12-26'),
('p600', 'uasasu', '$800', '2019-12-31'),
('p700', 'ehehehe', '$900', '2020-01-02'),
('p800', 'wwwww', '$950', '2019-12-20'),
('p103', 'hsgsgsgs', '$1000', '2019-12-03');

Task 2.5: SQL Query writing
1. SELECT date, patientCardNo,ServiceRendered, AmountPaid
2. FROM PatientCard
3. WHERE AmountPaid < $400;
Inner Joins.
SELECT Patient.PatientNo.FamilyDoctor.FamilyDoctor_Id
FROM Patient,FamilyDoctor
WHERE Patient.PatientNo=FamilyDoctor.FamilyDoctor_Id;
SELECT Drug.DrugNo.Patient.PatientNo
FROM Drug.Patient
WHERE Drug.DrugNO=Patient.PatientNo
OuterJoin.
SELECT Patient.PatientNo,Patient.FirstName,LastName,Drug.DrugNo
FROM Patient
LEFT OUTER JOIN Drug
ON Pateint.PatientNo = Drug.DrugNo;
SELECT Drug.DrugNo,DrugName,Desscription,ExpiryDate,ManufacturyDate
From
Drug
LEFT OUTER JOIN Patient
On Drug.Drugno=Patient.PatientNO;

Use of count and/or another similar mathematical expression
SELECT COUNT(PatientNo)
FROM Patient;
SELECT AVG(AMOUNT)
FROM Receipt;
Use of a sorting/ordering facility
SELECT
FirstName,
LastnAME,
Occupation
Gender
DOB
Martialstatus
Race
Postcode
Address
FROM
Patient
ORDER BY
FirstName DESC;
SELECT
FirstName,
LastnAME,
Occupation
Gender
DOB
Martialstatus
Race
Postcode
Address
FROM
Patient
ORDER BY
LastName ASC;

Acondition using “>”
SELECT avg(AmountPaid),Max(patientCardNo)
FROM PatientCard
Where AmountPaid>$800
SELECT avg(AmountPaid),Max(patientCardNo)
FROM PatientCard
Where AmountPaid<$800
A condition using IN, NOT NULL, or similar.
select *from Patient where In FirstName in (' EMMANUEL'', 'Einstein', ' Tabitha ')
select * from patient where FirstName is not null
A sub-query
SELECT PatientCardNo,AmountPaid
FROM PatientCard
WHERE AmountPaid>( SELECT AmountPaid
FROM PatientCard
WHERE PatientCardNo=PCN10);

Bibliography.
Connolly, M. & Begg, C. (2015). Database systems A practical approach to design,
implementation and management. (6th Ed.). Harlow, Essex, England: Addison-Wesley (Pearson
Education Limited).
Roman S. (2017).Access Database Design and Programming. [online].Available at:
https://www.oreilly.com/library/view/access-databasedesign/1565926269/ch07s08.html
[Accessed 23/12/2019 ]
Satzinger, W, J ,Jackson,B,R and Burd ,D,S (2016).System Analysis and Design in A changing
World (8th Ed). Boston Course Technology.