Relational and Non-Relational Databases Assignment ONE: Relational Database Design Introduction The doctor's surgery in Newtown is considering moving their appointments details from a spreadsheet based system onto a relational database. The doctor's surgery looks after the health of the town's residents who can book an appointment to meet with each resident's registered doctor. To help with the running of the surgery, they maintain a spreadsheet of the name, address, date of birth and phone number of each patient. The spreadsheet also stores information on each patient's most recent diagnosed condition, any current medication the patient is taking due to their recent condition, as well as any known allergies the patients may have. The current way in which the data are stored is shown in the table below. Note that each doctor can be registered to more than one patient and each patient can have more than one registered doctor. A patient can also be seen by a doctor that they are not registered with. The 'Known allergies' and 'Current Medication' columns are empty unless populated by the doctor. An appointment cannot exist without a patient or doctor, of course. Page 1 of 1 c. Database Schema - Decompose the design to create a schema to store the data in a suitable normal form (2NF or 3NF). Say which normal form each table in the design is in and justify your choice. List the tables and show the fields, primary keys, and any foreign keys in your schema. Describe any assumptions you have made or any additional fields you have added to your design. Make sure all the primary keys are a sensible choice and introduce new fields if needed. Draw a schema diagram to illustrate your design. d. Create Tables - Create a MySQL database to store the schema that you designed in part c. above. Create the tables using SQL statements and reproduce the code in your answer report. Define the keys and choose appropriate data types and any other restrictions you think are required. e. Insert the Data - Insert the data given above into the appropriate tables. Give a single example of an SQL statement that you used to insert one row of data into one of the tables. f. SQL Queries - Carry out the appropriate SQL queries of answering the following questions. For each query, give the SQL code and the result of running the query on the data in your database. i. Return a list of all patient names ii. Return a list of all patient addresses, showing each address only once iii. Write a query to count how many patients have Dr Jenkins as one of their registered doctors iv. Calculate the average age of all patients v. Return all the patients whose last name is 'Jones' vi. Find the names of the patients that were born before 1st January 1980 vii. List all the patients' names along with their registered doctors' names viii. List all the patients who are currently taking medication. Give the name of the patient, their current medication and the recent condition they are taking the medication for ix. List all patients, giving their name and date of birth and, if the patient has had a recent condition, provide the medication they are taking. Otherwise, if the patient has had no recent condition, return null in the current medication field