CMPUT 391: Database Management Systems

Assignment 2

CMPUT 391 (Winter 2002)

Due Date (in class): Monday March 4, 2002 in class and by e-mail before 7:30am
Percentage overall grade: 4%
Penalties: 20% off a day for late assignments
Maximum Marks: 100

Based on the final relations generated in the assignment 1 (see assignment 1 solution, create a web-based user friendly interface that allows querying the car rental database. For this, your should create the tables in ORACLE and use JDBC to access the created tables and generate the query results in an HTML page when a query is submitted.
Consider a flexible user interface that allows the expression of different queries using the schema of the tables defined in assignment 1.

Do not use your answer in assignment 1. Use the relations in the assignment 1 solution.

The interface should be an HTML. Use Tomcat introduced in tutorial 1 and create a java servlet to analyse the data entered in the HTML form and generate the equivalent SQL query that is submitted to the database using JDBC. The servlet should generate a new HTML page displaying the result of the SQL query in a table format as well as the equivalent SQL query.

HTML PAGE
WITH
HTML FORM
 -->
Java Servlet
in Tomcat
^
|
v
JDBC access
to Database
 -->
HTML PAGE
WITH
SQL QUERY
and QUERY RESULTS

The user interface is an HTML form that allows the entry of simple attribute values to answer some pre-defined queries. Here are 4 pre-defined queries. Select only two to implement. You can implement either query 1 or query 2 and either query 3 or query 4. 

EITHER

1- Display the number of car rentals per given time period (start date
and end date) for a given vehicle category and a given office or
city. If no start date is given, then start from oldest record in
database. If no end date is given then process up to the last
record. If no office is given, then consider all offices. If no
vehicle category is given, then consider all categories.

OR

2-  Display the amount of money made per given time period grouped by
vehicle category and office. If no start date is given, then start
from oldest record in database. If no end date is given then process
up to the last record.

AND EITHER

3- List the vehicle plates of cars driven more than a given kilometer
distance before a given date grouped by vehicle category for a given
office or city (all if not given).

OR

4- Display all cars which are available for renting for a given office
or city grouped by vehicle category (all if not given).

The TA will take into account the cleaness of the HTML code and Java code. It is your responsibility to make your code readable.

Deliverables:

  • HTML printout of the HTML FORM page.
  • Java code for the servlet.
  • Printout of an example of execution.
  • the deliverables should be handed out in class and sent by e-mail to the lab TA.
  • quick demo to the TA of the assignment during lab time. Assignment should be handed-in as hadr copy in class and the files (code etc.) should be send to the following TA:
    • L01 : Monday 8:00 - 10:50. TA : Chioon Lee
    • L02 : Wednesday 14:00 to 16:50 TA : Huiqing Li
    • L03 : Thursday 8:00 - 10:50 TA : Mohammad El-Hajj
    • L04 : Thursday 14:00 - 16:50 TA : Jia Li
    • L05 : Friday 8:00 - 10:50 TA : Huiqing Li
    • HOME     Activities