DATA INTEGRITY - Database Design and Development: An Essential Guide for IT Professionals - page 447

CREATE SCHEMA PARTS-SUPPLY
AUTHORIZATION McMillan;
CREATE DOMAIN ItemIdentifier CHARACTER (4) DEFAULT “ZZZZ”
CHECK (VALUE IS NOT NULL);
CREATE TABLE SUPPLIER(

SupplierID ItemIdentifier,

SupplierName CHARACTER (35) NOT NULL,

SupplierStatus CHARACTER (2),

CHECK (SupplierStatus IN '10', '20', '30', '40'),

SupplierCity CHARACTER (25),

ShipCode CHARACTER (6),

PRIMARY KEY (SupplierID),

FOREIGN KEY (ShipCode) REFERENCES

SHIP-METHOD(ShipCode)

ON DELETE SET NULL);

CREATE TABLE PART (

PartNo CHARACTER (8),

PartName CHARACTER (25),

PartColor CHARACTER (15)

DEFAULT 'RED',

PartWeight DECIMAL (5, 2) NOT NULL,

CHECK (PartWeight < 900.00 AND PartWeight > 99.00),

PRIMARY KEY (PartNo));

CREATE TABLE SHIP-METHOD (

ShipCode CHARACTER (6),

ShipDesc CHARACTER (25),

UnitCost DECIMAL (5, 2)

NOT NULL,

PRIMARY KEY (ShipCode) );
CREATE TABLE SUPPLY (

SupplierID ItemIdentifier,

PartNo CHARACTER (8),

Quantity INTEGER,

PRIMARY KEY (SupplierID, PartNo),

FOREIGN KEY (SupplierID) REFERENCES SUPPLIER (SupplierID)

ON DELETE CASCADE,

FOREIGN KEY (PartNo) REFERENCES PART (PartNo)

ON DELETE CASCADE);

Figure 15-6 Schema definition: suppliers and parts.

referential integrity. The integrity subsystem of the DBMS handles attempted ref-
erential integrity violations.

Business Rules The integrity subsystem can also enforce business rules through
stored procedures and triggers. A stored procedure is a named module of code
defining a business rule and specifying the action to be taken in case of attempted
violation. A trigger is a special type of stored procedure. A trigger module can be
made to fire when a particular type of insert, update, or delete takes place. Figure
15-7 presents a sample trigger.

CONCURRENT TRANSACTIONS

The database environment supports processing of transactions, several of which
may interact with the database at the same time. Transaction T1 may start at a certain