Transforming Data Models into Database Designs - Database Processing: Fundamentals, Design, and Implementation - page 267

ARTIST

Figure 6-41
Column Properties for the
View Ridge Database Design

Column Name Type Key NULL Status Remarks

ArtistID Int Primary Key NOT NULL Surrogate Key

IDENTITY (1,1)
Unique (AK1.1)
Unique (AK1.2)
IN ('Canadian',
'English',
'French',
'German',
'Mexican',
'Russian',
'Spanish',
'United States')
(DateOfBirth <
DateDeceased)
(BETWEEN 1900
and 2999)
(BETWEEN 1900
and 2999)

LastName
FirstName
Nationality

Char (25)
Char (25)
Char (30)

Alternate Key
Alternate Key
No

NOT NULL
NOT NULL
NULL

DateOfBirth Numeric (4) No NULL

DateDeceased Numeric (4) No NULL

(a) Column Characteristics for the ARTIST Table

WORK

Column Name Type Key NULL Status Remarks

WorkID Int Primary Key NOT NULL Surrogate Key

IDENTITY (500,1)
Unique (AK1.1)
Unique (AK1.2)

Title
Copy
Medium
Description

Char (35)
Char (12)
Char (35)
Varchar
(1000)

Alternate Key
Alternate Key
No
No

NOT NULL
NOT NULL
NULL
NULL

DEFAULT
value =
'Unknown
provenance'

ArtistID Int Foreign Key NOT NULL

(b) Column Characteristics for the WORK Table

TRANS

Column Name Type Key NULL Status Remarks

TransactionID Int Primary Key NOT NULL Surrogate Key

IDENTITY (100,1)

DateAcquired Date

No NOT NULL

AcquisitionPrice Numeric (8,2) No NOT NULL

AskingPrice Numeric (8,2) No NULL

DateSold Date No NULL (DateAcquired <=

DateSold)

SalesPrice Numeric (8,2) No NULL (SalesPrice > 0)

AND (SalesPrice
<=500000)

CustomerID Int Foreign Key NULL

WorkID Int Foreign Key NOT NULL

(c) Column Characteristics for the TRANS Table