database system

IMPORTANT:

Plagiarism: Every student has to work individually on this project assignment.

All of the code for this project must be your own. You may not copy source code

from other students or other sources that you fifind on the web. You may not share

your code with other students. You may not host your code on a public code

repository

.

Each submission will be checked using plagiarism detection software.

Plagiarism will be reported to School and College Academic Misconduct Offiffifficers.

See the University

’

s page on Academic Misconduct for additional information.

Start early and proceed in steps. Read the assignment description carefully before

you start programming

.

The assignment is out of 100 points and counts for 40% of your fifinal mark.

1INFR11199 (Spring 2021) Coursework Assignment Page 2 of 15

1 Goals and Important Points

In this project assignment, you will implement a lightweight database management system

called LightDB. The assignment goals are threefold:

to teach you how to translate from SQL queries to relational algebra query plans,

to familiarize you with the iterator model for relational operator evaluation, and

to build na

¨

ıve implementations for the most common operators (selection, projec

tion, join, sort)

.

You will be starting from a bare

–

bone project consisting of only the main class

LightDB, which defifines the expected command line interface. You are free to modify

this class but must preserve the command line interface. The project is also confifigured

to use JSqlParser

1

, so you do not have to write your own parser (unless you want to)

.

The main class gives an example of how to parse a SQL string into a Java object.

The reference implementation of this project is about 1100 lines of code, not including

comments. Whether or not you consider this a lot, it is not a project that should be left

to the last minute.

1.1 Setting Up Local Development Environment

You are free to use any text editor or IDE to complete the project. We will use Maven

to compile your project. We recommend setting up a local development environment by

installing Java 8 or later and using an IDE such as IntelliJ or Eclipse. To import the

project into IntelliJ or Eclipse, make sure that you import as a Maven project (select the

pom.xml fifile when importing)

.

2 Overview

In this project, you will implement a simple interpreter for SQL statements. That is, you

will build a program that takes in a database (a set of fifiles with data) and an input fifile

containing one SQL query

. The program will process and evaluate the SQL query on the

database and write the query result in the specifified output fifile.

1

https://github.com/JSQLParser/JSqlParser

Coursework Assignment continues. . .INFR11199 (Spring 2021) Coursework Assignment Page 3 of 15

2.1 Supported language features

Your interpreter will not support all of SQL, but it will handle a lot of relatively complex

queries. Here we give information about the queries you must support.

Your interpreter will process SELECT

–

FROM

–

WHERE queries, which may optionally also

have a DISTINCT, an ORDER BY, or both. You do not need to support nested subqueries,

set operators (UNION etc.

), GROUP BY, aggregates like COUNT, or any other features. In

addition, we make a few simplifying assumptions as below. When we say a query is valid,

we mean it is a permitted input to your interpreter which you should be able to handle.

When we talk about a base table, we mean a real table that exists in the database.

You may assume all valid queries follow correct SQL syntax and that they only refer

to tables that exist in the database. Also, when a query refers to a table column

such as Sailors.name, you may assume the column name is valid for that table.

You may assume there will be at least one table in the FROM clause.

Valid queries may use aliases such as Sailors S or they may just use the names of

base tables. If a query does not use aliases, all column references are fully qualifified

by the base table name. If a query does use aliases, all tables use aliases and all

column references are qualifified by an alias. Here are two examples of valid queries,

the fifirst one without aliases and the second with aliases:

1. SELECT Sailors.name, Reservations.date FROM Sailors, Reservations

WHERE Sailors.id

=

Reservations.sid;

2. SELECT S.name, R.date FROM Sailors S, Reservations R

WHERE S.id

=

R.sid;

You may assume that any string used as an alias will not also be the name of a

base table.

Self

–

joins, i.e.

, joining a table with itself, are valid and must be supported (and

require the use of aliases)

.

The WHERE clause, if present, is a conjunction (i.e.

, an AND) of expressions of the form

A op B, where op is one of

=

, !

=

, <, >, <

=

, >

=

and A and B are either integers or

column references. Thus Sailors.id

=

Reservations.sid, Sailors.id < 3 and

42

=

42 are all valid expressions for the WHERE cause, while for example Sailors.id

< Boats.id

–

1 is not a valid expression even though it would be ok in

“

real SQL

”

.

The SELECT clause will either specify a subset of columns or have the form SELECT

*. For SELECT *, order the columns in your answer following the FROM clause. Thus

for SELECT * FROM R, S, each answer row has all the columns of R followed by all

the columns of S. The order of columns in a table is defifined by the relation schema.

Coursework Assignment continues. . .INFR11199 (Spring 2021) Coursework Assignment Page 4 of 15

There may be an ORDER BY clause which specififies a subset of columns for ordering

.

You may assume that we only want to sort in ascending order. If two tuples agree

on all sort attributes, you can order them as you prefer. You may assume that no

ASC, DESC, OFFSET, or LIMIT keywords will be used.

You may also assume that the attributes mentioned in the ORDER BY are a subset of

those retained by the SELECT. This allows you to do the sorting last, after projection.

Note that this does not mean that every attribute in ORDER BY must be mentioned

in the SELECT

–

a query like SELECT * FROM Sailors S ORDER BY S.name is valid.

There may be a DISTINCT right after the SELECT, and it should be processed ap

propriately

. Yes, SELECT DISTINCT * FROM … is valid.