3:11 PM moodle.latech.edu AT&T; Department Number Table Name: DEPARTMENT Xxx Att

Question

3:11 PM moodle.latech.edu AT&T; Department Number Table Name: DEPARTMENT Xxx Attribute Department Name VARCHARI15 Department Number INT Manager SSN CHAR) Data Type Primary Foreign Constraint NOT NULL NOT NULL Employee (SSN) NOT NULI ON DELETE SET NULL Manage Start Date DATE Table Name: DEPT_LOCATION xxx Department Number INT Department Location VARCHAR(15) Data Type Primary Foreign Coestrain NOT NULL Department (DepNo) ON DELETE CASCADE NOT NULL Table Name: PROJECT XxX Atrnbute Project Name Project Number INT Project Location VARCHAR15) Department Number INT Data Type Primary Foreign VARCHARIS)UNIQUE NOT NULL NOT NULL Department (DepNo) Table Name: PROJECT ASSIGNMENT xxx Attribute Data Typ Primary Forcign Employee SSN CHAR9 Project Number INT Hours Employee (SSN)ON DELETE CASCADE Project (PNumber) ON DELETE CASCADE NOT NULL NOT NULL NOT NULL DECIMAL3, 1) Table Name: DEPENDENT Xxx Data Type Primary Forcig Censtraint Employee (SSN) NOT NUL ON DELETE CASCADE Employee SSN CHAR9) Dependent Name VARCHAR( 15)" Sex NOT NULL Gender CHECK DATE Relationship VARCHARIS) Hint: you have two ways to define foreign key, one is to define foreign key within the CREATE TABLE statement, for example: CREATE TABLE products (product id numeric(10 ot , supplier id numeric(10) CONSTRAINT fk supplier FOREIGN KEY (supplier id) REFERENCES supplier(supplier id) ON DELETE SET NULL) Another way is to create table without defining foreign key and add foreign key later using the ALTERTABLE statement (sometimes you might have to do like this), for example:

Explanation / Answer

CREATE TABLE Department_xxx
(
Department_Name varchar(15) NOT NULL,
Department_Number int NOT NULL,
Manager_SSN char(9) NOT NULL,
Manage_Start_Date date
PRIMARY KEY (Department_Number),
FOREIGN KEY (Manager_SSN) REFERENCES Project_Assignment_xxx

(Employee_SSN) ON DELETE SET NULL
);

CREATE TABLE Dept_Location_xxx
(
Department_Number int NOT NULL,
Department_Location varchar(15) NOT NULL,
PRIMARY KEY (Department_Number, Department_Location),
FOREIGN KEY (Department_Number) REFERENCES Department_xxx

(Department_Number) ON DELETE CASCADE
);

CREATE TABLE Project_xxx
(
Project_Name varchar(15) UNIQUE,
Project_Number int NOT NULL,
Project_Location varchar(15) NOT NULL,
Department_Number int,
PRIMARY KEY (Project_Number),
FOREIGN KEY (Department_Number) REFERENCES Department_xxx

(Department_Number)
);

CREATE TABLE Project_Assignment_xxx
(
Employee_SSN char(9) NOT NULL,
Project_Number int NOT NULL,
Hours decimal(3,1) NOT NULL,
PRIMARY KEY (Project_Number, Employee_SSN),
FOREIGN KEY (Employee_SSN) REFERENCES Department_xxx,

Dependent_xxx(Manager_SSN) ON DELETE CASCADE,
FOREIGN KEY (Project_Number) REFERENCES Project_xxx

(Project_Number) ON DELETE CASCADE
);

CREATE TABLE Dependent_xxx
(
Employee_SSN char(9) NOT NULL,
Dependent_Name varchar(15) NOT NULL,
Sex char CHECK (sex='M' OR sex='m' OR

sex='F' OR sex='f'),
Birthday date,
Realtionship varchar(8),
PRIMARY KEY (Employee_SSN, Dependent_Name),
FOREIGN KEY (Employee_SSN) REFERENCES Project_Assignment_xxx

(Manager_SSN) ON DELETE CASCADE
);

Related Questions

Navigate

• Browse (All)
• Browse 3
• Subjects

---

---

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