Lecture 4 – Constraints, Triggers, Views

musicincurableΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 9 μήνες)

123 εμφανίσεις

Slide
1

Lecture 4


Constraints, Triggers, Views,
Application Development

Slide
2

Lecture 4


ALTER TABLE


Integrity Constraints


Primary Key, Foreign Key


Check


Domain


Assertion


Triggers


Views


Levels of Abstraction


Data Independence


Embedded SQL


Cursors


Slide
3

Learning Objectives


Be able to:


LO4.1: Write ALTER TABLE statements


LO4.2: Describe what an integrity constraint or a
trigger accomplishes.


LO4.3: Construct a view to support a new
environment.

Slide
4

Fixing COBOL Format*


Look at
http://www.fec.gov/finance/disclosure/ftpdet.shtml

:


"
The amount field is in COBOL format…if the amount is 20] …The
correct amount is

200

"


Look at the table
iclcobol

to see what the FEC data looks like
before it is corrected.


How would you correct it?









Look very briefly at the code in
DDL.sql


we will go over it in
detail later.


Slide
5

Stored Procedures (SPs)


Motivation*


If we were using a programming language instead of
SQL, how would we implement this logic


subroutine
or inline? Why?




In SQL, the analog of a subroutine is called a
stored
procedure (SP)
.

Slide
6

SPs vs. Subroutines*


Similarities between SPs and subroutines


SPs can be written in many languages


Most common: dialects of SQL


In Postgresql this is SQL or PL/pgSQL (nonstandard)


In Postgres, you can register any language for writing SPs
(nonstandard)


http://www.postgresql.org/docs/8.3/interactive/plhandler.html


An SP returns a structure


In SQL
-
land this means a table


A call to an SP can appear in the FROM clause of a query


Difference between SPs and subroutines


An SP executes
in the DBMS's address space
, not the
clients'


This has
huge

performance implications.


Slide
7

Bad news, Good news


Stored Procedures are not in any SQL standard


Each vendors' implementation is different


But each vendor gives porting advice, e.g.,
http://www.postgresql.org/docs/8.3/interactive/plpgsql
-
porting.html


Some shops don't allow stored procedures for this reason


Stored Procedures can be
very

efficient compared to SQL


Suppose a program's logic is
S1;S2;…;Sn
, each Si is a SQL statement

Server

Client

S1

S2



Sn

:query

:answer

Implemented as SQL

Server

Client

S1

S2



Sn

Implemented as SP

Slide
8

Efficiency of SPs


If you write the program in
SQL
, the cost is


2n
context switches +


n
network traversals to send queries to the server +


n

network traversals to send answers to the client


If you write the program as a
Stored Procedure
, the cost is


2

context switches


1

network traversals to send queries to the server


1

network traversals to send answers to the client


Depending on the size of n, the size of each query and the size
of the intermediate and final answers, this can be a
huge
difference
.

"Sybase’s introduction of stored procedures gave a big boost in performance as the
entire debit/credit application could be implemented inside the database system,
replacing four roundtrip messages between the application and the DBMS with a
single round trip. As a consequence, Sybase had a significant performance
advantage until their competitors added stored procedures to their system"



http://www.sigmod.org/sigmod/record/issues/0806/p45.dewitt.pdf


"Not just correct, but correct and fast", A look at one of Jim Gray's contributions to


database system performance, David J. DeWitt and Charles Levine, SIGMOD Record,
June, 2008.

Slide
9

A Real Stored Procedure*


Look at
COBOLint(file text)

in
DDL.sql


Notice The SP is written in
PL/pgSQL
, not in
SQL
.


You can see this from the last few lines or because of the
EXECUTE statements. (nonstandard)


The reason is that PostgreSQL
precompiles

SPs (for efficiency)
written in SQL, and, as we shall see, this wreaks havoc with
COBOLint. (nonstandard)


Now look at how the SP parallels your algorithm for changing
COBOL to integer format.


Don't worry about the details of syntax, like the ||'s used to
escape names. (nonstandard)


What's important about SP's is on the 3 preceeding slides.

Slide
10

ALTER TABLE


ALTER TABLE… is a standard DML statement used
to change the schema of a table


You've see how it can be used to add and drop
columns and add foreign key declarations.


Here are some other uses.


--

If you INSERT a cand record and do not give a value

--

for princomm, the DBMS inserts the value ‘P00000000’

ALTER TABLE cand ALTER COLUMN princomm DEFAULT
‘P00000000’;


--

This drops default values from a column

ALTER TABLE cand ALTER COLUMN princomm DROP DEFAULT;

Slide
11

Integrity Constraints (ICs)


An IC describes conditions that every
legal instance
of a table must satisfy.


Inserts/deletes/updates that violate IC’s are typically
disallowed.


ICs can be used to ensure application semantics (e.g.,
candid

is a key), or prevent inconsistencies (e.g.,
candid

has to be an integer,
amount

must be >= 200)


Types of IC’s
: Primary key constraints, foreign key
constraints, general (check) constraints, domain
constraints.

Slide
12

Key Constraints


with Names


For efficiency one usually creates ICs seperately from
CREATE TABLE

statements, using
ALTER TABLE
.
But it can be done in one statement, e.g:


CREATE TABLE cand (… princomm char(9)…

CONSTRAINT cand_fkey FOREIGN KEY
(princomm)REFERENCES comm(commid) ON
UPDATE…

);


The advantages of using named constraints (like
cand_fkey
) are


Error messages are more meaningful


missing value for constraint “cand_fkey”


You can drop or defer the constraint


Slide
13

General Constraints*


CHECK constraints can cover extremely general
situations. They can use arbitrary queries, for
example:

CONSTRAINT limit_check CHECK(2300 >=


SELECT MAX(totamt) FROM
(



SELECT donorname, occup, zip, commid, SUM(amount) AS
totamt



FROM indiv



GROUP BY donorname,occup,zip, commid)
AS foo;


);



What does this accomplish?


Slide
14

Domain, Type Constraints


Here’s a typical mistake
:

--

This is a legal SQL query!

SELECT candname

FROM cand JOIN comm ON candid = commid;


SQL ’92 allowed the creation of domains

CREATE DOMAIN canddom Char(9) ;


But this won’t help


the above query is still legal
using
canddom

for the domain of
candid
.


SQL ’99 introduced Types.

CREATE TYPE candtype AS Char(9);


Using
candtype

as the domain of
candid
, the above
query will give a parse error, which is what we want.

Slide
15

Assertions: ICs over several tables*


Any logical
expression
involving an SQL
statement can be
used to constrain
tables in the
database.


What does this
accomplish?

CREATE ASSERTION salary_mgr

CHECK (NOT EXISTS (

SELECT *

FROM Employee E, Employee M

WHERE E.salary > M.salary
AND E.mgr = M.id ) )

Employee( id, name, address, mgr, salary )

Slide
16

Triggers (Not a constraint)


Trigger: procedure that executes if specified changes
occur to the DBMS


Three parts:


Event (activates the trigger)


This will be an insert, delete and/or update to a table


Condition (tests whether the triggers should run)


A logical statement or a query


Action (what happens if the trigger runs)


Can execute queries, execute data
-
definition commands,
transaction
-
oriented commands, and host
-
language procedures


When does the Action execute?


Specified with Event (BEFORE, AFTER)


Slide
17

Triggers: Example*


Assume one donation has been inserted to indiv, for simplicity


CREATE TRIGGER overmax ON indiv AFTER INSERT AS



IF 2300 <


SELECT MAX(totamt) FROM


(SELECT i.donorname, i.commid, SUM(i.amount) AS totamt


FROM indiv i


GROUP BY i.donorname, i.commid


WHERE i.donorname = new.donorname)



DELETE comm c


WHERE c.commid = new.commid



What does this accomplish?


Slide
18

Triggers vs Constraints


Triggers are harder to understand


If multiple triggers apply, their order of execution is
unspecified


One trigger can activate another, causing a chain of actions


A trigger can activate itself


Triggers are more powerful than constraints


They can make changes to tables


They can take action before or after a data modification

Slide
19

Views

students(sid, name, address, gpa)

completed( sid, course, grade)


This material is scattered in the first few chapters.


A
view

is a query stored in the database


Think of it as a table definition for future use


Example view definition:

CREATE VIEW
gstudents

AS


SELECT *


FROM students


WHERE gpa >= 2.5

Views can be used like
base tables
, in any query or in
any other view. Like a Macro. Different from INTO.

Slide
20

Example view use: simpler queries


Suppose you want to retrieve good students who have
completed CS386.

SELECT S.name, S.phone

FROM
gstudents

S JOIN completed C USING (sid)

WHERE C.course = ‘CS386’;



It’s easier to write the query using the view.


Slide
21

Views for Security


This is the student table without the gpa field.

CREATE VIEW sstudents AS

SELECT sid, name, address

FROM students


Can you think of some other security examples?

Slide
22

Views for Extensibility (most
impt!
)


An
old

company’s database includes a table:

Part (
PartID
, Name, Weight)


Weight is stored in pounds


The company is purchased by a
new

firm that uses
metric weights


The
two

databases,
old and new
, must be integrated
and use Kg.


But there’s
lots

of old software using pounds.


Solution: views!

Slide
23

Views for extensibility (ctd)


Solution:

1.
Base table with kilograms becomes NewPart, for
new

integrated company.

2.
CREATE VIEW Part AS
SELECT PartID, Name, 2.2046*Weight
FROM NewPart;

3.
Old

programs still call the table “Part”

Slide
24

Practice*


Your company uses a database including

emp( empid, name, address)


It is bought by a company with an Employee table

employee( empid, fname, lname, address)


Write a view so that SQL queries from your
company’s software repository will work.

Slide
25

But there’s one problem with views: update


Views cannot always be updated unambiguously. Consider


emp(empid, ename, address, deptid)

dept(deptid, dname)

CREATE VIEW empdept AS

SELECT ename, dname

FROM emp JOIN dept USING (deptid)

EMPDEPT


ename dname





jim


shoe





joe


suit



I want to delete (jim, shoe) from EMPDEPT.


How can I do that?




Slide
26

The good news


A view can be updated if


It is defined on a single base table


Using only selection and projection


No aggregates


No DISTINCT


Slide
27

Levels of Abstraction

Physical Schema

Conceptual Schema

ES 1

ES 2

ES 3

Physical storage; DBA

Logical storage; data
designer

External view; user
and data designer

Slide
28

Physical Schema

The
physical schema

is a description of how the data is
physically stored in the database. It includes


Where the data is located


File structures


Access methods


Indexes

The physical schema is managed by the DBA.

Slide
29

Conceptual Schema

The conceptual schema is a logical description of how
the data is stored. It consists of the schemas we
have described with CREATE TABLE statements. It
is managed by the data designer.

Slide
30

External Schemas

Each external schema is a combinaton of base tables
and views, tailored to the needs of a single user. It is
managed by the data designer and the user.

Slide
31

Data Independence


A database model possesses
data independence

if
application programs are immune to changes in the
conceptual and physical schemas.


Why is this important? Everything changes.


How does the relational model achieve logical
(conceptual) data independence?


Through views


If the conceptual schema changes, a view can be defined to
preserve existing applications

Slide
32

Data Independence (ctd.)


How does the relational model achieve physical
data independence?

1.
Conceptual level contains no physical info

2.
SQL can program against the conceptual level


Earlier DBMSs (network, hierarchical) did not have
these properties.


Their languages had physical properties embedded in
them.


That is the primary reason for the success of the
relational model

Slide
33

Views: Summary


A view is a stored query definition


Views can be very useful


Easier query writing, security, extensibility


But views cannot be unambiguously updated


Three levels of abstraction in a relational DBMS


Yields data independence: logical and physical

Slide
34

6. Embedded SQL


SQL is not enough! Needs to be embedded in a general
purpose language to get


GUI


Flow of control


Generate SQL dynamically based on user input



SQL
commands can be called from within a host language (e.g.,
C/C++, Basic, .NET

or
Java
) program or scripting language (e.g.,
PHP, Ruby).



A query answer is a bag of records
-

with arbitrarily many rows!
No such data structure in most languages. Called
impedance
mismatch
.



The SQL standard supports a
cursor

to handle this

Slide
35

Cursors


You can declare a cursor on a table or query statement (which
generates a table).



You can
open

a cursor, and repeatedly
fetch

a tuple then
move

the
cursor, until all tuples have been retrieved.



You can modify/delete a tuple pointed to by a cursor.



SQL must be able to report data
-
generated errors.

Slide
36

Cursor

that gets names of candidates who have
a principal committee, in alphabetical order

EXEC SQL
DECLARE cinfo CURSOR

FOR


SELECT

N.candname


FROM

cand N JOIN commM ON (N.princomm = M.commid)


ORDER BY
N.candname;


OPEN
cinfo
;


FETCH
cinfo

INTO
:c
-
name
; (probably in a loop in your program)


CLOSE
cinfo
;

Notice the colon in :cname


it refers to a variable that has
been declared in the surrounding program

Slide
37

Embedding SQL in C: An Example

Void ListAges( int minzip)

{


char SQLSTATE[6];



EXEC SQL BEGIN DECLARE SECTION


char c_name[20]; char c_party[3]; integer minzip;


EXEC SQL END DECLARE SECTION






SQLSTATE holds SQL error codes



EXEC SQL denotes embedded SQL section



DECLARE SECTION binds variables into SQL

Slide
38

Embedding in C: An Example


DECLARE cinfo CURSOR defines a name for the
cursor


SELECT …

is the SQL whose results the cursor will
point to


:minzip



Note the colon referring to a C variable
declared previously

EXEC SQL DECLARE cinfo CURSOR FOR


SELECT N. candname, N.party



FROM cand N


WHERE zip > :minzip


ORDER BY N.candname;




Slide
39

Embedding in C: An Example


OPEN cinfo
-

Executes the query and positions the
cursor before the first row of the output


FETCH cinfo INTO


assigns the data of the first row
(if it exists) into C program variables


CLOSE cinfo


Free the cursor’s resources

EXEC SQL OPEN cinfo;

EXEC SQL FETCH cinfo INTO :c_name;

While(SQLSTATE != “02000”) {


printf(“Candidate name is %s
\
n”, candname);


EXEC SQL FETCH cinfo INTO :c_name;

} ;

EXEC SQL CLOSE cinfo
;



Slide
40

Midterm


Thursday's Midterm will be open books and notes


At least 80% of the points will cover the Learning
Objectives from the lectures.

Slide
41

LO4.1: Alter Table*


Write SQL statements to eliminate the filler attributes
in the original candidate table


Write the statements necessary to declare that
pas.candid is a foreign key. Assume the data has
been cleaned.

Slide
42

LO4.2: Describe what an assertion

accomplishes *

What does this assertion accomplish?


CREATE ASSERTION check_assoc

CHECK (NOT EXISTS


(SELECT *


FROM comm JOIN pas ON comm.commid =
pas.commid


WHERE pass.candid <> comm.assoccand) )

Slide
43

LO4.3*


In next year’s election cycle, the FEC decides to store
committee data in two tables,

newcomm1(commid,commname,treasname,street1,street2,city,stat
e,zip)


and

newcomm2(commid,commdesig,commtype,party,filingfreq,intgrp,co
mmorg,assoccand)


Write a view so that SQL queries over the current year’s
database will work.