The Entity Relationship Data Model

Chapter 5
The Entity Relationship Data Model

Objectives
 Understand the Entity Relational Data Model
The Entity Relationship Data Model

 It is well suited to data modelling for use with databases because it is

fairly abstract and is easy to discuss and explain.
 ER models are readily translated to relations
 ER modelling is based on two concepts:
 Entities, defined as tables that hold specific information (data)
 Relationships, defined as the associations or interactions between
entities
The Entity Relationship Data Model

 In this session we will use a sample database called the COMPANY

database to illustrate the concepts of the ER model.
 This database contains information about employees, departments and
projects.
 Important points to note include:
 There are several departments in the company.
 Each department has a unique identification, a name, location of the
office and a particular employee who manages the department.
 A department controls a number of projects, each of which has a unique
name, a unique number and a budget
The Entity Relationship Data Model

 Each employee has a name, identification number, address, salary and

birthdate.
 An employee is assigned to one department but can join in several
projects. We need to record the start date of the employee in each
project.
 We also need to know the direct supervisor of each employee.
 We want to keep track of the dependents for each employee.
 Each dependent has a name, birthdate and relationship with the
employee
Key terms to note

 A primary key is a field in a table which uniquely identifies each row/record in a

database table.
 Primary keys must contain unique values.
 A primary key column cannot have NULL values.
 A foreign key (FK) is a column or combination of columns that is used to establish
and enforce a link between the data in two tables to control the data that can be
stored in the foreign key table
 A composite foreign key is a foreign key that consists of two or more columns.
 It is important to note that all the columns in a single foreign key must point to the
same table.
 In other words, it is not possible to have a foreign key that references to a column
in Table 1 and a column in Table 2
 Both foreign and primary keys must be of the same data type.
 Entity, Entity Set and Entity Type
 An entity is an object in the real world with an independent existence
that can be differentiated from other objects.
 An entity might be
 An object with physical existence (e.g., a lecturer, a student, a car)
 An object with conceptual existence (e.g., a course, a job, a position)
 Entities can be classified based on their strength.
 An entity is considered weak if its tables are existence dependent.
 Entity, Entity Set and Entity Type
 That is, it cannot exist without a relationship with another entity
 Its primary key is derived from the primary key of the parent entity
 The Spouse table, in the COMPANY database, is a weak entity because its primary
key is dependent on the Employee table.
 Without a corresponding employee record, the spouse record would not exist
 An entity is considered strong if it can exist apart from all of its related entities.
 A table without a foreign key or a table that contains a foreign key that can
contain nulls is a strong entity
 Entity, Entity Set and Entity Type
 Another term to know is entity type which defines a collection of similar
entities.
 An entity set is a collection of entities of an entity type at a particular point
of time.
 In an entity relationship diagram (ERD), an entity type is represented by a
name in a box.
 Example, we can have a collection of employees and the entity type is
EMPLOYEE
 Existence dependency
 An entity’s existence is dependent on the existence of the related entity.
 It is existence-dependent if it has a mandatory foreign key (i.e., a foreign
key attribute that cannot be null).
 For example, in the COMPANY database, a Spouse entity is existence -
dependent on the Employee entity.
 Kinds of Entities
 Independent entities, also referred to as kernels, are the backbone of the
database.
 They are what other tables are based on.
 Kernels have the following characteristics:
 They are the building blocks of a database.
 The primary key may be simple or composite.
 The primary key is not a foreign key.
 They do not depend on another entity for their existence.
 If we refer back to our COMPANY database, examples of an independent
entity include the Customer table, Employee table or Product table
 Kinds of Entities
 Dependent entities, also referred to as derived entities, depend on other
tables for their meaning.
 These entities have the following characteristics:
 Dependent entities are used to connect two kernels together.
 They are said to be existence dependent on two or more tables.
 Many to many relationships become associative tables with at least two foreign
keys.
 They may contain other attributes.
 The foreign key identifies each associated table.
 There are three options for the primary key:
 Use a composite of foreign keys of associated tables if unique
 Use a composite of foreign keys and a qualifying column
 Create a new simple primary key
 Kinds of Entities
 Characteristic entities provide more information about another table.
 These entities have the following characteristics:
 They describe other entities.
 They typically have a one to many relationship.
 The foreign key is used to further identify the characterized table.
 Options for primary key are as follows:
 Use a composite of foreign key plus a qualifying column
 Create a new simple primary key. In the COMPANY database, these might include:
 Employee (EID, Name, Address, Age, Salary) – EID is the simple primary key.
 EmployeePhone (EID, Phone) – EID is part of a composite primary key. Here, EID is also a
foreign key.
 Entities
 Each entity is described by a set of attributes (e.g., Employee = (Name,
Address, Birthdate (Age), Salary).
 Each attribute has a name, and is associated with an entity and a domain of
legal values.
 However, the information about attribute domain is not presented on the
ERD.
 In the entity relationship diagram, each attribute is represented by an oval
with a name inside
 Types of Entities
 Simple attributes
 Simple attributes are those drawn from the atomic value domains; they are
also called single-valued attributes.
 In the COMPANY database, an example of this would be: Name = {John} ;
Age = {23}
 Types of Entities
 Composite attributes are those that consist of a hierarchy of attributes.
 Using our database example, Address may consist of Number, Street and
Suburb.
 So this would be written as → Address = {59 + ‘Meek Street’ + ‘Kingsford’}
 Types of Entities
 Multivalued attributes are attributes that have a set of values for each
entity.
 An example of a multivalued attribute from the COMPANY database, are the
degrees of an employee: BSc, Msc, PhD
 Types of Entities
 Derived attributes are attributes that contain values calculated from
other attributes.
 Age can be derived from the attribute Birthdate.
 In this situation, Birthdate is called a stored attribute, which is physically
saved to the database
 Types of Keys
 Types of Keys
 The primary key is a candidate key that is selected by the database
designer to be used as an identifying mechanism for the whole entity set.
 It must uniquely identify tuples in a table and not be null.
 The primary key is indicated in the ER model by underlining the attribute.
 A candidate key is selected by the designer to uniquely identify tuples
(rows) in a table.
 It must not be null.
 In the following example, EID is the primary key:
 Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate,
Salary, DepartmentID)
 Types of Keys
 A foreign key (FK) is an attribute in a table that references the primary
key in another table OR it can be null.
 Both foreign and primary keys must be of the same data type.
 In the COMPANY database example below, DepartmentID is the foreign key:
 Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate,
Salary, DepartmentID
 Types of Keys
 A null is a special symbol, independent of data type, which means either
unknown or inapplicable. It does not mean zero or blank. Features of null
include:
 No data entry
 Not permitted in the primary key
 Should be avoided in other attributes
 Can represent
 An unknown attribute value
 A known, but missing, attribute value
 A “not applicable” condition
 Can create problems when functions such as COUNT, AVERAGE and SUM are
used
 Can create logical problems when relational tables are linked
 Types of Keys
 Example of how null can be used
 Use the Salary table to follow an example of how null can be used.
 Relationships
 Relationships are the glue that holds the tables together.
 They are used to connect related information between tables.
 Relationships
 Relationships are the glue that holds the tables together.
 They are used to connect related information between tables.
 One to many (1:M) relationship
 A one to many (1:M) relationship should be the norm in any relational
database design and is found in all relational database environments.
 For example, one department has many employees.
 Example: the relationship of one of these employees to the department
 Relationships
 One to many (1:M) relationship
 One class may have many students
 A one to one (1:1)
 A one to one (1:1) relationship is the relationship of one entity to only one
other entity, and vice versa.
 It should be rare in any relational database design.
 In fact, it could indicate that two entities actually belong in the same table.
 many-to-many relationship
 A many-to-many relationship occurs when multiple records in a table are
associated with multiple records in another table.
 For example, a many-to-many relationship exists between customers and
products: customers can purchase various products, and products can be
purchased by many customers
 Relational database systems usually don't allow you to implement a direct
many-to-many relationship between two tables
 To avoid this problem, you can break the many-to-many relationship into
two one to many relationships by using a third table, called a join table.
 Each record in a join table includes a match field that contains the value of
the primary keys of the two tables it joins
many-to-many relationship
 Entity Relationship Diagram

 ER Diagram stands for Entity Relationship Diagram, also known as ERD is

a diagram that displays the relationship of entity sets stored in a database.
 In other words, ER diagrams help to explain the logical structure of
databases. ER diagrams are created based on three basic concepts:
entities, attributes and relationships
 ER Diagrams contain different symbols that use rectangles to represent
entities, ovals to define attributes and diamond shapes to represent
relationships
 Why use ER Diagrams?
 Here, are prime 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
Why use ER Diagrams?
 Why use ER Diagrams?
 Here, are prime 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
 Example

 At CUT a student enrolls for a number for courses. A student should be

assigned to at least one course. Each course is taught by a single
professor. To maintain lecturing quality, a professor teaches one course.
 Draw an ER diagram
 Step 1: Entity identification

 We have three entities

 Student
 Course
 Professor
 Step 2: Relationship identification

 We have the following two relationships

 The student is assigned a course
 Professor delivers a course
 Step 3) Cardinality Identification

 For them problem statement we know that,

 A student can be assigned multiple courses
 A Professor can deliver only one course
 Step 4) Identify Attributes
 You need to study the files, forms, reports, data currently maintained by the
organization to identify attributes. You can also conduct interviews with various
stakeholders to identify entities. Initially, it's important to identify the attributes
without mapping them to a particular entity.
 Once, you have a list of Attributes, you need to map them to the identified
entities. Ensure an attribute is to be paired with exactly one entity. If you think an
attribute should belong to more than one entity, use a modifier to make it unique.
Step 4) Identify Attributes
Step 4) Identify Attributes
 Step 5) Create the ERD Diagram

 A more modern representation of Entity Relationship Diagram Example

 Conceptual data model
 Conceptual ERD models the business objects that should exist in a system
and the relationships between them.
 A conceptual model is developed to present an overall picture of the system by
recognizing the business objects involved.
 It defines what entities exist, NOT which tables. For example, 'many to many'
tables may exist in a logical or physical data model but they are just shown as a
relationship with no cardinality under the conceptual data model.
 Logical data model

 Logical ERD is a detailed version of a Conceptual ERD. A logical ER model

is developed to enrich a conceptual model by defining explicitly the columns
in each entity and introducing operational and transactional entities.
 Although a logical data model is still independent of the actual database
system in which the database will be created, you can still take that into
consideration if it affects the design
 Physical data model
 Physical ERD represents the actual design blueprint of a relational database.
 A physical data model elaborates on the logical data model by assigning each column
with type, length, nullable, etc.
 Since a physical ERD represents how data should be structured and related in a specific
DBMS it is important to consider the convention and restriction of the actual database
system in which the database will be created.
 Make sure the column types are supported by the DBMS and reserved words are not used
in naming entities and columns
Physical data model
 Group Assignment 2

This is a continuation from assignment one. Create a logical data model for
your database system

Due date: 17 June 2021