Database Reference
In-Depth Information
WORK
Column Name
Type
Key
NULL Status
Remarks
WorkID
Int
Primary Key
NOT NULL
Surrogate Key
IDENTITY (500,1)
Title
Char (35)
No
NOT NULL
Copy
Char (12)
No
NOT NULL
Medium
Char (35)
No
NULL
Description
Varchar
(1000)
No
NULL
DEFAULT
value =
'Unknown
provenance'
Figure 7-6
Column
Characteristics
for the VrG
WOrK table
ArtistID
Int
Foreign Key
NOT NULL
Figure 7-7
the artISt-to-WOrK
relationship
Relationship
Cardinality
Parent
Child
Type
Nonidentifying
MAX
1:N
MIN
M-O
ARTIST
WORK
Figure 7-8
actions to Enforce Minimum
Cardinality for the artISt-
to-WOrK relationship
ARTIST
Is Required Parent
Action on ARTIST
(Parent)
Action on WORK
(Child)
Insert
None
Get a parent
Modify key or
Foreign key
Prohibit—ARTIST uses a
surrogate key
Prohibit—ARTIST uses a
surrogate key
Delete
Prohibit if WORK exists—
data related to a
transaction is never deleted
(business rule)
Allow if no WORK exists
(business rule)
None
of the CREATE TABLE statement is the name of the table followed by a list of all column defini-
tions and constraints enclosed in parentheses and ending with the ubiquitous SQL semicolon (;).
As stated earlier, SQL has several column and table constraints: PRIMARY KEY, NULL,
NOT NULL, UNIQUE, FOREIGN KEY, and CHECK. The PRIMARY KEY constraint is used to
define the primary key of the table. Although it can be used as a column constraint, because it
has to be used as a table constraint to define compound primary keys, we prefer to always use
it as a table constraint, as shown in Figure 7-9. The NULL and NOT NULL column constraints
are used to set the NULL status of a column, indicating whether data values are required in
that column. The UNIQUE constraint is used to indicate that the values of a column or col-
umns must not use repeated values. The FOREIGN KEY constraint is used to define referential
integrity constraints, and the CHECK constraint is used to define data constraints.
In the first section of the CREATE TABLE statement for the ARTIST table, each column is
defined by giving its name, data type, and null status. If you do not specify the null status using
NULL or NOT NULL, then NULL is assumed.
In this database, DateOfBirth and DateDeceased are years. YearOfBirth and YearDeceased
would have been better column names, but that is not how the gallery personnel refer to them.
Because the gallery is not interested in the month and day of an artist's birth and death, those
columns are defined as Numeric (4, 0), which means a four-digit number with zero places to
the right of the decimal point.
Search WWH ::
Custom Search