In this assignment, you will use SQLiteStudio and JDBC: Use SQLiteStudio utility (if you haven't already...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
In this assignment, you will use SQLiteStudio and JDBC: Use SQLiteStudio utility (if you haven't already done it): o Connect to your database o Create the property, Customer, and Rental tables in your database using SQL scripts. Create an additional index on the Rental table. o Populate each of those tables using SQL scripts • Use JDBC to connect your Java program to your database and perform a variety of select, insert, update, and delete operations o Download your empty database with right click and save as using the following link. Source Directory Download the compressed file under the name "Pgm3.zip". You should notice that it has already coded the following classes. Two of them are partially coded: MySqlUtility.java P3Main.java java code which can print the result set and meta data. You should not have to change this code. contains a static main method for invoking your P3Program connecting to the database that you should have filled as explained below. You should not have to change this code. contains your java source code which uses jdbc. Initially, it will have some constants. This is where you will do the bulk of your work. P3Program.java You should notice that it created a "Script" directory which should contain: createProperty.sql createCustomer.sql createRental.sql insertProperty.sql insertCustomer.sql insertRental.sql SQL script to drop Property if it exists and create the Property table. SQL script to drop Customer if it exists and create the Customer table. SQL script to drop Rental if it exists and create the Rental table. SQL script to insert rows into the Property table. SQL script to insert rows into the Customer table. SQL script to insert rows into the Rental table. Using SQLiteStudio and adding your database 1. Launch the SQLiteStudio tool and connect to the database (as explained in class) 2. Open the "propertyDB.db" database that is located in the root folder of your Java project. 3. Your database doesn't have any tables. If not empty, you could drop the existing tables. 4. Open and execute the sql files that you can download from Blackboard to create and fill in the database tables in the following order: createProperty.sql createCustomer.sql createRental.sql insertProperty.sql insertCustomer.sql insertRental.sql You may open your files in any other editor (Notepad/Notes/Gedit) and paste the contents of those files. 5. Confirm that those tables are populated running the following queries in a new Query tab: select from Property; select from Customer; select from Rental; 6. Make sure you exit from the SQLiteStudio app to avoid that the database file has only access. Java Programs Download the zip file attached in the folder of the problem statement. Open the project in Intellij (or you can make it work in another environment by importing the different classes). Please, remember that this is a Maven project, which, as explained in class, will be in charge of obtaining all dependencies. P3Program.java 1. Create a printCustomers(title, resultSet) instance method which prints the title, a column heading, and the data for each tuple. Make certain you check for null values where appropriate and print "---". This must be coded in a manner similar to the printSections method in the MySQLandJDBC notes; however, you should indent the column headings and column data four spaces to improve readability. The output must match the output shown below. 2. Create a runProgram method which does the following: a. Test printCustomers() method Use statement to execute this select statement: b. Use printUtility() method select c.* from Customer c Use printCustomers("Beginning Customers", resultSet) to print those customers. c. Use statement.executeUpdate to insert a row into Customer which uses: • 1999 for the custNr • your name for the name • your state of birth (or TX if you were born outside of the USA) for the baseLoc your statt Use statement to execute this select statement: select m.* from Property mi Use MySqlUtility.print Utility("Beginning Properties", resultSet) to print those properties. e. • your birthdate (or use NULL if you would rather not provide it) for birthdate your birthd • your gender Note: place the executeUpdate in a special try... catch to print an error if there was a duplicate as was done in our notes Example # 14. Do NOT terminate. Show a message that it was not possible to be inserted as "Error inserting customer: " + not pos customerNumber. Use the default case, since it is possible that the numbers for MySQL could differ than the ones for SQLite. d. Display updated values . Use statement to get the customers (as was done in step a.) and printCustomers("Customers after I was added", resultSet). Create a prepareStatement which does an insert into Rental using substitution parameters for each attribute. This should be done outside the for statement of the next step. f. Use a Java for statement to iterate through the strPropertyIdMarray until a value of "END" is encountered. Set the substitution parameters as follows: Parameter 1 to 1999. Parameter 2 to strPropertyldM[i]. Assuming i is your counter variable in the for. Parameter 3 to "2019-12-14". (You will need to use String and setString, instead of Date). Parameter 4 to a totalCost using setDouble. The first rental should use 200.00. Increase the amount by 10.00 for each of the rentals (i.e., use 200, 210, 220). g. Execute the insertion using preparedStatement.executeUpdate(). Note: place the executeUpdate in a special try... catch to print an error if there was a duplicate as was done in our notes Example # 14. Do NOT terminate. Show a message that it was not possible to be inserted as "Error inserting customer: " + customerNumber. Use the default case, since it is possible that the numbers for MySQL could differ than the ones for SQLite. Create a prepareStatement to select rentals (all columns) for a custNr that is provided as a substitution parameter. Provide 1999 for the custNr parameter and use preparedStatement.executeQuery() to execute it. Print the result using MySqlUtility.printUtility("My rentals", resultSet). h. Use statement to execute a select statement which returns the propid, custNr, customer name, and totalCost for customers who rented properties that customer 1999 coacomer ameyang meny cono also rented. Do not include 1999 in the returned rows. Print them using aporanc CON MySqlUtility.printUtility("Other customers renting my properties", resultSet). i. Use statement.executeUpdate to update your rentals decreasing the totalCost by 10% (.e., set totalcost-g (i.e., set totalCost = 0.90* totalCost). totalcos j. Repeat step g, but use "My Cheaper Rentals" as the title. k. Use statement to execute a select statement which returns propld and count(*) for properties having more than two rentals. (See example # 15 in the SQL DML notes.) Prope mams mor Print the results using MySqlUtility.printUtility ("Properties Having more than 2 rentals", resultSet). 1. Delete the rentals for customer 1999 from the Rental Table using statement. m. Repeat step h, but use this for the title "Other customers renting my properties after my rentals were deleted". Of course, no results are printed. To compile your code (either through the teminal or Maven interface): mvn compile To execute your main: mvn package exec: java What to submit via BlackBoard? Submit a zip file named LastnameFirstName.zip containing: • Your P3Program.java • Your propertyDB.db Your output generated by your program. Place it in a file named p3Out.txt Do not include any directories. • Sample Partial Output: Beginning Customers CustNr Name 111 Ava Kashun 222 Cam Aruh 333 444 480 555 601 Pete Moss Bill Board Anita Vacay Perry Scope E Race State Birth Dt Gender 2000-04-01 M 1990-09-11 M ARANY TX CA FL CA TX OK OK 1992-03-03 M 1985-04-04 M 1975-06-01 F 1927-04-15 M 1986-10-01 M Rubric: 666 755 30 points Tom E Gunn Ray Mee Bob Wire T Doh 777 855 888 890 901 Peg Board 902 Al B Tross 903 B B Gunn 984 999 1999 Penny Lane Anita Break Sally Mander Marcus Absent Larry Beginning Properties Forest View propId... locDesc.... BEANSBD Friole Heaven FIREHHH HOMEJJJ MTNDDD NYCCC Times Square OILAAA Oil Slick QUAKELLL Rock N Roll RVBBB Home Alone Mountain View SAEEE SNOWFFF SNOWNNN STARIII TENTGGG Kamp Komfort VACAYKKK Griswold Paradise WESTMMM Desert View 15 Rows Water Logged Camper River Shores Snowcap Ridge Snowy View Star View M 1965-04-01 M 1973-07-07 M CA 1975-04-15 F CA 1990-08-08 F CA NY CA 1992-08-09 F 1987-04-04 F 138 333 23:EER OK 1957-07-12 M F F 1999-09-09 M TX 1957-12-04 M NY --- NY state propType rating TX CA CO CO NY TX CA FL TX CO co CO CO CO TX T T T T T T M M T T T с 1x^^*=*==³*16= с T с NULL + 5 points for performing all 6 operations in SQLite studio correctly (Database) + 5 points for showing the results of printCustomers method as explained and shown above - 2 points if nulls are not shown correctly - 1 points if the output does not match the examples shown above with the corresponding spaces. +2 points for functionality 2a including the method call + 1 points for functionality 2b correctly and as specified + 2 points for functionality 2c correctly + 1 point for functionality 2d correctly + 5 points for showing the correct inserted values following steps 2e to 2g. + 5 points for showing the correct updated values following steps 2i to 2j. + 2 points for showing the correct output of query of 2k. + 2 points for deleting and showing the correct output of 21 to 2m. In this assignment, you will use SQLiteStudio and JDBC: Use SQLiteStudio utility (if you haven't already done it): o Connect to your database o Create the property, Customer, and Rental tables in your database using SQL scripts. Create an additional index on the Rental table. o Populate each of those tables using SQL scripts • Use JDBC to connect your Java program to your database and perform a variety of select, insert, update, and delete operations o Download your empty database with right click and save as using the following link. Source Directory Download the compressed file under the name "Pgm3.zip". You should notice that it has already coded the following classes. Two of them are partially coded: MySqlUtility.java P3Main.java java code which can print the result set and meta data. You should not have to change this code. contains a static main method for invoking your P3Program connecting to the database that you should have filled as explained below. You should not have to change this code. contains your java source code which uses jdbc. Initially, it will have some constants. This is where you will do the bulk of your work. P3Program.java You should notice that it created a "Script" directory which should contain: createProperty.sql createCustomer.sql createRental.sql insertProperty.sql insertCustomer.sql insertRental.sql SQL script to drop Property if it exists and create the Property table. SQL script to drop Customer if it exists and create the Customer table. SQL script to drop Rental if it exists and create the Rental table. SQL script to insert rows into the Property table. SQL script to insert rows into the Customer table. SQL script to insert rows into the Rental table. Using SQLiteStudio and adding your database 1. Launch the SQLiteStudio tool and connect to the database (as explained in class) 2. Open the "propertyDB.db" database that is located in the root folder of your Java project. 3. Your database doesn't have any tables. If not empty, you could drop the existing tables. 4. Open and execute the sql files that you can download from Blackboard to create and fill in the database tables in the following order: createProperty.sql createCustomer.sql createRental.sql insertProperty.sql insertCustomer.sql insertRental.sql You may open your files in any other editor (Notepad/Notes/Gedit) and paste the contents of those files. 5. Confirm that those tables are populated running the following queries in a new Query tab: select from Property; select from Customer; select from Rental; 6. Make sure you exit from the SQLiteStudio app to avoid that the database file has only access. Java Programs Download the zip file attached in the folder of the problem statement. Open the project in Intellij (or you can make it work in another environment by importing the different classes). Please, remember that this is a Maven project, which, as explained in class, will be in charge of obtaining all dependencies. P3Program.java 1. Create a printCustomers(title, resultSet) instance method which prints the title, a column heading, and the data for each tuple. Make certain you check for null values where appropriate and print "---". This must be coded in a manner similar to the printSections method in the MySQLandJDBC notes; however, you should indent the column headings and column data four spaces to improve readability. The output must match the output shown below. 2. Create a runProgram method which does the following: a. Test printCustomers() method Use statement to execute this select statement: b. Use printUtility() method select c.* from Customer c Use printCustomers("Beginning Customers", resultSet) to print those customers. c. Use statement.executeUpdate to insert a row into Customer which uses: • 1999 for the custNr • your name for the name • your state of birth (or TX if you were born outside of the USA) for the baseLoc your statt Use statement to execute this select statement: select m.* from Property mi Use MySqlUtility.print Utility("Beginning Properties", resultSet) to print those properties. e. • your birthdate (or use NULL if you would rather not provide it) for birthdate your birthd • your gender Note: place the executeUpdate in a special try... catch to print an error if there was a duplicate as was done in our notes Example # 14. Do NOT terminate. Show a message that it was not possible to be inserted as "Error inserting customer: " + not pos customerNumber. Use the default case, since it is possible that the numbers for MySQL could differ than the ones for SQLite. d. Display updated values . Use statement to get the customers (as was done in step a.) and printCustomers("Customers after I was added", resultSet). Create a prepareStatement which does an insert into Rental using substitution parameters for each attribute. This should be done outside the for statement of the next step. f. Use a Java for statement to iterate through the strPropertyIdMarray until a value of "END" is encountered. Set the substitution parameters as follows: Parameter 1 to 1999. Parameter 2 to strPropertyldM[i]. Assuming i is your counter variable in the for. Parameter 3 to "2019-12-14". (You will need to use String and setString, instead of Date). Parameter 4 to a totalCost using setDouble. The first rental should use 200.00. Increase the amount by 10.00 for each of the rentals (i.e., use 200, 210, 220). g. Execute the insertion using preparedStatement.executeUpdate(). Note: place the executeUpdate in a special try... catch to print an error if there was a duplicate as was done in our notes Example # 14. Do NOT terminate. Show a message that it was not possible to be inserted as "Error inserting customer: " + customerNumber. Use the default case, since it is possible that the numbers for MySQL could differ than the ones for SQLite. Create a prepareStatement to select rentals (all columns) for a custNr that is provided as a substitution parameter. Provide 1999 for the custNr parameter and use preparedStatement.executeQuery() to execute it. Print the result using MySqlUtility.printUtility("My rentals", resultSet). h. Use statement to execute a select statement which returns the propid, custNr, customer name, and totalCost for customers who rented properties that customer 1999 coacomer ameyang meny cono also rented. Do not include 1999 in the returned rows. Print them using aporanc CON MySqlUtility.printUtility("Other customers renting my properties", resultSet). i. Use statement.executeUpdate to update your rentals decreasing the totalCost by 10% (.e., set totalcost-g (i.e., set totalCost = 0.90* totalCost). totalcos j. Repeat step g, but use "My Cheaper Rentals" as the title. k. Use statement to execute a select statement which returns propld and count(*) for properties having more than two rentals. (See example # 15 in the SQL DML notes.) Prope mams mor Print the results using MySqlUtility.printUtility ("Properties Having more than 2 rentals", resultSet). 1. Delete the rentals for customer 1999 from the Rental Table using statement. m. Repeat step h, but use this for the title "Other customers renting my properties after my rentals were deleted". Of course, no results are printed. To compile your code (either through the teminal or Maven interface): mvn compile To execute your main: mvn package exec: java What to submit via BlackBoard? Submit a zip file named LastnameFirstName.zip containing: • Your P3Program.java • Your propertyDB.db Your output generated by your program. Place it in a file named p3Out.txt Do not include any directories. • Sample Partial Output: Beginning Customers CustNr Name 111 Ava Kashun 222 Cam Aruh 333 444 480 555 601 Pete Moss Bill Board Anita Vacay Perry Scope E Race State Birth Dt Gender 2000-04-01 M 1990-09-11 M ARANY TX CA FL CA TX OK OK 1992-03-03 M 1985-04-04 M 1975-06-01 F 1927-04-15 M 1986-10-01 M Rubric: 666 755 30 points Tom E Gunn Ray Mee Bob Wire T Doh 777 855 888 890 901 Peg Board 902 Al B Tross 903 B B Gunn 984 999 1999 Penny Lane Anita Break Sally Mander Marcus Absent Larry Beginning Properties Forest View propId... locDesc.... BEANSBD Friole Heaven FIREHHH HOMEJJJ MTNDDD NYCCC Times Square OILAAA Oil Slick QUAKELLL Rock N Roll RVBBB Home Alone Mountain View SAEEE SNOWFFF SNOWNNN STARIII TENTGGG Kamp Komfort VACAYKKK Griswold Paradise WESTMMM Desert View 15 Rows Water Logged Camper River Shores Snowcap Ridge Snowy View Star View M 1965-04-01 M 1973-07-07 M CA 1975-04-15 F CA 1990-08-08 F CA NY CA 1992-08-09 F 1987-04-04 F 138 333 23:EER OK 1957-07-12 M F F 1999-09-09 M TX 1957-12-04 M NY --- NY state propType rating TX CA CO CO NY TX CA FL TX CO co CO CO CO TX T T T T T T M M T T T с 1x^^*=*==³*16= с T с NULL + 5 points for performing all 6 operations in SQLite studio correctly (Database) + 5 points for showing the results of printCustomers method as explained and shown above - 2 points if nulls are not shown correctly - 1 points if the output does not match the examples shown above with the corresponding spaces. +2 points for functionality 2a including the method call + 1 points for functionality 2b correctly and as specified + 2 points for functionality 2c correctly + 1 point for functionality 2d correctly + 5 points for showing the correct inserted values following steps 2e to 2g. + 5 points for showing the correct updated values following steps 2i to 2j. + 2 points for showing the correct output of query of 2k. + 2 points for deleting and showing the correct output of 21 to 2m.
Expert Answer:
Related Book For
Fundamental Managerial Accounting Concepts
ISBN: 978-1259569197
8th edition
Authors: Thomas Edmonds, Christopher Edmonds, Bor Yi Tsay, Philip Olds
Posted Date:
Students also viewed these programming questions
-
In this Assignment you examine the target customer using your CSR Tool Belt including the life stages of the targeted customer. The better you are at understanding who the targeted customer is, the...
-
In this assignment you are going to prepare payroll accounting entries, complete payroll records, and prepare payroll tax returns for a Company that started operations on October 1 of the current...
-
In this assignment you will use the Income Statement and Balance Sheet information of Triple B Company to meet the following requirements: Part 1: Use the worksheet titled Horizontal Analysis to...
-
Calculate x (1, 3) and y (1, 3) for (x, y) = 7x + y 2 .
-
The following hypotheses are given. H0: 0 H1: > 0 A random sample of 12 paired observations indicated a correlation of .32. Can we conclude that the correlation in the population is greater than...
-
Find Io in the network shown using superposition. 2KL2 6KO 10 4mA 12V 4
-
Juliette Shulof Furs (JSF) was a New York corporation that had been in the fur-dealing business for 15 years. George Shulof, an officer of JSF, attended two auctions conducted by Finnish Fur Sales...
-
Depreciation?SYD, Act., SL, and DDB the following data relate to the Plant Assets account of Eshkol, Inc. at December 31, 2010. *In the year an asset is purchased, Eshkol, Inc. does not record any...
-
The eigenvalues, eigenvectors in Question-1 needs to be calculated by hand (not by computer). Questions-1: (9 mark) A car rental company has three locations. Every customer can rent from and return...
-
Assume you have an graph as following. Use Dijkstra Algortihm to calculate shortest path from vertex A to every vertex (manuel calculation). Write the Dijkstra code to generate this graph and find...
-
Which of the following is correct for a space X: * If A is closed, then A is not open If A is not closed, then A is open If A is not closed, then X\A is not pen If A is not open, then A is closed If...
-
During 2021, Maria Gonzalez accepted a transfer from her current job with Acme Inc. in Charlottetown, Prince Edward Island (PEI) to a new position at Acme Inc.'s offices in Toronto. Maria and her...
-
1. What are the downsides and upsides (pros and cons) of ethnocentrism? 2. What happens if more people become ethnocentric in a society? Is it good or bad for society? Explain.
-
Explain what is the situation analysis is fiba women's basketball world up 2022.
-
A particle with a mass of 2.9 10-20 kg is oscillating with simple harmonic motion with a period of 3.5 10-5 s and a maximum speed of 1.4 x 10 m/s. Calculate (a) the angular frequency and (b) the...
-
A bullet of mass m=0.8 kg having an initial speed of V1A=20 m/s strikes a block of mass m=2 kg suspended from some light wires. The bullet embeds in the block and they start moving together with an...
-
Still remember the story of China and Bangladesh discussed in class? Productivity in Bangladesh is only 28% of China's on average, but the productivity gap is closed in apparel. In apparel,...
-
Show that, given a maximum flow in a network with m edges, a minimum cut of N can be computed in O(m) time.
-
Jenkins Frames Company, which manufactures ornate frames for original art work, began operations in January 2018. Bruce Preston, the owner, asks for your assistance. He believes that he needs to...
-
Pawhuska Company estimates that its overhead costs for 2018 will be $720,000 and output in units of product will be 300,000 units. Required a. Calculate Pawhuska's predetermined overhead rate based...
-
Shim Company presents its statement of cash flows using the indirect method. The following accounts and corresponding balances were drawn from Shim's 2017 and 2016 year-end balance sheets: Account...
-
The Milham Theatre Club has been in existence for a number of years. Members pay an annual subscription of 15 which entitles them to join trips to professional productions at a reduced rate. On 1...
-
The accounting records of the Happy Tickers Sports and Social Club are in a mess. You manage to find the following information to help you prepare the accounts for the year to 31 December 2015. (1)...
-
A business both buys loose tools and also makes some itself. The following data is available concerning the years ended 31 December 2014, 2015 and 2016. You are to draw up the Loose Tools Account for...
Study smarter with the SolutionInn App