Modification of the Database




We have restricted our attention until now to the extraction of information from
the database. Now, we show how to add,remove, or change information with SQL.

1. Deletion

A delete request is expressed in much the same way as a query. We can delete only
whole tuples; we cannot delete values on only particular attributes. SQL expresses
a deletion by

delete from r
where P;

Topics You May Be Interested In
Modeling Of Union Types Using Categories The Natural Join
Using High-level Conceptual Data Models For Database Design Entity Types, Entity Sets, Keys, And Value Sets-2
Data Mining And Information Retrieval Refining The Er Design For The Company Database
History Of Database Systems Relationship Types Of Degree Higher Than Two
Sql Data Definition Access Control, User Accounts, And Database Audits

where P represents a predicate and r represents a relation. The delete statement
first finds all tuples t in r for which P(t) is true, and then deletes them from r. The
where clause can be omitted, in which case all tuples in r are deleted.

Note that a delete command operates on only one relation. If we want to delete
tuples from several relations, we must use one delete command for each relation.
The predicate in the where clause may be as complex as a select command’s
where clause. At the other extreme, the where clause may be empty. The request

delete from instructor;

deletes all tuples from the instructor relation. The instructor relation itself still
exists, but it is empty.

Topics You May Be Interested In
View Of Data Relational Query Languages
Constraints And Characteristics Of Specialization And Generalization Hierarchies Additional Basic Operations-string Operations
Using High-level Conceptual Data Models For Database Design Entity Types, Entity Sets, Keys, And Value Sets-2
View Of Data Sensitive Data And Types Of Disclosures
Chapter 11 Object And Object-relational Databases Where Clause Predicates

Here are examples of SQL delete requests:

• Delete all tuples in the instructor relation pertaining to instructors in the
Finance department.

delete from instructor
where dept_name= ’Finance’;

• Delete all instructors with a salary between $13,000 and $15,000.

Topics You May Be Interested In
Specialization And Generalization Schema Diagrams
Data Storage And Querying Overview Of The Sql Query Language
Specialty Databases A Sample Database Application
Introduction To Database Security Issues Entity Types, Entity Sets, Keys, And Value Sets-2
Introduction To Database Security Issues Relationship Types Of Degree Higher Than Two

delete from instructor
where salary between 13000 and 15000;

• 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
’);

Topics You May Be Interested In
Modeling Of Union Types Using Categories Introduction To Database Security Issues
Using High-level Conceptual Data Models For Database Design Schema Diagrams
Purpose Of Database Systems Relational Operations
Introduction To Transaction Processing Additional Basic Operations-the Rename Operation
Database Design Null Values

This delete request first finds all departments located in Watson, and then
deletes all instructor tuples pertaining to those departments.

Note that, although we may delete tuples from only one relation at a time,
we may reference any number of relations in a select-from-where nested in the
where clause of a delete. The delete request can contain a nested select that
references the relation from which tuples are to be deleted. For example, suppose
that we want to delete the records of all instructors with salary below the average
at the university. We could write:

delete from instructor
where salary< (select avg (salary)

from instructor);

Topics You May Be Interested In
What Is Database Management System Basic Structure Of Sql Queries
Database Languages Entity Types, Entity Sets, Keys, And Value Sets-2
Relational Databases Relationship Types, Relationship Sets, Roles, And Structural Constraints
Introduction To Database Security Issues Example Of Notation- Uml Class Diagrams
Xml: Extensible Markup Language Aggregate Functions

The delete statement first tests each tuple in the relation instructor to check
whether the salary is less than the average salary of instructors in the univer?sity. Then, all tuples that fail the test— that is, represent an instructor with a
lower-than-average salary—are deleted. Performing all the tests before perform?ing any deletion is important—if some tuples are deleted before other tuples have been tested, the average salary may change, and the final result of the delete
would depend on the order in which the tuples were processed!

2. Insertion

To insert data into a relation, we either specify a tuple to be inserted or write a
query whose result is a set of tuples to be inserted. Obviously, the attribute values
for inserted tuples must be members of the corresponding attribute’s domain.
Similarly, tuples inserted must have the correct number of attributes.

The simplest insert statement is a request to insert one tuple. Suppose that
we wish to insert the fact that there is a course CS-437 in the Computer Science
department with title “Database Systems”, and 4 credit hours. We write:

Topics You May Be Interested In
Example Of Other Notation: Representing Specialization And Generalization In Uml Class Diagrams Overview Of Object Database Concepts
Data Mining And Information Retrieval The Natural Join
Database Schema Weak Entity Types
Keys Sensitive Data And Types Of Disclosures
Chapter 11 Object And Object-relational Databases Access Control, User Accounts, And Database Audits

insert into course

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

In this example, the values are specified in the order in which the corresponding
attributes are listed in the relation schema. For the benefit of users who may not
remember the order of the attributes, SQL allows the attributes to be specified as
part of the insert statement. For example, the following SQL insert statements are
identical in function to the preceding one:

insert into course (course_id, title, dept_name, credits)

Topics You May Be Interested In
Constraints And Characteristics Of Specialization And Generalization Hierarchies Keys
A Sample University Eer Schema, Design Choices, And Formal Definitions Basic Structure Of Sql Queries
Using High-level Conceptual Data Models For Database Design Additional Basic Operations-string Operations
Database Design Entity Types, Entity Sets, Attributes, And Keys-1
Data Mining And Information Retrieval Null Values

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

 

insert into course (title, course_id, credits, dept_name)

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

Topics You May Be Interested In
Characteristics Of The Database Approach Database Users And Administrators
Constraints And Characteristics Of Specialization And Generalization Hierarchies The Natural Join
View Of Data A Sample Database Application
Data Storage And Querying Access Control, User Accounts, And Database Audits
Transaction Management Aggregate Functions

More generally, we might want to insert tuples on the basis of the result of a
query. Suppose that we want to make each student in the Music department who
has earned more than 144 credit hours, an instructor in the Music department,
with a salary of $18,000. We write:

insert into instructor

select ID, name, dept_name, 18000
from student
where dept_name = ’Music’ and tot_cred > 144;

Instead of specifying a tuple as we did earlier in this section, we use a select to
specify a set of tuples. SQL evaluates the select statement first, giving a set of
tuples that is then inserted into the instructor relation. Each tuple has an ID, a
name, a dept name (Music), and an salary of $18,000.

Topics You May Be Interested In
Subclasses, Superclasses, And Inheritance Database Users And Administrators
Using High-level Conceptual Data Models For Database Design Keys
Relational Databases Introduction To Database Security Issues
Data Storage And Querying Initial Conceptual Design Of The Company Database
Transaction Management Relationship Between Information Security Versus Information Privacy

It is important that we evaluate the select statement fully before we carry
out any insertions. If we carry out some insertions even as the select statement is
being evaluated, a request such as:

insert into student

select *
from student;

might insert an infinite number of tuples, if the primary key constraint on student
were absent. Without the primary key constraint, the request would insert the
first tuple in student again, creating a second copy of the tuple. Since this second
copy is part of student now, the select statement may find it, and a third copy
would be inserted into student. The select statement may then find this third
copy and insert a fourth copy, and so on, forever. Evaluating the select statement
completely before performing insertions avoids such problems. Thus, the above
insert statement would simply duplicate every tuple in the student relation, if the
relation did not have a primary key constraint.

Topics You May Be Interested In
Database-system Applications Initial Conceptual Design Of The Company Database
A Sample University Eer Schema, Design Choices, And Formal Definitions Weak Entity Types
Using High-level Conceptual Data Models For Database Design Example Of Notation- Uml Class Diagrams
View Of Data Database Security And The Dba
Additional Basic Operations-string Operations Set Operations-introduction

Our discussion of the insert statement considered only examples in which
a value is given for every attribute in inserted tuples. It is possible for inserted
tuples to be given values on only some attributes of the schema. The remaining
attributes are assigned a null value denoted by null. Consider the request:

insert into student

values (’3003’, ’Green’, ’Finance’, null);

The tuple inserted by this request specified that a student with ID “3003” is in the
Finance department, but the tot cred value for this student is not known. Consider
the query:

Topics You May Be Interested In
Characteristics Of The Database Approach Using High-level Conceptual Data Models For Database Design
Database-system Applications Relational Databases
Constraints And Characteristics Of Specialization And Generalization Hierarchies Basic Structure Of Sql Queries
A Sample University Eer Schema, Design Choices, And Formal Definitions Entity Types, Entity Sets, Attributes, And Keys-1
Using High-level Conceptual Data Models For Database Design Weak Entity Types

select student
from student
where tot_cred > 45;

Since the tot_cred value of student “3003” is not known, we cannot determine
whether it is greater than 45.

Most relational database products have special “bulk loader” utilities to insert
a large set of tuples into a relation. These utilities allow data to be read from
formatted text files, and can execute much faster than an equivalent sequence of
insert statements.

3. Updates

Topics You May Be Interested In
Database Design Introduction To Database Security Issues
Database Users And Administrators Relational Operations
Structure Of Relational Databases Basic Structure Of Sql Queries
Introduction To Database Security Issues Relationship Types Of Degree Higher Than Two
Introduction To Database Security Issues Database Security And The Dba

In certain situations, we may wish to change a value in a tuple without changing
all values in the tuple. For this purpose, the update statement can be used. As we
could for insert and delete, we can choose the tuples to be updated by using a
query.

Suppose that annual salary increases are being made, and salaries of all in?structors are to be increased by 5 percent. We write:

update instructor
set salary= salary * 1.05;

The preceding update statement is applied once to each of the tuples in instructor
relation.

If a salary increase is to be paid only to instructors with salary of less than
$70,000, we can write:

update instructor
set salary = salary * 1.05
where salary < 70000;

In general, the where clause of the update statement may contain any construct
legal in the where clause of the select statement (including nested selects). As
with insert and delete, a nested select within an update statement may reference
the relation that is being updated. As before, SQL first tests all tuples in the relation
to see whether they should be updated, and carries out the updates afterward.
For example, we can write the request “Give a 5 percent salary raise to instructors
whose salary is less than average” as follows:

update instructor
set salary = salary * 1.05
where salary < (select avg (salary)

from instructor);

Let us now suppose that all instructors with salary over $100,000 receive a
3 percent raise, whereas all others receive a 5 percent raise. We could write two
update statements:

update instructor
set salary = salary * 1.03
where salary > 100000;

update instructor
set salary = salary * 1.05
where salary <= 100000;

Note that the order of the two update statements is important. If we changed the
order of the two statements, an instructor with a salary just under $100,000 would
receive an over 8 percent raise.

SQL provides a case construct that we can use to perform both the updates
with a single update statement, avoiding the problem with the order of updates.

update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end

The general form of the case statement is as follows.

case
when pred1 then result1
when pred2 then result2
...
when predn then resultn
else result0
end

The operation returns resulti , where i is the first of pred1, pred2,..., predn that is
satisfied; if none of the predicates is satisfied, the operation returns result0. Case
statements can be used in any place where a value is expected.

Scalar subqueries are also useful in SQL update statements, where they can be
used in the set clause. Consider an update where we set the tot cred attribute of
each student tuple to the sum of the credits of courses successfully completed by
the student. We assume that a course is successfully completed if the student has
a grade that is not ’F’ or null. To specify this update, we need to use a subquery
in the set clause, as shown below:

update student S
set tot_cred = (
select sum(credits)
from takes natural join course
where S.ID= takes.ID and
takes.grade <> ’F’ and
takes.grade is not null);

Observe that the subquery uses a correlation variable S from the update statement.
In case a student has not successfully completed any course, the above update
statement would set the tot cred attribute value to null. To set the value to 0
instead, we could use another update statement to replace null values by 0; a
better alternative is to replace the clause “select sum(credits)” in the preceding
subquery by the following select clause using a case expression:

select case
when sum(credits) is not null then sum(credits)
else 0
end



Frequently Asked Questions

+
Ans: SQL provides a mechanism for nesting subqueries. A subquery is a select-from where expression that is nested within another query. view more..
+
Ans: SQL provides a mechanism for nesting subqueries. A subquery is a select-from where expression that is nested within another query. view more..
+
Ans: The main idea of Sampling Algorithm is to select a small sample , one that fits in main memory , of the database of transaction view more..
+
Ans: We have restricted our attention until now to the extraction of information from the database. Now, we show how to add,remove, or change information with SQL. view more..
+
Ans: We introduced the natural join operation. SQL provides other forms of the join operation, including the ability to specify an explicit join predicate, and the ability to include in the result tuples that are excluded by natural join. We shall discuss these forms of join in this section. view more..
+
Ans: In our examples up to this point, we have operated at the logical-model level. That is, we have assumed that the relations in the collection we are given are the actual relations stored in the database. view more..
+
Ans: A transaction consists of a sequence of query and/or update statements. view more..
+
Ans: Integrity constraints ensure that changes made to the database by authorized users do not result in a loss of data consistency. view more..




Rating - 4/5
513 views

Advertisements