Chapter 4: Advanced SQL

1
Chapter 4 Advanced SQL

• Data Definition Language
• Domains
• Integrity Constraints
• Assertions
• Triggers
• Stored Procedures
• Embedded Dynamic SQL
• ODBC JDBC

2
Data Definition Language (DDL)

• DDL allows the specification of a set of
  relations.
• For each relation a DDL statement specifies
• A name for the relation
• A name for each attribute
• The domain of values associated with each
  attribute
• Integrity constraints
• An associated set of indices
• Security and authorization information
• The physical storage structure for the relation

3
Domain Types in SQL

• Standard SQL Types
• varchar(n) - Variable length character string,
  maximum length n.
• char(n) - Fixed length character string, with
  length n.
• int - Integer (machine-dependent).
• smallint - Small integer (machine-dependent).
• real - Floating point numbers machine-dependent
  precision.
• double precision - Floating point numbers
  machine-dependent precision.
• float(n) - Floating point number, precision of at
  least n digits.
• numeric(p,d) - Fixed point number p digits of
  precision and d digits to the right of decimal
  point.
• Most DBMSs provide other types, both simple and
  complex.
• Domains can be named
• create domain person-name varchar(32) not null

4
Date/Time Types in SQL (Cont.)

• More complex types are also supported
• date - Dates, containing a year, month and date
  date 2001-7-27
• time - Time of day, in hours, minutes and
  seconds time 090030 or time 090030.75
• timestamp - date plus time of day timestamp
  2001-7-27 090030.75
• interval - period of time Interval 1 day
• text, BLOB, CLOB, image, geometry, etc.
• Operations on complex types
• 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
• extract (year from r.starttime)
• String types can typically be cast to
  date/time/timestamp
• cast ltstring-valued-expressiongt as date

5
Create Table Construct

• An SQL relation is defined using the create table
  command
• create table r (A1 D1, A2 D2, ..., An
  Dn, (integrity-constraint1), ..., (integr
  ity-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
• Example
• create table branch (branch-name char(15), b
  ranch-city char(30), assets integer)

6
Integrity Constraints in Create Table

• Integrity constraints can be specified in DDL
• not null
• primary key (A1, ..., An)
• check (P), where P is a predicate
• Example
• create table branch (branch-name char(15), b
  ranch-city char(30) not null, assets integer,
  primary key (branch-name), // enforces not null
• check (assets gt 0))

7
Referential Integrity in SQL

• Keys (all types) can be specified as part of a
  create table statement
• primary key - lists attributes that comprise the
  primary key.
• unique key - lists attributes that comprise a
  (non-primary) candidate key.
• foreign key - lists the attributes that comprise
  a foreign key and the name of the relation
  referenced by the foreign key.
• A foreign key references the primary key of the
  referenced table
• foreign key (account-number) references
  account
• Short form for specifying a single column as
  foreign key
• account-number char (10) references account
• Reference columns can be explicitly specified
• foreign key (account-number) references
  account(account-number)

8
DDL Files

• A DDL file typically contains a collection of
• create table statements
• create index statements
• statements that create and/or specify other
  things
• Security and authority information
• Physical storage details
• See page 129 for an example.

9
Referential Integrity in SQL Example

• create table customer(customer-name char(20),cus
  tomer-street char(30),customer-city char(30),pri
  mary key (customer-name))
• create table branch(branch-name char(15),branch-
  city char(30),assets integer,primary key
  (branch-name))

10
Referential Integrity in SQL Example (Cont.)

• create table account(account-number char(10),bra
  nch-name char(15),balance integer,primary key
  (account-number), foreign key (branch-name)
  references branch)
• create table depositor(customer-name char(20),ac
  count-number char(10),primary key
  (customer-name, account-number),foreign key
  (account-number) references account,foreign key
  (customer-name) references customer)

11
Cascading Actions in SQL

• create table account
• . . . foreign key(branch-name) references
  branch on delete cascade on update cascade .
  . . )
• If a delete of a tuple in branch results in a
  referential-integrity constraint violation, the
  delete cascades to the account relation.
• Cascading updates are similar.

12
Cascading Actions in SQL (Cont.)

• If there is a chain of foreign-keys across
  multiple relations, with on delete cascade
  specified for each, a deletion or update can
  propagate across the entire chain.
• Alternative to cascading
• on delete set null
• on delete set default
• Null values in foreign key attributes complicate
  SQL referential integrity semantics, and are best
  prevented using not null.

13
Drop and Alter Table Constructs

• drop table - deletes all information about a
  relation.
• drop table customer
• alter table - used to add attributes to an
  existing relation.
• alter table r add A D // Attribute A
  and domain D
• All tuples in are assigned null as the value for
  the new attribute.
• The alter table command can also be used to drop
  attributes.
• alter table r drop A // Attribute A
• Dropping of attributes not supported by many
  DBMSs.

14
Assertions

• An assertion is a predicate expressing a
  condition that we wish the database always to
  satisfy.
• An assertion in SQL takes the form
• create assertion ltassertion-namegt check
  ltpredicategt
• When an assertion is made, the system tests it
  for validity, and tests it again on every update
  that may violate the assertion
• This testing may introduce a significant amount
  of overhead hence assertions should be used with
  great care.

15
Assertion Example

• The sum of all loan amounts for each branch must
  be no greater 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) gt
  (select sum(balance) from account
  where account.branch-name
  branch.branch-name)))

16
Assertion Example

• Every loan has at least one borrower who
  maintains an account with a minimum balance of
  1000.00
• create assertion balance-constraint check
  (not exists ( select loan.loan-number
  from loan where not exists (
  select borrower.customer-name from
  borrower, depositor, account where
  loan.loan-number borrower.loan-number
  and borrower.customer-name
  depositor.customer-name and
  depositor.account-number account.account-number
  and account.balance gt 1000)))

17
Triggers

• A trigger is a statement that is executed
  automatically by the system as a side effect of a
  modification to the database.
• More general actions are taken than with an
  assertion..
• To design a trigger mechanism, we must
• Specify the conditions under which the trigger is
  to be executed.
• Specify the actions to be taken when the trigger
  executes.

18
Trigger Example

• Suppose the bank deals with overdrafts by
• Setting the account balance to zero
• Creating a loan in the amount of the overdraft
• The condition for executing the trigger is an
  update to the account relation that results in a
  negative balance value.
• The actions to be taken by the trigger are to
• Create a loan tuple
• Create a borrower tuple
• Set the account balance to 0

19
Trigger Example in SQL1999

• create trigger overdraft-trigger after update on
  account referencing new row as nrow
  
  for each rowwhen nrow.balance
  lt 0begin atomic insert into loan
  values (nrow.account-number, nrow.branch-name,
  nrow.balance)
• insert into borrower (select
  depositor.customer-name, depositor.account-number
  from depositor where nrow.account-number
  depositor.account-number)
• update account set balance 0 where
  account.account-number nrow.account-numberend

20
Triggering Events and Actions in SQL

• Triggering event can be insert, delete or update.
• Triggers on update can be restricted to specific
  attributes
• create trigger overdraft-trigger after update of
  balance on account
• Values of attributes before and after an update
  can be referenced
• referencing old row as (deletes and updates)
• referencing new row as (inserts and updates)
• Triggers can be activated before an event, which
  can serve as extra constraints, e.g. convert
  blanks to null.

21
When Not To Use Triggers

• Triggers, along with all the other integrity
  checking mechanisms, provide yet another
  opportunity toslow up the database
• Triggers have been used for tasks such as
• Maintaining summary or derived data (e.g. total
  salary of each department).
• Replicating databases.
• There are better, more efficient ways to do this
• DBMSs typically provide materialized view
  facilities to maintain summary data.
• Data warehousing software can be used for
  maintaining summary/derived data.
• DBMSs provide built-in support for replication.

22
Procedural Extensionsand Stored Procedures

• SQL provides a module language that permits
  definition of procedures
• Conditional (if-then-else) statements
• Loops (for and while)
• Procedure definition with parameters
• Stored Procedures
• Can be stored in the database
• Executed using the call statement
• Permit external applications to operate on the
  database without knowing about internal details
  about the database or even SQL
• In the simplest case, a stored procedure simply
  contains a single query.

23
Embedded SQL

• The SQL standard defines embeddings of SQL in a
  variety of (host) programming languages such as
  Pascal, PL/I, Fortran, C, etc.
• Requires a vendor provided pre-compiler and
  libraries.
• Based on vendor specific APIs.
• Not available in all DBMSs, e.g., SQL Server
• Embedded SQL is explicitly identified in a
  program (varies by language)
• // Ada style
• EXEC SQL ltembedded SQL statement gt END-EXEC
• // Java style
• SQL .

24
Example Query

• Find the names and cities of customers with more
  than amount dollars in some account, where amount
  is a variable in a program.
• The following statement declares the query.
• EXEC SQL
• declare c cursor for select
  customer-name, customer-city from
  depositor, customer, account where
  depositor.customer-name customer.customer-name
  and depositor account-number
  account.account-number and
  account.balance gt amount
• END-EXEC
• The open statement causes the query to be
  evaluated.
• EXEC SQL open c END-EXEC

25
Embedded SQL (Cont.)

• The fetch statement causes the values of one
  tuple in the query result to be placed in host
  language variables.
• EXEC SQL fetch c into cn, cc END-EXEC
• Repeated calls (loop) will fetch successive
  tuples from the query result
• Variable SQLSTATE is set to 02000 to indicate
  no more data is available
• The close statement causes the database system to
  delete the temporary relation that holds the
  result of the query.
• EXEC SQL close c END-EXEC
• Note above details vary by language.

26
Dynamic SQL

• Dynamic SQL allows programs to construct and
  submit queries at run time.
• Does not require a pre-compiler but does require
  vendor supplied libraries.
• Queries are constructed, submitted, and compiled
  at run-time.
• This approach also uses APIs that are primarily
  vendor specific.
• 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 sqlprogchar account 10
  A-101EXEC 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.

27
ODBC

• Open DataBase Connectivity (ODBC) is a standard
  for programs to communicate with database
  servers.
• Independent of language, DBMS or operating
  system.
• ODBC defines an API providing the functionality
  to
• open a connection with a database
• send queries and updates
• get back results
• Each DBMS vendor supporting ODBC provides a
  "driver" library that must be linked with the
  client program.
• When a client program makes an ODBC API call, the
  code in the library communicates with the server
  to carry out the requested action, and fetch
  results, if necessary.

28
ODBC (Cont.)

• An ODBC program first allocates an SQL
  environment, and then a database connection
  handle.
• An ODBC program then opens the database
  connection using SQLConnect() with the following
  parameters
• connection handle
• server to connect to
• userid
• password
• Must also specify types of arguments
• SQL_NTS denotes previous argument is a
  null-terminated string.

29
ODBC Code

• int ODBCexample()
• RETCODE error
• HENV env / environment /
• HDBC conn / database connection /
• SQLAllocEnv(env)
• SQLAllocConnect(env, conn)
• SQLConnect(conn, "aura.bell-labs.com", SQL_NTS,
  "avi", SQL_NTS, "avipasswd", SQL_NTS)
• . Do actual work
• SQLDisconnect(conn)
• SQLFreeConnect(conn)
• SQLFreeEnv(env)

30
ODBC Code (Cont.)

• SQL commands are sent to the database using
  SQLExecDirect().
• Resulting tuples are fetched using SQLFetch().
• SQLBindCol() binds C language variables to
  attributes of the query result
• Good programming requires checking results of
  every function call for errors we have omitted
  most checks for brevity.

31
ODBC Code (Cont.)

• Main body of program (i.e., Do actual work)
• char branchname80float balanceint lenOu
  t1, lenOut2HSTMT stmt
• SQLAllocStmt(conn, stmt)char sqlquery
  "select branch_name, sum (balance)
  from account
  group by branch_name"
• error SQLExecDirect(stmt, sqlquery,
  SQL_NTS)
• if (error SQL_SUCCESS)
  SQLBindCol(stmt, 1, SQL_C_CHAR, branchname ,
  80, lenOut1) SQLBindCol(stmt, 2,
  SQL_C_FLOAT, balance, 0 , lenOut2)
• while (SQLFetch(stmt) gt SQL_SUCCESS)
  printf (" s g\n", branchname,
  balance) SQLFreeStmt(stmt, SQL_DROP)

32
JDBC

• JDBC is a Java specific 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
• Create a statement object
• Execute queries using the Statement object to
  send queries and fetch results
• Exception mechanism to handle errors

33
End of Chapter
34
SQL Data Definition for Part of the Bank Database
35
With Clause

• With clause allows views to be defined locally to
  a query, rather than globally.
• Analogous to procedures in a programming
  language.
• Find all accounts with the maximum balance
  with max-balance(value) as select max
  (balance) from account select
  account-number from account, max-balance
  where account.balance max-balance.value

36
Complex Query using With Clause

• Find all branches where the total account deposit
  is greater than the average of the total account
  deposits at all branches.

with branch-total (branch-name, value) as
select branch-name, sum (balance) from
account group by branch-name with
branch-total-avg(value) as select avg
(value) from branch-total select
branch-name from branch-total,
branch-total-avg where branch-total.value gt
branch-total-avg.value
37
Extra Material on JDBC and Application
Architectures
38
Update of a View

• Create a view of all loan data in the loan
  relation, hiding the amount attribute
• create view branch-loan as select
  branch-name, loan-number from loan
• Add a new tuple to branch-loan
• insert into branch-loan values (Perryridge,
  L-307)
• This insertion must be represented by the
  insertion of the tuple
• (L-307, Perryridge, null)
• into the loan relation (but does it take place
  automatically?).

39
Update of a View

• Updates on more complex views are difficult or
  impossible to translate, and hence are
  disallowed.
• Most SQL implementations allow updates only on
  simple views (without aggregates) defined on a
  single relation.
• It is probably best not to allow insertion into
  views.

40
Other SQL Features

• SQL sessions
• client connects to an SQL server, establishing a
  session
• executes a series of statements
• disconnects the session
• can commit or rollback the work carried out in
  the session
• An SQL environment contains several components,
  including a user identifier, and a schema, which
  identifies which of several schemas a session is
  using.

41
Schemas, Catalogs, and Environments

• Three-level hierarchy for naming relations.
• Database contains multiple catalogs
• Each catalog can contain multiple schemas
• SQL objects such as relations and views are
  contained within a schema
• e.g. catalog5.bank-schema.account
• Each user has a default catalog and schema, and
  the combination is unique to the user.
• Default catalog and schema are set up for a
  connection
• Catalog and schema can be omitted, defaults are
  assumed
• Multiple versions of an application (e.g.
  production and test) can run under separate
  schemas

42
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)

43
Procedure and Function Calls in JDBC

• JDBC provides a class CallableStatement which
  allows SQL stored procedures/functions to be
  invoked.
• CallableStatement cs1
  conn.prepareCall( call proc (?,?) )
• CallableStatement cs2
  conn.prepareCall( ? call func (?,?) )

44
Result Set MetaData

• The class ResultSetMetaData provides information
  about all the columns of the ResultSet.
• Instance of this class is obtained by
  getMetaData( ) function of ResultSet.
• Provides Functions for getting number of columns,
  column name, type, precision, scale, table from
  which the column is derived etc.
• ResultSetMetaData rsmd rs.getMetaData ( )
• for ( int i 1 i lt rsmd.getColumnCount( )
  i )
• String name rsmd.getColumnName(i)
• String typeName rsmd.getColumnTypeName
  (i)

45
Database Meta Data

• The class DatabaseMetaData provides information
  about database relations
• Has functions for getting all tables, all columns
  of the table, primary keys etc.
• E.g. to print column names and types of a
  relation
• DatabaseMetaData dbmd conn.getMetaData( )
• ResultSet rs dbmd.getColumns( null,
  BANK-DB, account, )
  //Arguments catalog, schema-pattern,
  table-pattern, column-pattern //
  Returns 1 row for each column, with several
  attributes such as //
  COLUMN_NAME, TYPE_NAME, etc.
• while ( rs.next( ) )
  System.out.println( rs.getString(COLUMN_NAME) ,
  
  rs.getString(TYPE_NAME)
  
• There are also functions for getting information
  such as
• Foreign key references in the schema
• Database limits like maximum row size, maximum
  no. of connections, etc

46
Application Architectures

• Applications can be built using one of two
  architectures
• Two tier model
• Application program running at user site directly
  uses JDBC/ODBC to communicate with the database
• Three tier model
• Users/programs running at user sites communicate
  with an application server. The application
  server in turn communicates with the database

47
Two-tier Model

• E.g. Java code runs at client site and uses JDBC
  to communicate with the backend server
• Benefits
• flexible, need not be restricted to predefined
  queries
• Problems
• Security passwords available at client site, all
  database operation possible
• More code shipped to client
• Not appropriate across organizations, or in large
  ones like universities

48
Three Tier Model
JDBC
Application/HTTP Server
Servlets
HTTP/Application Specific Protocol
Network
49
Three-tier Model (Cont.)

• E.g. Web client Java Servlet using JDBC to talk
  with database server
• Client sends request over http or
  application-specific protocol
• Application or Web server receives request
• Request handled by CGI program or servlets
• Security handled by application at server
• Better security
• Fine granularity security
• Simple client, but only packaged transactions

50
Statistical Databases

• Problem how to ensure privacy of individuals
  while allowing use of data for statistical
  purposes (e.g., finding median income, average
  bank balance etc.)
• Solutions
• System rejects any query that involves fewer than
  some predetermined number of individuals.
• ? Still possible to use results of multiple
  overlapping queries to deduce data about an
  individual
• Data pollution -- random falsification of data
  provided in response to a query.
• Random modification of the query itself.
• There is a tradeoff between accuracy and
  security.

51
An n-ary Relationship Set
52
Authorization-Grant Graph
53
Attempt to Defeat Authorization Revocation
54
Authorization Graph
55
Physical Level Security

• Protection of equipment from floods, power
  failure, etc.
• Protection of disks from theft, erasure, physical
  damage, etc.
• Protection of network and terminal cables from
  wiretaps non-invasive electronic eavesdropping,
  physical damage, etc.
• Solutions
• Replicated hardware
• mirrored disks, dual busses, etc.
• multiple access paths between every pair of
  devises
• Physical security locks,police, etc.
• Software techniques to detect physical security
  breaches.

56
Human Level Security

• Protection from stolen passwords, sabotage, etc.
• Primarily a management problem
• Frequent change of passwords
• Use of non-guessable passwords
• Log all invalid access attempts
• Data audits
• Careful hiring practices

57
Operating System Level Security

• Protection from invalid logins
• File-level access protection (often not very
  helpful for database security)
• Protection from improper use of superuser
  authority.
• Protection from improper use of privileged
  machine intructions.

58
Network-Level Security

• Each site must ensure that it communicate with
  trusted sites (not intruders).
• Links must be protected from theft or
  modification of messages
• Mechanisms
• Identification protocol (password-based),
• Cryptography.

59
Database-Level Security

• Assume security at network, operating system,
  human, and physical levels.
• Database specific issues
• each user may have authority to read only part of
  the data and to write only part of the data.
• User authority may correspond to entire files or
  relations, but it may also correspond only to
  parts of files or relations.
• Local autonomy suggests site-level authorization
  control in a distributed database.
• Global control suggests centralized control.

60
External World Actions

• We sometimes require external world actions to be
  triggered on a database update.
• For example, re-ordering an item whose quantity
  in a warehouse has become small, or turning on an
  alarm light.
• Triggers cannot be used to directly implement
  external-world actions, but
• Triggers can be used to record actions-to-be-taken
  in a separate table.
• Have an external process that repeatedly scans
  the table, carries out external-world actions and
  deletes action from table.
• Suppose a warehouse has the following tables
• inventory(item, level) How much of each item is
  in the warehouse
• minlevel(item, level) What is the minimum
  desired level of each item
• reorder(item, amount) What quantity should we
  re-order at a time
• orders(item, amount) Orders to be placed (read
  by external process)

61
External World Actions (Cont.)

• create trigger reorder-trigger after update of
  amount on inventory
• referencing old row as orow, new row as nrow
• for each row
• when nrow.level lt (select level
• from minlevel
• where minlevel.item
  orow.item)
• and orow.level gt (select level
• from minlevel
• where
  minlevel.item orow.item)
• begin
• insert into orders
• (select item, amount
• from reorder
• where reorder.item orow.item)
• end