2 December 2005 Introduction to Databases Advanced SQL Prof. Beat Signer Department of Computer Science Vrije Universiteit Brussel beatsigner.com

Beat Signer - Department of Computer Science - [email protected] 2 March 20, 2019 Context of Today's Lecture Access Methods System Buffers Authorisation Control Integrity Checker Command Processor Program Object Code DDL Compiler File Manager Buffer Manager Recovery Manager Scheduler Query Optimiser Transaction Manager Query Compiler Queries Catalogue Manager DML Preprocessor Database Schema Application Programs Database Manager Data Manager DBMS Programmers Users DB Admins Based on 'Components of a DBMS', Database Systems, T. Connolly and C. Begg, Addison-Wesley 2010 Data, Indices and System Catalogue

Beat Signer - Department of Computer Science - [email protected] 3 March 20, 2019 Authorisation ▪ Different types of authorisations (privileges) can be assigned to a user via DDL ▪ insert, read, update and delete privileges ▪ The creator of a new relation is automatically given all privileges on that relation grantPrivilege = "GRANT" , "ALL PRIVILEGES" , "TO" , ( "PUBLIC" | grantee ) | "GRANT" , privilege , { "," , privilege } ) , "ON" , ( table | view ) , { "," , ( table | view ) } , "TO" , ( "PUBLIC" | grantee ) , [ "WITH GRANT OPTION" ]; privilege = "INSERT" | "SELECT" | ( "UPDATE" , [ "(" , column , { "," , column } , ")" ] ) | "DELETE"; grantee = ( user | role ) , { "," , ( user | role ) };

Beat Signer - Department of Computer Science - [email protected] 4 March 20, 2019 Authorisation ... ▪ An update privilege may be limited to certain attributes only ▪ Roles can be used to group users GRANT ALL PRIVILEGES TO Alice, Bob; GRANT INSERT, UPDATE (amount, status) ON Order TO Eve WITH GRANT OPTION; createRole = "CREATE ROLE" , role; grantRole = "GRANT" , role , "TO" ( "PUBLIC" | grantee ); CREATE ROLE PowerUser; GRANT PowerUser TO Alice, Bob;

Beat Signer - Department of Computer Science - [email protected] 5 March 20, 2019 Authorisation ... ▪ Privileges can be passed to other users if the WITH GRANT OPTION has been specified ▪ Privileges and roles can be removed via the REVOKE command (similar to GRANT statement) ▪ Examples ▪ Note that the revocation of a privilege may have an effect on grants that have been passed to other users ▪ authorisation graph has to be inspected REVOKE INSERT ON Order FROM Eve; REVOKE PowerUser FROM Bob;

Beat Signer - Department of Computer Science - [email protected] 6 March 20, 2019 Authorisation Graph ▪ Privilege revocation examples ▪ remove privilege from user1 (g1 ) → user4 still has privilege via g24 ▪ remove privilege from user2 (g2 ) → user2 still has privilege via g23 ▪ remove privilege from user3 (g3 ) → user3 still has privilege via g23 ▪ remove privilege from user2 (g2 ) and user3 (g3 ) - g23 still exists but is no longer part of a path starting from the admin → user2 and user3 no longer have privilege → user5 no longer has privilege user1 user2 user3 user4 user5 admin g1 g2 g24 g3 g14 g25 g23

Beat Signer - Department of Computer Science - [email protected] 7 March 20, 2019 Working with SQL ▪ There are two main modes to work with SQL ▪ via generic query interfaces or SQL application interfaces ▪ note that standard SQL is not Turing complete ▪ Generic SQL interface ▪ command-line or web-based interface ▪ answer single questions (one query) ▪ interactive query evaluation ▪ SQL interface for applications ▪ sequence of queries, inserts, updates and deletes ▪ SQL statements that are embedded within a host language ▪ query results are processed and reused in non-declarative actions - I/O interfaces - human-computer interaction

Beat Signer - Department of Computer Science - [email protected] 8 March 20, 2019 Impedance Mismatch Problem ▪ Combination of SQL with a host language ▪ mix of declarative and procedural programming paradigms ▪ two completely different data models - relational model with sets and bags - no pointers, loops and branches in basic SQL - no NULL values in typical host languages ▪ different set of data types - many programming languages do not offer Date or Time data types ▪ Interfacing with SQL is not straightforward ▪ data has to be converted between the host language and SQL due to the impedance mismatch ▪ ~30% of the code and effort is used for this conversion! ▪ various libraries to simplify the mapping (e.g. Hibernate)

Beat Signer - Department of Computer Science - [email protected] 9 March 20, 2019 Impedance Mismatch Problem ... ▪ Why not simply use a single language? ▪ both languages have their domain-specific strengths /** * Transforms the Swiss Projection * coordinates (y, x, h') to the * ellipsoidic WGS84 coordinates (phi, * lambda, h) using and approach of * [U. Marti] based on the formulas of * [Bolliger, 1967]. * @param swissCoordinates the Swiss * coordinates to be transformed. * @return the WGS84 coordinates for * the specified Swiss coordinates. */ public static final WGS84Coordinates transformSwissCoordinatesToWGS84( SwissCoordinates swissCoordinates) { double yPrime = (swissCoordinates.getY() - 600000) / 1000000; double xPrime = (swissCoordinates.getX() - 200000) / 1000000; double xPrimePow2 = xPrime * xPrime; double xPrimePow3 = xPrimePow2 * xPrime; double yPrimePow2 = yPrime * yPrime; double yPrimePow3 = yPrimePow2 * ... Impedance Mismatch Application Code Database

Beat Signer - Department of Computer Science - [email protected] 10 March 20, 2019 SQL Programming Environments ▪ Embedded SQL (ESQL) ▪ integration of SQL in a host programming language ▪ e.g. interfacing with C through ESQL/C ▪ SQL Call-Level Interface (SQL/CLI) ▪ e.g. interfacing with Java through JDBC ▪ SQL Persistent Stored Modules (SQL/PSM) ▪ stored procedures ▪ Triggers

Beat Signer - Department of Computer Science - [email protected] 11 March 20, 2019 Database Applications ▪ Task sharing between client and server ▪ client does data presentation ▪ server does data management ▪ Where should we implement the application logic? ▪ on the client - ESQL, SQL/CLI, ODBC, JDBC, ... ▪ on the server - SQL/PSM, Stored Procedures, Triggers ▪ thin client vs. thick client

Beat Signer - Department of Computer Science - [email protected] 12 March 20, 2019 Example Database customerID name street postcode city orderID customerID cdID date amount status Customer CD Order name address Producer cdID title duration price year producer CREATE TABLE Customer ( customerID INTEGER CHECK (customerID > 0) PRIMARY KEY, name VARCHAR(30) NOT NULL, street VARCHAR(30) NOT NULL, postcode SMALLINT CHECK (postcode > 0), city VARCHAR(20) );

Beat Signer - Department of Computer Science - [email protected] 13 March 20, 2019 Example Database ... CREATE TABLE Producer ( name VARCHAR(40) PRIMARY KEY, address VARCHAR(30) ); CREATE TABLE Order ( orderID INTEGER CHECK (orderID > 0) PRIMARY KEY, customerID INTEGER, cdID INTEGER, date DATE, amount INTEGER, Status VARCHAR(20) NOT NULL DEFAULT 'open', UNIQUE (customerID, cdID, date), FOREIGN KEY (customerID) REFERENCES Customer(customerID) ON UPDATE CASCADE ON DELETE SET NULL, FOREIGN KEY (cdID) REFERENCES CD(cdID) ON UPDATE CASCADE );

Beat Signer - Department of Computer Science - [email protected] 14 March 20, 2019 Example Database ... CREATE TABLE CD ( cdID INTEGER PRIMARY KEY, title VARCHAR(30) NOT NULL, duration SMALLINT, price SMALLINT, year SMALLINT, producer VARCHAR(40), FOREIGN KEY (producer) REFERENCES Producer(name) );

Beat Signer - Department of Computer Science - [email protected] 15 March 20, 2019 Embedded SQL ▪ The expressiveness of SQL can be enhanced by embedding SQL statements into application code ▪ Use of a preprocessor ▪ transforms SQL statements into function calls in the host language ▪ type checks, syntax and semantic validation ▪ error handling ▪ Data interchange via shared variables ▪ shared variables are used in SQL statements as well as in statements of the host language ▪ A special SQLSTATE variable is used to connect the host language with the SQL execution engine

Beat Signer - Department of Computer Science - [email protected] 16 March 20, 2019 Embedded SQL Program Processing ▪ The preprocessor transforms a program with embedded SQL statements into the host language ▪ e.g. nsqlprep.exe for ESQL-C ▪ The compiler produces the linked program object code Host language with Embedded SQL Host language with function calls Program object code Embedded SQL preprocessor Host language compiler SQL library

Beat Signer - Department of Computer Science - [email protected] 17 March 20, 2019 Call Level Interface ▪ SQL library functions are called directly ▪ not tied to particular DBMS (drivers stored separately) ▪ Various implementations exist ▪ SQL/CLI for C ▪ Open Database Connectivity (ODBC) and its successor OLE DB ▪ Java™ Database Connectivity (JDBC) ▪ ActiveX® Data Objects (ADO)

Beat Signer - Department of Computer Science - [email protected] 18 March 20, 2019 Java Database Connectivity (JDBC) ▪ The following JDBC packages are part of Java SE 10 ▪ java.sql.* - basic JDBC functionality for J2SE ▪ javax.sql.* - supplemental extensions for J2EE ▪ The standardised Java API provides database connectivity through vendor-specific drivers ▪ Limited code portability among SQL platforms due to vendor-specific SQL dialects

Beat Signer - Department of Computer Science - [email protected] 19 March 20, 2019 Opening a JDBC Connection ▪ The JDBC URL varies for different drivers ▪ e.g. jdbc:microsoft:sqlserver for Microsoft SQL Server public Connection openConnection() { try { Connection conn = DriverManager.getConnection( "jdbc:microsoft:sqlserver://localhost:1433", "fred", "password"); return conn; } catch (SQLException e) { System.out.println("Opening JDBC Connection failed."); return null; } }

Beat Signer - Department of Computer Science - [email protected] 20 March 20, 2019 Statements ▪ There are three different kinds of statements ▪ Statement - general statement ▪ PreparedStatement - precompiled statement - more efficient if the same query is executed multiple times ▪ CallableStatement - used to call stored procedures ▪ Methods for executing a statement ▪ executeUpdate(String sql) ▪ executeQuery(String sql) ▪ No parameters are required for PreparedStatements

Beat Signer - Department of Computer Science - [email protected] 21 March 20, 2019 Statements ... void insertProducer(String name, String address) { try { Connection conn = this.openConnection(); Statement s = conn.createStatement(); s.executeUpdate("INSERT INTO Producer VALUES ('" + name + "', '" + address + "')"); } catch (SQLException e) { System.out.println("Inserting \"" + name + "\" failed."); } } public void insertProducer(String name, String address) { try { Connection conn = this.openConnection(); Statement s = conn.prepareStatement("INSERT INTO Producer VALUES ('" + name + "', " + address + ")"); s.executeUpdate(); } catch (SQLException e) { System.out.println("Inserting \"" + name + "\" failed."); } }

Beat Signer - Department of Computer Science - [email protected] 22 March 20, 2019 Result Set ▪ JDBC represents cursors as ResultSet ▪ API offers navigation methods on a ResultSet ▪ next(), previous() ▪ first(), last() ▪ absolute(int row), relative(int rows) ▪ API offers various get methods to fetch data as well as update methods for common SQL data types

Beat Signer - Department of Computer Science - [email protected] 23 March 20, 2019 Result Set Example ▪ Note that it would be easier to use the SQL AVG operator public float getAverageCDLength() { float result = 0.0; try { Connection conn = this.openConnection(); Statement s = conn.createStatement(); ResultSet set = s.executeQuery("SELECT length FROM CD"); int i = 0; while (set.next()) { result += set.getInt(1); i++; } return result/i; } catch (SQLException e) { System.out.println("Calculation of average length failed."); return 0; } }

Beat Signer - Department of Computer Science - [email protected] 24 March 20, 2019 Parameter Parsing ▪ Prepared statements can be parameterised ▪ flexibility to use different values in a given query ▪ A question mark (?) is used as a placeholder in the query string ▪ Various methods to insert data at runtime ▪ setString(int index, String s) ▪ setInt(int index, int i) ▪ setBoolean(int index, boolean b) ▪ setDate(int index, Date d) ▪ ...

Beat Signer - Department of Computer Science - [email protected] 25 March 20, 2019 Parameter Parsing Example public void insertProducers(List producers) { try { Connection conn = this.openConnection(); Statement s = conn.prepareStamement( "INSERT INTO Producer(name, address) VALUES(?, ?)"); for (Producer producer : producers) { s.setString(1, producer.getName()); s.setString(2, producer.getAddress()); s.executeUpdate(); } } catch (SQLException e) { System.out.println("Insertion of Producers failed."); } }

Beat Signer - Department of Computer Science - [email protected] 26 March 20, 2019 Special Datatypes and NULL Values ▪ Some SQL datatypes cannot be easily mapped to Java datatypes ▪ JDBC offers implementations for these types ▪ Clob, Blob, Date, Time, Timestamp ▪ Null values are handled via special methods ▪ wasNull() ▪ updateNull(int index) ▪ setNull(int index, int sqlType)

Beat Signer - Department of Computer Science - [email protected] 27 March 20, 2019 JDBC Summary ▪ Concepts of ESQL and JDBC are similar ▪ State of the art for Java database programming ▪ Generic JDBC-ODBC driver if no specific JDBC driver is available ▪ Java Data Objects (JDO) ▪ enables Java programmers to directly store their Java domain model instances in a database ▪ hides SQL from the programmer ▪ https://java.sun.com/javase/technologies/database/

Beat Signer - Department of Computer Science - [email protected] 28 March 20, 2019 Application Logic on the Server Side ▪ Stored Procedures ▪ PL/SQL - Oracle ▪ Transact-SQL - Microsoft ▪ standardised as SQL/PSM in SQL-99 - Persistent Stored Modules ▪ Triggers ▪ event driven execution of application logic ▪ User Defined Types ▪ standardised in SQL-99

Beat Signer - Department of Computer Science - [email protected] 29 March 20, 2019 SQL Persistent Stored Modules ▪ SQL is extended with constructs known from high-level programming languages ▪ assignments, branches, loops, subroutines and exceptions createProcedure = "CREATE PROCEDURE" , name , "(" , parameters , ")" , localDeclarations , procedureBody; createFunction = "CREATE FUNCTION" , name , "(" , parameters , ")" , "RETURNS" , type , localDeclarations , functionBody;

Beat Signer - Department of Computer Science - [email protected] 30 March 20, 2019 Parameters ▪ SQL/PSM defines three types for parameters ▪ IN - input only ▪ OUT - output only ▪ INOUT - both input and output ▪ The default mode is IN ▪ Procedure parameters can have any mode ▪ Function parameters can have IN mode only ▪ result given back only via the return value

Beat Signer - Department of Computer Science - [email protected] 31 March 20, 2019 Procedure Example ▪ Procedure parameters can have any mode ▪ DB administrators often create these type of procedures (for maintenance) CREATE PROCEDURE RelocateProducer( IN oldAddress VARCHAR(30), IN newAddress VARCHAR(30) ) UPDATE Producer SET address = newAddress WHERE address = oldAddress;

Beat Signer - Department of Computer Science - [email protected] 32 March 20, 2019 Function Example CREATE FUNCTION AverageCDLengthOfProducer( IN name VARCHAR(30), ) RETURNS INTEGER DECLARE result INTEGER; BEGIN SELECT AVG(length) INTO result FROM CD WHERE producer = name; RETURN result; END;

Beat Signer - Department of Computer Science - [email protected] 33 March 20, 2019 SQL/PSM Statements ▪ Procedure calls ▪ Function evaluation ▪ functions cannot be called ▪ functions are directly invoked as parts of expressions ▪ Return statement ▪ does not terminate the function ▪ additional RETURN statements may cause the return value to change after an initial return statement CALL name (arguments) RETURN expression

Beat Signer - Department of Computer Science - [email protected] 34 March 20, 2019 SQL/PSM Statements ... ▪ Local variable declaration ▪ Assignments ▪ NULL is a permissible expression ▪ Compound statement DECLARE name type SET variable = expression BEGIN ... END

Beat Signer - Department of Computer Science - [email protected] 35 March 20, 2019 Branch Statement ▪ If statement ▪ ELSEIFs and final ELSE are optional IF condition THEN statements ELSEIF condition THEN statements ELSE statements ENDIF

Beat Signer - Department of Computer Science - [email protected] 36 March 20, 2019 Branch Example CREATE FUNCTION IsExpensive( IN cdTitle CHAR(30) ) RETURNS BOOLEAN IF NOT EXISTS(SELECT * FROM CD WHERE title = cdTitle) THEN RETURN FALSE; ELSEIF 50 < (SELECT MAX(price) FROM CD WHERE title = cdTitle) THEN RETURN TRUE; ELSE RETURN FALSE; ENDIF;

Beat Signer - Department of Computer Science - [email protected] 37 March 20, 2019 Loop Statements ▪ Basic loop statement ▪ The loop can be terminated with LEAVE label ▪ the label identifies which loop has to be aborted label: LOOP statements END LOOP

Beat Signer - Department of Computer Science - [email protected] 38 March 20, 2019 Loop Statements ... ▪ While loop statement ▪ Repeat loop statement WHILE condition DO statements END WHILE REPEAT statements UNTIL condition END REPEAT

Beat Signer - Department of Computer Science - [email protected] 39 March 20, 2019 Loop Statements ... ▪ For loop statement ▪ Convenience statement to iterate over cursor ▪ open / close cursor ▪ check if more tuples to be fetched ▪ Name and cursor are needed for transformation into basic loop (by PSM interpreter) FOR name AS cursor CURSOR FOR query DO statements END FOR

Beat Signer - Department of Computer Science - [email protected] 40 March 20, 2019 Loop Example CREATE FUNCTION LengthVarianceOfProducer( IN name VARCHAR(40), ) RETURNS REAL DECLARE variance REAL; DECLARE mean REAL; DECLARE count INTEGER; BEGIN SET variance = 0.0; SET mean = 0.0; SET count = 0; FOR cdLoop AS cdCursor CURSOR FOR SELECT length FROM CD WHERE producer = name; DO SET count = count + 1; SET variance = variance + length * length; SET mean = mean + length; END FOR; SET mean = mean/count; RETURN variance/count – mean * mean; END;

Beat Signer - Department of Computer Science - [email protected] 41 March 20, 2019 SQL/PSM Summary ▪ Perform computation on the server side ▪ Implement functionality not contained in SQL ▪ recursion ▪ closure ▪ mathematical and statistical functions ▪ SQL in combination with SQL/PSM forms a Turing complete programming language ▪ Functionality for administrative tasks is often implemented as PSM

Beat Signer - Department of Computer Science - [email protected] 42 March 20, 2019 Transitive Closure (Recursion) ▪ Inconvenient to specify transitive closure using iteration ▪ Since SQL:1999 there is a form of recursion using the WITH RECURSIVE clause ▪ Example ▪ "Find all destinations that can be reached (directly or indirectly) from Zurich Airport" from to ZRH BRU ZRH JFK BRU CDG JFK BRU ... ... Flight

Beat Signer - Department of Computer Science - [email protected] 43 March 20, 2019 Transitive Closure (Recursion) ... ▪ Recursive view is a union of two subqueries ▪ base query ▪ recursive query making use of the recursive view - recursive query should be monotonic (e.g. no aggregation in recursive view) ▪ Repeat until no new tuples added and fixpoint is reached WITH RECURSIVE Connection(from, to) AS ( SELECT from, to FROM Flight UNION SELECT Flight.from, Connection.to FROM Flight, Connection WHERE Flight.to = Connection.from ) SELECT DISTINCT to FROM Connection WHERE Connection.from = 'ZRH';

Beat Signer - Department of Computer Science - [email protected] 44 March 20, 2019 Triggers ▪ A trigger is a statement that the DBMS executes as a side effect of a modification to the database ▪ The definition of a trigger follows the so-called event-condition-action (ECA) model ▪ event - specifies the signal that triggers the rule (e.g. insert, update or delete) ▪ condition - the condition that has to be satisfied for the execution of the action ▪ action - the action to be executed ▪ Triggers are a powerful tool for checks before/after an operation on a relation (e.g. for integrity constraints)

Beat Signer - Department of Computer Science - [email protected] 45 March 20, 2019 Triggers ... ▪ Example ▪ When data is restored from a backup or replicated, triggers often have to be disabled ▪ otherwise triggers might be executed multiple times ▪ The execution of a trigger can lead to a (infinite) cascade of other triggers that get fired ▪ often the length of such a trigger chain is limited by the system CREATE TRIGGER setNull BEFORE UPDATE ON Producer REFERENCING NEW ROW AS newRow FOR EACH ROW WHEN newRow.address = '' SET newRow.address = NULL;

Beat Signer - Department of Computer Science - [email protected] 46 March 20, 2019 Homework ▪ Study the following chapter of the Database System Concepts book ▪ chapter 5 - sections 5.1-5.4, 5.7 - Advanced SQL

Beat Signer - Department of Computer Science - [email protected] 47 March 20, 2019 Exercise 6 ▪ Structured Query Language (SQL) ▪

Beat Signer - Department of Computer Science - [email protected] 48 March 20, 2019 References ▪ A. Silberschatz, H. Korth and S. Sudarshan, Database System Concepts (Sixth Edition), McGraw-Hill, 2010 ▪ H. Garcia-Molina, J.D. Ullman and J. Widom, Database Systems: The Complete Book, Prentice Hall, 2002

2 December 2005 Next Lecture DBMS Architectures and Features