MIS 307 Homework 5

Non-programming Questions (10 points)

Use the sample database (four tables Invoice, Product, LineItem, and Customer) in Page W1022 of CH24 to answer the following questions.

R24.9 Give a SQL query that lists all customers in California or Nevada.

R24.12 Give a SQL query that lists all products that have been purchased by a customer in

California.

R24.13 Give a SQL query that lists all line items that are part of invoice number 11731.

R24.16 Give a SQL update statement that raises all prices by ten percent.

Programming Question (30 points)

Write a Java program that does the following jobs.

Step1. Creates a House table with property id (PID), neighborhood, year built, sqft, number of bedrooms, number of bathrooms, and price.

Step2. Reads data from the CSV file and insert the houses into the table created in Step 1.

Step3. Interact with the user, where the user can select one of the following actions.

– (Q) Quit: quit the program

– (A) Add a house

– (C) Calculate average house price

– (P) Print a subset of the houses based on SQFT

– (B) Print a subset of the houses based on numBeds and numBaths

The user can choose one of these actions by typing Q, A, C, P, or R in the console. When adding a house, the user must provide property id (PID), neighborhood, year built, sqft, number of bedrooms, number of

bathrooms, and price through the console. When selecting a subset of the houses based on SQFT, a

lower bound SQFT value must be provided. When selecting a subset of the houses based on numbed

and numBaths, lower bounds must be provided.

To help you understand how the program works, a sample run is available in Appendix A in Page 3 of

this document.

Four files are provided with the assignment.

1. AmesHousingDB.java: the main class of this assignment.

2. Database.properties: file for Apache Derby

3. SimpleDataSource.java: file for Apache Derby

4. Ameshousing.csv: sample CSV file for Step 2.

Submit one source file (AmesHousingDB.java). In other words, Database.properties,

SimpleDataSource.java, or Ameshousing.csv should not be submitted. In modifying the source file,

1. Do not change the file (class) names. Three Points will be deducted if you have different names.

Class name changed due to Canvas (e.g., Classname(1).java, Classname(2).java) is ignored.

2. Fill in your name in the @author section of the comment in each of the files. If you do not, two

points will be deducted.

Submit your work to Canvas. Make sure to submit a source file (*.java). Class file (*.class) will not be

graded and receive zero credit.