ZeePedia

Views, Data Independence, Security, Vertical and Horizontal Subset of a Table

<< Ordered, Dense, Sparse, Multi-Level Indices, Clustered, Non-clustered Indexes
Materialized View, Simple Views, Complex View, Dynamic Views >>
img
Database Management System (CS403)
VU
Lecture No. 39 and 40
Reading Material
"Database Systems Principles, Design and Implementation" written by Catherine Ricardo,
Maxwell Macmillan.
"Database Management System" by Jeffery A Hoffer
Overview of Lecture
Introduction to Views
o
Views, Data Independence, Security
o
Choosing a Vertical and Horizontal Subset of a Table
o
A View Using Two Tables
o
A View of a View
o
A View Using a Function
o
Updates on Views
o
Views
Views are generally used to focus, simplify, and customize the perception
each user has of the database. Views can be used as security mechanisms
by allowing users to access data through the view, without granting the users
permissions to directly access the underlying base tables of the view.
To Focus on Specific Data
Views allow users to focus on specific data that interests them and on the
specific tasks for which they are responsible. Unnecessary data can be left
out of the view. This also increases the security of the data because users
280
img
Database Management System (CS403)
VU
can see only the data that is defined in the view and not the data in the
underlying table.
A database view displays one or more database records on the same page. A
view can display some or all of the database fields. Views have filters to
determine which records they show. Views can be sorted to control the record
order and grouped to display records in related sets. Views have other options
such as totals and subtotals.
Most users interact with the database using the database views. A key to
creating a useful database is a well-chosen set of views. Luckily, while views
are powerful, they are also easy to create.
A "view" is essentially a dynamically generated "result" table that is put
together based upon the parameters you have defined in your query. For
example, you might instruct the database to give you a list of all the
employees in the EMPLOYEES table with salaries greater than 50,000 USD
per year. The database would check out the EMPLOYEES table and return
the requested list as a "virtual table".
Similarly, a view could be composed of the results of a query on several
tables all at once (sometimes called a "join"). Thus, you might create a view of
all the employees with a salary of greater than 50K from several stores by
281
img
Database Management System (CS403)
VU
accumulating the results from queries to the EMPLOYEES and STORES
databases. The possibilities are limitless.
You can customize all aspects of a view, including:
The name of the view
·
The fields that appear in the view
·
The column title for each field in the view
·
The order of the fields in the view
·
The width of columns in the view, as well as the overall width of the view
·
The set of records that appear in the view (Filtering)
·
The order in which records are displayed in the view (Sorting & Grouping)
·
Column totals for numeric and currency fields (Totaling & Subtotaling)
·
The physical schema for a relational database describes how the relations in
the conceptual schema are stored, in terms of the file organizations and
indexes used. The conceptual schema is the collection of schemas of the
relations stored in the database. While some relations in the conceptual
schema can also be exposed to applications, i.e., be part of the external
schema of the database, additional relations in the external schema can be
defined using the view mechanism. The view mechanism thus provides the
support for logical data independence in the relational model. That is, it can
be used to define relations in the external schema that mask changes in the
conceptual schema of the database from applications. For example, if the
schema of a stored relation is changed, we can define a view with the old
schema, and applications that expect to see the old schema can now use this
view. Views are also valuable in the context of security: We can define views
282
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