Database Management System Lecture 41: Updating Multiple Tables and Materialized Views, Slides of Introduction to Database Management Systems

Download Database Management System Lecture 41: Updating Multiple Tables and Materialized Views and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database

Management

System

Lecture - 41

Updating Multiple tables

One at a time CREATE VIEW st_pr_view1 (a1, a2, a3, a4) AS (select stId, stName, program.prName, prcredits from student, program WHERE student.prName = program.prName)

insert into st_pr_view1 (a1, a2) values (‘S1043', ‘Bilal Masood’) SELECT * from student

Interesting Thingi Select * from st_pr_view

Materialized Views

Views are virtual tables Query executed every time For complex queries involving large number of join rows and aggregate functions Problematic

Materialized Views

An indexed view also automatically reflects modifications made to the data in the base tables after the index is created, the same way an index created on a base table does.

Materialized Views

Create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications.

Materialized Views

create unique clustered index stdview_ind1 on st_view (stfname)

Partitioned Views

Lets not discuss it

Idea is, data lying at multiple places and combined in a view

Same table partitioned horizontally based on some condition

Transaction Management

Transaction Management

Comprises

Database Recovery Concurrency Control

Database

Management

System

Lecture - 41