Advanced Database Constraints & Security Measures in SQL

1. Chapter 4~5Intermediate/Advanced SQL 1st Semester, 2016 Sanghyun Park

2. Outline • Integrity Constraints • Domain constraints • Referential integrity • Assertions • Triggers • Security and Authorization • Embedded SQL • Dynamic SQL • ODBC • JDBC

3. Domain Constraints (1/2) • Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the databasedo not result in a loss of data consistency • Domain constraints are the most elementary form of integrity constraint • They test values inserted into the database,and test queries to ensure that the comparisons make sense • New domains can be created from existing data types:create domainDollarsnumeric (12,2);create domainPoundsnumeric (12,2); • We cannot assign or compare a value of type Dollars to a value of type Pounds

4. Domain Constraints (2/2) • The check clause in SQL-92 permits domains to be restricted:createdomainYearlySalary numeric(8,2)constraintsalary_value_testcheck (value >= 29000.00) • The domain has a constraintthat ensures that the YearlySalary is greater than or equal to 29000.00 • The clause constraintsalary_value_test is optional;useful to indicate which constraint an update violated • Can be restricted to contain only a specified set of values:createdomaindegree_level varchar(10)constraintdegree_level_testcheck (valuein (‘Bachelors’, ‘Masters’, ‘Doctorate’))

5. Referential Integrity (1/2) • 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 • If “Comp. Sci.” is a department name appearing in one of the tuples in the instructor relation, then there exists a tuple in the department relation for department “Comp. Sci.”

6. Referential Integrity (2/2) • Let r1(R1) and r2(R2) be relations with primary keys K1 and K2 respectively • The subset  of R2 is a foreign key referencing K1 in relation r1,if for every t2 in r2 there must be a tuple t1 in r1 such that t1[K1] = t2[] • Referential integrity constraint is also called subset dependencysince it can be written as:  (r2)  K1 (r1)

7. Referential Integrity in SQL create table instructor ( ID varchar(5),name varchar(20),dept_name varchar(20),salary numeric(8,2) check (salary > 29000), primary key (ID),foreign key (dept_name) references department ) create table department ( dept_name varchar(20),building varchar(15),budget numeric(12,2) check (budget > 0),primary key (dept_name) )

8. Assertions • An assertion is a predicate expressing a condition that we wish the database always to satisfy • An assertion in SQL takes the form:createassertion <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 • This testing may introduce a significant amount of overhead;hence assertions should be used with great care

9. Triggers (1/2) • 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 • Triggering events can be insert, delete, or update

10. Triggers (2/2) • Triggers on update can be restricted to specific attributes • Values of attributes before and after an update can be referenced: create triggersetnull-trigger before update onr referencing new row asnrow for each rowwhennrow.phone-number = ‘’setnrow.phone-number = null

11. Security And Authorization • Protection from malicious attempts to steal or modify data • Authentication and authorization mechanisms to allow specific users access only to required data • Forms of authorization: • read, insert, update, delete • index, resources, alteration, drop • The grant statement is used to confer authorization: grant <privilege list> on <relation name or view name> to <user-list>

12. Embedded SQL (1/4) • The SQL standard defines embeddings of SQL in a variety of programming languages such as Pascal, PL/I, C, and Cobol • A language to which SQL queries are embedded is referred to as a hostlanguage, and the SQL structures permitted in the host language constitute embeddedSQL • The basic form of these languagesfollows that of the System R embedding of SQL into PL/I • EXEC SQL statement is used to identify embedded SQL request to the preprocessor: EXEC SQL <embedded SQL statement > END-EXEC

13. Embedded SQL (2/4) • Specify the query in SQL and declare a cursor for itEXEC SQLdeclare c cursor for select ID, namefrom studentwhere tot_cred > :credit_amount;END-EXEC From within a host language, find the IDs and names of all students who have taken more than credit_amount credit hours

14. Embedded SQL (3/4) • The open statement causes the query to be evaluated EXEC SQL opencEND-EXEC • The fetch statement causes the values of one tuple in the query result to be placed on host language variables EXEC SQL fetch c into :si, :sn END-EXEC • Repeated calls to fetch get successive tuples • A variable called SQLSTATE in the SQL communication area (SQLCA) gets ‘02000’ to indicate that 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

15. Embedded SQL (4/4) • Can update tuples fetched by cursor by declaring that the cursor is for updatedeclare c cursor forselect *from instructorwhere dept_name = ‘Music’for update • To update tuple at the current location of cursorupdate instructorsetsalary = salary + 100where current of c

16. ODBC (1/6) • Open DataBase Connectivity (ODBC) standard • Standard for application program to communicate with a database • Application program interface (API) to • Open a connection with a database • Send queries and updates • Get results back • Applications such as GUI, spreadsheets, etc. can use ODBC

17. ODBC (2/6) • Each database system supporting ODBC provides a “driver” library that must be linked with the client program • When 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 • ODBC program first allocates an SQL environment, then a database connection handle • Opens database connection using SQLConnect() • Parameters for SQLConnect: • Connection handle • The server to which to connect • The user identifier, password • Must also specify types of arguments: • SQL_NTS denotes that argument is a null-terminated string

18. ODBC (3/6) int ODBCexample() { RETCODE error; HENV env; /* environment */ HDBC conn; /* database connection */ SQLAllocEnv(&env); SQLAllocConnect(env, &conn); SQLConnect(conn,“db.yale.edu", SQL_NTS,"avi", SQL_NTS,"avipasswd", SQL_NTS); { …. Do actual work … } SQLDisconnect(conn); SQLFreeConnect(conn); SQLFreeEnv(env); }

19. ODBC (4/6) • Program sends SQL commands to the database by using SQLExecDirect • SQLBindCol() binds C language variables to attributes of the query result. Arguments to SQLBindCol(): • ODBC stmt variable, attribute position in query result • The type conversion from SQL to C • The address of the variable • For variable-length types like character arrays,the maximum length of the variable andlocation to store actual length when a tuple is fetched • Result tuples are fetched using SQLFetch() • Good programming requires checking results of every function call for errors; we have omitted most checks for brevity

20. ODBC (5/6) • Main body of programchar deptname[80];float salary;int lenOut1, lenOut2;HSTMT stmt; char * sqlquery = “select dept_name, sum (salary) from instructor group by dept_name”; SQLAllocStmt(conn, &stmt); error = SQLExecDirect(stmt, sqlquery, SQL_NTS); if (error == SQL_SUCCESS) { SQLBindCol(stmt, 1, SQL_C_CHAR, deptname, 80, &lenOut1); SQLBindCol(stmt, 2, SQL_C_FLOAT, &salary, 0 , &lenOut2); while (SQLFetch(stmt) == SQL_SUCCESS) { printf (“ %s %g\n”, deptname, salary); }}SQLFreeStmt(stmt, SQL_DROP);

21. ODBC (6/6) • Prepared Statement • SQL statement prepared: compiled at the database • Can have placeholders: E.g. insert into department values(?,?,?) • Repeatedly executed with actual values for the placeholders • Metadata features • Finding all the relations in the database and • Finding the names and types of columns of a query result or a relation in the database • By default, each SQL statement is treated as a separate transaction that is committed automatically • Can turn off automatic commit on a connection • SQLSetConnectOption(conn, SQL_AUTOCOMMIT, 0) • Transactions must then be committed or rolled back explicitly by • SQLTransact(conn, SQL_COMMIT) or • SQLTransact(conn, SQL_ROLLBACK)

22. JDBC (1/4) • JDBC is 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 • Create a “statement” object • Execute queries using the Statement object to send queries and fetch results • Exception mechanism to handle errors

23. JDBC (2/4) public static void JDBCexample(String dbid, String userid, String passwd) { try { Class.forName (“oracle.jdbc.driver.OracleDriver”); Connection conn = DriverManager.getConnection(“jdbc:oracle:thin:@db.yale.edu:1521:univdb”, userid, passwd); Statement stmt = conn.createStatement(); … Do Actual Work …. stmt.close(); conn.close(); } catch (SQLException sqle) { System.out.println(“SQLException : ” + sqle); } }

24. JDBC (3/4) • Update to database try { stmt.executeUpdate(“insert into instructor values (‘77987’, ‘Kim’, ‘Physics’, 98000)”); } catch (SQLException sqle) { System.out.println(“Could not insert tuple. “ + sqle); } • Execute query and fetch and print results ResultSet rset = stmt.executeQuery( “select dept_name, avg(salary) from instructor group by dept_name”); while (rset.next()) { System.out.println(rset.getString(“dept_name”) + “ ” + rset.getFloat(2)); }

25. JDBC (4/4) • Prepared statement allows queries to be compiled once and executed multiple times with different arguments PreparedStatement pStmt = conn.prepareStatement( “insert into instructor values(?,?,?,?)”); pStmt.setString(1, “88877”); pStmt.setString(2, “Perry”);pStmt.setString(3, “Finance”); pStmt.setInt(4, 125000); pStmt.executeUpdate(); pStmt.setString(1, “88878”);pStmt.executeUpdate();