Database Reference
In-Depth Information
CREATE TABLE SKILL (
SkillType CHARACTER (8)
PRIMARY KEY ,
BonusRate NUMERIC (5,2) ,
HoursPerWeek NUMERIC (2)
DEFAULT 40
CHECK (HoursPerWeek > 0 and
HoursPerWeek <= 60 ) )
CREATE TABLE ASSIGNMENT (
WorkerId CHARACTER (6) ,
BldgId CHARACTER (6) ,
StartDate DATE ,
NumDays Numeric (3) ,
PRIMARY KEY (WorkerId, BldgId) ,
FOREIGN KEY WorkerId REFERENCES WORKER
ON DELETE CASCADE
FOREIGN KEY BldgId REFERENCES BUILDING
ON DELETE CASCADE )
TABLE SPACE Main_Extent
STORAGE ( INTIAL 1M, NEXT 100K,
MINEXTENTS 1, MAXEXTENTS 10, PCTINCREASE 10);
CREATE SCHEMA RARITAN-CONSTRUCTION
AUTHORIZATION TOM NETHERTON
CREATE TABLE WORKER (
WorkerId CHARACTER (6)
PRIMARY KEY,
Name CHARACTER(25),
HourlyRate NUMERIC (5,2),
SkillType CHARACTER (8),
SupvId CHARACTER (6),
FOREIGN KEY SupvId REFERENCES WORKER
ON DELETE SET NULL )
CREATE TABLE BUILDING (
BldgId CHARACTER (6)
PRIMARY KEY,
BldgAddr CHARACTER (35),
BldgType CHARACTER (9)
DEFAULT 'Office'
CHECK (BldgType IN
('Office', 'Warehouse', 'Retail', 'Home') )
Figure 12-4
SQL statements for building construction data model
Data edits with CHECK statements
Referential integrity constraints
ON DELETE SET NULL (set foreign key value to be null when corre-
sponding parent entity is deleted)
ON DELETE CASCADE (if a parent entity is deleted, cascade the delete
and delete child entity as well)
Storage statement indicating disk space extents
DATA STORAGE AND ACCESS
Physical design is the process of implementing the database on physical storage.
Therefore, a close look at data storage mechanisms and the methods of data access
will help you get a better understanding of physical design. What are the various
levels of data storage? How are data organized as physical files that reside on phys-
ical storage? When a user requests for a particular set of data from the database
system, how do different software and hardware layers execute the request? What
are the implications of data storage management in the physical design process?
We will cover the various aspects of storage management and trace the handling
of data requests. Data are stored as records within file blocks. We will explore the
nature of file blocks and understand how block management is part of the physical
design process. Records may be arranged in files in a variety of ways. Considering
the major access patterns, records must be arranged with the most optimal method.
This is called file organization, and we will review the major file organizations.
Search WWH ::




Custom Search