Reference: Ramez Elmasri and Shamkant Navathe, Fundamentals of Database Systems, 5th edition, Addison-Wesley, 2007. See www.aw-bc.com/elmasri

Contents:

1. Relational model concepts
2. Relational model constraints and relational DB schemas
3. Update operations, Transactions, and dealing with constraint violations
   Summary
   Review questions
   Exercises
   Selected bibliography

You should be able to:

Define critical terms

Identify constraints

Critical terms include:

Domain, attribute, n-tuple, relation schema, relation state, degree of a relation, relational database schema, relational database state, key vs. superkey, candidate key, primary key, NULL, entity integrity constraint, referential integrity constraint, foreign key, transaction, update.

For each, you should be able to give a definition and an example.

Goal of this chapter:

Describe the basic principles of the relational model of data

Intermediate step between Entity-Relationship diagrams and code

This is what we translate ERDs into

Exercise: What ERD concept maps into each relational model concept?

Mathematician: Let f be a function. Call it g.

5.1 Relational Model Concepts

Represent DB as collection of relations

Relation - table of values. Row is collection of related data values corresponding to real-world entity

Tuple - row

Attribute - column header

Relation - table

Domain D - set of atomic values. Named. Often specified as data type, enumeration, range, format, units of measurement

Atomic - value is indivisible, as far as relational model is concerned

Relation schema - R(A₁, A₂, ..., A_n) - Relation name R; list of attributes A₁, A₂, ..., A_n

Attribute A_i - name of a role played by some domain D = dom(A_i)

Degree of a relation - number of attributes

Relation (or relation state) r of relation schema R(A₁, A₂, ..., A_n), denoted r(R) - set of n-tuples r = {t₁, t₂, ..., t_m}. Note n vs. m

n-tuple t - ordered list of values t = <v₁, v₂, ..., v_n>, where v_i is in dom(A_i) or NULL.

Value t_i corresponds to attribute A_i, denoted t[A_i] or t[i]

Also relation intension for the schema R

Relation extension for a relation state r(R)

Tuples in relation are not (officially) ordered (attributes)

Tuple can be set {(<attribute>, <value>)} pairs

Formal definition:

A relation r(R) - mathematical relation of degree n on domains dom(A1), dom(A2), ..., dom(An), which is a subset of Cartesian product: r(R) subset dom(A1) x dom(A2) x ... x dom(An)

Total number of possible tuples - |dom(A₁)| x |dom(A₂)| x ... x |dom(A_n)|. Not all are valid

Current relation state - valid tuples that represent a particular state in the real world

Schema is stable in time

State changes frequently

Attribute values are atomic - officially no composite or multi-valued attributes

First normal form assumption

NULL - value of attribute, but not in dom(A_n)

• value unknown
• value exists, but not available
• attribute does not apply
• not zero

Relation schema as assertion - "Entity has attributes"

Assertions about entities

Assertions about relationships

Relation schema as predicate - "Values satisfy the predicate"

Close world assumptions - all true facts are present in the extension of the relation(s)

5.2 Constraints

Restrictions on Cartesian product

Inherent model-based or implicit constraints - inherent in the data model, e.g., no duplicate tuples

Schema-based or explicit constraints - directly expressed in schemas of the data model

Application-based or semantic constraints or business rules - cannot be directly expressed in schemas of data model - must be expressed or enforced by application

Data dependencies, including functional dependencies and multi-valued dependencies - for testing quality of DB design

Schema-based constraints:

5.2.1 Domain constraints - dom(A_i) - data types, subranges, enumeration. See Section 8.1, p. 246

5.2.2 Key constraints - no two tuples are the same
Superkey - subset of attributes such that no two tuples have the same combination of values
Superkey specifies a uniqueness constraint
Key - superkey with no redundancy:

1. Two distinct tuples cannot have identical values of all attributes in key
2. Cannot remove any attributes from key and have 1. still hold

Value of key uniquely identifies tuple

Relation schema may have more than one key

Defining a key enforces uniqueness

Primary key - choose one of candidate keys to identify tuples in relation

Primary key is usually single (or few) attributes

5.2.2 Constraints on NULL - for each attribute

• allow, or
• not allow

5.2.3 Relational databases and relational database schemas

So far: constraints apply to single relations & their attributes

Relational database schema S - Set of relation schemas S = {R₁, R₂, ..., R_m} and set of integrity constraints IC

Relation schema refers to ___
Relational database schema refers to _____

Relational database state DB of S - set of relation states DB = {r₁, r₂, ..., r_m} such that

1. r_i is a state of R_i, and
2. r_i relation states satisfy integrity constraints IC

Valid state - DB which satisfies IC

Invalid state - does not

Integrity constraints

• specified in database schema
• expected to hold in every valid DB state

5.2.4 Entity integrity constraint - primary key is not NULL

5.2.4 Referential integrity constraint -

• between two relations
• maintains consistency among tuples in two relations

Informal: Tuple in one relation that refers to another relation must refer to a tuple that exists

5.2.4 Foreign key - Set of attributes FK in relation schema R₁

• Attributes of FK have same domain(s) as primary key attributes PK of R₂
  FK refers to relation R₂
• FK in tuple t₁ of current state r₁(R₁) either
  1. matches a PK for some tuple t₂ of current state r₂(R₂) OR
  2. is NULL

Referencing relation - R₁

Referenced relation - R₂

Referential integrity constraint from R₁ to R₂ holds

FK may refer to its own relation

Notation: Directed arc from R₁.FK to R₂

5.2.5 Other type of constraints - Business rules. Enforced by application or by triggers or assertions in the DB

5.3 Update Operations

DB operations:

1. Retrieval
2. Update (change DB state): insert (SQL: INSERT), delete (DELETE), modify (UPDATE)

Constraints are invariants. How do you guarantee they are preserved?

1. Domain constraints
2. Key constraints
3. Entity integrity
4. Referential integrity

5.3.1 Insert - Can violate any

Handling:

• Reject insertion?
• Correct?
• Ask?

5.3.2 Delete - Can only violate referential integrity

Handling:

• Reject?
• Cascade? Delete also what "this" is refers to by (sic.)
• Modify referencing attribute values? NULL? Other?
• Ask?

DBMS should allow you to specify

5.3.3 Update - Modify attributes

Primary key?
Foreign key?
Other attributes?

Handling?

5.3.4 Transaction concept - Read, insert, delete, update leaving DB in consistent state