2023¶

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

Instructions to Candidates¶

1. Attempt three questions in total. Question 1 is mandatory. Answer any two questions from Questions 2, 3, and 4.
2. All questions carry equal marks. Each question is scored out of a total of 25 marks.
3. Do not begin the exam until instructed to do so by an invigilator.
4. Materials permitted: None

Question 1¶

A cinema chain is made up of a large number of movie theatres spread out across the country in different locations. Each theatre has its own schedule of movie screenings that is updated on a weekly basis.

The database needs to store any information relevant to the movie screenings, such as:

• Start time, date, running time
• Movie title, movie director
• And other related attributes

As well as information needed for the theatres, such as:

• Address, screens, and other facility details

(i) ER Model [8 marks]¶

Develop an ER model for the above problem, stating clearly any assumptions you make.

(ii) Relational Mapping [5 marks]¶

Using the appropriate mapping techniques, map the ER model to a Relational Model showing:

• The functional dependencies between objects
• The primary keys
• The foreign keys

(iii) SQL Queries [12 marks]¶

Give the SQL commands for the following:

a. Insert a new movie "Titanic 2" into the database with MOVIE_ID = 1012, including appropriate values for each of the attributes. [3 marks]

b. Find the name of all movies with a running time of less than 1 hour 30 minutes with a release date in 2019. [2 marks]

c. Find all of the movie screenings on today's date showing before 12 o'clock. [3 marks]

d. Find all of the movie screenings on today's date finishing before 12 o'clock. [4 marks]

Question 2¶

(i) Integrity Constraints [6 marks]¶

Describe three types of integrity constraints that can be applied to relational databases.

(ii) Referential Integrity in SQL [6 marks]¶

Describe how referential integrity can be specified in SQL, giving an example of your choice with a clear explanation of why referential integrity constraints are appropriate.

(iii) Assertions [9 marks]¶

Consider the below relations in a database that keeps track of stock levels in a music and movie store.

Write an SQL statement that creates an assertion called maximum_inventory that prevents insertions into either table violating the following condition:

The sum of the sale_price of all movies and music combined must not exceed €1,000,000.

(iv) Assertions vs Triggers [4 marks]¶

Explain any differences you are aware of between assertions and triggers in relational databases.

Question 3¶

(i) Transactions [8 marks]¶

Explain what a transaction is and how transactions can fail.

(ii) ACID Properties [8 marks]¶

What are the ACID properties? Describe each in detail.

(iii) Transaction Failure Scenario [9 marks]¶

In the below example, a Flight Transfer transaction (between flights X and Y) and a Flight Reservation (flight X only) transaction are being carried out simultaneously on the same database.

The flight transfer transaction fails after reading data item reserved_seats_Y.

Explain the type of error that will occur in this case.

Question 4¶

A prominent telecommunications company, Telemate, based in Ireland, is eager to get its latest deal out to its valued customers.

An email list is set up including the email addresses of all existing customers. The offer that Telemate is putting together will be highly cost-effective for customers, and the marketing manager, Helen, would like this information to reach as many customers as possible, as quickly as possible.

An email is composed that highlights the most important parts of the offer, including a deal that will allow contract customers to own the latest iPhone at 50% of the usual contract price.

Helen instructs Tom to send out the email to all existing customers on 6^th October 2020.

One customer, John, receives the email along with several other emails that John wastes time deleting — he is not interested in buying an iPhone. John would like to avoid receiving marketing emails altogether and never agreed to receiving these kinds of emails from companies.

(i) John's GDPR Rights [8 marks]¶

Describe in detail what John's rights are in terms of GDPR and what action John should initially take.

(ii) Responsibility at Telemate [8 marks]¶

Who in Telemate is responsible for the potential violation of GDPR?

(iii) Escalation and Repercussions [9 marks]¶

Despite John carrying out the action you described in (i), John was not satisfied with the result and in fact received a second similar email about a different offer 6 weeks later.

What action can John now take and what are the possible repercussions for Telemate?

© Trinity College Dublin, The University of Dublin 2023