These exercises use the Room and Equipment tables from this chapter and the Course, Module and Staff tables created in the exercises for Chapter 5 as practice for writing SQL statements. The table schema are:
Room (building VARCHAR(15) NOT NULL,
roomNo VARCHAR(4) NOT NULL,
capacity INTEGER
)
PRIMARY KEY (building, roomNo)
Equipment (assetNo VARCHAR(15) NOT NULL,
assetType VARCHAR(15) NOT NULL,
description VARCHAR(50),
building VARCHAR(15),
room VARCHAR(4),
portable BOOLEAN DEFAULT (FALSE) NOT NULL,
cost NUMERIC(7,2),
acquired DATE
)
PRIMARY KEY (assetNo)
FOREIGN KEY (building, room) REFERENCES Room (building, roomNo)
Course (code CHAR(4) NOT NULL,
title VARCHAR(50) NOT NULL,
leadDepartment VARCHAR(30) NOT NULL,
minorDepartment VARCHAR(30),
level VARCHAR(15) NOT NULL,
qualification VARCHAR(50),
mode VARCHAR(9)DEFAULT 'Full Time' NOT NULL
)
PRIMARY KEY (code)
CANDIDATE KEY (title, mode)
BUSINESS RULE mode must be one of {'Full Time', 'Part Time'}
Module (code CHAR(8) NOT NULL,
title VARCHAR(50) NOT NULL,
department VARCHAR(30) NOT NULL,
level VARCHAR(15) NOT NULL,
leader VARCHAR(10)
)
PRIMARY KEY (code)
CANDIDATE KEY (department, title)
FOREIGN KEY leader REFERENCES Staff(staffNo)
ON UPDATE CASCADE
ON DELETE SET NULL
Staff (staffNo VARCHAR(10) NOT NULL,
title VARCHAR(4) NOT NULL,
fName VARCHAR(50) NOT NULL,
lName VARCHAR(20) NOT NULL,
email VARCHAR(50) NOT NULL,
department VARCHAR(30) NOT NULL,
lineManager VARCHAR(10) NOT NULL
)
PRIMARY KEY (staffNo)
CANDIDATE KEY (email)
BUSINESS RULE title must be one of {'Mr', 'Ms', 'Miss', 'Mrs',
'Dr', 'Prof'}
FOREIGN KEY lineManager REFERENCES Staff(staffNo)
ON UPDATE CASCADE
ON DELETE NO ACTION