Assignment 1: Database design
IFQ544: Databases
Assignment 1: Database design
In this assignment, you will be working in teams of two to develop a conceptual data model using Steps 16 of the Conceptual Schema Design Procedure (CSDP) of ORM. Once you have completed the CSDP, you will individually map the schema to a relational database schema, including any possible constraints.
Task 1: ProjectRus business concern
A business called ProjectsRus hires a project team to develop and implement technology for a client. The project team has suitably qualified staff who fulfil the project roles of a project manager (PM), software developer (SD), system analyst (SA), database developer (DD), user interface developer (UID), and tester (T).
There can be other project roles and a staff member can have a different role in different projects. For example, you are the system analyst in one project, and in another project, you are a project manager.
Project team information
ProjectsRus maintains information about their project team staff members and the projects performed for clients. The following table lists the project team staff members details including their employment start and finish dates.
Staff ID Staff name Staff phone Employment
Start date End date
S100 Charlie 4663451 15/03/1990 S101 Isabella 4663452 01/01/2010 31/12/2015
01/01/2017 S102 Jacob 4663453 15/03/2001 S103 Damian 4663454 02/06/2015 31/12/2015
01/03/2017 05/09/2018
S104 Harry 4663455 01/02/2003 S105 James 4663456 15/03/2017 14/12/2018
S106 Michelle 4663457 12/07/2016 S107 Kyle 01/11/2019 S108 Amelia 4663459 15/12/2017 S109 Tom 4663460 01/02/2020 Client project information
A client project is reported in the form of a project charter, which describes some importantinformation about the projectfor example, the project name, period, scope, and its members. The details of the client project are as shown in the following table.
Note: A project team staff member has only one role in the project. As shown in the examples, the full names of the roles are not used in a project charter.
Project charter (1)
Project ID PRJ_515
Project name Mobile application to manage electronic scripts for doctor-prescribed medicines
Project sponsor Acme Chemist
Date of approval 01/02/2020 Last revision 03/02/2020
Project description Mobile application (app) so that patients with an electronic script for medicines can place an order and make payment for the medicines with their chemist of choice for pickup or delivery.
Project period (years) 2
Scope Identify system functionality from sample groups.
Mobile app will function on all mobile devices.
Identifies nearby chemists for immediate fulfilment, or a preferred chemist.
Order can be placed for pickup or delivery.
Payment options to be included. .
Compatibility with government E-health systems. Compatibility with existing formats of electronic scripts and associated security.
The scope goes on to explain further requirements included in the project.
Project members Role Name
PM
SA
SD
DD
UID
T Charlie (S100)
Jacob (S102)
Harry (S104)
Michelle (S106)
Kyle (S107)
Amelia (S108)
Total number 6
Project charter (2)
Project ID PRJ_517
Project name Application to interface dispensing machine to SCM IS.
Project sponsor ChemistRus franchise.
Date of approval 15/05/2017 Last revision 20/05/2017
Project description A standalone dispensing machine can load onto shelves and dispense prescription medicines (like a vending machine). The standalone dispensing machine needs to be interfaced with the existing supply chain management (SCM) information system (IS). An application is required to interface between the existing SCM IS and the dispensing machine. The SCM IS lists incoming medicines from suppliers for loading onto the shelves of the dispensing machine. The SCM IS will also list order information for dispensing medicines to customers.
Project period (years) 1
Scope Develop and implement an application to interface between the existing SCM IS and the existing dispensing machine. The interface application will need to transmit information to the dispensing machine so that a suppliers delivery of medicines is automatically loaded onto the correct shelves in the dispensing machine. The interface application will need to transmit information to the dispensing machine so that a customers order is automatically dispensed. All transmissions to the dispensing machine will need to be confirmed by reply message. The dispensing machine will transmit and display any error or fault codes.
Project members Role Name
PM
SA
SD
T Damian (S103)
Isabella (S101)
James (S105)
Michelle (S106)
Total Number 4
Assignment 1: Task 1 marking criteria
The following is the marking criteria for Task 1 of this assignment. This is a detailed breakdown of the mark distribution for each criterion in this task.
Task 1 marking criteria (25 marks)
Criteria High Distinction Distinction / Credit Credit / Pass Pass / Marginal Fail Fail / Low fail
Syntactic correctness
(7 marks) The model is complete and fully syntactically correct The model is complete and mostly syntactically correct The model is mostly complete and/or reasonably syntactically correct The model is partially complete and/or mainly syntactically incorrect The model is mostly incomplete and/or syntactically incorrect
Semantic correctness
(15 marks) The model fully and correctly reflects every aspect of the facts described in the scenario The model fully and correctly reflects most aspects of the facts described in the scenario The model correctly reflects many aspects of the facts described in the scenario, or reflects most aspects but is somewhat incorrect or inefficient in structure The model reflects a few aspects of the facts described in the scenario and/or has an incorrect or inefficient structure The model incorrectly reflects the facts described in the scenario
Pragmatic correctness
(3 marks) The model has a clear structure designed for maximal understandability by stakeholders (layout, labels, annotations, etc) The model has a mostly clear structure and/or is designed for high understandability by stakeholders (layout, labels, annotations, etc) The model has a mainly clear structure and/or is designed for reasonable understandability by stakeholders (layout, labels, annotations, etc) The model has a somewhat messy structure and/or does not reflect that it has been designed with consideration for stakeholder understandability The model has an unclear structure and/or most would find it difficult to understand
