CS327e

Lab 4 Solution

Fall 2003

6.43 Write SQL statements to create the tables for a relational design of the data model shown in Figure 5-17(c). Use the format of statements shown in Figure 6-7. Define foreign keys and set cascading behavior in accordance with the referential integrity actions shown there. Which of the referential integrity actions cannot be implemented using foreign key constraints?

CREATE TABLE SALESPERSON

(EmployeeID Integer Not Null,

Name Char(20) Not Null,,

Phone Char(12) Not Null,,

Email Char(20) Not Null,,

CONSTRAINT EmployeePK PRIMARY KEY (EmployeeID));

CREATE TABLE SALESORDER

(SalesOrderNumber Integer Not Null,

OrderDate DATE Not Null,

OrderDescription VarChar(50) Not Null,,

OrderTotal Number(8,2) Not Null,,

TotalCommission Number(6,2) Not Null,,

CONSTRAINT EmployeePK PRIMARY KEY (EmployeeID));

CREATE TABLE SALESPERSON_SHARE

(EmployeeID Integer Not Null,

SalesOrderNumber Integer Not Null,

CommissionPercent Number(3,2) Not Null,,

CONSTRAINT SharePK PRIMARY KEY (EmployeeID, SalesOrderNumber));

ALTER TABLE SALESPERSON_SHARE

ADD CONSTRAINT EmployeeFK

FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE (EmployeeID)

ON UPDATE CASCADE

ON DELETE NO ACTION;

ALTER TABLE SALESPERSON_SHARE

ADD CONSTRAINT BookFK

FOREIGN KEY (SalesOrderNumber) REFERENCES SALESORDER (SalesOrderNumber)

ON UPDATE CASCADE

ON DELETE CASCADE;

6.44 Use SQL INSERT statements to add data to the tables you created in Question 6.43. Ensure that you have at least three salespeople, three sales orders, and seven records of salesperson share.

Insert Into SALESPERSON Values (10, ‘Jack Jones’, ‘540.111.2222’, ‘JackJ@problems.com’);

Insert Into SALESPERSON Values (20, ‘Jill Jones’, ‘540.333.4444’, ‘JillJ@problems.com’);

Insert Into SALESPERSON Values (30, ‘Bill Baker’, ‘540.555.6666’, ‘BillB@problems.com’);

Insert Into SALESORDER Values

(101, ’5-10-2003’, ‘Discount Order for PeakView’, 2500.00, 250.00);

Insert Into SALESORDER Values

(102, ’5-22-2003’, ‘Replace Playground Equipment’, 55000.00, 5500.00);

Insert Into SALESORDER Values

(103, ’6-1-2003’, ‘Refurnish Hotel Lobby’, 100000, 10000.00);

Insert Into SALESPERSON_SHARE Values (10, 101, 1.00);

Insert Into SALESPERSON_SHARE Values (10, 102, .25);

Insert Into SALESPERSON_SHARE Values (20, 102, .25);

Insert Into SALESPERSON_SHARE Values (30, 102, .50);

Insert Into SALESPERSON_SHARE Values (20, 103, .60);

Insert Into SALESPERSON_SHARE Values (30, 103, .40);