Database Systems



Database Systems

CmpE 226 Project 2 Spring 2003

1. Use the database schema from project 1 and build these tables in a relational database. You will use JAVA, JDBC, and the MySql database in this project. You are to make a java program that when run will connect to the database and create all your tables with the correct attributes and corresponding types by using SQL CREATE TABLE commands. This program should also populate the tables in the database with at least 10 entries in each table by using SQL INSERT commands. MySql 4.0 can be downloaded at . A user manual and SQL examples can also be found at this website. This is an open source database and is available for a free download. The Java software development kit (J2SE 1.4.1 SDK) may be downloaded at java..

2. Create 10 SQL quires against your database. Be creative, join tables, and try to come up with useful queries that answer key questions your database was designed to answer. Please note MySql does not support nested quires or views, if you want to accomplish these types of queries, you will need to break these up into several single queries, and process this information in your application to come up with the desired result.

3. You will create a java application that when run will connect to the database, execute all your quires, and print the results to a text file, or HTML file. Please format the results in a readable form which shows what query was executed, and the result of this query.

A sample program that connects to the database, creates one table, inserts data into the table, and executes one query will be available for download on the course’s website. The program you build may be command line or have a simple GUI built using Java’s SWING. You may print the results to a file in plain text, html, or show them in a simple GUI. The results need to be shown in an understandable and organized format.

Example SQL

CREATE TABLE purchaseLog(

userID VARCHAR(30) NOT NULL,

productID BIGINT(20) NOT NULL,

purDate DATE NOT NULL,

quantity INT(3) NOT NULL,

merchantTraceNo BIGINT(20) NOT NULL );

INSERT INTO `PurchaseLog` ( `userID` , `productID` , `purDate` , `quantity` , `merchantTraceNo`)

VALUES ('aUserID', 'aProductID', 'aPurDate', 'aQuantity', 'aMerchantTraceNo') ;

Grading :

Demo: 20 points

DB Creator

Query Executor

Submissions:

Overall final Report

Updated Database Schema 5 points

10 SQL Statements : *2.5 25 points

Query

Description of the Query

Actual Output

Jave program 1: DB Creator – Code and Executable 20 pints

Jave Program 2 : Query Executor – Code & Executable 30 points

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download