MAHARANA PRATAP GROUP OF INSTITUTIONS

KOTHI MANDHANA, KANPUR

(Approved by AICTE, New Delhi and Affiliated to Dr.AKTU, Lucknow)

Digital Notes
[Department ofComputer Application]
Subject Name : Database Management System

Subject Code : KCA 204

Course : MCA
Branch :
Semester : 2nd
Prepared by : Avinash Bajpai

Reference No./CA/Avinash Bajpai/KCA 204/1/2

 Unit 1

What is Data?
o Data is a collection of a distinct small unit of information. It can be used in a
variety of forms like text, numbers, media, bytes, etc. it can be stored in
pieces of paper or electronic memory, etc.
o Word 'Data' is originated from the word 'datum' that means 'single piece of
information.' It is plural of the word datum.
o In computing, Data is information that can be translated into a form for
efficient movement and processing. Data is interchangeable.

What is Database?
o A database is an organized collection of data, so that it can be easily
accessed and managed.
o You can organize data into tables, rows, columns, and index it to make it
easier to find relevant information.
o Database handlers create a database in such a way that only one set of
software program provides access of data to all the users.
o The main purpose of the database is to operate a large amount of
information by storing, retrieving, and managing data.
o There are many dynamic websites on the World Wide Web nowadays
which are handled through databases. For example, a model that checks the
availability of rooms in a hotel. It is an example of a dynamic website that
uses a database.
o There are many databases available like MySQL, Sybase, Oracle,
MongoDB, Informix, PostgreSQL, SQL Server, etc.
o Modern databases are managed by the database management system
(DBMS).
o SQL or Structured Query Language is used to operate on the data stored in a
database. SQL depends on relational algebra and tuple relational calculus.
Database Management System
o Database management system is a software which is used to manage the
database. For example: MySQL, Oracle, etc are a very popular commercial
database which is used in different applications.
o DBMS provides an interface to perform various operations like database
creation, storing data in it, updating data, creating a table in the database and
a lot more.
o It provides protection and security to the database. In the case of multiple
users, it also maintains data consistency.

DBMS allows users the following tasks:

o Data Definition: It is used for creation, modification, and removal of

definition that defines the organization of data in the database.
o Data Updation: It is used for the insertion, modification, and deletion of the
actual data in the database.
o Data Retrieval: It is used to retrieve the data from the database which can
be used by applications for various purposes.
o User Administration: It is used for registering and monitoring users,
maintain data integrity, enforcing data security, dealing with concurrency
control, monitoring performance and recovering information corrupted by
unexpected failure.

Characteristics of DBMS
o It uses a digital repository established on a server to store and manage the
information.
o It can provide a clear and logical view of the process that manipulates data.
o DBMS contains automatic backup and recovery procedures.
o It contains ACID properties which maintain data in a healthy state in case of
failure.
o It can reduce the complex relationship between data.
o It is used to support manipulation and processing of data.
o It is used to provide security of data.
 o It can view the database from different viewpoints according to the
requirements of the user.

Advantages of DBMS
o Controls database redundancy: It can control data redundancy because it
stores all the data in one single database file and that recorded data is placed
in the database.
o Data sharing: In DBMS, the authorized users of an organization can share
the data among multiple users.
o Easily Maintenance: It can be easily maintainable due to the centralized
nature of the database system.
o Reduce time: It reduces development time and maintenance need.
o Backup: It provides backup and recovery subsystems which create
automatic backup of data from hardware and software failures and restores
the data if required.
o multiple user interface: It provides different types of user interfaces like
graphical user interfaces, application program interfaces

Disadvantages of DBMS
o Cost of Hardware and Software: It requires a high speed of data processor
and large memory size to run DBMS software.
o Size: It occupies a large space of disks and large memory to run them
efficiently.
o Complexity: Database system creates additional complexity and
requirements.
o Higher impact of failure: Failure is highly impacted the database because
in most of the organization, all the data stored in a single database and if the
database is damaged due to electric failure or database corruption then the
data may be lost forever.

Evolution of Databases

The database has completed more than 50 years of journey of its evolution from
flat-file system to relational and objects relational systems. It has gone through
several generations.
The Evolution

File-Based

1968 was the year when File-Based database were introduced. In file-based
databases, data was maintained in a flat file. Though files have many advantages,
there are several limitations.

One of the major advantages is that the file system has various access methods,
e.g., sequential, indexed, and random.

It requires extensive programming in a third-generation language such as COBOL,

BASIC.

Hierarchical Data Model

1968-1980 was the era of the Hierarchical Database. Prominent hierarchical

database model was IBM's first DBMS. It was called IMS (Information
Management System).

In this model, files are related in a parent/child manner.

Below diagram represents Hierarchical Data Model. Small circle represents

objects.
Like file system, this model also had some limitations like complex
implementation, lack structural independence, can't easily handle a many-many
relationship, etc.

Network data model

Charles Bachman developed the first DBMS at Honeywell called Integrated Data
Store (IDS). It was developed in the early 1960s, but it was standardized in 1971
by the CODASYL group (Conference on Data Systems Languages).

In this model, files are related as owners and members, like to the common
network model.

Network data model identified the following components:

o Network schema (Database organization)

 o Sub-schema (views of database per user)
o Data management language (procedural)

This model also had some limitations like system complexity and difficult to
design and maintain.

Relational Database

1970 - Present: It is the era of Relational Database and Database Management. In

1970, the relational model was proposed by E.F. Codd.

Relational database model has two main terminologies called instance and schema.

The instance is a table with rows or columns

Schema specifies the structure like name of the relation, type of each column and
name.

This model uses some mathematical concept like set theory and predicate logic.

The first internet database application had been created in 1995.

During the era of the relational database, many more models had introduced like
object-oriented model, object-relational model, etc.

Cloud database

Cloud database facilitates you to store, manage, and retrieve their structured,
unstructured data via a cloud platform. This data is accessible over the Internet.
Cloud databases are also called a database as service (DBaaS) because they are
offered as a managed service.

Some best cloud options are:

o AWS (Amazon Web Services)

o Snowflake Computing
o Oracle Database Cloud Services
o Microsoft SQL server
o Google cloud spanner
Advantages of cloud database

Lower costs

Generally, company provider does not have to invest in databases. It can maintain
and support one or more data centers.

Automated

Cloud databases are enriched with a variety of automated processes such as

recovery, failover, and auto-scaling.

Increased accessibility

You can access your cloud-based database from any location, anytime. All you
need is just an internet connection.

NoSQL Database

A NoSQL database is an approach to design such databases that can accommodate

a wide variety of data models. NoSQL stands for "not only SQL." It is an
alternative to traditional relational databases in which data is placed in tables, and
data schema is perfectly designed before the database is built.

NoSQL databases are useful for a large set of distributed data.

Some examples of NoSQL database system with their category are:

o MongoDB, CouchDB, Cloudant (Document-based)

o Memcached, Redis, Coherence (key-value store)
o HBase, Big Table, Accumulo (Tabular)

Advantage of NoSQL

High Scalability

NoSQL can handle an extensive amount of data because of scalability. If the data
grows, NoSQL database scale it to handle that data in an efficient manner.

High Availability
NoSQL supports auto replication. Auto replication makes it highly available
because, in case of any failure, data replicates itself to the previous consistent state.

Disadvantage of NoSQL

Open source

NoSQL is an open-source database, so there is no reliable standard for NoSQL yet.

Management challenge

Data management in NoSQL is much more complicated than relational databases.

It is very challenging to install and even more hectic to manage daily.

GUI is not available

GUI tools for NoSQL database are not easily available in the market.

Backup

Backup is a great weak point for NoSQL databases. Some databases, like
MongoDB, have no powerful approaches for data backup.

The Object-Oriented Databases

The object-oriented databases contain data in the form of object and classes.
Objects are the real-world entity, and types are the collection of objects. An object-
oriented database is a combination of relational model features with objects
oriented principles. It is an alternative implementation to that of the relational
model.

Object-oriented databases hold the rules of object-oriented programming. An

object-oriented database management system is a hybrid application.

The object-oriented database model contains the following properties.

Object-oriented programming properties

o Objects
o Classes
o Inheritance
 o Polymorphism
o Encapsulation

Relational database properties

o Atomicity
o Consistency
o Integrity
o Durability
o Concurrency
o Query processing

Graph Databases

A graph database is a NoSQL database. It is a graphical representation of data. It

contains nodes and edges. A node represents an entity, and each edge represents a
relationship between two edges. Every node in a graph database represents a
unique identifier.

Graph databases are beneficial for searching the relationship between data because
they highlight the relationship between relevant data.

Graph databases are very useful when the database contains a complex relationship
and dynamic schema.

It is mostly used in supply chain management, identifying the source of IP

telephony.

DBMS (Data Base Management System)

Database management System is software which is used to store and retrieve the
database. For example, Oracle, MySQL, etc.; these are some popular DBMS tools.
 o DBMS provides the interface to perform the various operations like creation,
deletion, modification, etc.
o DBMS allows the user to create their databases as per their requirement.
o DBMS accepts the request from the application and provides specific data
through the operating system.
o DBMS contains the group of programs which acts according to the user
instruction.
o It provides security to the database.

Advantage of DBMS

Controls redundancy

It stores all the data in a single database file, so it can control data redundancy.

Data sharing

An authorized user can share the data among multiple users.

Backup

It providesBackup and recovery subsystem. This recovery system creates

automatic data from system failure and restores data if required.

Multiple user interfaces

It provides a different type of user interfaces like GUI, application interfaces.

Disadvantage of DBMS

Size

It occupies large disk space and large memory to run efficiently.

Cost

DBMS requires a high-speed data processor and larger memory to run DBMS
software, so it is costly.

Complexity
DBMS creates additional complexity and requirements.

RDBMS (Relational Database Management System)

The word RDBMS is termed as 'Relational Database Management System.' It is

represented as a table that contains rows and column.

RDBMS is based on the Relational model; it was introduced by E. F. Codd.

A relational database contains the following components:

o Table
o Record/ Tuple
o Field/Column name /Attribute
o Instance
o Schema
o Keys

An RDBMS is a tabular DBMS that maintains the security, integrity, accuracy, and
consistency of the data.

Types of Database

There are various types of databases used for storing different varieties of data:
1) Centralized Database: It is the type of database that stores data at a centralized
database system. It comforts the users to access the stored data from different
locations through several applications. These applications contain the
authentication process to let users access data securely. An example of a
Centralized database can be Central Library that carries a central database of each
library in a college/university.

Advantages of Centralized Database

o It has decreased the risk of data management, i.e., manipulation of data will
not affect the core data.
o Data consistency is maintained as it manages data in a central repository.
o It provides better data quality, which enables organizations to establish data
standards.
o It is less costly because fewer vendors are required to handle the data sets.

Disadvantages of Centralized Database

o The size of the centralized database is large, which increases the response
time for fetching the data.
o It is not easy to update such an extensive database system.
o If any server failure occurs, entire data will be lost, which could be a huge
loss.
2) Distributed Database: Unlike a centralized database system, in distributed
systems, data is distributed among different database systems of an organization.
These database systems are connected via communication links. Such links help
the end-users to access the data easily. Examples of the Distributed database are
Apache Cassandra, HBase, Ignite, etc.

We can further divide a distributed database system into:

o Homogeneous DDB: Those database systems which execute on the same

operating system and use the same application process and carry the same
hardware devices.
o Heterogeneous DDB: Those database systems which execute on different
operating systems under different application procedures, and carries
different hardware devices.

Advantages of Distributed Database

o Modular development is possible in a distributed database, i.e., the system

can be expanded by including new computers and connecting them to the
distributed system.
o One server failure will not affect the entire data set.
3) Relational Database: This database is based on the relational data model,
which stores data in the form of rows(tuple) and columns(attributes), and together
forms a table(relation). A relational database uses SQL for storing, manipulating,
as well as maintaining the data. E.F. Codd invented the database in 1970. Each
table in the database carries a key that makes the data unique from
others. Examples of Relational databases are MySQL, Microsoft SQL Server,
Oracle, etc.

Properties of Relational Database

There are following four commonly known properties of a relational model known
as ACID properties, where:

A means Atomicity: This ensures the data operation will complete either with
success or with failure. It follows the 'all or nothing' strategy. For example, a
transaction will either be committed or will abort.

C means Consistency: If we perform any operation over the data, its value before
and after the operation should be preserved. For example, the account balance
before and after the transaction should be correct, i.e., it should remain conserved.

I means Isolation: There can be concurrent users for accessing data at the same
time from the database. Thus, isolation between the data should remain isolated.
For example, when multiple transactions occur at the same time, one transaction
effects should not be visible to the other transactions in the database.

D means Durability: It ensures that once it completes the operation and commits
the data, data changes should remain permanent.

4) NoSQL Database: Non-SQL/Not Only SQL is a type of database that is used

for storing a wide range of data sets. It is not a relational database as it stores data
not only in tabular form but in several different ways. It came into existence when
the demand for building modern applications increased. Thus, NoSQL presented a
wide variety of database technologies in response to the demands. We can further
divide a NoSQL database into the following four types:
 a. Key-value storage: It is the simplest type of database storage where it stores
every single item as a key (or attribute name) holding its value, together.
b. Document-oriented Database: A type of database used to store data as
JSON-like document. It helps developers in storing data by using the same
document-model format as used in the application code.
c. Graph Databases: It is used for storing vast amounts of data in a graph-like
structure. Most commonly, social networking websites use the graph
database.
d. Wide-column stores: It is similar to the data represented in relational
databases. Here, data is stored in large columns together, instead of storing
in rows.

Advantages of NoSQL Database

o It enables good productivity in the application development as it is not

required to store data in a structured format.
o It is a better option for managing and handling large data sets.
o It provides high scalability.
o Users can quickly access data from the database through key-value.
5) Cloud Database: A type of database where data is stored in a virtual
environment and executes over the cloud computing platform. It provides users
with various cloud computing services (SaaS, PaaS, IaaS, etc.) for accessing the
database. There are numerous cloud platforms, but the best options are:

o Amazon Web Services(AWS)

o Microsoft Azure
o Kamatera
o PhonixNAP
o ScienceSoft
o Google Cloud SQL, etc.

6) Object-oriented Databases: The type of database that uses the object-based

data model approach for storing data in the database system. The data is
represented and stored as objects which are similar to the objects used in the
object-oriented programming language.

7) Hierarchical Databases: It is the type of database that stores data in the form of
parent-children relationship nodes. Here, it organizes data in a tree-like structure.
Data get stored in the form of records that are connected via links. Each child
record in the tree will contain only one parent. On the other hand, each parent
record can have multiple child records.

8) Network Databases: It is the database that typically follows the network data
model. Here, the representation of data is in the form of nodes connected via links
between them. Unlike the hierarchical database, it allows each record to have
multiple children and parent nodes to form a generalized graph structure.

9) Personal Database: Collecting and storing data on the user's system defines a
Personal Database. This database is basically designed for a single user.

Advantage of Personal Database

o It is simple and easy to handle.

o It occupies less storage space as it is small in size.

10) Operational Database: The type of database which creates and updates the
database in real-time. It is basically designed for executing and handling the daily
data operations in several businesses. For example, An organization uses
operational databases for managing per day transactions.

11) Enterprise Database: Large organizations or enterprises use this database for
managing a massive amount of data. It helps organizations to increase and improve
their efficiency. Such a database allows simultaneous access to users.

Advantages of Enterprise Database:

o Multi processes are supportable over the Enterprise database.

o It allows executing parallel queries on the system.

What is RDBMS

o RDBMS stands for Relational Database Management Systems..

o All modern database management systems like SQL, MS SQL Server, IBM
DB2, ORACLE, My-SQL and Microsoft Access are based on RDBMS.
o It is called Relational Data Base Management System (RDBMS) because it
is based on relational model introduced by E.F. Codd.

How it works
o Data is represented in terms of tuples (rows) in RDBMS.
o Relational database is most commonly used database. It contains number of
tables and each table has its own primary key.
o Due to a collection of organized set of tables, data can be accessed easily in
RDBMS.

Brief History of RDBMS

o During 1970 to 1972, E.F. Codd published a paper to propose the use of
relational database model.
o RDBMS is originally based on that E.F. Codd's relational model invention.
o Here is brief note on E.F Codd’s Twelve rules:
o Rule 0 − Foundation rule
o Any relational database management system that is propounded to be RDBMS
or advocated to be a RDBMS should be able to manage the stored data in its
entirety through its relational capabilities.
o Rule 1 − Rule of Information
o Relational Databases should store the data in the form of relations. Tables are
relations in Relational Database Management Systems. Be it any user defined
data or meta-data, it is important to store the value as an entity in the table cells.
o Rule 2 − Rule of Guaranteed Access
o The use of pointers to access data logically is strictly forbidden. Every data entity
which is atomic in nature should be accessed logically by using a right
combination of the name of table, primary key represented by a specific row
value and column name represented by attribute value.
o Rule 3 − Rule of Systematic Null Value Support
o Null values are completely supported in relational databases. They should be
uniformly considered as ‘missing information’. Null values are independent of any
data type. They should not be mistaken for blanks or zeroes or empty strings.
Null values can also be interpreted as ‘inapplicable data’ or ‘unknown
information.’
o Rule 4 − Rule of Active and online relational Catalog
o In the Database Management Systems lexicon, ‘metadata’ is the data about the
database or the data about the data. The active online catalog that stores the
metadata is called ‘Data dictionary’. The so called data dictionary is accessible
only by authored users who have the required privileges and the query
languages used for accessing the database should be used for accessing the
data of data dictionary.
o Rule 5 − Rule of Comprehensive Data Sub-language
o A single robust language should be able to define integrity constraints, views,
data manipulations, transactions and authorizations. If the database allows
access to the aforementioned ones, it is violating this rule.
o Rule 6 − Rule of Updating Views
 o Views should reflect the updates of their respective base tables and vice versa. A
view is a logical table which shows restricted data. Views generally make the
data readable but not modifiable. Views help in data abstraction.
o Rule 7 − Rule of Set level insertion, update and deletion
o A single operation should be sufficient to retrieve, insert, update and delete the
data.
o Rule 8 − Rule of Physical Data Independence
o Batch and end user operations are logically separated from physical storage and
respective access methods.
o Rule 9 − Rule of Logical Data Independence
o Batch and end users can change the database schema without having to
recreate it or recreate the applications built upon it.
o Rule 10 − Rule of Integrity Independence
o Integrity constraints should be available and stored as metadata in data
dictionary and not in the application programs.
o Rule 11 − Rule of Distribution Independence
o The Data Manipulation Language of the relational system should not be
concerned about the physical data storage and no alterations should be required
if the physical data is centralized or distributed.
o Rule 12 − Rule of Non Subversion
o Any row should obey the security and integrity constraints imposed. No special
privileges are applicable.

o What is table
o The RDBMS database uses tables to store data. A table is a collection of
related data entries and contains rows and columns to store data.
o A table is the simplest example of data storage in RDBMS.
o Let's see the example of student table.

ID Name AGE COURSE

1 Ajeet 24 B.Tech

2 Aryan 20 C.A

3 Mahes 21 BCA
h

4 Ratan 22 MCA

5 Vimal 26 BSC
 What is field
o Field is a smaller entity of the table which contains specific information
about every record in the table. In the above example, the field in the student
table consist of id, name, age, course.

What is row or record

o A row of a table is also called record. It contains the specific information of
each individual entry in the table. It is a horizontal entity in the table. For
example: The above table contains 5 records.

Let's see one record/row in the table.

1 Ajeet 24 B.Tech

What is column
o A column is a vertical entity in the table which contains all information
associated with a specific field in a table. For example: "name" is a column
in the above table which contains all information about student's name.

Ajeet

Aryan

Mahesh

Ratan

Vimal

NULL Values
The NULL value of the table specifies that the field has been left blank
during record creation. It is totally different from the value filled with zero
or a field that contains space.

Data Integrity
There are the following categories of data integrity exist with each RDBMS:
Entity integrity: It specifies that there should be no duplicate rows in a
table.
 Domain integrity: It enforces valid entries for a given column by restricting
the type, the format, or the range of values.
Referential integrity: It specifies that rows cannot be deleted, which are
used by other records.
User-defined integrity: It enforces some specific business rules that are
defined by users. These rules are different from entity, domain or referential
integrity.

Difference between DBMS and RDBMS

o Although DBMS and RDBMS both are used to store information in physical
database but there are some remarkable differences between them.
o The main differences between DBMS and RDBMS are given below:

No. DBMS RDBMS

1 DBMS applications RDBMS applications store data in a

) store data as file. tabular form.

2 In DBMS, data is generally In RDBMS, the tables have an identifier

) stored in either a called primary key and the data values
hierarchical form or a are stored in the form of tables.
navigational form.

3 Normalization is Normalization is present in RDBMS.

) not present in DBMS.

4 DBMS does not apply any RDBMS defines the integrity

) security with regards to constraint for the purpose of ACID
data manipulation. (Atomocity, Consistency, Isolation and
Durability) property.

5 DBMS uses file system to in RDBMS, data values are stored in the
) store data, so there will form of tables, so
be no relation between the a relationship between these data values
tables. will be stored in the form of a table as
well.

6 DBMS has to provide some RDBMS system supports a tabular

 ) uniform methods to access structure of the data and a relationship
the stored information. between them to access the stored
information.

7 DBMS does not support RDBMS supports distributed

) distributed database. database.

8 DBMS is meant to be for RDBMS is designed to handle large

) small organization and deal amount of data. it supports multiple
with small data. it users.
supports single user.

9 Examples of DBMS are file Example of RDBMS

) systems, xml etc. are mysql, postgre, sql
server, oracle etc.

DBMS vs. File System

There are following differences between DBMS and File system:

DBMS File System

DBMS is a collection of data. In File system is a collection of data. In this

DBMS, the user is not required system, the user has to write the procedures
to write the procedures. for managing the database.

DBMS gives an abstract view File system provides the detail of the data
of data that hides the details. representation and storage of data.

DBMS provides a crash File system doesn't have a crash mechanism,

recovery mechanism, i.e., i.e., if the system crashes while entering
DBMS protects the user from some data, then the content of the file will
the system failure. lost.

DBMS provides a good It is very difficult to protect a file under the

protection mechanism. file system.
 DBMS contains a wide variety File system can't efficiently store and
of sophisticated techniques to retrieve the data.
store and retrieve the data.

DBMS takes care of Concurrent In the File system, concurrent access has
access of data using some form many problems like redirecting the file
of locking. while other deleting some information or
updating some information.

DBMS Architecture

o The DBMS design depends upon its architecture. The basic client/server
architecture is used to deal with a large number of PCs, web servers,
database servers and other components that are connected with networks.
o The client/server architecture consists of many PCs and a workstation which
are connected via the network.
o DBMS architecture depends upon how users are connected to the database to
get their request done.

Types of DBMS Architecture

Database architecture can be seen as a single tier or multi-tier. But logically,
database architecture is of two types like: 2-tier architecture and 3-tier
architecture.

1-Tier Architecture

o In this architecture, the database is directly available to the user. It means the
user can directly sit on the DBMS and uses it.
o Any changes done here will directly be done on the database itself. It doesn't
provide a handy tool for end users.
o The 1-Tier architecture is used for development of the local application,
where programmers can directly communicate with the database for the
quick response.

2-Tier Architecture

o The 2-Tier architecture is same as basic client-server. In the two-tier

architecture, applications on the client end can directly communicate with
the database at the server side. For this interaction, API's
like: ODBC, JDBC are used.
 o The user interfaces and application programs are run on the client-side.
o The server side is responsible to provide the functionalities like: query
processing and transaction management.
o To communicate with the DBMS, client-side application establishes a
connection with the server side.

3-Tier Architecture

o The 3-Tier architecture contains another layer between the client and server.
In this architecture, client can't directly communicate with the server.
o The application on the client-end interacts with an application server which
further communicates with the database system.
o End user has no idea about the existence of the database beyond the
application server. The database also has no idea about any other user
beyond the application.
o The 3-Tier architecture is used in case of large web application.

Three schema Architecture

 o The three schema architecture is also called ANSI/SPARC architecture or
three-level architecture.
o This framework is used to describe the structure of a specific database
system.
o The three schema architecture is also used to separate the user applications
and physical database.
o 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:

o It shows the DBMS architecture.

o Mapping is used to transform the request and response between various
database levels of architecture.
 o Mapping is not good for small DBMS because it takes more time.
o In External / Conceptual mapping, it is necessary to transform the request
from external level to conceptual schema.
o In Conceptual / Internal mapping, DBMS transform the request from the
conceptual to internal level.

1. Internal Level

o The internal level has an internal schema which describes the physical
storage structure of the database.
o The internal schema is also known as a physical schema.
o It uses the physical data model. It is used to define that how the data will be
stored in a block.
o The physical level is used to describe complex low-level data structures in
detail.

2. Conceptual Level

o The conceptual schema describes the design of a database at the conceptual

level. Conceptual level is also known as logical level.
o The conceptual schema describes the structure of the whole database.
o The conceptual level describes what data are to be stored in the database and
also describes what relationship exists among those data.
o In the conceptual level, internal details such as an implementation of the data
structure are hidden.
o Programmers and database administrators work at this level.

3. External Level

o 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.
o An external schema is also known as view schema.
o Each view schema describes the database part that a particular user group is
interested and hides the remaining database from that user group.
o The view schema describes the end user interaction with database systems.

Data Models
 o Data Model is the modeling of the data description, data semantics, and
consistency constraints of the data. It provides the conceptual tools for
describing the design of a database at each level of data abstraction.
Therefore, there are following four data models used for understanding the
structure of the database:

1) Relational Data Model: This type of model designs the data in the form of
rows and columns within a table. Thus, a relational model uses tables for
representing data and in-between relationships. Tables are also called relations.
This model was initially described by Edgar F. Codd, in 1969. The relational data
model is the widely used model which is primarily used by commercial data
processing applications.

2) Entity-Relationship Data Model: An ER model is the logical representation of

data as objects and relationships among them. These objects are known as entities,
and relationship is an association among these entities. This model was designed
by Peter Chen and published in 1976 papers. It was widely used in database
designing. A set of attributes describe the entities. For example, student_name,
student_id describes the 'student' entity. A set of the same type of entities is known
as an 'Entity set', and the set of the same type of relationships is known as
'relationship set'.

3) Object-based Data Model: An extension of the ER model with notions of

functions, encapsulation, and object identity, as well. This model supports a rich
type system that includes structured and collection types. Thus, in 1980s, various
database systems following the object-oriented approach were developed. Here, the
objects are nothing but the data carrying its properties.

4) Semistructured Data Model: This type of data model is different from the
other three data models (explained above). The semistructured data model allows
the data specifications at places where the individual data items of the same type
may have different attributes sets. The Extensible Markup Language, also known
as XML, is widely used for representing the semistructured data. Although XML
was initially designed for including the markup information to the text document, it
gains importance because of its application in the exchange of data.

Data model Schema and Instance

o The data which is stored in the database at a particular moment of time is

called an instance of the database.
o The overall design of a database is called schema.
o A database schema is the skeleton structure of the database. It represents the
logical view of the entire database.
o A schema contains schema objects like table, foreign key, primary key,
views, columns, data types, stored procedure, etc.
o A database schema can be represented by using the visual diagram. That
diagram shows the database objects and relationship with each other.
o 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 figure 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.
Data Independence

o Data independence can be explained using the three-schema architecture.

o Data independence refers characteristic of being able to modify the schema
at one level of the database system without altering the schema at the next
higher level.

There are two types of data independence:

1. Logical Data Independence

o Logical data independence refers characteristic of being able to change the

conceptual schema without having to change the external schema.
o Logical data independence is used to separate the external level from the
conceptual view.
o If we do any changes in the conceptual view of the data, then the user view
of the data would not be affected.
 o Logical data independence occurs at the user interface level.

2. Physical Data Independence

o Physical data independence can be defined as the capacity to change the

internal schema without having to change the conceptual schema.
o If we do any changes in the storage size of the database system server, then
the Conceptual structure of the database will not be affected.
o Physical data independence is used to separate conceptual levels from the
internal levels.
o Physical data independence occurs at the logical interface level.

Database Language

o A DBMS has appropriate languages and interfaces to express database

queries and updates.
 o Database languages can be used to read, store and update the data in the
database.

Types of Database Language

1. Data Definition Language

o DDL stands for Data Definition Language. It is used to define database

structure or pattern.
o It is used to create schema, tables, indexes, constraints, etc. in the database.
o Using the DDL statements, you can create the skeleton of the database.
o 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:

o Create: It is used to create objects in the database.

o Alter: It is used to alter the structure of the database.
o Drop: It is used to delete objects from the database.
o Truncate: It is used to remove all records from a table.
o Rename: It is used to rename an object.
o 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:

o Select: It is used to retrieve data from a database.

o Insert: It is used to insert data into a table.
o Update: It is used to update existing data within a table.
o Delete: It is used to delete all records from a table.
 o Merge: It performs UPSERT operation, i.e., insert or update operations.
o Call: It is used to call a structured query language or a Java subprogram.
o Explain Plan: It has the parameter of explaining data.
o Lock Table: It controls concurrency.

3. Data Control Language

o DCL stands for Data Control Language. It is used to retrieve the stored or
saved data.
o The DCL execution is transactional. It also has rollback parameters.

(But in Oracle database, the execution of data control language does not
have the feature of rolling back.)

Here are some tasks that come under DCL:

o Grant: It is used to give user access privileges to a database.

o 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:

o Commit: It is used to save the transaction on the database.

o Rollback: It is used to restore the database to original since the last Commit.