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

CUSTOMER

Column Name Type Key NULL Status Remarks

CustomerID Int Primary Key NOT NULL Surrogate Key

IDENTITY (1000,1)

LastName Char (25) No NOT NULL

FirstName Char (25) No NOT NULL

Street Char (30) No NULL

City Char (35) No NULL

State Char (2) No NULL

ZipPostalCode Char (9) No NULL

Country Char (50) No NULL

AreaCode Char (3) No NULL

PhoneNumber Char (8) No NULL

Email Varchar (100) Alternate Key NULL Unique (AK 1.1)

(d) Column Characteristics for the CUSTOMER Table

CUSTOMER_ARTIST_INT

Column Name Type Key NULL Status Remarks

ArtistID Int Primary Key,

Foreign Key

NOT NULL

CustomerID Int Primary Key,

Foreign Key

NOT NULL

Figure 6-41
Continued (e) Column Characteristics for the CUSTOMER_ARTIST_INT Table

According to this document, an INSERT trigger on WORK will be written to create the re-
quired child. This trigger will be fired whenever a work is first introduced at the gallery. At that
time, a new TRANS row will be created to store the values for DateAcquired and AcquisitionPrice.
Changes to the primary key in WORK will not occur because it has a surrogate key.
Changes to the foreign key in TRANS will not be allowed because a TRANS never switches
to another work. As stated earlier, the gallery has the policy that no transaction or related
data will ever be deleted. Consequently, deletions of either WORK or TRANS are not
allowed.

Column Properties for the View Ridge Gallery Database Design Tables
As we discussed at the beginning of this chapter, besides naming the columns in each table,
we must specify the column properties summarized in Figure 6-1 for each column: null
status, data type, default value (if any), and data constraints (if any). These are shown in
Figure 6-41, where surrogate keys are shown using the SQL Server IDENTITY({StartValue},
{Increment}) property to specify the values the surrogate key will use. We will describe how
it implements surrogate keys in our discussion of Microsoft SQL Server 2012 (Chapters 7
and 10A), Oracle Database 11 g Release 2 (Chapter 10B), and MySQL 5.6 (Chapter 10C).
With this step, we have completed our database design for the View Ridge Gallery data-
base, and now we are ready to create it as an actual, functioning database in a DBMS product.
We will do so in many of the following chapters, so be certain that you understand the View
Ridge Gallery database design we have built.