CSCI 411 /511 Projeet 1 Individual Assignment Due: February 20, 2019 Points: 40 Objestive: For this assignment, you will use SQL to create a number of relational tables, insert entries into the tables and perform a series of queries on the resulting relational tables. Requirements Create: Convert the ER diagram on the last page into a set of schemas. Create the corresponding tables. Note that the ER diagram is similar to the one you hould have created for Homework I, Problem A. Except for two cases, you should have a table for each entity set or relationship set in the diagram. 1 number of tables should be 12 2. Insert: Insert the following information into the appropriate tables. (Data is meant to depict fictitious situations). Note that the data in the following lists may have to be put into more than one table; that is, the lists do not always correspond to one table The following are the addresses (cid, streetAddress, city, state): 10234, 342 streetA, Monticello, MN 344 streetA, Monticello, MN 11567, 65 streetS, St. Cloud, MN 3289, 892 streetM, Minneapolis, MN 17645, 342 streetB, Monticello, MN 88331, 342 streetC, Monticello, MN 7760, 60 streetG, St. Paul, MN 7761, 6 streetA, St. Paul, MN 98760, 798 StreetE, Duluth, MN 98761, 999 StreetG, Duluth, MN 98762, 979 StreetO, Hibbing, MN The following is information about consumers who are persons (cid, name, creditCard): 10234, Able, 102341 11567, Baker, 115671 3289, George, 32891 17645, Mary, 176451 88331, Alice, 883311 CSCI 411 /511 Spring 2019 The following is information about consumers who are retail stores (cid, name, type) 7760, ABC-Grocery, grocery 7761, MyGrocery, grocery The following is information about consumers who are utilities (cid, name, product) 98760, ElectricCo, electricity 98761, GasCo, gas 98762, 0ilCo, oil The following are the firewood sales (cid of seller, cid of buyer, fid, quantity, type, price, date): 7760, 17645, 11, 6, maple, 15.00, 01-dec-18 7760, 10234, 12, 4, birch, 12.00, 02-dec-18 7761, 17645, 13, 2, maple, 5.00, 04-dec-18 7760, 3289, 14, 3, oak, 12.00, 08-dec-18 7760, 17645, 15, 3, maple, 8.00, 08-dec-18 7760, 88331, 17, 1, oak, 4.00, 08-dec-18 7761, 98760, 18, 5, maple, 12.50, 31-dec-18 The following are the bills that have been sent (streetAddress, city, state, accountNum, month, units, amountDue): 342 streetA, Monticello, MN, B03, December, 100, 100.00 344 streetA, Monticello, MN, B02, December, 250, 250.00 342 streetA, Monticello, MN, B03, November, 50, 50.00 342 streetB, Monticello, MN, B04, December, 7500, 75.00 65 streetS, St. Cloud, MN, BOS, November, 10000.00, 100.00 979 StreetO, Hibbing. MN, B06, December, 8, 800.00 The following are the bills that have been paid (cid of consumer, accountNum, month, cid of utility): 10234, BO2, December, 98760 10234, B03, December, 98760 17645, B04, December, 98760 11567, BO5, November, 98761 The following is the history of energy deliveries by Utilities (cid of utility streetAddress, city, state, hid, date, used): 98760, 342 streetA, Monticello, MN, H04, 08-dec-18, 50 98760, 342 streetA, Monticello, MN, H03, 18-dec-18, 50 98760, 344 streetA, Monticello, MN, H02, 10-dec-18, 100 98760, 344 streetA, Monticello, MN, H05, 20-dec-18, 150 98761 , 342 streetA. Monticello, MN, HO I , 24. jan. 19, 50 98761, 342 streetB, Monticello, MN, H06, 30-dec-18, 7500 98761, 65 streetS, St. Cloud, MN, H00, 1-jan-19, 10000.00 98762, 979 StreetO, Hibbing, MN, H07, 14-dec-18, 8 CSC 41 1 / 511 Spring 2019 Bills are Based On the related history (streetAddress, city, state, accountNum, month, hid): 342 streetA, Monticello, MN, B03, December, HO4 342 streetA, Monticello, MN, B03, December, HO3 344 streetA, Monticello, MN, B02, December, H02 344 streetA, Monticello, MN, B02, December, HO5 342 streetA, Monticello, MN, B03, November, HO1 342 streetB, Monticello, MN, B04, December, H06 65 streetS, St. Cloud, MN, BO5, November, H00 979 StreetO, Hibbing, MN, B06, December, H07 3. Display: Display the contents of the tables that you created in the previous step. 4. Required Queries: Perform the following queries using SQL a. Find the total amount of gas delivered during the month of January. b. Find the total monetary value of the firewood bought by each person. People must be listed even if they did not buy firewood. List cid, name and total monetary value of the firewood. Sort the result by cid. Find the names of the utility companies that do not deliver energy to Baker's address Find the addresses that are not associated with any consumer. List streetAddress, city and state. Sort the list by city and state. Of the bills that have been paid people, find the person(s) who paid the largest amountDue on a single bill. List the person name, account number and month, and the amount due. c. d. e. f. List the unpaid bills (those that have been sent but not paid). g List the retail stores, by specifying cid and name, that have sold firewood to the utilities that do not sell gas. Find the total value of all energy purchases (all utility bills paid plus firewood purchases) for each person who had both utility bills and firewood purchases. List cid and total value for each such person. h. In addition to the SQL commands and printed output from the execution of these queries, you are to justify the results in writing. For each query, the command, result and justification are to be listed adjacent to each other. Display of duplicate tuples must be eliminated. Your queries must be capable of producing correct results for ANY instance of the tables CSCI 4111511 Spring 2019 fid units cid Based On Address At city Delivers iSA History Person Utility Retail Store hid creditCard date Project 1, Spring 2019 CSCI 411 /511 Projeet 1 Individual Assignment Due: February 20, 2019 Points: 40 Objestive: For this assignment, you will use SQL to create a number of relational tables, insert entries into the tables and perform a series of queries on the resulting relational tables. Requirements Create: Convert the ER diagram on the last page into a set of schemas. Create the corresponding tables. Note that the ER diagram is similar to the one you hould have created for Homework I, Problem A. Except for two cases, you should have a table for each entity set or relationship set in the diagram. 1 number of tables should be 12 2. Insert: Insert the following information into the appropriate tables. (Data is meant to depict fictitious situations). Note that the data in the following lists may have to be put into more than one table; that is, the lists do not always correspond to one table The following are the addresses (cid, streetAddress, city, state): 10234, 342 streetA, Monticello, MN 344 streetA, Monticello, MN 11567, 65 streetS, St. Cloud, MN 3289, 892 streetM, Minneapolis, MN 17645, 342 streetB, Monticello, MN 88331, 342 streetC, Monticello, MN 7760, 60 streetG, St. Paul, MN 7761, 6 streetA, St. Paul, MN 98760, 798 StreetE, Duluth, MN 98761, 999 StreetG, Duluth, MN 98762, 979 StreetO, Hibbing, MN The following is information about consumers who are persons (cid, name, creditCard): 10234, Able, 102341 11567, Baker, 115671 3289, George, 32891 17645, Mary, 176451 88331, Alice, 883311 CSCI 411 /511 Spring 2019 The following is information about consumers who are retail stores (cid, name, type) 7760, ABC-Grocery, grocery 7761, MyGrocery, grocery The following is information about consumers who are utilities (cid, name, product) 98760, ElectricCo, electricity 98761, GasCo, gas 98762, 0ilCo, oil The following are the firewood sales (cid of seller, cid of buyer, fid, quantity, type, price, date): 7760, 17645, 11, 6, maple, 15.00, 01-dec-18 7760, 10234, 12, 4, birch, 12.00, 02-dec-18 7761, 17645, 13, 2, maple, 5.00, 04-dec-18 7760, 3289, 14, 3, oak, 12.00, 08-dec-18 7760, 17645, 15, 3, maple, 8.00, 08-dec-18 7760, 88331, 17, 1, oak, 4.00, 08-dec-18 7761, 98760, 18, 5, maple, 12.50, 31-dec-18 The following are the bills that have been sent (streetAddress, city, state, accountNum, month, units, amountDue): 342 streetA, Monticello, MN, B03, December, 100, 100.00 344 streetA, Monticello, MN, B02, December, 250, 250.00 342 streetA, Monticello, MN, B03, November, 50, 50.00 342 streetB, Monticello, MN, B04, December, 7500, 75.00 65 streetS, St. Cloud, MN, BOS, November, 10000.00, 100.00 979 StreetO, Hibbing. MN, B06, December, 8, 800.00 The following are the bills that have been paid (cid of consumer, accountNum, month, cid of utility): 10234, BO2, December, 98760 10234, B03, December, 98760 17645, B04, December, 98760 11567, BO5, November, 98761 The following is the history of energy deliveries by Utilities (cid of utility streetAddress, city, state, hid, date, used): 98760, 342 streetA, Monticello, MN, H04, 08-dec-18, 50 98760, 342 streetA, Monticello, MN, H03, 18-dec-18, 50 98760, 344 streetA, Monticello, MN, H02, 10-dec-18, 100 98760, 344 streetA, Monticello, MN, H05, 20-dec-18, 150 98761 , 342 streetA. Monticello, MN, HO I , 24. jan. 19, 50 98761, 342 streetB, Monticello, MN, H06, 30-dec-18, 7500 98761, 65 streetS, St. Cloud, MN, H00, 1-jan-19, 10000.00 98762, 979 StreetO, Hibbing, MN, H07, 14-dec-18, 8 CSC 41 1 / 511 Spring 2019 Bills are Based On the related history (streetAddress, city, state, accountNum, month, hid): 342 streetA, Monticello, MN, B03, December, HO4 342 streetA, Monticello, MN, B03, December, HO3 344 streetA, Monticello, MN, B02, December, H02 344 streetA, Monticello, MN, B02, December, HO5 342 streetA, Monticello, MN, B03, November, HO1 342 streetB, Monticello, MN, B04, December, H06 65 streetS, St. Cloud, MN, BO5, November, H00 979 StreetO, Hibbing, MN, B06, December, H07 3. Display: Display the contents of the tables that you created in the previous step. 4. Required Queries: Perform the following queries using SQL a. Find the total amount of gas delivered during the month of January. b. Find the total monetary value of the firewood bought by each person. People must be listed even if they did not buy firewood. List cid, name and total monetary value of the firewood. Sort the result by cid. Find the names of the utility companies that do not deliver energy to Baker's address Find the addresses that are not associated with any consumer. List streetAddress, city and state. Sort the list by city and state. Of the bills that have been paid people, find the person(s) who paid the largest amountDue on a single bill. List the person name, account number and month, and the amount due. c. d. e. f. List the unpaid bills (those that have been sent but not paid). g List the retail stores, by specifying cid and name, that have sold firewood to the utilities that do not sell gas. Find the total value of all energy purchases (all utility bills paid plus firewood purchases) for each person who had both utility bills and firewood purchases. List cid and total value for each such person. h. In addition to the SQL commands and printed output from the execution of these queries, you are to justify the results in writing. For each query, the command, result and justification are to be listed adjacent to each other. Display of duplicate tuples must be eliminated. Your queries must be capable of producing correct results for ANY instance of the tables CSCI 4111511 Spring 2019 fid units cid Based On Address At city Delivers iSA History Person Utility Retail Store hid creditCard date Project 1, Spring 2019