Users (nickname, firstName, surname, birthYear, city, suburb, job, TV, videoID,

Question

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 3 [3 marks]

Insert (1 mark)

Write an INSERT command to insert a new User into the Health Connect system. The users nickname is ‘stormy’, their firstname is ‘Sam’, surname is ‘Rodgers’, they were born in ‘1982’ and have not uploaded a video nor do they have a mentor.

Delete (1 marks)

Write a DELETE command to remove all rows from the phone number table where the phone number starts with ‘07’. Update (1 mark) Write an UPDATE comment to change the address of all Health Practitioners with the last name ‘Smith’ who work at ‘180 Zelda Street, Linkburb’ to ’72 Evergreen Terrace, Springfield’.

Update (1 mark)

Write an UPDATE comment to change the address of all Health Practitioners with the last name ‘Smith’ who work at ‘180 Zelda Street, Linkburb’ to ’72 Evergreen Terrace, Springfield’.

Explanation / Answer

Task 3

Insert

Insert into Posts (979, '2018-09-26 09:11:20', 'post-content');

Insert into PostAuthors values ('Stormy', 979) ;

Insert into PostComments values ('Stormy', 979, '2018-09-26 12:12:12', 'my content');

Insert into Users(nickname, firstName, surname, birthYear) values ('Stormy', 'Sam','Rodgers', 1982);

Delete

Delete from PhoneNumber where phone LIKE '07%';

Update

Update HealthPractitioners set streetNumber = 72 , street ='Evergreen Terrace' ,city = 'Springfield' where streetNumber = 180 and Street = 'Zelda Street' and suburb = 'Linkburb' and surName = 'Smith';

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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