7C1019: Create a Database Using Oracle SQL - IT Assignment Help

Assignment Task:

Task:

7C1019+Portfolio+
In this Task you will build a database to support the needs of The Central Hospital, England who are working with local doctors' surgeries to keep accurate data about the Coronavirus pandemic. The National Health Service (NHS) want to improve the digitization of the data files and be able to run queries easily and efficiently and report statistics to the UK government. THE BRIEF A doctor will see a patient, the consultation is recorded [for the purpose of this assessment scenario any other illness unrelated to the virus infections are excluded from the scope of the task]. Patients are tested for the virus in the case of a positive result they will be either sent to hospital, advised to quarantine and/or asked to repeat the test in 7 days time. The doctor will note symptoms, indicate higher risk patients, the diagnosis, the medication, personal patient details, testing details. Typically, symptoms could include: a new continuous cough, a high temperature, a loss of or change to sense of smell or taste. You may assume other symptoms. You may assume medication types, and could be commonly used medicine: paracetamol, cough linctus, high dose asprin, naproxen, etoricoxib, diclofenac, indomethacin. The hospital admits patients referred by family doctor surgery. The hospital doctors pick up the data from the surgeries, starting with referring surgery details, diagnosis, above patient information, PPE guidance for staff. Hospital staff will give an evaluation of the PPE, evaluation is a number from 1 to 5. The Hospital record the evaluations from all staff. The research and development (R&D) depaitnient at the hospital will use the computer database to monitor the infections, collate statistics and analyse the data. They may also run reports for the government. Produce a design for this system. This should include an ER diagram and a set of tables with suitably defined columns and constraints to support this scenario. State any assumptions made. 20 marks 
1. Create a database using Oracle SQL. Populate the tables with appropriate test data, bearing in mind the following: a. There may be some patients that have yet to be tested for Coronavirus. b. There may be some patients who have yet to see a doctor. c. All patients are uniquely identified by a primary key column d. Records of patients seeing a doctor (known as a surgery consultation) are identified by a combination of foreign keys (not by a single column surrogate key) e. There may be some patients that are yet to be given medication. Hint: In orde/ to Keep tne amount of test a ta to a minimum, 
 

1. Create a database using Oracle SQL. Populate the tables with appropriate test data, bearing in mind the following: a. There may be some patients that have yet to be tested for Coronavirus. b. There may be some patients who have yet to see a doctor. c. All patients are uniquely identified by a primary key column d. Records of patients seeing a doctor (known as a surgery consultation) are identified by a combination of foreign keys (not by a single column surrogate key) e. There may be some patients that are yet to be given medication. Hint: In order to keep the amount of test data to a minimum, we will assume that the company is unlikely to run with more than seven different surgeries and that the number of patients about which they have details is less than fifteen. Take a look at the query specifications in question 4. You will need to have data values that are relevant to these queries. BUT the design of your tables and test data should not be compromised merely to support just these queries.  
The creation of a good set of WELL DESIGNED test data that exploits all the degrees of the relationships is VERY IMPORTANT and is worth 5 marks. 
5 marks 
2. Develop a set of queries as follows: a. Find the surgery that referred the most number of patients, include their location; b. Write and test a query to list the patient name, gender, ethnicity and age of each patient who is in quarantine. Add a column that shows the average age of the patient; c. Find the location of the surgery that have an fewer high risk patients than the average number of high risk patients referred by all surgeries. Devise 3 further queries that test your design. Think about what sort of queries would be generated by doctors and R&D staff for the above scenario. Credit will be given to queries that are not trivial, for example, "SELECT * FROM Tablename" is unlikely to gain more than one mark. 
18 Marks 3. The live system will quickly contain a large amount of data. From your design, pick one table that you think will be queried regularly to generate statistical data (e.g., counts or averages) and would contain a large amount of data in a real system. a) Create a iikuluiginimaimmblisimimonstrates such as 
 

This IT Assignment has been solved by our IT Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.