Cardiff School of Computer Science and Informatics
Assessment Pro-forma
Module Code: Databases and Modelling
Module Title: CMT220
Lecturer: Irena Spasić
Assessment Title: Timed practical implementation
Assessment Number: 1
Date Set: 01 March 2023
Submission Date and Time: 03 May 2023 (online test)
Start: 11:00
End: 13:00
Return Date: 17 May 2023 (results returned)
This assignment is worth 50% of the total marks available for this module.
Submission Instructions
You will need to complete a timed practical implementation and submit the results via an online test,
which will be available on Learning Central at the following location:
Module 22/23-CMT220 Databases and Modelling
Page Course Content
Folder Assessment #1
The test will last 2 hours. You will need to answer 10 randomly chosen questions about the content
of a relational database provided for this assessment. You will need to query the database to answer
each question. The questions will be of the fill-in-the-blank format, e.g.
Question: Who starred most often opposite STAN LAUREL? Provide their full name,
i.e. first name followed by the last name.
Answer:...... OLIVER HARDY
To be able to answer such questions, you will need to write SQL queries that will retrieve the relevant
information from the database, e.g.
SELECT A2.actor_id, A2.first_name, A2.last_name, COUNT(*) AS tot
FROM actor A1, actor A2, film_actor FA1, film_actor FA2
WHERE A1.first_name = 'STAN' AND A1.last_name = 'LAUREL'
AND A1.actor_id = FA1.actor_id
AND FA1.film_id = FA2.film_id
AND FA2.actor_id = A2.actor_id
AND A1.actor_id <> A2.actor_id
GROUP BY A2.actor_id, A2.first_name, A2.last_name
ORDER BY tot DESC;
2
You do not need to submit the actual SQL queries. Instead, you will need to run the query against the
database and copy/paste the output to fill in the blank. Please do not re-type the output manually as
any typos will be automatically marked as incorrect answers.
Assignment
All material relevant to the timed exercise will be available on Learning Central at the location
provided above in the Submission instructions. The timed exercise will be based on a DVD rental
database. The database will be made available to you when you start the exercise as an SQLite
database whose name will be DVD03May2023.sqlite. It is recommended that you open this database
using DB Browser as we practised in the online classes. The following entity-relationship (ER) diagram
represents the conceptual design of the given database. Study the diagram in order to understand the
structure of the database.
The database contains a total of 15 tables. Their structure is provided below. All attribute names are
self-explanatory. Where necessary, additional information is specified in comments, which are
indicated by -- in SQL.
Table 1: The film table provides information about a film. A film is related to language. There are two
relationships in the ER diagram, one for an original language the film was recorded in and the other
one for the language it is available in. Both relationships are modelled in the table below by means of
the corresponding foreign keys.
3
CREATE TABLE film
(
film_id INTEGER NOT NULL,
title TEXT NOT NULL,
description TEXT,
release_year INTEGER,
language_id INTEGER NOT NULL,
original_language_id INTEGER,
rental_duration INTEGER NOT NULL DEFAULT 3, -- in days
rental_rate REAL NOT NULL DEFAULT 4.99, -- the cost to rent the film
-- for the period specified
-- in rental_duration
length INTEGER, -- in minutes
replacement_cost REAL NOT NULL DEFAULT 19.99,
rating TEXT, -- G, PG, PG-13, R or NC-17
special_features TEXT,
PRIMARY KEY(film_id),
FOREIGN KEY(language_id) REFERENCES language(language_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(original_language_id) REFERENCES language(language_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
Table 2: The category table describes different categories of films.
CREATE TABLE category
(
category_id INTEGER NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY(category_id)
);
Table 3: Each film may belong to multiple categories. This relationship is represented by the
film_category table. Each row of this table links a film to a category.
CREATE TABLE film_category
(
film_id INTEGER NOT NULL,
category_id INTEGER NOT NULL,
PRIMARY KEY(film_id,category_id),
FOREIGN KEY(category_id) REFERENCES category(category_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(film_id) REFERENCES film(film_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
Table 4: The language table provides the names of languages.
CREATE TABLE language
(
language_id INTEGER NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY(language_id)
);
4
Table 5: The actor table provides a full name of each actor/actress.
CREATE TABLE actor
(
actor_id INTEGER NOT NULL,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
PRIMARY KEY(actor_id)
);
Table 6: Each actor can have a role in a film. This relationship is represented by the film_actor table.
Each row of this table links an actor to a film.
CREATE TABLE film_actor
(
actor_id INTEGER NOT NULL,
film_id INTEGER NOT NULL,
PRIMARY KEY(actor_id, film_id),
FOREIGN KEY(actor_id) REFERENCES actor(actor_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(film_id) REFERENCES film(film_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
Table 7: The store table provides information about the stores that rent films on DVD. Each store has
got a manager whose information can be found in the staff table, which is explained further below.
CREATE TABLE store
(
store_id INTEGER NOT NULL,
manager_staff_id INTEGER NOT NULL,
address_id INTEGER NOT NULL,
PRIMARY KEY(store_id),
FOREIGN KEY(address_id) REFERENCES address(address_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(manager_staff_id) REFERENCES staff(staff_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);
Table 8: Each store maintains an inventory of DVDs that are available to rent. Each DVD has got an
inventory_id. Each DVD is linked to a film it contains and a store from which it is available to rent by
means of foreign keys.
CREATE TABLE inventory
(
inventory_id INTEGER NOT NULL,
film_id INTEGER NOT NULL,
store_id INTEGER NOT NULL,
PRIMARY KEY(inventory_id),
FOREIGN KEY(film_id) REFERENCES film(film_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(store_id) REFERENCES store(store_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
5
Table 9: The staff table provides information about staff members. Each staff works in a particular
store. This information is modelled by a foreign key. Each staff member also has an address, which is
stored in a separate table and linked to using a foreign key.
CREATE TABLE staff
(
staff_id INTEGER NOT NULL,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
address_id INTEGER NOT NULL,
picture BLOB,
email TEXT,
store_id INTEGER NOT NULL,
active BOOLEAN NOT NULL DEFAULT TRUE,
username TEXT NOT NULL,
password TEXT,
PRIMARY KEY(staff_id),
FOREIGN KEY(store_id) REFERENCES store(store_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY(address_id) REFERENCES address(address_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
Table 10: The customer table provide information about customers. Each customer has an address,
which is stored in a separate table and linked to using a foreign key.
CREATE TABLE customer
(
customer_id INTEGER NOT NULL,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT,
address_id INTEGER NOT NULL,
active BOOLEAN NOT NULL DEFAULT TRUE, -- indicates whether the customer
-- is an active customer:
-- 1 = active; 0 = not active
PRIMARY KEY(customer_id),
FOREIGN KEY(address_id) REFERENCES address(address_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
Table 11: The address table contains street address details and a phone number. Information about
the city is stored in a separate table and linked to using a foreign key.
CREATE TABLE address
(
address_id INTEGER NOT NULL,
address TEXT NOT NULL,
address2 TEXT,
district TEXT NOT NULL,
city_id INTEGER NOT NULL,
postal_code TEXT,
phone TEXT NOT NULL,
PRIMARY KEY(address_id),
FOREIGN KEY(city_id) REFERENCES city(city_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
6
Table 12: The city table stores city names. Each city is located in a country, whose information is stored
in a separate table and linked to using a foreign key.
CREATE TABLE city
(
city_id INTEGER NOT NULL,
city TEXT NOT NULL,
country_id INTEGER NOT NULL,
PRIMARY KEY(city_id),
FOREIGN KEY(country_id) REFERENCES country(country_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
Table 13: The country table stores country names.
CREATE TABLE country
(
country_id INTEGER NOT NULL,
country TEXT NOT NULL,
PRIMARY KEY(country_id)
);
Table 14: The rental table provides information about DVDs that have been rented. A customer rents
a DVD that is available in the inventory. Each rental is processed by a staff member. The rental's
relationships to the inventory, customer and staff member are modelled by foreign keys.
CREATE TABLE rental
(
rental_id INT NOT NULL,
rental_date TEXT NOT NULL,
inventory_id INTEGER NOT NULL,
customer_id INTEGER NOT NULL,
return_date TEXT,
staff_id INTEGER NOT NULL,
PRIMARY KEY(rental_id),
FOREIGN KEY(inventory_id) REFERENCES inventory(inventory_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(customer_id) REFERENCES customer(customer_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(staff_id) REFERENCES staff(staff_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
Table 15: The payment table contains information about payments for specific rentals. The table also
stores information about the customer who paid for the rental and the staff member who processed
the payment. The relationships to the rentals, customers and staff members are modelled by foreign
keys.
CREATE TABLE payment
(
payment_id INTEGER NOT NULL,
rental_id INTEGER, -- the rental that the payment is being applied to
-- NOTE: It is optional because some payments are
-- for outstanding fees.
customer_id INTEGER NOT NULL,
staff_id INTEGER NOT NULL,
amount REAL NOT NULL,
payment_date TEXT NOT NULL,
7
PRIMARY KEY(payment_id),
FOREIGN KEY(rental_id) REFERENCES rental(rental_id)
ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY(customer_id) REFERENCES customer(customer_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(staff_id) REFERENCES staff(staff_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
Learning Outcomes Assessed
▪ Interpret a conceptual model and relate it to a database schema.
▪ Manage relational database management systems.
▪ Use SQL to define and query a relational database.
Criteria for assessment
The test will be marked automatically using a binary scoring system:
▪ 1 point for each correct answer
▪ 0 points for an incorrect answer
Maximum number of points will be 10. By multiplying the total number of points by 10, you can
obtain the level of attainment against the appropriate award.
▪ Distinction: 70-100
▪ Merit: 60-69
▪ Pass: 50-59
▪ Fail: 0-49
The rationale for the binary marking scheme is as follows. The difficulty of SQL queries has been
adjusted so that they require few lines of code, which cannot be reasonably broken down into partial
marks.
If partial marking was to be used in this assessment, then the difficulty of the SQL queries would need
to be adjusted. This would make the test more difficult to pass. Also, various aspects of the queries
(e.g. clarity and efficiency) would also be marked. So, even the queries that provide a correct result
would be marked down if they did not follow the best practice. With the current marking scheme, as
long as you are able to provide the correct result, you will not be penalised for poor use of SQL.
This is an open book assessment so you can lookup up the syntax while you are doing the test meaning
that marking syntax alone does not warrant a partial mark.
Unlike traditional exams, you will have an opportunity to run your query using DB Browser. It will
generate error messages, which will help you address any errors in your query before you submit
your answer. Any such errors made in a traditional exam would warrant a partial mark. However, such
errors cannot go unnoticed when you have an opportunity to test your query and therefore do not
warrant a partial mark.
The length of the test is disproportionate to the difficulty of the questions, which leaves you ample
time to debug your queries and also double check their results in various ways, e.g. by writing an
alternative query and checking if you still get the same result. Giving partial marks would require the
8
marker to debug your queries, which is the learning outcome that is being tested. Therefore, no partial
marks should be given to queries that still require debugging.
If you were working in a bank for example and were asked to write an SQL query to transfer funds
from one account to another, the final result is what counts. It does not matter how big your mistake
is, even if it is just a simple a typo. Any such mistake would not be tolerated in a business setting, so
this assessment provides an opportunity to assess the results of your own SQL queries.
This approach to marking has been successfully tested to the overwhelming student satisfaction. If
nothing, the test was too easy, which is why the difficulty of the test has been adjusted so that you
will get 2 relatively more difficult questions. These 2 questions will be the last 2 questions and clearly
marked. This is designed to better differentiate between 1st class marks (specifically, 100%, 90% and
80%), i.e. those who excel at SQL. This should not affect any other level of SQL proficiency (0%-70%).
Feedback and suggestion for future learning
Group feedback will be given in the form of statistical distribution of marks together with a
discussion of individual solutions.