PART 2
RELATIONAL DATABASES
Chapter 4
Advanced SQL
April 2008 Database System Concepts - Chapter4 Advanced SQL - 3
Integrity constraints in SQL §4.1-4.2
Security in SQL (§4.3) authorization, grant/revoke
Embedded and dynamic SQL §4.4-4.5
Three Parts in Chapter 4
April 2008 Database System Concepts - Chapter4 Advanced SQL - 4
Case study used in this chapter
return
Three Parts in Chapter 4 (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 5
4.1.1 Built-in Data Types in SQL In addition to the basic data types, e.g. char(n), varchar(n), int,
smallint, numeric(p, d), real, double precision, float(n), described in §3.2, the other built-in data types include
date: Dates, containing a (4 digit) year, month and date e.g. date ‘2005-7-27
time: Time of day, in hours, minutes and seconds e.g. time ‘09:00:30.75
timestamp: date plus time of day e.g. timestamp ‘2005-7-27 09:00:30.75’
interval: period of time e.g. interval ‘1’ day
§4.1 SQL Data Types and Schemas
April 2008 Database System Concepts - Chapter4 Advanced SQL - 6
Subtracting a date/time/timestamp value from another gives an interval value
interval values can be added to date/time/timestamp values
Values of individual fields can be extracted from date/time/timestamp
e.g. extract (year from r.starttime) We can cast /convert string types to date/time/timestamp
e.g. cast <string-valued-expression> as date e.g. cast <string-valued-expression> as time
4.1.1 Built-in Data Types in SQL (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 7
The create type clause can be used to define new user-defined types
e.g. create type Dollars as numeric (12,2) final The create domain clause can be used to define new user-
defined domains e.g. create domain person_name char(20) not null
4.1.2 User-defined Types
April 2008 Database System Concepts - Chapter4 Advanced SQL - 8
4.1.3 Large-Object Types Large objects (photos, videos, CAD files, etc.) are stored as a
large object blob: binary large object
the 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 the 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.
April 2008 Database System Concepts - Chapter4 Advanced SQL - 9
E.g.
book_view clob(10KB)
image blob(10MB)
movie blob(2GB)
4.1.3 Large-Object Types (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 10
How to name the relations in DB, or how to specify the names of the relations in DB
The three-level hierarchy for naming relations catalogs, each of which can contain schemas;
The catalog is also called database schemas, in which the relations and views are stored relations and views e.g. a three-part name for the relation
catalog5.bank_schema.account SQL environments, including
connection, catalog, schema, user identifier
4.1.4 Schemas, Catalogs, and Environments
April 2008 Database System Concepts - Chapter4 Advanced SQL - 11
§4.2 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
Classification of integrity constraints and some examples refer to Fig.4.0.1
attribute-level tuple-level relation-level
static data type data format,
e.g. YY.MM.DD domain constraints,
e.g.1 null value
constraints among attributes
values e.g.2 mapping
cardinality constraints
entity integrity e.g.3 referential
integrity (§4.2.5) functional
dependency (§7) statistical constraints e.g.6
dynamic
constraints on
updating of
attribute values or attribute definition e.g.4
constraints among attributes values e.g.5
transaction constraint: atomy, consistency, isolation, durability
(§ 15, 16, 17)
Fig.4.0.1
April 2008 Database System Concepts - Chapter4 Advanced SQL - 13
E.g.1 “ the salary of manager should not be lower than $1000” in Employee
E.g.2 table T (x, y, z ), z =x+y, z is a derived attributes from x and y.
E.g.3 “the student# for table student should not be null” E.g.4 ”the age of students should only be added” E.g.5 when employee tuples is modified, new.sal > old.sal +
0.5*age E.g.6 statistical constraints: in table employee , “the salary
of manager should be four times more than that of workers”
Some Examples of Integrity Constraints
April 2008 Database System Concepts - Chapter4 Advanced SQL - 14
An SQL relation is defined using the create table command
create table r (A1 D1, A2 D2, ..., An Dn,(integrity-constraint1), ...,(integrity-constraintk))
r is the name of the relation each Ai is an attribute name in the schema of relation r
Di is the data type of values in the domain of attribute Ai
4.2.1-4 Constraints on a Single Relation
April 2008 Database System Concepts - Chapter4 Advanced SQL - 15
The allowed integrity constraints include primary key not null Unique foreign keys check (P ), where P is a predicate
Not Null Constraints e.g.1 Declare branch_name for branch is not null
branch_name char(15) not null e.g.2 Declare the domain Dollars to be not null
create domain Dollars numeric(12,2) not null
4.2.1-4 Constraints on a Single Relation (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 16
The Unique Constraint is as follows
unique ( A1, A2, …, Am) it states that the attributes
A1, A2, … Am form a candidate key
candidate keys are permitted to be null (in contrast to primary keys)
e.g. create table customer(customer-id char(15) customer-name char(15)
customer-city char(30), primary key (customer-id)
unique (customer-name)
4.2.1-4 Constraints on a Single Relation (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 17
The check clause is applied to relation declaration as well as to domain declaration
check (P ), where P is a predicate E.g. Declare branch_name as the primary key for branch and
ensure that the values of assets are non-negative create table branch
(branch_name char(15), branch_city char(30), assets integer, primary key (branch_name), check (assets >= 0))
4.2.1-4 Constraints on a Single Relation (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 18
E.g. if “Perryridge” is a branch name appearing in one of the tuples in the account relation, then there exists a tuple in the branch relation for branch “Perryridge”
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
4.2.5 Referential Integrity
April 2008 Database System Concepts - Chapter4 Advanced SQL - 19
The primary key, candidate keys and foreign keys can be specified as parts of the SQL create table statement:
the primary key clause lists attributes that comprise the primary key.
the unique key clause lists attributes that comprise a candidate key.
the foreign key clause lists the attributes that comprise the foreign key and the name of the relation referenced by the foreign key. By default, a foreign key references the primary key attributes of the referenced table.
4.2.5 Referential Integrity (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 20
Definition of referential integrity Let r1(R1) and r2(R2) be relations with primary keys K1 and K2
respectively, refer to Fig. 4.0.2 e.g. branch(branch-name, branch-city, assets),
account(account-number, branch-name, balance)
the subset of R2 (e.g. branch-name) is a foreign key (from
r2, e.g. account) referencing K1 in relation r1 (e.g. branch) , if for every t2 in r2 there must be a tuple t1 in r1 such that t1[K1] = t2[]
note: = K1 R2 ⊆
4.2.5 Referential Integrity (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 21
referential integrity constraint also called subset dependency since its can be written as (r2) K1 (r1)
e.g. branch-name (account) branch-name (branch)
a foreign key of table r2 references the primary key attributes K1 of the referenced table r1
A good DB design should ensures that any relation schema R2 (and its any tuples) can only reference other relation schema R1 through its foreign key
4.2.5 Referential Integrity (cont.)
K1 A2 A3
Perryridge Perryridge 1200
New-York New-York 5000
Brooklyn Brooklyn 3000
Los-Angles Los-Angles
2500
Austin Austin 1800
K2 B3
101 Perryridge 200
102 Perryridge 400
201 New-York 500
203 New-York 1000
303 Brooklyn 400
401 Los-Angles 600
Fig. 4.0.2
r1(R1), e.g. branch r2(R2), e.g. account
branch (branch-name, branch-city, assets) Account
(account-number, branch-name, balance)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 23
create table branch (branch_name char(15), branch_city char(30), assets numeric(12,2), primary key (branch_name ))
create table account(account_number char(10),branch_name char(15),balance integer,primary key (account_number), foreign key (branch_name) references branch )
Referential Integrity in SQL – Example
April 2008 Database System Concepts - Chapter4 Advanced SQL - 24
For a many to many relationship set R between entity sets E1 and E2 in Fig. 4.0.3, the reduced relational schema R for Re includes the primary keys K1 of E1 and K2 of E2, then K1 and K2 form foreign keys of R referencing R1 and R2 respectively
ReE1 E2
R1(K1 ,…, ) R2(K2 , …, )
R(K1, K2 , …, )
Fig. 4.0.3
4.2.5 Referential Integrity (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 25
Weak entity sets are also a source of referential integrity constraints
e.g. payment (loan-number, payment-number, date, amount)
Fig.4.0.4 E-R diagram with a weak entity set
manyone
total E1E2
4.2.5 Referential Integrity (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 26
Assuming a foreign key of table r2 references the primary key attributes K of the referenced table r1
An example, as shown in Fig.4.0.2 r1 (R1) : branch (branch-name, branch-city, assets )
r2 (R2) : account (account-number, branch-name, balance) : branch-name t1 = (New-York-branch , New York , $2000) in r1
t2 = (201#, New-York-branch, $500) in r2
Database Modification
April 2008 Database System Concepts - Chapter4 Advanced SQL - 27
When the DB is modified by Insert, Delete, and Update, the tests must be made in order to preserve the following referential integrity constraint:
(r2) K (r1)
e.g. branch-name (account) branch-name (branch)
E.g. insert t2 = (303#, Brooklyn-branch, $400) into account, t1= (Brooklyn-branch , * , *) exists in branch ?
e.g. t2 []=“Brooklyn-branch” branch-name (branch)
/*新插入的 account的开户行是否已在 branch中存在?
For more details, refer to Appendix A
Database Modification (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 28
An assertion is a predicate expressing a condition that we wish the database always to satisfy e.g. domain constraints, referential-integrity constraint
An assertion in SQL takes the form
create assertion <assertion-name> check <predicate>
When an assertion is made, the DBMS tests it for validity. Any modification to DB is allowed only if it does not cause that assertion to be violated this testing may introduce a significant amount of overhead,
hence assertions should be used with great care
§4.2.6 Assertions ( 断言 )
April 2008 Database System Concepts - Chapter4 Advanced SQL - 29
E.g. The sum of all loan amounts for each branch must be less than the 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(amount) from account
where loan.branch-name = branch.branch-name)))
分行存款总额
分行借款总额
§4.2.6 Assertions (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 30
§4.3 Authorization
Forms of authorization on parts of the database include read, insert, update, delete
Forms of authorization to modify the database schema (covered
in Chapter 8) index resources - allows creation of new relations alteration - allows addition or deletion of attributes in a
relation drop - allows deletion of relations.
April 2008 Database System Concepts - Chapter4 Advanced SQL - 31
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 in Chapter 8)
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).
April 2008 Database System Concepts - Chapter4 Advanced SQL - 32
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 branch 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 more in Chapter 8
April 2008 Database System Concepts - Chapter4 Advanced SQL - 33
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.
April 2008 Database System Concepts - Chapter4 Advanced SQL - 34
Revoking Authorization in SQL (cont.)
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.
April 2008 Database System Concepts - Chapter4 Advanced SQL - 35
§4.4 Embedded SQL
Four approaches to take SQL as DB query tools interactive SQL
SQL is used directly as DML and DDL through DBS human-machine interfaces
embedded SQL SQL is embedded in general-purpose programming
languages C language executing of general-purpose programming language
programs with SQL statement embedded results in DB access
April 2008 Database System Concepts - Chapter4 Advanced SQL - 36
§4.4 Embedded SQL (cont.)
dynamic SQL: ODBC/JDBC as general-purpose API in client-server DBS environments, the client
application program, which is written in C or Java language and located at client-sites, calls ODBC or JDBC API in which SQL query statements are included, to access the DB locating at server-site
as the general-purpose API, independent of DBMS e.g. SQLExecDirect(hstmt, "SELECT * FROM
authors", Len(SQLstmt))
April 2008 Database System Concepts - Chapter4 Advanced SQL - 37
the SQL query statements are included in the API e.g. MySQL API, refer to Appendix K for more details
§4.4 Embedded SQL (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 38
4.4.1 Embedding SQL in Host Language Programs
The SQL standard defines embeddings of SQL in a variety of programming languages such as C, Pascal, Fortran, and Cobol
A language to which SQL queries are embedded is referred to as a host language( 宿主语言 ), and the SQL structures permitted in the host language comprise embedded SQL
Merits of embedded SQL 交互式 SQL 只能进行 DB 的访问操作,不能对 DB 访问
结果进行进一步的数据处理 Embedded SQL 将 SQL 的数据库访问功能与 C 语言等宿
主语言的数据处理能力相结合,提高了数据应用系统的能力
April 2008 Database System Concepts - Chapter4 Advanced SQL - 39
A program with embedded SQL consists of two parts, the program declaration part and the program execution part.
In program declaration part, shared variables are defined, the host language statements SQL statements in the program exchange data through the shared variables
In host programs, shared variables are declared as, e.g. EXEC SQL BEGIN DECLARE SECTION
int amount;
EXEC SQL END DECLARE SECTION
4.4.1 Embedding SQL in Host Language Programs (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 40
In program execution part, SQL statements are embedded in host language programs as
EXEC SQL <embedded SQL statement > END-EXEC note: this varies by language. e.g. the Java embedding uses
# SQL { …. } E.g.1 From within C language, find the names and cities of
customers with more than the variable amount ( input from screen by users) dollars in some account
/* 找出所有在银行有“存款额大于 amount” 的帐户的客户的姓名和所在城市
amount is the shared variable defined in the declaration part
4.4.1 Embedding SQL in Host Language Programs (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 41
the C program is as follows EXEC SQL BEGIN DECLARE SECTION
int amount; EXEC SQL END DECLARE SECTION amount := input-from-screen-by-users EXEC SQL select customer-name, customer-city
from depositor, customer, accountwhere depositor.customer-name = customer.customer-name and depositor account-number = account.account- number and account.balance > :amount
END-EXEC
shared variable defined in host language
4.4.1 Embedding SQL in Host Language Programs (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 42
/* 利用 Embedded SQL 进行查询时,查询结果有可能包括多个元组,此时无法直接将多个元组通过共享变量赋值传递给宿主程序
/* 系统开辟专门 working 区域存放 SQL 查询的结果关系,并利用查询游标 c 指向此区域。宿主程序根据 c 指向的查询结果关系集合,使用 open, fetch, close 依次获取结果关系中的各元组
open c: the SQL query is evaluated, and the cursor is set to point to the first tuple of the result relation
fetch c: the tuple pointed by the cursor is retrieved and fetched into some shared variables, and then the cursor is updated to point to the next tuple of the result
close c: the result relation is deleted by DBMS
4.4.2 Cursor in Embedded SQL
April 2008 Database System Concepts - Chapter4 Advanced SQL - 43
Usage of cursor in embedded SQL
declare cursor – open – fetch - close
DBS
…results
SQL query
application program:
open fetch close
cursorworking area
4.4.2 Cursor in Embedded SQL (cont.)
Fig. 4.0.5 Cursor in Embedded SQL
April 2008 Database System Concepts - Chapter4 Advanced SQL - 44
E.g.2 From within C language, find the names and cities of customers with more than the variable amount ( input from screen by users) dollars in some account
4.4.2 Cursor in Embedded SQL (cont.)
the C program is as follows
EXEC SQL BEGIN DECLARE SECTION int amount; char cn; char cc;
EXEC SQL END DECLARE SECTION amount := input-from-screen-by-users EXEC SQL declare c cursor for select customer-name, customer-city
from depositor, customer, accountwhere depositor.customer-name = customer.customer-name and depositor account-number = account.account- number and account.balance> :amount
END-EXEC EXEC SQL open c END-EXEC EXEC SQL fetch c into :cn, :cc END-EXEC EXEC SQL close c END-EXEC
shared variable
April 2008 Database System Concepts - Chapter4 Advanced SQL - 46
Notes the open statement causes the query to be evaluated
evaluating query specified by c, applying :amount to the query
the query result is stored in a temporal relation the fetch statement causes the values of one tuple in the
query result to be placed on host language variables :cn and :cc
:cn corresponds to customer-name, :cc corresponds to customer-city
the close statement causes the DBS to delete the temporary relation that holds the result of the query
4.4.2 Cursor in Embedded SQL (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 47
A variable called SQLSTATE in the SQL communication area (SQLCA) gets set to ‘02000’ to indicate no more data is available
Tuples fetched can be updated by cursor by declaring that the cursor is for update E.g.
declare c cursor for select * from account where branch-name = ‘Perryridge’ for update
4.4.2 Cursor in Embedded SQL (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 48
To update tuple at the current location of cursor
update account set balance = balance + 100 where current of c
4.4.2 Cursor in Embedded SQL (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 49
§4.5 Dynamic SQL&ODBC/JDBC
Dynamic SQL allows programs to construct and submit SQL queries at run time.
Example of the use of dynamic SQL from within a C program.
char * sqlprog = “update account set balance = balance * 1.05
where account-number = ?”EXEC SQL prepare dynprog from :sqlprog;char account [10] = “A-101”;EXEC SQL execute dynprog using :account;
The dynamic SQL program contains a ?, which is a place holder for a value that is provided when the SQL program is executed.
April 2008 Database System Concepts - Chapter4 Advanced SQL - 50
ODBC acronym for Open DataBase Connectivity in the Microsoft WOSA structure, an interface providing a
common language for windows applications to gain access a database on networks
WOSA acronym for Windows Open System Architecture a set of application programming interfaces from Microsoft
that is intended to enable Windows applications form different vendors to communicate with each other, such as over a network
the interfaces within the WOSA standard include
4.5.1 ODBC (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 51
Open DataBase Connectivity, ODBC the Message Application Programming Interface, MAPI the Telephony Application Programming Interface,
TAPI Windows Socket, Winsock Micro Remote Procedure Calls, RPC
The functions of ODBC standard for application programs to communicate with a
database server. application program interface (API) to
open a connection with a database send queries and updates get back results
4.5.1 ODBC (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 52
Architecture of ODBC refer to Fig. 4.0.6
For more details about ODBC, refer to SQL Server —4—ODBC
4.5.1 ODBC (cont.)
Database application programs
ODBC API
ODBC manager
ODBC driver manager
SQL Serverdriver
Oracledriver
Sybasedriver
other DBdriver
SQLServer
Oracle Sybase other
DBMS
Data SourceName
ApplicationLevel
ODBCLevel
Data SourceLevel
Fig.4.0.6 ODBC Architecture
April 2008 Database System Concepts - Chapter4 Advanced SQL - 54
JDBC acronym for Java DataBase Connectivity a Java API for communicating with database systems
supporting SQL JDBC supports a variety of features for querying and updating
data, and for retrieving query results JDBC also supports metadata retrieval, such as querying about
relations present in the database and the names and types of relation attributes
Model for communicating with the database open a connection
4.5.2 JDBC
April 2008 Database System Concepts - Chapter4 Advanced SQL - 55
create a “statement” object execute queries using the Statement object to send queries and
fetch results Exception mechanism to handle errors
Transactions in JDBC as with ODBC, each statement gets committed automatically
in JDBC to turn off auto commit use
conn.setAutoCommit(false); to commit or abort transactions use
conn.commit() or conn.rollback() to turn auto commit on again, use
conn.setAutoCommit(true);
4.5.2 JDBC (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 56
SQL provides a module language permits definition of procedures in SQL, with if-then-else
statements, for and while loops, etc. more in Chapter 9
Stored Procedures ( 存储过程 ) can store procedures in the database then execute them using the call statement permit external applications to operate on the database
without knowing about internal details These features are covered in Chapter 9 (Object Relational
Databases)
§4.6 Functions and Procedures
April 2008 Database System Concepts - Chapter4 Advanced SQL - 57
Functions and Procedures (cont.)
SQL:1999 supports functions and procedures functions/procedures can be written in SQL itself, or in an
external programming language functions are particularly useful with specialized data types such
as images and geometric objects e.g.: functions to check if polygons overlap, or to compare
images for similarity some database systems support table-valued functions, which
can return a relation as a result SQL:1999 also supports a rich set of imperative constructs,
including loops, if-then-else, assignment
Many databases have proprietary procedural extensions to SQL that differ from SQL:1999
April 2008 Database System Concepts - Chapter4 Advanced SQL - 58
Define a function that, given the name of a customer, returns the count of the number of accounts owned by the customer.
create function account_count (customer_name varchar(20)) returns integer begin declare a_count integer; select count (* ) into a_count from depositor where depositor.customer_name = customer_name return a_count; end
Functions and Procedures (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 59
Find the name and address of each customer that has more than one account.
select customer_name, customer_street, customer_cityfrom customerwhere account_count (customer_name ) > 1
Functions and Procedures (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 60
Assuming a foreign key of table r2 references the primary key attributes K of the referenced table r1
An example, as shown in Fig.6.0.2 r1 (R1) : branch (branch-name, branch-city, assets )
r2 (R2) : account (account-number, branch-name, balance) : branch-name t1 = (New-York-branch , New York , $2000) in r1
t2 = (201#, New-York-branch, $500) in r2
Appendix A Database Modification
April 2008 Database System Concepts - Chapter4 Advanced SQL - 61
When the DB is modified by Insert, Delete, and Update, the tests for must be made in order to preserve the following referential integrity constraint:
(r2) K (r1)
e.g. branch-name (account) branch-name (branch)
Appendix A Database Modification (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 62
Insert vs. referential integrity if a tuple t2 is inserted into r2 (e.g. account), the system must
ensure that there is a tuple t1 in r1 (e.g. branch) such that
t1[K1] = t2[], i.e. t2 [] K1 (r1)
e.g. insert t2 = (303#, Brooklyn-branch, $400) into account, t1= (Brooklyn-branch , * , *) exists in branch ?
e.g. t2 []=“Brooklyn-branch” branch-name (branch)
/*新插入的 account的开户行是否已在 branch中存在?
Appendix A Database Modification (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 63
Delete vs. referential integrity if a tuple t1 is deleted from r1 (e.g. branch), the system must
compute the set of tuples in r2 (e.g. account) that reference t1:
= t1[K1] (r2) e.g. delete t1=(New-York , New York, $5000) from
branch,
= t1[K1] (r2)
= branch-name = New-York (account)
= {t | t = ( *, New-York, * ) }
/* 所有在被删除的 New-York 支行中的 account 元组
Appendix A Database Modification (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 64
if this set is not empty either the delete command is rejected as an error, or
/* 不允许 delete t1=(New-York, New York, $5000) from branch
the tuples in r2 that reference t1 must themselves be deleted (cascading deletions are possible)
/* 所有在被删除的 New-York 支行中的 account 元组需要同时被删除(级联删除)
Appendix A Database Modification (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 65
Update vs. referential integrity. Two cases as following Case1. If a tuple t2 is updated in relation r2 (e.g. account), and
the update modifies values for foreign key (e.g. branch-name), then a test similar to the insert case is made let t2’ denote the new value of tuple t2, the system must
ensure that
t2’[] K1(r1) e.g. if update t2= (201#, New-York-branch, $500) into
t2’ = (80#, Austin-branch, $500) in account, is
t2’[] = Austin-branch branch-name(branch) ?
/* 判断新改成的支行 Los-Angles-branch 是否存在?
Appendix A Database Modification (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 66
Case2. if a tuple t1 in r1 (e.g. branch) is updated, and the update modifies values for the primary key K1 of r1 (e.g. branch-name), then a test similar to the delete case is made e.g. t1=(New-York , New York, $5000) is updated into
t1=(N-YY, New York, $5000)
the system must compute = t1[K1] (r2) using the old value of t1 (the value before the update is applied)
branch-name = New-York(account) if this set is not empty
/* 在原来的 New-York 支行中有 account 元组 , e.g. (201#, New-York, $500), (203#, New-York, $1000)
Appendix A Database Modification (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 67
the update may be rejected as an error, or
/* 不允许对 branch 进行修改,即不允许将 t1=(New-York , New York, $5000) 修改为 t1=(N-YY , New York, $5000)
the update may be cascaded in a manner similar to delete
/* 将所有 account 元组, e.g. (201#, New-York, $500) 修改为 (201#, N-YY, $500)
Appendix A Database Modification (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 68
In SQL, primary and foreign keys can be specified as part of the create table statement the primary key clause lists attributes that comprise the
primary key the foreign key-reference clause lists the attributes that
comprise the foreign key and the name of the relation referenced by the foreign key
Appendix A Database Modification (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 69
E.g.1 Fig.6.2
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)
When a referential integrity is violated due to a delete or update action on the referenced relation, in stead of rejecting the action(e.g. delete, update) causing the violation, the foreign key-reference clause can specify the remedying steps to change the tuples in referencing relation to restore the constraints
Appendix A Database Modification (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 70
E.g. 2 create table account
( . . .foreign key(branch-name) references branch
on delete cascadeon update cascade
. . . ) account is the the referencing relation, branch is the
referenced relation the on delete cascade clause defines if a delete of a tuple in
the referenced branch results in referential-integrity constraint violation, the delete cascade to the referencing account should delete all the tuple that refers to the branch that was deleted
Appendix A Database Modification (cont.)
April 2008 Database System Concepts - Chapter4 Advanced SQL - 71
the on update cascade clause specifies that if an update to branch-name in the referenced tuple in branch violates the referential-integrity constraint, the system should also update the field branch-name in the referencing tuples in account to the new values
Besides cascading, if the constraints is violated, the referencing field can be set to null or predefined default
values by clauses on delete set null , or on delete set default
Appendix A Database Modification (cont.)