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

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

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

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

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

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

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

data types - int, float, char ...

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

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

= { 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.

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).

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

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.

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)

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)

Operations could be divided into

Set operation (math set theory) OR

Relational DB operations

- union
- intersection
- difference
- cartestion product

- select
- project
- join, etc.

- 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)

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

AND, OR, NOT

E.g.

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

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))

Also

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

converted to single select

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

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))

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)

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

- Union U
- Intersection
- Difference -

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)

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)

aka cross product

combine all tuples

union compatability not required

Ra: m

Rb: n

A X B => m x n