ZeePedia

Updating Multiple Tables, Transaction Management

<< Materialized View, Simple Views, Complex View, Dynamic Views
Transactions and Schedules, Concurrent Execution, Serializability, Lock-Based Concurrency Control, Deadlocks >>
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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
of
a
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
img
Database Management System (CS403)
VU
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:
  1. Introduction to Databases and Traditional File Processing Systems
  2. Advantages, Cost, Importance, Levels, Users of Database Systems
  3. Database Architecture: Level, Schema, Model, Conceptual or Logical View:
  4. Internal or Physical View of Schema, Data Independence, Funct ions of DBMS
  5. Database Development Process, Tools, Data Flow Diagrams, Types of DFD
  6. Data Flow Diagram, Data Dictionary, Database Design, Data Model
  7. Entity-Relationship Data Model, Classification of entity types, Attributes
  8. Attributes, The Keys
  9. Relationships:Types of Relationships in databases
  10. Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes
  11. Inheritance Is, Super types and Subtypes, Constraints, Completeness Constraint, Disjointness Constraint, Subtype Discriminator
  12. Steps in the Study of system
  13. Conceptual, Logical Database Design, Relationships and Cardinalities in between Entities
  14. Relational Data Model, Mathematical Relations, Database Relations
  15. Database and Math Relations, Degree of a Relation
  16. Mapping Relationships, Binary, Unary Relationship, Data Manipulation Languages, Relational Algebra
  17. The Project Operator
  18. Types of Joins: Theta Join, Equi–Join, Natural Join, Outer Join, Semi Join
  19. Functional Dependency, Inference Rules, Normal Forms
  20. Second, Third Normal Form, Boyce - Codd Normal Form, Higher Normal Forms
  21. Normalization Summary, Example, Physical Database Design
  22. Physical Database Design: DESIGNING FIELDS, CODING AND COMPRESSION TECHNIQUES
  23. Physical Record and De-normalization, Partitioning
  24. Vertical Partitioning, Replication, MS SQL Server
  25. Rules of SQL Format, Data Types in SQL Server
  26. Categories of SQL Commands,
  27. Alter Table Statement
  28. Select Statement, Attribute Allias
  29. Data Manipulation Language
  30. ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product
  31. Inner Join, Outer Join, Semi Join, Self Join, Subquery,
  32. Application Programs, User Interface, Forms, Tips for User Friendly Interface
  33. Designing Input Form, Arranging Form, Adding Command Buttons
  34. Data Storage Concepts, Physical Storage Media, Memory Hierarchy
  35. File Organizations: Hashing Algorithm, Collision Handling
  36. Hashing, Hash Functions, Hashed Access Characteristics, Mapping functions, Open addressing
  37. Index Classification
  38. Ordered, Dense, Sparse, Multi-Level Indices, Clustered, Non-clustered Indexes
  39. Views, Data Independence, Security, Vertical and Horizontal Subset of a Table
  40. Materialized View, Simple Views, Complex View, Dynamic Views
  41. Updating Multiple Tables, Transaction Management
  42. Transactions and Schedules, Concurrent Execution, Serializability, Lock-Based Concurrency Control, Deadlocks
  43. Incremental Log with Deferred, Immediate Updates, Concurrency Control
  44. Serial Execution, Serializability, Locking, Inconsistent Analysis
  45. Locking Idea, DeadLock Handling, Deadlock Resolution, Timestamping rules