COMS 4111 -
Introduction to Databases, Spring 2013
Project 1, Part 3 (Final Part)
(Worth 50% of overall
Project 1 grade)
You will do Part 3 (the final part) of Project 1 with the same team as for Parts 1 and 2. If your team partner dropped the class and you did not submit a contingency plan with your Part 1 submission, then, unfortunately, you will still have to complete the project yourself. If your team partner dropped the class and you did submit a contingency plan with your Part 1 submission, then you are welcome to switch to this reduced version of your project.
As you recall from Part 1, you had two options for Part 3 of the project: you could either follow the Web Front-End Option or the Expanded-Design Option. If you stated in Part 1 that you would follow the Expanded-Design Option, it is OK for you to change your mind now and follow the Web Front-End Option for Part 3; however, if you did not submit a plan for the Expanded-Design Option when you submitted Part 1 of the project, then you do not have a choice now and you will have to follow the Web Front-End Option.
Important notes:
If you are following the Web Front-End option, you will finish building the
application that you proposed in Part 1, on top of the database that you
created in Part 2. Your application can be written in Java or PHP (your choice), and should
have a web interface. Specifically, you have a choice of two technologies for
your project:
The web front-end for your application can be HTML forms in combination with Java servlets or PHP (pick one). The less your web site looks like it is interacting with a relational database, the better. At the very least, the user should be completely shielded from having to type anything resembling SQL.
Important notes:
If you choose Option 1 on Unix for your project, you should implement your application using Java servlets. Your implementation will consist of:
1. Java servlet and/or JSP,
2. Servlet engine (Resin), and
3. Oracle backend (your database)
The application you write will use Java servlets, which receive web page requests, interact with the database server by using JDBC, and return HTML. A friendly short course on Java database programming using JDBC is at http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html.
You may use Java as your language for CGI scripts. This option is permissible, but will result in extremely slow response times, since the web server will need to load a Java VM for every invocation of your Java program. Instead, follow the directions below to install Resin, a second-tier server. Note: Using Java directly for CGI is an unsupported alternative, so we discourage you from following it.
The following are directions for installing your Java second-tier server, Resin, assuming that you use bash as shell (if you use other shells, please use an appropriately modified version of this; please contact your project mentor if you have questions):
1.
Install Resin: Download Resin Servlet Container version 4.0.35 at http://www.caucho.com/download/,
save it in your home directory, and then extract/untar
the file, as follows.
Add the following line to your .bashrc or .profile
file, or equivalent:
export JAVA_HOME=/usr/lib/jvm/java-6-openjdk
Then execute the following commands:
~$. ~/.profile
~$tar zxvf resin- 4.0.35.tar.gz
~$cd resin- 4.0.35
~/resin- 4.0.35$./configure --prefix=/home/(yourUNI)/cs4111/resin
~/resin- 4.0.35$make
~/resin- 4.0.35$make install
2.
Configure Resin: Add the following lines to your .bashrc
or .profile file, or equivalent:
export RESIN_HOME=/home/(yourUNI)/cs4111/resin
export
CLASSPATH=$RESIN_HOME/lib/resin.jar:$RESIN_HOME/lib/javaee-16.jar:$CLASSPATH
Then execute the following command, so that the environment variables get set:
~/.profile
~$cd $RESIN_HOME/conf
Edit file resin.properties to change the value of
port (stored in variable app.http) from 8080 to the port on which your host (server) will accept incoming
connections (http requests). Your team’s port number will be 6000 + (the number in one of the team member's UNI), this is basically to keep the port for each team unique.
For example, if your UNI is ab2264, your team's port number is 8264.
3.
Start the server:
java -jar $RESIN_HOME/lib/resin.jar -conf $RESIN_HOME/conf/resin.xml start
4.
Create servlets: You should put your servlets in the resin- 4.0.35/webapps/ROOT/WEB-INF/classes directory. Then, you will be
able to access your servlets at http://localhost:portnumber/Javafile.
For example, you can create a Java file as resin- 4.0.35/webapps/ROOT/WEB-INF/classes/HelloServlet.java,
and then access it as http://helsinki.clic.cs.columbia.edu:7791/HelloServlet,
where “helsinki.clic.cs.columbia.edu” is the machine where your Resin server is
running and 7791 is the port number where your Resin server is listening.
5.
Stop the server:
java -jar $RESIN_HOME/lib/resin.jar -conf $RESIN_HOME/conf/resin.xml stop
Important
note: You must stop your Resin server by following the last step
above after you’re done testing, etc. and before you logout from the
clic.cs.columbia.edu machine where you were working.
A web.xml file is required
to provide configuration and deployment information for
the web components that comprise a web application. Place
the web.xml file in the directory resin- 4.0.35/webapps/ROOT/WEB-INF/,
where resin- 4.0.35 is the directory where you installed Resin.
A sample Java program that accesses one of our Oracle servers is at http://www.cs.columbia.edu/~biliris/cs4111/projects/Proj1-3/sample.java. The program assumes that it is stored in the resin- 4.0.35/webapps/ROOT/WEB-INF/classes directory and the compiled class is in the same directory, where resin- 4.0.35 is the directory where you installed Resin. The web.xml file for this sample Java file can be found at http://www.cs.columbia.edu/~biliris/cs4111/projects/Proj1-3/web.xml.txt. Please rename the file to web.xml. An HTML file to invoke the above Java program should be placed in the directory resin- 4.0.35/webapps/ROOT/. The HTML form action should resemble the following line:
<form name =
input action = sample>
If you choose Option 1 on Windows for your project, you should install Apache Tomcat on your Windows computer. To install and configure Apache Tomcat, please refer to our instructions.
If you choose Option 2 on Unix for your project, you should implement your application using PHP.
Important note 1: Always use the class web servers, w4111a.cs.columbia.edu, w4111d.cs.columbia.edu, w4111e.cs.columbia.edu and w4111h.cs.columbia.edu (rather than www.cs.columbia.edu or www1.cs.columbia.edu), for all your development and testing related to this project.
Your implementation of your application using PHP will consist of:
The application you write will use PHP, which is executed by the web server when a PHP page is requested by a browser. The PHP source code both connects to the backend database and outputs information (HTML) to the web server, which is in turn transmitted for display on the client.
For information on using PHP with Oracle, see the PHP OCI8 manual at http://us3.php.net/oci8.
Documentation for PHP can be found at http://www.php.net/docs.php as well as through the numerous
books and websites related to PHP.
Important note 2: If you pursue this option, please follow these steps
from your CS account as soon as possible to check that your CS account is
properly configured to work with our web servers:
$ cd html
$ cp ~biliris/html/test_w4111.php ./
From your web browser, type: http://w4111a.cs.columbia.edu/~(yourUNI)/test_w4111.php
If the browser displays a message "User Name: SCOTT," then your CS account is properly configured. Otherwise, please email Hahn (hc2361@columbia.edu) right away so that he can configure your account.
Please check our document with useful PHP tips.
If you choose to use PHP, please read this section carefully and follow our guidelines to secure your database account.
The PHP source files in your html directory are executed by the web server when accessed by a client. This means that the HTML produced by your PHP files is sent to the client, not the PHP source code. This is a good thing, in terms of security, since some of your PHP files will need to include your database account information (i.e., id, password, db name).
Your password, however, is not totally secure. Anyone with a CS account can still access your files by logging into a CS department machine and examining your directory. To help mitigate any security risks, please do the following:
1. Make sure that your Oracle password (which you should have
changed) is not the same as your UNI or CS password, or the password for any
other account.
2. Within your html directory, create a
subdirectory <new_directory> for this project.
While logged in and in your html directory, execute:
chmod 711 <new_directory>
This will prevent anyone other than you from obtaining a listing of this
directory.
3. Create
your project without using the filenames index.html or index.php.
Use somewhat more cryptic, yet useful filenames (e.g., john_db_index.php
instead of index.php). The server looks for these
index.* files by default, which is nice, but the permissions change in Step 2
cannot prevent CS users from copying a file in your directory whose name they
know. You can still test your project by typing the filename in your browser's
address box.
If you choose Option 2 on Windows for your project, you should install Apache and PHP, configured with the OCI8 extension, on your Windows computer. To install and configure Apache and PHP, please refer to our instructions. Please also check our document with useful PHP tips.
If you are following the Expanded-Design Option, you need to follow the expansion plans that you outlined in Part 1, and:
1. Extend your E/R diagram from Part 1 to include the entity sets and relationship sets—and all associated real-world constraints—for your expanded design.
2. Extend your SQL schema from Part 2 on one of our Oracle servers to include the mapping of all new entity sets and relationship sets, following the directions in Part 2 of the project on how to specify constraints in your expanded SQL design.
3. Add tuples to your new SQL relations on the Oracle server, following the directions in Part 2 of the project.
You do not need to submit anything for this (final) part of Project 1 if you are following the Web Front-End Option. Instead, students will meet with the project mentor on Thursday Apr 11 or the day after. Your project mentor will email you shortly to schedule a 15' meeting.
During the meeting with your project mentor, you will show your mentor your application using a regular web browser:
1.
Unix
option: You should have your application up and running so that you and
your project mentor can access it over the web from the CS department simply by
typing a URL in a regular browser. Your project mentor will either be running
Mozilla/Firefox over Linux or Windows, or Internet Explorer over Windows. (You
may use JavaScript for some of the browser control, but make sure it is
portable and works under Mozilla/Firefox over Linux and Windows, and Internet
Explorer on Windows.) Note: The TA room only has Linux machines
just as the clic.cs.columbia.edu machines.
Window/Mac option: You should bring your laptop to your meeting
with your mentor, to be able to run the application with your mentor.
2. Your project mentor should be able to interact with your application and access the functionality that you specified in Part 1, over the database that you created for Part 2.
3. The project mentor might ask to look at your code during your meeting. You do not need to submit anything to the project mentor ahead of time, but you might have to show your code during the meeting.
4. Your web interface does not need to be fancy. (See Grading below.) However, you should not force users to type SQL. The less your web site looks like it is interacting with a relational database, the better. At the very least, the user should be completely shielded from having to type anything resembling SQL. Most interactions should involve some sort of input values in addition to the user pressing a Submit button. Whenever possible, input values should be specified using menus, radio buttons, checkboxes, scrollers, etc. Text input boxes may also be appropriate.
5. Sophisticated error handling is not necessary; however your web site definitely should not "lock up" or crash regardless of how the user chooses to interact with it.
6. Your database should contain (at least) the data that you entered for Part 2. You can, of course, add extra tuples to your tables if you want to make interaction with your application more interesting and revealing.
7. Your grade will suffer considerably if your application is not running properly when you access it from your project mentor’s machine, if you are following the Unix option, or on your machine, if you are following the Windows option. It is your responsibility to ensure that your application is not dependent on a specific platform, if you follow the Unix option, and that it is up and running when you meet with your project mentor.
8. You should have a number of example interactions prepared so that you can use your meeting time efficiently. The more you can impress your project mentor during the 15-minute meeting, the better your grade is likely to be, so come to the meeting prepared.
If you follow the Expanded-Design Option, your submission deadline is Tuesday Apr 9, at 5 p.m. ET.
1. Create a directory named <your-UNI>-proj1part3, where you should replace <your-UNI> with the Columbia UNI of one teammate (for example, if the teammate's UNI is abc123, then the directory should be named abc123-proj1part3).
2. If you have your expanded E/R diagram in electronic form, please generate a PDF version of it and name the resulting file expanded-er.pdf. Place this file inside the <your-UNI>-proj1part3 directory. If you cannot generate a PDF file with your E/R diagram, please submit a hard-copy version of the diagram to your project mentor before the submission deadline.
3. After extending your SQL schema for Part 2, to account for your expanded design (see above), and populating the new SQL tables as required (see above), generate the dump file expdat.dmp using Oracle’s export utility exp and place it inside the <your-UNI>-proj1part3 directory. See Part 2 of Project 1 for instructions on how to use this utility.
4. Generate a plain-text file called queries.txt inside the <your-UNI>-proj1part3 directory, with your team names/UNIs at the top and three "interesting" SQL queries over your expanded database. Include a sentence or two per query explaining what the query is supposed to compute; the goal is simply to help us better understand your application.
5. (Unix option only) Tar and gzip the <your-UNI>-proj1part3 directory, to generate a single file <your-UNI>-proj1part3.tar.gz (containing the expanded-er.pdf file (unless you submitted a hard-copy of the expanded E/R diagram to your project mentor), as well as the expdat.dmp and queries.txt files), which is the file that you will submit.
6. (Windows option only) Zip the <your-UNI>-proj1part3 directory, to generate a single file <your-UNI>-proj1part3.zip (containing the expanded-er.pdf file (unless you submitted a hard-copy of the expanded E/R diagram to your project mentor), as well as the expdat.dmp and queries.txt files), which is the file that you will submit.
7. Login to CourseWorks at https://courseworks.columbia.edu/ and select the site for our class.
8. From the left pane, select "Assignments" and then "Project 1.3"
9. Go to the "Submission" section and attach your <your-UNI>-proj1part3.tar.gz file (from Unix) or your <your-UNI>-proj1part3.zip file (from Windows).
10. Hit the "Submit" button.
Web Front-End Option: Your grade for Part 3 of Project 1 will be a function of how well your application (which should be up and running) matches your specification that you submitted as Part 1, of how well you have incorporated any feedback that your project mentor has given you, and of how well you have followed the guidelines above. Your project mentor has kept a copy of your specification and of his comments. Your grade will not be influenced by how fancy the web-based user interface to your application is. It is sufficient and perfectly fine for this interface to be plain and simple as long as it supports the functionality that you indicated earlier, following the guidelines above about not having to type SQL commands, not “locking up” on unexpected input, etc.
Expanded-Design Option: Your grade for Part 3 of Project 1 will be a
function of how well you have incorporated any feedback that your project
mentor has given you, and the following factors:
1. Quality of your expanded E/R diagram: We will evaluate how well your expanded E/R diagram implements your plans for the Expanded-Design Option from Part 1, and how well your expanded E/R diagram models your application, including how well you modeled any relevant real-world constraints.
2. Quality of your expanded SQL schema and implementation on Oracle: We will evaluate how well you mapped your expanded E/R diagram, including constraints, into a SQL schema on Oracle, using the techniques that we covered in class.
3. Quality of your expanded constraint handling: We will evaluate how well you managed to capture real-world constraints of your expanded design through primary key, foreign key, unique, and attribute- and tuple-based CHECK constraints.
4. Quality of the expanded real-world (or at least realistic) data that you loaded into the expanded database on Oracle.