Relational Model and Algebra





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



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)


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



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

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


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



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


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


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


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



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


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

=> FK refer to R2



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



insert - new tuples in a relation

delete - tuples

modify - change values of some attributes



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


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

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



Reject operation - inform user

Perform interactive correction to prompt user for correct info.



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.



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


Relational DB Operations


Select (Sigma)


In General

DE <select cond> (<relation name>)


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

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


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

E.g. DNO = 20

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

<const val> = from attributes domain


Allow Boolean Operators



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



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



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

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


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


Not Communtative


does not equal to



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)



Use standard math set ops

Binary op: # operands = 2



Validity of results of such operators

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

=> same degree

=> same domains



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



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)



Union - Yes

Intersection - Yes


R1 U R2 = R2 U R1

R1 . R2 = R2 . R1


Difference is not commutative



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