CSCE-315: Programming Studio (Summer 2014)

This project is a team project, ideal team size is four. If the number of students is not divisible by four, we can have a few teams with size close to four. The teams will be assigned by the instructor, based on the programming proficiency survey.

This project consits of two parts. The task of the first part is to implement a simple database management system (DBMS). In the second pars, you will manually test the DBMS's basic functionality in a simple application domain.

Database management systems are very complex pieces of software. They support concurrent use of a database, transactions, permission handling, query optimizations, logging, you name it. To be efficient, they utilize highly tuned algorithms developed during the course of decades. So obviously, for a two-week long project, we have to simplify a lot. We thus base our DBMS on relational algebra.

Relational algebra is a formal system for manipulating relations. It consists of only six primitive operations. Each of the operations take relations as arguments, and produce a relation as a result. The operations thus compose freely.

The upside of using relational algebra is that the implementation effort of the DBMS stays manageable. The downside is that queries tend to be more verbose and maybe a bit harder to construct than, say, with SQL.

CREATE TABLE animals (name VARCHAR(20), kind VARCHAR(8), years INTEGER) PRIMARY KEY (name, kind);

INSERT INTO animals VALUES FROM ("Joe", "cat", 4);
INSERT INTO animals VALUES FROM ("Spot", "dog", 10);
INSERT INTO animals VALUES FROM ("Snoopy", "dog", 3);
INSERT INTO animals VALUES FROM ("Tweety", "bird", 1);
INSERT INTO animals VALUES FROM ("Joe", "bird", 2);

SHOW animals;

dogs <- select (kind == "dog") animals;
old_dogs <- select (age > 10) dogs;

cats_or_dogs <- dogs + (select (kind == "cat") animals);

CREATE TABLE species (kind VARCHAR(10)) PRIMARY KEY (kind);

INSERT INTO species VALUES FROM RELATION project (kind) animals;

a <- rename (aname, akind) (project (name, kind) animals);
common_names <- project (name) (select (aname == name && akind != kind) (a * animals));
answer <- common_names;

SHOW answer;

WRITE animals;
CLOSE animals;

EXIT;

The second phase of this project is to test your DBMS's functionality by manually typing in the commands in the DML, within the context of a specific DB application domain. The DB application domain will be a simple DVD rental database:

1. Customer data: USER-ID, Firstname, Lastname, Phone Number, account balance
2. DVD data: Inventory number, DVD-ID, Title
3. Rental log: USER-ID, Inventory number, Check out date, Due date
4. Operations: Add new customer, Add new DVD, Remove customer, Remove DVD, Update customer, Update DVD, List customer, Search customer (by name, phone), List DVD, Search DVD (by ID, title), Search inventory, Add credit to customer's account (add to current balance), Apply late penalty to customer's account (subtract from current balance), Check out DVD (don't allow if balance is negative), Check in DVD (apply penalty if late), Show rental list by customer, Show customers that have negative account balance. Show customer list by DVD-ID.

You should envision a scenario for initial database set up and a typical day at a DVD rental shop, based on which you will come up with a series of DB commands that correspond to the scenario. Finally, you will type those commands by hand and see if your DBMS works properly.

• All teams must use github.tamu.edu. Give access to the TA and the Instructor.
• Each team must maintain a development log (wiki page in github.tamu.edu titled "Development log") updated by the team members. This log will be graded. There is no designated format, except that you need to time stamp, write down the name, and write a brief description of the activity. We will check your daily progress.
• Major routines should include unit testing.
• Demo in the lab may be required.

1. Design documents: Follow the guidelines in Scott' Hackett's "How to Write an Effective Design Document" (Writing for a Peer Developer). Include all four sections described in the guide.
   • Set up your design document ("Design document") as a wiki page in github.tamu.edu.
   • The design document should cover both phase 1 (DB engine) and phase 2 (DB app testing).
   • Documents for phase 2 should include ER diagram and corresponding relation schema, besides other things.
   • Grading:
     1. 20%: all four sections included.
     2. 50%: Part I DBMS - parser, DB engine
     3. 30%: Part II DB app testing - ER diagram, relation schema, testing workflow (create, insert, ... )
2. Parser code: Upload your parser code. It should be able to accept or reject an arbitrary command or query: Accept syntactically correct command or query, and reject anything that violates the syntax.
   1. 10%: layout, style, comments
   2. 30%: commands (open, close, ..., delete)
   3. 40%: queries (select, project, ..., product)
   4. 10%: condition (conjunction, comparison, operators, etc.)
   5. 10%: development log
3. DB core function code: Upload the core functions. These are C++ classes and functions that implement the core DB functionality. For example, function calls for creating a relation table data structure, etc. You don't need to link this with the parser just yet. You should be able to write a test program in C++ that directly calls the core DB functions create( ....), insert( ... ), etc.
   1. 10%: layout, style, comments
   2. 30%: commands (open, close, ..., delete)
   3. 40%: queries (select, project, ..., product)
   4. 10%: condition (conjunction, comparison, operators, etc.)
   5. 10%: development log
4. Final project code + DB app test session log + report: The DBMS should be a stand-alone executable that integrates the parser and the DB core functions.
   • DBMS engine should compile into a stand-alone executable application so that when you run the application, it works as a DBMS command shell, where you can type in any command or query.
   • DB app test session log should include a detailed manual test of the DBMS, starting with the creation, insertion, etc. and the output of those commands. Set up a wiki page called "Test session log".
   • post production notes (changes you had to make to your design and why, difficulties, solutions, lessons learned). Make it a wiki page "Post production notes".
   • individual work load distribution (percentage, must add up to 100%). Include this in the "Post production notes".
     • Formula for individual score calculation is as follows:

       individual score = min(sqrt(your percentage/25)*team_score,110)

       For example, if your contribution was 20% and your team score was 85, your individual score is min(sqrt(20/25)*85,110) = 76. Note that 25% is the baseline (equal contribution by all four members). If your contribution was 30% and your team score was 85, your individual score is min(sqrt(30/25)*85,110) = 93.
   • Development log (wiki page).
   • Final Grading:
     1. 5%: Layout, style, comments
     2. 40%: DBMS engine: completeness, functionality
     3. 10%: Test session log: completeness, accuracy
     4. 5%: Post production notes
     5. 10%: Development log
     6. 30%: Weighted grades from earlier submissions (design doc, parser, DB core function).