Considering the following E-R diagram your task is to write SQL statements to cr

Question

Considering the following E-R diagram your task is to write SQL statements to create the corresponding relations and capture as many of the constraints as possible. If cannot capture some constraints, explain why. Make sure that the data type and size for each attribute are appropriate. For example, a numerical data type is better choice than a string data type for the attribute salary. Please put your SOL statements into a SQL script file. Make sure your SOL script file can be run successfully in M L 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: Create Tables 90 points Constraints 10 points Name Name lev skills name address Jobs SSn. bDate hone name age Employees WorksAt Divisions relationship Dependents dent FullTime Emp PartTime Emp hr Rate wor

Explanation / Answer

CREATE TABLE EMPLOYEES(
ID INT NOT NULL,
SSN VARCHAR (20) NOT NULL,
fName VARCHAR(50),
lName VARCHAR(50),
skills varchar(200),
bDate datetime,      
age INT ,
JOB_TITLE VARCHAR(50),
DIVISION_NAME VARCHAR(20),   
PRIMARY KEY (ID),
FOREIGN KEY (JOB_TITLE) REFERENCES JOBS(TITLE),
FOREIGN KEY (DIVISION_NAME) REFERENCES DIVISIONS(NAME)
);

CREATE TABLE JOBS(
   TITLE VARCHAR(50) NOT NULL,
   LEVEL INT,
   PRIMARY KEY (TITLE)
);

CREATE TABLE DIVISIONS(
   NAME VARCHAR(20) NOT NULL,
   ADDRESS VARCHAR(200),
   PHONE VARCHAR(12),
   PRIMARY KEY (NAME)
);

CREATE TABLE DEPENDENTS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   RELATIONSHIP VARCHAR(20) NOT NULL,
   EMPLOYEE_ID INT,
   PRIMARY KEY (ID),
   FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES(ID)
);

CREATE TABLE FULLTIMEEMP(
   ID INT,
   ANNUALSALARY INT,
   EMPLOYEE_ID INT,
   PRIMARY KEY (ID),
   FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES(ID)
);

CREATE TABLE PARTTIMEEMP(
   ID INT,
   HRRATE INT,
   WORKHOURS INT,
   EMPLOYEE_ID INT,
   PRIMARY KEY (ID),
   FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES(ID)
);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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