Schema in dbms Tutorial Study Material Notes with Examples

Schema in dbms Tutorial Study Material Notes with Examples

What is Schema

A schema is also known as database schema. It is a logical design of the database and a database instance is a snapshot of the data n the database at a given instant of time. A relational schema consists of a list of attributes and their corresponding domains.

 Types of Schema

It can be classified into three parts, according to the levels of abstraction Physical/internal Schema Describes the database design at the physics level.

Logical/Conceptual Schema/Community User View Describes the database design at the logical level.

Sub-schemas/View/External Schema Describes different views of the database, views may be queried, combined in queries with base relation used to define other views in general, not updated freely.

Schema Architecture

Data Independence

Possibility to change the schema at one level without having to change it at the next higher level (nor having to change programs that access it at the higher level).

There are two parts of data independence

Logical Data Independence Refers to the immunity of the external schema to changes in the conceptual schema (i.e., community schema) e.g., add new record or field.

Physical Data independence:  Refers to the immunity of the conceptual to the conceptual  schema to changes in the internal schema,  e.g., addition of an index should not affect existing one..

Database Functions and Application Functions

Application Program Functions

To be programmed in  application programs,

Database Functions or DBMS Functions

Supplied by the DBMS and invoked in application programs.


Database Design Phases

Handbook of CS and IT
Handbook of CS and IT


One execution of a user program (executing the same programs several :Ties corresponds to several transactions). Basic unit of change as seen by the DBMS.

OLTP (On-Line Transaction Processing) applications (e.g., banking and alien systems) with multiple simultaneous users.

 Functionality of DBMS

  1. Concurrency control 2. Backup and recovery
  2. Redundancy management 4. Access control
  3. Performance optimization
  4. Metadata management
  5. Active features (rules, triggers)

 Concurrency Control

It is responsible for ensuring correctness of competing accesses to same data. one single unit.One or more Structure Query Language (SQL) statements altogether treated as one single unit.

Correctness of data requires four desirable properties (ACID. properties) e.g., concurrency control means there should not be two simultaneous withdrawals  from the same bank account or there should not be multiple reservation of the same airplane seat.

            Backup and Recovery

  • Facilities for recovering from hardware and software failures.
  • If the computer system fails during a complex update program database must be resumed, where it was interrupted so that its full effect is recorded in the database.
  • In a multiuser environment, it is more complex and important.

Redundancy Management

Redundancy means storing several copies of the same data. Redundant entries are frequent in traditional file processing; a goal of the database approach was to control redundancy as much as possible.

Problems with redundancy includes waste of storage space, duplication of effort to perform a single conceptual update, danger of introducing inconsistency, if multiple updates are not coordinated.

Access Control

Responsible for enforcing security and authorization (e.g., who can create new bank accounts) and data (e.g., which bank accounts can I see).

It is all about who accesses what data, to do what, when, from where etc.

Some examples of access privileges are as follows

  • To create a database
  • To authorize (grant) additional users to access the database, access some relations, create new relations and update the database.
  • To revoke privileges.

Key Points

  • In a multiuser database, access control is mandatory g., for confidentiality.
  • Vitalriodus ways to access data (e.g., read only, read and update).
  • The data dictionary holds information about users and their access privileges

(e•g., name and password).

Performance Optimization

Performing physical reorganizations to enhance performance e.g., adding index, dropping index, sorting file. Performance optimization is made possible by physical data  independence and high level data       models with user program which can be optimized through  DBMS software.

Metadata Management

Metadata means data about data. is maintained in a special database, which we can  system catalog or data dictionary. It involves storing of  information about other information. With different types of media being used, refrerence to location of the data can allow management of diverse repositories.                                               

Active Features

Data objects, database statistics, physical structures and access paths, user  access privileges etc, are active features of DBMS.

Types of Database Model

The database model can be of three types as given below.

Schema in dbms Tutorial Study Material Notes with Examples
Schema in dbms Tutorial Study Material Notes with Examples
Schema in dbms Tutorial Study Material Notes with Examples
Schema in dbms Tutorial Study Material Notes with Examples

Sorting in Design and Analysis of Algorithm Study Notes with Example

Follow Us on Social Platforms to get Updated : twiter,  facebookGoogle Plus

Learn Sorting in Handbook Series:  Click here 

Leave a Reply

Your email address will not be published. Required fields are marked *