Database Processing - 12th edition, by Kroenke and Auer 1 answer below » Complet

Question

Database Processing - 12th edition, by Kroenke and Auer 1 answer below » Complete the task described in review questions 7.4 - 7.7 on pages 299-301 of your textbook. Make sure you read through and follow the instructions preceding the actual questions. (The instructions begin in red on page 299.) This assignment consists of handing in four (4) complete CREATE TABLE statements. 7.4 - write a CREATE TABLE statement for the Department Table 7.5 Write a Create TABLE statement for the Employee table, email is required and is an alternate key, and the default value of Department is Human REsources. Cascade updates by not deletions from DEPARTMENT to EMPLOYEE. 7.6 - Write a CREATE TABLE statement for PROJECT table. The default value for MaxHours is 100. Cascade updates but not deletions from DEPARTMENT to EMPLOYEE 7. 7 - Write a CREATE TABLE statement for the ASSIGNMENT table. Cascade only deletions from PROJECT to ASSIGNMENT; do not cascade either deletions or updates from EMPLOYEE to ASSIGNMENT.

Explanation / Answer

SOLUTION 7.4 Query:

CREATE TABLE DEPARTMENT
(
DepartmentName char(35) NOT NULL PRIMARY KEY,
BudgetCode char(30) NOT NULL,
OfficeNumber char(15) NOT NULL,
Phone char(12) NOT NULL
);

SOLUTION 7.5 Query:

CREATE TABLE EMPLOYEE
(
EmployeeNumber int NOT NULL IDENTITY(1,1) PRIMARY KEY,
FirstName char(25) NOT NULL,
LastName char(25) NOT NULL,
Department char(35) NOT NULL DEFAULT 'Human Resources' FOREIGN KEY REFERENCES DEPARTMENT(DepartmentName) ON UPDATE CASCADE,
Phone char(12) NOT NULL,
Email char(100) NOT NULL
);

SOLUTION 7.6 Query:

CREATE TABLE PROJECT
(
ProjectID int NOT NULL IDENTITY(1000,100) PRIMARY KEY,
Name char(50) NOT NULL,
Department char(35) NOT NULL FOREIGN KEY REFERENCES DEPARTMENT(DepartmentName),
MaxHours real NOT NULL DEFAULT 100,
StartDate datetime,
EndDate datetime
);

SOLUTION 7.7 Query:

CREATE TABLE ASSIGNMENT
(
ProjectID int NOT NULL,
EmployeeNumber int NOT NULL,
HoursWorked real,
PRIMARY KEY (ProjectID, EmployeeNumber),
FOREIGN KEY (ProjectID) REFERENCES PROJECT(ProjectID) ON DELETE CASCADE,
FOREIGN KEY (EmployeeNumber) REFERENCES EMPLOYEE(EmployeeNumber)
);

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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