MATRUSRI ENGINEERING COLLEGE�DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

SUBJECT NAME: DataBase Management Systems

FACULTY NAME: K Sunil Manohar Reddy

Insert Your Photo here🡪

MATRUSRI

ENGINEERING COLLEGE

INTRODUCTION: ��THIS UNIT DEALS WITH THE ADVANCED SQL AND ALSO EMBEDDED SQL AND DYNAMIC SQL.

UNIT-III

OUTCOMES:

​

Upon completion of this unit, student will be able to:

• Develop Database applications using SQL and Embedded SQL

MATRUSRI

ENGINEERING COLLEGE

CONTENTS:� ADVANCED SQL: SQL DATA TYPES AND SCHEMES, INTEGRITY CONSTRAINTS, AUTHORIZATION, EMBEDDED SQL, DYNAMIC SQL, FUNCTIONS AND PROCEDURAL CONSTRUCTS, RECURSIVE QUERIES, ADVANCED SQL FEATURES ��

OUTCOMES:

Upon completion of this module, student will be able to:

• Explain the concepts of SQL.

​

MODULE-I

MATRUSRI

ENGINEERING COLLEGE

Transactions

• A transaction consists of a sequence of query and/or update statements and is a “unit” of work
• The SQL standard specifies that a transaction begins implicitly when an SQL statement is executed.
• The transaction must end with one of the following statements:
• Commit work. The updates performed by the transaction become permanent in the database.
• Rollback work. All the updates performed by the SQL statements in the transaction are undone.
• Atomic transaction
• either fully executed or rolled back as if it never occurred
• Isolation from concurrent transactions

​

MATRUSRI

ENGINEERING COLLEGE

Built-in Data Types in SQL

• date: Dates, containing a (4 digit) year, month and date
• Example: date ‘2005-7-27’
• time: Time of day, in hours, minutes and seconds.
• Example: time ‘09:00:30’ time ‘09:00:30.75’
• timestamp: date plus time of day
• Example: timestamp ‘2005-7-27 09:00:30.75’
• interval: period of time
• Example: interval ‘1’ day
• Subtracting a date/time/timestamp value from another gives an interval value
• Interval values can be added to date/time/timestamp values

MATRUSRI

ENGINEERING COLLEGE

Index Creation

create table student �(ID varchar (5),�name varchar (20) not null,�dept_name varchar (20),�tot_cred numeric (3,0) default 0,�primary key (ID))

create index studentID_index on student(ID)

Indices are data structures used to speed up access to records with specified values for index attributes

e.g. select * � from student� where ID = ‘12345’

can be executed by using the index to find the required record, without looking at all records of student

MATRUSRI

ENGINEERING COLLEGE

Integrity Constraints

• 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.
• A checking account must have a balance greater than $10,000.00
• A salary of a bank employee must be at least $4.00 an hour
• A customer must have a (non-null) phone number

​

Constraints on a Single Relation

• not null
• primary key
• unique
• check (P), where P is a predicate

MATRUSRI

ENGINEERING COLLEGE

Not Null & Unique Constraints

• Not null
• Declare name and budget to be not null

name varchar(20) not null� budget numeric(12,2) not null

​

• Unique Constraints
• unique ( A₁, A₂, …, A_m)
• The unique specification states that the attributes A₁, A₂, …, A_m form a candidate key.
• Candidate keys are permitted to be null (in contrast to primary keys).

​

MATRUSRI

ENGINEERING COLLEGE

The check clause

• The check (P) clause specifies a predicate P that must be satisfied by every tuple in a relation.
• Example: ensure that semester is one of fall, winter, spring or summer

create table section

(course_id varchar (8),

sec_id varchar (8),

semester varchar (6),

year numeric (4,0),

building varchar (15),

room_number varchar (7),

time slot id varchar (4),

primary key (course_id, sec_id, semester, year),

check (semester in ('Fall', 'Winter', 'Spring', 'Summer')))

​

​

​

MATRUSRI

ENGINEERING COLLEGE

Authorization

• Forms of authorization on parts of the database:
• Read - allows reading, but not modification of data.
• Insert - allows insertion of new data, but not modification of existing data.
• Update - allows modification, but not deletion of data.
• Delete - allows deletion of data.

​

• Forms of authorization to modify the database schema
• Index - allows creation and deletion of indices.
• Resources - allows creation of new relations.
• Alteration - allows addition or deletion of attributes in a relation.
• Drop - allows deletion of relations.

​

​

MATRUSRI

ENGINEERING COLLEGE

Authorization Specification in SQL

• The grant statement is used to confer authorization

grant <privilege list>on <relation name or view name> to <user list>

• <user list> is:
• a user-id
• public, which allows all valid users the privilege granted
• A role (more on this later)
• Granting a privilege on a view does not imply granting any privileges on the underlying relations.
• The grantor of the privilege must already hold the privilege on the specified item (or be the database administrator).

​

MATRUSRI

ENGINEERING COLLEGE

Revoking Authorization in SQL

• The revoke statement is used to revoke authorization.
• revoke <privilege list>
• on <relation name or view name> from <user list>
• Example:
• revoke select on branch from U₁, U₂, U₃
• <privilege-list> may be all to revoke all privileges the revokee may hold.
• If <revokee-list> includes public, all users lose the privilege except those granted it explicitly.
• If the same privilege was granted twice to the same user by different grantees, the user may retain the privilege after the revocation.
• All privileges that depend on the privilege being revoked are also revoked.

​

​

MATRUSRI

ENGINEERING COLLEGE

Embedded SQL

• The SQL standard defines embeddings of SQL in a variety of programming languages such as C, C++, Java, Fortran, and PL/1,
• A language to which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language comprise embedded SQL.
• The basic form of these languages follows that of the System R embedding of SQL into PL/1.
• EXEC SQL statement is used in the host language to identify embedded SQL request to the preprocessor

EXEC SQL <embedded SQL statement >;

Note: this varies by language:

• In some languages, like COBOL, the semicolon is replaced with END-EXEC
• In Java embedding uses # SQL { …. };

�

MATRUSRI

ENGINEERING COLLEGE

Embedded SQL (Cont.)

• Before executing any SQL statements, the program must first connect to the database. This is done using:

EXEC-SQL connect to server user user-name using password;

Here, server identifies the server to which a connection is to be established.

• Variables of the host language can be used within embedded SQL statements. They are preceded by a colon (:) to distinguish from SQL variables (e.g., :credit_amount )
• Variables used as above must be declared within DECLARE section, as illustrated below. The syntax for declaring the variables, however, follows the usual host language syntax.

EXEC-SQL BEGIN DECLARE SECTION}

int credit-amount ;

EXEC-SQL END DECLARE SECTION;

• To write an embedded SQL query, we use the

declare c cursor for <SQL query>

statement. The variable c is used to identify the query

MATRUSRI

ENGINEERING COLLEGE

Functions and Procedures

• Functions and procedures allow “business logic” to be stored in the database and executed from SQL statements.
• These can be defined either by the procedural component of SQL or by an external programming language such as Java, C, or C++.
• The syntax we present here is defined by the SQL standard.
• Most databases implement nonstandard versions of this syntax.

MATRUSRI

ENGINEERING COLLEGE

Declaring SQL Functions

• Define a function that, given the name of a department, returns the count of the number of instructors in that department.

create function dept_count (dept_name varchar(20))� returns integer� begin� declare d_count integer;� select count (* ) into d_count� from instructor� where instructor.dept_name = dept_name� return d_count;� end

​

MATRUSRI

ENGINEERING COLLEGE

Declaring SQL Functions

• Define a function that, given the name of a department, returns the count of the number of instructors in that department.

create function dept_count (dept_name varchar(20))� returns integer� begin� declare d_count integer;� select count (* ) into d_count� from instructor� where instructor.dept_name = dept_name� return d_count;� end

• The function dept_count can be used to find the department names and budget of all departments with more that 12 instructors.

select dept_name, budget� from department� where dept_count (dept_name ) > 12

​

​

​

MATRUSRI

ENGINEERING COLLEGE

SQL Procedures

• The dept_count function could instead be written as procedure:

create procedure dept_count_proc (in dept_name varchar(20), � out d_count integer)� begin

select count(*) into d_count� from instructor� where instructor.dept_name = dept_count_proc.dept_name

end

• The keywords in and out are parameters that are expected to have values assigned to them and parameters whose values are set in the procedure in order to return results.
• Procedures can be invoked either from an SQL procedure or from embedded SQL, using the call statement.

declare d_count integer;� call dept_count_proc( 'Physics', d_count); Procedures and functions can be invoked also from dynamic SQL

• SQL allows more than one procedure of the so long as the number of arguments of the procedures with the same name is different.
• The name, along with the number of arguments, is used to identify the procedure.

​

MATRUSRI

ENGINEERING COLLEGE

Recursive Queries

• SQL:1999 permits recursive view definition
• Example: find which courses are a prerequisite, whether directly or indirectly, for a specific course � with recursive rec_prereq(course_id, prereq_id) as (� select course_id, prereq_id� from prereq� union� select rec_prereq.course_id, prereq.prereq_id, � from rec_rereq, prereq� where rec_prereq.prereq_id = prereq.course_id� )� select ∗ from rec_prereq;

This example view, rec_prereq, is called the transitive closure of the prereq relation

MATRUSRI

ENGINEERING COLLEGE

The Power of Recursion

• Recursive views make it possible to write queries, such as transitive closure queries, that cannot be written without recursion or iteration.
• Intuition: Without recursion, a non-recursive non-iterative program can perform only a fixed number of joins of prereq with itself
• This can give only a fixed number of levels of managers
• Given a fixed non-recursive query, we can construct a database with a greater number of levels of prerequisites on which the query will not work
• Alternative: write a procedure to iterate as many times as requires
• See procedure find All Prereqs in book
• Recursive views are required to be monotonic. That is, if we add tuples to prereq the view rec_prereq contains all of the tuples it contained before, plus possibly more

MATRUSRI

ENGINEERING COLLEGE