SQL: Part II

Introduction to Databases
CompSci 316 Fall 2014
 2

Announcements (Thu., Sep. 18)

• Homework #1 sample solution to be posted on
Sakai by tomorrow
• We are working on resolving the issue
• Use Chrome and IE for now
• Homework #2 due in two weeks
 3

Incomplete information

• Example: User (uid, name, age, pop)

• Value unknown
• We do not know Nelson’s age
• Value not applicable
• Suppose pop is based on interactions with others on our
social networking site
• Nelson is new to our site; what is his pop?
 4

Solution 1
• Dedicate a value from each domain (type)
• pop cannot be −1, so use −1 as a special value to
indicate a missing or invalid pop
• Leads to incorrect answers if not careful
•
• Complicates applications
•
!"
• Perhaps the value is not
as special as you think!
• Ever heard of the Y2K bug?
“00” was used as a
missing or invalid year value

# $%% &'( )""&* % +, %-./!*+ 0&1 ,

 5

Solution 2
• A valid-bit for every column
• User (uid, name, name_is_valid,
age, age_is_valid,
pop, pop_is_valid)
• Complicates schema and queries
•
2 23+4 5
 6

Solution 3
• Decompose the table; missing row = missing value
• UserName (uid, name)
UserAge (uid, age)
UserPop (uid, pop)
• UserID (uid)
• Conceptually the cleanest solution
• Still complicates schema and queries
• How to get all information about users in a table?
• Natural join doesn’t work!
 7

SQL’s solution
• A special value 6
• For every domain
• Special rules for dealing with 6 ’s

• Example: User (uid, name, age, pop)

• 789, “Nelson”, 6 ,6
 8

Computing with 6 ’s

• When we operate on a 6 and another value

(including another 6 ) using +, −, etc., the
result is 6

• Aggregate functions ignore 6 , except

6 7 (since it counts rows)
 9

Three-valued logic
• = 1, = 0, 686 6 = 0.5
• 69 = min( , )
• = max( , )
•6 =1−
• When we compare a 6 with another value
(including another 6 ) using =, >, etc., the
result is 686 6
• and :6 clauses only select rows for
output if the condition evaluates to
• 686 6 is not enough
 10

Unfortunate consequences
•
% 6 7
• Not equivalent
• Although ; % 6 still
• 7
7 ;
• Not equivalent
Be careful: 6 breaks many equivalences
 11

Another problem
• Example: Who has 6 pop values?
• 7 ; 6
• Does not work; never returns anything
• 7
< =
7 ;
• Works, but ugly
• Introduced special, built-in predicates
: 6 and : 6 6
• 7 : 6
 12

Outerjoin motivation
• Example: a master group membership list
• ,&, 5> ,&0+ ,0+ >
& 5> &0+ 0+
,> >
,&, 5 ; &, 5 69 & 5 ; & 5
• What if a group is empty?
• It may be reasonable for the master list to include empty
groups as well
• For these classes, uid and uname columns would be 6
 13

Outerjoin flavors and definitions

• A full outerjoin between R and S (denoted )
includes all rows in the result of ⋈ , plus
• “Dangling” rows (those that do not join with any
rows) padded with 6 ’s for ’s columns
• “Dangling” rows (those that do not join with any
rows) padded with 6 ’s for ’s columns
• A left outerjoin ( ) includes rows in ⋈ plus
dangling rows padded with 6 ’s
• A right outerjoin ( ) includes rows in ⋈
plus dangling rows padded with 6 ’s
 14

Outerjoin examples gid name uid

Group Member + * ? / 4 ABC

, 3 5 0 3 0 0 ".@
, 3 5 0 3 0 0 ABC
Group 5 9 +5 = 0, * - ").
gid name 0 / 0 5 6 *4 + / 6
+ * ? / 4
, 3 5 0 3 0 0
gid name uid
5 9 +5 = 0, * -
Group Member + * ? / 4 ABC
0 / 0 5 6 *4 + /
, 3 5 0 3 0 0 ".@
, 3 5 0 3 0 0 ABC

Member 5 9 +5 = 0, * - ").
uid gid D 6 CA'
"). 5
".@ , 3 gid name uid
ABC + * Group Member + * ? / 4 ABC
ABC , 3 , 3 5 0 3 0 0 ".@
CA' D , 3 5 0 3 0 0 ABC
5 9 +5 = 0, * - ").
0 / 0 5 6 *4 + / 6
D 6 CA'
 15

Outerjoin syntax
• 7 E :6
6 &, 5 ; &, 5
≈
!"#$%.'()*+,-.,".'()

• 7 : E :6
6 &, 5 ; &, 5
≈
!"#$%.'()*+,-.,".'()

• 7 E :6
6 &, 5 ; &, 5
≈
!"#$%.'()*+,-.,".'()

These are theta joins rather than natural joins

• Return all columns in Group and Member
A similar construct exists for regular (“inner”) joins:
• 7 E :6
6 &, 5 ; &, 5
 16

SQL features covered so far

• ! ! statements
• Set and bag operations
• Table expressions, subqueries
• Aggregation and grouping
• Ordering
•6 F and outerjoins

Next: data modification statements, constraints

 17

:6
• Insert one row
• :6 :6 CA'> G5 G
• User 789 joins Dead Putting Society

• Insert the result of a query

• :6 :6
5> G5 G
5 6 :6 5

, 5 ; G5 G
• Everybody joins Dead Putting Society!
 18

9
• Delete everything from a table
•9
• Delete according to a condition
Example: User 789 leaves Dead Putting Society
•9
5 ; CA' 69 , 5 ; G5 G
Example: Users under age 18 must be removed
from United Nuclear Workers
•9
5 :6 5
+, "A
69 , 5 ; G0 /G
 19

=9
• Example: User 142 changes name to “Barney”
• =9
0+ ; G?+ 0 -G
5 ; ").
• Example: We are all popular!
• =9
;
• But won’t update of every row causes average pop to change?
Subquery is always computed over the old table
 20

Constraints
• Restrictions on allowable data in a database
• In addition to the simple structure and type restrictions
imposed by the table definitions
• Declared as part of the schema
• Enforced by the DBMS
• Why use constraints?
• Protect data integrity (catch errors)
• Tell the DBMS about the data (so it can optimize better)
 21

Types of SQL constraints

•6 6
• Key
• Referential integrity (foreign key)
• General assertion
• Tuple- and attribute-based 8’s
 22

6 6 constraint examples
• ?
5 :6 6 6 >
0+ @( 6 6 >
5 "B 6 6 >
+, :6 >

• ?
, 5 "( 6 6 >
0+ "(( 6 6
• ?
5 :6 6 6 >
, 5 "( 6 6
 23

Key declaration
• At most one = : H 8 H per table
• Typically implies a primary index
• Rows are stored inside the index, typically sorted by the
primary key value ⇒ best speedup for queries
• Any number of 6:I keys per table
• Typically implies a secondary index
• Pointers to rows are stored inside the index ⇒ less
speedup for queries
 24

Key declaration examples

• ?
5 :6 6 6 = : H 8 H>
0+ @( 6 6 >
5 "B 6 6 6:I >
+, :6 >

• ?
, 5 "( 6 6 = : H 8 H>
0+ "(( 6 6
• ?
5 :6 6 6 >
, 5 "( 6 6 >
= : H 8 H 5> , 5
This form is required for multi-attribute keys
 25

Referential integrity example

• Member.uid references User.uid
• If an uid appears in Member, it must appear in User
• Member.gid references Group.gid
• If a gid appears in Member, it must appear in Group
That is, no “dangling pointers”
User Member Group
uid name … uid gid gid name
"). ?+ J "). 5 + * J
".@ 4# J ".@ , 3 , 3 J
ABC + J ABC + * 5 J
)BK +4 # J ABC , 3 J J
CA' 6 4 0 J )BK + *
J J J )BK , 3
J J
 26

Referential integrity in SQL

• Referenced column(s) must be = : H8 H
• Referencing column(s) form a : 68 H
• Example
• ?
5 :6 6 6
6 5 >
, 5 "( 6 6 >
= : H 8 H 5> , 5 >
: 6 8 H , 5 6 , 5
 27

Enforcing referential integrity

Example: Member.uid references User.uid
• Insert or update a Member row so it refers to a non-
existent uid
• Reject
• Delete or update a User row whose uid is
referenced by some Member row
• Reject
• Cascade: ripple changes to all referring rows
• Set 6 : set all references to 6
• All three options can be specified in SQL
 28

Deferred constraint checking

• No-chicken-no-egg problem
• ? 9
0+ .( 6 6 = : H 8 H>
*#+ @( 6 6
6 = D 0+
? = D
0+ @( 6 6 = : H 8 H>
5 .( 6 6
6 9 0+
• The first :6 will always violate a constraint!
• Deferred constraint checking is necessary
• Check only at the end of a transaction
• Allowed in SQL as an option
• Curious how the schema was created in the first place?
• ? 99 6 :6 (read the manual!)
 29

General assertion
• : 6 011 23 4_40
8 011 23 4_6 47323 4
• 011 23 4_6 47323 4 is checked for each
modification that could potentially violate it
• Example: Member.uid references User.uid
• : 6 D:0 , -
8 6 <:
7
5 6 :6
5
In SQL3, but not all (perhaps no) DBMS supports it
 30

Tuple- and attribute-based 8’s

• Associated with a single table
• Only checked when a tuple or an attribute is
inserted or updated
• Examples:
• ? &&&
+, :6
8 +, : 6 +, ( >
&&&
• ?
5 :6 6 6
8 5 :6
5 >
&&&
• Is it a referential integrity constraint?
• Not quite; not checked when User is modified
 31

SQL features covered so far

• Query
• ! ! statements
• Set and bag operations
• Table expressions, subqueries
• Aggregation and grouping
• Ordering
• Outerjoins
• Modification
• :6 %9 % =9
• Constraints
Next: triggers, views, indexes