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
Not supported on most databases
Database System Concepts - 6th Edition 4.1 ©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 4.2 ©Silberschatz, Korth and Sudarshan
Integrity Constraints on a Single Relation
not null
primary key
unique
check (P), where P is a predicate
Database System Concepts - 6th Edition 4.3 ©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 4.4 ©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),
check (semester in (’Fall’, ’Winter’, ’Spring’, ’Summer’))
);
Database System Concepts - 6th Edition 4.5 ©Silberschatz, Korth and Sudarshan
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.
Database System Concepts - 6th Edition 4.6 ©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 4.7 ©Silberschatz, Korth and Sudarshan
Integrity Constraint Violation During
Transactions
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 (next slide)
Database System Concepts - 6th Edition 4.8 ©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 <assertion-name> check <predicate>;
Also not supported by anyone
Database System Concepts - 6th Edition 4.9 ©Silberschatz, Korth and Sudarshan
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
Database System Concepts - 6th Edition 4.10 ©Silberschatz, Korth and Sudarshan
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.
Database System Concepts - 6th Edition 4.11 ©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 - allows addition or deletion of attributes in a relation.
Drop - allows deletion of relations.
Database System Concepts - 6th Edition 4.12 ©Silberschatz, Korth and Sudarshan
Authorization Specification in SQL
The grant statement is used to confer authorization
grant <privilege list>
on <relation name or view name> to <user list>
<user list> is:
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 4.13 ©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 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
Database System Concepts - 6th Edition 4.14 ©Silberschatz, Korth and Sudarshan
Revoking Authorization in SQL
The revoke statement is used to revoke authorization.
revoke <privilege list>
on <relation name or view name> from <user list>
Example:
revoke select on branch 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.
Database System Concepts - 6th Edition 4.15 ©Silberschatz, Korth and Sudarshan
Roles
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;
Database System Concepts - 6th Edition 4.16 ©Silberschatz, Korth and Sudarshan