Assessment 1B: Project (Applied) – details: IFB105_23se1 Database Management 30/3/2023, 10:02 am
https://canvas.qut.edu.au/courses/11725/pages/assessment-1b-project-applied-details?module_item_id=1164997 Page 1 of 8
Assessment 1B: Project (Applied) –
details
Overview
Task description:
You will have to solve two tasks:
(1) Apply the Conceptual Schema Design Process (steps 1 – 6) to a given business concern;
(2) Map a conceptual schema design to a relational database schema.
For task 1, you can use any modelling tool (including MS PowerPoint, LucidChart, Draw.io or
even handwriting if it is understandable and clear) to draw your models. Make sure the
diagrams are readable. Assignment submissions MUST be a single PDF file properly identified
(student name and number).
If you have questions please post in Teams, ask your tutor, or contact Manavdeep
Singh ([email protected]).
Unit Learning Outcomes assessed:
ULO 1: Create a conceptual schema to model information in a domain. Relates to: ACS CBOK:
3, 3.2
ULO 2: Construct SQL commands to store, retrieve and manipulate data in a relational
database. Relates to: ACS CBOK: 3, 3.2
ULO 3: Translate a conceptual schema into a relational database schema design. Relates to:
ACS CBOK: 3, 3.2
ULO 4: Normalise a relational database schema to improve data integrity. Relates to: ACS
CBOK: 3, 3.2
ULO 5: Analyse the social impacts, privacy and legal issues associated with managing data.
Relates to: ACS CBOK: 1, 1.5.3, 3.2
ULO 6: Apply ethical fundamentals, including the ACS Code of Ethics and Professional
)
Assessment 1B: Project (Applied) – details: IFB105_23se1 Database Management 30/3/2023, 10:02 am
https://canvas.qut.edu.au/courses/11725/pages/assessment-1b-project-applied-details?module_item_id=1164997 Page 2 of 8
Conduct to industry scenarios. ACS CBOK: 1, 1.1.3, 1.2

What you need to do
Task 1 [70 marks] details
A new doctor’s surgery clinic is starting up called UrLocalGP. The following paragraphs
describe the UoD.
On your initial visit or online you register your details. Must enter your name, address, mobile
phone number, and email address. Each person is allocated a Patient Number. If a person
registers online then in addition to the detail mentioned above the registration will also require
a password.
Once you are registered then you can make an appointment with a doctor. There are several
different types of appointments (Eg. General, General long, mental health, prescription
renewal, … etc). There are several doctors with appropriate qualifications for the various
appointment types they offer. On your first appointment with your doctor, you are required to
complete an online form that records your medical history including any medication that you
are taking. Your medical insurance details are also recorded. Before any appointment you are
informed of the cost.
During the appointment the doctor will record a number of measurements (Eg. Heart rate,
temperature, and blood oxygen levels) are always recorded. Other measurements maybe
taken and recorded as needed. An expert system (ES) is used that you are not required to
model. The ES requires a list of symptoms which it uses to predict a diagnosis and a treatment
plan. Your data model will include the data provided to and information from the ES. The doctor
will make a list of your symptoms from an existing list of symptoms. Once the list of symptoms
are entered into the ES, a diagnosis is made and a treatment plan is suggested. The treatment
plan can be modified by the doctor. The treatment plan includes any prescription medication
and provides a warning if there are any issues with your current medications.
Assessment 1B: Project (Applied) – details: IFB105_23se1 Database Management 30/3/2023, 10:02 am
https://canvas.qut.edu.au/courses/11725/pages/assessment-1b-project-applied-details?module_item_id=1164997 Page 3 of 8
and provides a warning if there are any issues with your current medications.
Your list of symptoms, diagnosis and treatment plan is recorded in your medical history.
Prescriptions are printed out and a medical certificate is printed with a date range if the doctor
considers you unfit for your normal activities like work or study.
At the end of the appointment, you will need to make payment.
Patient

Passwo
(Encryp
Asflnfd;
Jasfue8
Hfiuwife
Appointment
Assessment 1B: Project (Applied) – details: IFB105_23se1 Database Management 30/3/2023, 10:02 am
https://canvas.qut.edu.au/courses/11725/pages/assessment-1b-project-applied-details?module_item_id=1164997 Page 4 of 8

Doctor Availability

Medical History
Patient
Assessment 1B: Project (Applied) – details: IFB105_23se1 Database Management 30/3/2023, 10:02 am
https://canvas.qut.edu.au/courses/11725/pages/assessment-1b-project-applied-details?module_item_id=1164997 Page 5 of 8
Patient

In the story of the UoD some value or entity types are mentioned but the details of the data
have been deliberately omitted. You can make any reasonable assumptions about the data. My
advice is to make it as simple as possible. For example, Doctor use doctor number as a
reference mode and include minimal contact details. Nothing about qualifications. Symptoms,
Diagnosis, and Treatment plan will need to be modelled but not the expert system that
determines the diagnosis from the symptoms.
Task 1 getting started:
Perform CSDP step 1 ~ 6 and present your final ORM diagram. You are not required to submit
your results from each individual step. If you want to submit your elementary facts or note any
assumptions in your diagram, you are free to do so, but only the final ORM diagram will be
evaluated.
Criteria Sheet – Task 1 [70 marks]

The model is
The model is The model
Assessment 1B: Project (Applied) – details: IFB105_23se1 Database Management 30/3/2023, 10:02 am
https://canvas.qut.edu.au/courses/11725/pages/assessment-1b-project-applied-details?module_item_id=1164997 Page 6 of 8

The model is
The model is
The model
in the scenario
Task 2 [30 marks] details
The following is an ORM model. Map the schema to a relational database schema. Any
possible constraints need to be included (e.g., primary key, foreign key).
Assessment 1B: Project (Applied) – details: IFB105_23se1 Database Management 30/3/2023, 10:02 am
https://canvas.qut.edu.au/courses/11725/pages/assessment-1b-project-applied-details?module_item_id=1164997 Page 7 of 8
Criteria Sheet – Task 2
Marks will be awarded for the following:
Full marks will be awarded if all relations are correctly mapped with the schema including
correct primary/foreign keys.
Any incorrect/missing relations will be deducted:
– penalise for missing relations and penalise for each missing key
5 if a relation is missing
2 for missing primary key or missing foreign key
1 mark for minor error
For the ORM diagram provided. Map the schema to a relational database schema. Any
possible constraints need to be included (e.g., primary key, foreign key).
What to submit
Assessment 1B: Project (Applied) – details: IFB105_23se1 Database Management 30/3/2023, 10:02 am
https://canvas.qut.edu.au/courses/11725/pages/assessment-1b-project-applied-details?module_item_id=1164997 Page 8 of 8
TEQSA PRV12079 | CRICOS 00213J | ABN 83 791 724 622
What to submit
Assignment submissions MUST be a single PDF file properly identified (student name and
number).
Feedback:
Under normal circumstances, you will receive marks for each criterion via a Canvas rubric
within 10-15 working days of submission. Click on Grades to see your results. Usually the
reason for each choice of mark is self-evident, the marker will include some written feedback
about your performance. You should use this feedback to strengthen your performance in the
next assessment item.
Moderation:
All staff who are assessing your work meet to discuss and compare their judgements before
marks or grades are finalised.
enting us vaule type too many things are
pation can’tbe able type stored about it
cost – rale type
Transform familiar examples into elementary facts.
Draw the fact types, and apply a population check.
Check for entity types to be combined, and note any arithmetic derivations.
Add uniqueness constraints, and check the arity of fact types.
Add mandatory role constraints, and check for logical derivations.
Add value, set-comparison, and subtyping constraints.
https://www.inf.unibz.it/~montali/teaching/
1415/dpm/slides/3a.orm-1.pdf
Name
Address

Types of appointments
Insurance details

Cost
Body measurements

Schema procedure
↳
28
38
↑?
-⑦
58
68
I
understanding the UOD
Elementary fact types:
value
Entity/object
–~Time of ap?
I-Combine
the main entities are Patient, Appointment, Doctor, Medical History, Medication, and Medical Certificate.
Each Patient is assigned a Patient Number and can have multiple Appointments, each of which is
associated with a specific Doctor and Appointment Type. During an Appointment, measurements are
recorded and payment is processed. The Doctor entity includes basic contact details, such as name
and phone number, and is referenced by a Doctor Number. Medical History includes a list of
Medications that the Patient is taking, along with any allergies or other relevant medical information. If
the Doctor determines that the Patient is unfit for normal activities, a Medical Certificate is issued with a
start and end date.
+———-+ +————–+ +————
+

+———-+ | …
+————–+
|
|
|
+————–+
|Medical History|
+————–+

|Medications <|–*1
| … |
+————–+
|
|
|
+————–+
|MedicalCertific|
+————–+
|PatientID |
| StartDate |
| EndDate |
| … |
+————–+