ZeePedia Add to Favourites   |   Contact us


Database Management Systems

<<< Previous Materialized View, Simple Views, Complex View, Dynamic Views Next >>>
 
img
Database Management System (CS403)
VU
that give a group of user's access to just the information they are allowed to
see. For example, we can define a view that allows students to see other
students' name and age but not their GPA, and allow all students to access
this view, but not the underlying Students table.
There are two ways to create a new view in your database. You can:
Create a new view from scratch.
·
Or, make a copy of an existing view and then modify it.
·
Characteristics /Types of Views:
We have a number of views type of which some of the important views types are
listed below:
·
Materialized View
·
Simple Views
·
Complex View
·
Dynamic Views.
A materialized view is a replica of a target master from a single point in time.
The master can be either a master table at a master site or a master
materialized view at a materialized view site. Whereas in multi-master
replication tables are continuously updated by other master sites, materialized
views are updated from one or more masters through individual batch
updates, known as a refreshes, from a single master site or master
refreshes
materialized view site
Simple Views
283
img
Database Management System (CS403)
VU
As defined earlier simple views are created from tables and are used for
creating secure manipulation over the tables or structures of the database.
Views make the manipulations easier to perform on the database.
Complex Views
Complex views are by definition views of type which may comprise of many of
elements, such as tables, views sequences and other similar objects of the
database. When talking about the views we can have views of one table,
views of one table and one view, views of multiple tables views of multiple
views and so on...
Dynamic Views
Dynamic views are those types of views for which data is not stored and the
expressions used to build the view are used to collect the data dynamically.
These views are not executed only once when they are referred for the first
time, rather they are created and the data contained in such views is updated
every time the view is accessed or used in any other view or query.
Dynamic views generally are complex views, views of views, and views of
multiple tables.
An example of a dynamic view creation is given below:
CREATE VIEW st_view1 AS (select stName, stFname, prName
FROM student
WHERE prName = 'MCS')
284
img
Database Management System (CS403)
VU
Views can be referred in SQL statements like tables
We can have view created on functions and other views as well. Where the
function used for the view creation and the other nested view will be used as a
simple table or relation.
Examples:
View Using another View
CREATE VIEW CLASSLOC2
AS SELECT COURSE#, ROOM
FROM CLASSLOC
View Using Function
CREATE VIEW CLASSCOUNT(COURSE#, TOTCOUNT)
AS SELECT COURSE#,
COUNT(*)
FROM ENROLL
GROUP BY COURSE#;
Dynamic Views
SELECT * FROM st_view1
With Check Option
CREATE VIEW st_view2
AS (SELECT stName, stFname, prName FROM student WHERE prName = `BCS')
WITH CHECK OPTION
UPDATE ST_VIEW1 set prName = `BCS'
Where stFname = `Loving'
285
img
Database Management System (CS403)
VU
SELECT * from ST_VIEW1
SELECT * FROM ST_VIEW2
Update ST_VIEW2 set prName = `MCS'
Where stFname = `Loving'
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)
Select * from enr_view1
286
img
Database Management System (CS403)
VU
Deleting Views:
A view can be dropped using the DROP VIEW command, which is just like
DROP TABLE.
Updates on Views
Updating a view is quite simple and is performed in the same way as we
perform updates on any of the database relations. But this simplicity is limited
to those views only which are created using a single relation. Those views
which comprise of multiple relations the updation are hard to perform and
needs additional care and precaution.
As we know that the views may contain some fields which are not the actual
data fields in the relation but may also contain computed attributes. So update
or insertions in this case are not performed through the views created on
these tables.
287
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