Task 2 [11 marks] using the Health Connect database For task 2, we have provided

Question

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 haven’t 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.

Explanation / Answer

Query 1:SELECT FIRSTNAME,LASTNAME,NICKNAME,JOB FROM USERS WHERE SUBURBS='STAFFORD'

QUERY2: SELECT NICKNAME,MENTOR NICKNAME FROM USERS ORDER BY NICKNAME

QUERY 4:SELECT FIRSTNAME,CITY FROM USERE WHERE POST_ID =NULL

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.