Database Management Chapter 3

Chapter 3: Relational Data Model and Relational Database Constraints

• The relational model of data is based on the concept of a relation.
• A relation is a mathematical concept based on the ideas of sets.
• The strength of the relational approach to data management comes from the formal foundation provided by the theory of relations.
• The relational model was first proposed by Ted Codd of IBM in 1970.
• The paper "A Relational Model for Large Shared Data Banks" was published in Communications of the ACM, June 1970.
• This paper revolutionized database management and earned Ted Codd the ACM Turing Award.

Relational Model Concepts

• A relation can be thought of as a table of values.
• A relation can be thought of as a set of rows.
• A relation can be thought of as a set of columns.
• Each row in a relation represents a fact corresponding to a real-world entity or relationship.
• Each row has a value (or set of values) that uniquely identifies that row.
• Rows in a table are sometimes identified by row-ids or sequential numbers.
• Columns are typically identified by their column name, column header, or attribute name.
• A relation schema is defined as R(A1, A2, ..., An), where attributes are represented as A1, A2, etc., up to An.
• Example: CUSTOMER (Cust-id, Cust-name, Address, Phone#)
• A tuple is an ordered set of values, derived from an appropriate domain.
• Each row in the CUSTOMER table is a tuple.
• Example tuple: <632895, "John Smith," "101 Main St., Atlanta, GA 30332," "(404) 894-2000">

Definition Summary

• Informal Terms: Table, Column, Row, Values in a column, Table Definition, Populated Table
• Formal Terms: Relation, Attribute/Domain, Tuple, Domain, Schema of a Relation, Extension

Example

• Relation Name: STUDENT
• Attributes: Name, SSN, Home_phone, Address, Office_phone, Age, GPA
• Example tuples from the STUDENT relation are given.

Note

• Ordering of Tuples in a relation
• Ordering of Values within a Tuple
• Values and NULLs in the Tuples

Relational Integrity Constraints

• Constraints are conditions that must hold on all valid relation instances.
• There are four types of constraints:
  • Domain constraints
  • Key constraints
  • Entity integrity constraints
  • Referential integrity constraints

Domain Constraints

• Domain constraints specify that the value of each attribute A must be an atomic value from the domain dom(A).
• Data types associated with domains typically include standard numeric data types (integers, real numbers), characters, fixed-length strings, variable-length strings, date, time, timestamp, and money data types.

Key Constraints

• A relation is defined as a set of tuples. All elements of a set are distinct.
• No two tuples can have the same combination of values for all their attributes.
• A superkey of R is a set of attributes SK of R such that no two tuples in any valid relation instance r(R) will have the same value for SK.
• Every relation has at least one default superkey. A superkey can have redundant attributes.
• A key is a "minimal" superkey; removing any attribute from the key results in a set of attributes that is not a superkey.
• For example, in the STUDENT relation, {SSN} is a key; it is a minimal superkey.
• {SSN, Name, Age} is a superkey (but not a key).

Entity Integrity

• The primary key attributes (PK) of each relation schema R cannot have null values in any tuple of r(R).
• This is because primary key values are used to identify individual tuples. (t[PK] ≠ null for any tuple t in r(R))
• Other attributes might have constraints preventing null values even if not part of the primary key.

Referential Integrity

• A constraint involving two relations.
• Used to specify a relationship among tuples in two relations (referencing and referenced).
• Tuples in referencing relation R₁ have attributes (FK) that reference the primary key attributes (PK) of the referenced relation R₂.
• A tuple t₁ in R₁ references a tuple t₂ in R₂ if t₁[FK] = t₂[PK].
• A referential integrity constraint can be displayed as a directed arc from R₁.FK to R₂.

Statement of Referential Integrity Constraint

The value in the foreign key column (or columns) FK of the referencing relation R₁ can be either:

• a value of an existing primary key value of the corresponding primary key PK in the referenced relation R₂, or
• a null. In case (2), the FK in R₁ should not be part of its own primary key.

Update Operations on Relations

• INSERT, DELETE, MODIFY tuples.
• Integrity constraints should not be violated by update operations.
• Several update operations may need to be grouped together.
• Updates may automatically cause other updates to maintain integrity constraints.

Actions in Case of Integrity Violation

• Cancel the operation causing the violation (REJECT option).
• Perform the operation but inform the user (violation).
• Trigger additional updates (CASCADE option, SET NULL option).
• Execute a user-specified error-correction routine.

Insert Operation

• Provides a list of attribute values for a new tuple t to insert into a relation R.
• Insert operation can violate domain constraints, key constraints, entity integrity, or referential integrity constraints.

Delete Operation

• Can only violate referential integrity (if referenced by foreign keys in other tuples).
• A condition on attributes selects tuples to be deleted.

Update Operation

• The update operation changes values of one or more attributes in a tuple (or tuples) within a relation.

Update Operations Examples

• Examples of acceptable and unacceptable update operations are given.