Chapter 6

First, foreign keys that are based on surrogate keys have no meaning to the users. The second disadvantage of surrogate keys arises when data is shared among different databases. It is possible for two different SALE_ORDER rows, in two different databases, to have the same value of ID.

Describe two disadvantages of surrogate keys.

Primary keys can never be null since each row must have a unique identifier. Alternate keys can be null because one use of designating a column as an alternative key is simply to guarantee uniqueness of populated fields. Thus the value of an alternative key can be NULL, but if it has a value then the value must be unique.

Explain why primary keys may never be null, but alternate keys can be null.

(1) Replacing entities and attributes with tables and columns (2) Representing relationships and maximum cardinalities by use of foreign keys (3) Representing minimum cardinalities by defined actions to constrain the values of primary and foreign keys

Identify the three major tasks for transforming a data model into a database design.

Four column properties are: null status, datatype, default value, and data constraints.

Name four column properties.

The three characteristics of an ideal primary key are short, numeric and fixed.

What are the three characteristics of an ideal primary key?

The notation LastName (AK2.2) means that LastName is the second column in a composite alternate key.

What does the notation LastName (AK2.2) mean?

A surrogate key is a unique, DBMS-supplied identifier used as the primary key of a relation. Its advantages are: (1) They are unique within the table and never change. (2) They are assigned when the row is created and destroyed when the row is deleted. (3) It is an ideal primary key - short, numeric and fixed.

What is a surrogate key? What are its advantages

There is no difference between an alternate key and a candidate key - the terms are synonymous.

What is the difference between an alternate key and a candidate key?

When transforming a data model into a database design we create a table for each entity in the database design and a column for each attribute in the database design.

What is the relationship between entities and tables? Between attributes and columns?

A surrogate key is used when a table does not have a unique key, or when a unique key is too long, non-numeric and may change.

When should you use a surrogate key?

The choice of a primary key is important because: (1) The DBMS will use the primary key to facilitate searching and sorting of table rows. (2) Some DBMSs use the primary key to organize table storage. (3) Most DBMS products create indexes and other data structures using the value of the primary key.

Why is the choice of the primary key important?

The choice of a primary key is important because: (1) The DBMS will use the primary key to facilitate searching and sorting of table rows. (2) Some DBMSs use the primary key to organizing a table storage. (3) Most DBMS products create indexes and other data structures using the value of the primary key.

Why is the choice of the primary key important?