SQL Plus

1
Advanced Structured Query Language
2

• Table expressions in the from-clause
• from clause can contain
• base tables (from initial schema description)
  and
• query extractions

3
Table expressions remove need for
having-clause Example find the average tank
volume by species for those species found in 2
or more tanks.
Basic solution select S.sno, S.sname,
average(T.tvolume) as avgVol from Species S, Tank
T where exists (select from Fish F where
S.sno F.sno and F.tno T.tno) groupby
S.sno having count() gt 2
4
Restores relational algebra and calculus
capability --- calculate aggregates over same
set of equivalence classes --- for testing
and --- for reporting Find the average tank
volume by species for those species found in 2
or more tanks.
5
Table expressions also remove need for into-clause
Example find total tank volume by species, for
those species having 1000 or more fish
representatives.
6
Can substitute entire (select ...) construction
into from-clause
Another solution to find total tank volume by
species, for those species having 1000 or more
fish representatives.
select X.sno, X.sname, X.totVol from
TotVolBySpecies X, Fish G where X.sno
G.sno groupby X.sno having count() gt 1000
select S.sno, S.sname, sum(T.tvolume) as
totVol from Species, Tank T where exists (select
from Fish F where S.sno F.sno and F.tno
T.tno) groupby S.sno into TotVolBySpecies
7
Views --- virtual tables (vs. base table) ---
to use in query, substitute definition in
from-clause
Example create view oldaqua as (select F.fno
as fishno, F.fname as fishname, S.sno as
specno, S.sname as specname, S.sfood as
specfood, T.tno as tankno, T.tname as tankname,
4 T.tvolume as tankvolume from Species S,
Fish F, Tank T where S.sno F.sno and F.tno
T.tno)
select X.specname from (select F.fno as fishno,
F.fname as fishname, S.sno as specno, S.sname
as specname, S.sfood as specfood, T.tno as
tankno, T.tname as tankname, 4 T.tvolume as
tankvolume from Species S, Fish F, Tank
T where S.sno F.sno and F.tno T.tno) as
X where X.tankname "lagoon"
8
Can combine views with base tables in from-clause
Example find colors of fish in tanks named
lagoon. select F.fcolor from oldaqua X, Fish
F where X.fishno F.fno and X.tankname "lagoon"
Can involve views in existential and universal
queries
Example find names of species that cohabit with
a shark. select X.specname from oldaqua X,
oldaqua Y where X.tankno Y.tankno and
Y.specname "shark"
Example find names of species that appear in all
green tanks select X.specname from oldaqua
X where not exists (select from Tank T where
T.tcolor "green" and not exists (select
from oldaqua Y where X.specno Y.specno
and Y.tankno T.tno))
9
Views facilitate logical independence
External
External
External
Conceptual
Physical
10
Data modification through views
ambiguous modifications insert into
fishColor set fcolor "green"
create view fishColor as (select F.fcolor as
fcolor from Fish F)
delete from fishColor where fcolor "red"
fno fname fcolor tno sno 164 charlie orange 42 74
347 flipper black 35 17 228 killer white 42 22 281
charlie orange 85 22 483 albert red 55 17 119 bon
nie blue 42 22 388 cory purple 35 93 654 darron wh
ite 42 93 765 elsie white 42 22 438 fran black 55
74 277 george red 42 93 911 helen blue 44 74 104 i
ndira black 42 17 302 jill red 38 17 419 kilroy re
d 55 74 650 laura blue 85 17 700 maureen white 44
17
fcolor orange black white red blue purple
11

• In general, data modification through view
  allowed if
• the view is constructed over a single base
  table, and
• the view contains a key for that table

12
Some systems use view to materialize a
one-to-many relationship instance For
example create view TankWithFish as (select
T.tno as tno, tname, tcolor, tvolume, fno, fname,
fcolor, fweight from Tank T, Fish F where T.tno
F.tno)

• display occupies multiple screens
• each screen shows a subset of the solution set,
  perhaps just one tuple
• screen subset can use a subform for a
  one-to-many relationship instance
• e.g., tank information at various screen
  locations with textual and graphical
  annotations with scrolling window containing
  information on fish in the tank
• updates through view allowed, provided they are
  concern only fish data

13
Aquarium Tanks
tno 42 tname lagoon tcolor blue tvolume 500
fno fname fcolor fweight 456 flipper black 148
722 flounder gray 26 841 sebastien pink
12 944 ursala black 165
14
Check option for a view

• create view LargeTanks as
• (select
• from Tank
• where tvolume gt 200)
• single base table
• includes the table key
• allows data modifications

insert into LargeTanks (tno, tname, tcolor,
tvolume) values (71, "BigMuddy", "brown",
150) select from LargeTanks

• newly inserted tuple does not appear
• check option causes DBMS to disallows a
  modification if it creates a tuple that does
  not satisfy the view conditions

15
create view LargeTanks as (select from
Tank where tvolume gt 200) with check
option create view SmallTanks as (select
from LargeTanks where tvolume lt 300) with
check option
insert into SmallTanks (tno, tname, tcolor,
tvolume) values (71, "BigMuddy", "brown",
150) select from SmallTanks

• inserted tuple satisfies the defining condition
  for the SmallTanks view
• but fails the defining condition for the
  LargeTanks view on which SmallTanks is
  constructed
• by default, check options cascade, so insertion
  must pass defining conditions on all
  views underneath the insertion target
• so, insertion would be rejected
• SQL standard provides clauses for more complex
  behavior

16
Null values

• standard specifies systematic treatment
• must always be able to distinguish a null entry
  from any application data may need a separate
  field to indicate "data present"
• arithmetic operations produce null if any
  operand is null, e.g., null null null 3 -
  null null
• comparisons return "unknown" if any comparand is
  null, e.g., 3 lt null is unknown null null is
  unknown
• boolean operations extend to three-valued logic
  (true, false, unknown) new truth tables
• A B not A A and B A or B T T F T T
  T F F F T T U F U T F T T F T
  F F T F F F U T F U U T U U T
  U F U F U U U U U U
• where-clause and having-clause booleans require
  true to qualify a tuple or a cluster

17
Null values

• standard specifies systematic treatment
• must always be able to distinguish a null entry
  from any application data may need a separate
  field to indicate "data present"
• arithmetic operations produce null if any
  operand is null, e.g., null null null 3 -
  null null
• comparisons return "unknown" if any comparand is
  null, e.g., 3 lt null is unknown null null is
  unknown
• boolean operations extend to three-valued logic
  (true, false, unknown) new truth tables
• A B not A A and B A or B T T F T T
  T F F F T T U F U T F T T F T
  F F T F F F U T F U U T U U T
  U F U F U U U U U U
• where-clause and having-clause booleans require
  true to qualify a tuple or a cluster

18
Null values

• standard specifies systematic treatment
• must always be able to distinguish a null entry
  from any application data may need a separate
  field to indicate "data present"
• arithmetic operations produce null if any
  operand is null, e.g., null null null 3 -
  null null
• comparisons return "unknown" if any comparand is
  null, e.g., 3 lt null is unknown null null is
  unknown
• boolean operations extend to three-valued logic
  (true, false, unknown) new truth tables
• A B not A A and B A or B T T F T T
  T F F F T T U F U T F T T F T
  F F T F F F U T F U U T U U T
  U F U F U U U U U U
• where-clause and having-clause booleans require
  true to qualify a tuple or a cluster

19

• Null values (continued)
• special syntax available to test attribute for
  null, e.g., fcolor is null the keyword null
  cannot appear in equality comparisons,
  e.g., fcolor null is a syntax error

• special syntax to test for specific truth value,
  e.g., fcolor "red" is true
• exist (select ...) always returns true or
  false table with a row of nulls is still
  non-empty
• not exists (select ...) always returns true or
  false

Example find the names of fish that are not
red First attempt select fname from
Fish where fcolor not "red"
fcolor not "red" evaluates as not (fcolor
"red") null fcolor value gt unknown gt
where-clause rejects get fish with explicit
non-red colors presumably want all fish that are
not explicitly red, including those with null
colors
20
Need to check boundary situations Example find
the names of tanks with volumes larger than all
tanks that contain a shark

• Boundary situation there is a tank with a shark
  that has a null volume, say tank X
• X appears in the second set
• in the first set, when Q assumes the value X,
  comparison yields unknown
• boolean T.tvolume gt Q.tvolume is unknown (not
  true) gt X is rejected
• regardless of T under test, the first set cannot
  contain the second
• answer set is empty
• empty answer is correct, assuming that the
  English means report tanks known to have
  a volume greater than that of any "sharky" tank

21
Solution 2 select T.tname from Tank T where not
exists (select from Tank Q, Fish F, Species
S where Q.tno F.tno and F.sno S.sno and
S.sname "shark" and not (T.tvolume gt
Q.tvolume))
list of tanks containing a shark that T does not
dominate in volume

• Same boundary situation there is a tank with a
  shark that has a null volume, say tank X
• as before, the empty set is the correct answer
• but, suppose there is tank T that does indeed
  dominate all sharky tanks that have a non-null
  volume
• then, when T is under test in the outer scope,
  and Q assumes a sharky tank with a known
  volume, (T.tvolume gt Q.tvolume) is true, the
  negation is false, and Q is rejected
• when T is under test in the outer scope, and Q
  assumes X, the sharky tank with null volume,
  (T.tvolume gt Q.tvolume) is unknown, the negation
  is also unknown,and X is also excluded from the
  subquery construction
• consequently, the subquery constructs the empty
  set, and the not-exists predicate is true
• so, T appears in the final answer set
• this is incorrect according to our earlier
  interpretation
• in any case, it is different from the
  set-containment solution
• how to patch?

22
Nulls in aggregates

• min, max, sum, average if column is empty or
  all column entries are null gt return null else
  perform computation over non-null entries in
  column
• count if column is empty gt return 0 else
  return number of entries in column, including
  null entries

Convenience predicates

• (x between y and z) same as (x gt y) and (x lt
  z)
• for example, find the names of tanks in the
  volume range 100 to 600 inclusive select
  T.tname from Tank where T.tvolume between 100
  and 600 select T.tname from Tank T where
  T.tvolume gt 100 and T.tvolume lt 600
• tank with null volume gt where-clause evaluates
  to unknown gt tank rejected

23
Convenience predicates (continued)

• like-predicate available for limited pattern
  matching
• For example, find the names of tanks that have
  the syllable "pool" in their names select
  T.tname from Tank T where T.tname like
  "pool"
• can match string of zero or more characters ()
  or a single character (underscore)
• null tname gt unknown truth value gt tank is
  rejected

24
Lexicographical ordering for row comparisons

• find the names of blue tanks with volume
  500 select T.tname from Tank T where
  (T.tcolor, T.tvolume) ("blue", 500)
• what is interpretation of select
  T.tname from Tank T where (T.tcolor, T.tvolume)
  lt ("blue", 500)
• get tanks with colors alphabetically less than
  "blue" regardless of volume
• also get blue tanks with volumes less than 500
• comparison where decision is taken occupies the
  pivot position
• nulls beyond (to the right) of the pivot do not
  introduce unknowns into the boolean
• in the example above, suppose a tank T under
  consideration has color "aquamarine" and a null
  volume
• predicate returns true gt tank is included in
  answer aquamarine lt blue further comparisons
  are unnecessary null is not encountered
• if a candidate tank has a null color gt
  comparison returns unknown gt tank is rejected
• if a candidate tank has color "blue" but a null
  volume gt comparison is unknown gt rejection

25
Match predicate

• format (constructed tuple match constructed
  set)
• in the absence of nulls, same as set membership
  predicate
• For example, find the names of tanks containing
  a species that also appears in a tank
  named lagoon select T.tname from Tank T, Fish
  F, Species S where T.tno F.tno and F.sno
  S.sno and (S.sno, S.sname) in (select X.sno,
  X.sname from Species X, Fish Y, Tank Z where
  X.sno Y.sno and Y.tno Z.tno and Z.tname
  "lagoon")
• or select T.tname from Tank T, Fish F,
  Species S where T.tno F.tno and F.sno S.sno
  and (S.sno, S.sname) match (select X.sno,
  X.sname from Species X, Fish Y, Tank Z where
  X.sno Y.sno and Y.tno Z.tno and Z.tname
  "lagoon")
• actual test is r match Q is true if (a) r
  contains a null entry or (b) there exists a row
  s in Q such that r s is true otherwise r
  match Q is false

26
Match predicate (continued)

• match predicate always returns true or false,
  never unknown
• evaluate (r match Q)
• first suppose r has no null entries in
  comparing with a Q row having one or more null
  entries gt unknown gt that Q row cannot force
  a true return there remain Q rows that have no
  null entries either one such row equals r or
  not gt true or false, as the case may be
• now suppose r has a null entry all row
  comparisons with Q gt unknown gt no row s give
  s r true but, nevertheless, (r match Q)
  returns true
• this differs from set membership, which would
  return false if r had a null entry
• Why?
• referential integrity check
• consider the predicate F.tno match (select
  T.tno from Tank)
• if F.tno is null, the predicate is true
• if F.tno has a value equal to an entry in the
  T.tno list, the predicate is true
• otherwise, it is false, and F.tno points to a
  tank that is not in the T.tno list
• this is precisely the predicate "the foreign key
  F.tno preserves referential integrity
  between Fish and Tank"
• not useful in query extractions, but will appear
  in constraint assertions....later

27
Match predicate (continued)

• can embed the match predicate in an assertion of
  the form not exists (select from Fish
  F where not (F.tno match (select
  T.tno from Tank T)))
• can test this predicate after every database
  update if it is true, keep the update if it is
  false, rollback the update (referential integrity
  violation)
• now attempt to detect referential integrity
  violations with respect to two parent
  relations, i.e., between Fish and Tank and
  between Fish and Species not exists (select
  from Fish F where not ((F.tno, F.sno)
  match (select T.tno, S.sno from Tank T,
  Species S)))
• misses violation of form F.tno is null, but
  F.sno references a nonexistent species

28
Match predicate (continued)

• must use to separate assertions not
  exists (select from Fish F where not
  (F.tno match (select T.tno from Tank
  T))) not exists (select from Fish
  F where not (F.sno match (select
  S.sno from Species S)))
• or use variant (r match partial Q), which
  is true, if (a) all components of r are null,
  or (b) there exists s in Q such that
  (a b) is true when a is a non-null component
  of r and b is the corresponding component of
  s false, otherwise

29
Match predicate (continued)

• now the following predicate correctly detects
  referential integrity violations not exists
  (select from Fish F where not ((F.tno,
  F.sno) match partial (select T.tno,
  S.sno from Tank T, Species S)))
• but, suppose table X contains a two-attribute
  foreign key (a, b) that references
  a two-attribute primary key in the single parent
  table Y
• attempt referential integrity violation with
  match or match partial not exists
  not exists (select (select from
  X from X where not ((X.a, X.b) match where
  not ((X.a, X.b) match partial (select Y.a,
  Y.b (select Y.a, Y.b from Y))) from
  Y)))
• in this case, both fail to detect a referential
  integrity violation with foreign key (null, z),
  where z does appear under column b in Y

30
Match predicate (continued)

• need final variant (r match full Q), which
  is true, if (a) all components of r are null,
  or (b) there exists s in Q such that
  (s r) is true false, otherwise
• following predicate correctly detects
  referential integrity violations with multiple
  column keys that refer to a single parent not
  exists (select from X where not ((X.a,
  X.b) match full (select Y.a, Y.b from Y)))
• Conclusion can choose appropriate form of match
  predicate to detect referential
  integrity violations
• full format for such assertions later...

31
Unique predicate
for table T, unique T is false, if there exist
distinct rows r, s in T such that (r s) is
true true, otherwise detects a multiset (SQL
selects produce multisets by default) use
(select distinct ... ) to remove
duplicates useful for asserting a primary key
constraint ... later note table with all null
rows responds true
32

• any is disjunction of individual row
  comparisons, with standard null treatment true
  disjunct implies true for group, unknown disjunct
  implies unknown
• all is conjunction of individual row
  comparisons false conjunct implies false for
  group, unknown conjunct implies unknown
• (r any T) is the same as (r in T)

33

• (r gtany T) can return true/false even if all T
  rows have some null entries decision taken at
  pivot before null values encountered
• Likewise for (r gtany T), (r ltany T), (r ltany
  T), but not for (r any T)
• (r gtall T) can return true/false even if all T
  rows have some null entries decision taken at
  pivot before null values encountered
• Likewise for (r gtall T), (r ltall T), (r ltall
  T), but not for (r all T)
• Behavior well-defined, consistent with standard
  null treatment and lexicographic ordering
• Check boundary situations to determine
  suitability in given application context

Example find names of tanks containing a species
that is also found in a tank named lagoon
select T.tname from Tank T, Fish F, Species
S where T.tno F.tno and F.sno S.sno and
(S.sno, S.sname) any (select X.sno,
X.sname from Species X, Fish Y, Tank Z where
X.sno Y.sno and Y.tno Z.tno and Z.tname
"lagoon")
34
Revisit query find names of tanks that dominate
all sharky tanks in volume

• Suppose there exists a sharky tank X with null
  volume
• Interpret query such that correct answer is
  empty (no tanks are known to dominate every tank
  containing a shark)
• Have two solutions
• Set containment gt correctly produces empty
  table select T.tname from Tank T where (select
  Q.tno from Tank Q where
  T.tvolume gt Q.tvolume)
• contains (select Q.tno from Tank Q, Fish F,
  Species S where Q.tno F.tno and F.sno S.sno
  and S.sname "shark")
• Double negation gt initially incorrect, must
  adjust for three-valued logic select
  T.tname from Tank T where not exists (select
  from Tank Q, Fish F, Species S where Q.tno
  F.tno and F.sno S.sno and S.sname "shark"
  and (T.tvolume gt Q.tvolume) is not true

35
Find names of tanks that dominate all sharky
tanks in volume

• Consider third approach select T.tname from
  Tank T where T.tvolume gtall (select
  Q.tvolume from Tank Q, Fish F, Species
  S where Q.tno F.tno and F.sno S.sno and
  S.sname "shark")
• Analysis
• consider testing tank T which does dominate all
  sharky tanks that have non-null volumes
• sharky tank X, with null volume, appears in
  subquery
• (T.tvolume gt Q.tvolume) is not true for all rows
  Q in subquery (it is unknown when row Q
  corresponds to tank X)
• (T.tvolume gt Q.tvolume) is not false for any row
  Q in subquery (recall T does dominate when Q
  has a volume)
• Consequently, gtall predicate returns unknown
• Where-clause must be true to include a
  candidate, so T is excluded
• Solution is the empty table, which is correct
  without adjustment

36
Find names of tanks that dominate all sharky
tanks in volume

• Consider fourth approach select T.tname from
  Tank T where not (T.tvolume ltany (select
  Q.tvolume from Tank Q, Fish F, Species
  S where Q.tno F.tno and F.sno S.sno and
  S.sname "shark")
• Analysis
• consider testing tank T which does dominate all
  sharky tanks that have non-null volumes
• sharky tank X, with null volume, appears in
  subquery
• (T.tvolume lt Q.tvolume) is not true for any row
  Q in subquery (T dominates when Q has a
  non-null volume)
• (T.tvolume lt Q.tvolume) is not false for all
  rows Q in subquery (it is unknown when Q is
  tank X)
• Consequently, ltany predicate returns unknown
• not (T.tvolume ltany (....)) is then unknown,
  which is not true
• Again, solution is the empty table, which is
  correct without adjustment
• Have four solutions, three provide correct
  solution without adjustment

37
Join variations

• Natural join from relational algebra is
  officially a natural inner join --- inner to
  distinguish from a similar operation, the outer
  join ... later --- natural because it aligns
  columns with the same attribute name
• Can specify natural inner join directly in
  from-clause --- removes need to specify
  foreign-primary key conditions in where-clause
• For example find the names of species
  represented in a tank named lagoon select
  S.sname from ((Species S natural inner join Fish
  F) natural inner join Tank T) where T.tname
  "lagoon"
• The qualifier "inner" is optional inner is the
  default over the alternatives ... later
• Use of natural inner join restricted to cases
  where foreign key and primary key have same name
  and no other attributes share a name
• Suppose have too many common names, for
  example Tank (tno, name, color, volume) Fish
  (fno, name, color, weight, sno, tno) want names
  of tanks with orange fish

38
Join variations (continued)

• Suppose have too many common names, for
  example Tank (tno, name, color, volume) Fish
  (fno, name, color, weight, sno, tno) want names
  of tanks with orange fish

• Have flexibility in where-clause of basic
  SQL select T.name from Tank T, Fish F where
  T.tno F.tno and F.color "orange"
• Also have natural inner join variant select
  T.name from Tank T inner join Fish F using
  (tno) where F.color "orange"
• Join remains an inner join, but not a natural
  inner join
• Parenthesized list following "using" can include
  several identifiers --- primary/foreign key can
  span several attributes

39
Join variations (continued)

• Final variation available if primary key and
  foreign key have different names
• For example, the Employee (eno, ename, sno)
  table find the names of employees working for a
  "dennis" select E.ename from Employee E inner
  join Employee F on E.sno F.eno where F.ename
  "dennis"
• not particularly helpful because foreign key -
  primary key association must still be explicitly
  coded
• In summary, three syntax variations
• A natural inner join B
• A inner join B using (attribute-list)
• A inner join B on boolean-condition

40
The outer join
Consider Tank T natural inner join Fish
F, where some tanks (e.g., 72 below) contain
no fish
fno fname fcolor tno sno 164 charlie orange 42 74
347 flipper black 35 17 281 charlie orange 85 22 4
83 albert red 55 17 911 helen blue 44 74 302 jill
red 38 17
tno tname tcolor tvolume 42 lagoon blue 400 35 pud
dle green 800 85 fishbowl orange 600 55 cesspool g
reen 500 44 backwater black 200 38 tidepool yellow
500 72 saltpool green 900
Tank 72 (saltpool) does not appear in the inner
join
41
The outer join (continued)
By contrast, Tank T natural left outer join Fish
F preserves non-matching attributes from its
left argument
42
Example for each tank, report the number of
fish, including a zero count for tanks without
fish
First attempt select T.tno, T.tname, count()
as fishCount from Tank T natural left outer join
Fish F groupby T.tno
Second attempt select X.tno, X.tname,
sum(X.fishBoolean) as fishCount from (select
T.tno as tno, T.tname as tname, (case when
F.fno is null then 0 else 1) as fishBoolean
from Tank T natural left outer join Fish F)
as X groupby X.tno
43
Full outer join syntax
keep unmatched rows from left, right, or both
operands
alternatives to natural "using" for case of too
many common attributes "on" for case where
foreign key has different name from its primary
key referent
44
Set operations in the from-clause
A a b c 4 2 0 1 8 6 7 1 3 5 7 9
B d e f 4 2 0 1 8 6 0 4 9 2 3 7
ltgt a b c 4 2 0 1 8 6 7 1 3 5 7 9 0 4 9 2 3 7
select from A union select from B
select from A union B
Note duplicates removed by default To retain
duplicates select from A union all B
for a given tuple, if A contains n duplicates and
B contains m duplicates then (A union all B)
contains n m duplicates
45
Similar syntax for intersection and set difference
specifies retention of duplicates as follows
duplicates in A B result union n m n
m intersect n m min (n, m) except n m max (0, n
- m)
46
The union join
A a b c 4 2 0 1 8 6 7 1 3 5 7 9
B a b d 4 2 0 1 8 6 0 4 9 2 3 7
ltgt a b c d 4 2 0 null 1 8 6 null 7 1 3 null 5
7 9 null 4 2 null 0 1 8 null 6 0 4 null 9 2 3 null
7
select from A union join B

• Forces set-theoretic union
• extends each operands to include all attributes
• inserts nulls in newly created columns
• constructs union as before (with corresponding
  option to treat rows as associations
• known as an outer union in some older products

47
Constraints
Restrict table bodies to reflect
applications Constraint forms domain table co
lumn global
48
Domain Constraints
create domain keyvalue as integer check (value gt
0) check (value is not null) create domain
refvalue as integer check (value gt 0) create
domain tankcolor as char(10) default
"noColor" check (value in ("noColor", "blue",
"red", "green")) create domain fishcolor as
char(10) default "noColor" check (value in
"noColor", "blue", "red", "orange", "black",
"white", "purple")) create domain namestring as
char(25) default "noName" check (upper(value)
between A and Z) create domain wgtvalue as
integer check (value gt 0) create domain
volvalue as integer default 0 check (value gt 0
and value lt 5000) check (value mod 100 0)
49
Assert domain constraints by specifying domain
in attribute definition
create table Tank ( tno keyvalue, tname
namestring default "unnamedTank", tcolor
tankcolor, tvolume volvalue) create table
Species ( sno keyvalue, sname namestring
default "unnamedTank", sfood namestring
default "unspecifiedDiet") create table Fish
( fno keyvalue, fname namestring default
"unnamedFish", fcolor fishcolor, fweight
wgtvalue, tno refvalue, sno refvalue)
overrides domain default
50
Table constraints
create table Tank ( tno keyvalue, tname
namestring default "unnamedTank", tcolor
tankcolor, tvolume volvalue, primary key
(tno)) create table Species ( sno
keyvalue, sname namestring default
"unnamedTank", sfood namestring default
"unspecifiedDiet", primary key (sno)) create
table Fish ( fno keyvalue, fname namestring
default "unnamedFish", fcolor
fishcolor, fweight wgtvalue, tno
refvalue, sno refvalue, primary key
(fno), foreign key (tno) references Tank
(tno), foreign key (sno) references Species
(sno), check (fcolor not "white" or
exists (select from Species S where S.sno
Fish.sno and S.sname "shark")))
Three types primary key foreign key check
only sharks can be white
51
Primary and foreign key constraints
implemented with unique and match predicates
unique (select T.tno from Tank T)
primary key (tno)

• match partial also available
• with single-attribute keys, all are
  equivalent can omit match qualifier from table
  constraint
• can omit attribute in parent table defaults to
  primary key

52
Column constraints variation of table
constraints, but specified at column level adds
no new capability
create table Tank ( tno keyvalue primary
key, tname namestring default
"unnamedTank", tcolor tankcolor, tvolume
volvalue) create table Species ( sno keyvalue
primary key, sname namestring default
"unnamedTank", sfood namestring default
"unspecifiedDiet") create table Fish ( fno
keyvalue, fname namestring default
"unnamedFish", fcolor fishcolor check (fcolor
not "white" or exists (select from
Species S where S.sno Fish.sno and S.sname
"shark")), fweight wgtvalue, tno refvalue
references Tank (tno), sno refvalue references
Species (sno))
53
Global constraints
For functional dependency tvolume ? tcolor
(tanks in given volume range have same
color) Use SQL assertion outside table
definitions create assertion volColorFD check
(not exists (select from Tank T, Tank
U where T.tvolume U.tvolume and not (T.tcolor
U.tcolor)))
Another example no tank may provide less than
100 gallons per fish create assertion
populationDensity check (not exists (select
from (select T.tno as t, T.tvolume as v,
count() as fishCount from Tank T, Fish
F where T.tno F.tno groupby T.tno) as
X where X.v / X.fishCount lt 100))
54
Named constraints
create assertion populationDensity check (not
exists (select from (select T.tno as t,
T.tvolume as v, count() as fishCount from Tank
T, Fish F where T.tno F.tno groupby T.tno)
as X where X.v / X.fishCount lt 100))
create table Fish ( fno keyvalue, fname
namestring default "unnamedFish", fcolor
fishcolor, fweight wgtvalue, tno
refvalue, sno refvalue, constraint fishEntity
primary key (fno), constraint fishTank foreign
key (tno) references Tank (tno), constraint
fishSpecies foreign key (sno) references Species
(sno), constraint whiteShark check (fcolor not
"white" or exists (select from Species
S where S.sno Fish.sno and S.sname
"shark")))
55
Transactions

• indivisible unit of work
• used to guarantee ACID nature of DBMS processing
• atomic (unit completes in its entirety or rolls
  back leaving no trace)
• consistent (unit completion violates no
  constraints)
• isolated (unit executes as though it is the
  single process using DB)
• durable (after completion or roll-back, status
  survives any subsequent hardware or software
  failure)
• may need to temporarily lift constraint during a
  transaction
• named constraints facilitate such transaction
• For example, suppose following assertion insists
  on a minimal aquarium size create assertion
  minTanks check (3 ltall (select count() from
  Tank))
• How to load the initial tanks? First tank
  produces a violation and is therefore not
  accepted

56
Solution

• Defer constraint minTanks syntax set
  constraints minTanks deferred
• Start a transaction
• Insert first tank
• Insert second tank
• Insert third tank
• Re-apply constraint minTanks syntax set
  constraints minTanks immediate
• Commit the transaction

Similar problem occurs when loading fish prior to
tanks Each fish produces a referential integrity
violation Load all parent entities before their
dependents Or, defer the referential integrity
constraint during the entire load
57
Triggers

• Suppose update induces constraint violation
• DBMS has two possible responses
• reject the update
• accept the update and launch a compensatory
  update to restore the violated constraint
• Compensatory procedure is called a triggered
  routine

58
Suppose foreign key in fish described with one of
the following options, and we attempt deletion
of a populated tank
DBMS rejects the deletion same as no trigger
specified
59
Similar interpretation for the "on update"
clause Suppose we change the tno attribute of a
populated tank
DBMS rejects the update same as no trigger
specified
all fish referencing the changing tank receive
the new tno
all fish referencing the doomed tank have their
tno attributes changed to the default value
(specified in table or domain definition)
all fish referencing the doomed tank receive null
in their tno attributes
60
Update or delete can cascade through several
tables, marking records for deletion or change
A
attempt to delete a parent here
B
children specify cascade so attempt deletion of
all dependents
C
children specify set null
Deletion proceeds back up through the marked
records float (null) C dependents of B
dependents delete B dependents delete A parent
61
But, suppose encounter difficulty in cascade tree
A
attempt to delete a parent here
B
children specify cascade so attempt deletion of
all dependents
C
children specify no action either explicitly or
by default
Deletion fails because C dependents cannot
compensate for loss of parent all deletion marks
in tree removed
Could even have "set null" at C, but a further
constraint that disallows null in the foreign
key attribute
62

• Recursive SQL
• first appears in SQL-1999 standard (as non-core
  option)
• IBM's DB2 implements
• restricted to linear recursion (at most one
  recursive subgoal)

63
worker role
Employee eno ename sno 53 albert 14 42 bernice 14
86 candice 53 45 dennis 14 frank 45
Employee
supervisor role
Group 0 workers named dennis
select from Employee where ename
"dennis" into Group0
64
worker role
Employee eno ename sno 53 albert 14 42 bernice 14
86 candice 53 45 dennis 14 frank 45
Employee
supervisor role
Group 2 group 1 plus workers reporting to them
select from Group1 into Group2 union select
E.eno, E.ename, E.sno from Employee E, Group1
G where E.sno G.eno etc.
65
worker role
Employee eno ename sno 53 albert 14 42 bernice 14
86 candice 53 45 dennis 14 frank 45
Employee
supervisor role
SQL1999 with recursive UnderDennis (eno)
as (select E.eno from Employee E where
E.ename "Dennis") union (select E.eno from
UnderDennis UD, Employee E where UD.eno
E.sno) select from UnderDennis

• Semantics
• Start with UnderDennis empty
• Apply SQL using current UnderDennis to
  create next version
• Repeat until UnderDennis stabilizes (reach
  fixed-point)

66
Can keep names as well as employee numbers
with recursive UnderDennis (eno, ename)
as (select E.eno, E.ename from Employee E
where E.ename "Dennis") union (select E.eno,
E.ename from UnderDennis UD, Employee E where
UD.eno E.sno) select from UnderDennis
67
Another Example Flight relation for legs of
airline flights
airline source destination depart-time arrive-time

68
Query Find all airport pairs (x, y) such that
one can fly from x to y via some number of
connecting flights
SQL with recursive Reaches (source,
destination) as (select F.source, F.destination
from Flight F) union (select R.source,
F.destination from Reaches R, Flight F where
R.destination F.source) select from Reaches
69
Query Find all airport pairs (x, y) such that
one can fly from x to y via some odd number of
connecting flights
SQL with recursive Reaches_Odd (source,
destination) as (select source, destination from
Flight) union (select R.source,
F.destination from Reaches_Even R, Flight F
where R.destination F.source),
recursive Reaches_Even (source, destination)
as (select R.source, F.destination from
Reaches_Odd R, Flight F where R.destination
F.source), select from Reaches-Odd
70
Query Find all airport pairs (x, y) such that
one can fly from x to y via some odd number of
connecting flights, but not via an even number
SQL with recursive Reaches_Odd (source, dest)
as (select source, destination from
Flight) union ((select R.source,
F.destination from Reaches_Even R, Flight F
where R.destination F.source) except (select
R.source, R.destination from Reaches_Even R)),
recursive Reaches_Even (source, dest)
as (select R.source, F.destination from
Reaches_Odd R, Flight F where R.destination
F.source), select from Reaches-Odd
71
Suppose R contains the single tuple t
Solutions R t, P t, Q empty R
t, P empty, Q t
P Q
R
72
Query Find all airport pairs (x, y) such that
one can fly from x to y via some odd number of
connecting flights, but not via an even number
73
Query Find all airport pairs (x, y) such that
one can fly from x to y via some odd number of
connecting flights, but not via an even number
SQL with recursive Reaches_Odd (source,
destination) as (select source, destination from
Flight) union ((select F.source,
R.destination from Flight F, Flight G,
Reaches_Odd R where F.destination G.source
and G.destination R.source) except (select
R.source, R.destination from Reaches_Even R)),
recursive Reaches_Even (source,
destination) as (select F.source,
G.destination from Flight F, Flight G where
F.destination G.source) union (select
F.source, R.destination from Flight F, Flight G,
Reaches_Even R where F.destination G.source
and G.destination R.source), select from
Reaches-Odd
74
Codd's 12 rules
Relational model must contains features to
support structure (data, relationships, query
processing) integrity (constraints) manipulation
(insertion, deletion, update, retrieval)

• tabular structures
• each data item has a hierarchical address
  (table name, key value, attribute name)
• systematic treatment of null values
• active catalog describes tables, attributes,
  constraints
• a data definition interface language (DDL) is
  available for schema construction a data
  manipulation interface language (DML) for query
  and update
• updates through views is systematically
  addressed
• the interface languages support set-oriented
  operations

75
Codd's 12 rules (continued)

• conceptual schema buffers applications against
  physical hardware changes
• the view mechanism buffers applications against
  conceptual changes
• DBMS stores and enforces integrity constraints
• system functionality is not impaired when
  database is distributed across several sites
• security mechanism is resistant to attacks that
  accumulate information over many small
  transactions

76
Shortcomings of the relational model

• Typically, each real-world entity appears
  dispersed across several tables advantage
  promotes data integrity and economizes
  storage disadvantage exposes a fragmented view
  of application object
• Model stores only passive data no
  procedures server-side prepared queries and
  delete/update triggers are exceptions real-world
  entity has both attributes and behavior
• Model is somewhat unsuitable for non-textual
  data objects images, sound tracks, engineering
  drawings multimedia information, in general
• Incompleteness of SQL lacks a recursive closure
  operator