Chapter 3 Flashcards
Difference between a primary key and a candidate key?
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.
Difference between a candidate key and a superkey?
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.
What is a null value?
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.
What is the motivation for the entity integrity rule?
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.
What is the motivation for the referential integrity rule?
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.
Relationship between the referential integrity rule and foreign keys?
Foreign keys keys uphold referential integrity between tables.
How are candidate keys that are not primary keys indicated in the CREATE TABLE statement?
CREATE TABLE Table1
(Column1, dataType(XX),
Column2, dataType(XX) )
What is the advantage of using constraint names when defining primary key, candidate key, and referential integrity constraints in CREATE TABLE statements?
Constraint names facilitate identification of the constraint if a violation occurs when a row is inserted or updated.
When is it not permissible for foreign keys to store null values?
When the column value is required.
What is the purpose of a database diagram such as the Access Relationship window?
Lets you define a relationships between items stored in multiple access tables
How is 1-M relationship represented in the Relational Model?
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.
What about M-N relationship? (How is it represented in a relational model?)
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.
What is a self-referencing relationship?
A relationship in which a foreign key refers to the same table. Self-referencing relationships represent associations among members of the same set.
How is a self-referencing relationship represented in the Relational Model?
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.
What is a referenced row?
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.
What two actions on referenced rows can affect related rows in a child table?
Deleting a referenced row
Updating the primary key of a referenced row
What are the possible actions on related rows after a referenced row is deleted or its primary key is updated?
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.
Why is the restricted action for referenced rows more common than the cascade action?
Because of the possible impact of referential integrity and to prevent loss of data.
When is the nullify action not allowed?
When the foreign key doesn’t allow null values.
Why is the extended cross product operator used sparingly?
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)
How are grouping columns used in the summarize operator?
Groups can be compressed or summarized by a calculated value. (min, max, average, sum)
Why is the divide operator not as widely used as the join operator?
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.
What are the requirements of union compatibility?
Each table must have the same number of columns and each corresponding column must have a compatible data type.
Two interpretations for null values?
Absence of a value
Value unknown
