2022¶

Faculty of Engineering, Mathematics and Science
School of Computer Science & Statistics

Exam Details¶

Instructions to Candidates¶

1. Attempt three questions in total.
2. Question 1 is mandatory. Answer any two questions from Questions 2, 3 and 4.
3. All questions carry equal marks. Each question is scored out of 25 marks.

Question 1 — SecondChance Dog Shelter¶

SecondChance is a dog shelter for rehoming stray dogs. It provides veterinary treatment for stray dogs and tries to find them new owners. It is staffed by a number of vets, veterinary nurses, assistants and administrators.

SecondChance is installing a new database to store information about:

• The dogs in the shelter
• The treatments performed on the dogs
• The staff in the shelter
• The new owners found for the stray dogs

For each dog, the database stores:

• The microchip identity (either previously existing or newly assigned)
• The name(s) of the breed(s), where identifiable
• A description of the dog

For each treatment instance for a dog, the database stores:

• A unique identifier for that treatment instance
• A description of the treatment (e.g. grooming, vaccination for parvovirus, etc.)
• The date the treatment commenced
• The date the treatment completed (some treatments span several weeks)

For each staff member, the database records:

• The staff member's unique ID
• Their role in the shelter (e.g. vet, veterinary nurse, etc.)
• Their name

The database also records which staff members were involved in each treatment of each dog.

The database stores the names, contact addresses, date of adoption, and phone numbers of each new owner who has adopted a dog. An owner may adopt more than one dog, but a dog can only be adopted by a single owner at a time. If an owner returns a dog after adoption, the adoption record is deleted.

(i) Entity–Relational Model — [8 marks]¶

Develop an Entity–Relational (ER) model for the above database, stating any assumptions you make.

(ii) Relational Model & Normalisation — [6 marks]¶

Using appropriate mapping techniques, map the ER model to a relational model, giving explanation of your choices. Show the functional dependencies between attributes within each table, ensuring each is in Boyce–Codd Normal Form (BCNF). Identify the primary keys and foreign keys for each relation.

(iii) SQL Queries — [11 marks]¶

Give the SQL command(s) for the following queries:

(a) Retrieve a list of all dogs who have had the treatment "Vaccinated for Parvovirus".
— [2 marks]

(b) Count the number of veterinarians involved in all treatments for the dog with microchip number '00454'.
— [3 marks]

© Suppose the dog with microchip number '00454' has been returned to the SecondChance shelter. Give the SQL required to update the database.
— [3 marks]

(d) Suppose the database must record additional information about owners: whether an owner already owns any dogs. Give the SQL commands to augment the database to store this new information when available. What SQL command(s) would need to be performed to deal with the owner information previously entered but which did not have this information?
— [3 marks]

Question 2 — Employee Remuneration Database¶

A database storing information about employees and their salaries contains the employee and remuneration relations. Answer the questions that follow.

(i) View-Based Access Control — [6 marks]¶

A programmer, Mary, needs to access some but not all of the data in the employee relation on a regular basis. Describe how she can be given access to all employees' id numbers, first names and surnames but not their date of birth, and provide the code in SQL.

(ii) Salary Constraint — [7 marks]¶

The owner of the company, Michelle, has decided to give a salary of no less than €20,000 to everyone who does not receive a bonus and is not eligible for outside earnings. How should this constraint be applied to the remuneration relation?

(iii) Transaction Rollback — [6 marks]¶

In the schedule below, two transactions — Course Registration and Course Transfer — are executing concurrently. Describe in detail what takes place in the case that Course Transfer fails and is rolled back on its final write operation.

(iv) Concurrent Data Consistency — [6 marks]¶

In the schedule below, two transactions — Course Registration and Course Transfer — are again executing concurrently. Describe in detail what takes place in terms of data consistency.

Question 3 — MessageMania & GDPR¶

MessageMania is a social media platform based in Ireland that allows users to publish short messages online. It has become popular for encouraging concise content that other users can quickly digest. MessageMania currently has over 2 million users worldwide.

The platform allows two levels of access for user posts:

1. Public (default): Messages are published to everyone with an account on the platform, as well as any internet user who comes across the message via the general MessageMedia website or search engine.
2. Protected: Messages are shown only to users who are registered followers of the posting user — i.e. a follower is another user who has voluntarily signed up to receive your messages, with a list of your followers also being available to you.

The Bug¶

A bug in MessageMania's design means that when an Android user changes their email address (the one linked to their MessageMania account), protected messages are automatically switched to public.

Jessica, a programmer working for MessageMania, became aware of the issue on 26 December 2018 and realised that users would not be informed of this bug — to the best of her knowledge, the company itself was also unaware of it.

(i) GDPR Issues — [10 marks]¶

Describe in detail all issues related to GDPR that the above scenario raises.

(ii) Internal Reporting — [8 marks]¶

Who should Jessica inform in MessageMania about the potential issue she discovered, and following on from that, who should that individual inform about the situation?

(iii) Company Impact — [7 marks]¶

What is likely to be the impact of the issue Jessica discovered for the MessageMania company?

Question 4 — Enhanced ER & SQL Triggers¶

Part A — IrelandTalentProductions¶

In Enhanced ER modelling, new notations are introduced to support inheritance of attributes and relationships.

Suppose the entertainment talent management company IrelandTalentProductions seeks to set up a database for the entertainers and broadcasters it manages. The database needs to store information about entertainers such as date of birth, name, contact address and phone number.

Using inheritance, the database must model different kinds of entertainers and their associated information:

(i) Enhanced ER Model — [8 marks]¶

Using the EER notations given in this course, develop an Enhanced Entity–Relational (EER) model for the above database, stating any assumptions you make.

(ii) Relational Model — [8 marks]¶

Develop a relational model giving table definitions for this database and explaining your choice of tables, primary keys and foreign keys.

Part B — MyAssistant Virtual Assistant¶

MyAssistant is a virtual assistant (conversational bot) which can carry out specific actions, termed MyAssistant Skills.

Skill 1 — Room Bookings: If a user requests a meeting in a particular room, MyAssistant attempts to make a room booking. On success, it emails all attendees (identified as part of the original meeting request) to confirm the time and room number. For each booking, MyAssistant stores:

• The room number (unique)
• The start and stop time and date of the booked meeting
• The email address of the requesting person who booked the room
• The email addresses of those attending the meeting

Skill 2 — Reminder Service: A user can request a reminder email to be sent to him/her (or a set of users) about a particular event. The reminder can only be set to occur at a particular requested time and date.

Suppose the resultant database is modelled as the following tables:

Booking (BookingID, RoomNum, MeetingRequestor, StartDateAndTime, StopDateAndTime, ReminderID)
BookingAttendees (BookingID, MeetingAttendees)
Reminder (ReminderID, ReminderMessage, ReminderDateAndTime)
ReminderRecipients (ReminderID, ReminderRecipients)

Give the SQL commands for the following operations:

(a) Suppose a constraint was made whereby no meeting rooms can be booked before 7 am or after 9 pm. Give the SQL command to ensure that this constraint is met from the time the database was first created.
— [2 marks]

(b) Create a view of all the room bookings for room '101', their StartDateAndTime and the attendees who were invited to attend those room bookings.
— [3 marks]

© Suppose a MeetingAdmin table is created which consists of two attributes, BookingID and MeetingAttendeeNumbers. Write a trigger command to insert a row into the MeetingAdmin table when any meeting booking has been inserted or updated with more than 20 meeting attendees.
— [4 marks]

© Trinity College Dublin, The University of Dublin 20XX