Chapter 5: Entity Relationship Diagrams
Entity relationship diagrams
ERD is a standard modelling technique for illustrating entities within a system
- Uses data modelling techniques to help define business processes and serve as the foundation for a relational database
•ERDs in BIS's are used to design or analyse relational databases to:
-give a conceptual view of the database
-are independent of the choice of DBMS
-identify some problems in design
ERDs are made up of 3 components:
•entities
•attributes
•relationships
•cardinality
Purpose
- To capture the richest possible understanding of the meaning of data necessary for an organisation's IS
- To help explain the logical structure of databases
Primary key:an attribute that uniquely identifies an entity. No other record can have the same primary key.
Primary keys:
- Have to be unique
- Never changing, e.g key can't be an address because there is a risk that can change
- Never null
e.g ID or passport number
Components of ERD
Attributes
•The properties, facts, details or characteristics of an entity
Attributes have:
•A name (must be a unique noun with capital letter e.g Student_ID)
•An associated entity
•domains of possible values
•have to be drawn as an oval or a circle or written inside the entity rectangle underneath the entity name
Entities
•generalised class of people, place, or things(objects) for which data is collected, stored and maintained
•entities have characteristics called attributes (e.g characteristic of a car entity would be its model number, colour etc)
•Entities are drawn as a rectangle in ER diagram
•Entities are usually nouns
Relationships
•an association between two or more entities
Relationships have:
•A name
•A set of entities that are associated with each other
•a cardinality ratio
•shown as diamonds on ER diagram
•are verb phrased and not vague e.g "buys"
Cardinality ratio
•shows the instances of a relationship: one to one, one to many, many to many
(1:1)
•each lecturer has a unique office
(1:Many)
•a lecturer may tutor many students but each student has one lecturer
(Many:Many)
•Each student has many modules and each module is taken by many students
e.g One lecturer tutors many students
Steps to draw ER diagram
1.Purpose and scope
•Define the purpose and scope of what you're analysing or modelling
2.Entities
•Identify the entities that are involved. Start drawing and labelling them as nouns
- Relationships
•Determine how the entities are related or connected - draw lines between them to signify the relationship
- Attributes
•Layer in more detail by adding key attributes of entities - drawn as ovals
- Cardinality
•Show whether the relationship is (1:1), (1:Many) or (Many:Many)
Redundant relationships
Some relations may be redundant if:
•it is a (1:1) relationship between entity A and entity B
•Every A is related to every B
e.g a Person - Address relationship
-is one to one
-every person has an address
-we do not need addresses that aren’t related to a person
∴we merge the two entities together, with the new entity having the same attributes as the old one
Associative entity
- A data entity that represents a many-to-many relationsihp between two other data entities
- see pg 219 for example
See slide 7 of ERD pptx
Cardinality and Ordinality
Cardinality: the maximum number of times an instance in one entity can relate to instances of another entity
e.g One to one, one to many, many to many
Ordinality: the minimum number of relationships. It classifies the relationship as either mandatory or optional
Classification of relationships
Optional Relationship
e.g An employee may or may not be assigned to a department.
- shown by a vertical line next to the ordinality
Mandatory Relationship
e.g A customer has to make at least one order
shown by two vertical lines next to entity
see slide 4 of 'Powerpoint ERDs-1'
Weak entity
- An entity that can't be uniquely identitfied by its own attributes alone
- shown a rectangle with two borders
Multi-Valued Attribute
- an attribute that can have multiple values e.g an employee (entity) can have many dfferent skills (multi attrib.)
- shown by oval with two borders
Derived Attribute
- An attribute that is based on another attribute e.g an employee monthly salary is based on their annual salary
- shown by oval with dotted outline
Actions
- describe what entity does to another entity.
- shown by a diamond with a verb in between the lines connecting to entities
e.g A supervisor manages an employee
See document "Steps to create an ERD" pdf