Data Modeling using ER

Model

Muhammad Qasim
Design Stages

Analyze User Requirement

Develop Conceptual model

Develop Logical model

Choose DBMS

Develop Physical model

Implement System

Test System

Operational Maintenance
Sli
de
Database Design Stages
1) Analyze user Requirements
◦ Database designers interview prospective database users to
understand and document data requirements
Database Design Stages
2) Conceptual Data Model: A Conceptual Data Model is an organized view
of database concepts and their relationships. The purpose of creating a
conceptual data model is to establish entities, their attributes, and
relationships. In this data modeling level, there is hardly any detail available
on the actual database structure. Business stakeholders and data architects
typically create a conceptual data model.

The 3 basic tenants of Conceptual Data Model are

 Entity: A real-world thing

 Attribute: Characteristics or properties of an entity

 Relationship: Dependency or association between two entities

Conceptual Model
Database Design Stages
3) Logical data model: The Logical Data Model is used to define the
structure of data elements and to set relationships between them.
The logical data model adds further information to the conceptual
data model elements. The advantage of using a Logical data model
is to provide a foundation to form the base for the Physical model.
However, the modeling structure remains generic.

At this Data Modeling level, no primary or secondary key is

defined. At this Data modeling level, you need to verify and adjust
the connector details that were set earlier for relationships.
Logical Model
Database Design Stages
4) Physical Data Model: A Physical Data Model describes a
database-specific implementation of the data model. It offers
database abstraction and helps generate the schema. This is
because of the richness of meta-data offered by a Physical Data
Model. The physical data model also helps in visualizing database
structure by replicating database column keys, constraints,
indexes, triggers, and other RDBMS features.
Physical Model
ER Model - Basic Concepts
Entity-Relationship (ER) model: Popular high-level conceptual data
model. A semantic data model used for graphical representation of
conceptual database design

ER diagrams: Diagrammatic notation associated with the ER model

Unified Modeling Language (UML):

Simple Example of ER Diagram
ER Model - Basic Concepts
Entity: An entity can be a real-world object, either animate or
inanimate, that can be easily identifiable. For example, in a school
database, students, teachers, classes, and courses offered can be
considered as entities. All these entities have some attributes or
properties that give them their identity.
Naming Convention of Entity: Singular name recommended. Organization specific name
e.g. is same term choose which organization is used. Use capital letter. Abbreviation can
be used, be consistent

Entity Set: An entity set is a collection of similar types of entities. An

entity set may contain entities with attribute sharing similar values. For
example, a Students set may contain all the students of a school;
likewise a Teachers set may contain all the teachers of a school from
all faculties. Entity sets need not be disjoint.
 Strong Entity: 
A strong entity is not dependent on any other entity in the schema. A strong
entity will always have a primary key. Strong entities are represented by a
single rectangle. The relationship of two strong entities is represented by a
single diamond. 
Various strong entities, when combined together, create a strong entity set. 

 Weak Entity: 
A weak entity is dependent on a strong entity to ensure its existence. Unlike
a strong entity, a weak entity does not have any primary key. It instead has a
partial discriminator key. A weak entity is represented by a double rectangle. 
The relation between one strong and one weak entity is represented by a
double diamond. This relationship is also known as identifying relationship.
ER Model - Basic Concepts
Attributes: Entities are represented by means of their
properties, called attributes. All attributes have values. For
example, a student entity may have name, class, and age as
attributes. Attributes are represented by means of ellipses.
Every ellipse represents one attribute and is directly connected
to its entity (rectangle).

There exists a domain or range of values that can be assigned

to attributes. For example, a student's name cannot be a
numeric value. It has to be alphabetic. A student's age cannot
be negative, etc.
ER Model - Basic Concepts
Types of Attributes: Following are the types of
attributes;
 Simple attribute − Simple attributes are atomic values, which
cannot be divided further. For example, a student's phone
number is an atomic value of 10 digits.
 Composite attribute − Composite attributes are made of more
than one simple attribute. For example, a student's complete
name may have first_name and last_name.
ER Model - Basic Concepts
 Derived attribute − Derived attributes are the attributes that do
not exist in the physical database, but their values are derived
from other attributes present in the database. For example,
average_salary in a department should not be saved directly in
the database, instead it can be derived. For another example, age
can be derived from data_of_birth.
 Single-value attribute − Single-value attributes contain single
value. For example − Social_Security_Number.
 Multi-value attribute − Multi-value attributes may contain more
than one values. For example, a person can have more than one
phone number, email_address, etc
Symbols for Attributes
Example of derived Attributes
Example of composite Attributes
 Example

Experience empId empName

EMPLOYEE address

dateHired Hobbies street houseNo

Initial Conceptual Design of the
COMPANY Database
Types of Keys in DBMS
There are mainly Eight different types of Keys in DBMS and each key
has it’s different functionality:
 Super Key

 Candidate Key

 Primary Key

 Alternate Key

 Foreign Key

 Compound Key

 Composite Key

 Surrogate Key
Super Key: A super key is a group of single or multiple keys which
identifies rows in a table. A Super key may have additional attributes
that are not needed for unique identification.
In the above-given example, EmpSSN and EmpNum name are
superkeys.
CANDIDATE KEY in SQL is a set of attributes that uniquely identify tuples in a table.
Candidate Key is a super key with no repeated attributes. The Primary key should be
selected from the candidate keys. Every table must have at least a single candidate key. A
table can have multiple candidate keys but only a single primary key.
Properties of Candidate key:
 It must contain unique values
 Candidate key in SQL may have multiple attributes
 Must not contain null values
 It should contain minimum fields to ensure uniqueness
 Uniquely identify each record in a table
 Candidate key Example: In the given table Stud ID, Roll No, and email are candidate keys which help us
to uniquely identify the student record in the table.
PRIMARY KEY in DBMS is a column or group of columns in a table that uniquely
identify every row in that table. The Primary Key can’t be a duplicate meaning
the same value can’t appear more than once in the table. A table cannot have
more than one primary key.
Rules for defining Primary key:
 Two rows can’t have the same primary key value

 It must for every row to have a primary key value.

 The primary key field cannot be null.

 The value in a primary key column can never be modified or updated if any

foreign key refers to that primary key.

Example: In the following example, <code>StudID</code> is a Primary Key.
ALTERNATE KEYS is a column or group of columns in a table that uniquely
identify every row in that table. A table can have multiple choices for a primary
key but only one can be set as the primary key. All the keys which are not
primary key are called an Alternate Key.
Example: In this table, StudID, Roll No, Email are qualified to become a primary
key. But since StudID is the primary key, Roll No, Email becomes the alternative
key.
FOREIGN KEY is a column that creates a relationship between two tables. The
purpose of Foreign keys is to maintain data integrity and allow navigation
between two different instances of an entity. It acts as a cross-reference
between two tables as it references the primary key of another table.
Example: In this key in dbms example, we have two table, teach and department
in a school. However, there is no way to see which search work in which
department.
In this table, adding the foreign key in Deptcode to the Teacher name, we can
create a relationship between the two tables.

This concept is also known as Referential Integrity.

COMPOSITE KEY is a combination of two or more columns that
uniquely identify rows in a table. The combination of columns
guarantees uniqueness, though individually uniqueness is not
guaranteed. Hence, they are combined to uniquely identify records in a
table.
SURROGATE KEYS is An artificial key which aims to uniquely identify each record
is called a surrogate key. This kind of partial key in dbms is unique because it is
created when you don’t have any natural primary key. They do not lend any
meaning to the data in the table. Surrogate key in DBMS is usually an integer. A
surrogate key is a value generated right before the record is inserted into a
table.
Above, given example, shown shift timings of the different employee. In this
example, a surrogate key is needed to uniquely identify each employee.
Surrogate keys in sql are allowed when
 No property has the parameter of the primary key.

 In the table when the primary key is too big or complicated.

Difference Between Primary key & Foreign key
Relationship
Relationship: The association among entities is called a
relationship. For example, an employee works_at a department,
a student enrolls in a course. Here, Works_at and Enrolls are
called relationships.

Relationship Set: A set of relationships of similar type is called a

relationship set. Like entities, a relationship too can have
attributes. These attributes are called descriptive attributes.
Degree of Relationship: The number of participating entities in
a relationship defines the degree of the relationship.

 Binary = degree 2
 Ternary = degree 3
 n-ary = degree
Mapping Cardinalities
Cardinality defines the number of entities in one entity set,
which can be associated with the number of entities of other
set via relationship set.
 One-to-one − One entity from entity set A can be associated

with at most one entity of entity set B and vice versa.

One-to-many − One entity from entity set A can be associated
with more than one entities of entity set B however an entity
from entity set B, can be associated with at most one entity.
Many-to-one − More than one entities from entity set A can be
associated with at most one entity of entity set B, however an
entity from entity set B can be associated with more than one
entity from entity set A.
Many-to-many − One entity from A can be associated with
more than one entity from B and vice versa.
Multiplicity
Multiplicity is the active logical association when the cardinality
of a class in relation to another is being depicted.
For example, one fleet(ownership/organization) may include
multiple airplanes, while one commercial airplane may contain
zero to many passengers. The notation 0..* in the diagram
means “zero to many”.
Participation Constraints
 Total Participation − Each entity is involved in the relationship. Total
participation is represented by double lines.
 Partial participation − Not all entities are involved in the relationship.
Partial participation is represented by single lines.
Generalization
As mentioned above, the process of generalizing entities, where the
generalized entities contain the properties of all the generalized
entities, is called generalization. In generalization, a number of entities
are brought together into one generalized entity based on their similar
characteristics. For example, pigeon, house sparrow, crow and dove
can all be generalized as Birds.
Specialization
Specialization is the opposite of generalization. In specialization, a
group of entities is divided into sub-groups based on their
characteristics. Take a group ‘Person’ for example. A person has name,
date of birth, gender, etc. These properties are common in all persons,
human beings. But in a company, persons can be identified as
employee, employer, customer, or vendor, based on what role they
play in the company.
Similarly, in a school database, persons can be specialized as teacher,
student, or a staff, based on what role they play in school as entities.
 Inheritance
We use all the above features of ER-Model in order to create classes of
objects in object-oriented programming. The details of entities are
generally hidden from the user; this process known as abstraction.
Inheritance is an important feature of Generalization and
Specialization. It allows lower-level entities to inherit the attributes of
higher-level entities.

For example, the

attributes of a Person
class such as name,
age, and gender can
be inherited by
lower-level entities
such as Student or
Teacher.