DAD220 Ch 3 Questions with Verified Correct
Answers
Primary Key
A column, or group of columns, used to identify a row. In the Employee table below, the
primary key is ID because each employee has a unique employee ID. The primary key
usually appears on the table's left side, but the primary key's position is not significant to the
database.
The primary key is specified in SQL when the table is created.
Often, primary key values are used in the WHERE clause to select a specific row.
Alternate Key
If a table contains several unique columns, any unique column may be specified. A unique
column that is not the primary key is called an alternate key
Primary keys obey two rules
1. Values must be unique within the column. This rule ensures that each value identifies at
most one row.
2. Values may not be NULL. This rule ensures that each value identifies at least one row.
Together, the two rules ensure that each primary key value identifies exactly one row.
Name is a good primary key.
True
False
False. A new employee could have the same name.
A new employee can be added without an ID value. T F
,F Primary Key values cannot be NULL
A new employee can be added with ID 5384. T F
False. Primary keys cannot have duplicates
No alternate keys are present. T F
True. Two different employees may have the same name or salary. Since Name and Salary
might not be unique, the table has no alternate keys.
Simple Primary Key
A simple primary key consists of a single column
Composite Primary Key
A composite primary key consists of multiple columns. Composite primary keys are denoted
with parentheses. Ex: (ColumnA, ColumnB)
an employee may have several family members, so employee ID is not unique. The
composite primary key consists of the ID and Number columns, denoted (ID, Number).
Minimal Primary Key
In a minimal primary key, all columns are necessary for uniqueness. Ex: (ID, Number,
Relationship) is not minimal. Since (ID, Number) is unique, Relationship is unnecessary.
Composite primary keys obey three rule
1. Column values, when grouped together, must be unique. Ex: The combination (2538, 1) is
unique within (ID, Number).
2. Columns may not contain NULL.
3. Composite primary keys must be minimal.
The first two rules are similar to single-column primary keys.
, Can (ID, Relationship) be the primary key of Family? Y N
No. (ID, Relationship) cannot be the primary key because (6381, Daughter) is repeated.
Composite primary keys must be unique.
What is the primary key of the PhoneNumber table?
(Area code, Exchange, Number) is unique, non-null, and minimal, so (AreaCode, Exchange,
Number) is a primary key. Occasionally, a primary key includes all columns in a table.
Foreign Key
A foreign key is a column, or group of columns, that refer to a primary key. Non-NULL
foreign key values must match some value of the primary key. The data types of the foreign
and primary keys must be the same, but the names may be different.
The Manager column of the Department table below refers to the ID column of Employee, so
Manager is a foreign key.
Foreign keys do not obey the same rules as primary keys
1. Foreign key values may be repeated. Ex: Sales and Marketing have the same manager.
2. Foreign key values may be NULL. Ex: Technical support currently has no manager.
3. Non-NULL foreign key values must match some primary key value.
The data type of Manager and ID must be the same if manager is a foreign key of ID
which is primary key T F
True Manager is a foreign key, which refers to the ID primary key. Data types of foreign and
primary key must be the same.
NULL in the Manager column refers to a row of the Employee table with a NULL ID. T
F
Answers
Primary Key
A column, or group of columns, used to identify a row. In the Employee table below, the
primary key is ID because each employee has a unique employee ID. The primary key
usually appears on the table's left side, but the primary key's position is not significant to the
database.
The primary key is specified in SQL when the table is created.
Often, primary key values are used in the WHERE clause to select a specific row.
Alternate Key
If a table contains several unique columns, any unique column may be specified. A unique
column that is not the primary key is called an alternate key
Primary keys obey two rules
1. Values must be unique within the column. This rule ensures that each value identifies at
most one row.
2. Values may not be NULL. This rule ensures that each value identifies at least one row.
Together, the two rules ensure that each primary key value identifies exactly one row.
Name is a good primary key.
True
False
False. A new employee could have the same name.
A new employee can be added without an ID value. T F
,F Primary Key values cannot be NULL
A new employee can be added with ID 5384. T F
False. Primary keys cannot have duplicates
No alternate keys are present. T F
True. Two different employees may have the same name or salary. Since Name and Salary
might not be unique, the table has no alternate keys.
Simple Primary Key
A simple primary key consists of a single column
Composite Primary Key
A composite primary key consists of multiple columns. Composite primary keys are denoted
with parentheses. Ex: (ColumnA, ColumnB)
an employee may have several family members, so employee ID is not unique. The
composite primary key consists of the ID and Number columns, denoted (ID, Number).
Minimal Primary Key
In a minimal primary key, all columns are necessary for uniqueness. Ex: (ID, Number,
Relationship) is not minimal. Since (ID, Number) is unique, Relationship is unnecessary.
Composite primary keys obey three rule
1. Column values, when grouped together, must be unique. Ex: The combination (2538, 1) is
unique within (ID, Number).
2. Columns may not contain NULL.
3. Composite primary keys must be minimal.
The first two rules are similar to single-column primary keys.
, Can (ID, Relationship) be the primary key of Family? Y N
No. (ID, Relationship) cannot be the primary key because (6381, Daughter) is repeated.
Composite primary keys must be unique.
What is the primary key of the PhoneNumber table?
(Area code, Exchange, Number) is unique, non-null, and minimal, so (AreaCode, Exchange,
Number) is a primary key. Occasionally, a primary key includes all columns in a table.
Foreign Key
A foreign key is a column, or group of columns, that refer to a primary key. Non-NULL
foreign key values must match some value of the primary key. The data types of the foreign
and primary keys must be the same, but the names may be different.
The Manager column of the Department table below refers to the ID column of Employee, so
Manager is a foreign key.
Foreign keys do not obey the same rules as primary keys
1. Foreign key values may be repeated. Ex: Sales and Marketing have the same manager.
2. Foreign key values may be NULL. Ex: Technical support currently has no manager.
3. Non-NULL foreign key values must match some primary key value.
The data type of Manager and ID must be the same if manager is a foreign key of ID
which is primary key T F
True Manager is a foreign key, which refers to the ID primary key. Data types of foreign and
primary key must be the same.
NULL in the Manager column refers to a row of the Employee table with a NULL ID. T
F
