CSCI 5333.1 DBMS
Spring 2020
Homework #3

Relational Algebra

This homework refers to the Sakila SQL database that may come preloaded with MySQL installation. Refer to the general documentation at https://dev.mysql.com/doc/sakila/en/. In this assignment, we are not using SQL, but relational algebra

(1) Provide the relational algebra expressions using the format of the relational algebra interpreter we discussed in class.

First, you need to install the relational algebra (aql.jar) interpreter found in: http://tinman.cs.gsu.edu/~raj/elna-lab-2010/ch2/. Make sure you read the manual: http://tinman.cs.gsu.edu/~raj/elna-lab-2010/ch2/ch2.pdf carefully, especially section 2.2 on the relational algebra interpreter.

Note that in setting up your JDK, you may need to include ".;.\*;" as a part of the environment variable CLASSPATH. You will need to use sakila_ra.zip to populate the Sakila database for the RA interpreter. Unzip the file and put the folder sakila in the same folder as aql.jar, and then run "java edu.gsu.cs.ra.RA sakila" in the command line at the folder.

A hint: pay attention to the common attributes when you use natural joins.

(a) List the titles of all films with a PG rating and a length of 180 or more.

Number of tuples = 4
MONSOON CAUSE:
RECORDS ZORRO:
STAR OPERATION:
WORST BANGER:


(b) List the titles of all films with the actor with id #148.

Number of tuples = 14
ANONYMOUS HUMAN:
BASIC EASY:
CHAMBER ITALIAN:
CHRISTMAS MOONSHINE:
DESTINY SATURDAY:
FUGITIVE MAGUIRE:
GONE TROUBLE:
HOLLOW JEOPARDY:
INVASION CYCLONE:
OCTOBER SUBMARINE:
REBEL AIRPORT:
SCARFACE BANG:
SEA VIRGIN:
SHREK LICENSE:

(c) List the titles of all films with a length of 180 or more and in the category 'Comedy'.

Number of tuples = 3
CONTROL ANTHEM:
SATURN NAME:
SEARCHERS WAIT:

(d) List the titles of films with an actor with id 1 or 17.

Number of tuples = 51
ACADEMY DINOSAUR:
ANACONDA CONFESSIONS:
ANGELS LIFE:
BULWORTH COMMANDMENTS:
...

(e) List the titles of films that have not been rented. Include films that are not in the inventory.

Number of tuples = 42
ALICE FANTASIA:
APOLLO TEEN:
ARGONAUTS TOWN:
ARK RIDGEMONT:
ARSENIC INDEPENDENCE:
...

(f) List the titles of films that have an inventory copy that has not been rented.

Number of tuples = 1
ACADEMY DINOSAUR:


(2) Redo (1) using in RA using Mathematical notations.

Submission:

Submit your homework through Blackboard with two files:

(1) <<Yourname>>_<<YourStudentNUmber>>_h3q1.txt which contains your solution for question (1) in text format. The TA should be able to execute your file in the RA interpreter.

(2) <<Yourname>>_<<YourStudentNUmber>>_h3q2.docx which contains your solution for question (2). This is in docx format so you can include Mathematical symbols.