DATA BASE MANAGEMENT
SYSTEMS
Schemas and Instances:
The data which is stored in the database at a particular
moment of time is called an instance of the database.
The overall design of a database is called schema.
A database schema is the skeleton structure of the database.
It represents the logical view of the entire database.
A schema contains schema objects like table, foreign key,
primary key, views, columns, data types, stored procedure,
etc.
A database schema can be represented by using the visual
diagram. That diagram shows the database objects and
relationship with each other.
A database schema is designed by the database designers to
help programmers whose software will interact with the
database. The process of database creation is called data
modeling.
A schema diagram can display only some aspects of a schema like
the name of record type, data type, and constraints. Other aspects
can't be specified through the schema diagram. For example, the
given figures neither show the data type of each data item nor the
relationship among various files.
In the database, actual data changes quite frequently. For example, in
the given figure, the database changes whenever we add a new grade
or add a student. The data at a particular moment of time is called the
instance of the database.
Instance
Instance or extension or database state is a collection of information that stored
in a database at a particular moment is called an instance of the database. The
Database instance refers to the information stored in the database at a given
point of time. Thus, it is a dynamic value which keeps on changing.
Example
A database instance for the Person database can be
(User1,emai.com,11345679,addr) So the person construct will contain their
individual entities in the attributes called as instance. This is shown below −
Person
Name Email Phone no
Three schema Architecture & Data Independence in DBMS:
Three schema Architecture:
The three schema architecture is also called ANSI/SPARC architecture or three-level
architecture.
This framework is used to describe the structure of a specific database system.
The three schema architecture is also used to separate the user applications and
physical database.
The three schema architecture contains three-levels. It breaks the database down into
three different categories.
The three-schema architecture is as follows:
In the above diagram:
It shows the DBMS architecture.
Mapping is used to transform the request and response between various
database levels of architecture.
Mapping is not good for small DBMS because it takes more time.
In External / Conceptual mapping, it is necessary to transform the request
from external level to conceptual schema.
In Conceptual / Internal mapping, DBMS transform the request from the conceptual
to internal level.
1. Internal Level:
The internal level has an internal schema which describes the physical storage
structure of the database.
The internal schema is also known as a physical schema.
It uses the physical data model. It is used to define that how the data will be stored in a
block.
The physical level is used to describe complex low-level data structures in detail.
2. Conceptual Level:
The conceptual schema describes the design of a database at the conceptual level.
Conceptual level is also known as logical level.
The conceptual schema describes the structure of the whole database.
The conceptual level describes what data are to be stored in the database and also
describes what relationship exists among those data.
In the conceptual level, internal details such as an implementation of the data structure
are hidden.
Programmers and database administrators work at this level.
3. External Level:
At the external level, a database contains several schemas that sometimes called as
subschema. The subschema is used to describe the different view of the database.
An external schema is also known as view schema.
Each view schema describes the database part that a particular user group is interested
and hides the remaining database from that user group.
The view schema describes the end user interaction with database systems.
Data Independence:
1. Physical Data Independence :Physical Data Independence is defined as the ability to
make changes in the structure of the lowest level of the Database Management System
(DBMS) without affecting the higher-level schemas. Hence, modification in the Physical
level should not result in any changes in the Logical or View levels.
Note :There are 3 levels in the schema architecture of DBMS: physical level, logical level
and view level (arranged from the lowest to highest level).
2. Logical Data Independence :Logical Data Independence is defined as the ability to
make changes in the structure of the middle level of the Database Management System
(DBMS) without affecting the highest-level schema or application programs. Hence,
modification in the logical level should not result in any changes in the view levels or
application programs.
*****
Database Language:
A DBMS has appropriate languages and interfaces to express database queries and
updates.
Database languages can be used to read, store and update the data in the database.
Types of Database Language:
1. Data Definition Language:
DDL stands for Data Definition Language. It is used to define database structure or
pattern.
It is used to create schema, tables, indexes, constraints, etc. in the database.
Using the DDL statements, you can create the skeleton of the database.
Data definition language is used to store the information of metadata like the number
of tables and schemas, their names, indexes, columns in each table, constraints, etc.
Here are some tasks that come under DDL:
Create: It is used to create objects in the database.
Alter: It is used to alter the structure of the database.
Drop: It is used to delete objects from the database.
Truncate: It is used to remove all records from a table.
Rename: It is used to rename an object.
Comment: It is used to comment on the data dictionary.
These commands are used to update the database schema that's why they come under Data
definition language.
2. Data Manipulation Language:
DML stands for Data Manipulation Language. It is used for accessing and manipulating data
in a database. It handles user requests.
Here are some tasks that come under DML:
Select: It is used to retrieve data from a database.
Insert: It is used to insert data into a table.
Update: It is used to update existing data within a table.
Delete: It is used to delete all records from a table.
Merge: It performs UPSERT operation, i.e., insert or update operations.
Call: It is used to call a structured query language or a Java subprogram.
Explain Plan: It has the parameter of explaining data.
Lock Table: It controls concurrency.
3. Data Control Language:
DCL stands for Data Control Language. It is used to retrieve the stored or saved data.
The DCL execution is transactional. It also has rollback parameters.
Here are some tasks that come under DCL:
Grant: It is used to give user access privileges to a database.
Revoke: It is used to take back permissions from the user.
There are the following operations which have the
authorization of Revoke: CONNECT, INSERT, USAGE,
EXECUTE, DELETE, UPDATE and SELECT.
4. Transaction Control Language:
TCL is used to run the changes made by the DML statement. TCL can be
grouped into a logical transaction.
Here are some tasks that come under TCL:
Commit: It is used to save the transaction on the database.
Rollback: It is used to restore the database to original since the last
Commit.
*****
A database environment is a collective system of components that
comprise and regulates the group of data, management, and use of
data, which consist of software, hardware, people, techniques of
handling database, and the data also.
**********************************
APPLICATION OF DBMS:
Databases are used to support internal operations of organizations and to underpin online
interactions with customers and suppliers (see Enterprise software).
Databases are used to hold administrative information and more specialized data, such as
engineering data or economic models. Examples of database applications include
computerized
library systems, flight reservation systems and computerized parts inventory systems.
Application areas of DBMS
1. Banking: For customer information, accounts, and loans, and banking transactions.
2. Airlines: For reservations and schedule information. Airlines were among the first to use
databases in a geographically distributed manner - terminals situated around the world
accessed
the central database system through phone lines and other data networks.
3. Universities: For student information, course registrations, and grades.
4. Credit card transactions: For purchases on credit cards and generation of monthly
statements.
5. Telecommunication: For keeping records of calls made, generating monthly bills,
maintaining
balances on prepaid calling cards, and storing information about the communication
networks.
6. Finance: For storing information about holdings, sales, and purchases of financial
instruments
such as stocks and bonds.
7. Sales: For customer, product, and purchase information.
8. Manufacturing: For management of supply chain and for tracking production of items in
factories, inventories of items in warehouses / stores, and orders for items.
9. Human resources: For information about employees, salaries, payroll taxes and benefits,
and for generation of paychecks
