Chapter 3

Question 1

Difference between a primary key and a candidate key?

Answer 1

Candidate key: (a minimal superkey) If a composite key can not be reduced any further and still be unique.
Primary Key: (a specially designated candidate key) The primary key of a table cannot contain null values. Each table contains one primary key.

Question 2

Difference between a candidate key and a superkey?

Answer 2

Superkey: a column or combination of columns containing unique values for each row. The combination of every column in a table is always a superkey because rows in a table must be unique.
Candidate key: (a minimal superkey) A superkey is minimal if removing any column makes it no longer unique. A single column super key is minimal because no columns can be removed.

Question 3

What is a null value?

Answer 3

A special value that represents the absence of an actual value. A null value can mean that the actual value is unknown or does not apply to a specified row.

Question 4

What is the motivation for the entity integrity rule?

Answer 4

No two rows of a table can contain the same value for the primary key. In addition, no row can contain a null value for any column of a primary key.

Question 5

What is the motivation for the referential integrity rule?

Answer 5

Only two kinds of values can be stored in a foreign key:
A value matching a candidate key value is some row of the table containing the associated candidate key or
A null value.

Question 6

Relationship between the referential integrity rule and foreign keys?

Answer 6

Foreign keys keys uphold referential integrity between tables.

Question 7

How are candidate keys that are not primary keys indicated in the CREATE TABLE statement?

Answer 7

CREATE TABLE Table1
(Column1, dataType(XX),
Column2, dataType(XX) )

Question 8

What is the advantage of using constraint names when defining primary key, candidate key, and referential integrity constraints in CREATE TABLE statements?

Answer 8

Constraint names facilitate identification of the constraint if a violation occurs when a row is inserted or updated.

Question 9

When is it not permissible for foreign keys to store null values?

Answer 9

When the column value is required.

Question 10

What is the purpose of a database diagram such as the Access Relationship window?

Answer 10

Lets you define a relationships between items stored in multiple access tables

Question 11

How is 1-M relationship represented in the Relational Model?

Answer 11

1-M Relationship: A connection between two tables in which one row of a parent table can be referenced by many rows of a child table. 1-M relationships are the most common kind of relationship.
A line from the parent (“1 table) to the foreign key table (“Many table”) represents a 1-M relationship in the Relational Model.

Question 12

What about M-N relationship? (How is it represented in a relational model?)

Answer 12

M-N Relationship: A connection between two tables in which rows of one table can be related to many rows of the other table. M-N relationships cannot be directly represented in the Relational Model. Two 1-M relationships and a linking or associative table represent a M-N relationship.
Many-to-many relationships are not directly represented in the Relational Model. They are represented by a pair of 1-M relationships and an associative table.

Question 13

What is a self-referencing relationship?

Answer 13

A relationship in which a foreign key refers to the same table. Self-referencing relationships represent associations among members of the same set.

Question 14

How is a self-referencing relationship represented in the Relational Model?

Answer 14

There will be a column that represents the self-referencing relationship. (Example: Faculty Members Supervising other Faculty Members: FacSupervisor column). The FacSupervisor column in a row for “the supervised” will contain the PK value of another row that represents the Supervisor in the same table.

Question 15

What is a referenced row?

Answer 15

A row is referenced if there are rows in a child table with foreign key values identical to the primary key value of the parent table row.

Question 16

What two actions on referenced rows can affect related rows in a child table?

Answer 16

Deleting a referenced row

Updating the primary key of a referenced row

Question 17

What are the possible actions on related rows after a referenced row is deleted or its primary key is updated?

Answer 17

Restrict: Do not allow the action on the referenced row.
Cascade: Perform the same action (cascade the action) on related rows.
Nullify: Set the foreign key of relation rows to null.
Default: Set the foreign key of related rows to its default value.

Question 18

Why is the restricted action for referenced rows more common than the cascade action?

Answer 18

Because of the possible impact of referential integrity and to prevent loss of data.

Question 19

When is the nullify action not allowed?

Answer 19

When the foreign key doesn’t allow null values.

Question 20

Why is the extended cross product operator used sparingly?

Answer 20

Extended Cross Product: an operator that builds a table consisting of all combinations of rows from each of the two input tables. (Used sparingly because excessive data is generated)

Question 21

How are grouping columns used in the summarize operator?

Answer 21

Groups can be compressed or summarized by a calculated value. (min, max, average, sum)

Question 22

Why is the divide operator not as widely used as the join operator?

Answer 22

The matching requirement in divide is more stringent than join and more difficult to understand.
Example
Join: retrieves offerings taken by any student
Divide: retrieves offerings taken by all (or every) students.

Question 23

What are the requirements of union compatibility?

Answer 23

Each table must have the same number of columns and each corresponding column must have a compatible data type.

Question 24

Two interpretations for null values?

Answer 24

Absence of a value

Value unknown