Modelling Database Requirements with E-R Diagrams

Modelling Database Requirements
with E-R Diagrams
IS5102 – Database Management Systems

33.0% of continuous assessment for the module
(MMS is the definitive source for deadline and credit details)
In this assignment, you are asked to model a scenario for its database aspects, using entity-relationship
models to express models and requirements.
You are expected to have read and understood all the information in this specification at least a
week before the deadline. You must contact the lecturer regarding any queries well in advance of the
deadline.
Purpose
This practical will help practice and develop your skills in:
• analysing and defining database requirements;
• modelling data using E-R model techniques;
• understanding how to utilise functional requirements;
• understanding and reporting requirements.
Summary
This is an individual assignment.
In lectures we have talked about modelling database scenarios, in particular at the conceptual level
using E-R models. This assignment presents a short scenario and asks you to model it using the techniques presented.
Submission
The primary output will be a report, which should be in PDF format, with sections per tasks 1-3 as
below. Include a picture (machine-generated or scanned) of your model in the report. Submit this
report via MMS by the deadline, checking to make sure that the version you submit is the one you
mean to submit.
1
Scenario
A wildlife protection society needs to organise their data about observations of butterflies in the UK, as
well as about its society members, staff members, and volunteers. Next summer they will run a public
engagement event, called “butterfly count”, for which they need a new database management system.
The following initial specification has been given:
We have a system of regional branches, and each member of the society is associated with a
branch in a region they live. We also employ some staff members, which may work for some
branch(es) or in our central office. For staff members, we need to record their annual salary,
and for society members - the monthly membership fee they pay. We keep contact details
(postal address, phone number(s), email address) for society staff and members, and also
ask contact details from volunteers when they submit butterflies observations. We communicate with everyone involved in society activities, e.g. by informing its members to events
organised by their branch, and by inviting non-members to join the society. When known,
communication includes contact details of the local branch for enquiries.
We keep a list of UK butterflies species, with their English names, Latin names, brief description, and a collection of photos of each species. This information is displayed on our
website to help all participants of the butterfly count.
To perform a single observation, we ask to record all butterflies seen within 15-minute interval. To report it to our database, a person (who could be a society member, a staff member or
a volunteer) records the date and the time interval of the observation, names of the species
observed, the number of observed butterflies of each species, and the location of the place
of the observation, given by its coordinates, postcode and county.
Besides using our records to find out facts about sightings of particular species, we will
celebrate the success of the butterfly count by demonstrating its uptake across the UK.
Tasks
Task 1
From the given scenario, document the intended use of the data and any other factors that you consider
relevant. You should think particularly of the questions that will be asked of the data. As part of this,
come up with (at least) three queries that you feel are likely to be made by stakeholders (use plain
English to formulate questions and queries).
Write a specification of the data from the scenario that will be stored in the relational database.
It should be written in plain English (instead of using a formal notation). You should think of what
constraints may exist on the data, and how to express any such constraints. This specification will be
the basis for your database definition, so it should be as precise as possible. Document clearly any
assumptions you make.
Task 2
Design a representation of the data in terms of entities, attributes and relationships between entities. Construct an E-R diagram to depict this representation. Your E-R diagram should be submitted
electronically as part of your report. It is recommended that you use some drawing software in the
construction of your model (https://www.diagrams.net/, with its online version also known as
https://draw.io/, or drawing tools in Microsoft 365 components will suffice). However, if you
wish, you can use scanned versions of hand-drawn diagrams. These are acceptable provided they are
neat and legible.
In your report, indicate where the data to answer the queries from Task 1 lives in your data model,
showing that your data model does satisfy the requirements specification.
2
Task 3
Reflect on the process of doing database modelling, linking back to the work done in Tasks 1 and 2.
What did you feel you did well? What did you find challenging? What problems did you encounter, if
any, and how did you try to resolve those? Did you find gaps between the theory taught in class and/or
presented in textbooks, and the practical application? What would you do differently, if anything? This
should be a short reflective section of your report. You can say as much or as little as you want, but as
guidance, between half a page to a page should be enough.
Marking
A specification capturing the requirements, a correct E-R model which captures the scenario, and a
reflective section of the report make up the basic requirements. A good report covering all of these will
be sufficient to get marks up to 16.
To get the higher marks (17 and above), advanced work is required. This can include (for example)
• insightful and relevant questions for the requirements;
• exceptional clarity and understanding in the requirement specification;
• good style in E-R modelling including (for example, where applicable) weak entity sets; specialisation/generalisation; a range of simple, compound and derived attributes etc; or
• report demonstrating excellent understanding of the modelling process.
Policies and Guidelines
Marking
See also the standard mark descriptors in the School Student Handbook:
https://info.cs.st-andrews.ac.uk/student-handbook/learning-teaching/feedback.
html#Mark_Descriptors
Lateness
The standard penalty for late submission applies (Scheme B: 1 mark per 8 hour period, or part thereof):
https://info.cs.st-andrews.ac.uk/student-handbook/learning-teaching/assessment.
html#Lateness_Penalties
Good Academic Practice
The University policy on Good Academic Practice applies:
https://www.st-andrews.ac.uk/students/rules/academicpractice/
3