Team Project (Database Management System) Milestones

Below is a description of the individual project milestones. The project description can be found here.

  1. Download project milestones : here
  2. Unzip milestones to a directory {mstones}
  3. Download odiff.py
  4. Put your program {pi.exe} in the directory containing odiff.py
  5. Run 
    python odiff.py {pi.exe} {mstones}/project2/mi/test_input.txt {mstones}/project2/mi/test_output.txt
    replacing i in mi with the milestone number.
    For example, if the milestones directory is in the same directory as odiff.py and you are working on milestone 1 you would type 
    python odiff.py proj2_m1.exe project2/m1/test_input.txt project2/m1/test_output.txt

    NOTE: The script only runs on Python 2.x (not on Python 3.0).

VERY IMPORTANT NOTES ON SUBMISSION (READ AND FOLLOW VERY CAREFULLY)

Your project submission should be contained in a normal 'zip' file (NOT a 'rar', 'z7', etc.) with your team number as part of the zip file name. For example, if your team number is '10' then you would call your archive 'team10_Proj1.zip'.

The archive should contain

  1. report.pdf | report.html | report.txt
    2. Your project report should contain information on how to compile your program, run your program, and details about the design and software architecture you used for this project. Your report should also include a list of assumptions that you made and reference material that you used (including text books, websites, code obtained from other courses, etc.).
  2. Source Code for Each Milestone
    3. Your project should contain source code for each project milestone using the names defined below.
  3. Microsoft Windows Binaries
    4. Release mode executables compiled with Microsoft Visual Studio using the names defined below.
Submit your archive file on CSNet's turnin webpage. Note: You may resubmit your project without penalty up until the actual project deadline.

Please double check that you are submitting the correct files before submission! Students frequently submit the wrong files and expect to have no penalty when they submit the correct files after the project deadline. Late projects are counted late (unless stated otherwise by the professor)!

Milestone 1 : Create, Basic Insert & Show

Program Name : proj2_m1.exe
Main Source File : proj2_m1.cpp

The purpose of this milestone is to start you off with a simple database that supports basic "CREATE" and "INSERT" commands and allows you to print the contents of a table in a formatted way. You only need to implement the first production for insert (which inserts a tuple literal values into a table) and not the second.

The program should wait for an input string and perform different actions based on the command.

  1. CREATE Commands : Creates a relation in the database (does not print anything)
  2. INSERT Commands : Inserts a relation in a table in the database (does not print anything)
  3. SHOW Commands : Prints a formatted table to the standard output
  4. Illegal Commands : An error 
    ** unrecognized expression!
    is printed to the standard output
The SHOW command is not described in the project grammar, but is compatible with it and will be useful throughout this project. The SHOW command has the following syntax: 
SHOW relation-name ;
For example, if the database contains a relation named 'animals', then 
SHOW animals;
would print out the contents of the table animals. If the SHOW command (or any command) is applied to a relation-name that does not exist in the database, then 
** no such relation!
is printed to the standard output.

The format of the table printed by SHOW:

  1. is constructed from '|', '-', and '+' characters
  2. the first row shows the relation name as 'Relation : relation-name'
  3. the header for the table specifies the column names, data type, and whether the columns are part of the PRIMARY KEY (primary key values are denoted with an asterisk (*) directly after the column name)
  4. the columns with VARCHAR data type are left aligned
  5. the columns with INTEGER data type are right aligned
  6. the width of each column is padded by one space on the left and right sides of the widest column element (including the relation name and header)
For example, if a relation named 'animals' is in the current database and we type 
SHOW animals;
the program should produce: 
+------------------------------------------------------+
| Relation : animals                                   |
+-------------------+------------------+---------------+
| name* VARCHAR(20) | kind* VARCHAR(8) | years INTEGER |
+-------------------+------------------+---------------+
| Joe               | cat              |             4 |
+-------------------+------------------+---------------+
| Spot              | dog              |            10 |
+-------------------+------------------+---------------+
| Snoopy            | dog              |             3 |
+-------------------+------------------+---------------+
| Tweety            | bird             |             1 |
+-------------------+------------------+---------------+
| Joe               | bird             |             2 |
+-------------------+------------------+---------------+
assuming that 'animals' has three attributes (name, kind, and years) and the attributes name and kind form the primary key.

Milestone 2 : Basic Queries

Program Name : proj2_m2.exe
Main Source File : proj2_m2.cpp

For this milestone you need to implement basic queries and operations. Under this milestone you can assume that atomic-expr will evaluate to a relation-name. Your program should accept commands and queries as input, process the input strings immediately, and then wait for another input string until the end-of-file character is detected.

The operations you will need to implement are:

  1. Project
  2. Rename
  3. Set Union
  4. Set Difference
Note that these operations do not print anything. They only create views (which are non-persistent relations). To display the contents of the views you will need to use the SHOW command implemented in the previous milestone.

If an operation that requires two union compatible relations is given relations that are not union compatible, then the program should print 

** operation requires union compatible relations!
to the standard output. However, your implementation of these functions should throw an exception so that in later milestones this exceptional case can be handled differently.

Milestone 3 : Complex Queries

Program Name : proj2_m3.exe
Main Source File : proj2_m3.cpp

This program should be similar to the previous milestone except that it must now support all queries (including select, product, and natural join) and atomic-expr may now expand to any atomic expression. It should also support the SHOW command defined in previous milestones.

Notes on natural JOIN
The columns of a view that results from executing a JOIN operation should be organized in alphabetical order.

Milestone 4 : Complex Commands

Program Name : proj2_m4.exe
Main Source File : proj2_m4.cpp

Once this milestone has been completed you should have a program that fully implements the database management system project grammar (including all valid queries and commands).

The CLEAR command should delete all relations in a database.

Note that your database management system should have an API (Application Programming Interface) like the one described in the project description and a method for storing and loading database contents on the file system.