Health Connect relational model Users (nickname, firstName, surname, birthYear,

Question

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 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

Explanation / Answer

If you have any doubts, please give me comment...

-- 5)

SELECT I.illnessID, name, COUNT(nickname) AS no_of_users, MIN(dateStarted) firstTime, MAX(dateStarted) most_recent, AVG(degree) avg_degree

FROM Illness I, TreatmentRecords T

WHERE I.illnessID = T.illnessID

GROUP BY I.illnessID, name;

-- 6)

SELECT U.nickname, COUNT(P.postID)+COUNT(PC.postID) no_of_comments

FROM Users U, Posts P, PostComments PC

WHERE U.nickname = P.nickname AND U.nickname = PC.nickname

GROUP BY U.nickname

HAVING COUNT(P.postID)+COUNT(PC.postID)>=1;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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