Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

CMT220

Databases and Modelling

2023

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.

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 WHERE AND  AND  AND  AND

actor A1, actor A2, film_actor FA1, film_actor FA2

A1.first_name = 'STAN' AND A1.last_name = 'LAUREL'

A1.actor_id = FA1.actor_id

FA1.film_id = FA2.film_id

FA2.actor_id = A2.actor_id

A1.actor_id <> A2.actor_id

GROUP BY A2.actor_id, A2.first_name, A2.last_name

ORDER BY tot DESC;

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.

CREATE TABLE film

(

film_id

title

description

release_year

language_id

original_language_id

rental_duration

rental_rate

length

replacement_cost

rating

special_features

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)

);

Table 5: The actor table provides a full name of each actor/actress.

CREATE TABLE actor

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

manager_staff_id

address_id

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

);

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

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.