|
(Due : 10/11/2007) |
The goal of this project is to get familiar with SQL in relational database system by creating tables, populating the database, and executing various SQL queries. In addition, you can experience how the JDBC works to make a connection between Java program and Oracle database located in remote DB server.
l
SQL
(90 points)
|
Suppose that the following
tables are part of a library database system, where keys are underlined:
Student(studentID, firstname, lastname, grade, dept, DOB, fine) stores
the information of students, Book(ISBN, title, author, edition, year)
stores information of books, and Borrowed (studentID, ISBN, date, due)
stores the information of borrowing activities in libraries. |
Write SQL
statements for each statement below
1.
Create
the all three tables in the database without violating primary key integrity
constraints
2.
Insert
at least 10 tuples for each table with meaningful data for the following
queries.
3.
Retrieve
all student’s full name having more than $10 as a fine in the Dept of Computer
Science.
4.
Retrieve
all book’s name published in 2007.
5.
Find
how many students having “Michael” as the first name
6.
Retrieve
all books borrowed by “Michael”
7.
Find
student’s full name and book’s title, where the due is “10/30/2007”.
8.
Find
the student last name and his/her department who has the highest borrowing
record from 2000 to 2007.
9.
Find
the most popular book’s title and author in 2006.
10. Update the student’s fine with “0”
if he/she belongs to the Dept of Computer Science.
In order
to execute the above SQL statements you are required to use “isql” provided here.
With “isql”, you can execute SQL statements by running “isql.class” file in DOS
windows.
l
Improvement
of functionality for “isql” (10 points)
As you
will see that only a tuple is able to be inserted into a table one at a time in
the current “isql” environment. This puts users in trouble if there are a
number of data to be inserted. Thus, this project requests you to improve the
“isql” functionality able to insert multiple tuples (named “minsert”) from
external text file which stores more than one “insert” SQL statements. The
format of “minsert” command following the “isql” prompt “SQL>” will be as
such “minsert text-file-name”. The external file will be filled with multiple
“insert” SQL statements.
ü
Submission
Send a zip file to TA by email(aq9320@wayne.edu) with all source codes and all necessary files included. The zip file should include “read.txt” to explain how to compile and run your system, in addition to the information that TA must know to grade.Your program should be executable by “run.bat” file without doing any other extra works. TA is not required to make your system run. So you should take all responsibilities for getting your system ready to be graded. To do so, you must store all SQL answers into “sql.txt” to verify if the requirements of SQL part have been fulfilled and create a text file named “minsert.txt” with more than 5 tuples to be inserted. The zip file should be named after your name, for example, for project1 if your name is “David Smith”, then your file should be named as “david_smith_project1.zip”. The title of your email should be “CSC4710:Project Project1 Submission”.