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, one need to convert the
conceptual model in the relational model which can be implemented using any RDBMS
languages like Oracle SQL, MySQL

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 below.
STUDENT
ROLL_NO NAME ADDRESS PHONE AGE

1 RAMU DELHI 9847336518 18

2 RAM HARYANA 9345562647 18

3 SUJITH GOA 9487725313 20

4 SURESH DARJILING 18

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:

3 SUJITH GOA 9487725313 20

 Relation Instance: The set of tuples of a relation at a particular instance of time is called
as relation instance. Table 1 shows the relation instance of STUDENT at a particular time.
It can change whenever there is insertion, deletion or updation in the database.
 Degree: The number of attributes in the relation is known as 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: Column represents the set of values for a particular attribute. The
column ROLL_NO is extracted from relation STUDENT.

ROLL_NO

NULL Values: The value which is not known or unavailable is called NULL value. It is
represented by blank space. e.g.; PHONE of STUDENT having ROLL_NO 4 is NULL

Constraints in Relational Model

While designing Relational Model, some conditions which must hold for data present in
database are called Constraints. These constraints are checked before performing any
operation (insertion, deletion and updation) in database. If there is a violation in any of
constraints, operation will fail.
Domain Constraints: These are attribute level constraints. An attribute can only take values
which lie inside the domain range. e.g; If a constraints AGE>0 is applied on STUDENT relation,
inserting negative value of AGE will result in failure.
Key Integrity: Every relation in the database should have atleast one set of attributes which
defines a tuple uniquely. Those set of attributes is called key. e.g.; ROLL_NO in STUDENT is a
key. No two students can have 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 other
attribute of same relation or any other relation, it is called referential integrity. Let us
suppose we have 2 relations
STUDENT

ROLL_NO NAME ADDRESS PHONE AGE BRANCH_CODE

1 RAM DELHI 9455123451 18 CS

2 RAMESH GURGAON 9652431543 18 CS

3 SUJIT ROHTAK 9156253131 20 ECE

4 SURESH DELHI 18 IT

BRANCH

BRANCH_CODE BRANCH_NAME

CS COMPUTER SCIENCE

IT INFORMATION TECHNOLOGY

ECE ELECTRONICS AND 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 to
other 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
An anomaly is an irregularity, or something which deviates from the expected or normal
state. When designing databases, there are three types of anomalies:
Insert, Update and Delete.
Insertion Anomaly in Referencing Relation:
One can’t insert a row in REFERENCING RELATION if referencing attribute’s value is not
present in referenced attribute value. e.g.; Insertion of a student with BRANCH_CODE ‘ME’ in
STUDENT relation will result in error because ‘ME’ is not present in BRANCH_CODE of
BRANCH.
Deletion/ Updation Anomaly in Referenced Relation:
One can’t delete or update a row from REFERENCED RELATION if value of REFERENCED
ATTRIBUTE is used in value of REFERENCING ATTRIBUTE. e.g; if we try to delete tuple from
BRANCH having BRANCH_CODE ‘CS’, it will result in 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 following method:
ON DELETE CASCADE: It will delete the tuples from REFERENCING RELATION if value used by
REFERENCING ATTRIBUTE is deleted from REFERENCED RELATION. e.g;, 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 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’.
SUPERKEYS:
Any set of attributes that allows us to identify unique rows (tuples) in a given relation are
known as super keys. Out of these super keys we can always choose a proper subset among
these which can be used as a primary key. Such keys are known as Candidate keys. If there is
a combination of two or more attributes which is being used as the primary key then we call it
as a Composite key.
Primary key is an attribute or a set of attributes of a relation which posses the properties of
uniqueness and irreducibility (No subset should be unique). For example: Supplier number in S
table is primary key, Part number in P table is primary key and the combination of Supplier
number and Part Number in SP table is a primary key

Foreign key is the attributes of a table, which refers to the primary key of some another table.
Foreign key permit only those values, which appears in the primary key of the table to which it
refers or may be null (Unknown value). For example: SNO in SP table refers the SNO of S table,
which is the primary key of S table, so we can say that SNO in SP table is the foreign key. PNO in
SP table refers the PNO of P table, which is the primary key of P table, so we can say that PNO
in SP table is the foreign key.

Domains

A domain is a unique set of values permitted for an attribute in a table. For example, a domain
of month-of-year can accept January, February….December as possible values, a domain of
integers can accept whole numbers that are negative, positive and zero.
Definition: Domain constraints are user defined data type and we can define them like this:
Domain Constraint = data type + Constraints (NOT NULL / UNIQUE / PRIMARY KEY / FOREIGN
KEY / CHECK / DEFAULT)

Attribute

In general, an attribute is a characteristic. In a database management system (DBMS), an

attribute refers to a database component, such as a table. It also may refer to a database field.
Attributes describe the instances in the column of a database. In relational databases,
attributes are the describing characteristics or properties that define all items pertaining to a
certain category applied to all cells of a column. The rows, instead, are called tuples, and
represent data sets applied to a single entity to uniquely identify each item. Attributes are the
characteristics of every individual tuple that help describe its unique properties.

Think of a table in a relational database as being analogous to an electronic spreadsheet. An

attribute is simply one non-null cell in the spreadsheet, or the conjunction of a column and row.

It stores only one piece of data about the object represented by the table in which the attribute
belongs. For example, the tuple can be an Invoice entity. The attributes of an invoice might be
Price, Number, Date or Paid/unpaid.

Beyond the self-explanatory simple or single-valued attributes, there are several types of
attributes available.

 Composite attribute: is an attribute composed of several other simple attributes. For

example, the Address attribute of an Employee entity could consist of the Street, City,
Postal code and Country attributes.

 Multivalued attribute: is an attribute where more than one description can be

provided. For example, an Employee entity may have more than one Email ID attributes
in the same cell.

 Key attribute or primary attribute: is an ID, key, letter or number that uniquely
identifies that item. For example, it can be the number of a certain invoice (e.g. the
individual ID of that invoice). A table that contains a single key attribute is considered a
strong entity. However, a table might contain more than one key attribute if it’s derived
from other tables.

 Derived attribute: as the name implies, these are derived from other attributes, either
directly or through specific formula results. For example, the Age attribute of an
Employee could be derived from the Date of Birth attribute. In other instances, a
formula might calculate the VAT of a certain payment, so that whenever the cell with
the attribute Payment is filled, the cell with the derived attribute VAT automatically
calculates its value.
Tuple

In the context of relational databases, a tuple is one record (one row). The information in a
database can be thought of as a spreadsheet, with columns (known as fields or attributes)
representing different categories of information, and tuples (rows) representing all the
information from each field associated with a single record

Relation or Relationship
Any association between two entity types is called a relationship. Entities take part in the
relationship. It is represented by a diamond shape.

For example, A teacher teaches students. Here, "teaches" is a relationship and this is the
relationship between a Teacher entity and a Student entity.

We have two entity types of 'Customer'(Customer_id, Name, City, Phone) and

'Account'(Account_no, Type, Balance). We store the data of 'Customer' in one table and his
accounts details in the 'Account' table. Now, to link these two tables we need to insert the
primary key 'Customer_id' of the 'Customer' table in the 'Account' table. This key acts as a
foreign key for the 'Account' table and refers to a column with the same name in the
'Customer' table. This is how a relationship between two tables is established. There are
three types of relationships that can exist between two entities.

 One-to-One Relationship
 One-to-Many or Many-to-One Relationship
 Many-to-Many Relationship

One-to-One Relationship
Such a relationship exists when each record of one table is related to only one record of the
other table.

For example, If there are two entities ‘Person’ (Id, Name, Age, Address)and
‘Passport’(Passport_id, Passport_no). So, each person can have only one passport and each
passport belongs to only one person
One-to-Many or Many-to-One Relationship
Such a relationship exists when each record of one table can be related to one or more than
one record of the other table. This relationship is the most common relationship found. A
one-to-many relationship can also be said as a many-to-one relationship depending upon the
way we view it.

For example, If there are two entity type ‘Customer’ and ‘Account’ then each ‘Customer’ can
have more than one ‘Account’ but each ‘Account’ is held by only one ‘Customer’. In this
example, we can say that each Customer is associated with many Account. So, it is a one-to-
many relationship. But, if we see it the other way i.e many Account is associated with one
Customer then we can say that it is a many-to-one relationship.

Many-to-Many Relationship
Such a relationship exists when each record of the first table can be related to one or more
than one record of the second table and a single record of the second table can be related to
one or more than one record of the first table. A many-to-many relationship can be seen as a
two one-to-many relationship which is linked by a 'linking table' or 'associate table'. The
linking table links two tables by having fields which are the primary key of the other two
tables. We can understand this with the following example.

Example: If there are two entity type ‘Customer’ and ‘Product’ then each customer can buy
more than one product and a product can be bought by many different customers.
Domain constraints
Domain constraints can be defined as the definition of a valid set of values for an attribute.
The data type of domain includes string, character, integer, time, date, currency, etc. The value
of the attribute must be available in the corresponding domain.

In DBMS, constraints are the set of rules that ensures that when an authorized user modifies
the database they do not disturb the data consistency and the constraints are specified within
the DDL commands like “alter” and “create” command. There are several types of constraints
available in DBMS and they are:
 Domain constraints
 Entity Integrity constraints
 Referential Integrity constraints
 Key constraints

Constraints
Every relation has some conditions that must hold for it to be a valid relation. These conditions
are called Relational Integrity Constraints. There are three main integrity constraints −

 Key constraints
 Domain constraints
 Referential integrity constraints

Key Constraints
There must be at least one minimal subset of attributes in the relation, which can identify a
tuple uniquely. This minimal subset of attributes is called key for that relation. If there are
more than one such minimal subsets, these are called candidate keys.
Key constraints force that −
 in a relation with a key attribute, no two tuples can have identical values for key
attributes.
  a key attribute cannot have NULL values.
Key constraints are also referred to as Entity Constraints.

Domain Constraints
Attributes have specific values in real-world scenario. For example, age can only be a positive
integer. The same constraints have been tried to employ on the attributes of a relation. Every
attribute is bound to have a specific range of values. For example, age cannot be less than zero
and telephone numbers cannot contain a digit outside 0-9.

Referential integrity Constraints

Referential integrity constraints work on the concept of Foreign Keys. A foreign key is a key
attribute of a relation that can be referred in other relation.
Referential integrity constraint states that if a relation refers to a key attribute of a different or
same relation, then that key element must exist.
A set of attributes FK in relation schema R 1 is a foreign key of R 1 that references relation R 2
if it satisfies. R 1 is called the referencing relation and R 2 is the referenced relation

Triggers
A trigger is a statement or a block of statement which are executed automatically by the
system when an event like insert, update or delete takes place on a table. Triggers are more
powerful because the can check conditions and also modify the data.
Assertions
An assertion is a piece of SQL which makes sure a condition is satisfied or it stops action being
taken on a database object. It could mean locking out the whole table or even the whole
database. Assertions do not modify the data, they only check certain conditions
Update Operations, Transactions, and Dealing with Constraint Violations
The operations of the relational model can be categorized into retrievals and updates. The
relational algebra operations can be used to specify retrievals. A relational algebra expression
forms a new relation after applying a number of algebraic operators to an existing set of
relations; its main use is for querying a database to retrieve information. The user formulates a
query that specifies the data of interest, and a new relation is formed by applying relational
operators to retrieve this data. That result relation becomes the answer to (or result of) the
user’s query. There are three basic operations that can change the states of relations in the
data-base: Insert, Delete, and Update (or Modify). They insert new data, delete old data, or
modify existing data records. Insert is used to insert one or more new tuples in a
relation, Delete is used to delete tuples, and Update (or Modify) is used to change the values of
some attributes in existing tuples
Operations in Relational Model with Constraint Violations
Four basic operations performed on relational database model are Insert, update, delete and
select.
● Insert Operation:
Insert is used to insert data into the relation Insert can violate any of the four types of
constraints. Domain constraints can be violated if an attribute value is given that does not
appear in the corresponding domain or is not of the appropriate data type. Key constraints can
be violated if a key value in the new tuple t already exists in another tuple in the relation r(R).
Entity integrity can be violated if any part of the primary key of the new tuple t is NULL .
Referential integrity can be violated if the value of any foreign key in t refers to a tuple that
does not exist in the referenced relation. If an insertion violates one or more constraints, the
default option is to reject the insertion. If the insertion is not rejected then, the insertion
violation can cause cascade in the relation. A foreign key with cascade delete means that if a
record in the parent table is deleted, then the corresponding records in the child table will
automatically be deleted. This is called a cascade delete.
● Delete Operation:
is used to delete tuples from the table. The Delete operation can violate only referential
integrity. This occurs if the tuple being deleted is referenced by foreign keys from other tuples
in the database. Here are some examples. -Operation: Delete the Department tuple with Dept
NO = 1. Result: This deletion is acceptable and deletes exactly one tuple. -Operation:Delete the
Student tuple with Dept No = 1. Result: This deletion is not acceptable, because there are
tuples in Department that refer to this tuple. Several options are available if a deletion
operation causes a violation. The first option, called restrict, is to reject the deletion. The
second option, called cascade. A third option, called set null or set default, is to modify the
referencing attribute values that cause the violation.And also combinations of these three
options are also possible.
● Update Operation:
Modify allows you to change the values of some attributes in existing tuples. Consider two
table EMPLOYEE(Ssn, name, salary, Dno) and DEPARTMENT(Dno, Dname) -Operation:Update
the salary of the EMPLOYEE tuple with Ssn = ‘123’ to 2800. Result: Acceptable. -
Operation:Update the Dno of the EMPLOYEE tuple with Ssn = ‘123’ to 7. Result: Unacceptable,
because it violates referential integrity. -Operation:Update the Ssn of the EMPLOYEE tuple
with Ssn = ‘123‘ to ‘321’. Result: Unacceptable, because it violates primary key constraint
Updating an attribute that is neither part of a primary key nor of a foreign key usually causes
no problems
● The Transaction Concept:
A transaction is an executing program that includes some database operations, such as reading
from the database, or applying insertions, deletions, or updates to the database. At the end of
the transaction, it must leave the database in a valid or consistent state that satisfies all the
constraints specified on the database schema. A single transaction may involve any number of
retrieval operations C and any number of update operations. For example, a transaction to
apply a bank withdrawal will typically read the user account record, check if there is a
sufficient balance, and then update the record by the withdrawal amount.
Relational Algebra

Relational algebra is a widely used procedural query language. It collects instances of

relations as input and gives occurrences of relations as output. It uses various operation to
perform this action.
Basic Relational Algebra Operations: Relational Algebra divided in various groups

Unary Relational Operations

● SELECT (symbol: σ)
● PROJECT (symbol: π)
● RENAME (symbol:ρ )

Relational Algebra Operations From Set Theory

● UNION (υ)
● INTERSECTION ( ),
● DIFFERENCE (-)

Binary Relational Operations

● JOIN
● DIVISION
● CARTESIAN PRODUCT ( x )

SELECT (σ):The SELECT operation is used for selecting a subset of the tuples according to a
given selection condition. Sigma(σ)Symbol denotes it.
σ condition (Relation)
STUDENT

ID NAME MARK
1 Jisy 70
2 vishnu 75
3 Dwayne 80

1. To retrieve entire details from STUDENT. σ (STUDENT)

2. To retrieve details from STUDENT where ID=2
σ ID=2 (STUDENT)

Projection (π): The project operation is used for selecting attributes according to a given
selection condition.

Π Attribute (Relation)

1. To retrieve ID and NAME from STUDENT.

Π ID, NAME (STUDENT)

Combination of Select and Project: Retrieve ID and Name from STUDENT where mark>=75

Π ID, NAME ( σ Mark>=75 (STUDENT))

Rename Operation (ρ):

. The rename operation allows us to rename the oldt relation to new. ‘Rename’
operation is denoted with small Greek letter rho ρ.
ρ New (Old Relation Name)

Union operation (υ): UNION is symbolized by ∪ symbol. It includes all tuples that are in
tables A or in B. It also eliminates duplicate tuples. A ∪B
For a union operation to be valid, the following conditions must hold -

● R and S must be the same number of attributes.

● Attribute domains need to be compatible.
● Duplicate tuples should be automatically removed.
Retrieve students name either participant in arts or sports: Π NAME (ARTS) ∪Π NAME (SPORTS)
ARTS SPORTS

ID NAME ID NAME

1 A 3 C

2 B 2 B

3 C 4 D

ARTS U SPORTS

ID NAME
1 A
2 B
3 C
4 D

Intersection : A ∩ B of two sets A and B is the set that contains all elements of A that also
belong to B (or equivalently, all elements of B that also belong to A), but no other elements.

Retrieve students name those who participant in both arts

and sports: Π NAME (ARTS) ∩ Π NAME (SPORTS)

ARTS ∩ SPORTS

ID NAME

2 B

3 C
Set Difference (−):The result of set difference operation is tuples, which are present
in one relation but are not in the second relation.
Retrieve students name those who participant only in arts and not in
sports: Π NAME (ARTS) - Π NAME (SPORTS)
ARTS - SPORTS
ID NAME
1 A

Retrieve students name those who participant only in sports and not
in arts: Π NAME (SPORTS) - Π NAME (ARTS)

SPORTS - ARTS
ID NAME
4 D

Cartesian Product (Χ):Combines information of two different relations into one.

A B
K Y K Y

1 A 1 C

2 B 3 D

AΧB

K Y K Y

1 A 1 C
1 A 3 D
2 B 1 C
2 B 3 D
 Join operation (⋈):Used to combine two tables having same attributes. Two type,

● Inner join
● Outer Join
Inner join ⋈:If the join criterion is based on equality of column values, the result is
called an equijoin. A natural join is an equijoin with redundant columns removed

STUD
DEPT
ID NAME DNO. DNO DNAME

1 A 1 1 CS

2 B 2 2 EC

3 C 3 4 EE

STUD ⋈DEPT

ID NAM DNO. DNAM

E E
1 A 1 CS
2 B 2 EC

Outer joins: Includes all the tuples from the participating relations in the resulting relation.
There are three kinds of outer joins:
● Left outer join
● Right outer join
● Full outer join.

Left Outer Join(R S):All the tuples from the Left relation, R, are included in the
resulting relation. If there are tuples in R without any matching tuple in the Right
relation S, then the
S-attributes of the resulting relation are made

NULL. STUD DEPT

ID NAME DNO. DNAME

1 A 1 CS

2 B 2 EC

3 C 3 Null

Right Outer Join: ( R S ):All the tuples from the Right relation, S, are included in the
resulting relation. If there are tuples in S without any matching tuple in R, then the R-
attributes of resulting relation are made NULL.

STUD DEPT
ID NAM DNO. DNAM
E E
1 A 1 CS
2 B 2 EC
Nul Null 4 EE
l
Full Outer Join: ( R S):All the tuples from both participating relations are included in
the resulting relation. If there are no matching tuples for both relations, their respective
unmatched attributes are made NULL.

STUD DEPT
ID NAM DNO. DNAM
E E
1 A 1 CS
2 B 2 EC
3 C 3 Null
Nul Null 4 EE
l
Division operation ( ÷ ):The Division operator results in columns values in one table for which
there are other matching column

EXTENDED Relational – Algebraic Operations

The basic extended algebraic operations can be extended in many ways. Extended operations
can be done using

a) Generalized Projection

b) Aggregate Functions

c) Outer Joins

d) Null Values

a) Generalized Projection

The GENERALIZED PROJECTION operation is an extension to the PROJECTION operation by

llowing arithmetic functions can be used in the projection list on certain attributes. The
Generalized Projection has the form :
ΠF1, F2, ….. , Fn ( E )
Where E is a Relation Algebra Expression
F1, F2, F3…….Fn is an arithmetic expression.
For example :
How many more students can be there in a batch?
ΠBatchName, maxlimit – currentcount (Students)
b) Aggregate Functions

Aggregate functions take a collection of values and return a single value a result. ie, Aggregate
function count () take a collection of records and returns the count of the records. The
aggregation function is used to perform the calculations on multiple rows of a single column of
a table. It returns a single value. It is also used to summarize the data.

Outer Joins

In an outer join, unmatched rows in one or both tables can be returned. Includes all the
tuples from the participating relations in the resulting relation. There are three kinds of
outer joins:
● Left outer join
● Right outer join
● Full outer join.
Null Values

A null value in a relational database is used when the value in a column is unknown or missing.
A null is neither an empty string (for character or datetime data types) nor a zero value (for
numeric data types). A field with a NULL value is a field with no value. It is very important to
understand that a NULL value is different than a zero value or a field that contains spaces.

Conversion of ER diagram to relational schema

An entity type within ER diagram is turned into a table. Each attribute turns into a
column (attribute) in the table. The key attribute of the entity is the primary key of the table
which is usually underlined. Derived attributes are ignored and multivalued attributes are
represented in another table. Taking the following simple ER diagram of strong entity,

The initial relational schema is expressed in the following format writing the table
names with the attributes list inside a parenthesis
Student( Roll_No , Name, Class, Subject )
● Create table for each entity.
● Entity's attributes should become column of tables with their respective data types.
● Declare primary key.
 A multi-valued attribute is usually represented with a double-line oval.

If you have a multi-valued attribute, take the attribute and turn it into a new entity or
table of its own.
Person( personid , name) Phones ( personid, phone )

Person
Phones
Personid (pk) name
PhoneID(pk)
Personid (fk)
phone

A composite attribute,

The firstname and lastname become a individual attributes in a relational tables

Student(Roll_No, BirthDate, FirstName,LastName)

Mapping Relationship
A relationship is an association among entities.
-One to many
-many to many
-many to one
Many to Many

● Create table for each entity.

● Entity's attributes should becomes column of tables with their respective data types.
● Declare primary key.
● Create table for a relationship.
● Add the primary keys of all participating Entities as column of table with their
respective data types.
● If relationship has any attribute, add each attribute as column of table.
● Declare a primary key composing all the primary keys of participating entities.
● Declare all foreign key constraints
Student( RollNo,Name) Course(CID, Title)Enrolled( RollNo, CID, marks,JoiningDates)
One to many and Many to One

● Create table for each entity.

● Entity's attributes should becomes column of tables with their respective data types.
● Set primary Key of a table as a Foreign key of
other table House(Num,Address, personid)
Person(personid,Name,Lastname,Email,Phone) Or
House(Num,Address)
Person(personid,Name,Lastname,Email,Phone,Num)
Mapping Weak Entity Sets

A weak entity set is one which does not have any primary key associated with it.
● Create table for weak entity set.
● Add all its attributes to table as field.
● Add the primary key of linked strong entity set.
● Declare all foreign key constraints

Loan(Loan Number, amount,Payment Number )

Payment( Loan Number,Payment Number, Payment Data, Payment amount)

Entity Relationship Model(ER Model)

ER Model stands for Entity Relationship Model is a high-level conceptual data model diagram.
ER model helps to systematically analyze data requirements to produce a well-designed
database. The ER Model represents real-world entities and the relationships between them.
Creating an ER Model in DBMS is considered as a best practice before implementing a database.
ER Modeling helps to analyze data requirements systematically to produce a well-designed
database. So, it is considered a best practice to complete ER modeling before implementing the
database.
The main reasons for using the ER Diagram

 Helps you to define terms related to entity relationship modeling

 Provide a preview of how all your tables should connect, what fields are going to be on
each table
 Helps to describe entities, attributes, relationships
 ER diagrams are translatable into relational tables which allows you to build databases
quickly
 ER diagrams can be used by database designers as a blueprint for implementing data in
specific software applications
  The database designer gains a better understanding of the information to be contained
in the database with the help of ERP diagram
 ERD Diagram allows you to communicate with the logical structure of the database to
users

ER Diagrams Symbols & Notations

Entity Relationship Diagram Symbols & Notations mainly contains three basic symbols which
are rectangle, oval and diamond to represent relationships between elements, entities and
attributes. There are some sub-elements which are based on main elements in ERD Diagram. ER
Diagram is a visual representation of data that describes how data is related to each other using
different ERD Symbols and Notations.

The main components and its symbols in ER Diagrams:

 Rectangles: This Entity Relationship Diagram symbol represents entity types

 Ellipses : Symbol represent attributes
 Diamonds: This symbol represents relationship types
 Lines: It links attributes to entity types and entity types with other relationship types
 Primary key: attributes are underlined
 Double Ellipses: Represent multi-valued attributes

Components of the ER Diagram

This model is based on three basic concepts:

 Entities
 Attributes
 Relationships

ENTITY
A real-world thing either living or non-living that is easily recognizable and non-recognizable. It
is anything in the enterprise that is to be represented in our database. It may be a physical thing
or simply a fact about the enterprise or an event that happens in the real world.
An entity can be place, person, object, event or a concept, which stores data in the database.
The characteristics of entities are must have an attribute, and a unique key. Every entity is
made up of some ‘attributes’ which represent that entity.

Examples of entities:

 Person: Employee, Student, Patient

 Place: Store, Building
 Object: Machine, product, and Car
 Event: Sale, Registration, Renewal
 Concept: Account, Course

Entity set:
Student

An entity set is a group of similar kind of entities. It may contain entities with attribute sharing
similar values. Entities are represented by their properties, which also called attributes. All
attributes have their separate values. For example, a student entity may have a name, age,
class, as attributes.

Example of Entities:

A university may have some departments. All these departments employ various lecturers and
offer several programs.

Some courses make up each program. Students register in a particular program and enroll in
various courses. A lecturer from the specific department takes each course, and each lecturer
teaches a various group of students

Relationship
Relationship is nothing but an association among two or more entities. E.g., Tom works in the
Chemistry department.
Entities take part in relationships. Relationships are often used with verbs or verb phrases.

For example:

 You are attending this lecture

 I am giving the lecture
 A student attends a lecture
 A lecturer is giving a lecture.

Weak Entities
A weak entity is a type of entity which doesn’t have its key attribute. It can be identified
uniquely by considering the primary key of another entity. For that, weak entity sets need to
have participation.

Attributes
It is a single-valued property of either an entity-type or a relationship-type. For example, a
lecture might have attributes: time, date, duration, place, etc. An attribute in ER Diagram
examples, is represented by an Ellipse
Attribute Types

Types of Attributes Description

Simple attributes can’t be divided any further. For
Simple attribute example, a student’s contact number. It is also called
an atomic value.
It is possible to break down composite attribute. For
Composite attribute example, a student’s full name may be further
divided into first name, second name, and last name.
This type of attribute does not include in the physical
database. However, their values are derived from
Derived attribute other attributes present in the database. For
example, age should not be stored directly. Instead, it
should be derived from the DOB of that employee.
Multivalued attributes can have more than one
Multivalued attribute values. For example, a student can have more than
one mobile number, email address, etc.

Cardinality
Defines the numerical attributes of the relationship between two entities or entity sets.

Different types of cardinal relationships are:

 One-to-One Relationships
 One-to-Many Relationships
 May to One Relationships
 Many-to-Many Relationships

1. One-to-one:

One entity from entity set X can be associated with at most one entity of entity set Y and
vice versa.

Example: One student can register for numerous courses. However, all those courses
have a single line back to that one student.
2.One-to-many:

One entity from entity set X can be associated with multiple entities of entity set Y, but an
entity from entity set Y can be associated with at least one entity.

For example, one class is consisting of multiple students.

3. Many to One

More than one entity from entity set X can be associated with at most one entity of entity set Y.
However, an entity from entity set Y may or may not be associated with more than one entity
from entity set X.

For example, many students belong to the same class.

4. Many to Many:

One entity from X can be associated with more than one entity from Y and vice versa.

For example, Students as a group are associated with multiple faculty members, and faculty
members can be associated with
multiple students.

Steps in creating an Entity Relationship Diagram (ERD)

Step 1) Entity Identification
Step 2) Relationship Identification
Step 3) Cardinality Identification
Step 4) Identify Attributes
Step 5) Create the ERD Diagram
The ERD creation is explained with an Example: -
In a university, a Student enrolls in Courses. A student must be assigned to at least one or more
Courses. Each course is taught by a single Professor. To maintain instruction quality, a Professor
can deliver only one course
Step 1) Entity Identification
There are three entities

 Student
 Course
 Professor

Step 2) Relationship Identification

The following are the two relationships

 The student is assigned a course

 Professor delivers a course

Step 3) Cardinality Identification

For them problem statement is ,

 A student can be assigned multiple courses

 A Professor can deliver only one course

Step 4) Identify Attributes

The Study of files, forms, reports, data currently maintained by the organization to identify
attributes is required in this case. The conduct interviews with various stakeholders to identify
entities are also needed in some cases. Initially, it’s important to identify the attributes without
mapping them to a particular entity.

Once, there is a list of Attributes, and then it is needed to map them to the identified entities.
Ensure an attribute is to be paired with exactly one entity. If an attribute should belong to more
than one entity, use a modifier to make it unique.
Once the mapping is done, identify the primary Keys. If a unique key is not readily available,
create one.

Entity Primary Key Attribute

Student Student_ID StudentName

Professor Employee_ID ProfessorName

Course Course_ID CourseName

Step5) Create the ER Diagram