Updating Multiple Tables, Transaction Management

<< Materialized View, Simple Views, Complex View, Dynamic Views

Transactions and Schedules, Concurrent Execution, Serializability, Lock-Based Concurrency Control, Deadlocks >>

Database Management System (CS403)

Lecture No. 41

Reading Material

"Database Systems Principles, Design and Implementation" written by Catherine Ricardo,

Maxwell Macmillan.

"Database Management System" by Jeffery A Hoffer

Overview of Lecture

o Indexes

o Index Classification

In our previous lecture we were discussing the views. Views play an important role in

database. At this layer database is available to the users. The user needs to know that

they are dealing with views; it is infact virtual for them. It can be used to implement

security. We were discussing dynamic views whose data is not stored as such.

Updating Multiple Tables

We can do this updation of multiple views by doing it one at a time. It means that

while inserting values in different tables, it can only be done one at a time. We will

now see an example of this as under:

CREATE VIEW st_pr_view1 (a1, a2, a3, a4) AS (select stId, stName,

program.prName, prcredits from student, program WHERE student.prName =

program.prName)

In this example this is a join statement

We will now enter data in the table

insert into st_pr_view1 (a3, a4) values ('MSE', 110)

We will now see the program table after writing this SQL statement as the data

has been stored in the table.

Select * from program

288

Database Management System (CS403)

In this example the total semester is NULL as this attribute was not defined in view

creation statement, so then this value will remain NULL. We will now see another

example. In this we have catered for NOT NULL.

insert into st_pr_view1 (a1, a2) values (`S1043', `Bilal Masood')

SELECT * from student

Materialized Views

A pre-computed table comprising aggregated or joined data from fact and possibly

dimensions tables. Also known as summary or aggregate table.Views are virtual

tables. In which query is executed every time .For complex queries involving large

number of join rows and aggregate functions, so it is problematic. Its solution is

materialized views also called indexed views created through clustered index.

Creating a clustered index on a view stores the result set built at the time the index is

created. An indexed view also automatically reflects modifications made to the data in

289

Database Management System (CS403)

the base tables after the index is created, the same way an index created on a base

table does. Create indexes only on views where the improved speed in retrieving

results outweighs the increased overhead of making modifications.

Materialized views are schema objects that can be used to summarize, compute,

replicate, and distribute data. They are suitable in various computing environments

such as data warehousing, decision support, and distributed or mobile computing:

In data warehouses, materialized views are used to compute and store

aggregated data such as sums and averages. Materialized views in these

environments are typically referred to as summaries because they store

summarized data. They can also be used to compute joins with or without

aggregations. If compatibility is set to Oracle9i or higher, then materialized

views can be used for queries that include filter selections.

Cost-based optimization can use materialized views to improve query

performance by automatically recognizing when a materialized view can and

should be used to satisfy a request. The optimizer transparently rewrites the

request to use the materialized view. Queries are then directed to the

materialized view and not to the underlying detail tables or views.

In distributed environments, materialized views are used to replicate data at

distributed sites and synchronize updates done at several sites with conflict

resolution methods. The materialized views as replicas provide local access to

data that otherwise have to be accessed from remote sites.

In mobile computing environments, materialized views are used to download a

subset of data from central servers to mobile clients, with periodic refreshes

from the central servers and propagation of updates by clients back to the

central servers.

Materialized views are similar to indexes in several ways:

They consume storage space.

They must be refreshed when the data in their master tables changes.

290

Database Management System (CS403)

They improve the performance of SQL execution when they are used for

query rewrites.

Their existence is transparent to SQL applications and users.

Unlike indexes, materialized views can be accessed directly using a SELECT

statement. Depending on the types of refresh that are required, they can also be

accessed directly in an INSERT, UPDATE, or DELETE statement.

A materialized view can be partitioned. You can define a materialized view on a

partitioned table and one or more indexes on the materialized view.

Transaction Management

A transaction can be defined as an indivisible unit of work comprised of several

operations, all or none of which must be performed in order to preserve data integrity.

For example, a transfer of Rs 1000 from your checking account to your savings

account would consist of two steps: debiting your checking account by Rs1000 and

crediting your savings account with Rs1000. To protect data integrity and consistency

and the interests of the bank and the customer these two operations must be applied

together or not at all. Thus, they constitute a transaction.

Properties

transaction

All transactions share these properties: atomicity, consistency, isolation, and

durability (represented by the acronym ACID).

Atomicity: This implies indivisibility; any indivisible operation (one which

will either complete fully or not at all) is said to be atomic.

Consistency: A transaction must transition persistent data from one consistent

state to another. If a failure occurs during processing, the data must be restored

to the state it was in prior to the transaction.

Isolation: Transactions should not affect each other. A transaction in progress,

not yet committed or rolled back (these terms are explained at the end of this

section), must be isolated from other transactions. Although several

transactions may run concurrently, it should appear to each that all the others

291

Database Management System (CS403)

completed before or after it; all such concurrent transactions must effectively

end in sequential order.

Durability: Once a transaction has successfully committed, state changes

committed by that transaction must be durable and persistent, despite any

failures that occur afterwards.

A transaction can thus end in two ways: a commit, the successful execution of each

step in the transaction, or a rollback, which guarantees that none of the steps are

executed due to an error in one of those steps.

292

Table of Contents: