Database Systems Concepts

Lecture 3
Dr. Abdulmalek Alqobaty

Taiz University, 2020

Intermediate
SQL
 Introduction to Relational Database

Example: The university Database

Database Schema
department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
prereq(course_id, prereq_id)
instructor(ID, name, dept_name, salary)
student(ID, name, dept_name, tot cred)
classroom(building, room_number, capacity)
section(course_id, sec_id, semester, year, building, room number, time_slot_id)
teaches(ID, course_id, sec_id, semester, year)
takes(ID, course_id, sec_id, semester, year, grade)
advisor(s_ID, i_ID)
time_slot(time_slot_id, day, start_time, end_time)
 Intermediate SQL
Join Expressions
We introduced the natural join operation. SQL provides other forms of the join
operation, including the ability to specify an explicit join predicate, and the
ability to include in the result tuples that are excluded by natural join. We shall
discuss these forms of join in this section.
 Join Conditions
we saw the join . . . using clause, which is a form of natural join that only requires
values to match on specified attributes. The on condition allows a general
predicate over the relations being joined.
select *
from student join takes on student.ID = takes.ID;
The on condition above specifies that a tuple from student matches a tuple from
takes if their ID values are equal. The join expression in this case is almost the
same as the join expression student natural join takes.
select student.ID as ID, name, dept_name, tot cred, course_id, sec_id,
semester, year, grade from student join takes on student.ID = takes.ID;
The on condition can express any SQL predicate, and thus a join expressions using
the on condition can express a richer class of join conditions than natural join.
 Intermediate SQL
There are in fact three forms of outer join:
 The left outer join preserves tuples only in the relation named before (to
the left of) the left outer join operation.
select * from student natural left outer join takes;

select ID
from student natural left outer join takes
where course_id is null;
 Intermediate SQL
 The right outer join preserves tuples only in the relation named after (to the
right of) the right outer join operation.

The on clause can be used with outer joins.

select * from student left outer join takes on student.ID = takes.ID;
 The full outer join preserves tuples in both relations.
The full outer join is a combination of the left and right outer-join types. After the operation
computes the result of the inner join, it extends with nulls those tuples from the left-hand-side
relation that did not match with any from the right-hand side relation, and adds them to the result.
 Intermediate SQL
 Join Types and Conditions
To distinguish normal joins from outer joins, normal joins are called inner joins
in SQL. A join clause can thus specify inner join instead of outer join to specify
that a normal join is to be used. The keyword inner is, however, optional. The
default join type, when the join clause is used without the outer prefix is the
inner join. Similarly, natural join is equivalent to natural inner join.

select * select *
from student join takes using(ID); from student inner join takes using (ID);

The join operations that do not preserve nonmatched tuples are called inner join
operations, to distinguish them from the outer-join operations.
 Intermediate SQL
Views
In our examples up to this point, we have operated at the logical-model level. That is, we have
assumed that the relations in the collection we are given are the actual relations stored in the
database.
 View Definition
To define a view, we must give the view a name and must state the query that
computes the view.
create view v as <query expression>;
Where the view name is represented by v, and <query expression> is a legal query
expression
create view physics_fall_2009 as
Examples
select course.course_id, sec_id, building, room_number
from course, section
where course.course_id = section.course_id
create view faculty as and course.dept_name = ’Physics’
select ID, name, dept_name and section.semester = ’Fall’
from instructor; and section.year = ’2009’;

 Using Views in SQL Queries

Once we have defined a view, we can use the view select course_id
name to refer to the virtual relation that the view from physics_fall_2009
generates. where building= ’Watson’;
 Intermediate SQL
 Materialized Views
Certain database systems allow view relations to be stored, but they make sure that, if
the actual relations used in the view definition change, the view is kept up-to-date.
Such views are called materialized views. The process of keeping the materialized
view up-to-date is called materialized view maintenance (or often, just view
maintenance)
View maintenance can be done immediately when any of the relations on which the
view is defined is updated. Some database systems, however, perform view
maintenance lazily, when the view is accessed.

 Update of a View

Since we allow a view name to appear wherever a relation name is allowed, we,
then, can write:
insert into faculty
values (’30765’, ’Green’, ’Music’);
This insertion must be represented by an insertion into the relation instructor,
since instructor is the actual relation from which the database system
constructs the view faculty.
 Intermediate SQL

In general, an SQL view is said to be updatable (that is, inserts, updates or

deletes can be applied on the view) if the following conditions are all satisfied
by the query defining the view:

o The from clause has only one database relation.

o The select clause contains only attribute names of the relation, and does not
have any expressions, aggregates, or distinct specification.
o Any attribute not listed in the select clause can be set to null; that is, it does not
have a not null constraint and is not part of a primary key.
o The query does not have a group by or having clause.

Under these constraints, the update, insert, and delete operations would be
allowed on the following view:

create view history_instructors as select *

from instructor
where dept_name = ’History’;
 Intermediate SQL
Transactions
A transaction consists of a sequence of query and/or update statements. The SQL
standard specifies that a transaction begins implicitly when an SQL statement is executed.
One of the following SQL statements must end the transaction:
 Commit work: commits the current transaction; that is, it makes the updates
performed by the transaction become permanent in the database. After the
transaction is committed, a new transaction is automatically started.
 Rollback work: causes the current transaction to be rolled back; that is, it undoes
all the updates performed by the SQL statements in the transaction. Thus, the
database state is restored to what it was before the first statement of the
transaction was executed. The keyword work is optional.
Once a transaction has executed commit work, its effects can no longer be undone by
rollback work. In the event of some failure, a power outage, or a system crash, the
database system guarantees that a transaction’s effects will be rolled back if it has not
yet executed commit work. The rollback occurs when the system restarts.
By either committing the actions of a transaction after all its steps are completed, or
rolling back all its actions in case the transaction could not complete all its actions
successfully, the database provides an abstraction of a transaction as being atomic, that
is, indivisible. Either all the effects of the transaction are reflected in the database, or
none are (after rollback).
 Intermediate SQL

Integrity Constraints
The integrity constraints guard against accidental damage to the database.
 Constraints on a Single Relation
The create table command may also include integrity-constraint statements.
In addition to the primary-key constraint, there are a number of other ones
that can be included in the create table command. The allowed integrity
constraints include:
 not null
name varchar(20) not null
budget numeric(12,2) not null
 Unique
unique (Aj1 , Aj2, . . . , Ajm )
The unique specification says that attributes Aj1 , Aj2, . . . , Ajm form a candidate
key; candidate key attributes are permitted to be null unless they have explicitly
been declared to be not null.
 Integrity Constraints

 check (<predicate>)

check (semester in (’Fall’, ’Winter’, ’Spring’, ’Summer’))

Note that, the check clause permits attribute domains to be restricted in
powerful ways that most programming-language type systems do not
permit. We could specify the following referential-integrity constraint on the
relation section:
check (time_slot_id in (select time_slot_id from time_slot))
Complex check conditions can be useful when we want to ensure integrity of
data, but may be costly to test. For example, the predicate in the check clause
would not only have to be evaluated when a modification is made to the
section relation, but may have to be checked if a modification is made to the
time_slot relation because that relation is referenced in the subquery.
 Intermediate SQL
 Referential Integrity
Often, we wish to ensure 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. This
condition is called referential integrity. Foreign keys can be specified as part of
the SQL create table statement by using the foreign key clause by using SQL DDL.
By default, in SQL a foreign key references the primary-key attributes of the
referenced table. SQL also supports a version of the references clause where a list
of attributes of the referenced relation can be specified explicitly. The specified list
of attributes must, however, be declared as a candidate key of the referenced
relation, using either a primary key constraint, or a unique constraint.
We can use the following short form as part of an attribute definition to declare
that the attribute forms a foreign key:
dept_name varchar(20) references department
However, a foreign key clause can specify that if a delete or update action on the
referenced relation violates the constraint, then, instead of rejecting the action, the
system must take steps to change the tuple in the referencing relation to restore
the constraint.
 Intermediate SQL

Consider this definition of an integrity constraint on the relation course:

create table course
(...
foreign key (dept_name) references department
on delete cascade
on update cascade,
. . . );
Because of the clause on delete (or update) cascade associated with the foreign-
key declaration, if a delete (or update) of a tuple in department results in this
referential-integrity constraint being violated, the system does not reject the delete.
Instead, the delete (or update) “cascades” to the course relation, deleting (or
updating) the tuple that refers to the department that was deleted.
SQL also allows the foreign key clause to specify actions other than cascade, if the
constraint is violated: The referencing field (here, dept_name) can be set to null (by
using set null in place of cascade), or to the default value for the domain (by using
set default).
 Intermediate SQL
 Assertion
An assertion is a predicate expressing a condition that we wish the database
always to satisfy. Domain constraints and referential-integrity constraints are
special forms of assertions. However, there are many constraints that we cannot
express by using only these special forms. Two examples of such constraints are:
 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>;
In the following assertion, we show how the constraints can be written in SQL.
create assertion credits_earned_constraint check
(not exists (select ID
from student
where tot_cred <> (select sum(credits)
from takes natural join course
where student.ID= takes.ID
and grade is not null and grade<> ’F’ )
 Intermediate SQL
SQL Data Types and Schemas
 Date and Time Types in SQL
In addition to the basic data types, the SQL standard supports several data types
relating to dates and times:
 date: A calendar date containing a year, month, and day. date ’2001-04-25’
 time: The time of day, in hours, minutes, and seconds. A variant, time(p), can be
used to specify the number of fractional digits for seconds (the default being 0). It
is also possible to store time-zone information along with the time by specifying
time with timezone: time ’09:30:00’
 timestamp: A combination of date and time. A variant, timestamp(p), can be
used to specify the number of fractional digits for seconds (the default here being
6). Time-zone information is also stored if with timezone is specified. Date and
time values can be specified like this: timestamp ’2001-04-25 10:29:01.45’
 Default Values
SQL allows a default value to be specified for an attribute as illustrated by the
following create table statement:
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));
 Intermediate SQL

 Index Creation
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. For
example, if we create in index on attribute ID of relation student, the database
system can find the record with any specified ID value, such as 22201, or 44553,
directly, without reading all the tuples of the student relation.

Although the SQL language does not formally define any syntax for creating
indices, many databases support index creation using the syntax illustrated below.

CREATE INDEX index_name

ON table_name (column1, column2, ...);

create index studentID index on student(ID);

The above statement creates an index named studentID index on the attribute ID of
the relation student.
 Intermediate SQL

 Large-Object Types

Many current-generation database applications need to store attributes that

can be large (of many kilobytes), such as a photograph, or very large (of many
megabytes or gigabytes), such as a high-resolution medical image or video
clip.

SQL therefore provides large-object data types for character data (clob) and
binary data (blob). The letters “lob” in these data types stand for “Large
OBject.” For example, we may declare attributes.

book_review
clob(10KB)
image blob(10MB)
movie blob(2GB)
 Intermediate SQL
 User-Defined Types
SQL supports two forms of user-defined data types. The first form, which we cover
here, is called distinct types. The other form, called structured data types, allows the
creation of complex data types with nested record structures, arrays, and multisets.
Good type system should be able to detect such assignments or comparisons. To
support such checks, SQL provides the notion of distinct types. The create type clause
can be used to define new types. For example, the statements:
create type Dollars as numeric(12,2) final;
create type Pounds as numeric(12,2) final;
(The keyword final isn’t really meaningful in this context but is required by the
SQL:1999 standard for reasons we won’t get into here; some implementations allow
the final keyword to be omitted.)
create table department
(dept_name varchar (20),
building varchar (15),
budget Dollars);
As a result of strong type checking, the expression (department.budget+20) would not
be accepted since the attribute and the integer constant 20 have different types. Values
of one type can be cast (that is, converted) to another domain, as illustrated below:
cast (department.budget to numeric(12,2))
 Intermediate SQL
SQL provides drop type and alter type clauses to drop or modify types that
have been created earlier.
SQL had a similar but subtly different notion of domain (introduced in SQL-92),
which can add integrity constraints to an underlying type. For example, we could
define a domain DDollars as follows.
create domain DDollars as numeric(12,2) not null;
The domain DDollars can be used as an attribute type, just as we used the type
Dollars. However, there are two significant differences:
 Domains can have constraints, such as not null, specified on them, and can
have default values defined for variables of the domain type.
 Domains are not strongly typed. A
Note that, when applied to a domain, the check clause permits the schema
designer to specify a predicate that must be satisfied by any attribute declared to
be from this domain.
create domain YearlySalary numeric(8,2)
constraint salary_value_test check(value >= 29000.00);
create domain degree_level varchar(10)
constraint degree_level_test check (value in (’Bachelors’, ’Masters’, or ’Doctorate’));
 Intermediate SQL
SQL provides drop type and alter type clauses to drop or modify types that
have been created earlier.
SQL had a similar but subtly different notion of domain (introduced in SQL-92),
which can add integrity constraints to an underlying type. For example, we could
define a domain DDollars as follows.
create domain DDollars as numeric(12,2) not null;
The domain DDollars can be used as an attribute type, just as we used the type
Dollars. However, there are two significant differences:
 Domains can have constraints, such as not null, specified on them, and can
have default values defined for variables of the domain type.
 Domains are not strongly typed. A
Note that, when applied to a domain, the check clause permits the schema
designer to specify a predicate that must be satisfied by any attribute declared to
be from this domain.
create domain YearlySalary numeric(8,2)
constraint salary_value_test check(value >= 29000.00);
create domain degree_level varchar(10)
constraint degree_level_test check (value in (’Bachelors’, ’Masters’, or ’Doctorate’));
 Intermediate SQL

 Create Table Extensions

Applications often require creation of tables that have the same schema as an
existing table. SQL provides a create table like extension to support this task:
create table temp_instructor like instructor;
SQL:2003 provides a simpler technique to create a table containing the results of
a query. For example the following statement creates a table t1 containing the
results of a query.

create table t1 as
(select *
from instructor
where dept_name= ’Music’)
with data;

By default, the names and data types of the columns are inferred from the query
result. Due to SQL:2003 standard, if the with data clause is omitted, the table is
created but not populated with data.
 Intermediate SQL
Authorization
Authorizations on data include:
 Authorization to read data.
 Authorization to insert new data.
 Authorization to update data.
 Authorization to delete data.
Each of these types of authorizations is called a privilege.

 Granting of Privileges
The SQL standard includes the privileges select, insert, update, and delete. The
privilege all privileges can be used as a short form for all the allowable privileges.
The basic form of this statement is:
grant <privilege list>
on <relation name or view name>
to <user/role list>;
The privilege list allows the granting of several privileges in one command.
grant select on department to Amit, Satoshi;
This grant statement gives users Amit and Satoshi update authorization on the budget
attribute of the department relation:
grant update (budget) on department to Amit, Satoshi;
 Intermediate SQL
 The insert authorization on a relation allows a user to insert tuples into the
relation. The insert privilege may also specify a list of attributes; then inserts to
the relation must specify only these attributes. The delete authorization on a
relation allows a user to delete tuples from a relation.
 The user name public refers to all current and future users of the system. Thus,
privileges granted to public are implicitly granted to all current and future
users.
 Revoking of Privileges
To revoke an authorization, we use the revoke statement. It takes a form almost
identical to that of grant:
revoke <privilege list>
on <relation name or view name>
from <user/role list>;

Thus, to revoke the privileges that we granted previously, we write

revoke select on department from Amit, Satoshi;
revoke update (budget) on department from Amit, Satoshi ;
 Intermediate SQL
 Roles
 Any authorization that can be granted to a user can be granted to a role. Roles
are granted to users just as authorizations are. Roles can be created in SQL as
follows:
create role instructor;
 Roles can then be granted privileges just as the users can, as illustrated in this
statement:
grant select on takes
to instructor;
 Roles can be granted to users, as well as to other roles, as these statements
show:
grant dean to Amit;
create role dean;
grant instructor to dean;
grant dean to Satoshi;
 Thus the privileges of a user or a role consist of:
o All privileges directly granted to the user/role.
o All privileges granted to roles that have been granted to the user/role.
 Intermediate SQL
 Authorization on Views
In the university example, a staff member who needs to know the salaries of all
faculty in the Geology department, is not authorized to see information regarding
to other departments. Hence, the staff member must be denied direct access to the
instructor relation. But, he might be granted access to a view called geo_instructor,
consisting of only instructor tuples of Geology department.
create view geo_instructor as
(select *
from instructor
where dept_name = ’Geology’);
Suppose that the staff member issues the following SQL query:
select *
from geo_instructor;
A user who creates a view cannot be given update authorization on a view
without having update authorization on the relations used to define the view. If a
user creates a view on which no authorization can be granted, the system will
deny the view creation request. In our geo_instructor view example, the creator of
the view must have select authorization on the instructor relation.
 Intermediate SQL

 Authorizations on Schema
The SQL standard specifies a primitive authorization mechanism for the
database schema: Only the owner of the schema can carry out any modification
to the schema, such as creating or deleting relations, adding or dropping
attributes of relations, and adding or dropping indices. However, SQL includes a
references privilege that permits a user to declare foreign keys when creating
relations. The following grant statement allows user Mariano to create relations
that reference the key branch name of the branch relation as a foreign key:
grant references (dept_name) on department to Mariano;

 Transfer of Privileges
A user who has been granted some form of authorization may be allowed to pass
on this authorization to other users. If we wish to grant a privilege and to allow
the recipient to pass the privilege on to other users, we append the with grant
option clause to the appropriate grant command.
grant select on department to Amit with grant option;
 Intermediate SQL
 Revoking of Privileges

In transferred privileges, revocation of a privilege from a user/role may cause

other users/roles also to lose that privilege. This behavior is called cascading
revocation. In most database systems, cascading is the default behavior. However,
the revoke statement may specify restrict in order to prevent cascading
revocation:

revoke select on department from Amit, Satoshi restrict;

The following revoke statement revokes only the grant option, rather than the
actual select privilege:

revoke grant option for select on department from Amit;