Database System Concepts, 7th Ed.
©Silberschatz, Korth and Sudarshan
See www.db-book.com for conditions on re-use
Chapter 5 : Intermediate SQL
©Silberschatz, Korth and Sudarshan5.2Database System Concepts - 7th Edition
Outline
▪ Join Expressions
▪ Views
▪ Transactions
▪ Integrity Constraints
▪ SQL Data Types and Schemas
▪ Index Definition in SQL
▪ Authorization
©Silberschatz, Korth and Sudarshan5.3Database System Concepts - 7th Edition
Joined Relations
▪ Join operations take two relations and return as a result another
relation.
▪ A join operation is a Cartesian product that requires that tuples in the two
relations match (under a specific condition). It also specifies attributes to
be present in the result of the join
▪ The join operations are typically used as subquery expressions in the
from clause
▪ Three types of joins:
• Natural join
• Inner join
• Outer join
©Silberschatz, Korth and Sudarshan5.4Database System Concepts - 7th Edition
Natural Join in SQL
▪ Natural join matches tuples with the same values for all common attributes
and retains only one copy of each of the common columns.
▪ List the names of instructors along with the course ID of the courses that
they taught
• select name, course_id
from students, takes
where student.ID = takes.ID;
▪ Same query in SQL with “natural join” construct
• select name, course_id
from student natural join takes;
©Silberschatz, Korth and Sudarshan5.5Database System Concepts - 7th Edition
Natural Join in SQL (Cont.)
▪ The from clause can have multiple relations combined using natural join:
select A1, A2, … An
from r1 natural join r2 natural join .. natural join rn
where P ;
©Silberschatz, Korth and Sudarshan5.6Database System Concepts - 7th Edition
Student Relation
©Silberschatz, Korth and Sudarshan5.7Database System Concepts - 7th Edition
Takes Relation
©Silberschatz, Korth and Sudarshan5.8Database System Concepts - 7th Edition
student natural join takes
©Silberschatz, Korth and Sudarshan5.9Database System Concepts - 7th Edition
Dangerous in Natural Join
▪ Beware of unrelated attributes with the same name get equated incorrectly
▪ Example -- List the names of students along with the titles of courses that
they have taken
• Correct version
select name, title
from student natural join takes, course
where takes.course_id = course.course_id;
• Incorrect version
select name, title
from student natural join takes natural join course;
▪ This query omits all (student name, course title) pairs where the
student takes a course in a department other than the student's
own department.
▪ The correct version (above), correctly outputs such pairs.
©Silberschatz, Korth and Sudarshan5.12Database System Concepts - 7th Edition
Outer Join
▪ An extension of the join operation that avoids loss of information.
▪ Computes the join and then adds tuples from one relation that does not
match tuples in the other relation to the result of the join.
▪ Uses null values.
▪ Three forms of outer join:
• left outer join
• right outer join
• full outer join
©Silberschatz, Korth and Sudarshan5.13Database System Concepts - 7th Edition
Outer Join Examples
▪ Relation course
▪ Relation prereq
▪ Observe that
course information is missing CS-347
prereq information is missing CS-315
©Silberschatz, Korth and Sudarshan5.14Database System Concepts - 7th Edition
Left Outer Join
▪ course natural left outer join prereq
▪ In relational algebra: course ⟕ prereq
©Silberschatz, Korth and Sudarshan5.15Database System Concepts - 7th Edition
Right Outer Join
▪ course natural right outer join prereq
▪ In relational algebra: course ⟖ prereq
©Silberschatz, Korth and Sudarshan5.16Database System Concepts - 7th Edition
Full Outer Join
▪ course natural full outer join prereq
▪ In relational algebra: course ⟗ prereq
©Silberschatz, Korth and Sudarshan5.17Database System Concepts - 7th Edition
Joined Types and Conditions
▪ 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.
▪ 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 Sudarshan5.18Database System Concepts - 7th Edition
Joined Relations – Examples
▪ course natural right outer join prereq
▪ course full outer join prereq using (course_id)
©Silberschatz, Korth and Sudarshan5.19Database System Concepts - 7th 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 Sudarshan5.20Database System Concepts - 7th Edition
Joined Relations – Examples
▪ course natural right outer join prereq
▪ course full outer join prereq using (course_id)
©Silberschatz, Korth and Sudarshan5.21Database System Concepts - 7th Edition
Views
▪ In some cases, it is not desirable for all users to see the entire logical
model (that is, all and complete actual relations stored in the database.)
▪ Consider a person who needs to know an instructor’s name and
department, but not the salary. This person should see a relation,
described in SQL, as:
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 Sudarshan5.22Database System Concepts - 7th 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 Sudarshan5.23Database System Concepts - 7th Edition
View Definition and Use
▪ A view of instructors without their salar
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 Sudarshan5.24Database System Concepts - 7th Edition
Views Defined Using Other Views
▪ create view physics_fall_2017 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 = '2017’;
▪ create view physics_fall_2017_watson as
select course_id, room_number
from physics_fall_2017
where building= 'Watson';
©Silberschatz, Korth and Sudarshan5.25Database System Concepts - 7th Edition
View Expansion
▪ Expand the view :
create view physics_fall_2017_watson as
select course_id, room_number
from physics_fall_2017
where building= 'Watson'
▪ To:
create view physics_fall_2017_watson as
select course_id, room_number
from (select course.course_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 = '2017')
where building= 'Watson';
©Silberschatz, Korth and Sudarshan5.26Database System Concepts - 7th Edition
Materialized Views
▪ Certain database systems allow view relations to be physically stored.
• Physical copy created when the view is defined.
• Such views are called Materialized views:
▪ If relations used in the query are updated, the materialized view result
becomes out of date
• Need to maintain the view, by updating the view whenever the
underlying relations are updated.
©Silberschatz, Korth and Sudarshan5.27Database System Concepts - 7th 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 into the instructor
relation
• Must have a value for salary.
▪ Two approaches
• Reject the insert
• Insert the tuple
('30765', 'Green', 'Music', null)
into the instructor relation
©Silberschatz, Korth and Sudarshan5.28Database System Concepts - 7th 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');
▪ Issues
• Which department, if multiple departments in Taylor?
• What if no department is in Taylor?
©Silberschatz, Korth and Sudarshan5.29Database System Concepts - 7th 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 Sudarshan5.30Database System Concepts - 7th Edition
View Updates in SQL
▪ 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
specifications.
• 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 Sudarshan5.31Database System Concepts - 7th Edition
Transactions
▪ A transaction consists of a sequence of query and/or update
statements and is a “unit” of work
▪ The SQL standard specifies that a transaction begins implicitly when an
SQL statement is executed.
▪ The transaction must end with one of the following statements:
• Commit work. The updates performed by the transaction become
permanent in the database.
• Rollback work. All the updates performed by the SQL statements in
the transaction are undone.
▪ Atomic transaction
• either fully executed or rolled back as if it never occurred
▪ Isolation from concurrent transactions
©Silberschatz, Korth and Sudarshan5.32Database System Concepts - 7th 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 $10.00 an hour
• A customer must have a (non-null) phone number
©Silberschatz, Korth and Sudarshan5.33Database System Concepts - 7th Edition
Constraints on a Single Relation
▪ not null
▪ primary key
▪ unique
▪ check (P), where P is a predicate
©Silberschatz, Korth and Sudarshan5.34Database System Concepts - 7th Edition
Not Null Constraints
▪ not null
• Declare name and budget to be not null
name varchar(20) not null
budget numeric(12,2) not null
©Silberschatz, Korth and Sudarshan5.35Database System Concepts - 7th Edition
Unique Constraints
▪ 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 Sudarshan5.36Database System Concepts - 7th Edition
The check clause
▪ The check (P) clause specifies a predicate P that must be satisfied by
every tuple in specific relation.
▪ Example: ensure that a 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 Sudarshan5.37Database System Concepts - 7th 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 of 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 Sudarshan5.38Database System Concepts - 7th Edition
Referential Integrity (Cont.)
▪ Foreign keys can be specified as part of the SQL create table
statement
foreign key (dept_name) references department
▪ By default, a foreign key references the primary-key attributes of the
referenced table.
▪ SQL allows a list of attributes of the referenced relation to be specified
explicitly.
foreign key (dept_name) references department (dept_name)
©Silberschatz, Korth and Sudarshan5.39Database System Concepts - 7th Edition
Cascading Actions in Referential Integrity
▪ When a referential-integrity constraint is violated, the normal procedure is to
reject the action that caused the violation.
▪ An alternative, in case of deletion or update is to cascade
create table course (
(…
dept_name varchar(20),
foreign key (dept_name) references department
on delete cascade
on update cascade,
. . .)
▪ Instead of cascade we can use :
• set null,
• set default
©Silberschatz, Korth and Sudarshan5.40Database System Concepts - 7th Edition
Complex Check Conditions
▪ The predicate in the check clause can be an arbitrary predicate that can
include a subquery.
check (time_slot_id in (select time_slot_id from time_slot))
The check condition states that the time_slot_id in each tuple in the
section relation is actually the identifier of a time slot in the time_slot
relation.
• The condition has to be checked not only when a tuple is inserted or
modified in section , but also when the relation time_slot changes
©Silberschatz, Korth and Sudarshan5.41Database System Concepts - 7th Edition
Assertions
▪ An assertion is a predicate expressing a condition that we wish the
database always to satisfy.
▪ The following constraints, can be expressed using assertions:
▪ For each tuple in the student relation, the value of the attribute tot_cred
must equal the sum of credits of courses that the student has completed
successfully.
▪ An instructor cannot teach in two different classrooms in a semester in the
same time slot
▪ An assertion in SQL takes the form:
create assertion <assertion-name> check (<predicate>);
©Silberschatz, Korth and Sudarshan5.42Database System Concepts - 7th Edition
Triggers
©Silberschatz, Korth and Sudarshan5.43Database System Concepts - 7th 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.
• 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 Sudarshan5.45Database System Concepts - 7th Edition
Trigger to Maintain credits_earned value
▪ 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 atomic
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 Sudarshan5.46Database System Concepts - 7th 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 Sudarshan5.47Database System Concepts - 7th Edition
Large-Object Types
▪ Large objects (photos, videos, CAD files, etc.) are stored as a large
object:
• blob: binary large object – the 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 – the 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 Sudarshan5.48Database System Concepts - 7th Edition
User-Defined Types
▪ create type construct in SQL creates a user-defined type
create type Dollars as numeric (12,2) final
▪ Example:
create table department
(dept_name varchar (20),
building varchar (15),
budget Dollars);
©Silberschatz, Korth and Sudarshan5.49Database System Concepts - 7th 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.
▪ Example:
create domain degree_level varchar(10)
constraint degree_level_test
check (value in ('Bachelors', 'Masters', 'Doctorate'));
©Silberschatz, Korth and Sudarshan5.50Database System Concepts - 7th Edition
Index Creation
▪ Many queries reference only a small proportion of tuples in a table.
▪ It is inefficient for the system to read every tuple to find a tuple with a
particular value
▪ An index on an attribute of a relation is a data structure that allows the
database system to find those tuples in the relation that have a specified
value for that attribute efficiently, without scanning through all the tuples of
the relation.
▪ We create an index with the create index command
create index <name> on <relation-name> (attribute);
©Silberschatz, Korth and Sudarshan5.51Database System Concepts - 7th Edition
Index Creation Example
▪ 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)
▪ The query:
select *
from student
where ID = '12345'
can be executed by using the index to find the required tuple, without
looking at all tuples of student
©Silberschatz, Korth and Sudarshan5.52Database System Concepts - 7th Edition
Authorization
▪ We may assign a user several forms of authorization on parts of the
database.
• Read - allows reading, but not modification of data.
• Insert - allows insertion of new data, but not modification of existing
data.
• Update - allows modification, but not deletion of data.
• Delete - allows deletion of data.
▪ Each of these types of authorizations is called a privilege. We may
authorize the user all, none, or a combination of these types of privileges
on specified parts of a database, such as a relation or a view.
©Silberschatz, Korth and Sudarshan5.53Database System Concepts - 7th Edition
Authorization (Cont.)
▪ Forms of authorization to modify the database schema:
• Index - allows creation and deletion of indices.
• Resources - allows creation of new relations.
• Alteration - allows addition or deletion of attributes in a relation.
• Drop - allows deletion of relations.
©Silberschatz, Korth and Sudarshan5.54Database System Concepts - 7th Edition
Authorization Specification in SQL
▪ The grant statement is used to confer authorization
grant <privilege list> on <relation or view > to <user list>
▪ <user list> is:
• a user-id
• public, which allows all valid users the privilege granted
▪ Example:
• grant select on department to Amit, Satoshi
▪ Granting a privilege on a view does not imply granting any privileges on
the underlying relations.
▪ The grantor of the privilege must already hold the privilege on the
specified item (or be the database administrator).
©Silberschatz, Korth and Sudarshan5.55Database System Concepts - 7th Edition
Privileges in SQL
▪ select: allows read access to relation, or the ability to query using the
view
• Example: grant users U1, U2, and U3 select authorization on the
instructor relation:
grant select on instructor to U1, U2, U3
▪ insert: the ability to insert tuples
▪ update: the ability to update using the SQL update statement
▪ delete: the ability to delete tuples.
▪ all privileges: used as a short form for all the allowable privileges
©Silberschatz, Korth and Sudarshan5.56Database System Concepts - 7th Edition
Revoking Authorization in SQL
▪ The revoke statement is used to revoke authorization.
revoke <privilege list> on <relation or view> from <user list>
▪ Example:
revoke select on student from U1, U2, U3
▪ <privilege-list> may be all to revoke all privileges.
▪ If <user-list> includes public, all users lose the privilege except those
granted it explicitly.
▪ If the same privilege was granted twice to the same user by different
grantees, the user may retain the privilege after the revocation.
▪ All privileges that depend on the privilege being revoked are also
revoked.
©Silberschatz, Korth and Sudarshan5.57Database System Concepts - 7th Edition
End of Chapter 5