1 / 24

Chapter 4~5 Intermediate/Advanced SQL

Chapter 4~5 Intermediate/Advanced SQL. Yonsei University 1 st Semester, 2013 Sanghyun Park. Outline. Integrity Constraints Domain constraints Referential integrity Assertions Triggers Security and Authorization Embedded SQL Dynamic SQL ODBC JDBC. Domain Constraints (1/2).

benito
Download Presentation

Chapter 4~5 Intermediate/Advanced SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4~5Intermediate/Advanced SQL Yonsei University 1st Semester, 2013 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 • 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.” • Formal definition • 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)

  6. 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) )

  7. Assertions • An assertion is a predicate expressing a conditionthat 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

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

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

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

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

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

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

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

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

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

  17. 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); }

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

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

  20. 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)

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

  22. 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); } }

  23. 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)); }

  24. 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();

More Related