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 Releases has
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 Visits makes
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-56789011’ 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