SQL Constraints and Triggers in Database Systems

CSCE-608 Database Systems

Spring 2024

Instructor: Jianer Chen

Office: PETR 428

Phone: 845-4259

Email: chen@cse.tamu.edu

Notes 13: SQL – Constraints and triggers

SQL: Structured Query language



a very-high-level language.

* 

say “

what to do

” rather than “

how to do it

.”

* avoid a 

lot of data-manipulation details 

   needed in procedural languages like C++ 

   or Java.



Database management system figures

out the “

best

” way to execute queries 

* called “

query optimization

”



For both data definition and data

manipulation.

Database is just another model

of information processing

Data

(in disks)



Then why study DB?



Much more data,

regular data techs would

be very inefficient: How

should data be stored?



Operations are simpler &

more specific: How do we

take advantage of it?



New programming

languages for the above.



(ACID) Reliability, security,

consistency, currency

SQL

π

, 

σ

, 

ρ

,

∩

, 

⋃

,

 \

,

╳

,

⋈

,

 ⋈

C

Constraints and Triggers



A constraint is a relationship among

data elements that the DBMS is

required to enforce.

-- e.g., key constraints.



A trigger is an action only executed

when a specified condition occurs, e.g.,

insertion of a tuple.

-- easier to implement than complex 

   constraints.

Constraints and Triggers



A 

constraint 

is a relationship among

data elements that the DBMS is

required to enforce.

-- e.g., key constraints.



A trigger is an action only executed

when a specified condition occurs, e.g.,

insertion of a tuple.

-- easier to implement than complex 

   constraints.

Kinds of Constraints



Keys 

(unique, cannot be NULL).



Foreign-key

-- referential-integrity.



Value-based

 constraints.

-- constrain values of a particular attribute.



Tuple-based

 constraints.

-- relationship among components.



Assertions

.

-- any SQL boolean expression.

Kinds of Constraints



Keys 

(unique, cannot be NULL).



Foreign-key

-- referential-integrity.



Value-based

 constraints.

-- constrain values of a particular attribute.



Tuple-based

 constraints.

-- relationship among components.



Assertions

.

-- any SQL boolean expression.

8

Foreign Keys



Consider the relation 

Sells(

bar

, 

beer

, price)

.



We might expect that a 

beer

 value in

Sells

 is a real beer --- something

appearing in 

Beers.name

.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

9

Foreign Keys



Consider the relation 

Sells(

bar

, 

beer

, price)

.



We might expect that a 

beer

 value in

Sells

 is a real beer --- something

appearing in 

Beers.name

.



A constraint that requires a 

beer

 in

Sells

 to be a 

beer

 in 

Beers 

is called a

foreign-key 

constraint.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

10

Expressing Foreign Keys



Use the keyword 

REFERENCES

, either:

-- Within the declaration of an attribute (only

    for one-attribute keys).

-- As an element of the schema:

FOREIGN KEY 

(<attributes>)

REFERENCES

<relation> (<attributes>)

11

Expressing Foreign Keys



Use the keyword 

REFERENCES

, either:

-- Within the declaration of an attribute (only

    for one-attribute keys).

-- As an element of the schema:

FOREIGN KEY 

(<attributes>)

REFERENCES

<relation> (<attributes>)



Referenced attributes must be declared

PRIMARY KEY 

or 

UNIQUE

 in 

<relation>

.

12

Example: With Attribute

CREATE TABLE 

Beers

 (

name

CHAR(20) PRIMARY KEY

,

manf

CHAR(20)

 );

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

13

Example: With Attribute

CREATE TABLE 

Beers

 (

name

CHAR(20) PRIMARY KEY

,

manf

CHAR(20)

 );

CREATE TABLE 

Sells

 (

bar

CHAR(20)

,

beer

CHAR(20)

REFERENCES

Beers(name)

,

price

REAL

 );

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

14

Example: With Attribute

CREATE TABLE 

Beers

 (

name

CHAR(20) PRIMARY KEY

,

manf

CHAR(20)

 );

CREATE TABLE 

Sells

 (

bar

CHAR(20)

,

beer

CHAR(20)

REFERENCES

Beers(name)

,

price

REAL

 );

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

15

Example: As Element

CREATE TABLE 

Beers

 (

name

CHAR(20) PRIMARY KEY

,

manf

CHAR(20)

 );

CREATE TABLE 

Sells

 (

bar

CHAR(20)

,

price

REAL

,

FOREIGN KEY

(beer)

REFERENCES

Beers(name)

);

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

16

Example: As Element

CREATE TABLE 

Beers

 (

name

CHAR(20) PRIMARY KEY

,

manf

CHAR(20)

 );

CREATE TABLE 

Sells

 (

bar

CHAR(20)

,

price

REAL

,

FOREIGN KEY

(beer)

REFERENCES

Beers(name)

);

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

17

Example: As Element

CREATE TABLE 

Beers

 (

name

CHAR(20) PRIMARY KEY

,

manf

CHAR(20)

 );

CREATE TABLE 

Sells

 (

bar

CHAR(20)

,

price

REAL

,

FOREIGN KEY

(beer)

REFERENCES

Beers(name)

);

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

can be a list 

of more than 

one attributes

18

Example: As Element

CREATE TABLE 

Beers

 (

name

CHAR(20) PRIMARY KEY

,

manf

CHAR(20)

 );

CREATE TABLE 

Sells

 (

bar

CHAR(20)

,

price

REAL

,

FOREIGN KEY

(beer)

REFERENCES

Beers(name)

);

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

can be a list 

of more than 

one attributes

Remark.

Attributes in 

a foreign key 

MAY

 have 

value NULL

19

Enforcing Foreign-Key Constraints



If there is a foreign-key constraint from

attributes of relation 

R

 to a key of

relation 

S

, two violations are possible:

20

Enforcing Foreign-Key Constraints



If there is a foreign-key constraint from

attributes of relation 

R

 to a key of

relation 

S

, two violations are possible:

-- An insert or update to 

R

 introduces values

not found in 

S

.

21

Enforcing Foreign-Key Constraints



If there is a foreign-key constraint from

attributes of relation 

R

 to a key of

relation 

S

, two violations are possible:

-- An insert or update to 

R

 introduces values

not found in 

S

.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

Sells (R)

Beers (S)

22

Enforcing Foreign-Key Constraints



If there is a foreign-key constraint from

attributes of relation 

R

 to a key of

relation 

S

, two violations are possible:

-- An insert or update to 

R

 introduces values

not found in 

S

.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

Sells (R)

Beers (S)

No Bud Lite

23

Enforcing Foreign-Key Constraints



If there is a foreign-key constraint from

attributes of relation 

R

 to a key of

relation 

S

, two violations are possible:

-- An insert or update to R introduces values

not found in S.

-- A deletion or update to 

S

 causes some

tuples of 

R

 to “dangle.”

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

Sells (R)

Beers (S)

Sells (R)

Beers (S)

No Bud Lite

24

Enforcing Foreign-Key Constraints



If there is a foreign-key constraint from

attributes of relation 

R

 to a key of

relation 

S

, two violations are possible:

-- An insert or update to R introduces values

not found in S.

-- A deletion or update to 

S

 causes some

tuples of 

R

 to “dangle.”

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

Sells (R)

Beers (S)

Sells (R)

Beers (S)

?

No Bud Lite

25

Actions Taken



Suppose 

R

 = 

Sells

, 

S

 = 

Beers

.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

26

Actions Taken



Suppose 

R

 = 

Sells

, 

S

 = 

Beers

.



An insert or update to 

Sells

 that introduces

a nonexistent beer 

must be 

rejected.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

27

Actions Taken



Suppose 

R

 = 

Sells

, 

S

 = 

Beers

.



An insert or update to 

Sells

 that introduces

a nonexistent beer 

must be 

rejected.



A deletion or update to 

Beers

 that removes

a beer value found in some tuples of 

Sells

can be handled in three ways:

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

28

Actions Taken



Suppose 

R

 = 

Sells

, 

S

 = 

Beers

.



An insert or update to 

Sells

 that introduces

a nonexistent beer 

must be 

rejected.



A deletion or update to 

Beers

 that removes

a beer value found in some tuples of 

Sells

can be handled in three ways:

--

 Default

: Reject the modification.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

29

Actions Taken



Suppose 

R

 = 

Sells

, 

S

 = 

Beers

.



An insert or update to 

Sells

 that introduces

a nonexistent beer 

must be 

rejected.



A deletion or update to 

Beers

 that removes

a beer value found in some tuples of 

Sells

can be handled in three ways:

--

 Default

: Reject the modification.

--

 Cascade

: Make the same changes in 

Sells

.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

30

Actions Taken



Suppose 

R

 = 

Sells

, 

S

 = 

Beers

.



An insert or update to 

Sells

 that introduces

a nonexistent beer 

must be 

rejected.



A deletion or update to 

Beers

 that removes

a beer value found in some tuples of 

Sells

can be handled in three ways:

--

 Default

: Reject the modification.

--

 Cascade

: Make the same changes in 

Sells

.

    * Deleted beer: delete 

Sells

 tuple.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

31

Actions Taken



Suppose 

R

 = 

Sells

, 

S

 = 

Beers

.



An insert or update to 

Sells

 that introduces

a nonexistent beer 

must be 

rejected.



A deletion or update to 

Beers

 that removes

a beer value found in some tuples of 

Sells

can be handled in three ways:

--

 Default

: Reject the modification.

--

 Cascade

: Make the same changes in 

Sells

.

    * Deleted beer: delete 

Sells

 tuple.

    * Updated beer: change value in 

Sells

.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

32

Actions Taken



Suppose 

R

 = 

Sells

, 

S

 = 

Beers

.



An insert or update to 

Sells

 that introduces

a nonexistent beer 

must be 

rejected.



A deletion or update to 

Beers

 that removes

a beer value found in some tuples of 

Sells

can be handled in three ways:

--

 Default

: Reject the modification.

--

 Cascade

: Make the same changes in 

Sells

.

    * Deleted beer: delete 

Sells

 tuple.

    * Updated beer: change value in 

Sells

.

--

 Set NULL

: Change the beer (in 

Sells

) to NULL.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

33

Example: Cascade

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

34

Example: Cascade



Delete the Bud tuple from 

Beers

:

-- 

Then delete all tuples from 

Sells

 that have 

beer

    = ’Bud’.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

35

Example: Cascade



Delete the Bud tuple from 

Beers

:

-- 

Then delete all tuples from 

Sells

 that have 

beer

    = ’Bud’.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

Beers (S)

Sells (R)

36

Example: Cascade



Delete the Bud tuple from 

Beers

:

-- 

Then delete all tuples from 

Sells

 that have 

beer

    = ’Bud’.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

Beers (S)

Sells (R)

37

Example: Cascade



Delete the Bud tuple from 

Beers

:

-- 

Then delete all tuples from 

Sells

 that have 

beer

    = ’Bud’.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

Beers (S)

Sells (R)

38

Example: Cascade



Delete the Bud tuple from 

Beers

:

-- 

Then delete all tuples from 

Sells

 that have 

beer

    = ’Bud’.



Update the Bud tuple by changing ’Bud’ to

’Budweiser’:

-- Then change all 

Sells

 tuples with 

beer

 = ’Bud’ so

   that 

beer

 = ’Budweiser’.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

Beers (S)

Sells (R)

39

Example: Cascade



Delete the Bud tuple from 

Beers

:

-- 

Then delete all tuples from 

Sells

 that have 

beer

    = ’Bud’.



Update the Bud tuple by changing ’Bud’ to

’Budweiser’:

-- Then change all 

Sells

 tuples with 

beer

 = ’Bud’ so

   that 

beer

 = ’Budweiser’.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

Sells (R)

Beers (S)

Beers (S)

Sells (R)

40

Example: Cascade



Delete the Bud tuple from 

Beers

:

-- 

Then delete all tuples from 

Sells

 that have 

beer

    = ’Bud’.



Update the Bud tuple by changing ’Bud’ to

’Budweiser’:

-- Then change all 

Sells

 tuples with 

beer

 = ’Bud’ so

   that 

beer

 = ’Budweiser’.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

Sells (R)

Beers (S)

Budweiser

Beers (S)

Sells (R)

41

Example: Cascade



Delete the Bud tuple from 

Beers

:

-- 

Then delete all tuples from 

Sells

 that have 

beer

    = ’Bud’.



Update the Bud tuple by changing ’Bud’ to

’Budweiser’:

-- Then change all 

Sells

 tuples with 

beer

 = ’Bud’ so

   that 

beer

 = ’Budweiser’.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

Sells (R)

Beers (S)

Budweiser

Budweiser

Budweiser

Beers (S)

Sells (R)

42

Example: Set NULL

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

43

Example: Set NULL



Delete the Bud tuple from Beers:

-- Change all tuples of 

Sells

 that have 

beer

 = ’Bud’

    to have 

beer

 = NULL.

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

44

Example: Set NULL



Delete the Bud tuple from Beers:

-- Change all tuples of 

Sells

 that have 

beer

 = ’Bud’

    to have 

beer

 = NULL.

Beers (S)

Sells (R)

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

45

Example: Set NULL



Delete the Bud tuple from Beers:

-- Change all tuples of 

Sells

 that have 

beer

 = ’Bud’

    to have 

beer

 = NULL.

Beers (S)

Sells (R)

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

46

Example: Set NULL



Delete the Bud tuple from Beers:

-- Change all tuples of 

Sells

 that have 

beer

 = ’Bud’

    to have 

beer

 = NULL.

Beers (S)

Sells (R)

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

NULL

NULL

47

Example: Set NULL



Delete the Bud tuple from Beers:

-- Change all tuples of Sells that have beer = ’Bud’

    to have beer = NULL.



Update the Bud tuple by changing ’Bud’ to

’Budweiser’:

-- the same change as above.

Beers (S)

Sells (R)

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

NULL

NULL

48

Example: Set NULL



Delete the Bud tuple from Beers:

-- Change all tuples of Sells that have beer = ’Bud’

    to have beer = NULL.



Update the Bud tuple by changing ’Bud’ to

’Budweiser’:

-- the same change as above.

Sells (R)

Beers (S)

Beers (S)

Sells (R)

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

NULL

NULL

49

Example: Set NULL



Delete the Bud tuple from Beers:

-- Change all tuples of Sells that have beer = ’Bud’

    to have beer = NULL.



Update the Bud tuple by changing ’Bud’ to

’Budweiser’:

-- the same change as above.

Sells (R)

Beers (S)

Budweiser

Beers (S)

Sells (R)

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

NULL

NULL

50

Example: Set NULL



Delete the Bud tuple from Beers:

-- Change all tuples of Sells that have beer = ’Bud’

    to have beer = NULL.



Update the Bud tuple by changing ’Bud’ to

’Budweiser’:

-- the same change as above.

Sells (R)

Beers (S)

Budweiser

NULL

NULL

Beers (S)

Sells (R)

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

NULL

NULL

51

Choosing a Policy



When we declare a foreign key, we may

choose policies 

SET NULL 

or 

CASCADE

independently for deletions and updates.

52

Choosing a Policy



When we declare a foreign key, we may

choose policies 

SET NULL 

or 

CASCADE

independently for deletions and updates.



Follow the foreign-key declaration by:

ON [UPDATE, DELETE][SET NULL, CASCADE]

53

Choosing a Policy



When we declare a foreign key, we may

choose policies 

SET NULL 

or 

CASCADE

independently for deletions and updates.



Follow the foreign-key declaration by:

ON [UPDATE, DELETE][SET NULL, CASCADE]



Two such clauses may be used.

54

Choosing a Policy



When we declare a foreign key, we may

choose policies 

SET NULL 

or 

CASCADE

independently for deletions and updates.



Follow the foreign-key declaration by:

ON [UPDATE, DELETE][SET NULL, CASCADE]



Two such clauses may be used.



Otherwise, the default (reject) is used.

55

Example

CREATE TABLE 

Sells

 (

bar

CHAR

(20),

beer

CHAR

(20),

price

REAL

,

FOREIGN KEY

(

beer

)

REFERENCES

Beers

(

name

)

ON DELETE SET NULL

ON UPDATE CASCADE

);

Sells(

bar

, 

beer

, price)

Beers(

name

, manf)

Attribute-Based Checks

Attribute-Based Checks



Constraints on the value of an

attribute.

Attribute-Based Checks



Constraints on the value of an

attribute.



Add: 

CHECK

 (<condition>)

 to the

declaration for the attribute.

Attribute-Based Checks



Constraints on the value of an

attribute.



Add: 

CHECK

 (<condition>)

 to the

declaration for the attribute.



The condition may use the name of

the attribute, but any other relation

or attribute name must be in a

subquery.

Example: 

Attribute-Based Check

CREATE TABLE

 Sells (

bar

CHAR

(20),

beer

CHAR

(20)

CHECK

 ( beer 

IN

(

SELECT

 name 

FROM

 Beers)),

price

REAL

CHECK

 ( price <= 5.00 )

);

Beers(

name

, manf)

Sells(

bar

, 

beer

, price)

61

Timing of Attribute-based Checks

62



Attribute-based checks performed only

when a value for that attribute is

inserted or updated.

--

Example:

CHECK

(price <= 5.00) 

checks every 

    new price and rejects the modification (for that 

    tuple) if the price is more than $5.

Timing of Attribute-based Checks

63



Attribute-based checks performed only

when a value for that attribute is

inserted or updated.

--

Example:

CHECK

(price <= 5.00) 

checks every 

    new price and rejects the modification (for that 

    tuple) if the price is more than $5.

--

Example: 

(in the relation 

Sells(

bar

, 

beer

, price)

)

CHECK

(beer

IN

(

SELECT

name

FROM

Beers))

    is 

not 

checked if a beer is deleted from 

Beers

 -- 

    it is only checked for 

Sells

 (unlike foreign-keys).

Timing of Attribute-based Checks

Tuple-Based Checks



CHECK

 (<condition>)

 may be added

as a relation-schema element.



The condition may refer to any

attribute of the relation (but any other

attributes or relations require a

subquery).



Checked on insert or update only.

Example: 

Tuple-Based Check



Only Joe’s Bar can sell beer for more than $5

CREATE TABLE

 Sells (

bar

CHAR

(20),

beer

CHAR

(20),

price

REAL

,

CHECK

 (bar = ’Joe’’s Bar’ 

OR

price <= 5.00)

);

Sells(

bar

, 

beer

, price)

Cross-Relation Constraints



Remark.

 All constraints we discussed so far

are given in the declaration of a relation,

i.e., in a 

CREATE TABLE 

statement.

Cross-Relation Constraints



Remark.

 All constraints we discussed so far

are given in the declaration of a relation,

i.e., in a 

CREATE TABLE 

statement.



Constraints can also be given as database-

schema elements, like relations or views.



Assertions:

CREATE ASSERTION

 <name>

CHECK 

(<condition>);



Condition may refer to any relation or

attribute in the database schema.

Example 1:

 Assertion



In 

Sells(bar, beer, price),

 no bar may charge

an average of more than $5.

Sells(

bar

, 

beer

, price)

Example 1:

 Assertion



In 

Sells(bar, beer, price),

 no bar may charge

an average of more than $5.

CREATE ASSERTION 

NoRipoffBars

CHECK

(

NOT EXISTS

 (

SELECT

 bar 

FROM 

Sells

GROUP BY

 bar

HAVING 5.00

 < 

AVG

(price))

);

Sells(

bar

, 

beer

, price)

bars with an 

average price 

above $5

no bar can have 

an average price 

larger than $5

An constraint that no bar can have 

an average price larger than $5

Example 1:

 Assertion



In 

Sells(bar, beer, price),

 no bar may charge

an average of more than $5.

CREATE ASSERTION 

NoRipoffBars

CHECK

(

NOT EXISTS

 (

SELECT

 bar 

FROM 

Sells

GROUP BY

 bar

HAVING 5.00

 < 

AVG

(price))

);

Sells(

bar

, 

beer

, price)

An constraint that no bar can have 

an average price larger than $5

71

Example 2:

 Assertion



In 

Drinkers(name, addr, phone) 

and

Bars(name, addr, license), 

there cannot be

more bars than drinkers.

CREATE ASSERTION 

FewBar

CHECK

(

(

SELECT

COUNT

(*)

FROM

Bars) <=

(

SELECT COUNT

(*)

FROM

Drinkers)

);

Drinkers(

name

, addr, phone)

Bars(

name

, addr, license)

72

Timing of Assertion Checks



In principle, we must check every assertion

after every modification to any relation of the

database.

73

Timing of Assertion Checks



In principle, we must check every assertion

after every modification to any relation of the

database.



A clever system can observe that only certain

changes could cause a given assertion to be

violated.

-- Example: 

No change to 

Beers

 can affect 

FewBar

.  

   Neither can an insertion to 

Drinkers

.

CREATE ASSERTION 

FewBar

CHECK

(

(

SELECT

COUNT

(*)

FROM

Bars) <=

(

SELECT COUNT

(*)

FROM

Drinkers)

);

Constraints and Triggers

Constraints and Triggers



A 

constraint 

is a relationship among

data elements that the DBMS is

required to enforce.

-- e.g., key constraints.



A trigger is an action only executed

when a specified condition occurs, e.g.,

insertion of a tuple.

-- easier to implement than complex 

   constraints.

Constraints and Triggers



A constraint is a relationship among

data elements that the DBMS is

required to enforce.

-- e.g., key constraints.



A

 trigger

 is an action only executed

when a specified condition occurs, e.g.,

insertion of a tuple.

-- easier to implement than complex 

   constraints.

Triggers:

 Motivation



Assertions

 are powerful, but the DBMS

often can’t tell when they need to be

checked.

Triggers:

 Motivation



Assertions

 are powerful, but the DBMS

often can’t tell when they need to be

checked.



Attribute-

 and 

tuple-based

 checks are

checked at known times, but are not

powerful.

Triggers:

 Motivation



Assertions

 are powerful, but the DBMS

often can’t tell when they need to be

checked.



Attribute-

 and 

tuple-based

 checks are

checked at known times, but are not

powerful.



Triggers

 let the user decide when to

check for a powerful condition.

Event-Condition-Action Rules



Another name for 

trigger

 is 

ECA rule

,

or 

event-condition-action 

rule.

Event-Condition-Action Rules



Another name for 

trigger

 is 

ECA rule

,

or 

event-condition-action 

rule.



Event:

  typically a type of database

modification, e.g., “insert on Sells.”

Event-Condition-Action Rules



Another name for 

trigger

 is 

ECA rule

,

or 

event-condition-action 

rule.



Event:

  typically a type of database

modification, e.g., “insert on Sells.”



Condition:

 Any SQL boolean-valued

expression.

Event-Condition-Action Rules



Another name for 

trigger

 is 

ECA rule

,

or 

event-condition-action 

rule.



Event:

  typically a type of database

modification, e.g., “insert on Sells.”



Condition:

 Any SQL boolean-valued

expression.



Action:

 Any SQL statements.

Example: 

A Trigger



Instead of using a foreign-key constraint and

rejecting insertions into 

Sells(bar, beer, price)

with unknown beers, a trigger can add that

beer

 to 

Beers

, with a 

NULL

 manufacturer.

Beers(

name

, manf)

Sells(

bar

, 

beer

, price)

Example: 

A Trigger



Instead of using a foreign-key constraint and

rejecting insertions into 

Sells(bar, beer, price)

with unknown beers, a trigger can add that

beer

 to 

Beers

, with a 

NULL

 manufacturer.

Beers(

name

, manf)

Sells(

bar

, 

beer

, price)

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

   (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES

(NewTuple.beer);

Example: 

A Trigger



Instead of using a foreign-key constraint and

rejecting insertions into 

Sells(bar, beer, price)

with unknown beers, a trigger can add that

beer

 to 

Beers

, with a 

NULL

 manufacturer.

Beers(

name

, manf)

Sells(

bar

, 

beer

, price)

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

   (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

    VALUES(NewTuple.beer);

the event

the condition

the action

Trigger:

 CREATE TRIGGER



CREATE TRIGGER

<name>

Trigger:

 CREATE TRIGGER



CREATE TRIGGER

<name>



Option:

  CREATE OR REPLACE TRIGGER

<name>

-- Useful if there is a trigger with that name

   and you want to modify the trigger.

Trigger:

 The Event

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);



AFTER

 can be 

BEFORE

.

Trigger:

 The Event

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

 The Event



AFTER

 can be 

BEFORE

.

-- also, can be 

INSTEAD OF

 if the relation is a 

view

   (to be discussed later).

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

 The Event



AFTER

 can be 

BEFORE

.

-- also, can be 

INSTEAD OF

 if the relation is a 

view

(to be discussed later).



INSERT

 can be 

DELETE

 or 

UPDATE

.

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

 The Event



AFTER

 can be 

BEFORE

.

-- also, can be 

INSTEAD OF

 if the relation is a 

view

(to be discussed later).



INSERT

 can be 

DELETE

 or 

UPDATE

.

-- 

UPDATE ON

<relation> 

can be 

UPDATE OF

<attribute> 

ON

<relation> 

for a

    particular 

<attribute> 

of the 

<relation>

.

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

Row-Level and

Statement-Level

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

Row-Level and

Statement-Level



Triggers are either “

row-level

” or “

statement-

level

.”

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

Row-Level and

Statement-Level



Triggers are either “

row-level

” or “

statement-

level

.”



FOR EACH ROW

 indicates row-level; its

absence indicates statement-level (can also

be 

FOR EACH STATEMENT

).

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

Row-Level and

Statement-Level



Triggers are either “

row-level

” or “

statement-

level

.”



FOR EACH ROW

 indicates row-level; its

absence indicates statement-level (can also

be 

FOR EACH STATEMENT

).



Row-level triggers: 

execute once for each

modified tuple.

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

Row-Level and

Statement-Level



Triggers are either “

row-level

” or “

statement-

level

.”



FOR EACH ROW

 indicates row-level; its

absence indicates statement-level (can also

be 

FOR EACH STATEMENT

).



Row-level triggers: 

execute once for each

modified tuple.



Statement-level triggers: 

execute once for an

SQL statement (many tuples can be modified)

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

REFERENCING

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

REFERENCING



INSERT

 implies a new row (tuple) (for row-

level) or a new table (for statement-level).

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

REFERENCING



INSERT

 implies a new row (tuple) (for row-

level) or a new table (for statement-level).



DELETE

 implies an old tuple or table.

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

REFERENCING



INSERT

 implies a new row (tuple) (for row-

level) or a new table (for statement-level).



DELETE

 implies an old tuple or table.



UPDATE

 implies both.

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

REFERENCING



INSERT

 implies a new row (tuple) (for row-

level) or a new table (for statement-level).



DELETE

 implies an old tuple or table.



UPDATE

 implies both.



Refer to these by

   REFERENCING

 [

NEW OLD

] [

ROW TABLE

] 

AS

 <name>

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

REFERENCING



INSERT

 implies a new row (tuple) (for row-

level) or a new table (for statement-level).



DELETE

 implies an old tuple or table.



UPDATE

 implies both.



Refer to these by

   REFERENCING

 [

NEW OLD

] [

ROW TABLE

] 

AS

 <name>



For statement-level trigger, the 

<name>

above is used as a relation (table)

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

The Condition

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

The Condition



Any boolean-valued condition is appropriate.

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

The Condition



Any boolean-valued condition is appropriate.



It is evaluated before or after the triggering

event, depending on whether 

BEFORE

 or

AFTER

 is used in the event.

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

The Condition



Any boolean-valued condition is appropriate.



It is evaluated before or after the triggering

event, depending on whether 

BEFORE

 or

AFTER

 is used in the event.



Access the new/old tuple or set of tuples

(i.e., the table) through the names declared

in the 

REFERENCING

 clause.

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

The Action

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

The Action



There can be more than one SQL

statement in the action (surround by

BEGIN 

. . .

 END

 if there are more than

one statements).

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Trigger:

The Action



There can be more than one SQL

statement in the action (surround by

BEGIN 

. . .

 END

 if there are more than

one statements).



But queries make no sense in an action,

so we are really limited to modifications.

CREATE TRIGGER

 BeerTrig

AFTER INSERT ON

 Sells

REFERENCING NEW ROW AS

 NewTuple

FOR EACH ROW

WHEN

 (NewTuple.beer 

NOT IN

 (

SELECT

 name 

FROM

 Beers))

INSERT INTO

 Beers(name)

VALUES(NewTuple.beer);

Another Example



Using 

Sells(bar, beer, price) 

and a unary

relation 

RipoffBars(bar)

 created for the

purpose, maintain a list of bars that raise the

price of any beer by more than $1.

Another Example



Using 

Sells(bar, beer, price) 

and a unary

relation 

RipoffBars(bar)

 created for the

purpose, maintain a list of bars that raise the

price of any beer by more than $1.

CREATE TRIGGER 

PriceTrig

AFTER UPDATE OF 

price

 ON 

Sells

REFERENCING

OLD ROW AS 

ooo

NEW ROW AS 

nnn

FOR EACH ROW

WHEN 

(nnn.price > ooo.price + 1.00)

INSERT INTO 

RipoffBars

VALUES 

(nnn.bar);

Another Example



Using 

Sells(bar, beer, price) 

and a unary

relation 

RipoffBars(bar)

 created for the

purpose, maintain a list of bars that raise the

price of any beer by more than $1.

CREATE TRIGGER 

PriceTrig

AFTER UPDATE OF 

price

 ON 

Sells

REFERENCING

OLD ROW AS 

ooo

NEW ROW AS 

nnn

FOR EACH ROW

WHEN 

(nnn.price > ooo.price + 1.00)

INSERT INTO 

RipoffBars

VALUES 

(nnn.bar);

event: only when

price

 is changed

condition: 

price

is raised by > $1

action: record the

bar in 

RipoffBars

.

row-level trigger:

process whenever

price

 for a tuple in

Sells

 is updated

UPDATE

 let us talk

about new and old

tuples (rows).

Views



A 

view

 is a “virtual table” = a relation

defined in terms of other tables and views.



Declare by:

CREATE VIEW

<name>

AS

<subquery>

;

Views



A 

view

 is a “virtual table” = a relation

defined in terms of other tables and views.



Declare by:

CREATE VIEW

<name>

AS

<subquery>

;



Example:

CanDrink(drinker, beer) 

is a view made from relations

Sells(

bar

, 

beer

, price) 

and 

Frequents(

drinker

, 

bar

)

 that

“contains” the drinker-beer pairs such that the drinker

frequents at least one bar that serves the beer:

CREATE VIEW

 CanDrink

AS

SELECT

drinker, beer

FROM

Frequents, Sells

WHERE

Frequents.bar = Sells.bar;

Views



In general, it is impossible to modify a view,

because it doesn’t exist.

Triggers on Views

Sells(

bar

, 

beer

, price)

Likes(

drinker

, 

beer

)

Frequents(

drinker

, 

bar

)



In general, it is impossible to modify a view,

because it doesn’t exist.



But an 

INSTEAD OF

 trigger let us interpret

view modifications in a way that makes sense.

Triggers on Views

Sells(

bar

, 

beer

, price)

Likes(

drinker

, 

beer

)

Frequents(

drinker

, 

bar

)



In general, it is impossible to modify a view,

because it doesn’t exist.



But an 

INSTEAD OF

 trigger let us interpret

view modifications in a way that makes sense.



Example: we design a view 

Synergy

 with

tuples 

(drinker, beer, bar)

 such that the 

bar

serves the 

beer

, the 

drinker

 frequents the 

bar

and likes the 

beer

.

Triggers on Views

Sells(

bar

, 

beer

, price)

Likes(

drinker

, 

beer

)

Frequents(

drinker

, 

bar

)

CREATE VIEW 

Synergy

 AS

SELECT 

Likes.drinker, Likes.beer, Sells.bar

FROM 

Likes, Sells, Frequents

WHERE 

Likes.drinker = Frequents.drinker

AND 

Likes.beer = Sells.beer

AND 

Sells.bar = Frequents.bar;



In general, it is impossible to modify a view,

because it doesn’t exist.



But an 

INSTEAD OF

 trigger let us interpret

view modifications in a way that makes sense.



Example: we design a view 

Synergy

 with

tuples 

(drinker, beer, bar)

 such that the 

bar

serves the 

beer

, the 

drinker

 frequents the 

bar

and likes the 

beer

.

Triggers on Views

Sells(

bar

, 

beer

, price)

Likes(

drinker

, 

beer

)

Frequents(

drinker

, 

bar

)

CREATE VIEW 

Synergy

 AS

SELECT 

Likes.drinker, Likes.beer, Sells.bar

FROM 

Likes, Sells, Frequents

WHERE 

Likes.drinker = Frequents.drinker

AND 

Likes.beer = Sells.beer

AND 

Sells.bar = Frequents.bar;

Actually, a

natural join

of 

Likes

,

Sells

, and

Frequents

Inserting on a View



We cannot insert into 

Synergy

: it is 

a

view made from 

Sells

, 

Likes

, 

Frequents

.

Sells(

bar

, 

beer

, price)

Likes(

drinker

, 

beer

)

Frequents(

drinker

, 

bar

)

CREATE VIEW 

Synergy

 AS

SELECT 

Likes.drinker, Likes.beer, Sells.bar

FROM 

Likes, Sells, Frequents

WHERE 

Likes.drinker = Frequents.drinker

AND 

Likes.beer = Sells.beer

AND 

Sells.bar = Frequents.bar;

Inserting on a View



We cannot insert into 

Synergy

: it is 

a

view made from 

Sells

, 

Likes

, 

Frequents

.



But we can use an 

INSTEAD OF

 trigger

to turn a 

(drinker, beer, bar)

 triple into

three insertions of projected pairs, one

for each of 

Likes

, 

Sells

, and 

Frequents

.

Sells(

bar

, 

beer

, price)

Likes(

drinker

, 

beer

)

Frequents(

drinker

, 

bar

)

CREATE VIEW 

Synergy

 AS

SELECT 

Likes.drinker, Likes.beer, Sells.bar

FROM 

Likes, Sells, Frequents

WHERE 

Likes.drinker = Frequents.drinker

AND 

Likes.beer = Sells.beer

AND 

Sells.bar = Frequents.bar;

Inserting on a View

Sells(

bar

, 

beer

, price)

Likes(

drinker

, 

beer

)

Frequents(

drinker

, 

bar

)

CREATE VIEW 

Synergy

 AS

SELECT 

Likes.drinker, Likes.beer, Sells.bar

FROM 

Likes, Sells, Frequents

WHERE 

Likes.drinker = Frequents.drinker

AND 

Likes.beer = Sells.beer

AND 

Sells.bar = Frequents.bar;

CREATE TRIGGER

 ViewTrig

INSTEAD OF INSERT ON

 Synergy

REFERENCING NEW ROW AS

 n

FOR EACH ROW

BEGIN

INSERT INTO 

Likes

 VALUES

 (n.drinker, n.beer);

INSERT INTO

 Sells(bar, beer) 

VALUES

(n.bar, n.beer);

INSERT INTO

 Frequents 

VALUES

(n.drinker, n.bar);

END

;

Inserting on a View

Sells(

bar

, 

beer

, price)

Likes(

drinker

, 

beer

)

Frequents(

drinker

, 

bar

)

CREATE VIEW 

Synergy

 AS

SELECT 

Likes.drinker, Likes.beer, Sells.bar

FROM 

Likes, Sells, Frequents

WHERE 

Likes.drinker = Frequents.drinker

AND 

Likes.beer = Sells.beer

AND 

Sells.bar = Frequents.bar;

CREATE TRIGGER

 ViewTrig

INSTEAD OF INSERT ON

 Synergy

REFERENCING NEW ROW AS

 n

FOR EACH ROW

BEGIN

INSERT INTO 

Likes

 VALUES

 (n.drinker, n.beer);

INSERT INTO

 Sells(bar, beer) 

VALUES

(n.bar, n.beer);

INSERT INTO

 Frequents 

VALUES

(n.drinker, n.bar);

END

;

Inserting on a View

specifically 

for views

The value for 

Sells.price

 will

be the default

Sells(

bar

, 

beer

, price)

Likes(

drinker

, 

beer

)

Frequents(

drinker

, 

bar

)

CREATE VIEW 

Synergy

 AS

SELECT 

Likes.drinker, Likes.beer, Sells.bar

FROM 

Likes, Sells, Frequents

WHERE 

Likes.drinker = Frequents.drinker

AND 

Likes.beer = Sells.beer

AND 

Sells.bar = Frequents.bar;

CREATE TRIGGER

 ViewTrig

INSTEAD OF INSERT ON

 Synergy

REFERENCING NEW ROW AS

 n

FOR EACH ROW

BEGIN

INSERT INTO 

Likes

 VALUES

 (n.drinker, n.beer);

INSERT INTO

 Sells(bar, beer) 

VALUES

(n.bar, n.beer);

INSERT INTO

 Frequents 

VALUES

(n.drinker, n.bar);

END

;

Inserting on a View

Sells(

bar

, 

beer

, price)

Likes(

drinker

, 

beer

)

Frequents(

drinker

, 

bar

)

CREATE VIEW 

Synergy

 AS

SELECT 

Likes.drinker, Likes.beer, Sells.bar

FROM 

Likes, Sells, Frequents

WHERE 

Likes.drinker = Frequents.drinker

AND 

Likes.beer = Sells.beer

AND 

Sells.bar = Frequents.bar;

CREATE TRIGGER

 ViewTrig

INSTEAD OF INSERT ON

 Synergy

REFERENCING NEW ROW AS

 n

FOR EACH ROW

BEGIN

INSERT INTO 

Likes

 VALUES

 (n.drinker, n.beer);

INSERT INTO

 Sells(bar, beer) 

VALUES

(n.bar, n.beer);

INSERT INTO

 Frequents 

VALUES

(n.drinker, n.bar);

END

;

……

Inserting on a View

Sells(

bar

, 

beer

, price)

Likes(

drinker

, 

beer

)

Frequents(

drinker

, 

bar

)

CREATE VIEW 

Synergy

 AS

SELECT 

Likes.drinker, Likes.beer, Sells.bar

FROM 

Likes, Sells, Frequents

WHERE 

Likes.drinker = Frequents.drinker

AND 

Likes.beer = Sells.beer

AND 

Sells.bar = Frequents.bar;

In the same

database

schema

129

Database is just another model

of information processing

Data

(in disks)



Then why study DB?



Much more data,

regular data techs would

be very inefficient: How

should data be stored?



Operations are simpler &

more specific: How do we

take advantage of it?



New programming

languages for the above.



(ACID) Reliability, security,

consistency, currency

SQL

π

, 

σ

, 

ρ

,

∩

, 

⋃

,

 \

,

╳

,

⋈

,

 ⋈

C

130

Database is just another model

of information processing

Data

(in disks)



Then why study DB?



Much more data,

regular data techs would

be very inefficient: How

should data be stored?



Operations are simpler &

more specific: How do we

take advantage of it?



New programming

languages for the above.



(ACID) Reliability, security,

consistency, currency

SQL

π

, 

σ

, 

ρ

,

∩

, 

⋃

,

 \

,

╳

,

⋈

,

 ⋈

C