MINIPROJECT
Click here for an audio lecture that can be played using RealPlayer
Click here for audio-text lecture and feed it to the speech agent
The miniproject is 10 points plus 3 extra bonus points for early submission,
and 3 extra bonus points for completion of optional part.
Select one project from the projects described below. You may not design your own project.
You may not select more than one project.
If you are a graduate student, you are also required
to turn in a project report and give a five-minute project presentation and a live demo. If you
are an undergraduate student, you are required to turn in a
single-page readme file and give a live demo, but not required to turn in
a project report.
Project 1: SQL Compiler
Implement the SQL compiler discussed in
part (ii) of Exercise 3.
You may use any compilable programming language in the implementation,
except PERL, PYTHON and other interpretive languages.
Although PERL program can be compiled it will still be disallowed.
When you are ready to turn in the project, bring your diskette containing source, executable and test data, so that
you can give the instructor a live demo.
The miniproject is to implement the SQL Compiler to translate simplified SQL
queries into relational algebraic queries. The simplified SQL queries are of the following form:
Simplified SQL Query:
SELECT attribute-list
FROM relation-list (could be different relations)
WHERE conditions (could involve R1.A1 = R2.A2)
Optional Part P2:
3 bonus points if you also implement the relational operators
Project, Select and Join,
so that your program is a complete system to translate
and execute simplified SQL queries. (If you can only implement project and
select, you will still get some bonus points!)
Files on Diskette:
There should be the following files on the diskette you turn in:
README (a short user's manual explaining how your program works)
P1.exe (program to translate SQL into relational algebra)
P1.source (source for P1.exe)
DBD (input database schemas in ascii file)
Q1 (input query in ascii file)
Q2 (input query in ascii file)
T1 (output relational algebraic query in ascii file)
T2 (output relational algebraic query in ascii file)
(Obviously, you can have Q3, Q4, ..., etc. You should prepare a mixture of
queries, starting from simple ones, then to more complex ones, plus queries
containing syntactic errors or semantic errors.)
For the optional part P2, there should be the following files:
README (a short user's manual explaining how your progrm works)
P2.exe (program to execute the translated query)
P2.source (source for P2.exe)
INDATA (input data for populating the database)
OUTDATA (results after executing a relational algebraic query in T1, T2, etc.)
The database definition (DBD) file may look like this:
DBD:
3 (# of relations)
S (name of relation)
3 (# of attributes)
S# (name of first attribute)
2 (length of first attribute)
SNAME (name of second attribute)
10 (length of second attribute)
CITY (name of third attribute)
8 (length of third attribute)
P (name of second relation)
2 (# of attributes)
P# (name of first attributed)
2 (length of first attribute)
PNAME (name of second attribute)
10 (length of second attribute)
SP (name of third relation)
2 (# of attributes)
S# (name of first attribute)
2 (length of first attribute)
P# (name of second attribute)
2 (length of second attribute)
The input data file (INDATA) may look like this:
INDATA:
S (name of relation)
2 (# of tuples)
S1 Chang Paris
S2 Smith London
P (name of relation)
2 (# of tuples)
P1 Nut
P3 Screw
SP (name of relation)
3 (# of tuples)
S1P1
S1P3
S2P3
Translated queries can either be stored in files T1, T2, etc. or
displayed on the screen. Final output should be displayed on the screen.
Test your program on the following
examples:
Q1:
SELECT S#, SNAME, CITY
FROM S
WHERE S# = 'S1'
Q2:
SELECT S#, CITY
FROM S, SP
WHERE P# = 'P2' AND S.S# = SP.S#
The translated relational algebraic query T1 may look like:
T1:
t1 = project S [S#]
display t1
It is important to understand that the relational algebra
is a procedural language. Therefore you should generate
one command line for each relational algebraic operator.
If you just use simple pattern matching then you will not
be able to distinguish restrict and join operators.
Your program will not work correctly and you may lose 3 or more points!
The results to execute a query may look like:
OUTDATA:
Result:
S# SNAME CITY
-- ----- -----
S1 Chang Paris
Grading
6 for successfully doing P1 to translate queries (correctness)
1 capability to handle different DBD (0.5) and join queries (0.5) (generality)
1 capability to detect syntactic errors (0.5) and semantic errors (0.5) in input queries (error handling)
2 quality of programming (0.5), comments in source codes (0.5), display format (0.5), user's manual (0.5) (quality)
3 bonus points for doing P2 to execute queries
3 bonus points for early submission