Assignment 1: Data Modelling / Relational Algebra

COMP-421 Database Systems
Written Assignment 1: Data Modelling / Relational Algebra

Ex. 1 — ER Modelling (45 Points)
OnStream, an online streaming company is planning to use a database to manage its shows that users can rent and
watch online.
•A show can be either one of a Movie (Life is Beautiful) / Documentary (Frozen Planet) / TV Series (Murdoch
Mysteries).
•Shows have a name (not necessarily unique), content rating, the production company associated with it, and a
rental price. They are also associated with one or more genre.
•TV series can have many seasons. Each season has a season number (incremental within the same TV Series)
•A particular season can have many episodes. However, all of them are related with the same genre as the TV
series. Each episode has an airing date and its own episode name.
•Each show has a synopsis. Additionally, each episode of a TV series has its own synopsis.
•Episode names are guaranteed to be unique only within a particular TV series.
•Each show has associated with it one or more directors and actors.
•Optionally, each episode of a TV series may have one or more episode directors.
•Each documentary has one or more narrator(s) associated with it.
•Same person can perform multiple roles (actor/director/narrator ) within the same show or across different
shows. We need to keep track of their names and country of citizenship (no dual citizens). We will assume that
their name is unique.
•A movie can have a sequel that needs to be kept track of (for simplicity, we will consider only the immediately
following movie as the sequel). For example Lost World is a sequel to Jurassic Park and Jurassic Park III is a
sequel to Lost World.
•Users can rent a show. Their name, address and user id are stored in the database. User id is unique to each
user.
•Users can also optionally indicate their interest in certain genres.
•Once a user rents a show, they have 7 days to watch it (any number of times). So the date of rental needs to
be kept track. They are free to rent (and watch) the same show multiple times. We do not have to keep track
of how many times a user watches a particular show, but we need to keep track of all the rentals a user makes
for a particular show.
•Users can also optionally review (date, 1-5 rating and comment) a show (for TV series also reviews are done
for the entire series not for a specific season or episode). Only one review is allowed per show for a user which
they can edit and update later. We only keep track of the latest version of this review.
1. (30 Points) Design the ER Model for the following specification. Indicate any constraints that you might not
express in the ER model. For each entity, decide on the primary keys. If you think there is no appropriate
primary key, you can introduce an artificial key attribute. Also include any reasonable assumptions you have
made for your model (that does not violate common sense or explicit requirements).
2. (15 Points) Transform your diagram into the relational model (in the form R(a, b, c). Indicate primary keys
by underlining them. Indicate foreign keys by letting them point to the relation to which they relate, eg. Q(d,
e, a) (a ref R) . DO NOT write SQL statements. Indicate if there are other constraints depicted in the ER
model that you cannot (yet) describe in the relational model.
1
Ex. 2 — Testing your ER decoding skills. (16 points)
1. Consider the following ER diagram ( 8 points )
Softwares has Releases
name
type
version_number
platform
release_date release_notes
For each of the below statement, indicate whether you agree or disagree and briefly state your reasons (one
liners are sufficient as long as they are to the point).
You will get only half points if you do not state the reasons and just answer Agree/Disagree.
(A) A release can be not associated with any software.
(B) A software can be not associated with any release.
(C) A particular version of a given software may have more than one release date.
(D) The number of entities in the Releases entity set will always be equal to greater than the number of
entities in the Softwares entity set.
2. Consider the following ER diagram ( 8 points )
Patients makes Visits
patientId
name
diagnosis
visit_id
treatment_plan
visit_date
Doctors
addr
name
speciality
licenseNum
For each of the below statement, indicate whether you agree or disagree and briefly state your reasons (one
liners are sufficient as long as they are to the point).
You will get only half points if you do not state the reasons and just answer Agree/Disagree.
(A) This model can be used to record a patient visiting different doctors on the same day for different
diagnosis/treatments.
(B) This model can be used to record a patient visiting the same doctor multiple times on the same day for
different diagnosis/treatments.
(C) For a given patient visit (recorded with a visit id in the Visits entity set), multiple doctors can be
involved in the diagnosis.
(D) If we want to keep track of the cost associated with each patient visit, it can be made an attribute of
either the Visits entity set or the makes relationship.
2
Ex. 3 — Relational Algebra (39 Points)
Look at the following relational schema of an insurance company database (with some example tuples for each
relation )
Drivers (dlnum, name, city, addr, expdate)
‘L5231-623577-75’, ‘Jane Doe’, ‘Montreal’, ‘3484 University Ave’, ‘2020-09-30’
Automobiles (license, type, make, year)
‘AN467TY’, ‘car’, ‘Subaru Forester’, 2012
Accidents (reportid, city, addr, date)
612624, ‘Montreal’, ‘Peel’, ‘2018-01-10’
Regdrivers (dlnum, license)
‘L5234-845666-90’, ‘MJ4KRT3’
Accidentinfo (dlnum, license, reportid, claim)
‘L6452-825222-44’, ‘OP56K42’, 533322, 2300
Some useful information.
•A driver can be registered to drive multiple automobiles of any type.
•An individual has only one driver’s license number, no matter how many different types of automobiles (‘car’,
‘truck’, ‘motorcycle’, etc.) that person is allowed to drive.
•Multiple drivers can be registered to drive the same automobile.
•We can assume that when an accident occurs, the automobile involved was driven by one of its registered drivers.
Express the following queries in relational algebra using the notations and operators taught in class:
1. (2 Pts) List the name, city and address of all drivers in the city of ‘Montreal’.
2. (3 Pts) List the reportids of all accidents involving the driver with the driving license number ‘L1234-567890-
11’ and where the claim amount is above 200.
3. (3 Pts) Find the license plates of all automobiles with ‘Billy Joe’ as one of the registered drivers.
4. (4 Pts) Find the driving license number of all those drivers from ‘Montreal’ whose driving license expired as
of ‘2018-01-01’ but are still registered drivers for an automobile.
5. (3 Pts) Find the driving license number of all the drivers, such that a driver is a registered driver for at the
least one of ‘truck’ and ‘car’ type automobiles.
6. (5 Pts) Find the driving license number of all drivers, such that a driver is a registered driver for a ‘truck’ but
not for a ‘car’.
7. (6 Pts) Find the distinct driving license number of all the drivers involved in more than one accident.
8. (6 Pts) Find the highest claim amount across all accidents involving the driver with the driver’s license number
‘L9876-543210-99’.
9. (7 Pts) Find the driving license of drivers such that the car(s) for which they are registered as drivers were
involved in an accident, but they themselves were never involved in any accidents involving any cars that they
are registered for.
3