Database Design for Mere Mortals Ch. 8

Question 1

State the three reasons why keys are important.

Answer 1

— Ensure that each record in a table is properly identified
— Help establish and enforce various types of integrity
— Serve to establish table relationships

Question 2

What are the four main types of keys?

Answer 2

1. Candidate
2. Primary
3. Foreign
4. Non

Question 3

What is the purpose of a Candidate Key?

Answer 3

Uniquely identify a single instance of the table’s subject

Question 4

State four items of the Elements of a Candidate Key.

Answer 4

— Cannot be a multipart field
— Must contain unique values
— Cannot contain Null
— Its value is not optional in whole or in part
— Comprises a minimum number of fields necessary to define uniqueness
— Values must uniquely and exclusively identify each record in the table
— Its value must exclusively identify the value of each field within a given record
— Its value can be modified only in rare or extreme cases

Question 5

True or False: A candidate key can be composed of more than one field.

Answer 5

True

Question 6

Can a table have more than one candidate key?

Answer 6

Yes

Question 7

What is an artificial candidate key?

Answer 7

A field you create for the sole purpose of serving as a candidate key. You create this type of key when there are no ‘naturally occurring’ candidate keys in a table

Question 8

What is the most important key you assign to a table?

Answer 8

The primary key

Question 9

Why is the primary key important?

Answer 9

— A primary key field exclusively identifies the table throughout the database structure and helps establish relationships with other tables
— A primary key value uniquely identifies a given record within a table and exclusively represents that record throughout the entire database. It also helps to guard against duplicate records

Question 10

How do you establish a primary key?

Answer 10

By examining the table’s pool of available candidate keys and then selecting one as the primary key

Question 11

State four items of the Elements of a Primary Key.

Answer 11

— Cannot be a multipart field
— Must contain unique values
— Cannot contain Null
— Its value is not optional in whole or in part
— Compromises a minimum number of fields necessary to define uniqueness
— Its value must uniquely and exclusively identify each record in the table
— Its value must exclusively identify the value of each field within a given record
— Its value can be modified only in rare or extreme cases

Question 12

What must you do before you finalize your selection of a primary key?

Answer 12

You must make absolutely certain that it exclusively identifies the value of each field within a given record and meets all the Elements of a Primary Key

Question 13

What is an alternate key?

Answer 13

A candidate key that was not chosen to serve as the primary key of the table

Question 14

What do you ensure by establishing table-level integrity?

Answer 14

— There are no duplicate records in a table
— The primary key exclusively identifies each record in a table
— Every primary key value is unique
— Primary key values are not Null

Question 15

Why should you review the initial table structures?

Answer 15

— Ensure that the appropriate subjects are represented in the database
— Make certain that the table names and table descriptions are suitable and meaningful to everyone
— Make certain that the field names are suitable and meaningful to everyone
— Verify that all the appropriate fields are assigned to each table