database system concepts - ulisboa · “database system concepts” ... date: dates, containing a...

DatabaseSystem

Concepts

Data Typesand Domains

Constraints

Authorization

Functions andProcedures

Triggers

Examples

Database System ConceptsChapter 4(+8): Advanced SQL

Departamento de Engenharia InformaticaInstituto Superior Tecnico

1st Semester2007/2008

Slides (fortemente) baseados nos slides oficiais do livro“Database System Concepts”c©Silberschatz, Korth and Sudarshan.

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Outline

1 Data Types and Domains

2 Constraints

3 Authorization

4 Functions and Procedures

5 Triggers

6 Examples

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Time and Dates

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’ or 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 daySubtracting a date/time/timestamp value from anothergives an interval valueInterval values can be added to date/time/timestampvalues

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Time and Dates (cont.)

Can extract values of individual fields fromdate/time/timestamp

Example: extract (year from r .starttime)

Can cast string types to date/time/timestampcast <string-valued-expression> as date

cast ’2005-7-27’ as date

cast <string-valued-expression> as time

cast ’09:00:30’ as time

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Large-Object Types

Large objects (photos, videos, CAD files, etc.) are stored as alarge object:

blob: binary large object — object is a large collection ofuninterpreted binary data (whose interpretation is left toan application outside of the database system)

clob: character large object — object is a large collectionof character data

When a query returns a large object, a pointer is returnedrather than the large object itself.

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

User-Defined Types

create type construct in SQL creates user-defined type

create type Dollars as numeric(12,2) final

create domain construct in SQL-92 creates user-defineddomain types

create domain person name char(20) not null

Types and domains are similar.

Domains can have constraints, such as not null, specifiedon them.

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Outline


2 Constraints

3 Authorization


5 Triggers

6 Examples

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Domain Constraints

Domain constraints are the most elementary form ofintegrity constraint.

They test values inserted in the database, and test queriesto ensure that the comparisons make sense.New domains can be created from existing data types

Example:

create domain Dollars numeric(12, 2)create domain Euros numeric(12,2)

We cannot assign or compare a value of type Dollars to avalue of type Euros. However, we can convert types:

cast r.A as Euros

(Note: In this case, we should also multiply by thedollar-to-euro conversion-rate)

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Integrity Constraints

Integrity constraints guard against accidental damage to thedatabase, by ensuring that authorized changes to the databasedo not result in a loss of data consistency.

not null

primary key

unique

check (P), where P is a predicate

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Integrity Constraints — Unique

unique(A1, A2, . . . ,Am)

The unique specification states that the attributesA1, A2, . . . ,Am form a candidate key

Example:

create table branch (branch name char(15),branch city char(30),assets integer not null,primary key (branch name)unique(branch city)

)

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Integrity Constraints - Check

check (P)

Example: Ensure that the values of assets arenon-negative.

create table branch (branch name char(15),branch city char(30),assets integer,primary key (branch name),check (assets >= 0)

)

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Integrity Constraints - Check (cont.)

The check clause in permits domains to be restricted

Example: use check clause to ensure that an hourly wagedomain allows only values greater than a specified value

create domain hourly wage numeric(5,2)constraint value test check(value >= 4.0)

The domain has a constraint that ensures that thehourly wage is greater than 4.0

The clause constraint value test is optional; useful toindicate which constraint an update violated

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Referential Integrity

Ensures that a value that appears in one relation for agiven set of attributes also appears for a certain set ofattributes in another relation.

Example: If ’Perryridge’ is a branch name appearing in oneof the tuples in the account relation, then there exists atuple in the branch relation for branch ’Perryridge’.

Foreign keys can be specified as part of the SQL createtable statement:

The primary key lists attributes that comprise the primarykeyThe unique clause lists attributes that comprise acandidate keyThe foreign key clause lists the attributes that comprisethe foreign key and the name of the relation referenced bythe foreign key.

By default, a foreign key references the primary key

attributes of the referenced table.

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Foreign Key Example

create table customer (customer name char(20),customer street char(30),customer city char(30),primary key (customer name))

create table account (account number char(10),branch name char(15),balance integer,primary key (account number),foreign key (branch name)

references branch)

create table depositor (customer name char(20),account number char(10),primary key (customer name, account number),foreign key (account number) references account,foreign key (customer name) references customer)

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Deleting and Updating Foreign Keys

Actions can be associated with foreign keys, when thetarget row is deleted or updated

use on delete <action> or on update <action>

The action can be on of

cascade: deletes/updates the rows that point to thedeleted rowset null: sets to null the rows that point to thedeleted/updated rowset default: sets to the default value the rows that point tothe deleted/updated rowno action: stops the deletion/update from occurring

foreign key (customer name) references customer

on delete cascade

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Assertions

An assertion is a predicate expressing a condition that wewish the database always to satisfy

An assertion in SQL takes the form

create assertion <assertion-name> check <predicate>

When an assertion is made, the system tests it for validity,and tests it again on every update that may violate theassertion

This testing may introduce a significant amount ofoverhead; hence assertions should be used with great care.

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Assertions (cont.)

The sum of all loan amounts for each branch must be less thanthe sum of all account balances at the branch

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Assertions (cont.)

The sum of all loan amounts for each branch must be less thanthe sum of all account balances at the branch

create assertion sum constraint check(not exists

(select ∗from branch

where (select sum(amount)from loan

where loan.branch name = branch.branch name)>=(select sum(balance)from account

where account.branch name = branch.branch name)))

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Outline


2 Constraints

3 Authorization


5 Triggers

6 Examples

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Forms of 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 modificationof 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 arelation.

Drop - allows deletion of relations.

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Authorization Specification

The grant statement is used to confer authorization

grant <privilege list>on <relation/view name> to <user list>

<user list> is:

user idpublic, which allows all valid users the privilege granteda role

Granting a privilege on a view does not imply granting anyprivileges on the underlying relations

The grantor of the privilege must already hold the privilegeon the specified item (or be the database administrator)

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Privileges in SQL

select: allows read access to relation,or the ability to queryusing the view

Example: grant users U1, U2, and U3 select authorizationon the branch relation:

grant select on branch to U1,U2,U3

insert: the ability to insert tuples

update: the ability to update using the SQL updatestatement

delete: the ability to delete tuples.

references: ability to declare foreign keys when creatingrelations

usage: authorizes a user to use a specified domain

all privileges: used as a short form for all the allowableprivileges

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Privilege To Grant Privileges

with grant option: allows a user who is granted a privilegeto pass the privilege on to other users

Example:

grant select on branch to U1 with grant option

gives U1 the select privileges on branch and allows U1 togrant this privilege to others

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Revoking Authorization

The revoke statement is used to revoke authorizationrevoke <privilege list> on <relation/view name>

from <user list> [restrict|cascade]

Example:

revoke select on branch from U1, U2, U3 cascade

<privilege-list> may be all to revoke all privilegesIf <user-list> includes public, all users lose the privilegeexcept those granted it explicitly

Revocation of a privilege from a user may cause otherusers also to lose that privilege (cascading)

With restrict, the revoke command fails if cascadingrevokes are required

All privileges that depend on the privilege being revokedare also revoked

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Granting of Privileges

The passage of authorization from one user to anothermay be represented by an authorization graph

DBA

U1

U4

U2

U5

U3

Requirement: All edges in an authorization graph must bepart of some path originating with the databaseadministratorMust prevent cycles of grants with no path from the root

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Authorization and Views

Users can be given authorization on views, without beinggiven any authorization on the relations used in the viewdefinition

Example: suppose a bank clerk needs to know the namesof the customers of each branch, but is not authorized tosee specific loan information

Approach: deny direct access to the loan relation, butgrant access to the view cust loan, which consists only ofthe names of customers and the branches at which theyhave a loanThe cust-loan view is defined in SQL as follows:

create view cust loan asselect branch name, customer name

from borrower , loan

where borrower .loan number = loan.loan number

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Authorization and Views (cont.)

The clerk is authorized to see the result of the query:

select ∗from cust loan

Creation of view does not require resources authorizationsince no real relation is being created

The creator of a view gets only those privileges thatprovide no additional authorization beyond that he alreadyhad.

I.e. if creator of view cust loan had only read authorizationon borrower and loan, he gets only read authorization oncust loan

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Roles

Roles permit common privileges for a class of users

Privileges can be granted to or revoked from roles, justlike users

Roles can be assigned to users, and even to other roles

create role teller

create role manager

grant select on branch to teller

grant update(balance) on account to teller

grant all privileges on account to manager

grant teller to manager

grant teller to alice, bob

grant manager to john

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Limitations of SQL Authorization

SQL does not support authorization at a tuple level

E.g. we cannot restrict students to see only (the tuplesstoring) their own grades

All end-users of an application (such as a web application)may be mapped to a single database user

The task of authorization in above cases falls on theapplication program, with no support from SQL

Benefit: fine grained authorizations, such as to individualtuples, can be implemented by the application.Drawback: Authorization must be done in applicationcode, and may be dispersed all over an applicationChecking for absence of authorization loopholes becomesvery difficult since it requires reading large amounts ofapplication code

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Outline


2 Constraints

3 Authorization


5 Triggers

6 Examples

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Functions and Procedures

SQL supports functions and procedures

Functions/procedures can be written in SQL itself, or in anexternal programming languageFunctions are particularly useful with specialized data typessuch as images and geometric objectsExample: functions to check if polygons overlap, or tocompare images for similaritySome database systems support table-valued functions,which can return a relation as a result

SQL also supports a rich set of imperative constructs,including

Loops, if-then-else, assignment

Many databases have proprietary procedural extensions toSQL that differ from standard SQL

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

SQL Functions

Define a function that, given the name of a customer, returnsthe count of the number of accounts owned by the customer.

create function account count(customer name varchar(20))returns integer

begindeclare a count integer;select count(∗) into a count

from depositor

where depositor .customer name = account count.customer name;return a count

end

Find each customer that has more than one account.

select customer name, customer street, customer city

from customer

where account count(customer name) > 1

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Table Functions

SQL:2003 added functions that return a relation as aresult

Example: Return all accounts owned by a given customer

create function accounts of (customer name char(20))returns table(account number char(10), branch name char(15),

balance numeric(12,2))return table (

select account number , branch name, balance

from account, depositor

where depositor .customer name = accounts of .customer name

and depositor .account number = account.account number)

Usage:

select ∗from table(accounts of (’Smith’))

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

SQL Procedures

The account count function could instead be written asprocedure:

create procedure account count(in customer name char(20),out a count integer)

beginselect count(*) into a count

from depositor

where depositor .customer name = account count.customer name

end

Procedures can be invoked using the call statement:

declare a count integer;call account count(’Smith’, a count);

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Procedural Constructs

Compound statements:

begin<SQL statements>

end

May contain multiple SQL statementsLocal variables can be declared

Declaring variables

declare <variable> <type> [default <value>]

The scope of a local variable is within the block where it isdeclared

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Procedural Constructs (cont.)

Assigning values

set <variable> = expression

The select into statement

select <columns> into <variable><table expression>

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Conditional statements

The if-then-else statement

if r .balance < 1000 thenset l = l + r .balance

elseif r .balance < 5000 thenset m = m + r .balance

elseset h = h + r .balance

end if

The case statement

casewhen r .balance < 1000 then

set l = l + r .balance

else set h = h + r .balance

end case

There is also a case statement similar to the one in C

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Exceptions

Signaling of exception conditions, and declaring handlersfor exceptions

declare out of stock condition;declare exit handler for out of stock

begin...signal out of stock

end;

The handler here is exit— causes enclosing block to beexited

Other actions possible on exception (continue, undo, ...)

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Loops

While and repeat statements:

declare n integer default 0;while n < 10 do

set n = n + 1end while

declare n integer default 10;repeat

set n = n − 1until n = 0end repeat

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Loops (cont.)

The for loop

Permits iteration over all results of a queryExample: find total of all balances at the Perryridgebranch

declare n integer default 0;for r as

select balancefrom accountwhere branch name = ’Perryridge’

doset n = n + r .balance

end for

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Cursors

declare n integer default 0;declare r integer default 0;declare done integer default 0;declare c cursor for

select balance from accountwhere branch name = ’Perryridge’;

declare continue handler for sqlstate ’02000’ set done = 1;

open c;repeat

fetch c into r ;if not done then

set n = n + r

end ifuntil done

end repeat;close c;

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Outline


2 Constraints

3 Authorization


5 Triggers

6 Examples

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Triggers

A trigger is a statement that is executed automatically bythe system as a side effect of a modification to thedatabase.

To design a trigger mechanism, we must:

Specify the conditions under which the trigger is to beexecuted.Specify the actions to be taken when the trigger executes.

Triggers introduced to SQL standard in SQL:1999, butsupported even earlier using non-standard syntax by mostdatabases.

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Trigger Example

Suppose that instead of allowing negative accountbalances, the bank deals with overdrafts by

creating a loan in the amount of the overdraftgiving this loan a loan number identical to the accountnumber of the overdrawn accountsetting the account balance to zero

The condition for executing the trigger is an update to theaccount relation that results in a negative balance value

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Trigger Example

create trigger overdraft after update on account

for each rowwhen new .balance < 0begin

insert into borrower

(select customer name, account number

from depositor

where new .account number = depositor .account number);insert into loan values (new .account number ,

new .branch name, −new .balance);update account

set balance = 0where account.account number = new .account number

end

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Triggering Events and Actions

Triggering event can be insert, delete or update

Triggers on update can be restricted to specific attributescreate trigger overdraft after update of balance onaccount

Values of attributes before and after an update can bereferenced

referencing new row as <var> : for inserts and updatesreferencing old row as <var> : for deletes and updates

Triggering times can be before or after

Trigger can be at row level or statement leveluse for each row or for each statementAt statement level, old and new refer to temporary tables(called transition tables) containing the affected rowsStatement level can be more efficient when dealing withSQL statements that update a large number of rows

If the trigger fails, the triggering statement fails

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

When Not To Use Triggers

Triggers were used earlier for tasks such as

maintaining summary data (e.g. total salary of eachdepartment)replicating databases by recording changes to specialrelations (called change or delta relations) and having aseparate process that applies the changes over to a replica

There are better ways of doing these now:

Databases today provide built in materialized view facilitiesto maintain summary dataDatabases provide built-in support for replication

Encapsulation facilities can be used instead of triggers inmany cases

Define methods to update fieldsCarry out actions as part of the update methods instead ofthrough a trigger

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Outline


2 Constraints

3 Authorization


5 Triggers

6 Examples

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Example Function

Implement an SQL stored function that, given a branch name,returns the percentage of assets that branch represents to thebank.

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Example Function

create function percent(branch name varchar(50))returns numeric(5,2)

begindeclare total double;declare branch assets double;

select sum(assets) into total

from branch b;

select assets into branch assets

from branch b

where b.branch name = branch name;

return branch assets/total ∗ 100;end

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Example Procedure

Implement an SQL stored procedure that takes as input thename of a city and returns the names of the two customerswith accounts in the given city with the highest amount ofmoney deposited.

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Example Procedure

create procedure top customer(in city varchar(50), out top1 varchar(50),out top2 varchar(50))

begindeclare done int default 0;declare c cursor for

select d .customer name from depositor d , account a, branch b

where d .account number = a.account number anda.branch name = b.branch name and b.branch city = city

group by d .customer name

order by sum(a.balance) desc;declare continue handler for sqlstate ’02000’ set done = 1;

open c;if not done then

fetch c into top1;end if;if not done then

fetch c into top2;end if;close c;

end

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Example Trigger

Implement an SQL trigger to assure that every client with aloan also has a bank account at the same branch. If the bankaccount does not exist, it should be created with balance zeroand account number equal to the loan number.

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

Example Trigger

create trigger loan account trigger

before insert on borrower

for each rowbegin

declare b name varchar(50);declare accounts int;

select branch name into b name

from loan where loan number = new .loan number ;

select count(∗) into accounts

from depositor d , account a

where d .account number = a.account number andd .customer name = new .customer name anda.branch name = b name;

if accounts = 0 theninsert into account values(new .loan number , b name, 0);insert into depositor values(new .customer name, new .loan number);

end if;end

DatabaseSystem

Concepts


Constraints

Authorization


Triggers

Examples

End of Chapter 4(+8)

database system concepts - ulisboa · “database system concepts” ... date: dates, containing a...

Documents