CS 1555 / 2055 – DATABASE MANAGEMENT SYSTEMS (FALL 2022) Assignment #3

Goal:

The goal of this assignment is to continue the top-down design of a database system and gain familiarity

with the theoretical foundations of the relational model, SQL DDL, relational algebra, and basic SQL DML.

Description:

Assume the streaming company, called Yuhoo!, whose database was conceptually designed in HW2. Start- ing from the HW2 sample solution of the ER modeling, you will initially implement the Yuhoo!  database in PostgreSQL, subsequently enhance its schema, and finally define some basics operations (queries), expressed in relational algebra and SQL DML.

ER to Relational Model and SQL DDL Questions:

1.  [13 points] Use the ER-to-Relational model algorithm to produce a relational Yuhoo!  database schema from the sample solution of HW2.

2.  [21 points] Use the CREATE TABLE statement to define the tables of the relational Yuhoo!  database. The data type for each attribute is listed below. You need to define the primary keys, foreign keys and alternative keys (if any). Specify referential integrity triggering actions, e.g., “ON DELETE CASCADE” .

● actor id, director id, subscriber id, movie id: integer

● fname, lname, first, last, profile name: varchar(32)

● middle initial: char(1)

● main roles, support roles, short movies, regular movies, rating, total rentals: integer

● title, role, award name, category, url: varchar(50)

● description: varchar(250)

● movie rating: varchar(5)

● duration, resume watching point: time

● release year, year: char(4)

● address: varchar(100)

● genre, email: varchar(20)

● phone number: varchar(16)

● phone type: DOMAIN called phone enum of varchar(6) with values {’home’, ’mobile’, ’work’, ’other’}

● subscription type: DOMAIN called subscription plan of char(1) with values {’I’, ’F’}

● price, amount due, amount paid: real

● birthday, subscription date, last payment date, start date, end date, paid on: date

Further, specify in-line that the following attributes cannot be NULL: fname, birthday, main roles, sup- port roles, short movies, regular movies, title, director id, description, genre, duration, release year, role, year, address, email, phone number, phone type, first, subscription type, subscription date, and re- sume watching point. Also, the default value of all numeric and time attributes which cannot be NULL should be specified as 0 (zero). If it does not make sense for any of these attributes to be negative, specify it.

3.  [3 points: 1.5 points each] Use the CREATE TABLE to evolve the Yuhoo! database with two new tables.

– BILL (subscriber id, start date, end date, total rentals, amount due)

where total rentals reflects the cost of rented movies not included in the basic subscription and the amount due is the sum ofprice in SUBSCRIPTION (basic fee) and total rentals (both total rentals and amount due are of type real). In addition, enforce that the end date and total rentals attributes

cannot be NULL.

– PAYMENT (subscriber id, paid on, amount paid) where the amount paid does not have to be the same as amount due in the most recent bill. paid on is a date.

4.  [8 points: 1 points each] Use the ALTER TABLE statement to incorporate the following changes in the schema.

(a) Add a new attribute rental price of type real to the table MOVIE.

(b) Add a new attributefree to MOVIE which indicates whether the movie is included in the subscrip- tion and its rental is free. Its value can be either true or false.

(c) After evolving the table MOVIE, enforce that the attribute free cannot be NULL. It can only take the values true or false, with default value true.

(d) Add a constraint to MOVIE, called CHECK MOVIE RATING, that enforces that the movie rating is assigned one of the following values ‘G’, ‘PG’, ‘PG- 14’, ‘R’, ‘MA’ .

(e) Enforce that all currency attributes (e.g., price, amounts) have positive values with a default of 0 (zero). In addition, these attributes cannot be NULL.

(f) Add a constraint to RENTS, called CHECK RATING, that enforces that the rating is assigned a value between 1 and 5, with 3 as the default value.

(g) Remove the attribute last payment date from SUBSCRIBER.

(h) Add a new attribute rental date of type date to RENTS, indicating the date of the rental and enforce

that this attribute cannot be NULL.

5.  [1 points] After creating the database using your SQL statements, populate the database according to the data in sample-data .sql script. Recall that you can retrieve the data/tuples in the table by using the SQL DML statement:    select  *  from  <name  of  the  table>.

6.  [6 points] Create SQL script file test-constraints-data .sql by modifying the SQL INSERT commands in sample-data .sql to validate your specified constraints in the database.

Relational Algebra Questions:

7.  [8 points: 2 points each] Assuming that the relations MOVIE, DIRECTOR and ACTS in the Yuhoo database produced in Q4 have 160, 35 and 70 tuples, respectively, find the arity and cardinality of the following relations (For those whose accurate values can not be determined, give the min and max values). Recall that  Ba is the left outer natural join operator and Ba  is the right outer natural join operator.

● Igenre MOVIE

● MOVIE * DIRECTOR

● MOVIE Ba  DIRECTOR

● MOVIE  Ba ACTS

8.  [4 points: 2 points each] Using the Yuhoo!  database produced in Q4, for each of the following pair of expressions, determine if both expressions are equivalent. Please explain your answers.

● Ititle,price(MOVIE  * AWARD); Ititle,price(MOVIE);

● σprice>=10 A MOVIE .director  id=DIRECTOR.director  id((Ititle,price(MOVIE) X DIRECTOR)); Ititle,price((σprice>=10(MOVIE)) Badirector  id=director  id DIRECTOR)

9.  [10 points: 5 points each] Consider the Yuhoo! database schema produced in Q4 and write the relational algebra expression(s) in NESTING notation for each of the following queries. For the date/time use the standard format ‘YYYY-MM-DD’/ ’HH:mm’ and treat them as strings.

● List the primary subscribers’ first and last name and their subscription ID, who rented the movie ‘Grease’ and gave 5 as rating (i.e., Love it).

● Count the number of movies which were released in 2022 that were not watched to completion.

10.  [10 points, 5 points each] Consider the Yuhoo! database schema produced in Q4 and write the relational algebra expression(s) in SEQUENCE notation for each of the following queries.

● List the family subscriptions whose members watched/rented all of the same movies as the members of the family subscription whose subscription id is 121212.

● List the id, first and last names of the directors who have directed the longest and shortest movies along with the number of such movies. The schema of the result table is expected to be (director id, fname, lname, numLongest, numShortest).

11.  [6 points] Given relation R with attributes A, B, C, D and relation S with attributes D, E, F provide:

● An instance of relation R with 5 tuples,

● An instance of relation S with 8 tuples, and

● An instance of relation R  Ba R.D=S.D S,

such that relation R * S has 3 tuples, and relation R  BaR.D=S.D S has 5 tuples. Feel free to assume any type for attributes A, B, C, D, E, F in your relation instances/examples. You do not need to provide R * S or R  BaR.D=S.D S.

SQL DML Questions:

12.  [10 points: 5 points each] Consider the Yuhoo! database schema produced in Q4 and express in SQL each of the following queries:

● List the primary subscribers’ first and last name and their subscription ID, who rented the movie ‘Grease’ and gave 5 as rating (i.e., Love it).

● Count the number of movies for each genre which were released in 2022 and have average rating 4 and list them in descending order of the count.

What to submit:

You are expected to submit the following 4 files. Please include your name and pitt user name (e.g. pitt01) at the top of each file. In the case of a SQL script file, use the SQL comment to show your name.

1. hw3-<pitt user name>-db.sql

In this file, please submit your answers to questions 2, 3, 4, and 5 (i.e., CREATE TABLE, ALTER TABLE and INSERT statements). In addition to providing the answers, you are expected to:

● Identify the question number before each answer. You can do it using SQL comments.

● You must use SQL DROP TABLE IF EXISTS < table name > CASCADE; statements at the beginning of this file so that you can make sure your database does not have pre-existed tables which have the same name as the tables in this assignment.

2. hw3-<pitt user name>-test-constraints-data.sql

In this file, please submit your answers to question 6 (i.e., INSERT statements to test your con- straints). In addition to providing the answers, you are expected to:

● Identify the constraints being tested using SQL comments.

3. hw3-<pitt user name>.pdf

In this file you should add all the answers for question 1 and the relation algebra expression ques- tions.

● Do not forget to include your name and Pitt account name in the file.

● No hand-written assignments are accepted.  Any hand–written and scanned assignment will not be graded.

● If you have trouble generating any of the relational algebra symbols, for example R BaA=B  S, use a descriptive word, e.g., R Join(A=B) S.

4. hw3-<pitt user name>-queries.sql

In this file, please submit your answers to question 12 (i.e., SQL DML). In addition to providing the answers, you are expected to:

● Identify the question number before each answer. You can do it using SQL comments intro- duced with --.

E.g., --  this  is  an  SQL  comment