# Relational Model and Algebra

#### Introduction

• Created by E. Codd in 1970.
• Relational Model - Collection of relations (tables)
• Table - Each row represents a collection of related data values.

#### Concepts

Domains are important in design

E.g. Age: (16 - 70)

Range of each data type (domain)

Figure xxx

Each row is called a tuple

Domain is a set of atomic values

#### Example

 Values Domain SSN Set of 9 digit SSN GPA (0.00, 4.00) Age (16, 70) Dept CS, EE, ME, MIS, Set of departments)

Most specific data type for each domain

#### Relational Schema R

is denoted as

R (A1, A2, ... An)

where

attribute Ai has domain D dom(Ai)

Degree of relation = # of attributes

e.g. car (name, make, model, msrp)

degree = 4

dom (name) = all the car names

#### Relation

A relation (instance) r of relational schema R(A1, A2, ..., An) is denoted by r (R)

= set of n-triples r = { t1, t2, ..., tn}

OR

A relation r(R) is a subset of the cartesian product of the domains that define R

r(R) "is equal or subset of" (dom(A1) x dom(A2) x ... x dom(An))

=> x specific all possible combos of values from the underlying domain

#### Product

Total number of triples in Cartesian product

= | dom (A1) | x | dom (A2) | x ... x | dom (An) |

Pick any valid combo => current relation state

| Names | x | Phone # | x | Age |

Order of tuples => does not affect relation

Page 140, Fig 6.1

#### Tuple Values

Atomicity in tuple vales

• multivalues are prohibited (not allowed)
• first normal form (FNF)

An n-tuple t in r(R)

=> t = < v1, v2, ... , vn>

where vi represents value of attribute Ai

t[Ai] == t[Name] = <'Bubber Smith'>

t[A1, A2, ... , An] == t[Name, Salary, Age] = <'John', '2500', '40'>

Q, R, S denote rel names

q, r, s denote rel states

t, u, v denote tuples

#### Domain Constraints

Specify atomicity in each value of each attribute from dom(A)

data types - int, float, char ...

#### Key Constraints

• Relation defined as a set of tuples
• All elements are distinct
• All tuples must be distinct
• t1 not equal to t2 for all ti

#### Superkey of R

Set of all its attributes

Key of R

(SK of R) and (K -A = K') where K' is not SK of R

E.g.

SSN is key of STUDENT since there is no 2 students has same SSN

(Any attribute has SSN in there, will be superkey)

t1[SK] not equal t2[SK]

Key is minimal superkey ... at least set of all attriibutes

A relation schema may have more than one key (candidate key) (can be both)

P145, Fig 6.4 - Ned to choose one as primary key

E.g. SSN - primary key, cannot be null

#### Relational Database Schema S

= { R1, R2, ... , Rn}

Set of integnity constraints (IC)

A relational DB instance of S

= {r1, r2, ..., rm} where ri is an instance of Ri and ri satisfies IC.

#### Integnity Constraints Concept

Entity integnity:

No primary key can be null. E.g. unable to identify same tuples if null.

Referential Integrity Constriant

• For consistency among tuples of two relations
• A tuple in Ri that refers to Rj must refer to a tuple that exists in Rj

E.g.

EMPLOYEE

tuple i [------] ---------> DEPARTMENT (Must exist)

tuple j [------]

A foreign key FK is a set of attributes of some Ri if

Figure 6.7 P148

1. attr of Ri FK = attr of Rj PK ==> FK references Rj

(1)

dom of R1 (FK) = dom of R2 (PK)

=> FK refer to R2

(2)

t1 [FK] = t2 [PK]

t1 refers to t2

i.e. value of FK in t1 occurs in value of PK in t2 tuple t1 references tuple t2

(PK cannot be null).

#### Other Constraints

E.g. Retrieve all names of employees whose

salary < salary (supervisor)

All type of constraints should be provided by DDL

Operations

insert - new tuples in a relation

delete - tuples

modify - change values of some attributes

#### Insert

Constraints violated?

1. Key constraints: cannot insert tuple with a PK that occurs in another tuple of the same relation.

t1 = <'B. Smith', '1234', 'xxx', ...>

t2 = <'C. Smith', '1234', null>

1234 is the same, so should be rejected.

2. Entity constraint

t1 = <'B.Smith', '1234', '624-1234'> (All attrs combined are primary key)

t2 = <'C.Smith', '3456', null>

Entity constraint => PK not equal to null

3. Referential Integrity Constraints

Insert

t1 = <'B. Smith', ... , 20>

If there is only 15 dept, when try to refer to 20, we should get invalid error.

#### Actions

Reject operation - inform user

Perform interactive correction to prompt user for correct info.

#### Delete

Should observe all constraints

Delete a primary key that is reference by a foreign key (ref integrity)

#### Course of Action

Reject operation - inform user

Accept delete and delete all related tuples

R1 --- R2

R1 --- R3

Delete all tuples in R1, R2 and R3. Or null the FK in R2 and R3.

#### Modify

Check if PK or FK, make sure new value refers to an existing tuple

Check correct data type and domain

Can use delete and insert (modify PK)

## Relational Algebra

Operations could be divided into

Set operation (math set theory) OR

Relational DB operations

#### Set Operation

• union
• intersection
• difference
• cartestion product

• select
• project
• join, etc.

#### Select (Sigma)

• Select a subset of tuples in a relation that satisfy some condition
• Selecting rows (tuple)
• Get tuples from EMPLOYEE working in dept #4 or salary > 80000

DE <dno = 4> (EMPLOYEE)

DE <salary > 80000> (EMPLOYEE)

#### In General

DE <select cond> (<relation name>)

where

<select cond> is set of clauses s.t.

<attr name> <comparsion op><const value>

OR

<attr name> <comparsion op> <attr name>

E.g. DNO = 20

<comparsion op> = {=, <, <=, =>, >, noteq}

<const val> = from attributes domain

#### Allow Boolean Operators

AND, OR, NOT

E.g.

DE (DNO = 4 AND SALARY > 80000) OR (DNO = 5 AND SALARY > 20000) (EMPLOYEE)

#### Properties

Unary, i.e. used on one relation

Applied to each tuple individually

Degree of resulting relation = degree (# of attr) of orig relation

# tuples in result <= # tuples in original relation

#### Selectivity of condition

Faction of tuple get selected

Ration: # of selected tuple / total #

tells the nature of tuples in your relation

increase access performance

tell you what common query and selectivity and can tune system

#### Communtative

DE <cond1> (DE <cond2> (R))

= DE <cond2> (DE <cond1> (R))

Also

DE <cond1> (DE <cond2> ( ... (DE <condn> (R)))

converted to single select

DE <cond1> and <cond2> and ... and <condn> (R)

#### Project PI

Select certain columns. Reject the resst

E.g. PI (LNAME, FNAME, SALARY) (EMPLOYEE)

Not Communtative

PI AB (PI ABC (R))

does not equal to

PI ABC (PI AB (R))

#### In General

PI <attribute list> (<Relation name>)

size -> number of tuples

size(PI (R)) <= size (R)

Degree (resulting rel T1) <= Degree (R)

PI <attribute list> (R) never eq Size(R)

#### Combining DE & PI

P157 Fig 6.9

PI fname, lname, salary (DE dno=5 (EMPLOYEE))

DE dno = 5 (PI fname, lname, salary) (EMPLOYEE)

#### SET THEORETIC REL EXP

Use standard math set ops

Binary op: # operands = 2

• Union U
• Intersection
• Difference -

#### Validity

Validity of results of such operators

=> 2 reln must have same tuple types (aka union compatibility)

=> same degree

=> same domains

Union:

R1 U R2 => all tuples in R1 or in R2 or in both R1 & R2 (duplicates eliminated)

Intersection:

R1 . R2 => all tuples in both R1 and R2

Difference = R1 R2, all tuples in R1 that are not in R2

(P 159, Fig 6.11)

#### Communtative

Union - Yes

Intersection - Yes

R1 U R2 = R2 U R1

R1 . R2 = R2 . R1

Difference is not commutative

#### Associative

R1 U (R2 U R3) = (R1 U R2) U R3

R1 . (R2 . R3) = (R1 . R2) . R3

(R1 U R2) . R3 not eq R1 U (R2 . R3)

#### Cartesian Product (X)

aka cross product

combine all tuples

union compatability not required

Ra: m

Rb: n

A X B => m x n