Health Connect relational model Users (nickname, firstName, surname, birthYear,
ID: 3757302 • 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
Task 4 [3 marks]
Create Index (1 mark) Currently the database only contains a small number of records. However, the data contained within it is expected to grow significantly in the future. Creating indexes on commonly searched columns is a way performance issues can be minimized. Write a command to create an index on postID of the Posts table.
Create view – 2 marks Write a command to create a view to list the nickname, firstname, surname and birthyear of any users that haven’t listed any illness.
Explanation / Answer
ANSWER :
-- 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
);
-- 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
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.