NEED HELP CREATING SQL TABLES Use the following three tables for questions 1- 12

Question

NEED HELP CREATING SQL TABLES

Use the following three tables for questions 1- 12:

EMPLOYEE (EmpNumber, Name, Email)

PROJECT              (ProjectName, Description, StartDate, EndDate)

ASSIGNMENT (EmpNumber, ProjectName, TotalHoursWorked)

Assume that the relationship from EMPLOYEE to ASSIGNMENT is 1:N, M-O and that the relationship from PROJECT to ASSIGNMENT is 1:N, M-O.

4.            Write an ALTER statement to add the column Phone to EMPLOYEE. Assume that Phone is not required.

5.            Write an ALTER statement to remove the column Email from EMPLOYEE.

6.            Write an ALTER statement to make Phone an alternate key in EMPLOYEE (alternate keys are unique).

7.            Write an ALTER statement to drop the constraint that Names be unique in EMPLOYEE.

Explanation / Answer

-- oracle database

create table ASSIGNMENT
(EmpNumber   NUMBER,
ProjectName   VARCHAR2(500),
TotalHoursWorked NUMBER DEFAULT 3,
CONSTRAINT pk_assignment PRIMARY KEY (EmpNumber, ProjectName),
CONSTRAINT uk_EmpNumber UNIQUE(EmpNumber),
CONSTRAINT uk_ProjectName UNIQUE(ProjectName)
);

create table EMPLOYEE
(EmpNumber   NUMBER,
Name       VARCHAR2(500),
Email       VARCHAR2(500),
CONSTRAINT pk_EmpNumber PRIMARY KEY (EmpNumber),
CONSTRAINT uk_Name UNIQUE(Name),
CONSTRAINT fk_EmpNumber FOREIGN KEY (EmpNumber)
     REFERENCES ASSIGNMENT (EmpNumber));

create table PROJECT
(ProjectName   VARCHAR2(500),
Description   VARCHAR2(4000),
StartDate   DATE,
EndDate   DATE,
CONSTRAINT pk_ProjectName PRIMARY KEY (ProjectName),
CONSTRAINT fk_ProjectName FOREIGN KEY (ProjectName)
     REFERENCES ASSIGNMENT (ProjectName) ON DELETE CASCADE);

-- Write an ALTER statement to add the column Phone to EMPLOYEE. Assume that Phone is not required.
ALTER TABLE EMPLOYEE add Phone VARCHAR2(11);

-- Write an ALTER statement to remove the column Email from EMPLOYEE.
ALTER TABLE EMPLOYEE drop (email);

-- Write an ALTER statement to make Phone an alternate key in EMPLOYEE (alternate keys are unique).
ALTER TABLE EMPLOYEE ADD CONSTRAINT phone_unique UNIQUE (phone);

-- Write an ALTER statement to drop the constraint that Names be unique in EMPLOYEE.
ALTER TABLE EMPLOYEE drop CONSTRAINT uk_Name;

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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