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

CMT220

Databases and Modelling

2022

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:

Mo dule	21/22- CMT 220 Databases and Modelling
P age	As sessment
Fo lder	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 HA RDY

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 DVD 03 May 2022. 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 TA BLE film

(

film _ id

t itle

descriptio n

releas e _ year

lang uage _ id

origina l _ language _ id

re ntal _ duration

rental_ rate

l e ngth

re placement _ cost

rating

special _ features

PRIMARY KEY( film _ id ),

FOREIGN KEY ( language _ id ) REFERENCES language ( language _ id )

ON DELETE CASCADE ON UPDATE CASCADE ,

FOREIG N 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 TA BLE category

(

categor y _ id INTEGER NOT NULL ,

name TEX T NOT NULL ,

PRIMARY K EY ( 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.

CREAT E TABLE film _ category

(

film _ id INTEGER NOT NULL ,

category _ id INTEGER NOT NULL ,

PRIMAR Y 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 TA BLE language

(

language _ id INTEGER NOT NULL ,

name TEXT NOT NULL ,

PRIMARY K EY ( language _ id )

);

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

CREATE TAB LE actor

PRIMARY KEY(a ctor _ 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 TA BLE film _ actor

(

actor _ id INTEGER NOT NULL ,

film _ id INTEGER NOT NULL ,

PRIMA RY 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 TAB LE store

(

store _ id

manager_ staff _ id

a ddress _ id

PRIMARY KE Y ( store _ id ),

FOREIGN K EY ( address _ id ) REFERENCES address ( address _ id )

ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN K EY ( 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 TA BLE inventory

(

inventory_id INTEGER NO T NULL ,

film _ id INTEGER NOT NULL ,

store_id INTEGER NOT NULL ,

PRIMARY K EY ( 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 TAB LE staff

PRIMARY KE Y ( staff _ id ),

FOREIGN KEY ( store _ id ) REFERENCES store ( store _ id )

ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN K EY ( address _ id ) REFERENCES address ( address _ id )

ON DELETE CASCADE ON UPDATE CASCADE

);