Health Connect relational model Users (nickname, firstName, surname, birthYear,
ID: 3757303 • 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 5 [2 marks]
Health Connect have two employees, Wayne and Jake, to work with the MySQL database. Working as MySQL database administrator, provide the commands required to grant or revoke access so the following security requirements are met:
A. User Wayne must be able to add records to the USERS table (0.5 Marks)
B. User Wayne must be able to remove records from the USERS table (0.5 Marks)
C. User Jake is no longer allowed to add data to the USERS table (0.5 Marks)
D. User Jake is no longer allowed to delete records from the USERS table (0.5 Marks) Assume usernames of employees Wayne and Jake are wayne and jake respectively.
Explanation / Answer
A. User Wayne must be able to add records to the USERS table (0.5 Marks)
It is a DCL command for granting INSERT permission on USERS table for the User Wayne. This permission lets user Wayne add records to the table USERS.
GRANT command is used to add this permission.
GRANT INSERT ON USERS TO wayne;
B. User Wayne must be able to remove records from the USERS table (0.5 Marks)
It is a DCL command for granting DELETE permission on USERS table for the User Wayne. This permission lets user Wayne remove records from the table USERS.
GRANT command is used to add this permission.
GRANT DELETE ON USERS TO wayne;
C. User Jake is no longer allowed to add data to the USERS table (0.5 Marks)
It is a DCL command for revoking INSERT permission on USERS table for the User Jake. This permission lets user Jake revoke adding records from the table USERS.
REVOKE command is used to revoke adding new records permission from the user Jake.
REVOKE INSERT ON USERS FROM jake;
D. User Jake is no longer allowed to delete data records from the USERS table (0.5 Marks)
It is a DCL command for revoking DELETE permission on USERS table for the User Jake. This permission lets user Jake revoke adding records from the table USERS.
REVOKE command is used to revoke deleting new records permission from the user Jake.
REVOKE DELETE ON USERS FROM jake;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.