Chapter 4: Intermediate SQL
■ Join Expressions
■ Views
■ Transactions
■ Integrity Constraints
■ SQL Data Types and Schemas ■ Authorization
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.1 ©Silberschatz, Korth and Sudarshan
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.
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.2 ©Silberschatz, Korth and Sudarshan
Join operations – Example
■ Relation course
■ Relation prereq
■ Note:prereqinformationmissingforCS-315andcourse information missing for CS-437.
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.3 ©Silberschatz, Korth and Sudarshan
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.
Left Outer Join
■ course natural left outer join prereq
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
Note: read prere_id as prereq_id
4.4 ©Silberschatz, Korth and Sudarshan
Right Outer Join
■ course natural right outer join prereq
Full Outer Join
■ course natural full outer join prereq
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.5 ©Silberschatz, Korth and Sudarshan
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.
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.6 ©Silberschatz, Korth and Sudarshan
Joined Relations – Examples
■ course inner join prereq on
course.course_id = prereq.course_id
■ course left outer join prereq on
course.course_id = prereq.course_id
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.7 ©Silberschatz, Korth and Sudarshan
Joined Relations – Examples
■ course natural right outer join prereq
■ coursefullouterjoinprerequsing(course_id)
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.8 ©Silberschatz, Korth and Sudarshan
Example
■ Find the course ID, semester, year and title of each course offered by the Comp. Sci. department
select section.course_id, semester, year, title
from section, course
where section.course_id = course.course_id and
dept_name = ‘Comp. Sci.’ ■ Same with join operation
select section.course_id, semester, year, title
from section join course
where dept_name = ‘Comp. Sci.’ .
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.9 ©Silberschatz, Korth and Sudarshan
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
■ Aviewprovidesamechanismtohidecertaindatafromtheviewof
certain users.
■ Anyrelationthatisnotoftheconceptualmodelbutismadevisible
to a user as a “virtual relation” is called a view.
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.10 ©Silberschatz, Korth and Sudarshan
View Definition
■ A view is defined using the create view statement which has the form
create view v as < query expression >
where
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.
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.11 ©Silberschatz, Korth and Sudarshan
Example Views
■ Aviewofinstructorswithouttheirsalary
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;
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.12 ©Silberschatz, Korth and Sudarshan
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’;
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.13 ©Silberschatz, Korth and Sudarshan
View Expansion
■ Expand use of a view in a query/another view
create view physics_fall_2009_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 = ’2009’)
where building = ’Watson’;
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.14 ©Silberschatz, Korth and Sudarshan
Views Defined Using Other Views
■ One view may be used in the expression defining another view,
■ A view relation v1 is said to depend directly on a view relation v2 if v2 is used in the expression defining v1
■ A view relation v1 is said to depend on view relation v2 if either v1 depends directly to v2 or there is a path of dependencies from v1 to v2
■ A view relation v is said to be recursive if it depends on itself.
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.15 ©Silberschatz, Korth and Sudarshan
View Expansion
■ Awaytodefinethemeaningofviewsdefinedintermsofotherviews.
■ Let view v1 be defined by an expression e1 that may itself contain
uses of view relations.
■ View expansion of an expression repeats the following replacement step:
repeat
Find any view relation vi in e1
Replace the view relation vi by the expression defining vi
until no more view relations are present in e1
■ Aslongastheviewdefinitionsarenotrecursive,thisloopwill terminate.
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.16 ©Silberschatz, Korth and Sudarshan
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
into the instructor relation.
(’30765’, ’Green’, ’Music’, null)
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.17 ©Silberschatz, Korth and Sudarshan
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.
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.18 ©Silberschatz, Korth and Sudarshan
And Some Not at All
■ create view history_instructors as
select *
from instructor
where dept_name= ’History’;
■ Insert(’25566’,’Brown’,’Biology’,100000)into history_instructors
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.19 ©Silberschatz, Korth and Sudarshan
Transactions
■ Unit of work
■ Atomic transaction
● either fully executed or rolled back as if it never occurred
■ Isolation from concurrent transactions
■ Transactions begin implicitly
● Ended by commit work or rollback work
■ But default on most databases: each SQL statement
commits automatically
● Can turn off auto commit for a session (e.g. using API) ● In SQL:1999, can use: begin atomic …. end
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.20 ©Silberschatz, Korth and Sudarshan
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.
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.21 ©Silberschatz, Korth and Sudarshan
Constraints on a Single Relation
■ not null
■ primary key
■ unique
■ check (P), where P is a predicate
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.22 ©Silberschatz, Korth and Sudarshan
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).
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.23 ©Silberschatz, Korth and Sudarshan
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),
4.24 ©Silberschatz, Korth and Sudarshan
check (semester in (’Fall’, ’Winter’, ’Spring’, Database System Concepts – 6th Edition
’Summer’))
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
);
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”.
■ LetAbeasetofattributes. LetRandSbetwo relations that contain attributes A and where A is the primarykeyofS.Aissaidtobea foreignkeyofRif for any values of A appearing in R these values also appear in S.
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.25 ©Silberschatz, Korth and Sudarshan
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
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.26 ©Silberschatz, Korth and Sudarshan
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
● Also not supported by anyone
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.27 ©Silberschatz, Korth and Sudarshan
Built-in 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
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.28 ©Silberschatz, Korth and Sudarshan
Other Features
■ 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)
■ Large objects
● book review clob(10KB)
● image blob(10MB), movie blob(2GB)
■ 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);
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.29 ©Silberschatz, Korth and Sudarshan
■ 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’));
■ 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 char data ● When a query returns a large object, a pointer is returned rather than
the large object itself.
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.30 ©Silberschatz, Korth and Sudarshan
Authorization
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.
Forms of authorization to modify the database schema
■ Index – allows creation and deletion of indices.
■ Resources – allows creation of new relations.
■ Alteration-allowsadditionordeletionofattributesinarelation. ■ Drop – allows deletion of relations.
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.31 ©Silberschatz, Korth and Sudarshan
Authorization Specification in SQL
■ The grant statement is used to confer authorization grant
■
● a user-id
● public, which allows all valid users the privilege granted ● A role (more on this later)
■ 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).
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.32 ©Silberschatz, Korth and Sudarshan
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 branch 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.
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.33 ©Silberschatz, Korth and Sudarshan
Revoking Authorization in SQL
■ The revoke statement is used to revoke authorization. revoke
■ Example:
revoke select on branch from U1, U2, U3
■
hold.
■ If
■ If the same privilege was granted twice to the same user by different grantees, the user may retain the privilege after the revocation.
■ Allprivilegesthatdependontheprivilegebeingrevokedarealso revoked.
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.34 ©Silberschatz, Korth and Sudarshan
Roles
■ create role instructor;
■ Privileges can be granted to roles:
● grantselectontakestoinstructor;
■ Roles can be granted to users, as well as to other roles
● createrolestudent
● grantinstructortoAmit; ● createroledean;
● grantinstructortodean; ● grantdeantoSatoshi;
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.35 ©Silberschatz, Korth and Sudarshan
Authorization on Views
■ create view geo_instructor as
(select *
from instructor
where dept_name = ’Geology’);
■ grant select on geo_instructor to staff ■ Suppose that a staff member issues
● select*
from geo_instructor;
■ What if
● staffdoesnothavepermissionsoninstructor?
● creatorofviewdidnothavesomepermissionsoninstructor?
Database System Concepts – 6th Edition
Modified by T. Suel for CS6083, NYU School of Engineering, Spring 2020
4.36 ©Silberschatz, Korth and Sudarshan