Recursive Relationship and Cardinality Ratio Quiz

Relational Database Schemas

• Primary key: The candidate key that uniquely identifies tuples within a relation.
• Foreign key: An attribute or set of attributes that matches the candidate key of another relation.

Entity Integrity

• No attribute of a primary key can be null in a base relation.

Referential Integrity

• If a foreign key exists in a relation, its value must either match a candidate key value of some tuple in its home relation or be wholly null.

General Constraints

• Additional rules specified by users or database administrators to define or constrain aspects of the enterprise.

Views

• Base relation: A named relation corresponding to an entity in the conceptual schema, stored in the database.
• View: The dynamic result of one or more relational operations on base relations.

Relational Model Terminology

• Relation, tuple, and attribute are used instead of table, row, and column.
• Operator, function, procedure, routine, and method refer to essentially the same thing.

Properties of Relations

• A relation has a distinct name from all other relation names in the relational schema.
• Each cell contains exactly one atomic value.
• Each attribute has a distinct name.
• The values of an attribute are from the same domain.
• Each tuple is distinct, with no duplicates.
• The order of attributes has no significance.
• The order of tuples has no significance, theoretically.

Relational Keys

• Super key: An attribute or set of attributes that uniquely identifies a tuple within a relation.
• Candidate key: A super key with no proper subset that is a super key.
• Composite key: A key consisting of more than one attribute.

Recursive Relationships

• A recursive relationship is a relationship between the same entity type, such as SUPERVISION between EMPLOYEE in the supervisor and subordinate roles.

Constraints on Relationship Types

• The cardinality ratio for a binary relationship specifies the maximum number of relationship instances that an entity can participate in.
• Possible cardinality ratios for binary relationship types are 1:1, 1:N, N:1, and M:N.

Participation Constraints and Existence Dependencies

• The participation constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type.
• Min/max constraints can be specified.
• Participation constraints can be total or partial.

Attributes of Relationship Types

• Relationship types can have attributes, similar to those of entity types.
• Attributes of 1:1 or 1:N relationship types can be migrated to one of the participating entity types.
• For a 1:N relationship type, a relationship attribute can be migrated only to the entity type on the N-side of the relationship.