CS 245: Database System Principles

1
CPSC-608 Database Systems
Fall 2010
Instructor Jianer Chen Office HRBB 315C Phone
845-4259 Email chen_at_cse.tamu.edu
Notes 4
2
SQL Structured Query language

• How does SQL manipulate (read/write) tables?
  (continued)

3
in tables (relations)
database administrator
lock table
DDL complier
DDL language
file manager
logging recovery
concurrency control
transaction manager
database programmer
buffer manager
index/file manager
DML (query) language
query execution engine
DML complier
main memory buffers
secondary storage (disks)
DBMS
A Quick Review on Undergraduate Database
4
Products and Natural Joins

• Cross join (Cartesian Product) R CROSS JOIN S

5
Products and Natural Joins

• Natural join (join tuples agreeing on common
  attributes) R NATURAL JOIN S

6
Theta Join

• R JOIN S ON ltconditiongt
• Example using Drinkers(name, addr) and
  Frequents(drinker, bar)
• Drinkers JOIN Frequents ON
• name drinker
• gives us all (d, a, d, b) quadruples such that
  drinker d lives at address a and frequents bar b.

7
Theta Join

• R JOIN S ON ltconditiongt

8
Outerjoins

• R OUTER JOIN S is the core of an outerjoin
  expression. It is modified by
• Optional NATURAL in front of OUTER.
• Optional ON ltconditiongt after JOIN.
• Optional LEFT, RIGHT, or FULL before OUTER.
• LEFT pad dangling tuples of R only.
• RIGHT pad dangling tuples of S only.
• FULL pad both this choice is the default.

9
Outerjoins (Examples)

• R NATURAL FULL OUTER JOIN S
• R NATURAL LEFT OUTER JOIN S
• R NATURAL RIGHT OUTER JOIN S

10
Outerjoins (Examples)

• R NATURAL FULL OUTER JOIN S

• A B
• 2
• 3 9

• B C D
• 5 6
• 4 7 8

R
NATURAL FULL OUTER JOIN
S

• A B C D
• 1 2 5 6
• 9 N N
• N 4 7 8

11
Outerjoins (Examples)

• R NATURAL LEFT OUTER JOIN S

• A B
• 2
• 3 9

• B C D
• 5 6
• 4 7 8

R
NATURAL LEFT OUTER JOIN
S

• A B C D
• 1 2 5 6
• 9 N N

12
Outerjoins (Examples)

• R NATURAL RIGHT OUTER JOIN S

• A B
• 2
• 3 9

• B C D
• 5 6
• 4 7 8

R
NATURAL RIGHT OUTER JOIN
S
A B C D 1 2 5 6 N 4
7 8
13
Aggregations

• SUM, AVG, COUNT, MIN, and MAX can be applied to a
  column in a SELECT clause to produce that
  aggregation on the column.
• Also, COUNT() counts the number of tuples.

14
Example Aggregation

• From Sells(bar, beer, price), find the average
  price of Bud
• SELECT AVG(price)
• FROM Sells
• WHERE beer Bud

15
Eliminating Duplicates in an Aggregation

• Use DISTINCT inside an aggregation.
• Example find the number of different prices
  charged for Bud
• SELECT COUNT(DISTINCT price)
• FROM Sells
• WHERE beer Bud

16
NULLs Ignored in Aggregation

• NULL never contributes to a sum, average, or
  count, and can never be the minimum or maximum of
  a column.
• But if there are no non-NULL values in a column,
  then the result of the aggregation is NULL.

17
Example Effect of NULLs

• SELECT count()
• FROM Sells
• WHERE beer Bud
• SELECT count(price)
• FROM Sells
• WHERE beer Bud

18
Grouping

• We may follow a SELECT-FROM-WHERE expression by
  GROUP BY and a list of attributes.
• The relation that results from the
  SELECT-FROM-WHERE is grouped according to the
  values of all those attributes, and any
  aggregation is applied only within each group.

19
Example Grouping

• From Sells(bar, beer, price), find the average
  price for each beer
• SELECT beer, AVG(price)
• FROM Sells
• GROUP BY beer

20
Example Grouping

• From Sells(bar, beer, price) and
  Frequents(drinker, bar), find for each drinker
  the average price of Bud at the bars they
  frequent
• SELECT drinker, AVG(price)
• FROM Frequents, Sells
• WHERE beer Bud AND
• Frequents.bar Sells.bar
• GROUP BY drinker

21
Restriction on SELECT Lists With Aggregation

• If any aggregation is used, then each element of
  the SELECT list must be either
• Aggregated, or
• An attribute on the GROUP BY list.

22
Illegal Query Example

• You might think you could find the bar that sells
  Bud the cheapest by
• SELECT bar, MIN(price)
• FROM Sells
• WHERE beer Bud
• But this query is illegal in SQL.

23
HAVING Clauses

• HAVING ltconditiongt may follow a GROUP BY clause.
• If so, the condition applies to each group, and
  groups not satisfying the condition are
  eliminated.

24
Example. From Sells(bar, beer, price) and
Beers(name, manf), find the average price of
those beers that are either served in at least
three bars or are manufactured by Petes.

• SELECT beer, AVG(price)
• FROM Sells
• GROUP BY beer
• HAVING COUNT(bar) gt 3 OR
• beer IN (SELECT name
• FROM Beers
• WHERE manf Petes)

Beer groups with at least 3 non-NULL bars and
also beer groups where the manufacturer is Petes.
Beers manu- factured by Petes.
25
Requirements on HAVING Conditions

• These conditions may refer to any relation or
  tuple-variable in the FROM clause.
• They may refer to attributes of those relations,
  as long as the attribute makes sense within a
  group i.e., it is either
• A grouping attribute, or
• Aggregated.

26
Requirements on HAVING Conditions

• It is easier to understand this from an
  implementation viewpoint
• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING

step 5, output
step 1, input
step 2
step 3
step 4
27
Database Modifications

• A modification command does not return a result
  (as a query does), but changes the database in
  some way.
• Three kinds of modifications
• Insert a tuple or tuples.
• Delete a tuple or tuples.
• Update the value(s) of an existing tuple or
  tuples.

28
Insertion

• To insert a single tuple
• INSERT INTO ltrelationgt
• VALUES (ltlist of valuesgt)
• Example add to Likes(drinker, beer) the fact
  that Sally likes Bud.
• INSERT INTO Likes
• VALUES(Sally, Bud)

29
Specifying Attributes in INSERT

• We may add to the relation name a list of
  attributes.
• Two reasons to do so
• We forget the standard order of attributes for
  the relation.
• We dont have values for all attributes, and we
  want the system to fill in missing components
  with NULL or a default value.

30
Example Specifying Attributes

• Another way to add the fact that Sally likes Bud
  to Likes(drinker, beer)
• INSERT INTO Likes(beer, drinker)
• VALUES(Bud, Sally)

31
Inserting Many Tuples

• We may insert the entire result of a query into a
  relation, using the form
• INSERT INTO ltrelationgt
• (ltsubquerygt)

32
Example. Using Frequents(drinker, bar), enter
into the new relation PotBuddies(name) all of
Sallys potential buddies, i.e., those drinkers
who frequent at least one bar that Sally also
frequents.

• INSERT INTO PotBuddies
• (SELECT d2.drinker
• FROM Frequents d1, Frequents d2
• WHERE d1.drinker Sally AND
• d2.drinker ltgt Sally AND
• d1.bar d2.bar)

The other drinker
Pairs of Drinker tuples where the first is for
Sally, the second is for someone else, and the
bars are the same.
33
Deletion

• To delete tuples satisfying a condition from some
  relation
• DELETE FROM ltrelationgt
• WHERE ltconditiongt

34
Example Deletion

• Delete from Likes(drinker, beer) the fact that
  Sally likes Bud
• DELETE FROM Likes
• WHERE drinker Sally AND
• beer Bud

35
Example Delete all Tuples

• Make the relation Likes empty
• DELETE FROM Likes
• Note no WHERE clause needed.

36
Example Delete Many Tuples

• Delete from Beers(name, manf) all beers for which
  there is another beer by the same manufacturer.
• DELETE FROM Beers b
• WHERE EXISTS (
• SELECT name FROM Beers
• WHERE manf b.manf AND
• name ltgt b.name)

37
Semantics of Deletion (1)

• Suppose Anheuser-Busch makes only Bud and Bud
  Lite.
• Suppose we come to the tuple b for Bud first.
• The subquery is nonempty, because of the Bud Lite
  tuple, so we delete Bud.
• Now, when b is the tuple for Bud Lite, do we
  delete that tuple too?

38
Semantics of Deletion (2)

• Answer we do delete Bud Lite as well.
• The reason is that deletion proceeds in two
  stages
• Mark all tuples for which the WHERE condition is
  satisfied.
• Delete the marked tuples.

39
Updates

• To change certain attributes in certain tuples of
  a relation
• UPDATE ltrelationgt
• SET ltlist of attribute assignmentsgt
• WHERE ltcondition on tuplesgt

40
Example Update

• Change drinker Freds phone number to 555-1212
• UPDATE Drinkers
• SET phone [phone]
• WHERE name Fred

41
Example Update Several Tuples

• Make 4 the maximum price for beer
• UPDATE Sells
• SET price 4.00
• WHERE price gt 4.00

42

• Read Chapter 6 for more details.

43
Integrity in Data Definitions
44
in tables (relations)
database administrator
lock table
DDL complier
DDL language
file manager
logging recovery
concurrency control
transaction manager
database programmer
buffer manager
index/file manager
DML (query) language
query execution engine
DML complier
main memory buffers
secondary storage (disks)
DBMS
A Quick Review on Undergraduate Database
45
Constraints and Triggers

• A constraint is a relationship among data
  elements that the DBMS is required to enforce.
• A trigger is only executed when a specified
  condition occurs.

46
Kinds of Constraints

• Keys.
• Foreign-key (referential-integrity).
• Value-based constraints.
• Tuple-based constraints.
• Assertions any SQL boolean expression.

47
Foreign Keys

• A foreign key constraint on a set A of attributes
  in a relation R is such that
• A is a (primary or unique) key for another
  relation S
• Any value appearing in the A-column of relation R
  must also appear in the A-column in relation S

48
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 (ltlattributesgt)
• REFERENCES ltrelation-2gt (ltattributesgt)
• Referenced attributes (in relation-2) must be
  declared PRIMARY KEY or UNIQUE.

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

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

51
Actions Taken (1)

• Suppose R Sells, S Beers, and beer in Sells
  is a foreign key in Beers.
• An insert or update to Sells that introduces a
  nonexistent beer must be rejected.

52
Actions Taken (2)

• 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 the tuples in Sells.
• Updated beer change the values in Sells.
• Set NULL Change beer in Sells to NULL.

53
Choosing a Policy

• When we declare a foreign key, we may choose
  policies SET NULL or CASCADE for deletions and
  updates.
• Follow the foreign-key declaration by
• ON UPDATE,DELETESET NULL,CASCADE
• Otherwise, the default (reject) is used.

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

55
Attribute-Based Checks

• Constraints on the value of an attribute.
• Add CHECK (ltconditiongt) 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.

56
Example

• CREATE TABLE Sells (
• bar CHAR(20),
• beer CHAR(20)
• CHECK ( beer IN
• (SELECT name FROM Beers)),
• price REAL
• CHECK ( price lt 5.00 )
• )

57
Tuple-Based Checks

• CHECK (ltconditiongt) 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.

58
Example Tuple-Based Check

• Only Joes Bar can sell beer for more than 5
• CREATE TABLE Sells (
• bar CHAR(20),
• beer CHAR(20),
• price REAL,
• CHECK (bar Joes Bar OR
• price lt 5.00)
• )

59
Assertions

• These are database-schema elements, like
  relations or views.
• Defined by
• CREATE ASSERTION ltnamegt
• CHECK (ltconditiongt)
• Condition may refer to any relation or attribute
  in the database schema.

60
Example 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 lt AVG(price))
• )

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

62
Triggers Motivation

• Assertions are powerful, but the DBMS often cant
  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.

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

64
Preliminary 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.

65
Trigger an example

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

66
Trigger CREATE TRIGGER

• CREATE TRIGGER ltnamegt
• Option
• CREATE OR REPLACE TRIGGER ltnamegt
• Useful if there is a trigger with that name and
  you want to modify the trigger.

67
Trigger The Event

• AFTER can be BEFORE.
• Also, INSTEAD OF, if the relation is a view.
• A great way to execute view modifications have
  triggers translate them to appropriate
  modifications on the base tables.
• INSERT can be DELETE or UPDATE.
• And UPDATE can be UPDATE . . . ON a particular
  attribute.

68
Trigger FOR EACH ROW

• Triggers are either row-level or
  statement-level.
• FOR EACH ROW indicates row-level its absence
  indicates statement-level.
• Row level triggers execute once for each
  modified tuple.
• Statement-level triggers execute once for an
  SQL statement, regardless of how many tuples are
  modified.

69
Trigger REFERENCING

• INSERT statements imply a new tuple (for
  row-level) or new table (for statement-level).
• The table is the set of inserted tuples.
• DELETE implies an old tuple or table.
• UPDATE implies both.
• Refer to these by
• NEW OLDTUPLE TABLE AS ltnamegt

70
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 through
  the names declared in the REFERENCING clause.

71
Trigger The Action

• There can be more than one SQL statement in the
  action.
• Surround by BEGIN . . . END if there is more than
  one.
• But queries make no sense in an action, so we are
  really limited to modifications.

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

73
The Trigger

• CREATE TRIGGER PriceTrig
• AFTER UPDATE OF price ON Sells
• REFERENCING
• OLD ROW AS ooo
• NEW ROW AS nnn
• FOR EACH ROW
• WHEN (nnn.price gt ooo.price 1.00)
• INSERT INTO RipoffBars
• VALUES (nnn.bar)

74
Triggers on Views

• Generally, it is impossible to modify a view,
  because it doesnt exist.
• But an INSTEAD OF trigger lets us interpret view
  modifications in a way that makes sense.
• Example Well design a view Synergy that has
  (drinker, beer, bar) triples such that the bar
  serves the beer, the drinker frequents the bar
  and likes the beer.

75
Example The View

• 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

76
Interpreting a View Insertion

• We cannot insert into Synergy --- it is a view.
• 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.

77
The Trigger

• 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

78

• Read Chapter 7 for more details.

79
in tables (relations)
database administrator
lock table
DDL complier
DDL language
file manager
logging recovery
concurrency control
transaction manager
database programmer
buffer manager
index/file manager
query execution engine
DML complier
main memory buffers
DML (query) language
secondary storage (disks)
DBMS
A Quick Review on Undergraduate Database
80
database administrator
lock table
DDL complier
DDL language
file manager
logging recovery
concurrency control
transaction manager
database programmer
buffer manager
index/file manager
query execution engine
DML complier
main memory buffers
DML (query) language
secondary storage (disks)
DBMS
Gradiate Database
81
database administrator
lock table
DDL complier
DDL language
file manager
logging recovery
concurrency control
transaction manager
database programmer
buffer manager
index/file manager
query execution engine
DML complier
main memory buffers
DML (query) language
secondary storage (disks)
DBMS
Gradiate Database