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

DATABASE MANAGEMENT SYSTEMS

COURSE OBJECTIVES:

• To Learn mathematical concepts as applied in computer
• To introduce three scheme architecture and DBMS functional components.
• To learn formal and commercial query languages of RDBMS
• To Study different file organization and indexing techniques
• To familiarize theory of serializablity and implementation of concurrency control, and recovery

COURSE OUTCOMES:

​

• Understand three schema architecture and DBMS functional concepts, model E-R Diagrams
• Learn formal and commercial query languages
• Develop Database applications using SQL and Embedded SQL and understand Database Design
• Gain the knowledge of different file organization
• Familiarize theory of serializability and Understand the working of concurrency control and recovery mechanisms.

MATRUSRI

ENGINEERING COLLEGE

INTRODUCTION: ��THIS UNIT DEALS WITH THE RELATIONAL MODEL AND STORING, ACCESSING AND MANAGING DATA USING VARIOUS FORMAL AND COMMERCIAL QUERY LANGUAGES.

UNIT-II

OUTCOMES:

​

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

• Describe about concepts of Relational model
• Write basic SQL queries

MATRUSRI

ENGINEERING COLLEGE

CONTENTS:�RELATIONAL MODEL: STRUCTURE OF RELATIONAL DATABASES, FUNDAMENTAL RELATIONAL-ALGEBRA OPERATIONS, ADDITIONAL RELATIONAL-ALGEBRA OPERATIONS, EXTENDED RELATIONAL-ALGEBRA OPERATIONS, NULL VALUES, MODIFICATION OF THE DATABASES ��

OUTCOMES:

​

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

• Write basic SQL queries.

​

MODULE-II

MATRUSRI

ENGINEERING COLLEGE

Introduction to SQL

History:

​

• IBM Sequel language developed as part of System R project at the IBM San Jose Research Laboratory by E.F. CODD.
• Renamed Structured Query Language (SQL)
• ANSI and ISO standard SQL:
• SQL-86
• SQL-89
• SQL-92
• SQL:1999 (language name became Y2K compliant!)
• SQL:2003
• Commercial systems offer most, if not all, SQL-92 features, plus varying feature sets from later standards and special proprietary features.
• Not all examples here may work on your particular system.

MATRUSRI

ENGINEERING COLLEGE

Data Definition Language

The SQL Data Definition Language (DDL) allows the specification of information about relations, including:

• The schema for each relation.
• The domain of values associated with each attribute.
• Integrity constraints
• Also other information such as
• The set of indices to be maintained for each relations.
• Security and authorization information for each relation.
• The physical storage structure of each relation on disk.

​

MATRUSRI

ENGINEERING COLLEGE

Domain Types in SQL

• char(n). Fixed length character string, with user-specified length n.
• varchar(n). Variable length character strings, with user-specified maximum length n.
• int. Integer (a finite subset of the integers that is machine-dependent).
• small int. Small integer (a machine-dependent subset of the integer domain type).
• Numeric (p,d). Fixed point number, with user-specified precision of p digits, with d digits to the right of decimal point. (ex., numeric (3,1), allows 44.5 to be stored)
• real, double precision. Floating point and double-precision floating point numbers, with machine-dependent precision.
• float(n). Floating point number, with user-specified precision of at least n digits.

MATRUSRI

ENGINEERING COLLEGE

Create Table Construct

• An SQL relation is defined using the create table command:

create table r (A₁ D₁, A₂ D₂, ..., A_n D_n,� (integrity-constraint₁),� ...,� (integrity-constraint_k));

• r is the name of the relation
• each A_i is an attribute name in the schema of relation r
• D_i is the data type of values in the domain of attribute A_i

​

• Example:

create table instructor (� ID char(5),� name varchar(20),� dept_name varchar(20),� salary numeric(8,2));

MATRUSRI

ENGINEERING COLLEGE

Integrity Constraints in Create Table

• not null
• primary key (A₁, ..., A_n )
• foreign key (A_m, ..., A_n ) references r

​

Example:

create table instructor (� ID char(5),� name varchar(20) not null,� dept_name varchar(20),� salary numeric(8,2),� primary key (ID),� foreign key (dept_name) references department);

​

primary key declaration on an attribute automatically ensures not null

MATRUSRI

ENGINEERING COLLEGE

Examples of Relation Definitions

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

foreign key (dept_name) references department);

​

• create table takes (� ID varchar(5),� course_id varchar(8),� sec_id varchar(8),� semester varchar(6),� year numeric(4,0),� grade varchar(2),

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

foreign key (ID) references student,� foreign key (course_id, sec_id, semester, year) references section);

​

Note: sec_id can be dropped from primary key above, to ensure a student cannot be registered for two sections of the same course in the same semester

MATRUSRI

ENGINEERING COLLEGE

Updates to Tables

• Insert
• insert into instructor values (‘10211’, ’Smith’, ’Biology’, 66000);
• Delete
• Remove all tuples from the student relation
• delete from student
• Alter
• alter table r add A D
• where A is the name of the attribute to be added to relation r and D is the domain of A.
• All exiting tuples in the relation are assigned null as the value for the new attribute.
• alter table r drop A
• where A is the name of an attribute of relation r
• Dropping of attributes not supported by many databases.
• Drop Table
• drop table r

MATRUSRI

ENGINEERING COLLEGE

Basic Query Structure

• A typical SQL query has the form:�� select A₁, A₂, ..., A_n� from r₁, r₂, ..., r_m� where P;�A_i represents an attribute
• R_i represents a relation
• P is a predicate.

​

• The result of an SQL query is a relation.

​

MATRUSRI

ENGINEERING COLLEGE

The Select Clause

• The select clause lists the attributes desired in the result of a query
• corresponds to the projection operation of the relational algebra
• Example: find the names of all instructors:� select name� from instructor;
• NOTE: SQL names are case insensitive (i.e., you may use upper- or lower-case letters.)
• E.g., Name ≡ NAME ≡ name
• Some people use upper case wherever we use bold font.

​

• SQL allows duplicates in relations as well as in query results.

​

​

MATRUSRI

ENGINEERING COLLEGE

The Select Clause

• To force the elimination of duplicates, insert the keyword distinct after select.
• Find the department names of all instructors, and remove duplicates

select distinct dept_name� from instructor;

​

• The keyword all specifies that duplicates should not be removed.� select all dept_name� from instructor;
• An asterisk in the select clause denotes “all attributes”

select *� from instructor;

​

• An attribute can be a literal with no from clause

select ‘437’;

• Results is a table with one column and a single row with value “437”
• Can give the column a name using:

select ‘437’ as FOO;

​

MATRUSRI

ENGINEERING COLLEGE

The Select Clause

• An attribute can be a literal with from clause

select ‘A’� from instructor;

• Result is a table with one column and N rows (number of tuples in the instructors table), each row with value “A”

​

The select clause can contain arithmetic expressions involving the operation, +, –, , and /, and operating on constants or attributes of tuples.

​

select ID, name, salary/12� from instructor;

​

would return a relation that is the same as the instructor relation, except that the value of the attribute salary is divided by 12.

​

Can rename “salary/12” using the as clause:

select ID, name, salary/12 as monthly_salary;

MATRUSRI

ENGINEERING COLLEGE

The Where Clause

• The where clause specifies conditions that the result must satisfy
• Corresponds to the selection predicate of the relational algebra.
• To find all instructors in Comp. Sci. dept

select name� from instructor� where dept_name = ‘Comp. Sci.‘;

​

• Comparison results can be combined using the logical connectives and, or, and not
• To find all instructors in Comp. Sci. dept with salary > 80000

select name� from instructor� where dept_name = ‘Comp. Sci.' and salary > 80000;

​

• Comparisons can be applied to results of arithmetic expressions.

MATRUSRI

ENGINEERING COLLEGE

The From Clause

• The from clause lists the relations involved in the query
• Corresponds to the Cartesian product operation of the relational algebra.
• Find the Cartesian product instructor X teaches

select *� from instructor, teaches;

• generates every possible instructor – teaches pair, with all attributes from both relations.
• For common attributes (e.g., ID), the attributes in the resulting table are renamed using the relation name (e.g., instructor.ID)
• Cartesian product not very useful directly, but useful combined with where-clause condition (selection operation in relational algebra).

MATRUSRI

ENGINEERING COLLEGE

Examples

• Find the names of all instructors who have taught some course and the course_id
• select name, course_id�from instructor , teaches�where instructor.ID = teaches.ID;

​

• Find the names of all instructors in the Art department who have taught some course and the course_id
• select name, course_id�from instructor , teaches�where instructor.ID = teaches.ID and instructor. dept_name = ‘Art’;

MATRUSRI

ENGINEERING COLLEGE

The Rename Operation

• The SQL allows renaming relations and attributes using the as clause:

old-name as new-name�

• Find the names of all instructors who have a higher salary than �some instructor in ‘Comp. Sci’.
• select distinct T.name�from instructor as T, instructor as S�where T.salary > S.salary and S.dept_name = ‘Comp. Sci.’;

​

• Keyword as is optional and may be omitted� instructor as T ≡ instructor T

MATRUSRI

ENGINEERING COLLEGE

Cartesian Product

MATRUSRI

ENGINEERING COLLEGE

String Operations

• SQL includes a string-matching operator for comparisons on character strings. The operator like uses patterns that are described using two special characters:
• percent ( % ). The % character matches any substring.
• underscore ( _ ). The _ character matches any character.
• Find the names of all instructors whose name includes the substring “dar”.

select name� from instructor� where name like '%dar%' ;

​

• Match the string “100%”

like ‘100 \%' escape '\'

in that above we use backslash (\) as the escape character.

MATRUSRI

ENGINEERING COLLEGE

String Operations

• Patterns are case sensitive.
• Pattern matching examples:
• ‘Intro%’ matches any string beginning with “Intro”.
• ‘%Comp%’ matches any string containing “Comp” as a substring.
• ‘_ _ _’ matches any string of exactly three characters.
• ‘_ _ _ %’ matches any string of at least three characters.

​

• SQL supports a variety of string operations such as
• concatenation (using “||”)
• converting from upper to lower case (and vice versa)
• finding string length, extracting substrings, etc.

MATRUSRI

ENGINEERING COLLEGE

Ordering the Display of Tuples

• List in alphabetic order the names of all instructors

select distinct name� from instructor� order by name;

• We may specify desc for descending order or asc for ascending order, for each attribute; ascending order is the default.
• Example: order by name desc
• Can sort on multiple attributes
• Example: order by dept_name, name

MATRUSRI

ENGINEERING COLLEGE

Where Clause Predicates

• SQL includes a between comparison operator
• Example: Find the names of all instructors with salary between $90,000 and $100,000 (that is,  $90,000 and  $100,000)
• select name�from instructor�where salary between 90000 and 100000;
• Tuple comparison

select name, course_id

from instructor, teaches

where (instructor.ID, dept_name) = (teaches.ID, ’Biology’);

MATRUSRI

ENGINEERING COLLEGE

Duplicates

• In relations with duplicates, SQL can define how many copies of tuples appear in the result.

​

• Multiset versions of some of the relational algebra operators – given multiset relations r₁ and r₂:

1. σ_θ (r₁): If there are c₁ copies of tuple t₁ in r₁, and t₁ satisfies selections σ_θ,, then there are c₁ copies of t₁ in σ_θ (r₁).

2. Π_A (r ): For each copy of tuple t₁ in r₁, there is a copy of tuple Π_A (t₁) in Π_A (r₁) where Π_A (t₁) denotes the projection of the single tuple t₁.

3. r₁ x r₂: If there are c₁ copies of tuple t₁ in r₁ and c₂ copies of tuple t₂ in r₂, there are c₁ x c₂ copies of the tuple t₁. t₂ in r₁ x r₂

MATRUSRI

ENGINEERING COLLEGE

Duplicates

• Example: Suppose multiset relations r₁ (A, B) and r₂ (C) are as follows:

r₁ = {(1, a) (2,a)} r₂ = {(2), (3), (3)}

• Then Π_B(r₁) would be {(a), (a)}, while Π_B(r₁) x r₂ would be

{(a,2), (a,2), (a,3), (a,3), (a,3), (a,3)}

• SQL duplicate semantics:

select A₁,_, A₂, ..., A_n� from r₁, r₂, ..., r_m� where P;

is equivalent to the multiset version of the expression:

​

MATRUSRI

ENGINEERING COLLEGE

Set Operations

1. Find courses that ran in Fall 2009 or in Spring 2010

(select course_id from section where sem = ‘Fall’ and year = 2009)� union�(select course_id from section where sem = ‘Spring’ and year = 2010)

​

2. Find courses that ran in Fall 2009 and in Spring 2010

(select course_id from section where sem = ‘Fall’ and year = 2009)� intersect�(select course_id from section where sem = ‘Spring’ and year = 2010)

​

3. Find courses that ran in Fall 2009 but not in Spring 2010

(select course_id from section where sem = ‘Fall’ and year = 2009)� except�(select course_id from section where sem = ‘Spring’ and year = 2010)

​

4. Find the salaries of all instructors that are less than the largest salary.

select distinct T.salary�from instructor as T, instructor as S�where T.salary < S.salary;

​

​

​

​

​

MATRUSRI

ENGINEERING COLLEGE

Set Operations

5. Find all the salaries of all instructors

select distinct salary�from instructor

​

6. Find the largest salary of all instructors.

(select “second query” )� except� (select “first query”)

​

Set operations in SQL are union, intersect, and except

Each of the above operations automatically eliminates duplicates

• To retain all duplicates use the corresponding multiset versions union all, intersect all and except all.�
• Suppose a tuple occurs m times in r and n times in s, then, it occurs:

m + n times in r union all s

min(m,n) times in r intersect all s

max(0, m – n) times in r except all s

MATRUSRI

ENGINEERING COLLEGE

Null Values

• It is possible for tuples to have a null value, denoted by null, for some of their attributes
• null signifies an unknown value or that a value does not exist.
• The result of any arithmetic expression involving null is null
• Example: 5 + null returns null
• The predicate is null can be used to check for null values.
• Example: Find all instructors whose salary is null.

select name� from instructor� where salary is null

MATRUSRI

ENGINEERING COLLEGE

Null Values and Three Valued Logic

• Three values – true, false, unknown
• Any comparison with null returns unknown
• Example: 5 < null or null <> null or null = null
• Three-valued logic using the value unknown:
• OR: (unknown or true) = true,� (unknown or false) = unknown� (unknown or unknown) = unknown
• AND: (true and unknown) = unknown, � (false and unknown) = false,� (unknown and unknown) = unknown
• NOT: (not unknown) = unknown
• “P is unknown” evaluates to true if predicate P evaluates to unknown

​

• Result of where clause predicate is treated as false if it evaluates to unknown

MATRUSRI

ENGINEERING COLLEGE

Aggregate Functions

These functions operate on the multi set of values of a column of a relation, and return a value

• sum(): Returns sum of values
• count(): Returns number of values
• avg(): Returns average value
• min(): Returns minimum value
• max(): Returns maximum value

​

• Find the average salary of instructors in the Computer Science department

select avg (salary)�from instructor�where dept_name= ’Comp. Sci.’;

• Find the total number of instructors who teach a course in the Spring 2010 semester

select count (distinct ID)�from teaches�where semester = ’Spring’ and year = 2010;

• Find the number of tuples in the course relation

select count (*) from course;

MATRUSRI

ENGINEERING COLLEGE

Aggregate Functions – Group By

Find the average salary of instructors in each department

select dept_name, avg (salary) as avg_salary�from instructor�group by dept_name;

Attributes in select clause outside of aggregate functions must appear in group by list

​

​

​

​

MATRUSRI

ENGINEERING COLLEGE

avg_salary

Aggregate Functions – Having Clause

Find the names and average salaries of all departments whose average salary is greater than 42000

​

select dept_name, avg (salary)

from instructor

group by dept_name

having avg (salary) > 42000;

​

Note: Predicates in the having clause are applied after the � formation of groups whereas predicates in the where � clause are applied before forming groups

​

​

MATRUSRI

ENGINEERING COLLEGE

Null Values and Aggregates

• Total all salaries

Select sum (salary )� from instructor;

• Above statement ignores null amounts
• Result is null if there is no non-null amount
• All aggregate operations except count(*) ignore tuples with null values on the aggregated attributes
• What if collection has only null values?
• count returns 0
• all other aggregates return null

​

MATRUSRI

ENGINEERING COLLEGE

Nested Sub queries

• SQL provides a mechanism for the nesting of sub queries. A sub query is a select-from-where expression that is nested within another query.
• The nesting can be done in the following SQL query�� select A₁, A₂, ..., A_n� from r₁, r₂, ..., r_m� where P

�

• A_i can be replaced be a sub query that generates a single value.
• r_i can be replaced by any valid sub query
• P can be replaced with an expression of the form:

B <operation> (sub query)

Where B is an attribute and <operation> to be defined later.

MATRUSRI

ENGINEERING COLLEGE

Sub queries in the Where Clause

A common use of sub queries is to perform tests:

• For set membership
• For set comparisons
• For set cardinality.

​

• Find courses offered in Fall 2009 and in Spring 2010

​

select distinct course_id

from section

where semester = ’Fall’ and year= 2009 and � course_id in (select course_id

from section

where semester = ’Spring’ and year= 2010);

​

​

MATRUSRI

ENGINEERING COLLEGE

Set Membership

• Find courses offered in Fall 2009 but not in Spring 2010

select distinct course_id

from section

where semester = ’Fall’ and year= 2009 and � course_id not in (select course_id

from section

where semester = ’Spring’ and year= 2010);

​

• Find the total number of (distinct) students who have taken course sections taught by the instructor with ID 10101

select count (distinct ID)

from takes

where (course_id, sec_id, semester, year) in � (select course_id, sec_id, semester, year

from teaches

where teaches.ID= 10101);

​

Note: Above query can be written in a much simpler manner. � The formulation above is simply to illustrate SQL features.

​

MATRUSRI

ENGINEERING COLLEGE

Set Comparison – “some” Clause

• Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department.

​

select distinct T.name

from instructor as T, instructor as S

where T.salary > S.salary and S.dept name = ’Biology’;

​

Same query using > some clause

​

select name

from instructor

where salary > some (select salary

from instructor

where dept name = ’Biology’);

MATRUSRI

ENGINEERING COLLEGE

Set Comparison – “all” Clause

• Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department.

select name

from instructor

where salary > all (select salary

from instructor

where dept name = ’Biology’);

MATRUSRI

ENGINEERING COLLEGE

Use of “exists” Clause

• Yet another way of specifying the query “Find all courses taught in both the Fall 2009 semester and in the Spring 2010 semester”

select course_id� from section as S� where semester = ’Fall’ and year = 2009 and � exists (select *� from section as T� where semester = ’Spring’ and year= 2010 � and S.course_id = T.course_id);

​

• Correlation name – variable S in the outer query
• Correlated subquery – the inner query

MATRUSRI

ENGINEERING COLLEGE

Use of “not exists” Clause

Find all students who have taken all courses offered in the Biology department.

select distinct S.ID, S.name

from student as S

where not exists ( (select course_id

from course

where dept_name = ’Biology’)

except

(select T.course_id

from takes as T

where S.ID = T.ID));

​

• First nested query lists all courses offered in Biology
• Second nested query lists all courses a particular student took

​

• Note that X – Y = Ø ⇔ X ⊆ Y
• Note: Cannot write this query using = all and its variants

​

​

MATRUSRI

ENGINEERING COLLEGE

Sub queries in the From Clause

• SQL allows a sub query expression to be used in the from clause

​

• Find the average instructors’ salaries of those departments where the average salary is greater than $42,000.”

select dept_name, avg_salary�from (select dept_name, avg (salary) as avg_salary� from instructor� group by dept_name)�where avg_salary > 42000;

​

• Another way to write above query

select dept_name, avg_salary�from (select dept_name, avg (salary) � from instructor� group by dept_name) as dept_avg (dept_name, avg_salary)

where avg_salary > 42000;

MATRUSRI

ENGINEERING COLLEGE

Modification of the Database

• Deletion of tuples from a given relation.
• Insertion of new tuples into a given relation
• Updating of values in some tuples in a given relation

​

MATRUSRI

ENGINEERING COLLEGE

Deletion

• Delete all instructors

delete from instructor;

​

• Delete all instructors from the Finance department� delete from instructor� where dept_name= ’Finance’;

​

• Delete all tuples in the instructor relation for those instructors associated with a department located in the Watson building.

delete from instructor� where dept name in (select dept name� from department� where building = ’Watson’);

MATRUSRI

ENGINEERING COLLEGE

Deletion

• Delete all instructors whose salary is less than the average salary of instructors

​

delete from instructor

where salary < (select avg (salary)

from instructor);

​

• Problem: as we delete tuples from deposit, the average salary changes
• Solution used in SQL:

1. First, compute avg (salary) and find all tuples to delete

2. Next, delete all tuples found above (without recomputing

avg or retesting the tuples)

​

​

​

MATRUSRI

ENGINEERING COLLEGE

Insertion

Add a new tuple to course

insert into course values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

​

or equivalently

�

insert into course (course_id, title, dept_name, credits) values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

​

Add a new tuple to student with tot_creds set to null

​

insert into student values (’3003’, ’Green’, ’Finance’, null);

MATRUSRI

ENGINEERING COLLEGE

Insertion

• Add all instructors to the student relation with tot_creds set to 0

insert into student� select ID, name, dept_name, 0� from instructor

​

• The select from where statement is evaluated fully before any of its results are inserted into the relation.

Otherwise queries like

insert into table1 select * from table1

would cause problem

MATRUSRI

ENGINEERING COLLEGE

Updates

• Query to increase salaries of instructors whose salary is over $100,000 by 3%, and all others by a 5%

Write two update statements:

update instructor� set salary = salary * 1.03� where salary > 100000;� update instructor� set salary = salary * 1.05� where salary <= 100000;

• The order is important

​

• Can be done better using the case statement
• update instructor set salary = case when salary <= 100000 then salary * 1.05� else salary * 1.03� end

MATRUSRI

ENGINEERING COLLEGE

Test for Absence of Duplicate Tuples

• The unique construct tests whether a subquery has any duplicate tuples in its result.
• The unique construct evaluates to “true” if a given subquery contains no duplicates .

​

• Find all courses that were offered at most once in 2009

select T.course_id�from course as T�where unique (select R.course_id� from section as R� where T.course_id= R.course_id � and R.year = 2009);

MATRUSRI

ENGINEERING COLLEGE

With Clause

• The with clause provides a way of defining a temporary relation whose definition is available only to the query in which the with clause occurs.
• Find all departments with the maximum budget �� with max_budget (value) as � (select max(budget)� from department)� select department.name� from department, max_budget� where department.budget = max_budget.value;

MATRUSRI

ENGINEERING COLLEGE

Complex Queries using With Clause

• Find all departments where the total salary is greater than the average of the total salary at all departments

​

with dept _total (dept_name, value) as

(select dept_name, sum(salary)

from instructor

group by dept_name),

dept_total_avg(value) as

(select avg(value)

from dept_total)

select dept_name

from dept_total, dept_total_avg

where dept_total.value > dept_total_avg.value;

​

MATRUSRI

ENGINEERING COLLEGE