THE PHYSICAL DESIGN PROCESS - Database Design and Development: An Essential Guide for IT Professionals - page 350

Space Management Storage space allocation for database and files in terms of
database records, disk extents, disk block management parameters, storage
method for each relation—clustered or nonclustered

Defining Design Components As noted above, the data dictionary contains
definitions for all the design components. As you know, the language used for
defining these components is known as Data Definition Language (DDL). For the
relational data model, SQL (Structured Query Language), which has evolved as
the standard, contains a DDL component. Let us take a specific example of a
logical data model and express the definitions for the data dictionary with
SQL.
First, this is a partial data model consisting of just four relations. The data model
represents the information requirements for the assignment of workers to building
construction. Figure 12-3 shows the semantic data model and the relational data
model denoted with standard notation.
Figure 12-4 lists the SQL statements used for defining the data model in the data
dictionary.
Note the following statements in the figure:

• Definition for each relation with CREATE TABLE statement
• Definition of attributes with their data types, data lengths, and value domains
• Default values for selected attributes
• Primary key statements
• Foreign keys to represent the relationships

BldgId

WorkerID

* * *

assigned to

WORKER BUILDING

Name

*

1

BldgAddr

possesses

HourlyRate

supervises

StartDate

1

BldgType

SkillType

SKILL

NumDays

BonusRate

HoursPerWeek

WORKER ( WorkerId , Name, HourlyRate, SkillType, SupvId)

Foreign Keys: SkillType REFERENCES SKILL
SupvId REFERENCES WORKER

Relational
Notation

BUILDING (BldgId, BldgAddr, BldgType)

SKILL ( SkillType , BonusRate, HoursPerWeek)

ASSIGNMENT (WorkerId, BldgId, StartDate, NumDays)

Foreign Keys: WorkerId REFERENCES WORKER

BldgId REFERENCES BUILDING

Figure 12-3 Logical model for building construction.