COMP 3380留学生讲解、辅导Java程序、辅导CS Linux machines、讲解Java编程
讲解留学生Prolog|讲解Java程
COMP 3380 – Databases: Concepts and Usage
Department of Computer Science
The University of Manitoba
Fall 2018
Assignment 4
Course Number: COMP 3380
Section Instructor Due date/time Hand‐in
A02
A01
Rob Guderian
Dr. Carson K. Leung
(Fri) Nov 23, 2018
@13:00 UM Learn
Creating and querying a database.Using the provided program,complete the program to create?
a queryable expenses database.The provided code creates a command‐line interface to query
the data. The program can compile and run, and the ‘w’ command works (though incorrectly!).
1. Create the tables to hold the data. Create a database the reflects the following relation?
2. Read in the data file. The data file is from the Winnipeg Open Data database. It can be found
here https://data.winnipeg.ca/Council‐Services/Council‐Member‐Expenses/mgde‐4fua. We
have modified it slightly for this assignment. You may use either source. You will be loading
data from a single line into the normalized database. Consider the relationships, and the
order you must add your data.
3. Complete the methods in the ‘MyDatabase’ class that are marked as TODO. You will need to
complete the method that creates the tables, reads in the data, and the functions called by
the command‐line interface.
The provided command line interface does the following:
Commands:
w: print the names and IDs of all the known wards.
e: print all the expenses, with associated ward and councilor name.
c:Print the names of all councilors
ct name: Print total expenses for councilor 'name' (e.g., ct Brain Bowman)
wt name:Print total expenses for ward 'name' (e.g., wt Transcona)
dc name:Delete councilor named 'name' (e.g., dc Brian Bowman)
de id:delete expense 'id' (e.g., de 17000)
m:Show the highest single‐time expense for each councilor, with associated councilor name
COMP 3380 (Fall?2018), Guderian & Leung A4‐2
Some notes:
HSQLDB requires single quotes for strings
See http://hsqldb.org/doc/guide/sqlgeneral‐chapt.html#sgc_data_type_guide?for the data
type names for HSQLDB
To have HSQLDB create auto‐incrementing IDs for you:
o Leave the field blank in the INSERT INTO statement
o The primary key column must me be flagged with IDENTITY. Example:
CREATE TABLE Fidgets fID INTEGER IDENTITY, price NUMERIC,?PRIMARY KEY (fID))
The data has varying number of columns. For the description, account and amount columns,
index the ‘parts’ array from the right side (e.g., use parts[length–offset]).
To compile the program, use javac cs3380F18A4.java
o Or, use a Java IDE
To run the program,you must at add hsqldb.jar your classpath.
o On Windows: java -cp .;hsqldb.jar cs3380F18A4
o On Mac OS and the CS Linux machines, you may use the provided Makefile.
o Or, configure your IDE to include hsqldb.jar in the CLASSPATH
Hand in only your code .java file. Include your data if you modified it.