HW#1 --- last modified September 10 2019 22:08:11.

Solution set.

Due date: Sep 11

Files to be submitted:
  Hw1.zip

Purpose: To gain experience creating and altering a simple database. To learn about the relational model and the relational algebra. To make sure we can do simple Java coding necessary for this course.

Related Course Outcomes:

The main course outcomes covered by this assignment are:

CLO2 -- Write SQL commands to create databases, create tables, insert/update/delete/retrieve rows in a common database management system.

Specification:

This assignment consists of two parts a written assignment and a coding assignment. Put the files you create for each part into the file Hw1.zip that you submit.

For the written part of the assignment, do the following problems and write them up in Hw1.pdf and include this file in Hw1.zip:

1. Do each of the parts of Exercise 2.2.1, but rather than use Fig 2.6, use the example database from lecture.
2. Suggest an additional relation which might be useful for the database of problem 1.
3. For each of the tables used in the first exercise, suggest which attributes should be taken as a key for the table.
4. Next install SQLite on your computer if you don't already have it. One easy way to install software that you will use from the command line is a package manager such as Apt (comes with Debian/Ubuntu Linux), Homebrew (Macos), or Chocolatey (Windows). From the command shell (Windows) or terminal (Linux/Macos), launch a SQLite shell. Use SQL CREATE TABLE statements as we did in class to create the student database relations from the first exercise. Use INSERT statements to insert the rows of these tables. Make sure to make a transcript of each of the operations you perform and their results and include it in Hw1.pdf.

For the coding part of the homework, I want you to show me you still remember Java and to get you thinking about what might be involved in programming even a tiny part of a DBMS system. Two common database queries operations are selection which returns a subset of rows from a database table and projection which returns for all rows a subset of columns. For this homework, you will write a program SelectProject.java. To grade your program, the grader will first compile it using the command:

javac SelectProject.java

from the command line, and without using any special classpath. So do not have an package statements in your program. You can assume the grader is using some variant of Java 8. Your program will then be run using a line of the form:

java SelectProject some_file_name project_col1 project_col2 ... project_coln WHERE select_col_1.rel_1.value_1 ... select_col_n.rel_n.value_n

As a concrete example, your program might be run with the line:

java SelectProject Employee.txt FNAME LNAME WHERE SALARY.gt.50000 SALARY.lt.100000 MANAGER_ID.eq.51

In terms of command line arguments, some_file_name should be the path to a plain text file in the following format: The first line consists of a sequence of tab delimited column names, subsequent lines consist of tab delimited row data of values for the columns. For example, Employee.txt might look like:

FNAME	LNAME	SALARY	MANAGER_ID
Bob	Smith	750000	54
Sally	Jacob	52000	51
Deepika	Sharma	97534	51

To keep things simple column names and values are strings made of upper case letters, lower case letters, digits 0-9, and underscore. The command line arguments after the some_file_name up till the WHERE are a list of columns to project out of the table. In our example, we want to output the FNAME and LNAME columns. The command line arguments after the WHERE keyword are conditions that a row must satisfy before it is output. For example, SALARY.gt.50000 means that the value in the SALARY column for the row must be greater than 50000. The allowing are the allowed relations that can be used in conditions: eq (equal), lt (less than), gt (greater than), le (less than or equal), ge (greater than or equal). The first argument of a condition will always be assumed to be a column name and the second argument will always be assumed to be a value the column could take. Values which are made only of digits are compared as integers, values made of letters and digits are compared as strings lexicographically (i.e., based on alphabetical order). For a row to be output all the conditions must be satisfied. Your program should scan the provided file and then output in a sequence of lines the desired columns of each row that satisfies all the conditions. For example, for the file given above, your program should output:

Sally	Jacob	
Deepika	Sharma

If some_file_name is not in the format specified or the command line arguments are not as specified, your program should output:

ERROR!

You can assume no single row is more than 32KB. You should not assume that the file some_file_name though is small enough to fit into your computer's RAM memory. Your program will be tested on large files. This completes the description of the coding part of your homework. Make sure to submit the file SelectProject.java in the Hw1.zip file you submit.