Task 2 [11 marks] using the Health Connect database

For task 2, we have provided you with the creation script for the Health Connect database. You must run this script in MySQL Workbench and use this database to extract the necessary information. The script is based on the following schematic:

Health Connect relational model Users (nickname, firstName, surname, birthYear, city, suburb, job, TV, videoID, mentorNickname) Posts (postID, datetime, content) PostAuthors (nickname, postID) PostComments (nickname, postID, datetime, content) Video (videoID, title, description, duration, filetype) HealthPractitioners (healthPracID, firstName, surname, streetNumber, street, suburb, city, postcode, type) PhoneNumber (phoneNumber, healthPracID) Illness (illnessID, name, description) TreatmentRecords (nickname, healthPracID, illnessID, dateStarted, degree)

FOREIGN KEYS PostAuthors (nickname) is dependent on Users (nickname) PostComments (nickname) is dependent on Users (nickname) PostAuthors (postID) is dependent on Posts (postID) PostComments (postID) is dependent on Posts (postID) Users (videoID) is dependent on Video (videoID) TreatmentRecords (healthPracID) is dependent on HealthPractitioners (healthPracID) PhoneNumber (healthPracID) is dependent on HealthPractitioners (healthPracID) TreatmentRecords (illnessID) is dependent on Illness (illnessID)

OTHER CONSTRAINTS Video (filtype) domain is [AVI, MOV, FLV, MP4, WMV] HealthPractitioners (type) domain is [Doctor, Physio, Dietician] TreatmentRecords (degree) domain is [1-5] Health Practitioners may have up to three phone numbers

Query 1 (1 mark) Write a query to list the full names, (i.e. first name and last name combined), nickname and job of users who live in Stafford Heights or Stafford. Note: you can assume these are the only suburbs starting with Stafford.

Query 2 (1 mark) Write a query to list the nicknames of all users who have a mentor, together with the nickname of their mentor. Sort the result in alphabetical order of user surname.

Query 3 (2 marks) Write a query to count how many users each health practitioner in the database treats. In your result-set, include the health practitioners ID, first name, surname and the number of users they treat. Query 4 (2 marks) Write a query that lists the first name and city of all users that havent made any posts or comments.

Query 5 (2 marks) Write a query that will produce some statistics about each illness to report to the Health Connect exec team. Your result-set should include the following: the illness ID the illness name the number of users that have reported the illness the first time someone reported the illness, the most recent report of the illness and the average degree that patients experience the illness.

Query 6 (3 marks) Write a query to produce the number of comments and posts each user has made. Your result set should include the user nickname and their total comments and posts. Only show users who have made 1 or more comments or posts.