RDBMS Lecture Notes Unit4 chapter12 VIEW

Description:
Welcome to the comprehensive guide on Relational Database Management System (RDBMS) concepts, tailored for final year B.Sc. Computer Science students affiliated with Alagappa University. This document covers fundamental principles and advanced topics in RDBMS, offering a structured approach to understanding databases in the context of modern computing. PDF content is prepared from the text book Learn Oracle 8I by JOSE A RAMALHO.

Key Topics Covered:

Main Topic : VIEW
Sub-Topic :
View Definition, Advantages and disadvantages, View Creation Syntax, View creation based on single table, view creation based on multiple table, Deleting View and View the definition of view
Target Audience:

Final year B.Sc. Computer Science students at Alagappa University seeking a solid foundation in RDBMS principles for academic and practical applications.
Previous Slides Link:

1. Data Integrity, Index, TAble Creation and maintenance https://www.slideshare.net/slideshow/lecture_notes_unit4_chapter_8_9_10_rdbms-for-the-students-affiliated-by-alagappa-university/270123800

2. Sequences : https://www.slideshare.net/slideshow/sequnces-lecture_notes_unit4_chapter11_sequence/270134792


About the Author:
Dr. S. Murugan is Associate Professor at Alagappa Government Arts College, Karaikudi. With 23 years of teaching experience in the field of Computer Science, Dr. S. Murugan has a passion for simplifying complex concepts in database management.

Disclaimer:
This document is intended for educational purposes only. The content presented here reflects the author’s understanding in the field of RDBMS as of 2024.

1. RDBMS -Unit IV
Chapter 12
VIEW
Prepared By
Dr. S.Murugan, Associate Professor
Department of Computer Science,
AlagappaGovernment Arts College, Karaikudi.
(Affiliated by AlagappaUniversity)
Mailid: [email protected]
Reference Book:
LEARN ORACLE 8i, JOSE A RAMALHO

2. VIEW
➢Aviewisavirtualorlogicaltablethatallowstheviewingor
manipulatingofthecontentsofoneormoretablesinawindow.
➢Aviewlookslikeandworkssimilarlytoanormaltable,except
thatitscontentscanbedrawnfromseveraldifferenttablesor
evenotherviews.
➢Thetablescontainingtheorigincolumnsarecalledbasetables.
➢Aview,unlikeatable,hasnophysicalspaceallocatedtoits
data.
➢Itiscreatedbyaquerythatusesoriginorbase tables
from which to extractdata.
➢Thebasetablescanbetablesorviews.

3. VIEW

4. ADVANTAGES OF VIEW
➢Theviewcanbecreatedwithspecificcolumnonly.
➢Thesameviewcanbeusedbydifferentusers.
➢Atableforgeneralusecanbedividedintospecific
viewsforcertainusers.
➢TheexecutionoftheSELECTcommandissimplified.
➢Thenamesofcolumnscanbechanged.
➢A view can be referenced in the following SQL
commands: COMMENT, DELETE, INSERT,
LOCK TABLE, UPDATE AND SELECT

5. Restrictions on Using a View
➢Insert,update,anddeleteoperationscannotbe
performedinaviewformedbycolumnsfrommore
thanonetable.
➢viewscannotmentionthenextvalorcurrvalpseudo
columns.
➢Arowcannotbeinsertedinaviewthathasabase
tablecontainingacolumnwiththeNOTNULL
restriction.

6. Creating a View
➢The command responsible for creating and modifying views is
CREATE VIEW.
➢REPLACE: Re-creates an existing view
➢FORCE: View created even when the base table does not exist.
➢NOFORCE: If the base table is existthen only the view is
created.
➢WITH READ ONLY:
SpecifiesthattheINSERT,UPDATE,orDELETEcommands
cannotbeexecutedintheview.

7. A View Based on a Single Table
A view can be created with all the columns of a table by
using *. The NEWDEP View can be created from
DEPARTMENT.

8. A View Based on a Single Table
Here, DEPARTMENT is a base table and NEWDEP is a
VIEW.

9. A View Based on Two Tables (Join View)
➢Aviewcanbecreatedbyusingtwotables.The
creationofaviewlinkingtheDEPARTMENT and
LOCATIONtables.Let’sexaminethecontentsofeach
ofthem.

10. A View Based on Two Tables (Join View)
➢Aviewcanbecreatedbyusingthefollowing
command.
➢Itdisplaystherecordsfrombothtablesthatcontainthesame
valueinthelocation_idfield.
➢Only11recordscorrespondingtothe11rowsofthe
DEPARTMENT tablearedisplayed,alongwiththe
regional_groupfieldoftheLOCATIONtableoftherowwith
samelocation_id.

11. A View Based on Two Tables (Join View)
➢Theresultofviewcanbedisplayedasfollows.

12. Using the SELECT Command in a View
A view functions as a normal table for most operations.
The SELECT command also works the same in a view.

13. Deleting a View
➢Todeletethedefinitionofaviewfromthedata
dictionary,usetheDROPVIEWcommandfollowed
bythenameoftheview.
➢Thiscommanddoesnotaffectthebasetables.
Syntax:
DROPVIEWname_of_view
➢Example:DROPVIEWgendep

14. Deleting a View
➢Todeletethedefinitionofaviewfromthedata
dictionary,usetheDROPVIEWcommandfollowed
bythenameoftheview.
➢Thiscommanddoesnotaffectthebasetables.
Syntax:
DROPVIEWname_of_view
➢Example:DROPVIEWgendep

15. Viewing the Definitions of the View
➢The definitions of a view are stored in a view called
USER_VIEWS in the data dictionary. The following
shows the structure of such a view:
➢SQL> describe user_views;
➢To see the name and text of the command that builds
this view, use the view_namecolumn and text:
SQL> select view_name, text from user_views;
VIEW_NAME TEXT
-------------------------------------
EMPBASIC select employee_id,
last_name,first_name,
salary from employee