Introduction to DBMS

Topic-1: What is DBMS?

DBMS is a software that is used to manage the data. Some of the popular DBMS
softwares are: MySQL, IBM Db2, Oracle, PostgreSQL etc.

 DBMS provides an interface to the user so that the operations on database

can be performed using the interface.
 DBMS secure the data, that is the main advantage of DBMS over file
system.
 DBMS also secures the data from unauthorised access as well as corrupt
data insertions. It allows multiple users to access data simultaneously while
maintaining the data consistency and data integrity.

DBMS allows following operations to the authorized users of the database:

Data Definition: Creation of table, table schema creation, removal of table

definition etc. comes under data definition. It is basically a layout of the table and
their relation with the other tables in the database. This allows to properly
structure the data in such a way so that the data that is related or dependent on
other data in real world can be represented the same way in database.

Data Modification: DBMS allows users to insert, update and delete the data
from the tables. These tables contains rows and columns, where row represents
a record of data while column represents attributes of the records. You can also
bulk update the several records in DBMS with a single click.

Data Retrieval: DBMS allows users to fetch data from the database. Searching
and retrieval of data is fast in DBMS. The size of the database doesn’t impact
this operation, on the other hand in file system, the size of the data can hugely
impact the search operation efficiency.

User administration: DBMS also allows user management such as organizing

users in different groups with different access levels. Granting users access to
certain tables in database, revoking access from certain users etc. This allows
the admin of the database to efficiently manage the access to the
database and prevent unauthorised access to the databases.

What is the need of DBMS?

Database systems are basically developed for large amount of data. When
dealing with huge amount of data, there are two things that require
optimization: Storage of data and retrieval of data.

Storage: According to the principles of database systems, the data is stored in

such a way that it acquires lot less space as the redundant data (duplicate data)
has been removed before storage. Let’s take a layman example to understand
this:
In a banking system, suppose a customer is having two accounts, one is saving
account and another is salary account. Let’s say bank stores saving account data
at one place (these places are called tables we will learn them later) and salary
account data at another place, in that case if the customer information such as
customer name, address etc. are stored at both places then this is just a wastage
of storage (redundancy/ duplication of data), to organize the data in a better way
the information should be stored at one place and both the accounts should be
linked to that information somehow. The same thing we achieve in DBMS.

Fast Retrieval of data: Along with storing the data in an optimized and
systematic manner, it is also important that we retrieve the data quickly when
needed. Database systems ensure that the data is retrieved as quickly as
possible.

Purpose of Database Systems

The main purpose of database systems is to manage the data. Consider a
university that keeps the data of students, teachers, courses, books etc. To
manage this data we need to store this data somewhere where we can add new
data, delete unused data, update outdated data, retrieve data, to perform these
operations on data we need a Database management system that allows us to
store the data in such a way so that all these operations can be performed on the
data efficiently.

Database systems are much better than traditional file processing systems.

What is a database?

A database is an organized collection of data, so that it can be easily

accessed, managed and updated.

Data is organized into tables, rows, columns, and index it to make it easier to
find relevant information.

Topic-2: Data Models

Data Model gives us an idea that how the final system will look like
after its complete implementation. It defines the data elements and the
relationships between the data elements. Data Models are used to show
how data is stored, connected, accessed and updated in the database
management system.

A Data Model in Database Management System (DBMS), is the concept of

tools that are developed to summarize the description of the database.

It is classified into 3 types:

1. Conceptual Data Model :

Conceptual data model, describes the database at a very high level

and is useful to understand the needs or requirements of the
database. It is this model, that is used in the requirement gathering
process i.e., before the Database Designers start making a
particular database. One such popular model is
the entity/relationship model (ER model). The E/R model
specializes in entities, relationships and even attributes which are
used by the database designers. In terms of this concept, a
discussion can be made even with non-computer science(non-
technical) users and stakeholders, and their requirements can be
understood.

2. Representational Data Model :

This type of data model is used to represent only the logical part
(integers, characters, alpha-numeric data etc.) of the database and
does not represent the physical structure ( bits & bytes) of the
databases. The representational data model allows us to focus
primarily, on the design part of the database. A popular
representational model is Relational model. For example:
In below table, logical data like datatype of Name, Class, Rollnum,
Address and phone num attributes is deiced at designing phase.

Student_Info
Name( cha Class Roll num Address(Alpha Phone
racters) (Alpha ( Alpha numeric) num( intege
numeric) numeric) r)
GAUTAM CSTA 2021UCB6063 Fdhjguy58709 6789809804
NITIN csta 344587dgf Sdgfh575 5767000878
 3. Physical Data Model :
Ultimately, all data in a database is stored physically on a secondary
storage device such as discs and tapes. This is stored in the form of files,
records and certain other data structures. It has all the information of the
format in which the files are present and the structure of the databases,
presence of external data structures and their relation to each other.
Types of Data Model
For the creation of any database, the data model is considered a
logical structure for creating a database. The data model
includes entities, attributes, constraints, relationships, etc. The
data models are used to represent the data and how it is stored
in the database, how data is accessible and updated in the
database management system.
Different Types of Data Models
There are four types of data models: Hierarchical model,
Network model, Entity-relationship model, Relational
model.

1. Hierarchical Model
In this type of data model, the data is organized into a tree-like
structure that has a single root and the data is linked to the root. In
this model, the main hierarchy begins from the root and it expands like
a tree that has child nodes and further expands in the same manner. In
this model the child node has on;e single parent node but one parent
can have multiple child nodes. As the data is stored like tree structure
in this data model when data is retrieved the whole tree is traversed
from the root node. The hierarchical data model contains a one-to-
many relationship between various types of data. The data is stored in
the form of a record and is connected through links.

For Example- there is an organization that has a requirement to store

the information of its employees. The table contains the following
attributes: employee name, employee code, department name, and
last name. And the organization provides a computer for each
employee. So there is a requirement for storing information on a
computer which is stored in a separate table. The computer table store
employee code, serial number, and type. According to the hierarchical
data model, the employee table can be considered as a parent table
and a computer table can be considered as a child node.
2. Network model
The network model is a type of database model which is designed
based on a flexible approach for representing objects and the
relationship exist among objects. The schema is very important in the
network data model which can be represented in the form of a graph
where a relationship is represented using edges and the nodes are
used to represent objects. The basic difference between the
hierarchical model and network model is that data is represented in
the form of hierarchy in a hierarchical data model whereas in network
model the data is represented in the form of a graph. One of the
advantages of a network model is that the basic connections are also
represented in this data model. There are different types of
relationship can exist in this data model like one to one, many to
many, etc. The data access becomes simple to compare to other data
model like the hierarchical model. The parent node and child node are
always connected as there is always a relationship exist among parent-
child node. And the data is not dependent on the other node. One of
the key drawbacks of this model is that this system is not adaptive
toward changes. It means when there is a requirement of some
modification of system it requires to change the whole system which
takes a lot of effort. And to maintain data is difficult to part in this
model as every record is connected via some pointers which make it
difficult to maintain and make the system complex.

3. E-R model
The ER model is used to describe the database structure using the
entity-relationship diagram. The E-R model is just like the blueprint of a
database which is used to implement the database. In the entity set,
the relationship exists which can be shown using the ER diagram. The
entity set consists of a similar type of entities that consist of attributes .

The components of the ER model are relationship set and entity set
and attributes. The entity is the component of data which is
represented as a rectangle in the ER diagram. For example, there are
two entities: college and student and there exist one too many
relationships as there can be more than one student who can go to
college.

The entity which cannot be identified by attributes and which

require a relationship is called a weak entity. For representing a
weak entity the entity is represented in a double rectangle. For
example, there is a bank account but it cannot relate until the bank
name is not known to the bank account is termed as a weak entity.

The attributes are used to represent the property of the entity. In

the ER diagram, the attribute is represented as an oval. There are
different types of attributes like key attribute, composite attribute,
multivalued attribute and derived attribute. For example, a student is
an entity and the related attributes for student entity are student
name, student age, student roll number, student address, etc.

The relationship is represented in a diamond shape in the ER diagram.

The relationships exist among entities. There are multiple types of
relationships like one to one, one to many, many to one, and many to
many.

4. Relational model
In this data model, the data tables are used to collect a group of
elements into the relations. In this model, the relationships and
data are represented using interrelated tables. And in the table,
there are multiple rows and multiple columns in which column
represents the attribute of the entity and the rows are used to
represent records. In this data model there exist different primary key
which issued to distinguish each record in the table. And for retrieving
the data elements the SQL (Structured Query Language) is used.
For using the relational data model the primary key issued as the
fundamental tool. And for each entry in the data set, it needs to be
unique. The data table should not contain any type of inconsistency as
it can create a problem at the time of data retrieval. The other problem
with the relational data model is data duplicacy, incomplete data and
inappropriate links used to connect data.

Topic-3: Database system views and architecture

The Database Management System (DBMS) architecture shows how data in the
database is viewed by the users. It is not concerned about how the data are handled
and processed by the DBMS.
It helps in implementation, design, and maintenance of a database to store and
organize information for companies. The concept of DBMS depends upon its
architecture. The architecture can be designed as centralized, decentralized, or
hierarchical. The architecture of DBMS can be defined at three levels as follows −
  External levels.
 Conceptual levels.
 Internal levels.
The main objective of the three level architecture is nothing but to separate each user
view of the data from the way the database is physically represented. The database
internal structure should be unaffected while changes to the physical aspects of
storage.
The DBA should be able to change the conceptual structure of the database without
affecting all other users.

External level/ View level

External level describes a part of the database that is relevant to each user. This level
insulates the users from the details of conceptual and the internal level.

Conceptual level/ logic level

Conceptual level describes what data is stored into the database and the relationship
among the data.
It represents the following −
 All the entities, attributes and their relationships.
 The constraints on the data.
 Security and integrity information.

Internal level/ storage level

Internal level is the physical representation of the database on the computer. This
level describes how the data is stored in the database. It covers the data structure and
file organization used to store the data on storage devices.
This level is managed by Operating System under the direction of DBMS.
The levels in the architecture of DBMS are shown below in diagram form −
Topic 5: Data Independence
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.

Fig: Data Independence

Topic-6: Entity Relationship Diagram
An Entity–relationship model (ER model) describes the structure of a
database with the help of a diagram, which is known as Entity Relationship
Diagram (ER Diagram). An ER model is a design or blueprint of a database that
can later be implemented as a database. The main components of E-R model
are: entity set and relationship set.

Components of a ER Diagram

As shown in the above diagram, an ER diagram has three main components:

1. Entity
2. Attribute
3. Relationship

1. Entity
An entity is an object or component of data. An entity is represented as rectangle
in an ER diagram.
For example: In the following ER diagram we have two entities Student and
College and these two entities have many to one relationship as many students
study in a single college. We will read more about relationships later, for now
focus on entities.

Weak Entity:
An entity that cannot be uniquely identified by its own attributes and relies on the
relationship with other entity is called weak entity. The weak entity is represented
by a double rectangle. For example – a bank account cannot be uniquely
identified without knowing the bank to which the account belongs, so bank
account is a weak entity.

2. Attribute
An attribute describes the property of an entity. An attribute is represented as
Oval in an ER diagram. There are four types of attributes:

1. Key attribute
2. Composite attribute
3. Multivalued attribute
4. Derived attribute
1. Key attribute:

A key attribute
can uniquely identify an entity from an entity set. For example, student roll
number can uniquely identify a student from a set of students. Key attribute is
represented by oval same as other attributes however the text of key attribute
is underlined.

2. Composite attribute:

An attribute that
is a combination of other attributes is known as composite attribute. For example,
In student entity, the student address is a composite attribute as an address is
composed of other attributes such as pin code, state, country.
3. Multivalued attribute:
An attribute that can hold multiple values is known as multivalued attribute. It is
represented with double ovals in an ER Diagram. For example – A person can
have more than one phone numbers so the phone number attribute is
multivalued.

4. Derived attribute:
A derived attribute is one whose value is dynamic and derived from another
attribute. It is represented by dashed oval in an ER Diagram. For example –
Person age is a derived attribute as it changes over time and can be derived
from another attribute (Date of birth).

3. Relationship
A relationship is represented by diamond shape in ER diagram, it shows the
relationship among entities.

Topic-7: Relationship Types

There are four types of relationships:
1. One to One
2. One to Many
3. Many to One
4. Many to Many

1. One to One Relationship

Entity
Relationship Diagram – ER Diagram in DBMS
BY CHAITANYA SINGH | FILED UNDER: DBMS
An Entity–relationship model (ER model) describes the structure of a
database with the help of a diagram, which is known as Entity Relationship
Diagram (ER Diagram). An ER model is a design or blueprint of a database that
can later be implemented as a database. The main components of E-R model
are: entity set and relationship set.

Topic8: ER Diagram Notations

An ER diagram shows the relationship among entity sets. An entity set is a group
of similar entities and these entities can have attributes. In terms of DBMS, an
entity is a table or attribute of a table in database, so by showing relationship
among tables and their attributes, ER diagram shows the complete logical
structure of a database. Lets have a look at a simple ER diagram to understand
this concept.

A simple ER Diagram:

In the following diagram we have two entities Student and College and their
relationship. The relationship between Student and College is many to one as a
college can have many students however a student cannot study in multiple
colleges at the same time. Student entity has attributes such as Stu_Id,
Stu_Name & Stu_Addr and College entity has attributes such as Col_ID &
Col_Name.

Here are the geometric shapes and their meaning in an E-R Diagram. We will
discuss these terms in detail in the next section(Components of a ER Diagram)
of this guide so don’t worry too much about these terms now, just go through
them once.
Rectangle: Represents Entity sets.
Ellipses: Attributes
Diamonds: Relationship Set
Lines: They link attributes to Entity Sets and Entity sets to Relationship Set
Double Ellipses: Multivalued Attributes
Dashed Ellipses: Derived Attributes
Double Rectangles: Weak Entity Sets
Double Lines: Total participation of an entity in a relationship set

Extra Topics: Characteristics of DBMS

The following are some of the most important and well-known characteristics of
DBMS:

1. Real World Entity

 One of the most important and easy-to-understand characteristics of

DBMS is that it is realistic.
 The DBMS has been designed in such a way that it can cater to the needs
of huge business organizations and can store large data with efficient
operations on them. Database can store things like cost of vegetables or
cost of different brands of breads or milk etc. The entities in the database
look like the real world entities (please have a look at the table for student
database shown below).
 For example, we can have a Database Management System for a School
or a big MNC, and the data is stored in the form of real-world entities. Any
student that is stored in a Student database is like a real-world student
(object/entity) and has properties (commonly known as attributes in DBMS
terms) like his/her name, gender, age, roll number, etc.

2. Self Explaining Nature

 A DBMS contains one database and along with that it also contains
metadata about that one database.
 Metadata is the data about data. For example: In a DBMS for a particular
School, the total Number of rows in the database and what is the name of
each column of the database table, and all such information about the data
is metadata.
 So, the combination of this database and metadata leaves no questions in
anyone’s mind as the DBMS becomes self-explanatory. This is because
the database has all the information in a structured format and if anyone
has any doubts or questions regarding how the database is designed, they
may look into the metadata.

3. Atomicity of Operations (Transactions)

 Atomicity means that either the operation should not be performed or

it should be performed in its entirety i.e. it must be either 0% or 100%
completed.
 DBMS provides us with atomic operations (i.e. any operation which is
either complete 100% or not complete at all). This is very useful and
important. You can understand the importance of atomic operations with
the help of the example given below.
 For instance: Every bank has its own database with data of transactions
and balance all other other useful information of their customers. Let us
take the case of a transaction as it is one of the most commonly seen
atomic operation. Here, a person Aditi transfers 500 Rupees to Rahul by
any of the money transfer facilities available nowadays.
Now, let us say that there was some problem with Rahul’s bank servers. If
databases were not atomic, 500 Rupees would have been debited from
Aditi’s account but not credited to Rahul because of the problems with his
bank server. This would have been such a mess. Who would be
responsible for the lost 500 Rupees as Aditi is innocent and fair in his
transaction?
 Since the databases are atomic, the transaction in the above example
would not have occurred at all and the money would not have been
debited from Aditi’s account as well. This is the importance of atomicity.
 Basically, in case of a successful transaction, the transaction is committed
to the database. This means that the changes that occurred due to the
translation are reflected in the database also. However, in case of an
unsuccessful transaction, the rollback occurs i.e. the database comes back
to its original state (state before the current transaction) so that no changes
are reflected to the database because of any incomplete transaction.

4. Concurrent Access without Anomalies

 Multiple users can access the database at the same time without any
anomalies (problems: like 2 people trying to access the same data might
restrict one from accessing it).
 For instance: Again, let us consider the database of a bank where a
person Rohan has his account. Let us say that Rohan has a brother Neil
and he gives Neil his ATM card to withdraw some cash from the ATM.
Now, when Neil reached the ATM, at the same time Rahul asked Rohan to
transfer 1000 Rupees to him. If Rohan’s initial balance was rupees 5000
and he transfers rupees 1000 to Rahul and he asks Neil to withdraw 2000
Rupees, his balance should now be Rupees 2000 (5000-3000).
If the users would not have concurrent access to the database, either
Neil’s transaction or Rohan’s transaction would have taken place and
instead of 3000 Rupees, only 1000 or 2000 rupees would have been less
from Rohan’s account.
 Thus, we see that concurrent access is such a great characteristic of
DBMS.

5. Stores Any Kind of Structured Data

 A database can store any kind of structured data.

 In most of the books or websites, you will see examples of students or
employees as these are easy to explain but the very important fact is that
the DBMS is not limited to storing only these kinds of information.
 DBMS can store practically any data that exists in the world and is
structured and this is yet another very important characteristic because we
need to work on every kind of data present.
6. Integrity

 Integrity means that the data which comes into the Database should be
correct as well as consistent. Let us see an example to understand the
meaning of correct and consistent data with respect to DBMS.
 For instance: Let us say that there is an XYZ bank and its has its own
database of all the customers of the bank. If we are entering the details of
XYZ Bank’s account and the account number is not specific to that
bank, the data is incorrect. However, if a person has changed his/her
address in the savings account of the same Bank and the current account
still has the old address of the person in the current account’s details, this
is data inconsistency.
 In DBMS, the data that is entered into the database is both correct as well
as consistent. Apart from that, integrating changes is very easy in a
database.
 For instance, if the bank earlier had a constraint of having a minimum
account balance of 0 and now it has changed it to 2000 Rupees, the
DBMS will be able to integrate this change very easily and all the accounts
that do not have a minimum balance of 2000 will be detected and notified
accordingly.

7. Ease of Access (The DBMS Queries)

 Before DBMS, the conventional file system (i.e. files and folders) was used
to store complex and large data.
 Searching for a Name in thousands of Students was a very difficult task.
This is because manual searching is done in a conventional file system for
a particular data. However, DBMS provides ease of access to the data
inside the database.
 We can run a search query to find any data and the process is way faster
than manual searching and is more reliable.
 The CRUD (Create, Read, Update & Delete) operations are very easy to
perform with a database because of the DBMS queries.

8. SQL and No-SQL Databases

 There are 2 types of databases (not DBMS): SQL and No-SQL.

 The SQL databases store the data in the form of Tables i.e. rows and
columns.The No-SQL databases can store data in any other form than a
table.For instance: the very popular MongoDB stores the data in the form
of JSON (JavaScript Object Notation).
 The availability of SQL and No-SQL databases provides us with the
flexibility of choosing the method of storing the data as well.
 There should not be any debate between SQL and No-SQL databases.
The one that we require for a particular project is better on that project
while the other might be better for some other use.
 This is a characteristic of DBMS because DBMS allows us to perform
operations on both kinds of databases. So, we can run queries and
operations on SQL as well as No-SQL databases.

9. ACID Properties

 The DBMS follows certain properties to maintain consistency in the

database. These properties are usually termed as ACID Properties.
 ACID stands for Atomicity, Consistency, Isolation, and Durability.
 We have already talked about atomicity and consistency. Atomicity
means the transaction should either be 0% or 100% completed
and consistency means that the change in data should be reflected
everywhere in a database.
 Isolation means that multiple transactions can occur independently
without the interference of some other transactions.
 Durability means that the chances of a successful atomic transaction i.e.
a transaction that has been 100% completed should reflect in the
database.
 10. Security

 The database should be accessible to the users in a limited way.

 The access to make changes to a database by the user should be limited
and the users must not be given complete access to the entire database.
 Unauthorized users should not be allowed to access the database.
 Authentication: The DBMS has authentication for various users that
directly refers to the limit to which the user can access the Database.
Authentication means the process of laughing in of the user only with the
rights that he/she has been authorized to. For instance, in any
organization, admin has the access to make changes to the database of
the organization as some new employee might have joined the
organization or someone might have left it. However, the employees have
access only to their personal profile and can make changes to it only. They
cannot access the database of any other employee or the organization as
a whole.
 These kinds of security measures are available and built very strongly in a
DBMS.

So, these are some of the most important characteristics of DBMS.

A database schema is the skeleton structure that represents the logical view of the
entire database. It defines how the data is organized and how the relations among
them are associated.
A database schema can be divided broadly into two categories –

 Physical Database Schema − This schema pertains to the actual storage of

data and its form of storage like files, indices, etc. It defines how the data will be
stored in a secondary storage.
 Logical Database Schema − This schema defines all the logical constraints that
need to be applied on the data stored. It defines tables, views, and integrity
constraints.

Database Instance
It is important that we distinguish these two terms individually. Database schema is the
skeleton of database. It is designed when the database doesn't exist at all. Once the
database is operational, it is very difficult to make any changes to it. A database
schema does not contain any data or information.
A database instance is a state of operational database with data at any given time. It
contains a snapshot of the database. Database instances tend to change with time.
Different types of Database Users
Database users are categorized based up on their interaction with the data
base.
These are seven types of data base users in DBMS.
1. Database Administrator (DBA) :
Database Administrator (DBA) is a person/team who defines the schema
and also controls the 3 levels of database.
The DBA will then create a new account id and password for the user if
he/she need to access the data base.
DBA is also responsible for providing security to the data base and he allows
only the authorized users to access/modify the data base.
 DBA also monitors the recovery and back up and provide technical
support.
 The DBA has a DBA account in the DBMS which called a system or
superuser account.
 DBA repairs damage caused due to hardware and/or software failures.

2. Naive / Parametric End Users :

Parametric End Users are the unsophisticated who don’t have any DBMS
knowledge but they frequently use the data base applications in their daily
life to get the desired results.
For examples, Railway’s ticket booking users are naive users. Clerks in any
bank is a naive user because they don’t have any DBMS knowledge but they
still use the database and perform their given task.

3. System Analyst :
System Analyst is a user who analyzes the requirements of parametric end
users. They check whether all the requirements of end users are satisfied.

4. Sophisticated Users :
Sophisticated users can be engineers, scientists, business analyst, who are
familiar with the database. They can develop their own data base
applications according to their requirement. They don’t write the program
code but they interact the data base by writing SQL queries directly through
the query processor.

5. Data Base Designers :

Data Base Designers are the users who design the structure of data base
which includes tables, indexes, views, constraints, triggers, stored
 procedures. He/she controls what data must be stored and how the data
items to be related.

6. Application Program :
Application Program are the back end programmers who writes the code for
the application programs.They are the computer professionals. These
programs could be written in Programming languages such as Visual Basic,
Developer, C, FORTRAN, COBOL etc.

7. Casual Users / Temporary Users :

Casual Users are the users who occasionally use/access the data base but
each time when they access the data base they require the new information,
for example, Middle or higher level manager.