Database SystemsCmpE 226 Project 2 Spring 20031. 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 www.mysql.com. 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.sun.com.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 yourapplication 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. Theprogram 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 SQLCREATE TABLE purchaseLog(userID VARCHAR(30) NOT NULL, productID BIGINT(20) NOT NULL, purDate DATE NOT NULL, quantity INT(3) NOT NULL,merchantTraceNoBIGINT(20) NOT NULL );INSERT INTO `PurchaseLog` ( `userID` , `productID` , `purDate` , `quantity` , `merchantTraceNo`)VALUES ('aUserID', 'aProductID', 'aPurDate', 'aQuantity', 'aMerchantTraceNo') ;Grading :Demo: 20 pointsDB CreatorQuery ExecutorSubmissions:Overall final ReportUpdated Database Schema 5 points10 SQL Statements : *2.5 25 pointsQueryDescription of the QueryActual OutputJave program 1: DB Creator – Code and Executable 20 pintsJave Program 2 : Query Executor – Code & Executable 30
View Full Document
Unlocking...