Rationale and Description

      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