UNSW Business School
School of Information Systems and
Technology Management
INFS1603
Introduction to Business Databases
Term 1, 2024
Team Assignment (Part B)
SDN Early Learning and Care Centres
George Joukhadar UNSW ©
Version 1.3 – 28/03/2024
2 | P a g e
Contents
1. Overview ......................................................................................................................................... 3
2. IMPORTANT NOTE on File-Sharing Websites – Please read ........................................................... 3
3. Key Dates ......................................................................................................................................... 3
4. Aim of the Assignment .................................................................................................................... 4
5. Case Study – SDN Early Learning and Care Centres ........................................................................ 5
6. Tasks and Deliverables .................................................................................................................... 8
6.1 Report B: Tasks ...................................................................................................................... 8
6.2 Pitch Video ........................................................................................................................... 10
6.3 Format and Deliverables ..................................................................................................... 10
7. Assessment Criteria, Grading and Feedback ................................................................................. 11
8. Diaries ............................................................................................................................................ 11
8.1 Individual Diary .................................................................................................................... 12
8.2 Group Diary ......................................................................................................................... 12
9. Peer-Review .................................................................................................................................. 13
9.1 Within-Group Peer Review .................................................................................................. 13
10. General Rules ................................................................................................................................ 13
10.1 Proper Academic Conduct ................................................................................................... 13
10.2 Assignment Submission ....................................................................................................... 13
11. FAQs .............................................................................................................................................. 14
12. Appendix 1 - Entities and Relationships Provided ........................................................................ 15
3 | P a g e
1. Overview
For this project, you will be assessed on:
(1.) Two written reports (Part A and Part B)
(2.) Implement a database system using Oracle in Part B
(3.) Video presentation in Part B
• The individual assignment (Part A) is worth 20% of the marks of the course.
• The team assignment (Part B) is worth 25% of the mark of the course. Part B is
to be undertaken in groups of four (or three to fit) students, all of which must be
from the same tutorial class.
Team Assignment (Part B) is the focus of this document.
2. IMPORTANT NOTE on File-Sharing Websites – Please read
There are some file-sharing websites that specialise in buying and selling academic
work to and from university students.
You should be aware that you would be committing plagiarism if you download a
piece of work from these websites and present it as your own either wholly or
partially. For more information about Academic Integrity and Plagiarism, please click
here.
If you upload your original work to these websites, and if another student
downloads and presents it as their own either wholly or partially, you might be
found guilty of collusion — even years after graduation.
3. Key Dates
What? When?
Part A Released Week 2.
Assignment Part A Due: Submission
of assignment written report
Ms Word file submission via Moodle on or before
Monday 4 March 2024, 11.59am (Week 4)
Part B Group Formation Groups of 3 (or 4 to fit), Week 5 tutorial
Part B Progress Check 1 Week 8 Tutorial (1 Mark)
Part B Progress Check 2 Week 9 Tutorial (1 Mark)
Assignment Part B Due: Submission
of assignment written report
Ms Word and ZIP file submission via Moodle on or
before Monday 15 April 2024, 1.00 pm (Week 10)
Pitch video due Ms Word submission via Moodle on or before
Monday 15 April 2024, 1.00pm (Week 10)
Pitch video presentations Week 10 tutorials (pitch videos will be played in
class followed by Q/A)
These file-sharing websites may also accept purchase of course materials, such
as copies of lecture slides and tutorial handouts. By law, the copyright on course
materials (including this assignment brief), developed by UNSW staff in the
course of their employment, belongs to UNSW. It constitutes copyright
infringement, if not plagiarism, to trade these materials.
4 | P a g e
4. Aim of the Assignment
This course project (individual assignment + team assignment) addresses all the Learning
Outcomes of the course. By completing each requirement of this assignment, you will
demonstrate your mastery of the principles and practice of Business Databases:
▪ CLO1: Apply conceptual and relational database modelling methods.
▪ CLO2: Design and implement database systems.
▪ CLO3: Apply Structured Query Language to access and update databases.
▪ CLO4: Carry out professional teamwork.
▪ CLO5: Discuss professional responsibilities and ethical issues regarding databases.
The aim of the assignment is to design an Entity Relationship (ER) diagram for SDN
Early Learning and Care Centres at UNSW.
5 | P a g e
5. Case Study – SDN Early Learning and Care Centres
Consider the following scenario for SDN Early Learning and Care Centres at UNSW.
UNSW employs around 7,000 individuals, spanning academic and administrative roles. It's
worth mentioning that every employee of UNSW, including those in casual positions, has the
privilege of enrolling their children in the SDN Early Learning and Care Centres. This facility,
while situated within UNSW's premises and dedicated to addressing the childcare needs of
the university's working parent community, is managed by an external third-party. This setup
ensures that although the childcare centre is geographically located within UNSW, its daily
administration and operational tasks are the responsibility of the separate organization.
The SDN Early Learning and Care Centres stand out as a beacon of excellence in providing
top-tier childcare services to kids ranging from 6 months to 5 years of age. It is designed to
support UNSW’s working parents by offering a secure and enriching environment conducive
to children's learning, playing, and overall growth. To enhance its operational efficiency and
childcare management capabilities, the centre has embarked on the development of an
intricate database system. This initiative aims to refine and optimise the management of
various centre operations.
In your capacity as a database designer, you have been assigned the critical task of
developing the database schema to meet the comprehensive needs of the centre. The
envisioned database is expected to adeptly manage essential aspects of the childcare centre's
functionality, including but not limited to the enrolment of children, monitoring their attendance,
organising daily activities, overseeing billing and payment processes, and maintaining a
current inventory of necessary supplies. The goal of this advanced database system is to
facilitate seamless communication among parents, staff, and the management team, thereby
guaranteeing an orderly and effective childcare environment for the benefit of the children and
their families. This strategic enhancement is projected to elevate the quality of care and
streamline the operational framework of the Childcare Centre.
There are several key regulations and situations to bear in mind regarding the database for
the SDN Early Learning and Care Centres at UNSW:
- Priority for child enrolment at the SDN Early Learning and Care Centres is given to the
employees of the childcare centre. It is crucial to note that employees working for the
third-party organization managing the centre are not classified as employees of UNSW
and therefore do not possess a zID. SDN employees receive a 20% discount when
enrolling their children at the centre.
- Beyond the children of its employees, the Childcare Centre is also open to children
whose parents are part of the staff at UNSW. Starting from 2020, the centre expanded
its enrolment policy to include children of Postgraduate Research students, such as
those pursuing PhDs and Masters by research.
- The demand for spots at the Childcare Centre often exceeds availability, leading to a
lengthy waiting list. To secure a spot in advance, parents can fill out a pre-enrolment
form upon learning of the pregnancy. While UNSW employees are generally subjected
to a first-come, first-served basis on the waiting list, precedence is given to those
parents who already have a child enrolled at the centre, although they must still join
the waiting list and notify the centre upon pregnancy.
6 | P a g e
- Should an employee leave UNSW, they are presented with the option to continue their
child's enrolment at the Childcare Centre at an additional cost of $45 per day.
- In cases where both parents are employed by UNSW, the child's enrolment is
registered under the name of just one parent; there is no requirement for dual parental
enrolment in the database.
- The database must contain information about each child enrolled, including details of
their parents/guardians, emergency contacts, and any pertinent medical information
such as allergies or health conditions. UNSW is responsible for supplying the childcare
centre with an updated list of staff zIDs on a consistent basis.
- The childcare centre has the capacity to accommodate up to 227 children across 6
different classrooms, with each room having a set limit on the number of children it can
hold. For instance, the Koala room, designated for children aged 2-3, has a maximum
capacity of 40 children. The database is required to monitor the daily attendance of
both children and assigned staff members in each classroom, including time logs of
arrivals and departures. Additionally, it must detail the scheduled daily activities, meals,
and nap times for each room.
- Essential information about the childcare centre’s personnel must be stored in the
database, including the various qualifications a staff member may hold, ranging from
Certificate III to Diploma levels.
- Furthermore, the database is tasked with the management of billing and payment
records for the childcare services of each child, encapsulating details on billing cycles,
fees, and payment statuses.
- For parents who are eligible for the Australian Government Child Care Subsidy (CCS),
the subsidy is paid directly to the Childcare Centre in accordance with government
regulations. Parents are responsible for covering the remaining balance, often referred
to as the "gap fee," themselves.
- It is also important to keep an inventory of the centre's supplies, including toys and
childcare materials, ensuring each classroom is adequately equipped.
- In response to inflation and the escalating cost of living, the childcare fees have seen
an increment of $10 per day at the beginning of 2023, followed by an additional
increase of $10 per day at the onset of 2024. It is essential for the database to be
updated to reflect these changes accurately.
Consider these scenarios as examples of the varied situations that can arise in the context of
the SDN Early Learning and Care Centres at UNSW:
- Alex, a Research Assistant at UNSW since 2017, and Kim, a project coordinator at
UNSW, welcomed twins in 2018. Understanding the benefits offered by UNSW, they
were keen to enrol their twins at the Childcare Centre. Given the centre's policy and
the high demand for spots, Alex and Kim submitted pre-enrolment forms immediately
after discovering their expectancy. In 2020, they were delighted to have both children
accepted into the centre. In 2023, they found out that one of the twins has asthma,
while the other requires a gluten-free diet due to an allergy. The centre is committed
to accommodating the special needs of these children, ensuring their health and safety
are prioritized. Accordingly, these essential health and dietary requirements are
recorded in the database, enabling the centre to provide the necessary care and
support for each child. This ensures that all staff are aware of and can properly manage
the asthma care plan for the affected child, as well as adhere to the dietary needs of
the child who requires gluten-free options.
7 | P a g e
- Sara, an IT Specialist at UNSW for five years, and her husband, who is not affiliated
with UNSW, decided to adopt a child in 2019. Sara was aware of the childcare centre's
reputation and the priority given to UNSW employees' children. They submitted a pre-
enrolment form soon after starting the adoption process. In 2020, their adoption was
finalized, and their child was enrolled at the centre.
- Martin has dedicated 15 years of his career to UNSW, initially joining as a casual tutor
back in 2008. His journey at the university took a significant turn when he decided to
embark on a PhD in February 2010, successfully completing it by 2014. After a year of
continuing in his casual role, Martin transitioned to a full-time position as an Associate
Lecturer in January 2016. It was during this period that he met Janet, who, at the time,
was not affiliated with UNSW. The couple got married in November 2016 and
welcomed their first child in January 2018. They were able to secure a spot for their
child at the Childcare Centre in 2019. Janet, after concluding her maternity leave,
joined UNSW as an Executive Assistant in January 2020. However, Martin moved on
from UNSW in July 2021 to pursue an opportunity at a different university, leaving
Janet as the sole employee from their family at UNSW. To avoid the additional daily
fee of $45, Martin and Janet needed to inform the childcare centre of Martin's departure
and request that their child's enrolment be officially transferred under Janet’s name.
- Mary worked at UNSW as a school manager for ten years. Her personal life saw a
significant change when she married John, an Administration Officer at UNSW, in 2020,
and they enrolled their child in the childcare centre the following year. The couple's
journey took a turn in September 2022 when they decided to divorce. Seeking a fresh
start and to distance herself from the shared workplace with her ex-husband, Mary
pivoted towards a career in childcare education. While maintaining her role at UNSW,
she pursued an Early Childhood Education and Care (ECEC) Certificate III part-time
at TAFE NSW, a recognized institution offering certification in this field. Upon
completing her certification, Mary was fortunate to secure a managerial position at the
childcare centre. Additionally, she benefited from a 20% discount on her childcare
expenses, a support that helped her manage her finances more effectively.
8 | P a g e
6. Tasks and Deliverables
6.1 Report B: Tasks
Note: students will work in a group of 3 (or 4).
1. Use Appendix 1 below to provide a relational model/relational database
schema for the SDN Early Learning and Care Centres. Use the notation of the
lecture to draw the final relational model. Underline Primary Keys with a solid line
and Foreign Keys with a dotted line. You must include the entities and
attributes exactly as they are written in Appendix 1. You must include any
additional attributes that are relevant.
2. Implement a relational database in Oracle based on your relational database
schema:
- Write SQL DDL statements to create the table architecture of the above
relational model.
- You must include the entities and attributes exactly as they are written in
Appendix 1 below, and format these attributes appropriately.
- Additional attributes that are not listed in Appendix 1 but relevant can be
formatted and named freely, in line with the case and your determination of
the appropriate data types.
- Ensure that referential integrity between the tables is enforced.
- Populate the database with at least ten sample records in each table.
- Ensure that you have adequate data to run and test queries and showcase
the database.
- Ensure your sample data can return the queries as specified in the Q3.
- Do NOT change your group SQL account login password.
- Disregard the existing DB in the Group account.
- Note: If more than one person works on a table and makes changes, this
might create errors in the DB and the account. Please collaborate within the
group on who will be, what and when!
- Save the statements used to create your database (i.e. your Create and
Insert statements) as a .SQL or .TXT file and submit it with your report.
- Export your DB from Oracle and submit it as a .ZIP file with your report.
3. Develop the below SQL queries for the database. The SQL query scripts must
be inserted as TEXT (NOT as a snapshot/screenshot and NOT in an appendix).
SQL query scripts inserted as screenshots will NOT be marked. Printouts of the
results of each query (e.g., screenshots) should also be in the report following
each query (not part of the word limit).
a. Provide a list of activities (the names)
b. Provide a list of dates that any employee supervised (attended) classes – do
not list duplicates (i.e. which dates all employees supervised classes).
c. List all Parent Names (first and last name) who are UNSW-affiliated parents
(i.e. they can be either UNSW staff or UNSW PhD)
9 | P a g e
d. How many different qualifications are there? List the total number only and
call it “Total Qualifications”.
e. Summarise the total number of payments and the total payment amount
made for each Child. Show the children (by child ID) with the highest total
associated payment amount first.
f. Summarise employee performance in the following way – for each
employee, show:
i. Their ID and name (show as “Full Name”)
ii. If they are a parent (either a Yes or No result)
iii. Their hire date
iv. How many classes they have supervised
v. How many different rooms they have supervised in
4. Discuss ONE [1] ethical consideration and ONE [1] privacy concern that SDN
Early Learning and Care Centres must address when implementing a Big Data
initiative. Outline TWO [2] strategies to ensure the protection of children's and
families' data [maximum 250 words]. To answer this question, there are two
steps:
- Provide a screenshot of ChatGPT’s question and answer in your report.
The answer must be labelled clearly as “ChatGPT Question and Answer:”.
Failure to provide the ChatGPT answer will lead to a ZERO [0] mark for this
question. You can ask ChatGPT the question in the format you like.
ChatGPT’s question AND answer must be screenshotted.
- Provide your answer below the ChatGPT’s answer. Your answer must be
clearly labelled (e.g. Our Answer:). You can build your answer based on
ChatGPT's answer, or you can have your own answer. The mark will be
allocated for your answer. Both answers must be on the same page of your
report to make it easier for the marker to compare the two answers.
5. Write a collective reflection (no more than 250 words) focusing on your
collaboration as a team. This reflection should be a unified submission
representing the entire group's perspectives and experiences. Specifically,
address the following points:
- Reflect on how working in a group helped you to learn from each other.
- Reflect on members’ responsibility and contribution to the group/team
activities, and how you have perceived each other.
- Reflect on conflicts (if any) and how you resolved it.
- Reflect on the overall learning experience garnered from undertaking this
task as a group.
10 | P a g e
6.2 Pitch Video
Final presentation: This is the project’s final presentation. Your team will be
presenting your proposed solution to SDN executives and similar businesses (i.e.
you need to convince your audience to implement your solution).
Video Pitch:
- Each group’s video pitch should be a maximum of 5 minutes. Not all
members need to have a role in the video (you can if you want), but at
least 2 members.
- Audience: the business managers (non-technical audience, not familiar
with Databases).
- Purpose: to convince executives to implement your DB design.
Requirements:
- The slides AND the presenter’s face should be shown in the video. Slides
are optional. (you could use Zoom or Teams to record your video)
- All Team Leaders (or delegate person): you must submit your video on
Moodle before the due date.
- The file name should be as follows: GroupID-VideoPresentation.mp4
(you should only use .mp4 video format).
- zIDs and names of all members should be included on the first slide of
the presentation and video.
- Include the name of the person presenting on the corresponding
slide.
- The video file could not be more than 200MB in size.
A possible structure for the video presentation could be:
- Clearly and briefly introduce your team, the presentation’s structure and
key points.
- Clearly and concisely identify how your Database solution makes things
different from what the business does today.
- Clearly and concisely identify the benefits of your solution to the business.
6.3 Format and Deliverables
For the written report of Part B, you need to consider:
• UNSW Coversheet. Submit your assignment with a signed coversheet (typed
signatures or initials are allowed). Failure to include the UNSW coversheet with
signatures/initials will lead to 5% penalty, and no marks will be released until the
coversheet is received.
• Length. Please see the limit for each question (i.e. no 10% leniency in the word
limit). You cannot go beyond the limit.
• Table of Contents. Should not exceed one page, restricted to two levels of headline.
The headline and report structure should follow the above deliverables.
11 | P a g e
• Format. The style/format of the report can be as you find it appropriate and useful.
You should use headings, sub-headings, bullet points, diagrams, and tables as
appropriate. The file format of the report is only Microsoft Word.
• References. References and citations (if any) should follow either the UNSW
(Harvard) or the APA 7th citation style standard.
• Turnitin Title and File Naming Convention: Your Turnitin Title and your submitted
file should be GroupID-Assignment-B.docx and GroupID-Assignment-B.ZIP.
Failure to use the correct file naming will lead to a 5% penalty.
• Each group member must keep a backup of your DB.
• Text inserted as pictures will NOT be marked. For example, a table cannot be
screenshotted as a picture. Only figures (e.g. ER model, Relational model, figures,
etc.) can be inserted as pictures.
• The clarity and readability of your report are very important.
7. Assessment Criteria, Grading and Feedback
In assessing your work, two questions will be considered:
1. Have you fulfilled ALL of the requirements of the assessment task?
2. How well have you demonstrated your achievement of the learning outcomes associated
with the task?
Feedback on this individual assessment task will be provided:
▪ Summative, by the teaching team within 10 working days.
8. Diaries
An important part of a project is to record and to evaluate the teamwork. The purpose
of the diary is to reflect and learn from the project (in addition to achieving the
project’s aims in a narrow sense). The project requires that each team member to
keep a personal diary, and the group to keep a group diary (diaries might be asked
for in cases of disputes) and that the group includes a critical reflection in their report.
12 | P a g e
8.1 Individual Diary
You, as an individual member of a group, are required to keep a diary of your work
activities. The individual dairy should record your work on the assignment and
should note details such as what you did and when you did it. You may also record
meeting notes in the diary. The individual diary is NOT to be submitted with the
assignment and will NOT be marked. The individual diary may be consulted by the
LiC in case of group disputes. Failure to produce an individual diary on request by
the LiC will be taken as evidence for a lack of a structured effort in the project.
8.2 Group Diary
Your group’s dairy must record all the activities that take place in completing the
assignment. The group diary is NOT to be submitted with the assignment and will
NOT be marked. The group diary may be consulted by the LiC in case of group
disputes.
The format of your group’s diary is up to you, but it must record the following details
for each group activity:
• A group needs to agree on who is responsible for keeping the group diary.
• Record what the activity (meetings, work) entailed.
• Record location, time, date and duration of a group activity.
• Record who was present at the activity.
• For “next actions”: specify who is doing what by when (Action plan)
• Signatures of all members, or other suitable forms of demonstrating that all members
have seen and approved meeting minutes and other records in the group diary.
The Team Leader is responsible for keeping the group diary unless other
arrangements are made within the group.
13 | P a g e
9. Peer-Review
9.1 Within-Group Peer Review
In general, equal contribution of group members is expected. Significantly unequal
contributions should be discussed in due time within the group. In the case that a
dispute emerges, the group needs to discuss with the LiC. At the end of the
assignment period, you need to perform a within-group peer review to evaluate the
contribution of all group members (including yourself) to the group project. The
purpose of the within-group peer review is to critically reflect on the group work, to
prevent “free-riding” and to redistribute marks between group members in cases
where free-riding or unequal contributions occur. Claims of unequal contributions,
especially if contradicting the evaluation of others, will need to be substantiated with
evidence (e.g., group diary, personal diary, meeting notes, emails, Facebook
messages, etc.). The LiC will make a final judgement in the case that a dispute
emerges, calling in the group, considering earlier discussions and submitted
evidence.
10. General Rules
10.1 Proper Academic Conduct
All assignments need to follow UNSW’s guidelines regarding proper academic
conduct. The submission of materials that are non-original or have been submitted
elsewhere will be considered plagiarism. Plagiarism is unacceptable. All instances of
plagiarism or other academic misconduct will be pursued. Plagiarism may lead to
you failing this course and may have negative consequences for your studies at
UNSW. The general UNSW guideline on academic conduct is available online.
10.2 Assignment Submission
Assignments are to be submitted via Moodle on, or better before, the due date. Late
submission of assignments is not desirable, disrupts the course timelines and is a sign
of poor time management, and will lead to reduced marks. The late submission of
assignments carries a penalty of 5% of the awarded marks for that assignment per day
of lateness (including weekends and holidays). For example, a 70 marking would be
reduced by 3.5 marks per day of lateness. An assessment will not be accepted after 5
days (120 hours) of the original deadline unless special consideration has been
approved. An assignment is considered late if the requested format, such as hard copy
or electronic copy, has not been submitted on time or where the ‘wrong’ assignment
has been submitted.
Students must plan ahead and submit on time. Providing an extension (via Special
Consideration) is unlikely to be granted for this assignment task.
14 | P a g e
11. FAQs
Q: Should we include the coversheet in the first page of the word file?
A: Yes for sure, the cover sheet should be the first page of the Word document.
Q.: Are we allowed to get help for Assignment in the PASS sessions?
A: As mentioned in the Course Orientation slides, teaching staff and PASS leaders do not
provide direct help with the Assignment. However, you can ask general questions.
Q: As there is a word limit (e.g. 250 words), is there is 10% lenience
A: No, you must not exceed the word limit. You might stay well beyond this limit.
Q: Is it allowed in a Word document for the Relational model to be in landscape format
but the coversheet to be in portrait format?
A: It is highly recommended to use landscape for the ER model / Relational model and keep
other pages as portrait.
Q: Can I insert a link in my Word document for the solution?
A: No, we will not access or mark links.
15 | P a g e
12. Appendix 1 - Entities and Relationships Provided
The following entities have been provided as a standard solution to Part A of the SDN Childcare
Centre case. Primary and foreign keys have also been provided to you (and some additional key
fields) – any additional attributes relevant to the case are to be considered and included in your
submission of Part B. No additional entities are required in your submission of Part B.
Note that Primary keys are underlined, foreign keys are italicised. An attribute can be both.
Parent (ParentID, First_Name, Surname, Parent_Type)
UNSW_Staff_Parent (Parent_ID)
UNSW_PHD_Parent (Parent_ID)
Previous_UNSW_Parent (Parent_ID)
SDN_Employee_Parent (Parent_ID, Employee_ID)
Child (Child_ID, Parent_ID, Waitlist_ID)
Waitlist (Waitlist_ID)
Child_Attendance (Child_ID, Class_ID)
Child_Medication (Child_ID, Medication_ID)
Medication (Medication_ID)
Payment (Payment_ID, Child_ID, Payment_Amount, Payment_Date)
Employee (Employee_ID, Hire_Date, First_Name, Surname)
Employee_Qualification (Employee_ID, Qualification_ID)
Qualification (Qualification_ID)
Employee_Attendance (Employee_ID, Class_ID)
Class (Class_ID, Room_ID)
Room (Room_ID)
Room_Inventory (Room_ID, Inventory_ID)
Inventory (Inventory_ID)
Class_Activity (Class_ID, Activity_ID)
Activity (Activity_ID)