Question

Considering the following E-R diagram, your task is to create a script file for defining relations and populating data, you can use any text editor (notepad/notepad+) to write SQL statements and save it with the file extension.sql (i.e., Test3Tsay.sql). Name the file as Test3XXX.sql (where XXX is your last name). Upload it to here. endedDate phone startedDate name address name location no salary no Employees Projects assignDate P WorksOn Scientists Technicians research hEduDegree N certifications Equipments cost no Task 1 (60 points): translate the above ER diagram to relations by writing SQL statements to capture as many of the constraints as possible. If your translation cannot capture any constraints in the ER diagram, explain why. Make sure that the data type and size for each attribute are appropriate. Task 2 (15 points): write SQL statements to add two records for each relation. You are required to come up with the content of these records. Make sure your SQL script file can be run successfully in MySQL before submitting it. If it can't be run, your grade of this assignment will be a ZERO. If your file can be run without error messages, the maximum possible points for each portion are listed below:: 50 points Define Tables Constraints that were unable to capture 10 points 15 points Populate Data

Answer 1

CREATE TABLE EMPLOYEE (NO INT PRIMARY KEY, NAME CHAR (20), PHONE VARCHAR(8), ADDRESS CHAR (25), SALARY INT(7));

INSERT INTO EMPLOYEE (111, JOY, 83684672, CROSS ROAD LONDON, 25000);

INSERT INTO EMPLOYEE (112, MARIA, 82345472, MALL ROAD LONDON, 15000);

CREATE TABLE TECHNICIANS (NO INT REFERENCES EMPLOYEE, CERTIFICATIONS(15));

INSERT INTO TECHNICIANS (112, LIBRARIAN);

INSERT INTO TECHNICIANS (111, NET);

CREATE TABLE SCIENTISTS (NO INT REFERENCES EMPLOYEE, RESEARCH CHAR (20), HEDUDEGREE CHAR (20));

INSERT INTO SCIENTISTS (111, ADHOC, PHD);

INSERT INTO SCIENTISTS (112, FORENSIC, PHD);

CREATE TABLE PROJECTS (NO CHAR (10) PRIMARY KEY, NAME CHAR (20), ENDED_DATE DATETIME, STARTED_DATE DATETIME, LOCATION CHAR (15));

INSERT INTO PROJECTS (P001, HOTEL MGMT, 22-06-2019, 10-06-2019, LONDON);

INSERT INTO PROJECTS (P002, CYBER MGMT, 25-07-2019, 11-06-2019, NEW JERSY);

CREATE TABLE WORKSON (NO INT REFERENCES EMPLOYEE, NO CHAR (10) REFERENCES PROJECTS, ASSIGNDATE DATETIME);

INSERT INTO WORKSON (111, P001, 10-05-2019);

INSERT INTO WORKSON (112, P002, 01-06-2019);

CREATE TABLE EQUIPMENTS (NO CHAR(10) REFERENCES PROJECTS, NO INT PRIMARY KEY, COST INT);

INSERT INTO EQUIPMENTS (P001, 811, 5400);

INSERT INTO EQUIPMENTS (P002, 812, 5100);