SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 288

Date and Time
Data Types

Description

DATE
DATETIME

YYYY-MM-DD : Range is from 1000-01-01 to 9999-12-31.
YYYY-MM-DD HH:MM:SS.
Range is from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
See documentation.
HH:MM:SS : Range is from 00:00:00 to 23:59:59.
M = 2 or 4 (default).
IF M = 2, then range is from 1970 to 2069 (70 to 69).
IF M = 4, then range is from 1901 to 2155.

TIMESTAMP
TIME
YEAR (M)

String Data Types Description

CHAR (M)
VARCHAR (M)
BLOB (M)
TEXT (M)
TINYBLOB
MEDIUMBLOB
LONGBLOB
TINYTEXT
MEDIUMTEXT
LONGTEXT
ENUM ('value1',
'value2', . . . )
SET ('value1',
'value2', . . . )

M = 0 to 255.
M = 1 to 255.
BLOB = Binary Large Object: maximum 65,535 characters.
Maximum 65,535 characters.
See documentation.

An enumeration. Only one value, but chosen from list. See documentation.

A set. Zero or more values, all chosen from list. See documentation.

Figure 7-4
Continued

The third new feature is the use of the DEFAULT column constraint in the Description
column of the WORK table. The DEFAULT constraint is used to set a value that will be inserted
into each row unless some other value is specified.
Figure 7-7 describes in tabular form the M-O relationship between ARTIST and WORK
shown in Figure 7-1, and Figure 7-8 (based on the template in Figure 6-28(a)) details the
referential integrity actions that will be needed to enforce the minimum cardinalities in the
ARTIST-to-WORK relationship.
Figure 7-9 shows the SQL CREATE TABLE statement for constructing the ARTIST table. (All
of the SQL in this chapter runs on SQL Server. If you are using a different DBMS, you may need to
make adjustments, so consult the chapter or appendix for the DBMS you are using.) The format

ARTIST

Figure 7-5
Column
Characteristics
for the VrG
artISt table

Column Name Type Key NULL Status Remarks

ArtistID Int Primary Key NOT NULL Surrogate Key

IDENTITY (1,1)
AK1 LastName Char (25) Alternate Key NOT NULL

FirstName Char (25) Alternate Key NOT NULL AK1

Nationality Char (30) No NULL

DateOfBirth Numeric (4) No NULL

DateDeceased Numeric (4) No NULL