1

CS 430Database Theory

Winter 2005

Lecture 4: Relational Model

2

Domains

A Domain is a set of atomic values Atomic: the values are indivisible as far as the relational

model is concerned Easy examples:

Numbers Character strings Dates

Questionable example: A set of Character Strings

Allowed by some theoreticians Not allowed by many RDBMSs

Assumption: Domains come with appropriate operations for each domain

3

Relation Schema

Denoted by: R(A1, A2, …, An) R is the name of the Relation A1, A2, …, An is the list of attributes of R

Attributes are names

Each attribute has an associated Domain: dom(Ai) n is the Degree or Arity of the Relation

Example: PROJECT(PName, PNumber, PLocation, DNum)

Domains: Appropriate strings and numbers

4

Tuples

Relation or Relation State Date: Relation Variable r(R) = {t1, t2, …, tm}

May use just r if the context is clear A mathematical set

Each t = <v1, v2, … , vn> An (ordered) tuple of values Each vi is either a member of dom(Ai) or the special null value

Mathematically r is a subset of the cartesian product of the dom(Ai)

5

Relations

The term Relation State is used to emphasize the potential time varying nature of the relation

Characteristics There is no order for the tuples of relation (the ti) The ordering of values in a tuple is only important to

associate each value with the corresponding domain It is possible to define relations as a mapping of attributes to

values, thereby removing the order However, in SQL there are cases where the order is important

The null value is provided for representing unknown or not applicable data

6

Relations (Continued)

A Relation can be viewed as a set of facts or assertions in a logic programming language “Closed World” assumption: all true (at the current time)

facts are in the database “Open World” assumption: opposite of “Closed World” Most database applications make the “closed world”

assumption Notation

t[Ai] or t.Ai or t[i] can be used to refer an attribute value

t[Au, Av, … Az] or t.(Au, Av, … Az) can be used to refer to the corresponding sub-tuple of t

7

Database

A Relational Database Schema S is a set of Relations and a set of Integrity Constraints S = ({R1, R2, … , Rm}, IC) Integrity Constraints are a collection of assertions

or predicates that should be satisfied by the database

A Database State DB is A set of Relation States

DB = {r1(R1), r2(R2), … , rm(Rm)} which satisfy the Integrity Constraints

8

Example

Relational Schema See Figure 5.5 of Text Book

Relational State See Figure 5.6 of Text Book

9

Constraints

Internal Ones enforced by the DBMS

External Ones true in the users view of the world Some may be enforced by Application software

10

Types of Internal Constraints

Inherent Model Based Constraints Ones based on relational model, e.g. no repeated

values Schema-Based Constraints

Domain Constraints Attributes must be in the corresponding domain

Key Constraints and Constraints on Null Values Entity Integrity, Referential Integrity, and Foreign

Keys General Constraints and Triggers

11

General Constraints and Triggers Some DBMSs provide mechanisms for General

Constraints and Triggers General constraint: an arbitrary assertion attached to a

Table (Relation) or Schema Trigger: procedural code that is executed when certain

kinds of events (e.g. updating the database) occur These can be used to enforce arbitrary constraints Example:

No employee makes more than their supervisor

12

Key Constraints

Super Key For a given relation R, a subset of the attributes {Au, Av, …

Az} such that t[Au, Av, … Az] uniquely determines t

(Candidate) Key A super key which is minimal or irreducible in the sense

that no smaller subset of the attributes is also a Super Key Primary and Alternate Keys

Primary Key - some chosen (distinguished) Candidate Key Alternate Key - any non-primary Candidate Key

13

Entity Integrity

Entity Integrity All attributes of a primary key are non-null Good practice: All attributes of an candidate key

are non-null Question: If nulls are allowed how is it a candidate key?

14

Foreign Keys

Foreign Key or Referential Integrity Constraint Given relations R1 and R2, a subset FK of the attributes of

R1, FK = {Au, Av, … Az} is a foreign key referencing R2 which has primary PK if given t1 a tuple in R1 either

1. t1[FK] is null, or

2. there exists a tuple t2 in R2 with t1[FK] = t2[PK]

Notes1. R1 and R2 can be the same relation

2. Circular foreign key dependencies are allowed Example:

See figure 5.7 in Text Book

15

Database Operationsand Constraint Violations Three basic kinds of update operations in the

relational model: Insert Update Delete

What happens if an operation causes a constraint violation

Especially violation of a referential integrity constraint

16

Possible Remedies

No Action Don’t do anything, accept the action as is

This may cause problems “down the road”

Restrict Refuse the action

Cascade Cascade the change to other tuples to correct

violation Set Null and Set Default

Set offending attributes to null or a default value

17

Insert Violations

Domain constraint Insert value not in domain

Key constraint New tuple with same key

Entity integrity Primary key attribute is null

Foreign Key Foreign key refers to tuple that doesn’t exist

Generally Restrict (i.e. Reject) such an Insert

18

Delete Violations

Foreign Key Delete primary key referred to by foreign key Remedies:

No Action: Accept operation May result in subsequent integrity violations

Restrict: Reject operation Cascade: delete referring records Set Null or Set Default: Modify referencing keys to some

other value

19

Update Violations

Domain, Key, Entity Same as insert - usually Rejected

Foreign Key Modify primary key referred to by foreign key

No action, Restrict, Set Null or Set Default: same as insert

Cascade: modify referencing keys to new value