Write the SQL statements required to create the following tables, including appr

Question

Write the SQL statements required to create the following tables, including appropriate primary and foreign key constraints.

(supervisor is also an employee; employee belongs to only one department specified by dnumber; department has a manager who is also an employee, same department can be located in many locations)   

Employee (ssn, name, salary, supervisorSSN, dnumber)

Department (dnumber, dmname, managerSSN)

DeptLocation (dnumber, dlocation)

Project (pnumber, pname, plocation)

WorksOn (emplSSN, pnumber, hours)

Explanation / Answer

Hi,

Below are the SQL statements for the tables-

CREATE TABLE EMPLOYEE(SSN VARCHAR(10) PRIMARY KEY, NAME VARCHAR(20), SALARY NUMBER(10,2), SUPERVISORSSN VARCHAR(10), DNUMBER NUMBER(10) FOREIGN KEY REFERENCES DEPARTMENT(DNUMBER));

CREATE TABLE DEPARTMENT(DNUMBER NUMBER(10) PRIMARY KEY,DMNAME VARCHAR(10),MANAGERSSN VARCHAR(10) FOREIGN KEY REFERENCES EMPLOYEE(SSN));

CREATE TABLE DEPTLOCATION(DNUMBER NUMBER(10) FOREIGN KEY REFERENCES DEPARTMENT(DNUMBER), DLOCATION VARCHAR(20));

CREATE TABLE PROJECT(PNUMBER NUMBER(10) PRIMARY KEY, PNAME VARCHAR(20), PLOCATION VARCHAR(20));

CREATE TABLE WORKSON(EMPISSN VARCHAR(10) FOREIGN KEY REFERENCES PROJECT(PNUMBER), PNUMBER NUMBER(10), HOURS NUMBER(10));

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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