DBMS Module 4 (Chap 1)

Download as pdf or txt
Download as pdf or txt
You are on page 1of 37

MODULE –IV NOTES

Chapter-1
SQL : Advanced Queries
This Module describes more advanced features of the SQL language standard for relational databases.

5.1 More Complex SQL Retrieval Queries


we described some basic types of retrieval queries in SQL.Because of the generality and expressive power of the
language, there are many additional features that allow users to specify more complex retrievals from the database.

5.1.1 Comparisons Involving NULL and Three-Valued Logic


SQL has various rules for dealing with NULL values.NULLis used to represent a missing value,but that it usually
has one of three different interpretations—value unknown (exists but is not known),value not available (exists but is
purposely withheld),or value not applicable (the attribute is undefined for this tuple).
Consider the following examples to illustrate each of the meanings of NULL.
1. Unknown value. A person’s date of birth is not known, so it is represented by NULLin the database.
2. Unavailable or withheld value. A person has a home phone but does not want it to be listed,so it is withheld and
represented as NULLin the database.
3. Not applicable attribute. An attribute LastCollegeDegree would be NULLfor a person who has no college
degrees because it does not apply to that person.
It is often not possible to determine which of the meanings is intended;for example, a NULLfor the home
phone of a person can have any of the three meanings.Hence, SQL does not distinguish between the different
meanings of NULL.
In general,each individual NULLvalue is considered to be different from every other NULLvalue in the
various database records.When a NULLis involved in a comparison operation, the result is considered to be
UNKNOWN (it may be TRUE or it may be FALSE). Hence, SQL uses a three-valued logic with values TRUE,
FALSE, and UNKNOWNinstead of the standard two-valued (Boolean) logic with values TRUEorFALSE.It is
therefore necessary to define the results (or truth values) of three-valued logical expressions when the logical
connectives AND, OR, and NOT are used. Table 5.1 shows the resulting values.

In Tables 5.1(a) and 5.1(b),the rows and columns represent the values of the results of comparison
conditions,which would typically appear in the WHERE clause of an SQL query. Each expression result would have
a value of TRUE, FALSE, or UNKNOWN. The result of combining the two values using the AND logical
connective is shown by the entries in Table 5.1(a).Table 5.1(b)shows the result of using the

Page 1
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
ORlogicalconnective.Forexample,the result of (FALSE AND UNKNOWN) is FALSE, whereas the result of
(FALSE OR UNKNOWN) is UNKNOWN.Table 5.1(c) shows the result of the NOT logical operation. Notice that
in standard Boolean logic, only TRUEor FALSE values are permitted; there is no UNKNOWNvalue.
SQL allows queries that check whether an attribute value is NULL.Rather than using = or <> to compare an
attribute value to NULL,SQL uses the comparison operators ISor IS NOT.This is because SQL considers each
NULLvalue as being distinct from every other NULL value, so equality comparison is not appropriate.
Query 18. Retrieve the names of all employees who do not have supervisors.
SELECT Fname,Lname
FROM EMPLOYEE WHERE Super_ssnIS NULL;

5.1.2 Nested Queries, Tuples, and Set/Multiset Comparisons


Some queries require that existing values in the database be fetched and then used in a comparison
condition. Such queries can be conveniently formulated by using nested queries, which are complete select-from-
where blocks within the WHERE clause of another query. That other query is called the outer query.

IN operator:
whichis a comparison operator that compares a value v with a set (or multiset) of values V and evaluates to
TRUE if v is one of the elements in V.
ex: Query 17. Retrieve the Social Security numbers of all employees who work on project numbers
1,2,or 3.

SELECT DISTINCTEssn
FROM WORKS_ON WHEREPnoIN (1, 2, 3);

SQL allows the use of tuples of values in comparisons by placing them within parentheses.To illustrate
this,consider the following query:
SELECT DISTINCT Essn
FROM WORKS_ON WHERE (Pno, Hours) IN( SELECTPno, Hours FROM WORKS_ON
WHERE Essn=‘123456789’);
This query will select the Essns of all employees who work the same (project,hours) combination on some
project that employee ‘John Smith’ (whose Ssn = ‘123456789’) works on. In this example, the IN operator
compares the subtuple of values in parentheses (Pno, Hours) within each tuple in WORKS_ON with the set of type-
compatible tuples produced by the nested query.
In addition to the INoperator,a number of other comparison operators can be used to compare a single value
v (typically an attribute name) to a set or multiset v (typically a nested query).The = ANY (or = SOME) operator
returns TRUEif the value v is equal to some value in the set V and is hence equivalent to IN. The two keywords
ANY and SOME have the same effect. Other operators that can be combined with ANY (or SOME) include >, >=,
<, <=, and <>. The keyword ALL can also be combined with each of these operators.For example,the comparison
condition (v>ALL V) returns TRUEif the value vis greater than allthe values in the set (or multiset) V.
An example is the following query, which returns the names of employees whose salary is greater than the
salary of all the employees in department 5:
SELECT Lname,Fname
FROM EMPLOYEE WHERE Salary > ALL( SELECT Salary FROM EMPLOYEE
WHERE Dno=5 );

Page 2
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

5.1.3 Correlated Nested Queries

Whenever a condition in the WHEREclause of a nested query references some attribute of a relation
declared in the outer query,the two queries are said to be correlated. We can understand a correlated query better
by considering that the nested query is evaluated once for each tuple (or combination of tuples) in the outer query.
Example for Correlated Nested Querie:
Retrieve the name of each employee who has a dependent with the same first name and is the same sex as
the employee.
Q16: SELECT E.Fname, E.Lname FROM EMPLOYEE AS E
WHERE E.Ssn IN ( SELECTEssn FROM DEPENDENT AS D
WHERE E.Fname=D.Dependent_name AND E.Sex=D.Sex );

For example, we can think of Q16 as follows: For each EMPLOYEE tuple, evaluate the nested query, which
retrieves the Essn values for all DEPENDENT tuples with the same sex and name as that EMPLOYEEtuple; if the
Ssn value of the EMPLOYEEtuple is in the result of the nested query,then select that EMPLOYEEtuple.
In generala query written with nested select-from-where blocks and using the = or INcomparison operators
can always be expressed as a single block query.For example,Q16 may be written as in Q16A:
Q16A:SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E, DEPENDENT AS D
WHERE E.Ssn=D.Essn AND E.Sex=D.Sex AND E.Fname=D.Dependent_name;

5.1.4 The EXISTS and UNIQUE Functions in SQL

Page 3
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

EXISTS and NOT EXISTS are typically used in conjunction with a correlated nested query.In Q16B,the
nested query references the Ssn,Fname,andSexattributes of the EMPLOYEErelation from the outer query.We can
think of Q16Bas follows:For each EMPLOYEE tuple, evaluate the nested query, which retrieves all DEPENDENT
tuples with the same Essn,Sex,andDependent_name as the EMPLOYEEtuple; if at least one tuple EXISTS in the
result of the nested query, then select that EMPLOYEE tuple.
In general, EXISTS(Q) returns TRUE if there is at least one tuple in the result of the nested query Q,and it
returns FALSE otherwise.
On the other hand,NOT EXISTS(Q) returns TRUE if there are no tuples in the result of nested query Q,
and it returns FALSE otherwise.Next,we illustrate the use of NOT EXISTS.
Query 6.Retrieve the names of employees who have no dependents.
SELECT Fname,Lname
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT * FROM DEPENDENT
WHERE Ssn=Essn );

In Q6, the correlated nested query retrieves all DEPENDENTtuples related to a particular EMPLOYEE
tuple. If none exist, the EMPLOYEE tuple is selected because the WHERE-clause condition will evaluate to TRUE
in this case. We can explain Q6 as follows: For each EMPLOYEE tuple, the correlated nested query selects all
DEPENDENT tuples whose Essn value matches the EMPLOYEE Ssn; if the result isempty, no dependents are
related to the employee, so we select that EMPLOYEE tuple and retrieve its Fname and Lname.
5.1.5 Explicit Sets and Renaming of Attributes in SQL
Explicit Sets
We have seen several queries with a nested query in the WHEREclause.It is also possible to use an explicit
set of values in the WHERE clause, rather than a nested query.Such a set is enclosed in parentheses in SQL.
Query 17.Retrieve the Social Security numbers of all employees who work on project numbers 1,2,or
3.
SELECT DISTINCT Essn FROM WORKS_ON WHERE Pno IN (1, 2, 3);

Renaming of Attributes
In SQL,it is possible to rename any attribute that appears in the result of a query by adding the qualifier AS
followed by the desired new name. Hence, the AS construct can be used to alias both attribute and relation
names,and it can be used in both the SELECT and FROM clauses.

Page 4
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
For example, to retrieve the last name of each employee and his or her supervisor,while renaming the
resulting attribute names as Employee_name and Supervisor_name.The new names will appear as column headers
in the query result.
SELECT E.LnameASEmployee_name, S.LnameASSupervisor_name
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;

5.1.6 Joined Tables in SQL and Outer Joins


The concept of a joined table (or joined relation) was incorporated into SQL to permit users to specify a
table resulting from a join operation in the FROM clause of a query. This construct may be easier to comprehend
than mixing together all the select and join conditions in the WHERE clause.
For example, consider query Q1, which retrieves the name and address of every employee who works for
the ‘Research’ department. It may be easier to specify the join of the EMPLOYEE and DEPARTMENT relations
first, and then to select the desired tuples and attributes. This can be written in SQL as in Q1A:
Q1A: SELECT Fname,Lname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)
WHEREDname=‘Research’;
TheFROMclause in Q1Acontains a single joined table.The attributes of such a table are all the attributes of
the first table, EMPLOYEE, followed by all the attributes of the second table,DEPARTMENT.
The concept of a joined table also allows the user to specify different types of join, such as NATURAL
JOIN and various types of OUTER JOIN.
In a NATURAL JOIN on two relations RandS,no join condition is specified;an implicit EQUIJOIN
condition for each pair of attributes with the same name from R and S is created. Each such pair of attributes is
included only once in the resulting relation.
If the names of the join attributes are not the same in the base relations, it is possible to rename the attributes
so that they match,and then to apply NATURAL JOIN.In this case, the AS construct can be used to rename a
relation and all its attributes in the FROM clause. This is illustrated in Q1B, where the DEPARTMENT relation is
renamed as DEPT and its attributes are renamed as Dname, Dno (to match the name of the desired join attribute
Dno in the EMPLOYEE table), Mssn, and Msdate. The implied join condition for this NATURAL JOIN is
EMPLOYEE.Dno=DEPT.Dno, because this is the only pair of attributes with the same name after renaming:
Q1B: SELECT Fname, Lname, Address FROM
(EMPLOYEE NATURAL JOIN (DEPARTMENT AS DEPT (Dname, Dno, Mssn,Msdate)))
WHERE Dname=‘Research’;

The default type of join in a joined table is called an inner join, where a tuple is included in the result only
if a matching tuple exists in the other relation
There are a variety of outer join operations.
1) LEFT OUTER JOIN (every tuple in the left table must appear in the result;if it does not have a
matching tuple,it is padded with NULLvalues for the attributes of the right table).
2) RIGHT OUTER JOIN (every tuple in the right table must appear in the result;if it does not
have a matching tuple,it is padded with NULLvalues for the attributes of the left table).
3) FULL OUTER JOIN :It is a combination of left and right outer joins .
In the latter three options, the keyword OUTER may be omitted.If the join attributes have the same
name,one can also specify the natural join variation of outer joins by using the keyword NATURAL before
the operation (for example, NATURAL LEFT OUTER JOIN).
The keyword CROSS JOIN is used to specify the CARTESIAN PRODUCT operationalthough this
should be used only with the utmost care because it generates all possible tuple combinations.
It is also possible to nest join specifications; that is, one of the tables in a join may itself be a joined table.
This allows the specification of the join of three or more tables as a single joined table,which is called a multiway
join.
EX: SELECT Pnumber, Dnum, Lname, Address, Bdate
Page 5
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
FROM ((PROJECT JOIN DEPARTMENT ON Dnum=Dnumber)
JOIN EMPLOYEE ON Mgr_ssn=Ssn) WHERE Plocation=‘Stafford’;

Not all SQL implementations have implemented the new syntax of joined tables.In some systems, a different syntax
was used to specify outer joins by using the comparison operators +=, =+, and +=+ for left, right, and full outer
join, respectively, when specifying the join condition. For example, this syntax is available in Oracle. To specify
the left outer join using this syntax, we could write the query as follows:
SELECT E.Lname, S.Lname
FROM EMPLOYEE E, EMPLOYEE S
WHERE E.Super_ssn +=S.Ssn;

5.1.7 Aggregate Functions in SQL


Aggregate functions are used to summarize information from multiple tuples into a single-tuple summary.
Grouping is used to create subgroups of tuples before summarization. Grouping and aggregation are required in
many database applications, and we will introduce their use in SQL through examples.
A number of built-in aggregate functions exist: COUNT, SUM, MAX, MIN, and AVG.The COUNT
function returns the number of tuples or values as specified in aquery.The functions SUM,MAX,MIN,and AVG can
be applied to a set or multiset of numeric values and return,respectively,the sum,maximum value,minimum value,
and average (mean) of those values.
Query 19. Find the sum of the salaries of all employees,the maximum salary, the minimum salary,and
the average salary.
SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary)
FROM EMPLOYEE;

If we want to get the preceding function values for employees of a specific department—say, the ‘Research’
department—we can write Query 20, where the EMPLOYEEtuples are restricted by the WHERE clause to those
employees who work for the ‘Research’department.
Query 20. Find the sum of the salaries of all employees of the ‘Research’ department,as well as the
maximum salary,the minimum salary,and the average salary in this department.
SELECT SUM (Salary), MAX (Salary), MIN (Salary), AVG (Salary)
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)
WHERE Dname=‘Research’;

Queries 21 and 22. Retrieve the total number of employees in the company (Q21) and the number of
employees in the ‘Research’department (Q22).

Page 6
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

will not be counted. In


general, NULL values are discarded when aggregate functions are applied to a particular column (attribute).

5.1.8 Grouping: The GROUP BY and HAVING Clauses


GROUP BY clause
SQL has a GROUP BY clause . The GROUP BYclause specifies the grouping attributes, which should also
appear in the SELECT clause, so that the value resulting from applying each aggregate function to a group of tuples
appears along with the value of the grouping attribute(s).
In many cases we want to apply the aggregate functions to subgroups of tuples in a relation, where the
subgroups are based on some attribute values. For example, we may want to find the average salary of employees in
each department or the number of employees who work on each project.In these cases we need to partition the
relation into nonoverlapping subsets (or groups) of tuples.Each group (partition) will consist of the tuples that have
the same value of some attribute(s), called the grouping attribute(s). We can then apply the function to each such
group independently to produce summary information about each group.

Page 7
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

HAVING clause
SQL provides aHAVING clause,which can appear in conjunction with a GROUP BY clause. HAVING provides a
condition on the summary information regarding the group of tuples associated with each value of the grouping
attributes. Only the groups that satisfy the condition are retrieved in the result of the query.This is illustrated by
Query 26.

Page 8
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

5.1.9 Discussion and Summary of SQL Queries

In order to formulate queries correctly, it is useful to consider the steps that define the meaning or semantics of each
query. A query is evaluated conceptually by first applying the FROMclause (to identify all tables involved in the
query or to materialize any joined tables), followed by the WHERE clause to select and join tuples, and then by
GROUP BY and HAVING. Conceptually, ORDER BY is applied at the end to sort the query result.

Page 9
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
5.2 Specifying Constraints as Assertions and Actions as Triggers
In this section, we introduce two additional features of SQL: the CREATE ASSERTION statement and the
CREATE TRIGGER statement.

CREATE ASSERTION, which can be used to specify additional types of constraints that are outside the
scope of the built-in relational model constraints (primary and unique keys, entity integrity, and referential integrity)
that we presented early.
CREATE TRIGGER,which can be used to specify automatic actions that the database system will perform
when certain events and conditions occur. This type of functionality is generally referred to as active databases.

5.2.1 Specifying General Constraints as Assertions in SQL


ASSERTIONS
In SQL,users can specify general constraints—those that do not fall into any of the categories described via
declarative assertions, using the CREATE ASSERTION statement of the DDL. Each assertion is given a constraint
name and is specified via a condition similar to the WHERE clause of an SQL query.

The basic technique for writing such assertions is to specify a query that selects any tuples that violate the desired
condition.By including this query inside a NOT EXISTSclause, the assertion will specify that the result of this query
must be empty so that the condition will always be TRUE.Thus,the assertion is violated if the result of the query is
not empty.In the preceding example,the query selects all employees whose salaries are greater than the salary of the
manager of their department. If the result of the query is not empty,the assertion is violated.

5.2.2 Introduction to Triggers in SQL


Another important statement in SQL is CREATE TRIGGER. In many cases it is convenient to specify the type of
action to be taken when certain events occur and when certain conditions are satisfied.For example,it may be useful
to specify a condition that, if violated, causes some user to be informed of the violation.The CREATE TRIGGER
statement is used to implement such actions in SQL.
A typical trigger has three components:

Page 10
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
 Event: When this event happens, the trigger is activated
 Condition (optional): If the condition is true, the trigger executes, otherwise skipped
 Action: The actions performed by the trigger
 The action is to be executed automatically if the condition is satisfied whenevent occurs.

Trigger: Events
Three event types
Insert
Update
Delete
Two triggering times
Before the event
After the event
Two granularities
Execute for each row
 Execute for each statement

Page 11
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

Trigger: Condition

Page 12
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

Trigger: Action

Example 1

Page 13
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
Example 2

5.3 Views (virtual table) in SQL


1.1 Concept of a View in SQL

 A view is a single table that is derived from one or more base tables or other views
 Views neither exist physically nor contain data itself, it depends on the base tables for its existence
 A view contains rows and columns, just like a real table. The fields in a view are fields from one or more
real tables in the database.

1.2 Specification of Views in SQL


Syntax:
CREATEVIEWview_nameAS
SELECTcolumn_name(s)
FROMtable_name
WHEREcondition
Example
CREATE VIEW WORKS_ON1
AS SELECT Fname, Lname, Pname, Hours
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn=Essn ANDPno=Pnumber ;
 We can specify SQL queries on view
Example #
 Retrieve the Last name and First name of all employees who work on ‘ProductX’
SELECT Fname, Lname
FROM WORKS_ON1
WHERE Pname=‘ProductX’ ;
 A view always shows up-to-date
 If we modify the tuples in the base tables on which the view is defined, the view must automatically
reflect these changes
 If we do not need a view any more, we can use the DROP VIEW command
DROP VIEW WORKS_ON1;

Page 14
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
1.3 View Implementation and View Update
View Implementation
 The problem of efficiently implementing a view for quering is complex
Two main approaches have been suggested

Query Modification

 Modifying the view query into a query on the underlying base tables
 Disadvantage: inefficient for views defined via complex queries that are time-consuming to execute ,
especially if multiple queries are applied to the view within a short period of time.

Example
 The query example# would be automatically modified to the following query by the DBMS
SELECT Fname, Lname
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn=Essn ANDPno=Pnumber
AND Pname=”ProductX’;
View Materialization
 Physically create a temporary view table when the view is first queried
 Keep that table on the assumption that other queries on the view will follow
 Requires efficient strategy for automatically updating the view table when the base tables are updated, that
is Incremental Update
 Incremental Update determines what new tuples must be inserted, deleted, or modified in a materialized
view table when a change is applied to one of the defining base table
View Update
 Updating of views is complicated and can be ambiguous
 An update on view defined on a single table without any aggregate functions can be mapped to an update on
the underlying base table under certain conditions.
 View involving joins, an update operation may be mapped to update operations on the underlying base
relations in multiple ways.
Example
 Update the Pname attribute of ‘john smith’ from ‘ProductX’ to ‘ProductY’
UPDATE WORKS_ON1
SET Pname= ‘ProductY’
WHERE Lname=‘smith’ ANDFname=‘john’
AND Pname= ‘ProductX’
 This query can be mapped into several updates on the base relations to give the desired effect on the view.
 Two possible updates (a) and (b) on the base relations corresponding to above query .
(a) UPDATE WORKS_ON
SET Pno= (SELECT Pnumber
FROM PROJECT
WHERE Pname= ‘ProductY’)
WHERE Essn IN (SELECT Ssn
FROM EMPLOYEE
WHERE Lname=‘smith’ ANDFname=‘john’)
AND
Pno= (SELECT Pnumber
FROM PROJECT
WHERE Pname=‘ProductX’ );

(b) UPDATE PROJECT


Page 15
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
SET Pname=‘ProductY’
WHERE Pname= ‘ProductX’ ;
 Update (a) relates ‘john smith’ to the ‘ProductY’ PROJECT tuple in place of the ‘ProductX’ PROJECT
tuple and is the most likely update.
 Update (b) would also give the desired update effect on the view, but it accomplishes this by changing the
name of the ‘ProductX’ tuple in the PROJECT relation to ‘ProductY’
OBSERVATIONS ON VIEWS
 A view with a single defining table is updatable if the view attributes contain the primary key of the base
relation, as well as all attributes with the NOT NULL constraint that do not have default values specified
 Views defined on multiple tables using joins are generally not updatable
 Views defined using grouping and aggregate functions are not updatable
 In SQL, the clause WITH HECK OPTION must be added at the end of the view definition if a view is to be
updated.
Advantages of Views
 Data independence
 Currency
 Improved security
 Reduced complexity
 Convenience
 Customization
 Data integrity

5.4 Schema Change Statements in SQL


In this section,we give an overview of the schema evolution commandsavailable in SQL, which can be used to alter
a schema by adding or dropping tables, attributes, constraints, and other schema elements. This can be done while
the database is operational and does not require recompilation of the database schema. Certain checks must be done
by the DBMS to ensure that the changes do not affect the rest of the database and make it inconsistent.

5,4.1The DROP Command


The DROP command can be used to drop named schema elements, such as tables, domains, or constraints.
One can also drop a schema. For example, if a whole schema is no longer needed, the DROP SCHEMA
command can be used. There are two drop behavior options: CASCADE and RESTRICT. For example, to remove
the COMPANY database schema and all its tables, domains, and other elements, the CASCADE option is used as
follows:

DROPSCHEMA COMPANY CASCADE;


If the RESTRICToption is chosen in place of CASCADE,the schema is dropped only if it has no elements in it;
otherwise, the DROP command will not be executed. To use the RESTRICT option, the user must first individually
drop each element in the schema,then drop the schema itself.

DROP TABLE COMMAND :


If a base relation within a schema is no longer needed,the relation and its definition can be deleted by using the
DROP TABLE command. For example, if we no longer wish to keep track of dependents of employees in the
COMPANY .we can get rid of the DEPENDENT relation by issuing the following command:
DROP TABLE DEPENDENT CASCADE;
If the RESTRICT option is chosen instead of CASCADE, a table is dropped only if it is not referenced in any
constraints (for example, by foreign key definitions in another relation) or views or by any other elements. With the

Page 16
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
CASCADE option, all such constraints, views, and other elements that reference the table being dropped are also
dropped automatically from the schema, along with the table itself.
Notice that the DROP TABLEcommand not only deletes all the records in the table if successful, but also removes
the table definition from the catalog.

5.4.2 The ALTER Command


The definition of a base table or of other named schema elements can be changed by using the ALTER
command. For base tables, the possible alter table actions include adding or dropping a column (attribute),changing
a column definition,and adding or dropping table constraints.
For example, to add an attribute for keeping track of jobs of employees to the EMPLOYEE base relation in the
COMPANY schema ,we can use the command.
ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12);

We must still enter a value for the new attribute Job for each individual EMPLOYEE tuple.This can be done either
by specifying a default clause or by using the UPDATEcommand individually on each tuple..If no default clause is
specified,the new attribute will have NULLs in all the tuples of the relation immediately after the command is
executed;hence,the NOT NULLconstraint is not allowedin this case.

Page 17
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

Page 18
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
Database Application Development
Applications that rely on the DBMS to manage data run as separate processes that connect to the DBMS
to interact with it. Once a connection is established,SQL commands can be used to insert, delete, and
modify data. SQL queries canbe used to retrieve desired data. but we need to bridge an important
differencein how a database system sees data and how an application program in alanguage like Java or C
sees data: The result of a database query is a set (ormultiset) or records, hut Java has no set or multiset
data type. This mismatchis resolved through additional SQL constructs that allow applications toobtain
a handle on a collection and iterate over the records one at a time.

ACCESSING DATABASES FROM APPLICATIONS


In this section, we cover how SQL commands can be executed from within aprogram in a host language
such as C or Java. The use of SQL commandswithin a host language program is called Embedded SQL.
Details of Embedded SQL also depend on the host language. Although similar capabilities are
supported for a variety of host languages, the syntax sometimes varies.

Embedded SQL
Conceptually, embedding SQL commands in a host language program is straightforward.SQL
statements (i.e., not declarations) can be used wherever a statementin the host language is allowed (with a
few restrictions). SQL statementsmust be clearly marked so that a preprocessor can deal with them before
invokingthe compiler for the host language. Also, any host language variablesused to pass arguments into
an SQL command must be declared in SQL. Inparticular, some special host language variables must be
declared in SQL (sothat, for example, any error conditions arising during SQL execution can
becommunicated back to the main application program in the host language).

There are, however, two complications to bear in mind. First, the data typesrecognized by SQL
may not be recognized by the host language and vice versa.This mismatch is typically addressed by
casting data values appropriately beforepassing them to or frorn SQL commands.The second
complication hasto do with SQL being set-oriented,and is addressed using cursors.

Declaring Variables and Exceptions


SQL statements can refer to variables defined in the host program. Such host language variables
must be prefixed by a colon (:) in SQL statements and be declared between the commands EXEC SQL
BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION. The declarations are
similar to how they would look in a C program and, as usual in C. are separated by semicolons. For
example.we can declare variables c-sname, c_sid, c_mt'ing, and cage (with the initial c used as a naming
convention to emphasize that these are host language variables) as follows:
EXEC SQL BEGIN DECLARE SECTION
charc_sname[20];
longcsid;

short crating;
float cage;

EXEC SQL END DECLARE SECTION

Page 19
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
The SQL-92 standard defines a correspondence between the host language types and SQL types
for a number of host languages. In our example, c_snamc has the type CHARACTER(20) when referred
to in an SQL statement, csid has the type INTEGER, crating has the type SMALLINT, and cage has the
type REAL.
The SQL-92 standard recognizes two special variables for reporting errors, SQLCODE and
SQLSTATE.

Embedding SQL Statements


All SQL staternents embedded within a host program must be clearly marked, with the details
dependent on the host language; in C, SQL statements must be prefixed by EXEC SQL.
As a simple example, the following EmbeddedSQL statement inserts a row, whose column values
are based on the values of the host language variables contained in it, into the Sailors relation:
EXEC SQL
INSERT INTO Sailors VALUES (:c_sname, :csid, :crating, :cage);
The SQLSTATE variable should be checked for errors and exceptions after each Embedded SQL
statement. SQL provides the WHENEVER command to simplify this tedious task:
EXEC SQL WHENEVER [SQLERROR IF NOT FOUND] [ CONTINUE I GOTO stmt ]
The intent is that the value of SQLSTATE should bechecked after each Embedded SQL statement is
executed. If SQLERROR is specified and the value of SQLSTATE indicates an exception, control is
transferred to stmt, which is presumably responsible for error and exception handling. Control is also
transferred to stmt if NOT FOUND is specified and the value of SQLSTATE is 02000, which denotes NO
DATA.

Cursors
A major problem in embedding SQL statements in a host language like C is that an impedance
mismatch occurs because SQL operates on set of records, whereas languages like C do not cleanly
support a set-of-records abstraction. The solution is to essentially provide a mechanism that allows us to
retrieve rows one at a time from a relation.This mechanism is called a cursor.
We can declare a cursor on any relation or on any SQL query (because every query returns a set
of rows). Once a cursor is declared, we can open it (which positions the cursor just before the first row);
fetch the next row; move the cursor (to the next row, to the row after the next n, to the first row, or to the
previous row, etc., by specifying additional parameters for the FETCH command); or close the cursor.
Thus, a cursor essentially allows us to retrieve the rows in a table by positioning the cursor at a particular
row and reading its contents.

Basic Cursor Definition and Usage


cursors enable us to examine, in the host language program, a collection of rows computed by an
Embedded SQL statement:
We usually need to open a cursor if the embedded statement is a SELECT query. However, we
can avoid opening a cursor if the answer contains a single row.
INSERT, DELETE, and UPDATE statements typically require no cursor, although some variants
of DELETE and UPDATE use a cursor.
As an example, we can find the name and age of a sailor, specified by assigning a value to the
host variable c_sid, declared earlier, as follows:
EXEC SQL
SELECTS.sname, S.age
INTO :c_sname, :c_age
FROM Sailors S WHERES.sid = :c_sid;
The INTO clause allows us to assign the columns of the single answer row to the host variables
c_sname and c_age. Therefore, we do not need a cursor to embed this query in a host language program.

Page 20
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
But what about the following query, which computes the names and ages of all sailors with a rating
greater than the current value of the host variable c_minrating?
SELECT S.sname, S.age
FROM Sailors S
WHERE S.rating> :c_minrating

A cursor can be thought of as 'pointing' to a row in the collection of answers to the query
associated with it. When a cursor is opened, it is positioned just before the first row. We can use the
FETCH command to read the first row of cursor sinfo into host language variables:
FETCH sinfoINTO :c_sname, :c_age;
When the FETCH statement is executed, the cursor is positioned to point at the next row (which
is the first row in the table when FETCH is executed for the first time after opening the cursor) and the
column values in the row are copied into the corresponding host variables. By repeatedly executing this
FETCH statement (say, in a while-loop in the C program), we can read all the rows computed by the
query, one row at a time. Additional parameters to the FETCH command allow us to position a cursor in
very flexible ways.
How do we know when we have looked at all the rows associated with the cursor? By looking at
the special variables SQLCODE or SQLSTATE, of course. SQLSTATE, for example, is set to the value
02000, which denotes NO DATA, to indicate that there are no more rows ifthe FETCH statement
positions the cursor after the last row.
When we are done with a cursor, we can close it:
CLOSE sinfo;
It can be opened again if neededand the value of :c_minrating in the SQL query associated with
the cursor would be the value of the host variable c_minrating at that time.

Page 21
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

DELETE commands allow us to update or delete the row on which the cursor is positioned.If the keyword
SCROLL is specified, the cursor is scrollable, which means that variants of the FETCH command can be
used to position the cursor in very flexible ways;If the keyword INSENSITIVE is specified, the cursor
behaves as if it is ranging over a private copy of the collection of answer rows.A holdable cursor is
specified using the WITH HOLD clause, and is not closed when the transaction is conunitted.Finally, in
what order do FETCH commands retrieve rows? In general this order is unspecified, but the optional
ORDER BY clause can be used to specify a sort order. Note that columns mentioned in the ORDER BY
clause cannot be updated through the cursor!

Dynamic SQL

The first statement declares the C variable c_sqlstring and initializes its value to the string representation
of an SQL command. The second statement results in this string being parsed and compiled as an SQL
command, with the resulting executable bound to the SQL variable readytogo. (Since readytogo is an
SQL variable, just like a cursor name, it is not prefixed by a colon.) The third statement executes the
command.

Page 22
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
6.2 AN INTRODUCTION TO JDBC
ODBC and JDBC, short for Open DataBase Connectivity and Java DataBase Connectivity, also
enable the integration of SQL with a general-purpose programming language. Both ODBC and JDBC
expose database capabilities in a standardized way to the application programmer through an application
programming interface (API).
An application that interacts with a data source through ODBC or JDBC selects a data source,
dynamically loads the corresponding driver, and establishes a connection with the data source.

6.2.1 JDBC Architecture


The architecture of JDBC has four main components: the application, the driver manager,
several data source specific drivers, and the corresponding data Sources.

The application initiates and terminates the connection with a data source. It sets transaction
boundaries, submits SQL statements, and retrieves the results all through a well-defined interface as
specified by the JDBC API.
The primary goal of the driver manager is to load JDBC drivers and pass JDBC function calls
from the application to the correct driver.
The driver establishes the connection with the data source.
The data source processes commands from the driver and returns the results.

Driversin JDBC are classified into four types depending on the architectural relationship between the
application and the data source:

Page 23
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

JDBC CLASSES AND INTERFACES

JDBC is a collection of Java classes and interfaces that enables database access from programs
written in the Java language. It contains methods for connecting to a remote data source, executing SQL
statements, examining sets of results from SQL statements, transaction management, and exception
handling. The classes and interfaces are part of the java.sql package. Thus, all code fragments in the
remainder of this section should include the statement import java. sql .* at the beginning of the code;

JDBC Driver Management


In JDBC, data source drivers are managed by the Drivermanager class, which maintains a list of
all currently loaded drivers. The Drivermanager class hasmethods registerDriver, deregisterDriver, and
getDrivers to enable dynamic addition and deletion of drivers.
The first step in connecting to a data source is to load the corresponding JDBC driver. This is
accomplished by using the Java mechanism for dynamically loading classes. The static method forName
in the Class class returns the Java class as specified in the argument string and executes its static
constructor. The static constructor of the dynamically loaded class loads an instance of the Driver class,
and this Driver object registers itself with the DriverManager class.
The following Java example code explicitly loads a JDBC driver:
Class.forName("oracle/jdbc.driver.OracleDriver");

Connections
A session with a data source is started through creation of a Connection object; A connection identifies a
logical session with a data source; multiple connections within the same Java program can refer to
different data sources or the same data source. Connections are specified through a JDBC URL, a URL
that uses the jdbc protocol. Such a URL has the form
jdbc:<subprotocol>:<otherParameters>
The code example shown in Figure 6.2 establishes a connection to an Oracle database assuming that the
strings userld and password are set to valid values.

Page 24
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

Establishing a connection to a data source is a costly operation since it involves several steps, such as
establishing a network connection to the data source, authentication, and allocation of resources such as
memory. In case an application establishes many different connections from different parties (such as a
Web server), connections are often pooled to avoid this overhead. A connection pool is a set of
established connections to a data source. Whenever a new connection is needed, one of the connections
from the pool is used, instead of creating a new connection to the data source.

Executing SQL Statements


We now discuss how to create and execute SQL statements using JDBC. In the JDBC code examples in
this section, we assume that we have a Connection object named con. JDBC supports three different ways
ofexecuting statements: Statement, PreparedStatement, and CallableStatement. The Statement class is the
base class for the other two statment classes. It allows us to query the data source with any static or
dynamically generated SQL query.
The PreparedStatement class is dynamically generates precompiled SQL statements that can be
used several times; these SQL statements can have parameters, but their structure is fixed when the
PreparedStatement object (representing the SQL statement) is created.
Consider the sample code using a PreparedStatment object shown in Figure 6.3. The SQL query
specifies the query string, but uses ''?' for the values of the parameters, which are set later using methods
setString,setFloat, and setlnt. The ''?' placeholders can be used anywhere in SQL statements where they
can be replaced with a value. Examples of places where they can appear include the WHERE clause (e.g.,
'WHERE author=?'), or in SQL UPDATE and INSERT staternents, as in Figure 6.3.
The method setString is one wayto set a parameter value; analogous methods are available for int,
float, and date. It is good style to always use clearParameters() before setting parameter values in order to
remove any old data.

Page 25
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

The executeUpdate method returns an integer indicating the number of rows the SQL statement
modified; it returns 0 for successful execution without modifying any rows.
The executeQuery method is used if the SQL statement returns data, such &"l in a regular
SELECT query.JDBC has its own cursor mechanism in the form of a ResultSet object, which we discuss
next. The execute method is more general than executeQuery and executeUpdate.

ResultSets
The statement executeQuery returns a, ResultSet object, which is similar to a cursor. ResultSet
cursors in JDBC 2.0 are very powerful; they allow forward and reverse scrolling and in-place editing and
insertions.
In its most basic form, the ResultSet object allows us to read one row of the output of the query at
a time. Initially, the ResultSet is positioned before the first row, and we have to retrieve the first row with
an explicit call to the next0 method. The next method returns false if there are no more rows in the query
answer, and true other\vise. The code fragment shown in Figure 6.4 illustrates the basic usage of a
ResultSet object.

Page 26
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
Matching Java and SQL Data Types
JDBC provides special data types and specifies their relationship to corresponding SQL data
types. Figure 6.5 shows the accessor methods in a ResultSet object for the most common SQL datatypes.
With these accessor methods, we can retrieve values from the current row of the query result referenced
by the ResultSet object. There are two forms for each accessor method: One method retrieves values by
column index, starting at one, and the other retrieves values by column name. The following example
shows how to access fields of the current ResultSet row using accesssor methods.

Exceptions and Warnings


Similar to the SQLSTATE variable, most of the methods in java.sql can throw an exception of the type
SQLException if an error occurs. The information includes SQLState, a string that describes the error
(e.g., whether the statement contained an SQL syntax error). In addition to the standard getMessage0
method inherited from Throwable, SQLException has two additional methods that provide further
information, and a method to get (or chain) additional exceptions:

An SQL Warning is a subclass of SQLException. Warnings are not severe as errors and the program can
usually proceed without special handling of warnings. Warnings are not thrown like other exceptions, and

Page 27
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
they are not caught as part of the try"-catch block around a java.sql statement. We Heed to specifically
test whether warnings exist. Connection, Statement, and ResultSet objects all have a getWarnings 0
method with which we can retrieve SQL warnings if they exist. Statement objects clear warnings
automatically on execution of the next statement; ResultSet objects clear warnings every time a new tuple
is accessed.

Examining Database Metadata


we can use theDatabaseMetaData object to obtain information about the database system itself, as well as
information from the database catalog. For example, the following code fragment shows how to obtain
the name and driver version of the JDBC driver:
DatabaseMetaData md = con.getMetaD<Lta():
System.out.println("Driver Information:");
System.out.println("Name:" + md.getDriverNameO + "; version:" + mcl.getDriverVersion());
The DatabaseMetaData object has many more methods.we list some methods here:
1) publicResultSet getCatalogs0 throws SqLException. This function returns a ResultSet that can be used
to iterate over all the catalog relations. The functions getIndexInfo0 and getTables0 work analogously.

Page 28
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
2) publicintgetMaxConnections0 throws SqLException. This function returns the ma.ximum number of
connections possible.

We will conclude our discussion of JDBC with an example code fragment that examines all database
metadata shown in Figure 6.7.

SQLJ
SQLJ was developed by the SQLJ Group, a group of database vendors and Sun. SQLJ was
developed to complement the dynamic way of creating queries in JDBC with a static model. It is therefore
very close to Embedded SQL. Unlike JDBC, having semi-static SQL queries allows the compiler to
perform SQL syntax checks, strong type checks of the compatibility of the host variables with the
respective SQL attributes, and consistency of the query with the database schema-tables, attributes, views,
and stored procedures--all at compilation time. For example, in both SQLJ and Embedded SQL, variables
in the host language always are bound statically to the same arguments, whereas in JDBC, we need
separate statements to bind each variable to an argument and to retrieve the

Page 29
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
result. For example, the following SQLJ statement binds host language variables title, price, and author to
the return values of the cursor books.
#sql books = { SELECT title, price INTO :title, :price
FROM Books WHERE author = :author
};
In JDBC, we can dynamically decide which host language variables will hold the query result. In the
following example, we read the title of the book into variable ftitle if the book was written by Feynman,
and into variable otitle otherwise:
/ / assume we have a ResultSet cursor rs
author = rs.getString(3);
if (author=="Feynman")
{ ftitle = rs.getString(2):
}
else { otitle = rs.getString(2);
}
When writing SQLJ applications, we just write regular Java code and embed SQL statements according to
a set of rules. SQLJ applications are pre-processed through an SQLJ translation program that replaces the
embedded SQLJ code with calls to an SQLJ Java library. The modified program code can then be
compiled by any Java compiler. Usually the SQLJ Java library makes calls to a JDBC driver, which
handles the connection to the database system.

Writing SQLJ Code


We will introduce SQLJ by means of examples. Let us start with an SQLJ code fragment that selects
records from the Books table that match a given author.

Page 30
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
The corresponding JDBC code fragment looks as follows (assuming we also declared price, name, and
author:

System.out.println(rs.getString(l) + ", " + rs.getFloat(2));


}

Comparing the JDBC and SQLJ code, we see that the SQLJ code is much easier to read than the JDBC
code. Thus, SQLJ reduces software development and maintenance costs.

Let us consider the individual components of the SQLJ code in more detail. All SQLJ statements
have the special prefix #sql. In SQLJ, we retrieve the results of SQL queries with iterator objects, which
are basically cursors. An iterator is an instance of an iterator class. Usage of an iterator in SQLJ goes
through five steps:

There are two types of iterator classes: named iterators and positional iterators. For named iterators, we
specify both the variable type and the name of each column of the iterator. This allows us to retrieve
individual columns by name as in our previous example where we could retrieve the title colunm from the
Books table using the expression books.titIe(). For positional iterators, we need to specifY only the
variable type for each column of the iterator. To access the individual columns of the iterator, we use a
FETCH ... INTO construct, similar to Embedded SQL. Both iterator types have the same performance;
which iterator to use depends on the programmer's taste.

Page 31
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
6.5 STORED PROCEDURES
It is often important to execute some parts of the application logic directly in the process space of
the database system. Running application logic directly at the database has the advantage that the amount
of data that is transferred between the database server and the client issuing the SQL statement can be
minimized, while at the same time utilizing the full power of the databa.se server.
When SQL statements are issued from a remote application, the records in the result of the query
need to be transferred from the database system back to the application. If we use a cursor to remotely
access the results of an SQL statement, the DBMS has resources such as locks and memory tied up while
the application is processing the records retrieved through the cursor.
In contrast, a stored procedure is a program that is executed through a single SQL statement that
can be locally executed and completed within the process space of the database server. The results can be
packaged into one big result and returned to the application, or the application logic can be performed
directly at the server, without having to transmit the results to the client at all.
Once a stored procedure is registered with the database server, different users can re-use the
stored procedure, eliminating duplication of efforts in writing SQL queries or application logic, and
making code maintenance ea."lY. In addition, application programmers do not need to know the the
databa.se schema if we encapsulate all databa.'3e access into stored procedures.
Although theyare called stored procedur'es, they do not have to be procedures in a programming
language sense; they can be functions.

6.5.1 Creating a Simple Stored Procedure


Let us look at the example stored procedure written in SQL shown in Figure (i.S. vVe see that stored
procedures must have a name; this stored procedurehas the name 'ShowNumberOfOrders.' Otherwise, it
just contains an SQL statement that is precompiled and stored at the server.

Let us look at an example of a stored procedure with arguments. The stored procedure shown in Figure
6.9 has two arguments: book_isbn and addedQty. It updates the available number of copies of a book with
the quantity from a new shipment.

Page 32
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

Stored procedures do not have to be written in SQL; they can be written in any host language. As an
example, the stored procedure shown in Figure 6.10 is a Java function that is dynamically executed by the
database server whenever it is called by the client:

CREATE PROCEDURE RallkCustomers(IN number INTEGER)


LANGUAGE Java
EXTERNAL NAME 'file:// /c:/storedProcedures/rank.jar'

Figure 6.10 A Stored Procedure in Java

Calling Stored Procedures


Stored procedures can be called in interactive SQL with the CALL statement:

CALL storedProcedureName(argumentl, argument2, ... , argumentN);

Calling Stored Procedures from JDBC

Page 33
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

Calling Stored Procedures from SQLJ

SQL/PSM
All major database systems provide ways for users to write stored procedures in a simple,
general purpose language closely aligned with SQL. In this section, we briefly discuss the SQL/PSM
standard, which is representative of most vendorspecific languages. In PSM, we define modules, which
are collections of stored procedures, temporary relations, and other declarations.

Each parameter is a triple consisting of the mode (IN, OUT, or INOUT as discussed in the previous
section), the parameter name, and the SQL datatype of the parameter.

We start out with an example of a SQL/PSM function that illustrates the main SQL/PSM
constructs. The function takes as input a customer identified by her cid and a year. The function returns
the rating of the customer, which is defined a...'3 follows: Customers who have bought more than ten
books during the year are rated 'two'; customer who have purchased between 5 and 10 books are rated
'one', otherwise the customer is rated 'zero'. The following SQL/PSM code computes the rating for a given
customer and year.

Page 34
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES
CREATE PROCEDURE RateCustomer(IN custId INTEGER, IN year INTEGER)
RETURNS INTEGER
DECLARE rating INTEGER;
DECLARE numOrders INTEGER;
SET numOrders = (SELECT COUNT(*) FROM Orders 0 WHERE O.tid = custId);
IF (numOrders>10) THEN rating=2;
ELSEIF (numOrders>5) THEN rating=1;
ELSE rating=O;
END IF;
RETURN rating;

Let us use this example to give a short overview of some SQL/PSM constructs:

6.6 CASE STUDY: THE INTERNET BOOK SHOP


DBDudes finished logical database design, as discussed in Section 3.8, and now consider the
queries that they have to support. They expect that the application logic will be implemented in Java, and
so they consider JDBC and SQLJ as possible candidates for interfacing the database system with
application code.
Recall that DBDudes settled on the following schema:
Books(isbn: CHAR(10), title: CHAR(8), author: CHAR(80), qty_in_stock: INTEGER, price:
REAL, year_published: INTEGER)
Customers(cid: INTEGER, cname: CHAR(80), address: CHAR(200))
Orders(ordernum: INTEGER, isbn: CHAR(lO), cid: INTEGER, cardnum: CHAR(l6), qty:
INTEGER, order_date: DATE, ship_date: DATE)

Page 35
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

CREATE PROCEDURE SearchByISBN (IN book.isbn CHAR (10) )


SELECT B.title, B.author, B.qty_in_stock,B.price, B.yeaLpublished
FROM Books B
WHERE B.isbn = book.isbn
Placing an order involves inserting one or more records into the Orders table.Since DBDudes has not yet
chosen the Java-based technology to program the application logic, they assume for now that the
individual books in the order are stored at the application layer in a Java array. To finalize the order, they
write the following JDBC code shown in Figure 6.11, which inserts the elements from the array into the
Orders table. Note that this code fragment assumes several Java variables have been set beforehand.

Page 36
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)
MODULE –IV NOTES

DBDudes writes other JDBC code and stored procedures for all of the remaining tasks. They use code
similar to some of the fragments that we have seen in this chapter.

DBDudcs takes care to make the application robust by processing exceptions and warnings, as
shown in Figure 6.6.
DBDudes also decide to write a trigger, which is shown in Figure 6.12. Whenever a new order is
entered into the Orders table, it is inserted with ship~date set to NULL. The trigger processes each row in
the order and calls the stored procedure 'UpdateShipDate'. This stored procedure (whose code is not
shown here) updates the (anticipated) ship_date of the new order to 'tomorrow', in case qtyjlLstock of the
corresponding book in the Books table is greater than zero. Otherwise, the stored procedme sets the
ship_date to two weeks.

Page 48
Jyoti Kumari, Asst. Professor, SVIT CSE(DS)

You might also like