SEN235 Introduction to Database Systems

(Fall 2022)
Intermediate SQL
Asst.Prof.Dr. Hasan ÇİFCİ
 Agenda

▪ Join Expressions
▪ Views
▪ Transactions
▪ Integrity Constraints
▪ SQL Data Types and Schemas
▪ Index Definition in SQL
▪ Authorization

2/48
 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
• Three types of joins:
– Natural join
– Inner join
– Outer join

3/48
 Natural Join in SQL

• Natural join matches tuples with the same values for all common attributes and
retains only one copy of each common column.
• List the names of students along with the course ID of the courses that they take
– select name, course_id
from student, takes
where student.ID = takes.ID;
• Same query in SQL with “natural join” construct
– select name, course_id
from student natural join takes;
• 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 ;

4/48
Student and Takes Relation

5/48
student natural join takes

6/48
 Dangerous in Natural Join

• Beware of unrelated attributes with same name which get equated

incorrectly
• Example -- List the names of students instructors 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. (Because student and course both have column
titled “dept_name”)
• The correct version (above), correctly outputs such pairs.

7/48
 Inner Join

▪ The INNER JOIN keyword selects records that have matching values in both
tables.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

10/48
 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.
• Three forms of outer join:
– left outer join
– right outer join
– full outer join

11/48
 Left Outer Join

▪ The LEFT JOIN keyword returns all records from the left table (table1), and
the matching records from the right table (table2).
▪ The result is 0 records from the right side, if there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

12/48
 Right Outer Join

▪ The RIGHT JOIN keyword returns all records from the right table (table2),
and the matching records from the left table (table1).
▪ The result is 0 records from the left side, if there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

13/48
 Full Outer Join

▪ The FULL OUTER JOIN keyword returns all records when there is a match in
left (table1) or right (table2) table records.
▪ Tip: FULL OUTER JOIN and FULL JOIN are the same.
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

14/48
 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.

15/48
 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.

16/48
 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.

17/48
 View Definition and Use

• 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;

18/48
 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.
Example:
• 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'; 19/48
 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';

20/48
 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 view:
• 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.

21/48
 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

22/48
 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 building?
– What if no department is in Taylor building?

• create view history_instructors as

select *
from instructor
where dept_name= 'History';
• What happens if we insert
('25566', 'Brown', 'Biology', 100000)
into history_instructors?
23/48
 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
– An atomic transaction is an indivisible and irreducible series of database
operations such that either all occurs, or nothing occurs.
– either fully executed or rolled back as if it never occurred.

24/48
 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

25/48
 Constraints on a Single Relation

• not null
• primary key
• unique
• check (P), where P is a predicate

26/48
 Not Null Constraints

• not null
– Declare name and budget to be not null
name varchar(20) not null
budget numeric(12,2) not null

27/48
 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).

28/48
 The check clause

• The check (P) clause specifies a predicate P that must be satisfied by every tuple
in a relation.
• 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')))

29/48
 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.
• 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)

30/48
 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 delete 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

31/48
 Integrity Constraint Violation During Transactions

• Consider:
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

32/48
 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

33/48
 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>);

34/48
 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

35/48
 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.

36/48
 User-Defined Types

• create type construct in SQL creates user-defined type

create type Dollars as numeric (12,2) final

• Example:
create table department
(dept_name varchar (20),
building varchar (15),
budget Dollars);

37/48
 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'));

38/48
 Index Creation

• Many queries reference only a small proportion of the records in a table.

• It is inefficient for the system to read every record to find a record with 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);

39/48
 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 record, without looking at all
records of student

40/48
 Authorization

• We may assign a user several forms of authorizations 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.
• 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.

41/48
 Authorization Specification in SQL

• The grant statement is used to confer authorization

grant <privilege list> on <relation or view > to <user list>
• <user list> can be:
– a user-id
– public, which allows all valid users the privilege granted
– A role (more on this later)
• 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).

42/48
 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

43/48
 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 the revokee may hold.
• If <revokee-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.

44/48
 Roles

• A role is a way to distinguish among various users as far as what these users can
access/update in the database.
• To create a role we use:
create a role <name>
• Example:
– create role instructor
• Once a role is created we can assign “users” to the role using:
– grant <role> to <users>

45/48
 Roles Example

• create role instructor;

• grant instructor to Amit;
• Privileges can be granted to roles:
– grant select on takes to instructor;
• Roles can be granted to users, as well as to other roles
– create role teaching_assistant
– grant teaching_assistant to instructor;
• Instructor inherits all privileges of teaching_assistant
• Chain of roles
– create role dean;
– grant instructor to dean;
– grant dean to Satoshi;

46/48
 Authorization on Views

• create view geo_instructor as

(select *
from instructor
where dept_name = 'Geology');
• grant select on geo_instructor to geo_staff
• Suppose that a geo_staff member issues
– select *
from geo_instructor;
• What if
– geo_staff does not have permissions on instructor?
– Creator of view did not have some permissions on instructor?

47/48
Thank you…
Hasan ÇİFCİ