数据库代写 COMP 3380 – Databases: Concepts and Usage

Course Number: COMP 3380

COMP 3380 – Databases: Concepts and Usage

Department of Computer Science The University of Manitoba Fall 2018

Assignment 4

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‐1

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,useaJavaIDE

•   To run the program, you must at add hsqldb.jar to your classpath.

  o OnWindows:java-cp.;hsqldb.jarcs3380F18A4
  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.

COMP 3380 (Fall 2018), Guderian & Leung A4‐2