i need help with this database Alter the database as follows. 1 - Add a timestam
ID: 3688061 • Letter: I
Question
i need help with this database
Alter the database as follows.
1 - Add a timestamp column to the survey_responders table called "last_modified".
2 - Add a timestamp column to the survey_questions table called "last_modified".
3 - Add an index to the survey_responses table to indexes both the survey_question_id and the survey_responder_id
Alter table data as follows
4 - Create a new column on the responders table call username. It is to be a varchar(50)
5 - Create a new column on the responders table call password. It is to be a varchar(75)
6 - Populate the username field /column with the first letter of the survey responder's firstname concatenated with the last name of the survey responder.
7 - Create a function that generates a random string that is at least 8 alphanumeric characters. This function should take that random string and then apply an MD5 hash to it. (MD5() function).
8 - Update the newly created password field using the function you just created.
Create the following 3 triggers
9 - Create a trigger that will update the last_modified on the survey_responders when the record is updated.
10 - Create a trigger that will update the last_modified on the survey_questions when the record is updated.
11 - Create a trigger that will delete all database record from the survey_responses for a given survey_responder table when you delete a record from survey_responder. In other words, when I delete a survey responder (someone who answered some questions), I want to purge their responses from the database so that there are no orphan records left behind (because everyone hates orphans! Sorry to any orphans in the crowd.)
Explanation / Answer
Solution: See the answers below
1. ALTER TABLE 'survey'.`survey_responders` ADD `last_modified` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
2. ALTER TABLE 'survey'.`survey_questions` ADD `last_modified` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
3. CREATE INDEX `idx_survey_responses` ON `survey`.`survey_responses` (survey_question_id, survey_responder_id) COMMENT '' ALGORITHM DEFAULT LOCK DEFAULT
4. ALTER TABLE `survey`.`survey_responders` ADD COLUMN `username` VARCHAR(50) NULL AFTER `survey_responders_id`;
5. ALTER TABLE `survey`.`survey_responders` ADD COLUMN `password` VARCHAR(75) NULL AFTER `username`;
6. UPDATE survey_responders SET username = CONCAT(LEFT(firstname,1), lastname);
7. CREATE FUNCTION GENERATE_STR() RETURNS VARCHAR
BEGIN
DECLARE STR VARCHAR DEFAULT "";
SELECT LEFT(UUID(),8) INTO STR;
RETURN MD5(STR);
END;
8. UPDATE survey_responders SET password = GENERATE_STR();
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.