I need help with using SQL developer and java.

The requirements to do this assignment is to know SQL Developer and Java.  Attached is the homework assignment

Get Help With a similar task to - I need help with using SQL developer and java.

Login to view and/or buy answers.. or post an answer
Additional Instructions:

General Background Information In events of disaster or other emergencies, markers on maps that indicate the location of available resources such as medical center, food supplies, and water supplies may be very important in helping the local population and any aid workers helping the local population. We’ll develop a system to support a limited database of resources for the community which could, in the future, be used to display local resource maps. We can use our resource database as information for an online maps (e.g. a Google map or something similar), marking the map to show the location of available resources. Overall Assignment Your overall task is to write additional code for a Java / JDBC / Oracle application that provides support for inserting, updating and deleting humanitarian resource information of three types (Medical Center, Food and Water resources) into an Oracle database. Template Code A initial base of code is available in CS260_Assignment3T.JAR on Canvas. It consists of the following files: · Main – a main driver class · CLInterface – a command-line interface for our program, including a simple menu · ResourceTransactions – a set of overall database transactions that access an Oracle database through the DAO · DataAccessObject – a DAO class, similar to what we used in Labs 7 and 8 · HumResource – a class holding general Humanitarian Resource data, such as the name, address, phone number, etc. (see more information below) · Medical Center, Food and Water – three classes for specific added resource data, each of which extends the HumResource class (that is, we’re using inheritance to give these three specific classes both the general HumResource data and the specific data relevant to each of the three types of resources Database – Sample Data and Database Field Data Types A script file (ResourcesScript.sql) with starter data for this assignment is provided on Canvas. Run this script under SQL Developer to get this base data loaded into your database schema, and try some queries to look at data in each of the tables. Notice that for any resource (e.g. look at HRID 1000), there is one row in the general table (HumResource) and one row in one of the specific resource tables (e.g. MedicalCenter. Initial Setup and Testing Load this code into a project in your IDE, update the DAO class with your Oracle username and password, add ojdbc8.jar to your project structure/build path, and run the Main class. You can make menu choices to see how the provided command-line menu system works. Choose Insert, Medical Center, and enter any test information in to follow the prompts. Use SQL Developer to see if your information is entered into both the HumResource and MedicalCenter tables. Specific Tasks For this assignment, you will need to do the following work, in increasing order of difficulty: 1. Load the base data from the ResourcesScript.sql file through SQL Developer (if you have not already done that) a. Be sure to Commit after you run the script 2. Modify the DataAccessObject class a. Add your Oracle username and password 3. Modify the ResourceTransactions class, getNextHRID method a. For now, I’ve hardcoded this method to return the value 2000, which will work once but then fail for subsequent operations b. See the discussion below on possible ways to implement the getNextHRID method 4. Modify the CLInterface class, add code for the following eight methods a. insertWater b. insertFood c. updateMedicalCenter d. updateWater e. updateFood f. deleteMedicalCenter g. deleteWater h. deleteFood i. I’ve given you sample code for insertMedicalCenter as an example to get you started. Review this code carefully to understand how we get new data from the command-line interface, build two insert statements (one for the HumResource table, and one for the MedicalCenter table), and execute those SQL statements as an SQL transaction. You’re welcome to use this code, modify it, or develop your own solution, as long as you follow the other requirements here. j. I’ve also added template comments for the first update and first delete operation to give you some hints on a possible way to implement those statements. However, you’re welcome to find your own solutions as well. Database Structure We will use four database tables to store resource information, as follows: HumResource (HRID, HRName, HRAddressString, HRPhoneNumber, HRLatitude, HRLongitude, HRType, HRDesc, HROpenHoursString) MedicalCenter (HRID (FK), NumBeds, EmergencyRoomCapacity, NumDoctors, NumNurses) Water (HRID (FK), Num10OzBottlesAvailable, NumHalfLiterbottlesAvailable, Num5GallonJugsAvailable) Food (HRID (FK), FType, FMealsAvailable, FSpecificDesc) Database Field Definitions HumResource HRID – overall humanitarian resource id number HRName – the name of the resource (e.g. Mayo/Luther Hospital) HRAddressString – the street address as a string, for display only HRPhoneNumber – the phone number for the resource, in format DDD.DDD.DDDD (D = digit 0-9) HRLatitude – the resource geographical location latitude (e.g. 48.4427475) HRLongitude – the resource geographical location longitude (e.g. 22.9175259) HRType – one of ‘MedicalCenter’, ‘Water’ or ‘Food’, to help us find which child table to work with HRDesc – a short description of the resource, including features that may be useful to people HROpenHoursString – a list of open hours of the resource, as a string, for display only MedicalCenter HRID – primary key, and foreign key to HumResource NumBeds – the number of beds available in that medical center EmergencyRoomCapacity – the number of emergency patients that can be treated simultaneously NumDoctors – the number of doctors generally available in that medical center NumNurses – the number of nurses generally available in that medical center Water HRID – primary key, and foreign key to HumResource Num10OzBottlesAvailable – number of 10 ounce bottles of water available NumHalfLiterBottlesAvailable – number of half liter bottles of water available Num5GallonJugsAvailable – the number of 5-gallon jugs of water available Food HRID – primary key, and foreign key to HumResource FType – the type of food resource; may have one value from ‘Grocery’, ‘Distributor’, ‘Restaurant’ FMealsAvailable – an estimate of how many meals the resource currently has available for consumption FSpecificDescription – a further description of the individual food resource Relational tables vs. Java classes There is a conceptual mismatch between the relational data model and Java. Java uses inheritance to be able to store general data in one parent class, and then allow that data to be inherited down to child classes. Relational DBMSs such as Oracle usually use separate normalized tables to store general data in one table (e.g. HumResource) and specific data in another table (e.g. MedicalCenter, Food and Watner), and connects these tables through primary key-foreign key pairs as we’ve done. Though there are different approaches to deal with this mismatch, we’ll represent all four Java classes with their own table. That means that when we work with a concrete Java resource class (e.g. MedicalCenter), we will have to make SQL statements that modify both the general data table (HumResource) and the specific table (here, MedicalCenter) for that resource. Be sure you’re clear on this – every operation we implement has to change two relational tables! Getting the next available HRID value (for inserting a new resource) When we want to insert a new resource, how do we choose a value that won’t conflict with a value already used in the database? We know that HRID, as a primary key, must be unique, so we can’t have duplicate HRID values. Some options to consider: · Letting the user choose the next HRID · This DOES NOT WORK – 1000 points off if you use this approach :-). While there are a lot of values available, we can’t ever risk having the user choose a value that’s already being used. · Selecting the count of the resource rows currently in the HumResource table (e.g. there are 9 rows in the sample data I’m giving you), and adding 1 to that to generate a new HRID (which would give us 10). · This WORKS SOMETIMES, DOES NOT WORK SOMETIMES. If we have consecutive HRID values starting at 1 in the database, this works. However, the sample data has HRID values 1000 through 1008 as the HRID value for the nine rows present. Using (count of existing rows + 1) will give us 10, which works for now, but we’ll gradually generate more resources with increasing HRID values that will at some point get to 1000 and cause a conflict. Also, if we delete rows in the middle of our HRID sequence, count + 1 could clash with an existing HRID. So, we shouldn’t use this approach either. · Selecting the maximum HRID in the database, and adding 1 to that for the next HRID. · This WORKS! Well, subject to eventually running out of values for whatever data type we’re using for HRID, but that’s a much bigger set of values we have that will work. For this assignment, this is a possible approach. · Oracle also supports a feature called a sequence. A sequence makes it easier – we don’t have to get the max value and add one, but just create the sequence once in SQL Developer, and then get the next value each time we need one, as follows: · In SQL Developer, execute the following statement ONCE ONLY (and commit it): · CREATE SEQUENCE A3Sequence START WITH x INCREMENT BY y; · x is the first value you want to use, so for our purposes it should be a value > 1008. · y is the increment we change by each time, normally 1. In fact, if you leave off the INCREMENT BY y clause, the sequence defaults to an increment of 1. Works for us… · In your Java program, every time you need a value from the sequence, execute the following SQL statement: · SELECT MySequence.NEXTVAL FROM DUAL · NEXTVAL is a pseudovalue (like NULL or SYSDATE) that give us the next value · DUAL is a “dummy” table in Oracle that you can use when you are not referring to a specific table. Here, we’re just wanting to execute the sequence NEXTVAL more like a function, so we use DUAL to fill out our SQL SELECT statement. · This WORKS! It’s probably what most Oracle programmers would do, though sequence is not a standard SQL feature, and may not work with other DBMSs. · SUMMARY: you can either use the SELECT MAX(HRID) and add 1 approach, or the sequence approach, making sure that you start the sequence after any existing values. Interface – Text-Based or GUI The insert, update and delete functionality can be done through a console / text-based interface, and I’ve given you a class (CLInterface) that gives you a menu-based system that you can work with if you want. A simple console-based read method (readEntry) was shown in the TestJDBC.java.txt file from Canvas (mentioned in the lecture notes and discussed in lecture), and this method is included in the CLInterface class. This functionality can be used to read in a data value for a given field from the keyboard. You are welcome, but not required, to construct your data reading method or methods as well, using Scanner or other Java classes. Switching to a GUI interface is certainly allowable for those students familiar with Java GUI construction. If you make a GUI, you’re welcome to use any or all of the initial code base I’ve given you. I recommend you only switch to a GUI after you have all of the basic work completed. Required Operations and Classes Whether you use the given CLI or make a GUI, your application must support data entry for any of the three resource types: medical center, water, or food. It must also support each of the three “write” operations for each resource type; that is, inserting a given resource, updating a resource, and deleting a resource. It must also use a DAO class for the JDBC operations; that is, there should be no JDBC calls in any class except the DAO class. Constraints on the Required Operations UPDATE There are many possible ways we could allow updates of information: one field in a given resource, all fields in a given resource, one field in multiple resources that meet some criteria, etc. These possibilities get too complicated for this assignment and the time we have. So, for this assignment, it’s only required that you implement one update operation for each type of resource (Medical Center, Water, or Food. Specifically, you must allow the user to choose a particular resource instance by HRID, then display the current field values in order, one at a time, to allow the user to change each field in turn or continue to use the current values, and finally to update that instance by updating all fields (or, more difficult, update only the changed fields). There are two fields that we won’t allow to change: 1) the HRID, as primary key values shouldn’t change, and 2) HRType, as it’s too complicated for this assignment to allow the resource type to change, which would then require us to get information for different fields. DELETE For delete, you only have to allow delete of a single instance at a time, based on the HRID. You do NOT have to support delete for arbitrary WHERE conditions (e.g. all Medical Centers, or all resources with an HRID greater than 100). Generating SQL Strings Remember that the SQL strings you execute do not have to be hard coded strings (e.g. SELECT * FROM Account, or the INSERT statements you generated in lab 7). As we discussed in lecture, you can build your own SQL strings using either the string concatenation operator (+) or other classes such as StringBuilder, combining SQL phrases (for example “INSERT INTO Account VALUES (“ as the first part of an SQL insert statement) with variable values that you get from input. You’ll need to do this for each of the eight methods you’ll need to complete. Be sure that strings that you send to Oracle are executable, for example that any string literals are enclosed in single quote marks. It's worth manually checking the strings you build for correctness. Remember that SQL strings executed through JDBC must NOT have an ending semicolon. All Work Done As Transactions Each of your operations (e.g. Inserting a Food resource) should be done as a transaction. Each operation should modify data consistently in both the general HumResource table and in the appropriate child table (MedicalCenter, Water, or Food). Testing · You can also test your insert, update and delete functionality through queries in SQL Developer. ASSIGNMENT SUBMISSION: 1. Generate a JAR (in Eclipse) or a ZIP (in IntelliJ IDEA) files for your application, making sure that your source code for all Java files that are part of your application is included. a. In Eclipse, Export your project to create a JAR file (right-click on your project, choose Export, choose Java JAR file, making sure that you select the option that source code files and resources are added. Points will be deducted if I must ask you for a resubmission because source files were not added. b. In IntelliJ IDEA, Export your project as a ZIP file (File/Export to ZIP file). This should create a folder of all of your project files, including all source code. c. By the deadline of Friday, May 8th, 2020, at 5:00 PM, submit your JAR or ZIP file or files to Canvas. There will be no extended submission time – this is an absolute deadline.

Related Questions

Similar orders to I need help with using SQL developer and java.
30
Views
0
Answers
Advance topics in machine learning (Kernels) 2 questions.
Any 2/3 Questions need answering on Advance topics in machine learning (Kernels). relevant notes can be shared upon request....
17
Views
0
Answers
Using R programming to perform functions and do work.
The expectations are to use R programming to answer the questions attached the file, and the result is also to be a .r file....
13
Views
0
Answers
write up a report using c programming.
Using the knowledge acquired from this module write a descriptive report to solve the programming problems listed below. The word count limit is 500 words You must include the full code implementation as appendix! I recommend you use Courier New size 1...
16
Views
0
Answers
Analyse requirements and select appropriate solutions. Design programmes that use appropriate data structures
The assignment requires you to select and implement appropriate data structures, design and implement algorithms and create the relevant software applications that will allow a user to store, update and manipulate the data relating to the operations of an...