Database Management System: Understanding Views and Their Uses, Slides of Introduction to Database Management Systems

Download Database Management System: Understanding Views and Their Uses and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database

Management

System

Lecture - 40

Dynamic Views Example

CREATE VIEW st_view1 AS (select stName, stFname, prName from student WHERE prName = 'MCS') View can be referred in SQL statements like tables

With Check Option

CREATE VIEW st_view2 AS (select stName, stFname, prName from student WHERE prName = ‘BCS') WITH CHECK OPTION

SELECT * FROM ST_VIEW

Update ST_VIEW2 set prName = ‘MCS’ Where stFname = ‘Loving’

Characteristics of Views

Different attribute names

CREATE VIEW st_view3 (name, abbaG, pata) as (select stname, stfname, stadres from student)

Characteristics of Views

Computed attributes Nesting of views CREATE VIEW enr_view AS (select * from enroll) CREATE VIEW enr_view1 as (select stId, crcode, smrks, mterm, smrks + mterm sessional from enr_view)

CREATE VIEW st_pr_view AS (select stName, stFname, student.prName, prcredits FROM student, program WHERE student.prname = program.prname)

SELEC * FROM st_pr_view

• SELECT * FROM st_view
• SELECT * FROM ST_VIEW
• SELECT * FROM ST_VIEW
• Select * from enr_view
• SELECT * FROM st_cr_enr_view

Updating Data

If single table, updation piece of cake (with check option) We can even insert data, BUT  Not through computed attribute  Cannot miss “Not NULL” attributes  One of the multiple tables  Not in case of aggregate functions

 ALTER VIEW st_view1 as (SELECT stId, stName, stFname, prName from student where prName = 'MCS’) WITH CHECK OPTION

 INSERT INTO st_view values ('S1044', ‘Ali Raza', ‘M. Raza ', 'BCS')