Database Management

System (DBMS)
Dr. Amit Kumar (AI Researcher)

Assistant Professor - VIT Bhopal Uni

 Database Management System

DBMS is a software to manage (Insert, update and deletion )and access

the database in efficient manner.
DBMS Applications
What is data??
• A data can be any meaningful information.
• A data can be a number, text, image , post etc.
Traditional File System
• Before the use of a DBMS, a manual file system was used to maintain the
records and files. Data were stored and processed using a traditional file
system and it makes it easy to find any information.
• In this traditional file system, each file is independent of other file and data
in the different file can be integrated only by writing an individual program
for each application.
• It is a computer file system works under control of operating system.
• A file system becomes increasingly inefficient as more data is added
• The files do not communicate with each other. The organization was simple
to generate and had better local control but the data of an organization is
dispersed throughout the functional subsystem.
File system approach
 Example of File System Approach
The below given tables are independent and don’t do communication with each other therefore redundancy is present.
Here only one table can be used in a single application. For example a student performance application can make use
only student performance table, whereas the Fee due application will make use of the fee due table.

Student Fee Due Student Performance

Student Student Pee Paid Addre Student Student Course Grade Address
ID Name ss ID Name
24 Ram yes B61 24 Ram Data A B44
43 shyam no B24 Structure
Functions of Traditional File System
• Store and arrange the computer files.
• Stored files into database, manipulation, and retrieval by the
computer's operating system.
• Perform services for the end-users, such as updating, insertion,
deletion adding new files to database etc.
• Each program defines and manages its data.
Disadvantages of Traditional File System
• Data redundancy (Each application has its own data file so, same data may have to
be recorded and stored in many times).
• Data inconsistency (Due to the same data items that appear in more than one file do
not get updated simultaneously in each and every file).
• Data dependence (Program and application in the file processing system are data
dependent but, the problem is incompatible with file format).
• Limited data sharing.
• No Access Control
• Less Concurrency level
• High input/output cost
• Inefficient to maintain the record of the big firm having a large number of items.
• Required Lots of labor work to do.
Database Management Approach
 Traditional Vs Database Approach
Traditional File Approach Database Approach
Use separate data file for each application, each data file All Application shares a pool of related and integrated data.
contains single table.
Data redundancy – independent data files included a lot of Minimal data redundancy – Separate data files are
duplicated data. integrated in to a single, logical structure.
Same data is recorded and stored in several files. Each occurrence of a data item is recorded only once.
There is no centralized control for overall data in different There is centralized control for overall data in database.
files.
Data dependence – description of files, records and data Data independence – the database system separates data
items are embedded within individual application programs. descriptions from the application programs that use the data
in it
Modification to data files requires the programs which Data structure can be modified without changing the
access that file to be modified. programs accessing the data
Difficult to manipulation data Easy to manipulation data
Data Base Management System

Management
Data Base + System
 Database Management System
Database Management System (DBMS) as a "software system that
enables users to define, create, maintain and control access to the
database".
Examples of DBMS's include MySQL, PostgreSQL, MSSQL, Oracle
Database, and Microsoft Access.
Functions of DBMS
• Modify, delete, insert the data.
• Security
• Control multi-user access management.
• Achieved data integrity.
• Transaction management.
• Data Access control
• Data Independency
Data Models
Representational model or logical model

OODM
Relational Model
• Relational Model was proposed by E.F. Codd to model data in the form of
relations or tables.
• After designing the conceptual model of Database using ER diagram, we
need to convert the conceptual model in the relational model which can be
implemented using any RDBMS languages like Oracle SQL, MySQL etc.

• What is Relational Model?

• Relational Model represents how data is stored in Relational Databases. A
relational database stores data in the form of relations (tables). Consider a
relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE and
AGE shown in Table 1.
• IMPORTANT TERMINOLOGIES
• Attribute: Attributes are the properties that define a relation. e.g.; ROLL_NO, NAME
• Relation Schema: A relation schema represents name of the relation with its
attributes. e.g.; STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and AGE) is relation
schema for STUDENT. If a schema has more than 1 relation, it is called Relational
Schema.
• Tuple: Each row in the relation is known as tuple. The above relation contains 4
tuples, one of which is shown as:
Functionalities of a DBMS
• Data definition – Creation, modification and removal of definitions that
define the organization of the data.
• Update – Insertion, modification, and deletion of the actual data.
• Retrieval – Providing information in a form directly usable or for further
processing by other applications. The retrieved data may be made available
in a form basically the same as it is stored in the database or in a new form
obtained by altering or combining existing data from the database.
• Administration – Registering and monitoring users, enforcing role based
access control, monitoring performance, maintaining data integrity, dealing
with concurrency control, data independency and recovering information
that has been corrupted by some event such as an unexpected system failure.
Role Based Access Control
• Controlled Access allow users to access only relevant details to which they are authorized.
• In the below given example, A table is stored in the database having fields student id, marks and address. A faculty
can access only the marks of the student and admin department can access their address only.

Student Id Marks address

Student Id Marks Student Id address

Faculty Administrator
Data Integrity
• Data integrity refers to the accuracy and consistency of the data.
• When creating databases, attention needs to given to data integrity and
how to maintain it.
• Maintaining the data integrity means making sure the data remains
intact and unchanged throughout the cycle.
• Example:
❑ a user could accidentally try to enter a phone number into a date
field. If the system enforces data integrity, it will prevent the user
from making these mistakes.
❑ A user tries to enter a date outside an acceptable range.
Data Independency
• Data Independence is defined as a property of DBMS that helps you to change the Database
schema at one level of a database system without requiring to change the schema at the next
higher level. Data independence helps you to keep data separated from all programs that
make use of it.
• Data Independence means users and data should not directly interact with each other. The
user should be at a different level and the data should be present at some other level. By
doing so, Data Independence can be achieved.
• To provide the data independency there should be at least 2 levels of data abstraction.
• Data Abstraction refers to the process of hiding irrelevant details from the user. So, what is
the meaning of irrelevant details? Let's understand this with one example.
• If we want to access any mail from our Gmail then we don't know where that data is
physically stored i.e is the data present in India or USA or what data model has been used to
store that data? We are not concerned about these things. We are only concerned with our
email. So, information like these i.e. location of data and data models are irrelevant to us and
in data abstraction
Three level of data abstraction in DBMS
Types of Schema
View Level or External Schema
This level tells the application about how the data should be shown to the user.
Example: If we have a login-id and password in a university system, then as a
student, we can view our marks, attendance, fee structure, etc. But the faculty of
the university will have a different view. He will have options like salary, edit
marks of a student, enter attendance of the students, etc. So, both the student and
the faculty have a different view. By doing so, the security of the system also
increases. In this example, the student can't edit his marks but the faculty who is
authorized to edit the marks can edit the student's marks. Similarly, the dean of
the college or university will have some more authorization and accordingly, he
will has his view. So, different users will have a different view according to the
authorization they have.
Conceptual Level or Logical Level
• This level tells how the data is actually stored and structured. We have
different data models by which we can store the data(You can read
more about the different types of data model from here). Example: Let
us take an example where we use the relational model for storing the
data. We have to store the data of a student, the columns in the student
table will be student_name, age, mail_id, roll_no etc. We have to
define all these at this level while we are creating the database.
Though the data is stored in the database but the structure of the tables
like the student table, teacher table, books table, etc are defined here in
the conceptual level or logical level. Also, how the tables are related to
each other are defined here. Overall, we can say that we are creating a
blueprint of the data at the conceptual level.
Physical Level or Internal Schema
As the name suggests, the Physical level tells us that where the data is
actually stored i.e. it tells the actual location of the data that is being
stored by the user. The Database Administrators(DBA) decide that which
data should be kept at which particular disk drive, how the data has to be
fragmented, where it has to be stored etc. They decide if the data has to be
centralized or distributed. Though we see the data in the form of tables at
view level the data here is actually stored in the form of files only. It
totally depends on the DBA, how he/she manages the database at the
physical level.
Data Independency
Three level of data abstraction provides two type of data independency.
• A) Physical Independency
• B) Logical Independency
 Physical Data Independence
Physical data independence helps you to separate conceptual levels from
the internal/physical levels. It allows you to provide a logical description
of the database without the need to specify physical structures. Compared
to Logical Independence, it is easy to achieve physical data
independence.
Examples of changes under Physical Data Independence

• Due to Physical independence, any of the below change will not affect
the conceptual layer.
• Using a new storage device like Hard Drive or Magnetic Tapes
• Modifying the file organization technique in the Database
• Switching to different data structures.
• Changing the access method.
• Modifying indexes.
• Changes to compression techniques or hashing algorithms.
• Change of Location of Database from say C drive to D Drive
 Logical Data Independence
Logical Data Independence is the ability to change the conceptual scheme without changing
1.External views
2.External API or programs
Any change made will be absorbed by the mapping between external and conceptual levels.
When compared to Physical Data independence, it is challenging to achieve logical data independence.

Examples of changes under Logical Data Independence

Due to Logical independence, any of the below change will not affect the external layer.
1.Add/Modify/Delete a new attribute, entity or relationship is possible without a rewrite of existing
application programs
2.Merging two records into one
3.Breaking an existing record into two or more records
 Difference between Physical and Logical Data Independence

Logica Data Independence Physical Data Independence

Logical Data Independence is mainly concerned with Mainly concerned with the storage of the data.
the structure or changing the data definition.

It is difficult as the retrieving of data is mainly It is easy to retrieve.

dependent on the logical structure of data.
Compared to Logic Physical independence it is Compared to Logical Independence it is easy to achieve physical data
difficult to achieve logical data independence. independence.
You need to make changes in the Application A change in the physical level usually does not need change at the
program if new fields are added or deleted from the Application program level.
database.
Modification at the logical levels is significant Modifications made at the internal levels may or may not be needed to
whenever the logical structures of the database are improve the performance of the structure.
changed.
Concerned with conceptual schema Concerned with internal schema
Example: Add/Modify/Delete a new attribute Example: change in compression techniques, hashing algorithms,
storage devices, etc
DBMS Architecture

DBMS architecture helps in design, development, implementation, and

maintenance of a database
An n-tier architecture divides the whole system into related but
independent n modules, which can be independently modified, altered,
changed, or replaced.

Types of DBMS Architecture:

• One tier architecture
• Two Tier architecture
• Three Tier Architecture
One Tier Architecture
•The simplest of Database Architecture are 1 tier where a
user directly in interact with the DBMS. The DBMS
provides the facility of Query processing and transaction
management.
Advantages
• Easy to implement and optimize performance.
• Do not have compatibility or Context switching issues.
• Fast for a single user because communication with another system is
not necessary.
• The cost of deployment is less eg - development and management cost.

Disadvantages:
• Do not support remote/ distributed access for data resources.
• Completely unscalable. Only one user can access the system at a given
time via the local client.
Two Tier Architecture
• If the architecture of DBMS is 2-tier, then it must have an application through
which the DBMS can be accessed. Programmers use 2-tier architecture where they
access the DBMS by means of an application. Here the application tier is entirely
independent of the database in terms of operation, design, and programming.
• Presentation layer runs on a client (PC, Mobile, Tablet, etc)
• Data is stored on a Server.
• The server side is responsible for providing query processing and transaction
management functionalities.
• On the client side , the user interface and application program run. The application
establishes the connection in order to communicate with the DBMS
• An application interface which is called ODBC (Open Database Connectivity) an
API which allows the client-side program to call the DBMS. Today most of the
DBMS offers ODBC drivers for their DBMS. 2 tier architecture provides added
security to the DBMS as it is not exposed to the end user directly.
2 Tier Architecture
Disadvantages of 2 Tier Architecture
• A two tier architecture can not respond to multiple request same time,
as a result it cause data integrity issue.
• Less Security as client can directly interact with the database.
• Less scalable: performance degrades when number of user increases.

❑ Advantages of 2 tier architecture:

• Low maintenance.
Three Tier Architecture
Three Tier Architecture
Three Tier Architecture
• 3-tier schema is an extension of the 2-tier architecture. 3-tier
architecture has following layers
• Presentation layer (your PC, Tablet, Mobile, etc.)
• Application layer (server) or Business Layer
• Database Server
Presentation Layer
• This is the topmost level of the application.
• Provides user interface, handles the interaction with the user.
Sometimes called the GUI or client view or front-end.
• It sends content to browsers in the form of HTML/JS/CSS. This might
leverage frameworks like React, Angular, Ember, Aurora, etc.
• It communicates with other tiers by which it provides the results to
the browser/client side.
Business Layer:
• This contains set of rules for processing information, business logic and able
to accommodate many users. Sometimes also called as middleware.
• It processes the inputs received from the clients and interacts with the
database.

Data Layer:
• A database, comprising both data sets and the database management
system or RDBMS software that manages and provides access to the data
(back-end).
• It provides security, data integrity and support application.
• The data tier would be some sort of database, such as a MySQL, SQLite or
PostgreSQL database. All of these are run on a separate database server.
Advantages of 3 tier Architecture
• Data Integrity is maintained. Since there is a middle layer between
client and server, data corruption can be avoided and removed.
• Security is improved. This type of model prevents direct interaction of
the client with the server thereby reducing access to unauthorized
access of data.
• It is scalable as it can handle multiple users at a same time.
 Entity Relation-Ship Model
• An Entity Relationship Diagram (ERD) is a pictorial representation of the
information that can be captured by a database.
• E-R model stands for Entity Relationship model. ER Model is used to
model the logical view of the system from data perspective
• Such a “picture” serves two purposes. It allows database professionals to
describe an overall design concisely yet accurately.
• An ER Diagram can be easily transformed into the relational schema.
• There are three components in ERD: Entities, Attributes, and
Relationships.
Entities
Entities is the basic objects of ERDs. These are the tables of your database,
i.e. students, courses, books, campus, employees, payment, projects. A
specific example of an entity is called an instance. Each instance becomes a
record or a row in a table.
Type of Entities

Entity
Strong weak
Strong entity: An entity set which is having at least one attribute for unique identification is a strong entity. In ER
Diagram strong entity is represented by the help of rectangular box.

Student

Weak Entity: An entity set which is not having any single attribute for unique identification is a weak entity. In ER
diagram Weak entity is represented by the double rectangle.

Visit
Attributes
• Attributes are the properties which define the entity type. For
example, Roll_No, Name, DOB, Age, Address, Mobile_No are the
attributes which defines entity type Student. In ER diagram, attribute
is represented by an oval.

• Types of Attributes:
• Key Attribute, Composite, Derived, Multivalued Attribute.
 • Key Attribute:
The attribute which uniquely identifies each entity in the entity set is
called key attribute. For example, Roll_No will be unique for each student.
In ER diagram, key attribute is represented by an oval with underlying
lines.

• Composite Attribute:
An attribute composed of many other attribute is called as composite
attribute. For example, Address attribute of student Entity type consists of
Street, City, State, and Country. In ER diagram, composite attribute is
represented by an oval comprising of ovals.
Multivalued Attribute:
An attribute consisting more than one value for a given entity. For example, Phone_No (can
be more than one for a given student). In ER diagram, multivalued attribute is represented by
double
 • Derived Attributes:
An attribute which can be derived from other attributes of the entity
type is known as derived attribute. e.g.; Age (can be derived from
DOB). In ER diagram, derived attribute is represented by dashed oval.
ER Diagram
Relationship
• A relationship type represents the association between entity types. For
example,‘Enrolled in’ is a relationship type that exists between entity type
Student and Course. In ER diagram, relationship type is represented by a
diamond and connecting the entities with lines.
 Student Enrolled IN Course
Std_cid Name Age Std_id Course Type
Full time Course Course Fee
132 ram 32 _id _id name
132 BE partime BE bachelor in 1 lakh
engineering
Degree of a relationship set:
• The number of different entity sets participating in a relationship set
is called as degree of a relationship set.
• Unary Relationship –
When there is only ONE entity set participating in a relation, the
relationship is called as unary relationship. For example, one person is
married to only one person.
• Binary Relationship –
When there are TWO entities set participating in a relation, the
relationship is called as binary relationship.For example, Student is enrolled
in Course.

• n-ary Relationship –When there are n entities set participating in a

relation, the relationship is called as n-ary relationship.
Cardinality:
• The number of times an entity of an entity set participates in a
relationship set is known as cardinality. Cardinality can be of different types:
• One to one – When each entity in each entity set can take part only once in the
relationship, the cardinality is one to one. Let us assume that a male can marry to
one female and a female can marry to one male. So the relationship will be one to
one. One to one is represented by the arrows.

Male Married Female

• Many to one – When entities in one entity set can take part only
once in the relationship set and entities in other entity set can take
part more than once in the relationship set, cardinality is many to
one. Let us assume that a student can take only one course but one
course can be taken by many students. So the cardinality will be n to 1.
It means that for one course there can be n students but for one
student, there will be only one course. The entity of the many side will
be linked through a simple line and the entity of side one will be
linked through a arrow.
• Many to many – When entities in all entity sets can take part more than
once in the relationship cardinality is many to many. Let us assume that a
student can take more than one course and one course can be taken by many
students. So the relationship will be many to many.
Participation Constraint:
• Participation Constraint is applied on the entity participating in the relationship
set.
• Total Participation – Each entity in the entity set must participate in the
relationship. If each student must enroll in a course, the participation of student
will be total. Total participation is shown by double line in ER diagram.
• Partial Participation – The entity in the entity set may or may NOT
participate in the relationship. If some courses are not enrolled by any of the
student, the participation of course will be partial. The diagram depicts the
‘Enrolled in’ relationship set with Student Entity set having total participation
and Course Entity set having partial participation.
Every student in Student Entity set is participating in relationship but there exists a course C4 which is not taking part in
the relationship.
Generalization
• Generalization is like a bottom-up approach in which two or more
entities of lower level combine to form a higher level entity if they
have some attributes in common.
• In generalization, an entity of a higher level can also combine with the
entities of the lower level to form a further higher level entity.
• Generalization is more like subclass and superclass system, but the
only difference is the approach. Generalization uses the bottom-up
approach.
• In generalization, entities are combined to form a more generalized
entity, i.e., subclasses are combined to make a superclass.
For example, Faculty and Student entities can be generalized
and create a higher level entity Person.
Specialization
• Specialization is a top-down approach, and it is opposite to Generalization.
In specialization, one higher level entity can be broken down into two lower
level entities.
• Specialization is used to identify the subset of an entity set that shares some
distinguishing characteristics.
• Normally, the superclass is defined first, the subclass and its related
attributes are defined next, and relationship set are then added.
• For example: In an Employee management system, EMPLOYEE entity can
be specialized as TESTER or DEVELOPER based on what role they play in
the company.
Specialization
Aggregation
• In aggregation, the relation between two entities is treated as a single
entity. In aggregation, relationship with its corresponding entities is
aggregated into a higher level entity.
• For example: Center entity offers the Course entity act as a single
entity in the relationship which is in a relationship with another entity
visitor. In the real world, if a visitor visits a coaching center then he
will never enquiry about the Course only or just about the Center
instead he will ask the enquiry about both.
•
Aggregation
 Center Course
Center Address Course Duration
SIRT B93 CSE 4 year
SUB d21 ME 4year
SIRTE y56 Architect 6year

OFFER Enquiry
Offer Center Course Fee Enquiry ID OFFER ID Visitor
ID Name
1 SIRT CSE 30 k 1 2 Ram
2 SUB ME 26k 2 3 Shyam
3 SIRT Civil 20k 3 2 ramu
4 SIRTE CSE 20k
Weak Entity Set
• A weak entity set is an entity set that does not contain sufficient attributes
to uniquely identify its entities.
• In other words, a primary key does not exist for a weak entity set.
• However, it contains a partial key called as a discriminator.
• Discriminator can identify a group of entities from the entity set.
• Discriminator is represented by underlining with a dashed line.
• The combination of discriminator and primary key of the strong entity set
makes it possible to uniquely identify all entities of the weak entity set.
• Thus, this combination serves as a primary key for the weak entity set.
• Clearly, this primary key is not formed by the weak entity set completely.
• Symbols Used-
• A double rectangle is used for representing a weak entity set.
• A double diamond symbol is used for representing the relationship
that exists between the strong and weak entity sets and this
relationship is known as identifying relationship.
• A double line is used for representing the connection of the weak
entity set with the relationship set.
• Total participation always exists in the identifying relationship.
Weak Entity Set
In this ER diagram,
• One strong entity set “Building” and one weak entity set “Apartment” are related
to each other.
• Strong entity set “Building” has building number as its primary key.
• Door number is the discriminator of the weak entity set “Apartment”.
• This is because door number alone can not identify an apartment uniquely as
there may be several other buildings having the same door number.
• Double line between Apartment and relationship set signifies total participation.
• It suggests that each apartment must be present in at least one building.
• Single line between Building and relationship set signifies partial participation.
• It suggests that there might exist some buildings which has no apartment.
•
Weak Entity Set

Account Nomination Nominee

Holder

Account Holder D name Age

E2 Ram 22
E2 Shyam 62
E2 rohan 22
E6 Rohan 26
Example of Weak Entity:
Nomination Nominee (weak Entity)
Account Holder
Emp Name Age DOB Phone Number Emp ID Nominee Nominee Age Workin
ID Name Name g Status
1 John 34 1986 463245 1 SOhan SOhan 34 Working
2 Tom 34 1986 769313 2 SOhan SOhan 30 Not
3 Alice 34 1986 421456 1 Mohan working
Mohan 36 Not
working

Emp Name Age DOB Phone Number Emp ID Nominee Age Working
ID Name Status
1 John 34 1986 463245 1 Sohan 30 Not working
2 Tom 34 1986 769313 2 Sohan 34 working
3 Alice 34 1986 421456 1 Mohan 36 Not working