ASSIGNMENT ONE

SCHOOL OF COMPUTING
COMP1350 S2 2023 – ASSIGNMENT ONE
Due: Week 6 Sunday 03 September 2023 at 11.55 pm
Introduction to Database Design and Management
Database Design Assignment
(Worth 20% of your grade, marked out of 100)
Table of Contents
Case Background- Task 1 ……………………………………………………………………………………………………………………………. 3
Case Background- Task 2 ……………………………………………………………………………………………………………………………. 4
Task Descriptions …………………………………………………………………………………………………………………………………………. 4
Task 1- ER Diagram (30 marks) ……………………………………………………………………………………………………………. 4
Task 2- EER Diagram (30 marks)………………………………………………………………………………………………………….. 4
Task 3- Logical Transformation- ER (20 marks) ……………………………………………………………………………….. 5
Task 4- Logical Transformation-EER (20 marks) ……………………………………………………………………………… 5
What to include in your report (as a PDF) ………………………………………………………………………………………………. 5
What to Submit for the Assignment ………………………………………………………………………………………………………….. 5
Marking Rubric ……………………………………………………………………………………………………………………………………………… 6
Case Background- Task 1
South African Conservation Society had a major internal restructuring in 2022. They started
storing data in a file-based system and then transitioned to using spreadsheets. As years
progressed and as conservation is now being taken more seriously, their data has grown
exponentially leading to a more efficient way of capturing data. You have recently employed a
database model to replace the current spreadsheets. You have been provided with the following
business rules about the society.
Reserve: Every reserve is uniquely identified using a primary identifier. Other details of a reserve
include the name, location, and size of hectares. Every reserve has a compliance regulation
description in place which is uniquely identified using a primary identifier. Other details of the
compliance include the description and a certificate number. Every reserve can be funded and
supported by multiple partner organisations. The details of the organisation include the name,
the name of the manager, and their contact number. A partner organisation may fund/support
multiple reserves in South Africa. As a part of their funding, details such as the duration of the
support (in dates), and the amount are also recorded.
Wildlife: Every species of animal is uniquely identified using a primary identifier. Other details
of a species include the name and its binomial name. Every species lives in a certain habitat. The
habitat is uniquely identified using a primary identifier. Other details of the habitat include the
name and description. Also, the conservation status of a species must be recorded. It is uniquely
identified using a primary identifier. Other details of the conservation status include the name
and description. Every animal that is recorded is identified using a primary identifier. Other
details of an animal include the name, gender, birth date and the date of death.
Staff, Incidents and Observations: Every staff is provided with a unique staff number primary
identifier. The society also needs to keep track of other details about its staff like their name,
position, and their salary. The staff works across multiple reserves, and this is not something the
society intends to track. The staff are experts in working with different animals. They can list up
to three different animals they are comfortable working with. But they will have to at least be
comfortable working with one animal. For every animal they list, they must also add the number
of years of experience they have had working with the animal. The staff are responsible for
recording observations and incidents. An observation is recorded by staff when they notice a
behaviour/pattern in a species at a particular reserve. Other details such as the date and time of
the observation and the comments are recorded. An incident is recorded by staff when there is
an event/occurrence that happens at a reserve with individual animals. Please note, an incident
could involve multiple animals.
Tours: The society offers tours for visitors. Every visitor is uniquely identified using a primary
identifier. Other details of a visitor include their name and country. Every tour is uniquely
identified using a primary identifier. Other details of a tour include the name, cost, and duration.
Some tours are package tours, and this means a package tour is made up of multiple tours. Every
reserve will have vehicles registered. They are uniquely identified for each reserve, and this
means the vehicle identification numbers are reused across multiple reserves. Other details of the
vehicles such as their type, capacity, and rego plate number are also recorded. When a booking is
made, it involves one staff, a vehicle, a tour, and multiple visitors along with the date and time of
the booking. During the booking, each visitor in the booking can have any add multiple
requirements such as accessibility requirements, preferred language, etc.
Case Background- Task 2
Programs: The South African Conservation Society also offers several types of conservation
programs. Every program is uniquely identified using a primary identifier. Other details of a
program include the name, cost, and the duration. There are three types of programs: educational
programs, volunteer programs, and research programs. Educational programs have details such
as the target audience, the name of the certificate, and the resources it will require recorded.
Volunteer programs record the requirements of the volunteers and the name of the certificate.
Research programs record the name of the research and the data collection requirements. Also,
to note is that some programs that are educational are also volunteer programs and vice-versa. A
research program can be funded by multiple organisations. Every funding organisation is
provided with a unique identifier. The details of the organisation include the name, the name of
the manager, and their contact number. As a part of their funding, the amount every organisation
provides for the program is recorded.
Teaching Staff: The society employs teaching staff full-time to help with the conservation
programs. Every teaching staff is uniquely identified using a primary identifier. Other details of
staff include their name, educational qualifications, and their pay. There are three types of
teaching staff: Conservationists whose expertise and the number of years of experience are
captured; Researchers whose area of research and ORCID are captured; and Volunteer Staff.
Students: Every student is provided with a unique student number. The society also needs to
keep track of other details about its students like their name, country, and their email. A student
can enrol in multiple programs, and the enrol date is captured.
Delivery of the programs: Any of the teaching staff can be involved in the educational programs.
A volunteer program is run by one volunteer staff, and the researchers can be involved in utmost
one research program. A research program will be run by a researcher, at least one up to three
conservationists, and up to five research students who are exclusively enrolled to work on the
research. There are also research programs that have no students.
Task Descriptions
Task 1- ER Diagram (30 marks)
Based on the business rules provided under case background for task 1, you are expected to
construct an ER diagram using a Crow’s Foot notation. The ER diagram should include entities,
attributes, and identifiers. You are also expected to show the relationships among entities using
cardinality and constraints. You may choose to add attributes on the relationships (if there are
any) or create an associative entity, when necessary.
Task 2- EER Diagram (30 marks)
Based on the business rules provided under case background for task 2, you are expected to
construct an Enhanced-ER (EER) diagram. The EER diagram should include entities, attributes,
and identifiers. You are also expected to show the relationships among entities using cardinality
and constraints. You may choose to add attributes on the relationships (if there are any) or create
an associative entity, when necessary. Your diagram should also specify the complete (total) and
disjoint (mutually exclusive) constraints on the EER.
Task 3- Logical Transformation- ER (20 marks)
Based on your ER from Task 1, perform a logical transformation. You must include the final list of
tables with primary and foreign keys denoted with (pk) and (fk). Please note, if there are errors
in the ER diagram, this will impact your marks in the transformation. However, the correctness
of the process will be considered.
Task 4- Logical Transformation-EER (20 marks)
Based on your EER from Task 2, perform a logical transformation. You must include the final list
of tables with primary and foreign keys denoted with (pk) and (fk). You will have to use your
judgement about 8a/8b/8c/8d for the transformation and use the best fit. Please note, if there
are errors in the EER diagram, this will impact your marks in the transformation. However, the
correctness of the process will be considered.
What to include in your report (as a PDF)
• To present your answers to these tasks, please use the template provided in the
assignment folder.
• Fill out the details provided on the first page of the template.
• For Tasks 1 and 2, attach the image in the document of the ER model you have created.
You can use a tool of your choice to generate the diagram. Digital copies of hand-drawn
diagrams will not be accepted.
• For Task 3 and 4, write the answers under the right space in the template. Steps must be
clear with primary and foreign keys listed explicitly.
• Assumptions: If you have any assumptions, please list them down in your report. (e.g.,
about relationships between entities in your ER/EER Model). Please note only make
assumptions if something is not clear. Assumptions will only be valid if they are not
contradicting something which is given in the case study.
• If your images lack clarity, you will not be given any marks. Tutors can zoom in to check
the diagram but should not have to deal with images that are blurry / fuzzy. Whatever is
submitted is the final submission. So, please make sure your image is readable.
What to Submit for the Assignment
• One pdf document with all the answers included. Please do not submit multiple files.
• You can submit many times up until the deadline (so get an early version in just in case)
• Please submit your work on iLearn (there is a Turnitin submission link in the Assignments
section) as a report (as a .pdf ONLY) by 11.55 pm Sunday 03 September 2023. You need
to rename the template provided using this format (e.g., StudentName_StudentID.pdf).
• It is your responsibility to make sure you have submitted the correct file.
Marking Rubric
Marks Topic Explanation
30 marks
Task 1
Entities Existence of correct entities which cover the details
in the specification?
Attributes Correct attributes? Correct identification of primary
identifier?
Relationships
Existence of appropriate relationships?
Appropriate cardinalities?
Appropriate optional/mandatory values?
Existence of multiple relationships?
Valid Relationships?
30 marks
Task 2
Entities Existence of correct entities which cover the details
in the specification?
Attributes Correct attributes? Correct identification of primary
identifier?
Relationships
Existence of appropriate relationships?
Appropriate cardinalities?
Appropriate optional/mandatory values?
Existence of multiple relationships?
Correct Super
types/Subtypes
Correct Super types/Subtype entities?
Valid reuse of attributes?
Relationships Valid relationships?
Specialization
Constraints Correct disjoint/completeness used?
20 marks
Task 3
Valid Transformation Steps (Steps 1-7)
Final set of tables
20 marks
Task 4
Valid Transformation Steps (Steps 1-7, Step8, Repeat steps 2-7)
Final set of tables
Please note that due to nature of the questions, a more specific rubric cannot be provided but we
have provided you the various aspects that would be marked.