Foundational to addressing business concerns is an understanding of core concepts, principles
and skills required for understanding, designing and managing databases. This implies the
understanding of the kinds of techniques that may be used to model data, and an ability to
develop a concise conceptual model that represents a given universe of discourse.
This assessment will involve the creation of a conceptual schema design for a given universe
of discourse and the generation of a relational mapping.
You will use your knowledge from the lectures together with the techniques practiced in the
tutorial sessions and apply both a set of tasks that refer to some business concern. You will
not only perform the necessary steps to solve the tasks, but also provide an explanation of
your approach.
Instructions
This assessment will have a Group task with Individual components. 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 (individual task)
For task 1, you can use any modelling tool (including MS PowerPoint, LucidChart, or even
handwriting) to draw your models. Make sure the diagrams are readable.
Assessment Tasks
Task 1
RQUT, a research center, maintains information about researchers and research projects. The following
shows the list of researchers including the contract history of the researchers.
Res ID Res name Res phone Contract
Start date End date
R100 Charlie 4663451 15/03/1990
R101 Isabella 4663452 01/01/2010 31/12/2015
01/01/2017
R102 Jacob 4663453 15/03/2001
R103 Damian 4663454 02/06/2015 31/12/2015
01/03/2017 05/09/2018
R104 Harry 4663455 01/02/2003
R105 James 4663456 15/03/2017 14/12/2018
R106 Michelle 4663457 12/07/2016
R107 Kyle 01/11/2019
R108 Amelia 4663459 15/12/2017
R109 Tom 4663460 01/02/2020
A research project is reported in a form of project charter which describes some important information
of the project (e.g., the project name, period, scope, members, etc.) as shown in the following. A
researcher involved in a project plays one role of ‘Principal Investigator (PI)’, ‘Sub Investigator (SI)’,
‘Regulatory Coordinator (RC)’ or ‘Data Coordinator (DC)’. As shown in the examples, the full names of
the roles are not used in a project charter. The project member of the charter presents all participating
researchers.
Research Project Charter (1)
Project ID PRT_515
Project Name Data Governance for Secondary Use of Data
Project Sponsor QUT, Queensland Government
Date of Approval 01/02/2020 Last Revision 03/02/2020
Project Description To improve privacy of data subjects, and prevent
abuse/misuse of data for secondary purpose
Project Period
(year)
2
Scope Analyze legal requirements and develop a governance
framework
…..
Project Members
Role Name
PI
SI
SI
SI
RC
DC
Charlie (R100)
Jacob (R102)
Harry (R104)
Michelle (R106)
Kyle (R107)
Amelia (R108)
Total Number 6
Research Project Charter (2)
Project ID PRT_517
Project Name Contingency Model for Data Governance
Project Sponsor QUT
Date of Approval 15/05/2017 Last Revision 20/05/2017
Project Description To support decision-making of the governing body based on the
different contingencies
Project Period (year) 1
Scope Develop a new contingency model and conduct case studies
…..
Project Members
Role Name
PI
SI
SI
SI
Damian (R103)
Isabella (R101)
James (R105)
Michelle (R106)
Total Number 4
Perform CSDP step 1 ~ 6 and submit your final ORM diagram (not required to submit results of those
steps).
Criteria Sheet – Task 1 [25 marks]
Criteria High Distinction
[20 – 16 marks]
Fail
[4 – 0 marks]
Syntactic
Correctness
The model is complete
and fully syntactically
correct
The model is mostly
incomplete and/or
syntactically
incorrect
Semantic
Correctness
The model fully and
correctly reflects every
aspect of the facts
described in the
scenario
The model
incorrectly reflects
the facts described
in the scenario
Pragmatic
Correctness
The model has a clear
structure designed for
maximal
understandability by
stakeholders (layout,
labels, annotations,
etc)
The model has an
unclear structure
and/or most would
find it difficult to
understand
Task 2
Following is an ORM model for employee. Map the schema to a relational database schema.
Any possible constraints need to be included (e.g., primary key, foreign key).
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 relation
3 marks
– penalise for missing table and penalise for each missing key
5 if a table is missing
1.5 for missing primary key or missing foreign key
0.5 mark for minor errors
