This is an individual assessment. You must not collaborate or confer with others. You ，may help others by verbally explaining concepts and making suggestions in general terms, but without directly showing or sharing your own work. You must develop the logical structure, the details of your scripts, and the database on your own, even if you are working alongside others. Any work that is copied or shares a similar logic to others will receive zero marks for both parties.

The University policy on Academic Integrity can be found here.

GenAI Usage: Partial Usage Permitted

GenAI tools include, but are not limited to, large language models (e.g. ChatGPT, Copilot, Perplexity, Bard), grammar/writing software (e.g. Grammarly Premium) language translation software (e.g. Lokalise, Quillbot), and coding (e.g., Qodo, Github Copilot). This is not an exhaustive list.

Acknowledgement Requirement: Any use of GenAI tools for data generation must be clearly acknowledged. This includes identifying the GenAI data generation tool used, describe how it was used, including the prompts utilised, and when it was used.

Compliance: Assessment submissions will be analysed for evidence of the use of GenAI, and any student suspected of violating this GenAI Usage may be referred to the Proctor.

Acceptance: By submitting your assessment, you accept and have agreed to adhere to the GenAI Usage as outlined above.

PART A

Scenario

The Health and Wellness Centre at LincolnUniversity maintains records for students who access medical and wellness services. These services are available exclusively to students currently enrolled at the university.

For each student, the centre records a student ID, full name, home address, phone number, university email, date of birth, and gender. Additional information includes degree program, student category (e.g., undergraduate, postgraduate, exchange), enrolment status, blood type, allergies, medical conditions, and any relevant medical notes. Students may also provide an emergency contact with name, relationship, and phone number, but this is optional.

Health Centre Staff and Service Units

The Health and Wellness Centre employs staff such as doctors, nurses, counsellors, physiotherapists, and administrative personnel. Staff details include staff ID, name, phone number, date of birth, gender, position, specialisations, employment type, and office location.

The centre is organized into service units such as the Medical Clinic, Counselling Unit, Physiotherapy Unit, and Health Education Unit. Each unit has a unique ID, name, location, phone number, and operating hours. Every service unit is managed by a senior staff member, and some staff may oversee more than one unit.

Appointments

physiotherapy), and status (scheduled, completed, cancelled, or missed).

Each appointment involves a student, a staff member, and a service unit. Students may have multiple appointments over time, and staff may conduct multiple appointments.

Medical Records, Prescriptions, and Referrals

Some appointments generate a medical record, but not all. Each appointment that does so is associated with exactly one medical record, which includes diagnosis, treatment, follow-up instructions, and additional notes.

Medical records may include prescriptions if medication is recommended. Each prescription records the medicine, dosage, frequency, duration, and special instructions. A medical record may also result in referrals to other units within the centre or to external specialists. Each referral records the date, destination, reason, and status.

Billing and Payments

Wellness Programs and Enrolment

The centre offers a range of wellness programs, including fitness courses, stress management workshops, nutrition seminars, and mental health awareness sessions.

Each program has a unique ID, name, description, start and end dates, location, and can accommodate up to 20 participants. Students may enrol in up to three programs.

Task 1: ERD Design (25 Marks)

• Identify all strong entities

• Identify weak entities

• Identify associative entities if needed

• List relevant attributes for each entity

• Identify the primary key for each entity

• Identify foreign keys for each entity

• Identify relationships between entities and label them with verbs

• Decide whether relationships are one-to-one, one-to-many, or many-to-many.

• Decide the minimum and maximum number of occurrences for each relationship.

• Clearly indicate optionality on both sides of each relationship.

• Ensure all relationships are connected to the correct entities.

• Label relationships clearly.

• Use Crow’s Foot notation consistently.

Task 2: ERD Validation (10 Marks)

Use normalisation techniques to validate the ERD you created in Task 1. Explain and show how each table (entity) in the ERD satisfies the criteria for Third Normal Form (3NF).

Redraw the final version of the ERD to incorporate any necessary changes, if any, identified during the validation process, making sure that the entire model fully meets the requirements of 3NF. Document the changes made and provide explanations for the reasons each change was required.

Task 3: Data Dictionary (10 Marks)

Write a Data Dictionary that provides a detailed description of all tables (entities) in the finalised ERD.

For each table, list all attributes along with their content descriptions, data types, field lengths, formats, value ranges, required or optional status, primary and foreign keys, foreign key reference tables, and any other relevant details about the attributes that support the database implementation.

Document any decisions made on attribute characteristics, providing clear explanations for the rationale behind each decision.

Task 4: SQL Table Creation (10 Marks)

Write a Structured Query Language (SQL) script to create the table structures for the finalised database design, following the details in the data dictionary from Task 3.

Make sure that all table attributes and necessary constraints (entity integrity, referential integrity, and relationships) are defined correctly.

Task 5: SQL Data Population (10 Marks)

Write a SQL script to populate the database tables with at least five (5) rows of test data for each table. Some tables may require more rows to properly reflect the design. The test data should align with the database design and not violate any constraints defined during the database creation in Task 4. Pay close attention to referential integrity constraints when inserting data.

PART B

You are required to write a series of SQL queries to extract information from a relational banking database named AuroraBankDB maintained by Aurora Bank, a New Zealand– based financial institution. The database contains tables for customers, branches, accounts, transactions, loans, and loan payments.

Your queries should demonstrate the ability to retrieve, filter, and aggregate data to answer questions about customer activity, account balances, transactions, loan details, and branch performance. Use SQL concepts such as joins, grouping, aggregation functions, subqueries, and conditional logic as needed to complete the tasks.

Download the PARTB.zip folder from LEARN, which includes the BankLoan.sql file to set up the database on your local machine. Refer to the Entity-Relationship Diagram (ERD) shown in Figure 1, which will guide you in understanding the relationships between different entities in the database.

Task 6: SQL Queries (35 marks)

What to Submit

Marking Criteria