Transcript dept_name
Chapter 4~5
Intermediate/Advanced SQL
1st Semester, 2016
Sanghyun Park
Outline
Integrity Constraints
Domain constraints
Referential integrity
Assertions
Triggers
Security and Authorization
Embedded SQL
Dynamic SQL
ODBC
JDBC
Domain Constraints (1/2)
Integrity constraints guard against accidental damage to the
database, by ensuring that authorized changes to the database
do 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 domain Dollars numeric (12,2);
create domain Pounds numeric (12,2);
We cannot assign or compare a value of type Dollars to a value of
type Pounds
Domain Constraints (2/2)
The check clause in SQL-92 permits domains to be restricted:
create domain YearlySalary numeric(8,2)
constraint salary_value_test check (value >= 29000.00)
The domain has a constraint
that ensures that the YearlySalary is greater than or equal to 29000.00
The clause constraint salary_value_test is optional;
useful to indicate which constraint an update violated
Can be restricted to contain only a specified set of values:
create domain degree_level varchar(10)
constraint degree_level_test
check (value in (‘Bachelors’, ‘Masters’, ‘Doctorate’))
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.”
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 dependency
since it can be written as:
(r2) K1 (r1)
Referential Integrity in SQL
create table instructor (
ID
name
dept_name
salary
primary key
foreign key
)
varchar(5),
varchar(20),
varchar(20),
numeric(8,2) check (salary > 29000),
(ID),
(dept_name) references department
create table department (
dept_name
building
budget
primary key
)
varchar(20),
varchar(15),
numeric(12,2) check (budget > 0),
(dept_name)
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 <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
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
Triggers (2/2)
Triggers on update can be restricted to specific attributes
Values of attributes before and after an update can be referenced:
create trigger setnull-trigger
before update on r
referencing new row as nrow
for each row
when nrow.phone-number = ‘’
set nrow.phone-number = null
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>
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
host language, and the SQL structures permitted in the host
language constitute embedded SQL
The basic form of these languages
follows 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
Embedded SQL (2/4)
From within a host language, find the IDs and names of all
students who have taken more than credit_amount credit
hours
Specify the query in SQL and declare a cursor for it
EXEC SQL
declare c cursor for
select ID, name
from student
where tot_cred > :credit_amount;
END-EXEC
Embedded SQL (3/4)
The open statement causes the query to be evaluated
EXEC SQL open c END-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 close c END-EXEC
Embedded SQL (4/4)
Can update tuples fetched by cursor by declaring that the cursor is
for update
declare
select
from
where
for update
c cursor for
*
instructor
dept_name = ‘Music’
To update tuple at the current location of cursor
update
set
where
instructor
salary = salary + 100
current of c
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
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
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);
}
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 and
location 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
ODBC (5/6)
Main body of program
char 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);
ODBC (6/6)
Prepared Statement
Metadata features
SQL statement prepared: compiled at the database
Can have placeholders: E.g. insert into department values(?,?,?)
Repeatedly executed with actual values for the placeholders
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)
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
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);
}
}
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));
}
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();