Entity Relationship Diagram

Entity Relationship Diagram

• An entity-relationship diagram (ERD), also known as an entity-
relationship model, is a graphical representation of an
information system that depicts the relationships among people,
objects, places, concepts or events within that system. An ERD
is a data modeling technique that can help define business
processes and be used as the foundation for a relational
database
Entity Relationship Diagram
• An Entity Relationship (ER) Diagram is a type of flowchart that
illustrates how “entities” such as people, objects or concepts relate to
each other within a system. ER Diagrams are most often used to
design or debug relational databases in the fields of software
engineering, business information systems, education and research.
• Also known as ERDs or ER Models, they use a defined set of symbols
such as rectangles, diamonds, ovals and connecting lines to depict the
interconnectedness of entities, relationships and their attributes.
They mirror grammatical structure, with entities as nouns and
relationships as verbs.
Importance of ERDs and their uses
• Entity-relationship diagrams provide a visual starting point for
database design that can also be used to help determine information
system requirements throughout an organization. After a relational
database is rolled out, an ERD can still serve as a referral point, should
any debugging or business process re-engineering be needed later.
Importance of ERDs and their uses
• However, while an ERD can be useful for organizing data that can be
represented by a relational structure, it can't sufficiently represent
semi-structured or unstructured data. It's also unlikely to be helpful
on its own in integrating data into a pre-existing information system.
 Main components of an ERD
ERDs are generally depicted in one or more of the following models:
1. A conceptual data model, which lacks specific detail but provides an
overview of the scope of the project and how data sets relate to one another.

2. A logical data model, which is more detailed than a conceptual data model,
illustrating specific attributes and relationships among data points. While a
conceptual data model does not need to be designed before a logical data
model, a physical data model is based on a logical data model.

3. A physical data model, which provides the blueprint for a physical

manifestation -- such as a relational database -- of the logical data model. One
or more physical data models can be developed based on a logical data model.
Three Basic Components Of An Entity-
Relationship Diagram:
• 1. Entities, which are objects or concepts that can have data
stored about them
Three Basic Components Of An Entity-
Relationship Diagram:
• 2. Attributes, which are properties or characteristics of entities. An
ERD attribute can be denoted as a primary key, which identifies a
unique attribute, or a foreign key, which can be assigned to multiple
attributes.
Three Basic Components Of An Entity-
Relationship Diagram:
3. The relationships between and among those entities
• For example, an ERD representing the information system for a company's
sales department might start with graphical representations of entities
such as the sales representative, the customer, the customer's address, the
customer's order, the product and the warehouse. Then lines or other
symbols can be used to represent the relationship between entities, and
text can be used to label the relationships.
• A cardinality notation can then define the attributes of the relationship
between the entities. Cardinalities can denote that an entity is optional (for
example, a sales rep could have no customers or could have many) or
mandatory (for example, there must be at least one product listed in
order.)
 Three Common Cardinalities
• one-to-one
• one-to-many
• many-to-many

A one-to-one relationship is mostly used to split a table in two in order to provide

information concisely and make it more understandable. In the hospital example, such
a relationship could be used to keep apart doctors' own unique professional
information from administrative details.
A many-to-many relationship between records in doctor and records
in patient because doctors have many patients and patients can see many doctors.
A one-to-many relationship between records in patient and records
in encounter because patients can have many encounters and each encounter
involves only one patient.
 Three Common Cardinalities
• one-to-one
• one-to-many
• many-to-many

• Other Cardinalities:
• Zero
• One and only one
Cardinality
• Cardinality allows you express
the number of each entity that
can be associated with another
entity.
• For example, in an employee
database, a manager will have
multiple employee reports (in a
one to many relationship), but
an employee will only have one
ID number (a one to one
relationship).
1. one-to-one relationship: In relational database design, a one-to-one (1:1) relationship exists when zero
or one instance of entity A can be associated with zero or one instance of entity B, and zero or one
instance of entity B can be associated with zero or one instance of entity A. (abbreviated 1:1)
2. one-to-many relationship: (abbreviated 1:N) In relational database design, a one-to-many (1:N)
relationship exists when, for one instance of entity A, there exists zero, one, or many instances of entity
B; but for one instance of entity B, there exists zero or one instance of entity A.
3. many-to-many relationship: In relational database design, a many-to-many (M:N) relationship exists
when, for one instance of entity A, there exists zero, one, or many instances of entity B; and for one
instance of entity B, there exists zero, one, or many instances of entity A. (abbreviated M:N)
• Following are simple examples of each:
• 1:1 relationship - In a traditional American marriage, a man can be married to only one woman; a
woman can be married to only one man.
• 1:N relationship - A child has exactly one biological father; a father can have many biological children.
• M:N relationship - A student can enroll in many classes; a class can have many enrolled students.
Mandatory vs. Optional
• In a mandatory relationship, every instance of one entity must
participate in a relationship with another entity. In an optional
relationship, any instance of one entity might participate in a
relationship with another entity, but this is not compulsory.
One to One Relationships
• Country - capital city: Each country has exactly one capital city. Each
capital city is the capital of exactly one country.
• Person - their fingerprints. Each person has a unique set of
fingerprints. Each set of fingerprints identifies exactly one person.
• Email - user account. For many websites, one email address is
associated with exactly one user account and each user account is
identified by its email address.
• Spouse - spouse: In a monogamous marriage, each person has
exactly one spouse.
• User profile - user settings. One user has one set of user settings.
One set of user settings is associated with exactly one user.
One to One Relationships
One to Many Relationships
• If the two entity types are ‘Customer’ and ‘Account,’ each
‘Customer’ can have many ‘Accounts,’ but each
‘Account’ can only be owned by one ‘Customer.’ In this
case, we can say that each customer is linked to a
number of accounts.
• Country - city: Each country has exactly many cities. But the city
to its capital is One to One relationship
Many to One Relationship
Many to Many
 Types of Entity Relationship Diagrams

• ER diagrams will
differ on how they
express cardinality.
They will also differ
in how they display
entities and their
attributes and
whether or not they
show relationships
or attributes as
separate symbols.
 Common ERD Symbols
• An ER diagram has three main components: entities, relationships, and attributes
connected by lines.
• Entities, which are represented by rectangles. An entity is an object or concept about
which you want to store information. A weak entity is an entity that must be defined by a
foreign key relationship with another entity as it cannot be uniquely identified by its own
attributes alone.
• Relationships, which are represented by diamond shapes, show how two entities share
information in the database. In some cases, entities can be self-linked.
• Attributes, which are represented by ovals. A key attribute is the unique, distinguishing
characteristic of the entity. For example, an employee's social security number might be
the employee's key attribute.
A multivalued attribute can have more than one value. For example, an employee entity
can have multiple skill values. A derived attribute is based on another attribute. For
example, an employee's monthly salary is based on the employee's annual salary.
• Connecting lines, solid lines that connect attributes and show the relationships of entities
in the diagram.
• Cardinality specifies the numerical attribute of the relationship between entities. It can
be one-to-one, many-to-one, or many-to-many.
Conclusion:
• Here are some best practice tips for constructing an ERD:
• Identify the entities. The first step in making an ERD is to identify
all the entities you will use. An entity is nothing more than a
rectangle with a description of something that your system
stores information about. This could be a customer, a manager,
an invoice, a schedule, etc. Draw a rectangle for each entity you
can think of on your page. Keep them spaced out a bit.
• Identify relationships. Look at two entities, are they related? If
so draw a solid line connecting the two entities and add a
diamond between them with a brief description of how they are
related.
• Add attributes. Any key attributes of entities should be added
using oval-shaped symbols.
• Complete the diagram. Continue to connect the entities with
lines, and adding diamonds to describe each relationship until
all relationships have been described. Each of your entities
may not have any relationships, some may have multiple
relationships. That is okay.
 Quiz 2 – ERD and Relational Database
Presentation
• Choose a topic on the following Construction Related Topics and
create and ERD and Relational Database:
[Situation or Solution]
1. Supply and Logistics in a Project
2. Construction Delays
3. Infrastructure Works (Bridge, Tunnels, Railways, Airports)
4. Road and Drainage Works
5. Mass Housing Works
6. High Rise Building Works
7. Manpower and Material Management
8. Land / Site Development / Urban Planning