Relational Algebra – Cross Product
What is Relational Algebra – Cross Product?
Relational Algebra is a meta-language and forms underlying basis of SQL query language. It has six basic operators including: select, project, union, set difference, rename, and cross product. The operators take one or two relations as inputs and produce a new relation as a result.
Activity Outcomes:
After performing this lab students should be able to:
- Implement relational algebra operations
- Design query expressions by composing relational algebra operations
- Retrieve data from a database using RA expressions
Tools/Software Requirement
- MySQL Community Server 6
- MySQL Workbench 1
- Sakila Database
- Relational Algebra Interpreter (Duke University)
Lab Activities:
Activity 1:
- Load RA Interpreter and unzip it to any appropriate folder. We will use the interpreter to write relational algebra queries and submit these to MySQL for retrieving required
- Open console and change current directory to where the interpreter was
- Run following command from the
- java -jar ra.jar properties
- A new ra prompt will be activated in the console. Here you can type and run relational algebra
- As specified in the mysql.properties file, the interpreter would be connected to MySQL and Sakila database. You can try to execute different expressions on the Sakila
- Make sure MySQL Workbench is also running where you can inspect relation schema to cross check that the queries are referring to accurate relation and attribute names. Note that clicking on any relation on the left pane of Workbench opens relation information in a bottom left
- Preferably write relational algebra expression within the Workbench and ignore if any error is reported as the Workbench is primarily meant to work with SQL queries instead of relational algebra. Once written, the expression can be pasted on the ra console to execute the query.
- The interpreter supports handful of relational algebra operations as listed in the following table. The interpreter uses a special syntax and the expression should end with semi-colon symbol. Examples are presented in the next table. Focus on first two
- You can start your experiment with the given relational algebra
- Continue playing with the interpreter till you are comfortable with the querying syntax and mechanism.
Solution:
| Operation | Syntax and Description |
| SELECT | \select_{CONDITION} EXPRESSION
Selection over an expression |
| PROJECT | \project_{ATTRIBUTE_LIST} EXPRESSION
Projection on selected attributes |
| RENAME | \rename_{NEW_ATTRIBUTE_NAME_LIST} EXPRESSION
Rename all attributes of an expression |
| UNION | EXPRESSION_1 \union EXPRESSION_2
Union between two expressions |
| DIFFERENCE | EXPRESSION_1 \diff EXPRESSION_2
Difference between two expressions |
| INTERSECT | EXPRESSION_1 \intersect EXPRESSION_2
Intersection between two expressions |
| CROSS PRODUCT | EXPRESSION_1 \cross EXPRESSION_2
Cross-product between two expressions |
| JOIN | EXPRESSION_1 \join EXPRESSION_2 |
- You can start your experiment with the given relational algebra
| Information Need | Expression in Interpreter Syntax |
| Copies of ALASKA PHANTOM in the inventory | \select_{film_id=flm_id and title=’ALASKA PHANTOM’} ( film \cross (
\rename_{inventory_id, flm_id, store_id, last_updat} inventory
)
); |
| Category of the film ICE CROSSING | \project_{name} (
\select_{category_id=cat_id} (
\select_{film_id=flm_id and title=’ICE CROSSING’} ( film \cross ( \rename_{flm_id, cat_id, last_updat} film_category
)
)
\cross
(\rename_{category_id,name,last_updt} category)
)
); |
- Continue playing with the interpreter till you are comfortable with the querying syntax and mechanism.
Activity 2:
- Write relational algebra expression for the following information needs. Write their corresponding expressions in the interpreter syntax as
- Store all expressions in a plain text
- Information needs:
- First and last name of actors who played in BOONDOCK BALLROOM. [3]
- Rental and return date for the movie ALASKA PHANTOM. [3]
- List of movie titles that were never rented. [4]
- Email addresses of customers who haven’t paid even a single penny yet. [5]
- Email addresses of customers who although rented a movie but didn’t pay anything. [5]
Home Activities:
- Answer questions from the task. These questions assume Sakila database hasn’t changed.
Lab Assignment and Viva voce
Deliverable:
Submit a PDF document including the relational algebra expressions in the interpreter syntax to answer above-mentioned information needs as well as snapshot of their outcome when executed on the interpreter.
