UNIT-1

 TOPIC: Introduc on to database and ER modelling

 DEFINITION: -
 A Database Management System (DBMS) is a so ware system that is
designed to manage and organize data in a structured manner. It
allows users to create, modify, and query a database, as well as
manage the security and access controls for that database.
 DBMS provides an environment to store and retrieve the data in
convenient and efficient manner.
 Query Languages like SQL are used along with DBMS to allow the
users to interact with the database.

 PURPOSE/USES: -
 Earlier, database applica ons were built directly on top of file systems,
which leads to:
1. Data redundancy and inconsistency: Data is stored in mul ple file
formats resul ng in duplica on of informa on in different files
duplicated data may remain inconsistent.
2. Difficulty in accessing data: Need to write a new program to carry out
each new task.
3. Data isola on: if mul ple users accessing the same data, then they may
access inconsistent data since, mul ple files and formats, ability of a
database to allow a transac on to execute as if there are no other
concurrently running transac ons
4. Integrity problems: Integrity constraints (e.g. account balance > 0)
become a part of program code and hence, a repe ve task. Hard to add
new constraints or change exis ng ones.
5. Atomicity of updates: Any transac on failure may leave database in an
inconsistent state with par ally updated data. E.g. transfer of funds from
one account to another should either complete or not happen at all.
6. Concurrent access by mul ple users: Concurrent access needed for
performance, but uncontrolled concurrent accesses can lead to
 inconsistencies. Ex: Two people reading a balance (say 100) and upda ng
it by withdrawing money (say 50 each) at the same me.
Database systems offer solu ons to all the above problems

 Formal Defini ons:

 DBMS is a set of programs (i.e. so ware) that enables to define, store,

modify/manipulate, retrieve/extract and manage the data in a database.
 DBMS is a set of programs (i.e. so ware) that enables to define, store,
modify/manipulate, retrieve/extract and manage the data in a database.
 DBMS is a set of programs (i.e. so ware) that enables to define, store,
modify/manipulate, retrieve/extract and manage the data in a database.
 DBMS Examples: MySQL, MS-Access, Oracle, dBase, FoxPro, etc.

 Need, Advantages of DBMS/Disadvantages of file system:

 The file management system is a tradi onal mechanism to store data
permanently into secondary devices but in today digital word TBs of data
need to be stored in an organized place which needs security as well as
retrieval in seconds of me
 Tradi onal file systems have the very lowest level of storage and
processing capabili es which created problems regarding security,
integrity, memory storage and duplicate data, etc.
 For these problems, DBMS is a solu on:

1. Data retrieval: If you want to retrieve data from the flat file then we
must develop applica on programs in a high-level language, so that data
can be stored and retrieved Fastly and securely within the me bound.
2. Data redundancy: In any storage, we need to make copies of data for
backup but in tradi onal file management systems once we update data
in one loca on some mes it fails to get updated in the copy of the data,
so that it may create problems of inconsistency this rate is called
duplicate data or redundant data. The database automa cally maintains
consistent data through a transac on using certain rules and procedures
3. Data Integrity: Data integrity ensure that only required data is stored in
the database data is validated before entered into the database using
integrity constraints such as primary key, foreign key, etc.
4. Data security: In tradi onal file management, there is no authen ca on
mechanism at high-end whereas DBMS provides levels of security
authen ca on which can be done at user level admin level, etc.
5. Data Indexing: If you want to retrieve data very fastly from the database
we are using indexing mechanism whereas Flat files don’t support
indexing and solely depend upon secondary storage devices. Indexing is
a mechanism where data is uniquely iden fied and stored using some

 Applica ons Of DBMS:

1. Accoun ng: Database systems are used in maintaining informa on
employees, salaries, and payroll taxes.
2. Manufacturing: For management of supply chain and tracking
produc on of items in factories database systems are maintained.
3. Retail Shops: For maintaining customer, product and purchase
informa on the databases are used.
4. Banking: In banking sector, for customer informa on, accounts and loan
and for performing banking applica ons the DBMS is used. For purchase
on credit cards and genera on of monthly statements database systems
are useful.
5. Universi es: The database systems are used in universi es for
maintaining student informa on, course registra on, and accoun ng.
6. Reserva on systems: In airline / railway reserva on systems, the
database is used to maintain the reserva on and schedule informa on.
7. Telecommunica on: In telecommunica ons for keeping records of the
calls made, genera ng monthly bills, maintaining balances on prepaid
calling cards, and storing informa on about communica on networks
the database systems are used.

 Data Abstrac on/Levels of Abstrac on:-

 Data abstrac on means retrieving only the required amount of

informa on about the system and hiding background details.
 Main objec ve of DBMS is to provide the user an abstract view of data.
 DBMS hides- how data is stored and maintained.
 Complex data structures are used to store & retrieve the data efficiently
 This complexity of data storage and maintenance is hidden
 Three levels of abstrac on are provided to hide complexity and simplify
user’s interac on with the system.
 Levels of Abstrac on:
 Physical level: It is the lowest level abstrac on. It describes how data is
actually or physically stored (i.e. using HDD, SSD, etc.).
 Logical level: It is the middle level abstrac on. It describes what data is
stored in database and the rela onships among the data.
 View level: It is the highest level of abstrac on. It consists of applica on
programs used to serve the required data; generally, they hide details of
data
 A view refers to only some part of the en re DB/ table
 Many of the DB users do not need all the info. at a me, they need to
access only a part of DB. There are many different views of same data.
E.g. authen ca on system for different users.

 Database Languages:
 There are three types of languages supported by database systems.

1. DDL (Data Defini on Language):

 DDL or Data Defini on Language actually consists of the SQL
commands that can be used to define the database schema.
 It is a language used for crea ng and modifying the structures of
tables, views, indexes, etc. but not the data.
 CREATE: command creates database objects, tables, and triggers.
 ALTER: command alters the database structure by adding, dele ng,
and modifying columns of the already exis ng tables.
 DROP: command deletes the defined table with all the table data.
  TRUNCATE: command deletes all the data and records from an
exis ng table, including the allocated spaces for the records without
WHERE Clause.
 RENAME: command changes the table name when the user or
administrator wants to give a more relevant name to it.

2. DML (Data Manipula on Language):

 DML commands are SQL commands that perform opera ons like
storing data in database tables, modifying and dele ng exis ng rows,
retrieving data, or upda ng data.
 SELECT: command fetches data or records from one or more tables in
the SQL database.
 INSERT: command inserts one or more new records into the table in
the SQL database.
 UPDATE: command updates or changes the exis ng data or records in
a table in the SQL database.
 DELETE: command deletes all the exis ng records and the allocated
spaces from a table in the SQL database. We can use the WHERE
clause with the AND or OR operators to delete selected rows from the
database.
3. DCL (Data Control Language):
 DCL includes commands such as GRANT and REVOKE which mainly
deal with the rights, permissions, and other controls of the database
system.
 GRANT: Assigns new privileges to a user account, allowing access to
specific database objects, ac ons, or func ons.
 REMOVE: Removes previously granted privileges from a user account,
taking away their access to certain database objects or ac ons.

 Database System Structure:

 Database Architecture is logically divided into two types:
1. Two- er architecture in DBMS refers to a client-server architecture
where the user interface and the applica on logic are separated into two
separate components.
 The client component is typically the user interface and the server
component is responsible for handling the data and business logic.
 An interface called ODBC (Open Database Connec vity) provides an API
(i.e. the driver) that allows client-side program to communicate with the
DBMS.
 When mul ple users access the database server at the same me then
there may be the problem of security and scalability. So the three- er
architecture come into picture.
2. Three-Tier Architecture is commonly used for building web applica ons
which uses an intermediate layer called the applica on server.
 It uses the web-browser on client side (1st er) to communicate with the
server
 Server runs a so ware called web-server which searches the relevant
web-page as a response to client request
 A so ware called applica on server (2nd er) runs the script wri en on
the fetched page (i.e. the business logic)
 Data required to execute this script is fetched from the Database-server
(3rd er)
 Data models:
 It is a collec on of conceptual tools for describing data, rela onships
among data, seman cs (meaning) of data and constraints.
 A Data Model in Database Management System (DBMS) is the concept of
tools that are developed to summarize the descrip on of the database.
1. Rela onal data model.
2. Object-based data model.
3. Semi-structured data model.
4. En ty-Rela onship model.
 Explana on:
1. Rela on Data model:
 It uses a collec on of tables to represent both data and the rela onship
among these data.
 A table has rows and columns, where rows represent records and
columns represent the a ributes.
 A table has rows and columns, where rows represent records and
columns represent the a ributes.
 Rela onal model is most widely used data model for commercial data
processing because of its simplicity and ease to maintain.
2. Object Oriented Data model:
 The Object-Oriented Model in DBMS or OODM is the data model where
data is stored in the form of objects.
 This model is used to represent real-world en es. The data and data
rela onship are stored together in a single en ty known as an object in
the Object-Oriented Model.
 An object provides informa on about rela onship between data within
the object, as well as informa on about its rela onship with other
objects.
 A Class is a collec on of similar objects having a shared structure
(A ributes) and behaviour (Methods).

3. Semi-structured data model:

 The semi-structured data model is a type of data model that allows
for flexible representation of data without requiring a strict schema or
predefined structure.
 When the data is huge in amount yet flexible, using a DBMS to store,
retrieve and manipulate the data is not feasible.
 So, such data is kept in semi-structured model, where it is stored as text
data but under a specific structure.
 The extensible Markup Language (XML) is widely used for represen ng
the semi-structured data.
 In XML we can create tags and use different markups to describe the
data.
 E.g.
<Product name= “Lenovo” country = “China”>
<price> 42000</price>
<offer>3000</offer>
<category>electronics <subcategory> laptops </subcategory>
</category>
</Product>
• No predefined schema to store data.

4. Hierarchical data model:

 This is one of the oldest models in a data model which was
developed by IBM, in the 1950s.
 In a hierarchical model, data are viewed as a collection of tables,
or we can say segments that form a hierarchical relation.
 In this, the data is organized into a tree-like structure where each
record consists of one parent record and many children.
 In the hierarchical model, segments pointed to by the logical associa on
are called the child segment and the other segment is called the parent
segment.
 The major drawback was, here we had only one to many rela onships
between nodes.

5. Network data Model:

 This data model is the extension of hierarchical model, because here we
use graphs instead of trees to store rela ons between parent and child.
 In this model, en es (i.e. nodes) are organized in a graph structure, in
which each en ty can be accessed through several paths, thus we have
one to many and many to one rela ons.
 It is not necessary that all en ty nodes are connected to all other en ty
nodes.
 Links between nodes represent the way data can be possibly accessed
from any node (i.e. no root node concept).
 But Hierarchical and Network model are obsolete and are no more used.
Hence, Rela onal model is the most common data model.
 En ty-Rela onship model:
 It is a high model data model diagram.
 It describes the structure of a database with the help of a diagram called
as E-R diagram.
 E-R models describe real world: En es E.g. customers, accounts, bank
branch and Rela onships: between en es E.g. Account A-101 is held by
customer Johnson.
 A database design in E-R model usually is converted to design in the
rela onal model which is used for storage and processing.
 ER model diagram has the following three components:
 ER diagrams:
 ER diagrams represent the E-R model in a database, making them easy
to convert into relations (tables).
 ER diagrams require no technical knowledge and no hardware support.
These diagrams are very easy to understand and easy to create even for
a naive user.
 It gives a standard solu on for visualizing the data logically.
 Symbols used in ER-diagram:

 En ty:
 An en ty is anything in the real world which has its independence
existence, such as an object, class, person, or place.
 Objects that physically exist and are logically constructed in the real
world are called en es.
 Each en ty consists of several characteris cs or a ributes that describe
that en ty. For example, if a person is an en ty, its a ributes or
 characteris cs are age, name, height, weight, occupa on, address,
hobbies, and so on.
 En ty type: A collec on of en es having same a ributes. E.g. Student
en ty, having different students with their names, roll-no, id etc. as their
a ributes.
 En ty Set: It is the collec on of en es of a par cular en ty-type at a
point in me. E.g. In Student en ty if we consider all students having roll
no between 1-10, then it will make en ty set.

1. Strong en ty:
 A strong en ty is an en ty type that has a key a ribute. It doesn't
depend on other en es in the schema. A strong en ty always has a
primary key, and it is represented by a single rectangle in the ER diagram.

2. Weak En ty:
 They are called Weak en ty types because they can’t be iden fied on
their own. It relies on another powerful en ty for its unique iden ty.
 Weak en ty type doesn’t have a key a ribute and so we cannot uniquely
iden fy them by their a ributes alone. Therefore, a foreign key must be
used in combina on with its a ributes to create a primary key.
 A weak en ty is represented by a double-outlined rectangle in ER
diagrams.
 A ributes:
 An en ty is represented by a set of a ributes i.e. the descrip ve
proper es possessed by all members of its en ty set.
 In the ER diagram, the a ribute is represented by an oval.
 E.g. Student is an en ty and its a ributes are id, phone no, name, age,
class.

 Types of A ributes:
1. Simple a ributes: A ributes that cannot be further decomposed
into sub-a ributes are called simple a ributes. It's an atomic value
and is also known as the key a ribute. The simple a ributes are
represented by an oval shape in ER diagrams.
2. Composite A ributes: An a ribute that is composed of many
other a ributes and can be decomposed into simple a ributes is
known as a composite a ribute. The composite a ribute is
represented by an ellipse. It shows tree-like structure.

3. Mul valued a ributes: It is the opposite of the single-valued

a ribute. Mul valued a ributes have mul ple values for the
single instance of an en ty.
e.g. Here the student has an a ribute named phone no. One student
can have mul ple phone no, so we can say that phone no can have
mul ple values.

4. Derived a ribute: Derived a ributes are those a ributes that do

not exist in the database physically, but their values can be derived
from other a ributes present in the database. These are
represented by dashed ellipse.
5. Key a ribute: The a ribute which uniquely iden fies each en ty I
the en ty-set is called key a ributes. In simple terms it represents
the primary key for the en ty. It is represented in oval shap with
a ribute underlined.

6. Stored a ribute: If the data of the a ribute remains constant for

every instance of en ty, then it is called a stored a ribute.
The value of the a ribute present in the database does not get
updated and it remains constant once it is stored.
 Rela onships: The concept of rela onship in DBMS is used to describe
the rela onship between different en es. This is denoted by the
diamond or a rhombus symbol. For example, the teacher en ty type is
related to the student en ty type and their rela on is represented by the
diamond shape.
 Cardinality mapping/ra o: It expresses the number of en es to which
an en ty can be related via a rela onship. We express cardinality
constraints by drawing either a directed line ( ) signifying “one” or an
undirected line (—), signifying “many” between the rela onship set and
the en ty set.

1. One to one (1:1): When one en ty from en ty set A can be associated

with at most one en ty of set B and vice versa, then it is called as 1:1
rela onship/cardinality.
 2. One to many (1: N): If only one instance of the en ty on the le side of
the rela onship is linked to mul ple instances of the en ty on the right
side, then this is considered a given one-to-many rela onship.

3. Many-to-One(N:1): If only one instance of the en ty on the le side of the

rela onship is linked to mul ple instances of the en ty on the right side,
then this is considered a given one-to-many rela onship. For example, a
student enrolls for only one course, but a course can have many students.

4. Many to many (M: N): If mul ple instances of the en ty on the le are
linked by rela onships to mul ple instances of the en ty on the right,
this is considered a many-to-one-rela onship means rela onship. For
example, one employee can be assigned many projects, and one project
can be assigned by many employees.
 Extended ER Features: E-R model with addi onal seman cs is called as
extended en ty rela onship model or EER model. As the complexity of
data increased in the late 1990’s, it became very difficult to use the
tradi onal ER model for database modelling. Hence some improvements
or enhancements are done to make data modelling be er called as EER.
 There are three concepts in EER:
1. Generaliza on:
2. Specializa on:
 3. Aggrega on:

---------------------------------------------------------------------------------------------------------