CS443443G Database Management System

1
CS443/443G Database Management System

• Assertions, Triggers and Views
• SQL Programming
• Instructor Dr. Huanjing Wang

Slides Courtesy of R. Elmasri and S. B. Navathe
2
Constraints as Assertions

• General constraints constraints that do not fit
  in the basic SQL categories (presented in chapter
  8)
• Mechanism CREAT ASSERTION
• Components include
• a constraint name,
• followed by CHECK,
• followed by a condition

3
Assertions An Example

• The salary of an employee must not be greater
  than the salary of the manager of the department
  that the employee works for
• CREAT ASSERTION SALARY_CONSTRAINT
• CHECK (NOT EXISTS (SELECT
• FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
• WHERE E.SALARY gt M.SALARY AND
• E.DNOD.NUMBER AND D.MGRSSNM.SSN))

4
Using General Assertions

• Specify a query that violates the condition
  include inside a NOT EXISTS clause
• Query result must be empty
• if the query result is not empty, the assertion
  has been violated

5
SQL Triggers

• Objective to monitor a database and take
  initiate action when a condition occurs
• Triggers are expressed in a syntax similar to
  assertions and include the following
• Event
• Such as an insert, deleted, or update operation
• Condition
• Action
• To be taken when the condition is satisfied

6
SQL Triggers An Example

• A trigger to compare an employees salary to
  his/her supervisor during insert or update
  operations
• CREATE TRIGGER INFORM_SUPERVISOR
• BEFORE INSERT OR UPDATE OF
• SALARY, SUPERVISOR_SSN ON EMPLOYEE
• FOR EACH ROW
• WHEN
• (NEW.SALARYgt (SELECT SALARY FROM EMPLOYEE WHERE
  SSNNEW.SUPERVISOR_SSN))
• INFORM_SUPERVISOR NEW.SUPERVISOR_SSN,NEW.S
  SN)

7
Views in SQL

• A view is a virtual table that is derived from
  other tables
• Allows for limited update operations
• Since the table may not physically be stored
• Allows full query operations
• A convenience for expressing certain operations

8
Specification of Views

• SQL command CREATE VIEW
• a table (view) name
• a possible list of attribute names (for example,
  when arithmetic operations are specified or when
  we want the names to be different from the
  attributes in the base relations)
• a query to specify the table contents

9
SQL Views An Example

• Specify a different WORKS_ON table
• CREATE VIEW WORKS_ON_NEW AS
• SELECT FNAME, LNAME, PNAME, HOURS
• FROM EMPLOYEE, PROJECT, WORKS_ON
• WHERE SSNESSN AND PNOPNUMBER
• GROUP BY PNAME

10
Using a Virtual Table

• We can specify SQL queries on a newly create
  table (view)
• SELECT FNAME, LNAME
• FROM WORKS_ON_NEW
• WHERE PNAMESeena
• When no longer needed, a view can be dropped
• DROP WORKS_ON_NEW

11

• Chapter 9
• Introduction to SQL Programming Techniques

12
SQL in Real Programs

• We have seen only how SQL is used at the generic
  query interface --- an environment where we sit
  at a terminal and ask queries of a database.
• Reality is almost always different.
• Programs in a conventional language like C are
  written to access a database by calls to SQL
  statements.

13
Host Languages

• Any conventional language can be a host language,
  that is, a language in which SQL calls are
  embedded.
• The use of a host/SQL combination allows us to do
  anything computable, yet still get the
  very-high-level SQL interface to the database.

14
Connecting SQL to the Host Language

• Embedded SQL is a standard for combining SQL
  with several languages.
• CLI (Call-Level Interface ) is a different
  approach to connecting C to an SQL database.
• JDBC (Java Database Connectivity ) is a way to
  connect Java with an SQL database.

15
Embedded SQL

• Key idea Use a preprocessor to turn SQL
  statements into procedure calls that fit with the
  host-language code surrounding.
• All embedded SQL statements begin with EXEC SQL,
  so the preprocessor can find them easily.

16
SQL/CLI

• Instead of using a preprocessor, we can use a
  library of functions and call them as part of an
  ordinary C program.
• The library for C is called SQL/CLI Call-Level
  Interface.

17
JDBC

• Java Database Connectivity (JDBC) is a library
  similar to SQL/CLI, but with Java as the host
  language.
• JDBC/CLI differences are often related to the
  object-oriented style of Java.

18
JDBC/ODBC Overview

• ODBC and JDBC
• Open Database Connectivity (ODBC) is a standard
  software API for connecting to database
  management systems (DBMS)
• JDBC is a Java API which provides classes and
  methods to easily interact with all kinds of data
  sources

19
Basic Process of Using JDBC

• Load JDBC driver and establish connection to
  database
• Prepare SQL statement
• Execute the statement and process results
• Close connection
• Note wrap these statements in trycatch block
  since many of these statements require exception
  handling
• ClassNotFoundException
• SQLException

20
Database Connection

• Load/register JDBC-ODBC driver
• Class.forName ("com.mysql.jdbc.Driver")
• Create a connection (object)
• Connection conn DriverManager.getConnection
  ("jdbcmysql//localhost3306/company",
  "username", "password")

21
Preparing and Executing SQL

• Use the connection object to create a statement
  object
• Statement stmt conn.createStatement ()
• Prepare a SQL query string and execute the
  statement
• ResultSet rset stmt.executeQuery (select ssn
  from employee")
• Use a ResultSet object to accept the retrieved
  data
• ResultSet represents retrieved data as a data
  table

22
ResultSet Processing

• ResultSet object (table) maintains a cursor
  pointing to only one row at a time.
• By default, ResultSet is forward only and read
  only
• ResultSet type is determined when creating
  Statement object
• Read data by moving the cursor
• A while loop can be used to traverse all rows
• Initially (when the ResultSet object is
  successfully created) the cursor is positioned
  before the first row
• Use the next() method to move to the next row
• When it moves after the last row, the method
  returns false

23
Getting Data from ResultSet

• Get row number (row starts from 1)
• int rowNumberrs.getRow()
• Get field values using getter methods
• rs.getString(String columnName) or
• rs.getString(int columnNumber)
• Column number starts from 1
• Usually used when a column is not explicitly
  named
• Note the get method can be used only once for
  the same field

24
Getting Data from ResultSet

• while (rset.next ())
• system.out.println (rset.getString (ssn"))

25
Last Step

• Close the connection the connection when all
  database operations are performed
• This will also close all statement and result set
  objects
• conn.close()
• It is a good habit to close connection explicitly
  for performance reasons

26
Exercise

• Write a program to perform the following
  operations on the company database using JDBC.
  The program will go over the employee table.
  Retrieve the names of employees and salary in
  department 5.