Advanced SQL

1. 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, i.e., tables. • For each table a DDL statement specifies: • A name for the table • A name for each attribute • The domain (i.e., a type) 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.

4. Date/Time Types in SQL (Cont.) • More complex types are also supported: • date - Dates, containing a year, month and date • time - Time of day, in hours, minutes and seconds • timestamp - Date plus time of day • interval - Period of time • text, BLOB, CLOB, image, geometry, etc. • Operations on complex types: (typical) • Subtracting a date/time/timestamp value from another gives a value of type “interval” (or rather “time interval”) • Interval values can be added to date/time/timestamp values • Values of individual fields can be extracted from date/time/timestamp: extract (year from student.birth-date) • String types can typically be cast to date/time/timestamp: cast <string-valued-expression> as date

5. Domain Types in SQL • Domains can be named: create domain person-name varchar(32) not null; • Most DBMSs provide support for user defined class types (SQL3 and later versions).

6. Create Table Construct • An table is defined using the create table command: create table r (A1D1, A2D2, ..., An Dn,(integrity-constraint1), ..., (integrity-constraintk)) • r is the name of the table • Ai is an attribute name • Di is a data type • Example: create table branch (branch-name varchar(16),branch-city varchar(32),assets integer)

7. Integrity Constraints in Create Table • Integrity constraints: • not null • primary key (A1, ..., An) - - Also enforces not null • check (P), where P is a predicate • Example: create table branch (branch-name varchar(16),branch-city varchar(32) not null,assets integer,primary key (branch-name), check(assets >= 0))

8. Referential Integrity in SQL • Keys can be specified as part of a create table statement: • primary key - attributes in the primary key. • unique key – attributes in a (non-primary) candidate key. • foreign key - attributes in 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 • Reference columns can be explicitly specified: foreign key (account-number) references account(account-number)\ • Foreign key references have several implications for insertions, deletions and modifications…

9. 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 • A DDL file can be coded by hand, or generated by a schema design or modeling tool.

10. Referential Integrity in SQL – Example create table customer(customer-name varchar(32),customer-street varchar(32),customer-city varchar(16),primary key (customer-name)) create table branch(branch-name varchar(16),branch-city varchar(16),assets integer,primary key(branch-name))

11. Referential Integrity in SQL – Example (Cont.) create table account(account-number char(10),branch-name varchar(16),balance integer,primary key (account-number), foreign key (branch-name) references branch) create table depositor(customer-name varchar(32),account-number char(10),primary key (customer-name, account-number),foreign key (account-number) references account,foreign key (customer-name) references customer) • Similarly for loan and borrower.

12. Cascading Actions in SQL • A foreign key reference can be enhanced to prevent insertion, deletion, and update errors. create table account ( . . .foreign key(branch-name) references branchon delete cascade on update cascade. . . ) • If a delete of a tuple in branchresults in a referential-integrity constraint violation, the delete “cascades” to the account relation. • Cascading updates are similar.

13. 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.

14. Drop and Alter Table Constructs • drop table - deletes all information about a table. drop table customer • alter table - used to add or delete attributes to an existing relation. alter table r add A D // Attribute A and domain D alter table r drop A // Attribute A • More generally, the alter table command can be used to modify an existing table in many ways, such as adding indexes, changing permissions, storage properties, etc.

15. Assertions • An assertionis a predicate expressing a condition that we wish the database always to satisfy. • Assertions are similar to DDL check constraints, but more general in that they can test conditions that apply across mutliple tables. • 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 the assertion.

16. 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 branchwhere (select sum(amount) from loanwhere loan.branch-name = branch.branch-name) > (select sum(balance) from accountwhere account.branch-name = branch.branch-name)))

17. 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 loanwhere not exists ( select borrower.customer-name from borrower, depositor, accountwhere loan.loan-number = borrower.loan-numberand borrower.customer-name = depositor.customer-nameand depositor.account-number = account.account-numberand account.balance >= 1000)))

18. Triggers • A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database. • 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.

19. 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

20. Trigger Example in SQL:1999 create trigger overdraft-trigger after update on account referencing new row as nrow for each rowwhen nrow.balance < 0begin atomicinsert 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

21. 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. Triggering Events and Actions in SQL

22. When Not To Use Triggers • Triggers, along with all the other integrity checking mechanisms, provide yet another opportunity to…slow 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 many of these things: • 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.

23. SQL provides a module language that permits definition of procedures: Conditional (if-then-else) statements Loops (for and while) Procedure definition with parameters Arbitrary SQL statements Stored Procedures: Typically stored in the database. Executed by calling them by name, on the command-line or from a program. Permit external applications to operate on the database without knowing about internal details about the database or even SQL. A standard that is not uncommon – put all queries in stored procedures; applications are then only allowed to call stored procedures. In the simplest case, a stored procedure simply contains a single query. Procedural Extensionsand Stored Procedures

24. Example: CREATE PROCEDURE stpgetauthors    @surname varchar(30)=null  AS BEGIN    IF @surname = null    BEGIN       RAISERROR( 'No selection criteria provided !', 10, 1)    END    ELSE    BEGIN       SELECT * FROM authors          WHERE au_lname LIKE @surname    END END Procedural Extensionsand Stored Procedures

25. 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 • Queries are compiled and optimized at compile time for the host program • No longer available in all DBMSs for many languages • Embedded SQL is explicitly identified in a program (varies by language): // Ada style EXEC SQL <embedded SQL statement > END-EXEC; // Java style # SQL { …. } ;

26. Example Query “Find the names and cities of customers with more than a given amount of dollars in some account.” • The following statement declares the query. • EXEC SQL • declare c cursor for select customer-name, customer-cityfrom depositor, customer, accountwhere depositor.customer-name = customer.customer-name and depositor account-number = account.account-numberand account.balance > :amount • END-EXEC • The open statement causes the query to be evaluated. EXEC SQL opencEND-EXEC

27. 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 closec END-EXEC Note: above details vary by language.

28. Dynamic SQL • Dynamic SQL is similar to embedded SQL in some ways: • Syntax is similar - SQL is intermixed with programming language code. • Uses vendor provided APIs and libraries. • Dynamic SQL, however: • Allows programs to construct, compile, optimize and submit queries at run time. • Does not require a pre-compiler. • The main problem with embedded and dynamic APIs is that they are vendor specific.

29. 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. ODBC

30. 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. ODBC (Cont.)

31. int ODBCexample() { RETCODE error; /* query return code */ 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); } ODBC Code

32. ODBC Code (Cont.) • Main body of program (i.e., “Do actual work”): char branchname[80];float balance;int lenOut1, lenOut2;HSTMT 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) >= SQL_SUCCESS) {printf (" %s %g\n", branchname, balance); }}SQLFreeStmt(stmt, SQL_DROP);

33. 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. • Similar to ODBC in general structure and operation: • Open a connection • Create a “statement” object • Execute queries using the Statement object to send queries and fetch results • Exception mechanism to handle errors

34. End of Chapter

35. SQL Data Definition for Part of the Bank Database

36. 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 withmax-balance(value) asselect max (balance)fromaccountselectaccount-numberfromaccount, max-balancewhereaccount.balance = max-balance.value

37. 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. withbranch-total (branch-name, value) asselectbranch-name, sum (balance)fromaccountgroupbybranch-namewithbranch-total-avg(value) asselectavg (value)frombranch-totalselect branch-namefrombranch-total, branch-total-avg wherebranch-total.value >= branch-total-avg.value

38. Extra Material on JDBC and Application Architectures

39. 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-numberfrom loan • Add a new tuple to branch-loan insert into branch-loanvalues (‘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?).

40. 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.

41. 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.

42. 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 Schemas, Catalogs, and Environments

43. Transactions in JDBC • As with ODBC, each statement gets committed automatically in JDBC • To turn off auto commit useconn.setAutoCommit(false); • To commit or abort transactions use conn.commit() or conn.rollback() • To turn auto commit on again, use conn.setAutoCommit(true);

44. 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 (?,?)}” ); Procedure and Function Calls in JDBC

45. 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 <= rsmd.getColumnCount( ); i++ ) { String name = rsmd.getColumnName(i); String typeName = rsmd.getColumnTypeName(i); } Result Set MetaData

46. 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 Database Meta Data

47. 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

48. 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 Two-tier Model

49. CGI Program Database Server Client Client Client Three Tier Model JDBC Application/HTTP Server Servlets HTTP/Application Specific Protocol Network

50. 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 Three-tier Model (Cont.)