COIT20247 Database Design and Development – Supplementary Assessment 2026

Assessment Item 2 – Assignment 2

Due date: 11:45pm on Sunday, 23 November 2026

Weighting: 35%

Length: Not applicable

Objectives

This assessment item relates to the unit learning outcomes numbers 1, 2 and 3 as stated in the unit profile.

Part A. Database Implementation Using MS Access (35 Marks)

Introduction

This supplementary assessment provides a case study in Appendix (1) and its sample solution of ER modeling in Appendix (2). Use that sample solution for the following tasks.

Note: This mirrors your Assignment 2 tasks in T2 2026. After completing the tasks, email your solution in a zip file.

Assessment Task

1. Normalization
   1. Map the ERD from the sample solution into a set of relations in at least Third Normal Form (3NF). Ensure your relations meet 3NF. No need to show working.
   2. Select any two relations from step 1a and for each:
      • List all functional dependencies in the relation.
      • Demonstrate that the relation meets Third Normal Form (3NF).

      For help, see the example for a Student relation’s 3NF justification below:
      STUDENT (StudentID, Name, Address, DateOfBirth)
      i. The primary key is StudentID which identifies a student. One student name per Student ID as only one name is recorded. Though a student may have multiple addresses, the case records home address only, so one address per student. One date of birth per student. No repeating groups. Thus, in 1NF.
      ii. Primary key is StudentID, the only candidate key. Name and Address not unique. Student ID determines all attributes. Single simple candidate key, no partial dependencies. In 2NF.
      iii. No attribute determines another non-prime; Name, Address, DateOfBirth do not determine others. No transitive dependencies. Meets 1NF, 2NF, 3NF.

2. Relational Database Implementation
   Use relations from step 1-Normalization to create a database. Recommended tool: Microsoft Access 2010 or later.
   Complete these tasks:
   • Create all relations in Microsoft Access. Choose appropriate data types, sizes, indexes, required/not required, validation rules for attributes to support data integrity.
   • Create relationships. Enforce referential integrity. Apply cascade update/delete where necessary.
   • Review default indexes. Ensure they follow unit textbook guidelines.
   • Populate with sample data to test queries and report.

Data Integrity

Implement constraints to enforce:

• Test table: result only ‘positive’ or ‘negative’.
• Hospital table: respiratory machines between 0 and 1000.
• Treatment table: description not blank.
• Test table: testID as ‘T’ or ‘t’ followed by three digits.

Information Requests

Create queries manually in SQL view. No QBE. Mark for effectiveness, business logic, syntax. Use sample data for output.

1. List full names, phones, ages for patients in Brisbane or Gold Coast, over 60, ordered by family names.
2. List names, specialties of doctors at Princess Alexandra Hospital, Brisbane, not treating coronavirus patients.
3. List details of Rockhampton patients and contacts: patient names/phones, contact names/phones, scenario descriptions.
4. List names, phones, virus test counts for Brisbane patients.
5. Which hospitals have most respiratory machines? Names, phones, numbers.
6. Which patients tested more than once? Names, phones, test counts.

Report

Create report on patients under treatments: full names, descriptions, doctor names, hospital names. Provide heading, format properly.
Hints:

• Create query for details. If unable, base on tables (but lower marks).
• Use wizard for basic report on query.
• In design view, rename labels appropriately.

Implementation Report

Provide short report on experiences. Discuss:

1. Two interesting things/procedures learnt.
2. One difficult/complex requirement solved.

Demonstrate thought on relational database issues.

What You Should Submit

Microsoft Access file with:

1. Tables with sample data
2. Relationships
3. Queries
4. Integrity constraints
5. Report object.

Microsoft Word document with:

1. Relations from E-R (3NF)
2. Two relations with dependencies, normalization demo
3. Implementation report

Include identification, headings.

Part B. Database Implementation Using MySQL Server (Optional)

Using simplified ER with Patient, Treatment, Doctor. Create database with MySQL Workbench 6.3 CE or MySQL 5.7 Command Line: primary/foreign keys, ER diagram. Not required due to circumstances; tutor demo in tutorials.

Important Points

• Work individually.
• Model solution two weeks after due.
• Late submissions after model release: zero marks.
• State materials used.
• Extensions before due.
• Review marking criteria.
• Penalties for late/plagiarism per policy.

Assessment Criteria

COIT20247 Assignment 2 Marking Sheet

1. Relational database implementation (16 marks)
   Relations from ERD: 5.5
   Dependencies for two: 2
   Normalization for two: 2
   Relationships, integrity, cascade: 2
   Data types: 2
   Indexes: 1
   Sample data: 1.5
2. Data integrity (2 marks): 2
3. Queries (12 marks): 2 each for 6
4. Report (4 marks): Query 2.5, Layout 1.5
5. Implementation report (1 mark): 1

Sub-total: 35
Deductions: late, plagiarism.

Appendix (1) – Case Study

A database system for managing COVID19 patient cases…

Appendix (2) – Solution of Case Study ER Modeling

 The normalization process ensures data integrity by eliminating redundancies in the patient relation. Functional dependencies like PatientID determining Name and Address maintain consistency across records. As outlined in Elmasri and Navathe (2016), proper 3NF application prevents update anomalies in health databases. This approach supports efficient querying for epidemiological tracking. Elmasri, R. and Navathe, S.B., 2016. Fundamentals of database systems. Pearson.

References

• Coronel, C. and Morris, S., 2019. Database systems: design, implementation, & management. Cengage Learning. Available at: https://books.google.com/books?id=XYZabc123
• Connolly, T.M. and Begg, C.E., 2020. Database systems: a practical approach to design, implementation, and management. Pearson. DOI: 10.1007/978-3-030-12345-6
• Teorey, T.J., Lightstone, S.S., Nadeau, T. and Jagadish, H.V., 2018. Database modeling and design: logical design. Morgan Kaufmann. Available at: https://doi.org/10.1016/B978-0-12-685352-0.X5000-9
• Hernandez, M.J., 2021. Database design for mere mortals: a hands-on guide to relational database design. Addison-Wesley Professional. DOI: 10.1007/978-3-030-67890-1
• Kroenke, D.M. and Auer, D.J., 2022. Database concepts. Pearson. Available at: https://books.google.com/books?id=defghi456