Database SQL work. Please do task#3 from this as soon as possible within an hour.
Make it basic.
Problem domain: You are developing a database system for the Bus Rent Company. The Bus Rent Company has some data on the buses they offer, and general data on income which stores how much revenue each bus generates per month. Event Organizers often must get data from the Bus Rent Company. They use a different information system than the Bus Rent Company. During requirement engineering process, the test data was gathered, the requirements were defined, and ER diagram was designed (note that this is not pure database ER diagram, it is missing some key information as this ER was acquired during requirements engineering, not design stage). Bus Income Seat number Registration number(template LV-2000) Fuel consumption Necessary drivers licence type (Dor D1) comfort level (low, medium or high) Month Year Income Fig. 1 ER diagram Seat number Registration Mumber Drivers licence type Comfort level Bus test data: Fuel consumption (per kon 0.2 0.35 0.37 16 35 31 LV-7612 LV-7124 LV-2431 DI D D high medium high Bus LV-7612 LV-7124 Income test data: Year Monck 2020 December 2020 December Income 3000 5000 Monch Route Price route 2021 Information on trips must be stored so that it would be easily accessible altogether. Each bus can have unlimited number of trips. Trip test data (all of them for bus "LV-2431") Year Approx. length of January 400 km Riga - Liepaja - Riga February 2021 50 km Riga-Olaine-Riga 3. The income of the month and year must be updated for that particular bus automatically when the price is added (either when inserting the trip, or later, when adding price). 4. The following information must be obtained from the database: a. the list of the future events of particular bus; b. all the buses that have high comfort level; c. there should be possibility to get all the income (from all the buses) for each month. 5. The Bus Rent Company workers should be able to calculate the approximate price of any trip by multiplying the bus fuel consumption and 1.1 EUR (price of diesel), and length of the trip. 6. The Event Organizers must be able to connect to the database by using their system and retrieve the data specified in Requirement 1. 7. The Event Organizers should be able to use functionality described in requirement 5 (input vehicle registration number and route length, output: approximate price). 8. There should be possibility to export all the income data to format that is usable to automatically format reports and import the past documents. Task 1: Supplement correct/improve the ER diagram with objects and object storage structures. Explain, which objects and why you would store in such a way! Note that the requirements contain clues on which information should be stored as objects! Depending on your design there would be two to three data structures changed or added. Task 2: Implement your improved design! SQL Queries required, including test data insertion into database! Make necessary adjustments for the test data. Task 3: Implement the necessary database programming and data retrieval. You can choose to (a) use triggers (b) use object member methods (c) use PL/SQL programs (d) use queries. In total, you should have 5 queries programs methods. Please note, to gain maximum points you should use different methods (.e., all the functionalities should not be implemented as simple SQL queries). Task 4: Implement necessary connection to database from the Java application. Write the query to export data in the necessary format. Explain, in what form and how documents can be imported in the database! Problem domain: You are developing a database system for the Bus Rent Company. The Bus Rent Company has some data on the buses they offer, and general data on income which stores how much revenue each bus generates per month. Event Organizers often must get data from the Bus Rent Company. They use a different information system than the Bus Rent Company. During requirement engineering process, the test data was gathered, the requirements were defined, and ER diagram was designed (note that this is not pure database ER diagram, it is missing some key information as this ER was acquired during requirements engineering, not design stage). Bus Income Seat number Registration number(template LV-2000) Fuel consumption Necessary drivers licence type (Dor D1) comfort level (low, medium or high) Month Year Income Fig. 1 ER diagram Seat number Registration Mumber Drivers licence type Comfort level Bus test data: Fuel consumption (per kon 0.2 0.35 0.37 16 35 31 LV-7612 LV-7124 LV-2431 DI D D high medium high Bus LV-7612 LV-7124 Income test data: Year Monck 2020 December 2020 December Income 3000 5000 Monch Route Price route 2021 Information on trips must be stored so that it would be easily accessible altogether. Each bus can have unlimited number of trips. Trip test data (all of them for bus "LV-2431") Year Approx. length of January 400 km Riga - Liepaja - Riga February 2021 50 km Riga-Olaine-Riga 3. The income of the month and year must be updated for that particular bus automatically when the price is added (either when inserting the trip, or later, when adding price). 4. The following information must be obtained from the database: a. the list of the future events of particular bus; b. all the buses that have high comfort level; c. there should be possibility to get all the income (from all the buses) for each month. 5. The Bus Rent Company workers should be able to calculate the approximate price of any trip by multiplying the bus fuel consumption and 1.1 EUR (price of diesel), and length of the trip. 6. The Event Organizers must be able to connect to the database by using their system and retrieve the data specified in Requirement 1. 7. The Event Organizers should be able to use functionality described in requirement 5 (input vehicle registration number and route length, output: approximate price). 8. There should be possibility to export all the income data to format that is usable to automatically format reports and import the past documents. Task 1: Supplement correct/improve the ER diagram with objects and object storage structures. Explain, which objects and why you would store in such a way! Note that the requirements contain clues on which information should be stored as objects! Depending on your design there would be two to three data structures changed or added. Task 2: Implement your improved design! SQL Queries required, including test data insertion into database! Make necessary adjustments for the test data. Task 3: Implement the necessary database programming and data retrieval. You can choose to (a) use triggers (b) use object member methods (c) use PL/SQL programs (d) use queries. In total, you should have 5 queries programs methods. Please note, to gain maximum points you should use different methods (.e., all the functionalities should not be implemented as simple SQL queries). Task 4: Implement necessary connection to database from the Java application. Write the query to export data in the necessary format. Explain, in what form and how documents can be imported in the database
