CS3223: Database Systems Implementation

Project Assignment 0: Setting up SimpleDB

Every assignment will involve running the SimpleDB database system. It is essential that you get SimpleDB installed and become comfortable with its use as soon as possible. The setup comprises two parts: You MUST do part I; while part II is optional, for this project, it is sufficient to work with the standalone server mode. Please do the following tasks by next class.

PART I

1. Ensure that the Eclipse IDE is on Your Computer

If you do not have the Eclipse IDE on your computer, go to eclipse.org to download and install Eclipse for Java. If you have a different IDE then that is fine, but you are responsible for getting the SimpleDB code to work with it.

2. Download the SimpleDB Source Code

Download SimpleDB.zip and extract its contents on your computer. The extracted folder should have subfolders named simpledb and simpleclient. (It will also have a folder named derbyclient, but that can be ignored.)

3. Create a Project for the SimpleDB Engine

A. In Eclipse, create a new Java project named SimpleDBEngine.

· You will need to specify the location. I recommend using the default location, which tells Eclipse to create a folder named SimpleDBEngine within its workspace.

· You should specify "Create separate folders for sources and class files". Eclipse will create folders named src and bin within the SimpleDBEngine folder.

· Click on the Finish button.

B. Use the operating system to copy the entire downloaded simpledb folder to the folder SimpleDBEngine/src in your Eclipse workspace.

· When you are done, the src folder should have one child folder, namely simpledb. The simpledb folder should have the child folders buffer, file, etc.

C. In Eclipse, execute Project/refresh (F5) to compile all the source files.

· The bin folder will now contain a class file for each source file.

4. Create a Project for the Client Code

A. In Eclipse, create a new Java project named SimpleDBClients.

· Configure the project in the same manner as the first two bullet points of part A above.

· Instead of clicking "Finish", click "Next" to get to the Java Settings window.

· Click on the Projects tab. Then click Add, and click the box for the SimpleDBEngine project. (Doing so adds the SimpleDB source code to the project's class path. Otherwise, the client code will not be able to resolve references to the SimpleDB classes.)

· Now you can click the Finish button.

B. Use the operating system to copy the contents of the downloaded simpleclient folder into the SimpleDBClients/src folder in your Eclipse workspace. Do NOT copy the enclosing simpleclients folder. The src folder should have four items: two folders and two files.

C. In Eclipse, refresh the project as in the previous step C.

5. Run the Embedded Client Programs

A. Create the student database in embedded mode.

· Look at the programs in the embedded folder of the SimpleDBClients project.

· Run CreateStudentDB. It will create a database named studentdb with several tables: STUDENT, DEPT, ENROL, COURSE and SECTION.

· Refresh the project. You should see a folder for the studentdb database in the project window. Feel free to examine its contents.

· Run StudentMajor. It should open a console window and display 9 records showing the names of the students and their majors.

· Run the ChangeMajor client, which will change the MajorId value of Amy’s record in the STUDENT table. Re-run the StudentMajor program to verify this.

· Run CreateStudentDB again. Technically, you shouldn’t do this, but do it anyway just for fun. Re-run StudentMajor. What happened to the database?

B. Delete the database and re-create it. [NOTE: When working on the assignments, if your database is corrupted as a result of bugs, you should delete your database and re-create it.]

· From the Eclipse client project, delete the folder containing the files for the studentdb database. You just destroyed the database!

· From Eclipse, re-run CreateStudentDB. You just re-created the database. Rerun StudentMajors to verify that it is back to normal.

· If you want to see the database folder in the Eclipse project list, refresh the project.

6. Run the SimpleDB Engine as a Server

A. Create a run configuration for the server program.

· Go to “Run Configurations” in the Eclipse Run menu. Add a new configuration to your SimpleDBEngine project, called “SimpleDB Server”. In the field for the main class, enter “simpledb.server.StartServer”.

· By default, the server will use a database named “studentdb”. This is what I recommend. But if you want to use a differently-named database, use the Arguments tab in the configuration to enter the database name.

B. Run the SimpleDB Server configuration you just created. A console window should appear indicating that the SimpleDB server is running.

C. The server creates its database in a different location from the embedded client. The folder for this database lives in the SimpleDBEngine project. Refresh the project to see it in the Eclipse project window.

7. Run the Server-based Client Programs

Look at the programs in the network folder of the SimpleDBClients project.

· While the server is running, run the CreateStudentDB and StudentMajor clients. They should print the same output as in step 6.

· Go to the console window for the server, and shut it down (by clicking on the red square near the top of the console window). Run StudentMajor again. You should get an error message.

· Rerun the server, then run StudentMajor. It should now work.

· Run the ChangeMajor network client. Now you have two slightly different student databases. In the embedded database, Amy is a math major. In the network database, Amy is a drama major.

8. Run the SimpleIJ Client Demo

· Run the program SimpleIJ, which is in the “default package” folder for the SimpleDBClients project.

· The first thing it will ask for is a connection. Enter the following string, which will establish a connection to the embedded database.

jdbc:simpledb:studentdb

· The client will now repeatedly ask you to enter SQL queries, one per line. Type the following query, which should print the name and majorid of all students.

select sname, majorid from student

Note that Amy has majorid = 20 in this database (if you have recreated StudentDB in Step 5).

· Type “exit” to terminate the program.

· Assuming that the server is still running, re-run SimpleIJ. This time, enter the following network connection string

jdbc:simpledb://localhost

This will connect you to the network database (If your server is not running, it will show an error message). Type the following query, and note that Amy has majorid = 20 in this database.

select sname, majorid from student

· Play with the engine. Try entering some other queries into SimpleIJ. See Appendices below for the names of the tables and their fields, and the subset of SQL supported by SimpleDB. What happens when you try to execute an SQL statement that SimpleDB doesn't support?

· Type “exit” to terminate the program.

· Shut down the server (by clicking on the red square near the top of the console window).

9. Finish Up

Configuring a system is often deceptively difficult.

· The point of this assignment was to ensure that your system is properly configured, and to get you totally comfortable using it.

· Subsequent assignments will ask you to change the code for the system. The time you spend now getting comfortable with the system will make it possible for you to debug your code confidently and effectively.

PART II: Standalone server (without JDBC)

Part I allows you to understand how to connect to a DBMS (in this case SimpleDB) using JDBC. For the project, due to time constraint, we will focus on the server part only. As such, it suffices to work only at the backend, i.e., you can write all your test programs to access the SimpleDB classes directly without connecting to it as a JDBC client. With this, there is also no need to switch between the client and server when you need to code and test your programs.

A. Create a test subdirectory in SimpleDB

· Use the Operating System to create a subdirectory called test under your SimpleDB project.

· Copy the java files related to the StudentDB in Part I to test (i.e., CreateStudentDB, StudentMajor, ChangeMajor, FindMajors). These files can be found in the subdirectory simpleclient of the downloaded SimpleDB source code. There are two other files in this subdirectory – SimpleIJ and StudentMajorNoJDBC (StudentMajorNoJDBC is a non-JDBC version of StudentMajor). Copy these files to test also. Rename StudentMajorNoJDBC to StudentMajor.

B. Modify all the files in test to work on the SimpleDB classes directly.

· Study StudentMajorNoJDBC to see how this can be done. You can also look at PlannerTest1 and PlannerTest2 in subdirectory Plan of SimpleDB. CreateStudentDB, ChangeMajor and FindMajors are straightforward to modify.

· A key difference in SimpleIJ from the other test programs in SimpleDB is the code within the doQuery routine that prints the output. In the other programs (e.g., StudentMajor, PlannerTest1, PlannerTest2), the columns/fields to be printed are hardcoded. [Hint: Look at the structure for schema (under SimpleDB’s record subdirectory); also see how the information are used in SimpleDB subdirectory jdbc/embedded’s EmbeddedMetaData. Look at how the information is passed between the server and the client-based SimpleIJ.] If you are unable to handle this, its fine. Just hardcode queries for testing.

C. In Eclipse, refresh the project.

Appendix I: Sample StudentDB database

The database comprises 5 tables as shown below:

· There is a STUDENT record for each student that has attended the university. Each record contains the student’s ID number, name, graduation year, and ID of the student’s major department.

· There is a DEPT record for each department in the university. Each record contains the department’s ID number and name.

· There is a COURSE record for each course offered by the university. Each record contains the course’s ID number, title, and the ID of the department that offers it.

· There is a SECTION record for each section of a course that has ever been given. Each record contains the section’s ID number, the year the section was offered, the ID of the course, and the professor teaching that section.

· There is an ENROLL record for each course taken by a student. Each record contains the enrollment ID number, the ID numbers of the student and the section of the course taken, and the grade the student received for the course.

Appendix II: Subset of SQL Syntax Supported in SimpleDB

SimpleDB implements only a tiny subset of standard SQL and imposes restrictions not present in the SQL standard. Here are some basic restrictions. The assignments will require you to implement some of the omitted features.

A query in SimpleDB consists only of select-from-where clauses in which the select clause contains a list of field names (without the AS keyword), and the from clause contains a list of table names (without range variables).

The terms in the optional where clause can be connected only by the boolean operator and. Terms can only compare constants and fieldnames for equality. Unlike standard SQL, there are no other comparison operators, no other boolean operators, no arithmetic operators or built-in functions, and no parentheses. Consequently, nested queries, aggregation, and computed values are not supported.

Because there are no range variables and no renaming, all field names in a query must be disjoint. And because there are no group by or order by clauses, grouping and sorting are not supported. Other restrictions are:

· The “*” abbreviation in the select clause is not supported.

· There are no null values.

· There are no explicit joins or outer joins in the from clause.

· The union keyword is not supported.

· An insert statement takes explicit values only. That is, an insertion cannot be specified by a query.

· An update statement can have only one assignment in the set clause.

Acknowledgement

Part I of this assignment is from Edward Sciore. The Appendices are extracted from his textbook.