CSC 1302, Honors Principles of Computer Science II (Fall 2023)

Phase 2 (Relational Algebra Interpreter)

In Phase 2 of the project, you will complete the implementation of a terminal-based Python program that will accept Relational Algebra Queries in the terminal input, parse them for syntax, perform semantics checks, and finally execute the query using the relational algebraic operators that you built in Phase 1. For this, you will first download the following skeleton files:

RALexer.py, RAParser.py, Node.py, and RA.py.

You should also download the following sample databases, available in zip format:

company.zip and drinks.zip.

Please unzip these files in the same directory as the remaining code. The Lexer and Parser specifications use the PLY package available at PLY 3.11. To install PLY 3.11, you will run the following command on the Terminal (pip3 on Mac and pip on Windows):

pip3 install ply
or
pip install ply

In particular, you will complete the definitions of the following 3 functions in RA.py.

# In this function you will set the value of Node.relation_name for each node
# of the input tree to a unique value. You can choose to use a global variable
# called count, initially set to 0, and each time you need a new relation name,
# you can generate the string "temp_"+str(count). count can be incremented each
# time by 1. This will allow you to generate a unique relation name for each node
# in the tree (temp_0, temp_1, temp_2, etc)
def set_temp_table_names(tree):
    pass

# Perform semantic checks on each node of the tree. 
# Also, set tree.attributes and tree.domains along the way on each node of the tree.
# return "OK" or ERROR message
def semantic_checks(tree, db):
    return 'OK'

# This function evaluates the query expressed in the tree and returns the
# Relation object that corresponds to the "root" node in the tree.
def evaluate_query(tree, db):
    pass

The semantic errors to be checked for are as follows:

RELATION:
  Relation does not exist

UNION/INTERSECT/MINUS:
  Relations do not have same number of attributes
  Attributes do not have the same data types

PROJECT:
  Not a valid attribute
  Duplicate attribute

RENAME:
  Not valid number of attributes
  Duplicate attribute

SELECT:
  Left Operand is not a valid column name
  Right Operand is not a valid column name
  Data types do not match in comparison

JOIN:
  common column have different data types

TIMES:
  No possible errors

Once you have completed Phase 2, you can verify that your code works by running RA.py. A sample session is shown below:

mac-mini:phase2 raj$ python3 RA.py company
RA: schema;

EMPLOYEE(FNAME:VARCHAR,MINIT:VARCHAR,LNAME:VARCHAR,SSN:VARCHAR,BDATE:VARCHAR,ADDRESS:VARCHAR,SEX:VARCHAR,SALARY:DECIMAL,SUPERSSN:VARCHAR,DNO:INTEGER)
DEPARTMENT(DNAME:VARCHAR,DNUMBER:INTEGER,MGRSSN:VARCHAR,MGRSTARTDATE:VARCHAR)
DEPT_LOCATIONS(DNUMBER:INTEGER,DLOCATION:VARCHAR)
PROJECTS(PNAME:VARCHAR,PNUMBER:INTEGER,PLOCATION:VARCHAR,DNUM:INTEGER)
WORKS_ON(ESSN:VARCHAR,PNO:INTEGER,HOURS:DECIMAL)
DEPENDENT(ESSN:VARCHAR,DEPENDENT_NAME:VARCHAR,SEX:VARCHAR,BDATE:VARCHAR,RELATIONSHIP:VARCHAR)

RA: department;

ANSWER(DNAME:VARCHAR,DNUMBER:INTEGER,MGRSSN:VARCHAR,MGRSTARTDATE:VARCHAR)
Number of tuples:6

Research:5:333445555:22-MAY-1978:
Administration:4:987654321:01-JAN-1985:
Headquarters:1:888665555:19-JUN-1971:
Software:6:111111100:15-MAY-1999:
Hardware:7:444444400:15-MAY-1998:
Sales:8:555555500:01-JAN-1997:

RA: select[dname='Research'](department);

ANSWER(DNAME:VARCHAR,DNUMBER:INTEGER,MGRSSN:VARCHAR,MGRSTARTDATE:VARCHAR)
Number of tuples:1

Research:5:333445555:22-MAY-1978:

RA: project[dnumber](select[dname='Research'](department));

ANSWER(DNUMBER:INTEGER)
Number of tuples:1

5:

RA: dept;
Relation 'DEPT' does not exist
RA: project[dno,dname](department);
SEMANTIC ERROR (PROJECT): Attribute DNO does not exist
RA: project[fname,lname,address](
  (rename[dname,dno,mgrssn,mgrstartdate](
      select[dname='Research'](department)) 
   join 
   employee
  )
);

ANSWER(FNAME:VARCHAR,LNAME:VARCHAR,ADDRESS:VARCHAR)
Number of tuples:4

Franklin:Wong:638 Voss, Houston, TX:
John:Smith:731 Fondren, Houston, TX:
Ramesh:Narayan:971 Fire Oak, Humble, TX:
Joyce:English:5631 Rice, Houston, TX:

RA: exit;               

mac-mini:phase2 raj$ python3 RA.py drinks
RA: schema;

DRINKER(DNAME:VARCHAR)
BAR(BNAME:VARCHAR)
BEER(RNAME:VARCHAR,PRICE:INTEGER)
FREQUENTS(DNAME:VARCHAR,BNAME:VARCHAR)
LIKES(DNAME:VARCHAR,RNAME:VARCHAR)
SERVES(BNAME:VARCHAR,RNAME:VARCHAR)
BARS(NAME:VARCHAR,ADDR:VARCHAR)
SELLS(BAR:VARCHAR,BEER:VARCHAR,PRICE:INTEGER)


RA: beer;

ANSWER(RNAME:VARCHAR,PRICE:INTEGER)
Number of tuples:5

Bud:12:
Michelob:8:
Fosters:16:
Heineken:18:
Kingfisher:16:

RA: exit;
mac-mini:phase2 raj$