Answer Happy

21; 22:23 2022 Q.3.1 Somebody already started creating the database. They have written the below code and executed it. 1 CREATE SCHEMA butterflies2022; 2 USE butterflies 2022; 3. CREATE TABLE author ( 4 authorid INT AUTO INCREMENT NOT NULL PRIMARY KEY, 5 name varchar(100), 6 lastname varchar(100) 7 (3) Q.3.1.1 Include the above SQL statements exactly as is in your SQL script. Add a comment above each statement explaining what it does. Q.3.1.2 Write an SQL statement to change the name of the column lastname in table author to be called surname and be a required field. (3) Note: You may not change the already executed script to accomplish this. Q.3.2 Write a SQL statement to create the Paper table with the foreign key constraint. (8) Q.3.3 Write a SQL statement to insert the following data: (2) 2 Table: Author Authorld Name Surname 1 Bob Smith 2 Sarah McLaglenna Q.3.4 Write a SQL statement to change the already inserted data as follows: (4) Table: Author AuthorID Name Surname 2 Sarah McLaglen END OF PAPER The Independent Institute of Education (Pty) Ltd 2022 Page 6 of 6

21, 22, 23 2022 Question 3 -SQL (Marks:20) Practical Computer Work: The answer to this question should be submitted electronically. Using MySQL, create a single Structured Query Language (SOL) script that answers all the below questions. Include comments to indicate which part of the script answers which question The script must execute correctly using Mysol to get full marks, Make use of the following Entity Relationship Diagram (ERD) and Data Dictionary: Author 1..1 1... Paper writes Authorld (PK) PaperiD (PK) AuthorID (FK) Name Surname Title Description Date Table: Author Data Field Field name Data format Req? Description Example type size Authorld (PK) int Yes 1 Autonumber primary key that uniquely identifies the author Name of the author Surname of the author Name No Bob varchar(100) varchar(100) 100 100 Surname Yes Smith Table: Paper Field Field name Data type Data format Req? Description Example size PaperID (PK) int Yes 3 AuthorID (FK) int Autonumber primary key that uniquely identifies the paper Foreign key that identifies the author of the paper The title of the paper Yes 1 Title varchar(255) 255 Yes Description varchar(255) 255 No A brief description of the paper Butterfly migration paths A study of how butterflies migrate across Africa 2020/01/06 Date date YYYY/mm/dd No The date that the paper was published The Independent Institute of Education (Pty) Ltd 2022 Page 5 a