About 150 Assignments Calendar Resources Site contents Find in 150

# document.write("Chapter " + thisChapt + " " + Chapter[thisChapt]);

 Chapter Slides (ppt) Figures (ppt)

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

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(A1, A2, ..., An) - Relation name R; list of attributes A1, A2, ..., An

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

Degree of a relation - number of attributes

Relation (or relation state) r of relation schema R(A1, A2, ..., An), denoted r(R) - set of n-tuples r = {t1, t2, ..., tm}. Note n vs. m

n-tuple t - ordered list of values t = <v1, v2, ..., vn>, where vi is in dom(Ai) or NULL.

Value ti corresponds to attribute Ai, denoted t[Ai] 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(A1)| x |dom(A2)| x ... x |dom(An)|. 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(An)

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

Relation schema as assertion - "Entity has attributes"

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(Ai) - 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 = {R1, R2, ..., Rm} 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 = {r1, r2, ..., rm} such that

1. ri is a state of Ri, and
2. ri 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 R1

• Attributes of FK have same domain(s) as primary key attributes PK of R2
FK refers to relation R2
• FK in tuple t1 of current state r1(R1) either
1. matches a PK for some tuple t2 of current state r2(R2) OR
2. is NULL

Referencing relation - R1

Referenced relation - R2

Referential integrity constraint from R1 to R2 holds

FK may refer to its own relation

Notation: Directed arc from R1.FK to R2

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?

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?

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

 Chapter Slides (ppt) Figures (ppt)

 Marquette University. Be The Difference. Marquette | Corliss | COEN 150