HW#1 --- last modified September 17 2023 16:29:01.

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:

CLO1 (Course Learning Outcome 1) -- Explain basic database concepts, including the structure and operations of the relational data model.

CLO4 -- Use SQL as a data definition language (DDL) to create and alter databases, tables, views, and indexes

CLO5 -- Use SQL as a data manipulation language (DML) for querying and modifying databases

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:

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. Remember to also include a readme.txt file with a list of the team mates of your group as well as any other code set-up, etc. details you would like to mention to help the grader grade your homework.

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. Consider the following subset of a database used for discussion groups:

   USERS

   USER_ID	FIRST_NAME	LAST_NAME	USER_NAME	EMAIL
   1	Jerry	Springer	jspringer	springer@dev.null
   2	Bob	Barker	bbarker	barker@dev.null
   3	Dick	Van Dyke	dvdyke	vdyke@dev.null

   
   

   USER_GROUP

   USER_ID	GROUP_ID	STATUS	JOIN_DATE
   1	1	BANNED	1691188589
   1	2	ACTIVE	1691084584
   2	1	INVITED	1681984623
   2	2	ACTIVE	1681984523
   3	1	ACTIVE	1671100580

   
   

   SOCIAL_GROUPS

   GROUP_ID	GROUP_NAME	CREATION_TIME	OWNER_ID
   1	Epynomous	1671100580	3
   2	Game Shows	1681984523	2

   Indicate the following:

   1. The attributes of each relation.
   2. The tuples of each relation.
   3. The relation schema for each relation
   4. A suitable domain for each attribute.
2. Suggest an additional relation which might be useful for the database of problem 1, given its schema, and some example tuples.
3. For each of the tables used in the first exercise, suggest which attributes should be taken as a key for the table. For each relation, if we only looked at the tuples in the table, given an example of another collection of attributes which could be a key.
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 discussion 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 order by which sorts the query results in either ascending or descending order according to the values in some column. For this homework, you will write a program SelectOrder.java that will do this for text file based tables. To grade your program, the grader will first compile it using the command:

javac SelectOrder.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 recent variant of Java. Your program will then be run using a line of the form:

java SelectOrder some_file_name WHERE select_col_1.rel_1.value_1 ... select_col_n.rel_n.value_n ORDER_BY sort_column sort_direction

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

java SelectOrder Employee.txt WHERE SALARY.gt.50000 SALARY.lt.100000 MANAGER_ID.eq.51 ORDER_BY LNAME DESC

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 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 following 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 compute the sequence of rows that would satisfy the query. In a real DB, these results might be streamed to disk before sorting and a disk based sorting algorithm would be used. You can assume for this homework you have enough RAM to keep the selected rows in memory. Your program should then sort these rows according to values in the specified ORDER_BY column and output the sorted rows, one on each line. There are two possible accepted values for sort order that your program should support ASC (ascending order), and DESC (descending order). As an example, if Employee.txt contained the data above, and the command line was as above, then your program should output:

Deepika	Sharma	97534	51
Sally	Jacob	52000	51

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

ERROR!

and stop.

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, however, you can assume the output of any query we will test on can fit in 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 SelectOrder.java in the Hw1.zip file you submit.