Database System Concepts, 6th Ed.
©Silberschatz, Korth and Sudarshan
See www.db-book.com for conditions on re-use
Chapter 4: Intermediate SQL
©Silberschatz, Korth and Sudarshan4.2Database System Concepts - 6th Edition
Chapter 4: Intermediate SQL
 Join Expressions
 Views
 Integrity Constraints
 SQL Data Types and Schemas
 Triggers
©Silberschatz, Korth and Sudarshan4.3Database System Concepts - 6th Edition
Joined Relations
 Join operations take two relations and return as a result
another relation.
 A join operation is a Cartesian product which requires that
tuples in the two relations match (under some condition).
It also specifies the attributes that are present in the result
of the join
 The join operations are typically used as subquery
expressions in the from clause
©Silberschatz, Korth and Sudarshan4.4Database System Concepts - 6th Edition
Join operations – Example
 Relation course
 Relation prereq
 Observe that
prereq information is missing for CS-315 and
course information is missing for CS-347
©Silberschatz, Korth and Sudarshan4.5Database System Concepts - 6th Edition
Outer Join
 An extension of the join operation that avoids loss of
information.
 Computes the join and then adds tuples form one relation
that does not match tuples in the other relation to the result
of the join.
 Uses null values.
©Silberschatz, Korth and Sudarshan4.6Database System Concepts - 6th Edition
Left Outer Join
 course natural left outer join prereq
©Silberschatz, Korth and Sudarshan4.7Database System Concepts - 6th Edition
Right Outer Join
 course natural right outer join prereq
©Silberschatz, Korth and Sudarshan4.8Database System Concepts - 6th Edition
Joined Relations
 Join operations take two relations and return as a result
another relation.
 These additional operations are typically used as subquery
expressions in the from clause
 Join condition – defines which tuples in the two relations
match, and what attributes are present in the result of the join.
 Join type – defines how tuples in each relation that do not
match any tuple in the other relation (based on the join
condition) are treated.
©Silberschatz, Korth and Sudarshan4.9Database System Concepts - 6th Edition
Full Outer Join
 course natural full outer join prereq
©Silberschatz, Korth and Sudarshan4.10Database System Concepts - 6th Edition
Joined Relations – Examples
 course inner join prereq on
course.course_id = prereq.course_id
 What is the difference between the above, and a natural join?
 course left outer join prereq on
course.course_id = prereq.course_id
©Silberschatz, Korth and Sudarshan4.11Database System Concepts - 6th Edition
Joined Relations – Examples
 course natural right outer join prereq
 course full outer join prereq using (course_id)
©Silberschatz, Korth and Sudarshan4.12Database System Concepts - 6th Edition
Join Types and Conditions Summary
©Silberschatz, Korth and Sudarshan4.13Database System Concepts - 6th Edition
Views
 In some cases, it is not desirable for all users to see the entire
logical model (that is, all the actual relations stored in the
database.)
 Consider a person who needs to know an instructors name
and department, but not the salary. This person should see a
relation described, in SQL, by
select ID, name, dept_name
from instructor
 A view provides a mechanism to hide certain data from the
view of certain users.
 Any relation that is not of the conceptual model but is made
visible to a user as a “virtual relation” is called a view.
©Silberschatz, Korth and Sudarshan4.14Database System Concepts - 6th Edition
View Definition
 A view is defined using the create view statement which has
the form
create view v as < query expression >
where <query expression> is any legal SQL expression. The
view name is represented by v.
 Once a view is defined, the view name can be used to refer to
the virtual relation that the view generates.
 View definition is not the same as creating a new relation by
evaluating the query expression
 Rather, a view definition causes the saving of an expression;
the expression is substituted into queries using the view.
©Silberschatz, Korth and Sudarshan4.15Database System Concepts - 6th Edition
Example Views
 A view of instructors without their salary
create view faculty as
select ID, name, dept_name
from instructor
 Find all instructors in the Biology department
select name
from faculty
where dept_name = „Biology‟
 Create a view of department salary totals
create view departments_total_salary(dept_name, total_salary) as
select dept_name, sum (salary)
from instructor
group by dept_name;
©Silberschatz, Korth and Sudarshan4.16Database System Concepts - 6th Edition
Views Defined Using Other Views
 create view physics_fall_2009 as
select course.course_id, sec_id, building, room_number
from course, section
where course.course_id = section.course_id
and course.dept_name = ‟Physics‟
and section.semester = ‟Fall‟
and section.year = ‟2009‟;
 create view physics_fall_2009_watson as
select course_id, room_number
from physics_fall_2009
where building= ‟Watson‟;
©Silberschatz, Korth and Sudarshan4.17Database System Concepts - 6th Edition
Update of a View
 Add a new tuple to faculty view which we defined earlier
insert into faculty values (‟30765‟, ‟Green‟, ‟Music‟);
This insertion must be represented by the insertion of the tuple
(‟30765‟, ‟Green‟, ‟Music‟, null)
into the instructor relation
©Silberschatz, Korth and Sudarshan4.18Database System Concepts - 6th Edition
Some Updates cannot be Translated Uniquely
 create view instructor_info as
select ID, name, building
from instructor, department
where instructor.dept_name= department.dept_name;
 insert into instructor_info values (‟69987‟, ‟White‟, ‟Taylor‟);
 which department, if multiple departments in Taylor?
 what if no department is in Taylor?
 Most SQL implementations allow updates only on simple views
 The from clause has only one database relation.
 The select clause contains only attribute names of the
relation, and does not have any expressions, aggregates, or
distinct specification.
 Any attribute not listed in the select clause can be set to null
 The query does not have a group by or having clause.
©Silberschatz, Korth and Sudarshan4.19Database System Concepts - 6th Edition
And Some Not at All
 create view history_instructors as
select *
from instructor
where dept_name= ‟History‟;
 What happens if we insert (‟25566‟, ‟Brown‟, ‟Biology‟, 100000)
into history_instructors?
©Silberschatz, Korth and Sudarshan4.20Database System Concepts - 6th Edition
Integrity Constraints
 Integrity constraints guard against accidental damage to the
database, by ensuring that authorized changes to the
database do not result in a loss of data consistency.
 A checking account must have a balance greater than
$10,000.00
 A salary of a bank employee must be at least $4.00 an
hour
 A customer must have a (non-null) phone number
©Silberschatz, Korth and Sudarshan4.21Database System Concepts - 6th Edition
Integrity Constraints on a Single Relation
 not null
 primary key
 unique
 check (P), where P is a predicate
©Silberschatz, Korth and Sudarshan4.22Database System Concepts - 6th Edition
Not Null and Unique Constraints
 not null
 Declare name and budget to be not null
name varchar(20) not null
budget numeric(12,2) not null
 unique ( A1, A2, …, Am)
 The unique specification states that the attributes A1, A2, …
Am
form a candidate key.
 Candidate keys are permitted to be null (in contrast to primary
keys).
©Silberschatz, Korth and Sudarshan4.23Database System Concepts - 6th Edition
The check clause
 check (P)
where P is a predicate
Example: ensure that semester is one of fall, winter, spring
or summer:
create table section (
course_id varchar (8),
sec_id varchar (8),
semester varchar (6),
year numeric (4,0),
building varchar (15),
room_number varchar (7),
time slot id varchar (4),
primary key (course_id, sec_id, semester, year),
check (semester in (‟Fall‟, ‟Winter‟, ‟Spring‟, ‟Summer‟))
);
©Silberschatz, Korth and Sudarshan4.24Database System Concepts - 6th Edition
Referential Integrity
 Ensures that a value that appears in one relation for a given
set of attributes also appears for a certain set of attributes in
another relation.
 Example: If “Biology” is a department name appearing in
one of the tuples in the instructor relation, then there exists
a tuple in the department relation for “Biology”.
 Let A be a set of attributes. Let R and S be two relations that
contain attributes A and where A is the primary key of S. A is
said to be a foreign key of R if for any values of A appearing
in R these values also appear in S.
©Silberschatz, Korth and Sudarshan4.25Database System Concepts - 6th Edition
Cascading Actions in Referential Integrity
 create table course (
course_id char(5) primary key,
title varchar(20),
dept_name varchar(20) references department
)
 create table course (
…
dept_name varchar(20),
foreign key (dept_name) references department
on delete cascade
on update cascade,
. . .
)
 alternative actions to cascade: set null, set default
©Silberschatz, Korth and Sudarshan4.26Database System Concepts - 6th Edition
Integrity Constraint Violation
 E.g. create table person (
ID char(10),
name char(40),
mother char(10),
father char(10),
primary key ID,
foreign key father references person,
foreign key mother references person)
 How to insert a tuple without causing constraint violation?
 insert father and mother of a person before inserting person
 OR, set father and mother to null initially, update after
inserting all persons (not possible if father and mother
attributes declared to be not null)
 OR defer constraint checking
 and use transactions – see Chapter 14
©Silberschatz, Korth and Sudarshan4.27Database System Concepts - 6th Edition
Complex Check Clauses
 check (time_slot_id in (select time_slot_id from time_slot))
 why not use a foreign key here?
 Every section has at least one instructor teaching the section.
 how to write this?
 Unfortunately: subquery in check clause not supported by
pretty much any database
 Alternative: triggers (later)
 create assertion <assertion-name> check <predicate>;
 Also not supported by anyone
©Silberschatz, Korth and Sudarshan4.28Database System Concepts - 6th Edition
Built-in Time/Date Data Types in SQL
 date: Dates, containing a (4 digit) year, month and date
 Example: date „2005-7-27‟
 time: Time of day, in hours, minutes and seconds.
 Example: time „09:00:30‟ time „09:00:30.75‟
 timestamp: date plus time of day
 Example: timestamp „2005-7-27 09:00:30.75‟
 interval: period of time
 Example: interval „1‟ day
 Subtracting a date/time/timestamp value from another gives
an interval value
 Interval values can be added to date/time/timestamp values
©Silberschatz, Korth and Sudarshan4.29Database System Concepts - 6th Edition
User-Defined Types
 create type construct in SQL creates user-defined type
create type Dollars as numeric (12,2) final
 create table department
(dept_name varchar (20),
building varchar (15),
budget Dollars);
©Silberschatz, Korth and Sudarshan4.30Database System Concepts - 6th Edition
Domains
 create domain construct in SQL-92 creates user-defined
domain types
create domain person_name char(20) not null
 Types and domains are similar. Domains can have
constraints, such as not null, specified on them.
 create domain degree_level varchar(10)
constraint degree_level_test
check (value in (‟Bachelors‟, ‟Masters‟, ‟Doctorate‟));
©Silberschatz, Korth and Sudarshan4.31Database System Concepts - 6th Edition
Large-Object Types
 Large objects (photos, videos, CAD files, etc.) are stored as a
large object:
 blob: binary large object -- object is a large collection of
uninterpreted binary data (whose interpretation is left to an
application outside of the database system)
 clob: character large object -- object is a large collection of
character data
 When a query returns a large object, a pointer is returned
rather than the large object itself.
©Silberschatz, Korth and Sudarshan4.32Database System Concepts - 6th Edition
Index Creation
 create table student
(ID varchar (5),
name varchar (20) not null,
dept_name varchar (20),
tot_cred numeric (3,0) default 0,
primary key (ID))
 create index studentID_index on student(ID)
 Indices are data structures used to speed up access to records
with specified values for index attributes
 e.g. select *
from student
where ID = „12345‟
can be executed by using the index to find the required
record, without looking at all records of student
More on indices in Chapter 11
©Silberschatz, Korth and Sudarshan4.33Database System Concepts - 6th Edition
Triggers
 A trigger is a statement that is executed automatically by
the system as a side effect of a modification to the
database.
 To design a trigger mechanism, we must:
 Specify the conditions under which the trigger is to be
executed.
 Specify the actions to be taken when the trigger
executes.
 Triggers introduced to SQL standard in SQL:1999, but
supported even earlier using non-standard syntax by
most databases.
 Syntax illustrated here may not work exactly on your
database system; check the system manuals
©Silberschatz, Korth and Sudarshan4.34Database System Concepts - 6th Edition
Trigger Example
 Maintain total credits earned for each student
 Executed when a student passes an exam
 i.e. update of grade attribute of takes table
 create trigger credits_earned after update of takes on (grade)
referencing new row as nrow
referencing old row as orow
for each row
when nrow.grade <> ‟F‟ and nrow.grade is not null
and (orow.grade = ‟F‟ or orow.grade is null)
begin
update student
set tot_cred= tot_cred +
(select credits from course
where course.course_id= nrow.course_id)
where student.id = nrow.id;
end;
©Silberschatz, Korth and Sudarshan4.35Database System Concepts - 6th Edition
Trigger Example
 Use of triggers to implement a special integrity constraint:
 time_slot_id is not a primary key of timeslot, so we cannot
create a foreign key constraint from section to timeslot.
 Insert trigger on section table:
create trigger timeslot_check1 after insert on section
referencing new row as nrow
for each row
when (nrow.time_slot_id not in (
select time_slot_id
from time_slot)) /* time_slot_id not present in time_slot */
begin
rollback
end; Rollback command cancels all changes
to DB currently made (a transaction).
So the INSERT is taken back.
©Silberschatz, Korth and Sudarshan4.36Database System Concepts - 6th Edition
Trigger Example Cont.
 Insert trigger on time_slot table:
create trigger timeslot_check2 after delete on time_slot
referencing old row as orow
for each row
when (orow.time_slot_id not in (
select time_slot_id
from time_slot)
/* last tuple for time slot id deleted from time slot */
and orow.time_slot_id in (
select time_slot_id
from section)) /* and time_slot_id still referenced from section*/
begin
rollback
end;
©Silberschatz, Korth and Sudarshan4.37Database System Concepts - 6th Edition
Triggering Events and Actions in SQL
 Triggering event can be insert, delete or update
 Triggers on update can be restricted to specific attributes
 E.g., after update of takes on grade
 Values of attributes before and after an update can be
referenced
 referencing old row as : for deletes and updates
 referencing new row as : for inserts and updates
 Triggers can be activated before an event, which can serve as
extra constraints. E.g. convert blank grades to null.
create trigger setnull_trigger before update of takes
referencing new row as nrow
for each row
when (nrow.grade = „ „)
begin atomic
set nrow.grade = null;
end;
©Silberschatz, Korth and Sudarshan4.38Database System Concepts - 6th Edition
Statement Level Triggers
 Instead of executing a separate action for each affected
row, a single action can be executed for all rows affected by
a transaction
 Use for each statement instead of for each row
 Use referencing old table or referencing new
table to refer to temporary tables (called transition
tables) containing the affected rows
 Can be more efficient when dealing with SQL
statements that update a large number of rows
©Silberschatz, Korth and Sudarshan4.39Database System Concepts - 6th Edition
When Not To Use Triggers
 Triggers were used earlier for tasks such as
 maintaining summary data (e.g., total salary of each department)
 Replicating databases by recording changes to special relations
(called change or delta relations) and having a separate process
that applies the changes over to a replica
 There are better ways of doing these now:
 Databases today provide built in materialized view facilities to
maintain summary data
 Databases provide built-in support for replication
 Encapsulation facilities can be used instead of triggers in many cases
 Define methods to update fields
 Carry out actions as part of the update methods instead of
through a trigger
©Silberschatz, Korth and Sudarshan4.40Database System Concepts - 6th Edition
When Not To Use Triggers
 Risk of unintended execution of triggers, for example, when
 loading data from a backup copy
 replicating updates at a remote site
 Trigger execution can be disabled before such actions.
 Other risks with triggers:
 Error leading to failure of critical transactions that set off the
trigger
 Cascading execution
Database System Concepts, 6th Ed.
©Silberschatz, Korth and Sudarshan
See www.db-book.com for conditions on re-use
End of Chapter 4