Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due

MDS604_Assessment 1_Assignment

Task Summary

In this assignment, you are required to create the database design for the given business scenario. There are three tasks in this assignment. The answer must be submitted individually using the given template, carrying 20% weight. This assignment is due by 11:55 pm AEST Friday end of week 4 (22/11/2024).

Context

To complete this task please refer to the slides of week 1, week 2, and week 3. This assignment targets two learning objectives of this course:

• LO1: Develop a fundamental understanding of the architecture of relational database management systems.

• LO2: Demonstrate the expertise of the Entity Relationship (ER) Model and analyze database systems.

Problem Statement

SweetCare is a day care institute that has been offering childcare services in Australia for the last decade. They have multiple centres across the country. Till now, they have been using a spreadsheet to store their data. Now, they have decided to expand their business and want to use a centralized relational database to store their data.

Let’s assume that you have been hired to design their database. Given the following business rule, you need to design the database of SweetCare.

Business Rules:

· For every centre, a unique ID, name and location needs to be stored.

· A centre can offer multiple are packages. For every package, a unique ID, name, fee and number of days need to be stored in the database. There are two types of packages: LongDayCare and CasualCare. For CasualCare, the number_of_hours also need to be stored.

· A centre can employ many staff members. For every staff member, details such as a unique ID, name and address need to be stored. There are two types of staff: Fulltime and Causal.

· For Fulltime staff, the salary needs to be stored while the hourly rate is stored for the Casual staff. A centre can have many Fulltime and Casual staff members. A Fulltime staff must work at only one centre while a Causal staff can work in multiple centres. When a Casual staff member works at a centre, the number of hours worked is recorded.

· A child must enrol in one care package while a package can have many children enrolled. For every child, a unique identification number, name and address need to be recorded.

· A child must have one guardian while a guardian can be registered for one child only. For every guardian, a unique ID, name and contact number need to be stored. MDS604_Assessment 1_Assignment

Task 1: EERD [30 marks]

In this task, you are required to create an Extended Entity Relationship Diagram (EERD) using the Crow’s foot notation for the given scenario using the above business rules. The EERD should contain all necessary information such as entities, attributes, primary identifiers, relationships among entities with cardinalities, constraints, associative entities, supertypes, and subtypes. The EER diagram should specify the completeness (total/partial) and disjointness (disjoint/overlap) inheritance constraints on the generalization/specialization.

If cardinalities or constraints are not clearly stated in business rules, you can make valid assumptions. If making assumptions, make sure that:

· Only made if necessary

· Assumptions are stated clearly.

· Not contradicting the given business rules.

You can use draw.io to create your model.

Task 2: LDM [10 marks]

You need to convert the model created in Task 1 into a Logical model using the ER to the relational model mapping process. The logical model should include all required tables, columns, primary keys, and foreign keys for all the tables.

Task 3: Normalization [10 marks]

Which normal form is the following table in?

Convert the given table into the 3rd normalized form and show the conversion process to the third form. In each normal form, show the tables, primary Keys, and foreign keys.

Assessment Policy:

The following policies are applicable for all the assessments. Ask your teacher, if you need more details.

· Academic Integrity: Please refer to the SPI Academic Integrity Policy and Procedure.

· Special Consideration: Please refer to the SPI Student Assessment Policy and Procedure.

· Use of Generative AI: Students cannot use any generative AI tools (e.g., ChatGPT) to complete any assessment part.

· Late Submission: The general policy (unless advised otherwise) for late submissions of the take-home assignments will be as follows:

o There is no penalty for submission up to 15 minutes late (grace period).

o 10% of the total possible marks will be deducted if it is more than 15 minutes and up to 24 hours late.

o A further 10% of the total possible marks will be deducted for each 24 hours up to 3 days (including weekends).

o 100% of the marks will be deducted after 3 days and zero marks will be awarded.

Assessment Rubric