Data Models in DBMS

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

concept of tools that are developed to summarize the description of
the database. Data Models provide us with a transparent picture of
data which helps us in creating an actual database. It shows us from
the design of the data to its proper implementation of data.
Types of Relational Models
1. Conceptual Data Model
2. Representational Data Model
3. Physical Data Model
It is basically classified into 3 types:-

1. Conceptual Data Model

The 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 that are used by 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.
Entity-Relationship Model( ER Model): It is a high-level data
model which is used to define the data and the relationships
between them. It is basically a conceptual design of any database
which is easy to design the view of data.
Components of ER Model:
1. Entity: An entity is referred to as a real-world object. It can be a
name, place, object, class, etc. These are represented by a
rectangle in an ER Diagram.
2. Attributes: An attribute can be defined as the description of the
entity. These are represented by Ellipse in an ER Diagram. It can
be Age, Roll Number, or Marks for a Student.
3. Relationship: Relationships are used to define relations among
different entities. Diamonds and Rhombus are used to show
Relationships.
Characteristics of a conceptual data model
 Offers Organization-wide coverage of the business concepts.
 This type of Data Models are designed and developed for a
business audience.
 The conceptual model is developed independently of hardware
specifications like data storage capacity, location or software
specifications like DBMS vendor and technology. The focus is to
represent data as a user will see it in the “real world.”
Conceptual data models known as Domain models create a common
vocabulary for all stakeholders by establishing basic concepts and
scope
2. Representational Data Model
This type of data model is used to represent only the logical part of
the database and does not represent the physical structure of the
database. The representational data model allows us to focus
primarily, on the design part of the database. A popular
representational model is a Relational model. The relational Model
consists of Relational Algebra and Relational Calculus. In the
Relational Model, we basically use tables to represent our data and
the relationships between them. It is a theoretical concept whose
practical implementation is done in Physical Data Model.
The advantage of using a Representational data model is to provide
a foundation to form the base for the Physical model
3. Physical Data Model
The physical Data Model is used to practically implement Relational
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 on the format in which the files are present and the
structure of the databases, the presence of external data structures,
and their relation to each other. Here, we basically save tables in
memory so they can be accessed efficiently. In order to come up
with a good physical model, we have to work on the relational model
in a better way. Structured Query Language (SQL) is used to
practically implement Relational Algebra.
This Data Model describes HOW the system will be implemented
using a specific DBMS system. This model is typically created by
DBA and developers. The purpose is actual implementation of the
database.
Characteristics of a physical data model:
 The physical data model describes data need for a single project
or application though it maybe integrated with other physical
data models based on project scope.
 Data Model contains relationships between tables that which
addresses cardinality and nullability of the relationships.
 Developed for a specific version of a DBMS, location, data storage
or technology to be used in the project.
 Columns should have exact datatypes, lengths assigned and
default values.
 Primary and Foreign keys, views, indexes, access profiles, and
authorizations, etc. are defined
Some Other Data Models
1. Hierarchical Model
The hierarchical Model is one of the oldest models in the 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. Even if the segments are connected as a chain-
like structure by logical associations, then the instant structure can
be a fan structure with multiple branches. We call the illogical
associations as directional associations.
2. Network Model
The Network Model was formalized by the Database Task group in
the 1960s. This model is the generalization of the hierarchical
model. This model can consist of multiple parent segments and
these segments are grouped as levels but there exists a logical
association between the segments belonging to any level. Mostly,
there exists a many-to-many logical association between any of the
two segments.
3. Object-Oriented Data Model
In the Object-Oriented Data Model, data and their relationships are
contained in a single structure which is referred to as an object in
this data model. In this, real-world problems are represented as
objects with different attributes. All objects have multiple
relationships between them. Basically, it is a combination of Object
Oriented programming and a Relational Database Model.
4. Float Data Model
The float data model basically consists of a two-dimensional array of
data models that do not contain any duplicate elements in the array.
This data model has one drawback it cannot store a large amount of
data that is the tables can not be of large size.
5. Context Data Model
The Context data model is simply a data model which consists of
more than one data model. For example, the Context data model
consists of ER Model, Object-Oriented Data Model, etc. This model
allows users to do more than one thing which each individual data
model can do.
6. Semi-Structured Data Model
Semi-Structured data models deal with the data in a flexible way.
Some entities may have extra attributes and some entities may
have some missing attributes. Basically, you can represent data
here in a flexible way.
Advantages of Data Models
1. Data Models help us in representing data accurately.
2. It helps us in finding the missing data and also in minimizing
Data Redundancy.
3. Data Model provides data security in a better way.
4. The data model should be detailed enough to be used for building
the physical database.
5. The information in the data model can be used for defining the
relationship between tables, primary and foreign keys, and stored
procedures.
Disadvantages of Data Models
1. In the case of a vast database, sometimes it becomes difficult to
understand the data model.
2. You must have the proper knowledge of SQL to use physical
models.
3. Even smaller change made in structure require modification in
the entire application.
4. There is no set data manipulation language in DBMS.
5. To develop Data model one should know physical data stored
characteristics.

Relational Model in DBMS

E.F. Codd proposed the relational Model to model data in the form of
relations or tables. After designing the conceptual model of the
Database using ER diagram, we need to convert the conceptual
model into a relational model which can be implemented using
any RDBMS language like Oracle SQL, MySQL, etc. So we will see
what the Relational Model is.
The relational model uses a collection of tables to represent both
data and the relationships among those data. Each table has
multiple columns, and each column has a unique name. Tables are
also known as relations. The relational model is an example of a
record-based model. Record-based models are so named because
the database is structured in fixed-format records of several types.
Each table contains records of a particular type. Each record type
defines a fixed number of fields, or attributes. The columns of the
table correspond to the attributes of the record type. The relational
data model is the most widely used data model, and a vast majority
of current database systems are based on the relational model.
What is the Relational Model?
The relational model represents how data is stored in Relational
Databases. A relational database consists of a collection of tables,
each of which is assigned a unique name. Consider a relation
STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE, and
AGE shown in the table.
Table Student
ROLL_NO NAME ADDRESS PHONE AGE

1 RAM DELHI 9455123451 18

2 RAMESH GURGAON 9652431543 18

3 SUJIT ROHTAK 9156253131 20

4 SURESH DELHI 18
Important Terminologies
 Attribute: Attributes are the properties that define an entity.
e.g.; ROLL_NO, NAME, ADDRESS
 Relation Schema: A relation schema defines the structure of the
relation and represents the name of the relation with its
attributes. e.g.; STUDENT (ROLL_NO, NAME, ADDRESS, PHONE,
and AGE) is the 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 a tuple. The above
relation contains 4 tuples, one of which is shown as:
1 RAM DELHI 9455123451 18

 Relation Instance: The set of tuples of a relation at a particular

instance of time is called a relation instance. Table 1 shows the
relation instance of STUDENT at a particular time. It can change
whenever there is an insertion, deletion, or update in the
database.
 Degree: The number of attributes in the relation is known as the
degree of the relation. The STUDENT relation defined above has
degree 5.
 Cardinality: The number of tuples in a relation is known
as cardinality. The STUDENT relation defined above has
cardinality 4.
 Column: The column represents the set of values for a particular
attribute. The column ROLL_NO is extracted from the relation
STUDENT.
ROLL_NO

4
 NULL Values: The value which is not known or unavailable is
called a NULL value. It is represented by blank space. e.g.; PHONE
of STUDENT having ROLL_NO 4 is NULL.
 Relation Key: These are basically the keys that are used to
identify the rows uniquely or also help in identifying tables. These
are of the following types.
o Primary Key
o Candidate Key
o Super Key
o Foreign Key
o Alternate Key
o Composite Key
Constraints in Relational Model
While designing the Relational Model, we define some conditions
which must hold for data present in the database are called
Constraints. These constraints are checked before performing any
operation (insertion, deletion, and updation ) in the database. If
there is a violation of any of the constraints, the operation will fail.
Domain Constraints
These are attribute-level constraints. An attribute can only take
values that lie inside the domain range. e.g.; If a constraint AGE>0
is applied to STUDENT relation, inserting a negative value of AGE
will result in failure.
Key Integrity
Every relation in the database should have at least one set of
attributes that defines a tuple uniquely. Those set of attributes is
called keys. e.g.; ROLL_NO in STUDENT is key. No two students can
have the same roll number. So a key has two properties:
 It should be unique for all tuples.
 It can’t have NULL values.
Referential Integrity
When one attribute of a relation can only take values from another
attribute of the same relation or any other relation, it is
called referential integrity. Let us suppose we have 2 relations
Table Student
ROLL_NO NAME ADDRESS PHONE AGE BRANCH_CODE

1 RAM DELHI 9455123451 18 CS

ROLL_NO NAME ADDRESS PHONE AGE BRANCH_CODE

2 RAMESH GURGAON 9652431543 18 CS

3 SUJIT ROHTAK 9156253131 20 ECE

4 SURESH DELHI 18 IT

Table Branch
BRANCH_CODE BRANCH_NAME

CS COMPUTER SCIENCE

IT INFORMATION TECHNOLOGY

ELECTRONICS AND
ECE
COMMUNICATION ENGINEERING

CV CIVIL ENGINEERING

BRANCH_CODE of STUDENT can only take the values which are

present in BRANCH_CODE of BRANCH which is called referential
integrity constraint. The relation which is referencing another
relation is called REFERENCING RELATION (STUDENT in this case)
and the relation to which other relations refer is called REFERENCED
RELATION (BRANCH in this case).
Anomalies in the Relational Model
An anomaly is an irregularity or something which deviates from the
expected or normal state. When designing databases, we identify
three types of anomalies: Insert, Update, and Delete.
Insertion Anomaly in Referencing Relation
We can’t insert a row in REFERENCING RELATION if referencing
attribute’s value is not present in the referenced attribute value.
e.g.; Insertion of a student with BRANCH_CODE ‘ME’ in STUDENT
relation will result in an error because ‘ME’ is not present in
BRANCH_CODE of BRANCH.
Deletion/ Updation Anomaly in Referenced Relation:
We can’t delete or update a row from REFERENCED RELATION if the
value of REFERENCED ATTRIBUTE is used in the value of
REFERENCING ATTRIBUTE. e.g; if we try to delete a tuple from
BRANCH having BRANCH_CODE ‘CS’, it will result in an error
because ‘CS’ is referenced by BRANCH_CODE of STUDENT, but if we
try to delete the row from BRANCH with BRANCH_CODE CV, it will be
deleted as the value is not been used by referencing relation. It can
be handled by the following method:
On Delete Cascade
It will delete the tuples from REFERENCING RELATION if the value
used by REFERENCING ATTRIBUTE is deleted from REFERENCED
RELATION. e.g.; For, if we delete a row from BRANCH with
BRANCH_CODE ‘CS’, the rows in STUDENT relation with
BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will be deleted.
On Update Cascade
It will update the REFERENCING ATTRIBUTE in REFERENCING
RELATION if the attribute value used by REFERENCING ATTRIBUTE is
updated in REFERENCED RELATION. e.g;, if we update a row from
BRANCH with BRANCH_CODE ‘CS’ to ‘CSE’, the rows in STUDENT
relation with BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will
be updated with BRANCH_CODE ‘CSE’.
Super Keys
Any set of attributes that allows us to identify unique rows (tuples)
in a given relationship is known as super keys. Out of these super
keys, we can always choose a proper subset among these that can
be used as a primary key. Such keys are known as Candidate keys.
If there is a combination of two or more attributes that are being
used as the primary key then we call it a Composite key.
Codd Rules in Relational Model
Edgar F Codd proposed the relational database model where he
stated rules. Now these are known as Codd’s Rules. For any
database to be the perfect one, it has to follow the rules.
For more, refer to Codd Rules in Relational Model.
Advantages of the Relational Model
 Simple model: Relational Model is simple and easy to use in
comparison to other languages.
 Flexible: Relational Model is more flexible than any other
relational model present.
 Secure: Relational Model is more secure than any other
relational model.
 Data Accuracy: Data is more accurate in the relational data
model.
 Data Integrity: The integrity of the data is maintained in the
relational model.
 Operations can be Applied Easily: It is better to perform
operations in the relational model.
Disadvantages of the Relational Model
 Relational Database Model is not very good for large databases.
 Sometimes, it becomes difficult to find the relation between
tables.
 Because of the complex structure, the response time for queries
is high.
Characteristics of the Relational Model
 Data is represented in rows and columns called relations.
 Data is stored in tables having relationships between them called
the Relational model.
 The relational model supports the operations like Data definition,
Data manipulation, and Transaction management.
 Each column has a distinct name and they are representing
attributes.
 Each row represents a single entity.

Introduction of ER Model
Peter Chen developed the ER diagram in 1976. The ER model was
created to provide a simple and understandable model for
representing the structure and logic of databases. It has since
evolved into variations such as the Enhanced ER Model and the
Object Relationship Model
The Entity Relational Model is a model for identifying entities to be
represented in the database and representation of how those
entities are related. The ER data model specifies enterprise schema
that represents the overall logical structure of a database
graphically.
The Entity Relationship Diagram explains the relationship among the
entities present in the database. ER models are used to model real-
world objects like a person, a car, or a company and the relation
between these real-world objects. In short, the ER Diagram is the
structural format of the database.
Why Use ER Diagrams In DBMS?
 ER diagrams represent the E-R model in a database, making them
easy to convert into relations (tables).
 ER diagrams provide the purpose of real-world modeling of
objects which makes them intently useful.
 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 solution for visualizing the data logically.
Symbols Used in ER Model
ER Model is used to model the logical view of the system from a
data perspective which consists of these symbols:
 Rectangles: Rectangles represent Entities in the ER Model.
 Ellipses: Ellipses represent Attributes in the ER Model.
 Diamond: Diamonds represent Relationships among Entities.
 Lines: Lines represent attributes to entities and entity sets with
other relationship types.
 Double Ellipse: Double Ellipses represent Multi-Valued
Attributes.
 Double Rectangle: Double Rectangle represents a Weak Entity.

Symbols used in ER Diagram

Components of ER Diagram
ER Model consists of Entities, Attributes, and Relationships among
Entities in a Database System.

What is Entity?
An Entity may be an object with a physical existence – a particular
person, car, house, or employee – or it may be an object with a
conceptual existence – a company, a job, or a university course.
What is Entity Set?
An Entity is an object of Entity Type and a set of all entities is called
an entity set. For Example, E1 is an entity having Entity Type
Student and the set of all students is called Entity Set. In ER
diagram, Entity Type is represented as:
 Entity Set

We can represent the entity set in ER Diagram but can’t represent

entity in ER Diagram because entity is row and column in the
relation and ER Diagram is graphical representation of data.
Types of Entity
There are two types of entity:
1. Strong Entity
A Strong Entity is a type of entity that has a key Attribute. Strong
Entity does not depend on other Entity in the Schema. It has a
primary key, that helps in identifying it uniquely, and it is
represented by a rectangle. These are called Strong Entity Types.
2. Weak Entity
An Entity type has a key attribute that uniquely identifies each
entity in the entity set. But some entity type exists for which key
attributes can’t be defined. These are called Weak Entity types.
For Example, A company may store the information of dependents
(Parents, Children, Spouse) of an Employee. But the dependents
can’t exist without the employee. So Dependent will be a Weak
Entity Type and Employee will be Identifying Entity type for
Dependent, which means it is Strong Entity Type.
A weak entity type is represented by a Double Rectangle. The
participation of weak entity types is always total. The relationship
between the weak entity type and its identifying strong entity type
is called identifying relationship and it is represented by a double
diamond.
 Strong Entity and Weak Entity

What is Attributes?
Attributes are the properties that define the entity type. For
example, Roll_No, Name, DOB, Age, Address, and Mobile_No are the
attributes that define entity type Student. In ER diagram, the
attribute is represented by an oval.

Attribute

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

Key Attribute

2. Composite Attribute
An attribute composed of many other attributes is called a
composite attribute. For example, the Address attribute of the
student Entity type consists of Street, City, State, and Country. In ER
diagram, the composite attribute is represented by an oval
comprising of ovals.
 Composite Attribute

3. Multivalued Attribute
An attribute consisting of more than one value for a given entity. For
example, Phone_No (can be more than one for a given student). In
ER diagram, a multivalued attribute is represented by a double
oval.

Multivalued Attribute

4. Derived Attribute
An attribute that can be derived from other attributes of the entity
type is known as a derived attribute. e.g.; Age (can be derived from
DOB). In ER diagram, the derived attribute is represented by a
dashed oval.

Derived Attribute

The Complete Entity Type Student with its Attributes can be

represented as:
 Entity and Attributes

Relationship Type and Relationship Set

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, the
relationship type is represented by a diamond and connecting the
entities with lines.

Entity-Relationship Set

A set of relationships of the same type is known as a relationship

set. The following relationship set depicts S1 as enrolled in C2, S2 as
enrolled in C1, and S3 as registered in C3.
 Relationship Set

Degree of a Relationship Set

The number of different entity sets participating in a relationship set
is called the degree of a relationship set.
1. Unary Relationship: When there is only ONE entity set
participating in a relation, the relationship is called a unary
relationship. For example, one person is married to only one
person.

Unary Relationship

2. Binary Relationship: When there are TWO entities set

participating in a relationship, the relationship is called a binary
relationship. For example, a Student is enrolled in a Course.

Binary Relationship
3. Ternary Relationship: When there are three entity sets
participating in a relationship, the relationship is called a ternary
relationship.
4. N-ary Relationship: When there are n entities set participating
in a relationship, the relationship is called an n-ary relationship.
What is 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:
1. 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 one female and a female can marry
one male. So the relationship will be one-to-one.
the total number of tables that can be used in this is 2.

one to one cardinality

Using Sets, it can be represented as:

Set Representation of One-to-One

2. One-to-Many: In one-to-many mapping as well where each

entity can be related to more than one entity and the total number
of tables that can be used in this is 2. Let us assume that one
surgeon department can accommodate many doctors. So the
Cardinality will be 1 to M. It means one department has many
Doctors.
total number of tables that can used is 3.

one to many cardinality

3. Many-to-One: When entities in one entity set can take part only
once in the relationship set and entities in other entity sets 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 total number of tables that can be used in this is 3.

many to one cardinality

Using Sets, it can be represented as:

 Set Representation of Many-to-One

In this case, each student is taking only 1 course but 1 course has
been taken by many students.
4. 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.
the total number of tables that can be used in this is 3.

many to many cardinality

Using Sets, it can be represented as:

 Many-to-Many Set Representation

In this example, student S1 is enrolled in C1 and C3 and Course C3

is enrolled by S1, S3, and S4. So it is many-to-many relationships.
Participation Constraint
Participation Constraint is applied to the entity participating in the
relationship set.
1. Total Participation – Each entity in the entity set must
participate in the relationship. If each student must enroll in a
course, the participation of students will be total. Total participation
is shown by a double line in the ER diagram.
2. 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 students, the participation in the 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.

Total Participation and Partial Participation

Using Set, it can be represented as,

Set representation of Total Participation and Partial Participation

Every student in the Student Entity set participates in a relationship

but there exists a course C4 that is not taking part in the
relationship.
How to Draw ER Diagram?
 The very first step is Identifying all the Entities, and place them in
a Rectangle, and labeling them accordingly.
 The next step is to identify the relationship between them and
place them accordingly using the Diamond, and make sure that,
Relationships are not connected to each other.
 Attach attributes to the entities properly.
 Remove redundant entities and relationships.
 Add proper colors to highlight the data present in the database.

Entity in DBMS
Database management systems (DBMS) are large, integrated
collections of data. They play an important role in modern data
management, helping agencies keep, retrieve, and manage data
effectively. At the core of any DBMS is the concept of entities, which
is a basic concept that refers to real-world devices or ideas inside a
database. This article will explore the sector of entities within a
DBMS, providing an in-depth understanding of this fundamental
concept and its significance in database format.
Entity
An entity is a “thing” or “object” in the real world. An entity
contains attributes, which describe that entity. So anything about
which we store information is called an entity. Entities are recorded
in the database and must be distinguishable, i.e., easily recognized
from the group.
For example: A student, An employee, or bank a/c, etc. all are
entities.

Entity

Entity Set
An entity set is a collection of similar types of entities that share the
same attributes.
For example: All students of a school are a entity set
of Student entities.
Key Terminologies used in Entity Set:
 Attributes: Attributes are the houses or traits of an entity. They
describe the data that may be connected with an entity.
 Entity Type: A category or class of entities that share the same
attributes is referred to as an entity kind.
 Entity Instance: An entity example is a particular incidence or
character entity within an entity type. Each entity instance has a
unique identity, often known as the number one key.
 Primary Key: A primary key is a unique identifier for every entity
instance inside an entity kind.
It can be classified into two types:
Strong Entity Set
Strong entity sets exist independently and each instance of a strong
entity set has a unique primary key.
Example of Strong Entity includes:
 Car Registration Number
 Model
 Name etc.

Strong Entity

Weak Entity Set

A weak entity cannot exist on its own; it is dependent on a strong
entity to identify it. A weak entity does not have a single primary
key that uniquely identifies it; instead, it has a partial key.
Example of Weak Entity Set includes:
 Laptop Color
 RAM, etc.

Weak Entity
Kinds of Entities
There are two types of Entities:
Tangible Entity
 A tangible entity is a physical object or a physical thing that can
be physically touched, seen or measured.
 It has a physical existence or can be seen directly.
 Examples of tangible entities are physical goods or physical
products (for example, “inventory items” in an inventory
database) or people (for example, customers or employees).
Intangible Entity
 Intangible entities are abstract or conceptual objects that are not
physically present but have meaning in the database.
 They are typically defined by attributes or properties that are not
directly visible.
 Examples of intangible entities include concepts or categories
(such as “Product Categories” or “Service Types”) and events or
occurrences (such as appointments or transactions).
Entity Types in DBMS
 Strong Entity Types: These are entities that exist
independently and have a completely unique identifier.
 Weak Entity Types: These entities depend on another entity for
his or her lifestyles and do now not have a completely unique
identifier on their own.
The Example of Strong and Weak Entity Types in DMBS is:

Example

 Associative Entity Types: These constitute relationships

between or greater entities and might have attributes in their
own.
 Derived Entity Types: These entities are derived from different
entities through a system or calculation.
 Multi-Valued Entity Types: These entities will have more than
one value for an characteristic.