Health Connect relational model Users (nickname, firstName, surname, birthYear,
ID: 3755029 • Letter: H
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 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 4 (2 marks) Write a query that lists the first name and city of all users that haven’t made any posts or comments.
Explanation / Answer
If you have any doubts, please give me comment..
-- 2)
SELECT nickname, mentorNickname
FROM users
WHERE mentorNickname IS NOT NULL
ORDER BY surname;
-- 4)
SELECT firstname, city
FROM Users
WHERE nickname NOT IN(
SELECT nickname
FROM PostComments
);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.