Question 1 (Marks: 60) This question relates to creating and altering tables and implementing constraints and...

Transcribed Image Text:

Question 1 (Marks: 60) This question relates to creating and altering tables and implementing constraints and referential integrity. Answer all the questions below by creating the necessary script. Q.1.1 You are required to create the following tables in a database named (20) StudentNumber_DATA6212_ExamQ1: EMPLOYEES EMPLOYEE ID EMPLOYEE NAME EMPLOYEE SURNAME DATE OF BIRTH DOCTORS DOCTOR_ID DOCTOR NAME EMPLOYEE SICKLEAVE EMPLOYEE ID START DATE DOCTOR ID VARCHAR(S) NOT NULL VARCHAR(30) NOT NULL VARCHAR(30) NOT NULL DATE NOT NULL VARCHAR(5) NOT NULL VARCHAR(30) NOT NULL VARCHAR(5) NOT NULL DATE NOT NULL VARCHAR(S) NOT NULL NUMBER OF DAYS SMALLINT NOT NULL PRIMARY KEY PRIMARY KEY PRIMARY KEY FOREIGN KEY REFERENCES EMPLOYEES EMPLOYEE_ID) PRIMARY KEY Q.1.2 Populate the tables created in Q.1.1 with the following data: EMPLOYEES EMPLOYEE ID 10001 10002 10003 10004 10005 DOCTORS DOCTOR_ID D0001 D0002 D0003 D0004 DO005 10001 10002 10003 EMPLOYEE_NAME EMPLOYEE DATE OF BIRTH SURNAME Woolridge Baird 0003 10001 10005 Dominique Nico Derek Neo Andrew EMPLOYEE SICKLEAVE EMPLOYEE ID DOCTOR_ID START DATE DOCTOR_NAME Thabo Ntlali Deon Coetzee Kwezi Mbete Trevor January Julia Robins D0004 Moore D0001 Petlele Crouch 00003 D0002 D0001 00001 2021-01-25 2021-05-14 2021-06-07 2021 06 29 2021 08-01 2021 10 22 1993-04-19 1991-11-19 1992-06-24 1993-12-29 1994-01-30 NUMBER OF DAYS 2 1 5 3 Question 1 (Marks: 60) This question relates to creating and altering tables and implementing constraints and referential integrity. Answer all the questions below by creating the necessary script. Q.1.1 You are required to create the following tables in a database named (20) StudentNumber_DATA6212_ExamQ1: EMPLOYEES EMPLOYEE ID EMPLOYEE NAME EMPLOYEE SURNAME DATE OF BIRTH DOCTORS DOCTOR_ID DOCTOR NAME EMPLOYEE SICKLEAVE EMPLOYEE ID START DATE DOCTOR ID VARCHAR(S) NOT NULL VARCHAR(30) NOT NULL VARCHAR(30) NOT NULL DATE NOT NULL VARCHAR(5) NOT NULL VARCHAR(30) NOT NULL VARCHAR(5) NOT NULL DATE NOT NULL VARCHAR(S) NOT NULL NUMBER OF DAYS SMALLINT NOT NULL PRIMARY KEY PRIMARY KEY PRIMARY KEY FOREIGN KEY REFERENCES EMPLOYEES EMPLOYEE_ID) PRIMARY KEY Q.1.2 Populate the tables created in Q.1.1 with the following data: EMPLOYEES EMPLOYEE ID 10001 10002 10003 10004 10005 DOCTORS DOCTOR_ID D0001 D0002 D0003 D0004 DO005 10001 10002 10003 EMPLOYEE_NAME EMPLOYEE DATE OF BIRTH SURNAME Woolridge Baird 0003 10001 10005 Dominique Nico Derek Neo Andrew EMPLOYEE SICKLEAVE EMPLOYEE ID DOCTOR_ID START DATE DOCTOR_NAME Thabo Ntlali Deon Coetzee Kwezi Mbete Trevor January Julia Robins D0004 Moore D0001 Petlele Crouch 00003 D0002 D0001 00001 2021-01-25 2021-05-14 2021-06-07 2021 06 29 2021 08-01 2021 10 22 1993-04-19 1991-11-19 1992-06-24 1993-12-29 1994-01-30 NUMBER OF DAYS 2 1 5 3

Answer rating: 100% (QA)

The questions in the images involve creating database tables and populating them with data Below I have provided SQL code snippets that create the spe... View the full answer