View of Data

The view level provides the “view of data” to the users and hides the irrelevant details such as data relationship, database schema, constraints, security, etc. from the user and helps in easy and efficient user-database interaction.

Benefits of a Database View

There are many benefits to using views.  Which are:-

• Enforce Business Rules – Use views to define business rules, such as when an item is active, or what is meant by “popular.”  By placing complicated or misunderstood business logic into the view, and be sure to present a unified description of the data.  This increases use and quality.
• Consistency – Simplify complicated query logic and calculations by hiding it behind the view’s definition.  This makes for fewer mistakes and easier maintenance of code.
• Security – Restrict access to a table. Allow users to access non-confidential data via views. 

For example, restrict access to the employee table, that contains social security numbers, but allow access to a view containing the name and phone number.

• Simplicity – many tables Database possess complex relationships, which can be difficult to navigate.  But in views to provide a “flattened” view of the database for reporting or ad-hoc queries.
• Space – Views take little space, as the data is stored once in the source table.  Some DBMS create an index on a view, so in some cases, views do take up more space than the definition.

 Disadvantages of Views

• Performance –a simple query in a view is a hugely complex job for the database engine, because each time a view is referenced, the query used to define it, is rerun.
• Modifications – All views not support INSERT, UPDATE, or DELETE operations because the primary key and required fields must be required. Complex multi-table views are generally read-only.

Data Abstraction in DBMS

• Database systems are designed from complex data structures.
• Easy user interaction with the database, the developers hide internal irrelevant details from users.
• This process of hiding irrelevant details from users is called data abstraction.
• Many of the users interacting with the database system are not that computer trained to understand the complex data structures of the database system.

Levels of abstraction:

The three-schema architecture defines the view of data at three levels:

1. View level (external level)
2. Logical level (conceptual level)
3. Physical level (internal level)

Three-Schemas Architecture 

1. Physical Level/ Internal Level

• The physical or the internal level schema describes how the data is stored in the hardware.
• It also describes how the data can be accessed.
• The physical level shows the data abstraction at the lowest level and it has complex data structures.
• Only the database administrator operates at this level.
• It contains multiple occurrences of multiple types of internal records.
• It helps you to keeps information about the actual representation of the entire database. Like the actual storage of the data on the disk in the form of records
• Internal schema views a physical device as a collection of physical pages
• Described as blocks of storage (bytes, gigabytes, terabytes, etc.) in memory.
• These details are often hidden from the programmers.

2. Logical Level/ Conceptual Level

• It is a level above the physical level (middle layer).
• The data is stored in the form of the entity set, entities, their data types, the relationship among the entity sets.
• User operations performed to retrieve or modify the data with certain constraints.
• Users are restricted to access some particular parts of the database as Security and integrity.
• It is the developer and database administrator who operates at the logical or conceptual level.
• The conceptual schema describes the Database structure of the whole database for the community of users.
• This logical level comes between the user level and the physical storage view. However, there is only a single conceptual view of a single database.

3. View Level/ User level/ External level 

• It is the highest level of data abstraction and exhibits only a part of the whole database.
• Users interact with the system with the help of GUI and enter the details at the screen, they are not aware of how the data is stored and what data is stored; such details are hidden from them.

• The view level can describe many views of the same data.
• The external schema describes the segment of the database which is needed for a certain user group and hides the remaining details from the database from the specific user group
• The user retrieves the information using a different application from the database.
• An external schema describes the part of the database that a specific user is interested in.
• It hides the unrelated details of the database from the user.
• For example, a user from the sales department will see only sales-related data.

Definition of schema:

• Schema is the overall design of the entire database.
• The schema of the database is not changed frequently.
• Schema is of three types:
• Physical schema, logical schema, and view schema.
• For example: In the following diagram, we have a schema that shows the relationship between three tables: Course, Student, and Section.
  •  The diagram only shows the design of the database, it doesn’t show the data present in those tables.
  •  Schema is only a structural view (design) of a database as shown in the diagram below.

Schemas

• At the physical level is called physical schema, it defines how the data stored in blocks of storage is described at this level.
• At the logical level is called logical schema, it defines programmers and database administrators work at this level and described as certain types of data records gets stored in data structures and the internal details such as the implementation of the data structure are hidden at this level.
• At view, the level is called view schema. It describes end-user interaction with database systems.

Advantages Database Schema

• Manage data independent of the physical storage
• Faster Migration to new graphical environments
• DBMS Architecture allows you to make changes on the presentation level without affecting the other two layers
• As each tier is separate, it is possible to use different sets of developers
• It is more secure as the client doesn't have direct access to the database business logic
• In case of the failure of the one-tier no data loss as you are always secure by accessing the other tier

Disadvantages Database Schema

• Complete DB Schema is a complex structure that is difficult to understand for every one
• Difficult to set up and maintain
• The physical separation of the tiers can affect the performance of the Database

DBMS Instance

Definition of instance: The data stored in a database at a particular moment of time is called an instance of the database. 

Defines the variable declarations in tables that belong to a particular database.

The value of these variables at a moment of time is called the instance of that database.

For example, in the table student database, the table has 100 records, so today the instance of the database has 100 records. Add another 100 records to this table then the instance of the database has 200 records in the table. In short, at a particular moment, the data stored in the database is called the instance, which changes over time when we add or delete data from the database.

 ==============================================================