IS5102 – Database Management Systems Solved

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 tech-niques 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 commu-nicate 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 de-scription, 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 inter-val. 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 enti-ties. 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; speciali-sation/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