BACHELOR OF COMPUTER

DCA1208: Database Management System

APPLICATION
SEMESTER 2

DCA1208
DATABASE MANAGEMENT SYSTEM
Unit: 5 – ER Diagrams 1
DCA1208: Database Management System

Unit - 5
ER Diagrams

DCA324
KNOWLEDGE MANAGEMENT
Unit: 5 – ER Diagrams 2
DCA1208: Database Management System
TABLE OF CONTENTS

SL Fig No / Table SAQ /

Topic Page No
No / Graph Activity

1 Introduction - -
4-5
2.1 Objectives - -

2 The Entity-Relationship Model 1, 2 -

2.1 Components of ER Diagram - -
6-17
2.2 Symbols Used in the E-R Model - -
2.3 Some Examples of ER Diagrams - -
Converting ER Diagrams into Tables 3, 4, 5, 6, 7, 8,
3 - 18-25
9, 10, 11, 12
4 Summary - - 26-27

5 Glossary - - 28-29

6 Self-Assessment Question - 1 30-32

7 Terminal Questions - - 33

8 Answers - -
8.1 Self-Assessment Question - - 34-35
8.2 Terminal Questions - -

9 References - - 36

Unit: 5 – ER Diagrams 3
DCA1208: Database Management System

1. INTRODUCTION
In the previous unit, we studied the Entity-Relationship (ER) Model, exploring the fundamental
framework used in database design to represent data structures visually. We learnt how the ER model
helps define the data elements (entities) and their interrelationships, serving as a blueprint for
constructing databases. It simplifies the process of conceptualising and structuring data by using
diagrams to illustrate how different entities relate to one another. We also saw how this model is
especially useful during the initial stages of database design, as it provides a clear and organised way
to map out the data requirements before moving into more complex relational database schemas.

The ER model taught us that the primary components are crucial. Entities and Attributes refer to the
objects of interest in the database and their properties. Entities are usually nouns, such as "Student"
or "Course," while attributes provide additional details about these entities, like a student's name or
course credits. Relationships describe how entities interact with each other, indicating connections
like "Enrollment" between a student and a course. Keys are critical in the ER Model, with primary
keys uniquely identifying each entity instance, ensuring data integrity. Foreign keys are used to link
entities together, representing the relationships between them. Therefore, The ER Model provides a
clear, organised method for designing databases, ensuring the structure accurately reflects the real-
world scenario it intends to model.

In this unit, we will explore the foundational elements that make up an Entity-Relationship (ER)
diagram, which is a critical tool in database modelling. ER diagrams visually represent the structure
of a database by outlining entities, their attributes, and the relationships between them. We'll learn
the specifics of entities, objects or concepts that can be distinctly identified and their attributes, which
are properties or characteristics of these entities.

Moving on to The Entity-Relationship Model, this topic will cover the theoretical foundation of ER
diagrams, which includes the symbols used to represent entities, relationships, and attributes. We'll
learn about the standard notation for these components, such as rectangles for entities, diamonds for
relationships, and ovals for attributes. In the

section on Symbols Used in the E-R Model, we'll go deeper into the specific symbols and their
meanings, which are essential for correctly interpreting and creating ER diagrams. Following this, we

Unit: 5 – ER Diagrams 4
DCA1208: Database Management System

will study Some Examples of ER Diagrams to see how these concepts are applied in real-world
scenarios, providing practical insights into database design. Finally, the topic of Converting ER
Diagrams into Tables will teach us how to translate these visual representations into actual
database tables, a critical step in implementing a database design. This process involves mapping
entities to tables, attributes to columns, and relationships to foreign keys, ensuring that the database
structure accurately reflects the original ER diagram.

1.1. Objectives
By the end of the Unit 5, the learners should be able to
understand:
• Define the Entity-Relationship Model.
• Identify the Components of the ER Diagram.
• List the Symbols Used in the E-R Model.
• Explain how to Convert ER Diagrams into Tables

Unit: 5 – ER Diagrams 5
DCA1208: Database Management System

2. THE ENTITY-RELATIONSHIP MODEL

As we have discussed an introduction to the ER Model in the previous unit, let us quickly recap here.

The entity-relationship model is a tool for analysing an application's semantic features independent
of events. This approach includes a graphical notation depicting entity classes as rectangles,
relationships as diamonds, and attributes as circles or ovals. For a complex situation, a partial entity-
relationship diagram may present a summary of the entities and relationships that do not include the
details of the attributes.

The entity-relationship diagram provides a convenient method for visualisingthe interrelationships

among entities in a given application. This tool has proven helpful in transitioning from an
information application description to a formal database schema. The entity-relationship model is
used to describe the conceptual schema of an enterprise without

attention to the efficiency of the physical database design. The entity-relationship diagrams are then
turned into a logical schema in which the database is implemented.

2.1. Components of ER Diagram

1. Entity: An entity is a thing that exists and is distinguishable.
a) Entity instance. An instance is a particular occurrence of an entity. For example, each person
is an instance of an entity Person, each car is an instance of an entity Car, etc.
b) Entity class. A group of similar entities is called an entity class or entity type. An entity class
has common attributes.

2. Attributes: Attributes define a database's schema and are essential for ensuring data integrity,
retrieval, and manipulation within a DBMS. They describe the properties of entities and
relationships.
Types of Attributes in DBMS:
o Simple (Atomic) Attribute: An attribute that cannot be divided into smaller components.
Example: FirstName, Age, EmployeeID.
o Composite Attribute: An attribute that can be subdivided into smaller, meaningful
components, which themselves are attributes.
Example: Address might comprise Street, City, State, and ZipCode.

Unit: 5 – ER Diagrams 6
DCA1208: Database Management System

o Single-Valued Attribute: An attribute with a single value for each entity.

Example: SocialSecurityNumber, DateOfBirth.
o Multi-Valued Attribute: An attribute holding multiple values for each entity.
Example: Phone numbers for an employee might store multiple contact
numbers.
o Derived Attribute: An attribute whose value is calculated or derived from other attributes.
Example: Age could be derived from the DateOfBirth attribute.
o Domain: The conceptual definition of attributes is a named set of scalar values, all of the
same type, and a pool of possible values.
3. Relationships: A relationship is a connection between entities. For example, a relationship
between PERSONS and AUTOMOBILES could be an “OWNS” relationship. That is to say, people
own automobiles.
• Is-a hierarchies are a special type of relationship that allows attribute inheritance. For
example, to say that a truck is an automobile and an automobile has a model and serial
number implies that a truck also has a model and serial number.

4. Keys: A key is an identifier that uniquely differentiates one entity instance from all others in the
entity.
a) Primary Key: Identifier used to uniquely identify one particular instance of an entity.
A primary key
• can be one or more attributes (e.g., consider substituting asingle concatenated key
attribute for multiple attribute keys)
• must be unique within the domain (not just the current data set),
• its value should not change over time,
• must always have a value, and
• is created when no prominent attribute exists. Each instance hasa value.

b) Candidate Key: When multiple possible identifiers exist, each is a candidate key.
c) Concatenated Key: A key comprises parts that, when combined, become a unique
identifier. Multiple attribute keys are concatenated keys.
d) Borrowed Key Attributes: If an is-a relationship exists, the key of the more general entity
is also a key of the sub-entities. For example, if the serial number is a keyfor automobiles, it
would also be a key for trucks.

Unit: 5 – ER Diagrams 7
DCA1208: Database Management System

e) Foreign Keys. Foreign keys reference a related table through the primary key of that
related table.

An ER schema may identify certain constraints to which the data content must conform.

Two of the most important types of constraints are:

1. The mapping cardinality of a relationship indicates the number of instances in entity E1 that can
or must be associated with instances in entity E2:
a) One-One Relationship: For each entity instance in one entity, there is at most one
associated entity instance in the other. An example is a Person table linked to a passport
table, where each person has one unique passport, and each passport is assigned to one
person
b) Many-One Relationships: One entity instance in entity E2 is associated with zero or more
entity instances in entity E1. Still, at most, each entity instance in E1 is associated with one
entity instance inE2. For example, a woman may have many children, but a child has only
one birth mother.
c) Many-Many Relationships There are no restrictions on how many entity instances in
either entity are associated with a single entity instance in the other. An example of a many-
to-many relationship would be students taking classes. Each student takes many classes,
and each class has many students.
Mapping cardinality is derived from cardinality constraints. The cardinality constraint
between two entities E1 and E2, denoted by (m,n), specifies that an instance in E1 appears
in E2 at least m andat most n times. Mapping cardinality takes the maximum number of
cardinality constraints for each entity in a relationship.
2. Existence dependence: If the existence of an entity instance x depends on the existence of an
entity instance y, then x is said to be existence dependent on y. If y is deleted, so is x. For example,
loan_payment exists and is dependent on loan_number. If loan_number is deleted, so is
loan_payment.

Unit: 5 – ER Diagrams 8
DCA1208: Database Management System

2.2. Symbols Used in the ER Model

We will focus on the various graphical symbols fundamental to creating and understanding Entity-
Relationship (ER) diagrams. These symbols form the building blocks of ER models, allowing for a
transparent and standardised representation of the database structure.

Key symbols include:

• Entity: Represented by a rectangle, entities are objects or concepts within the domain of interest.
Each entity type has its rectangle; inside it, the entity's name is written.

• Weak Entity: Weak entities, which depend on another entity for their existence, are depicted
with double rectangles, and their identifying relationships are shown with double diamonds.

• Relationship: Relationships between entities are shown using diamonds. The diamond is
connected to the involved entities and describes the nature of the relationship.

• Identifying Relationship: This type of relationship, crucial for connecting weak entities to their
strong entities, is depicted using a double diamond. An identifying relationship indicates that
the weak entity's existence depends on the strong entity and that the weak entity's key is
partially or fully derived from the strong entity.

• Attributes: Attributes are properties or characteristics of entities and are depicted as ovals
connected to their respective entities. Variations include key attributes, which are underlined,
and multivalued attributes, which are depicted as double ovals.

• Key Attribute: A key attribute uniquely identifies each instance of an entity. It is underlined in
the diagram to distinguish it from other attributes.

Unit: 5 – ER Diagrams 9
DCA1208: Database Management System

• Multivalued Attribute: Represented by a double oval, a multivalued attribute indicates that an

entity can have multiple values for this attribute. For example, an entity "Person" might have a
multivalued attribute "Phone Numbers" if a person can have more than one phone number.

• Composite Attribute: A composite attribute consists of multiple components, which can be

broken down into more detailed attributes. It is depicted as an oval connected to smaller ovals,
each representing a component attribute.

• Derived Attribute: Represented by a dashed oval, a derived attribute is one whose value is
calculated or derived from other attributes.

• Cardinality: Cardinality is not always a separate symbol but is often represented alongside
relationships to indicate the nature of the relationship (e.g., one-to-one, one-to-many, or many-
to-many).

• Total Participation: Total participation indicates that every instance of an entity must
participate in a relationship. It is represented by a double line connecting the entity to the
relationship diamond.

• Structural Constraint: Structural constraints define the rules and limitations on the
relationships between entities, focusing on two main aspects: cardinality (which we’ve
discussed) and participation (total or partial). Cardinality constraints specify the maximum

Unit: 5 – ER Diagrams 10
DCA1208: Database Management System

number of times an entity can participate in a relationship, while participation constraints

indicate whether the participation is total or partial.

2.3. Some Examples of ER Model

Some Examples of ER Diagrams provide practical insights into how Entity-Relationship (ER)
models are used to design databases. These examples showcase how entities, attributes, relationships,
and constraints are organised to represent different scenarios or applications.

Here are a few illustrative examples:

1. Online Ticket & Event Management System

Fig 1. Online Ticket & Event Management System

Online Ticketing and Event Management Features

• User Registration: Allows users to create accounts and manage their profiles, and User
Authentication implements secure login mechanisms to authenticate users.
• Event Creation: This feature enables event organisers to create and manage events. It also
allows users to search for events based on criteria such as location, date, category, and keywords.

Unit: 5 – ER Diagrams 11
DCA1208: Database Management System

• Ticket Booking: This allows users to book tickets for events. Ticket Printing and Delivery also
provide users with options to print or receive tickets electronically.
• Payment Processing: Payments are processed securely for ticket bookings. Payment
Confirmation also confirms users' ticket purchases and payment status.
• Event Management: Provide tools for event organisers to manage event details, such as dates,
venues, and ticket prices.

Let us analyse the Entities and Attributes now:

1. Event: This table stores information about events.

• event_id: This is a unique identifier for each event, often an auto-incrementing integer value.
• name: The name of the event.
• description: A detailed event description, including its purpose, agenda, etc.
• date: The date on which the event occurs.
• time: The time of the event.
• location: The venue where the event occurs (a reference to the venue table using a foreign
key).
2. User: This table stores user information.
• user_id: This is a unique identifier for each user, often an auto-incrementing integer value.
• name: The user’s full name.
• email: A user's email address is used for login and communication purposes.
• password: The user’s password for secure login (needs to be hashed and stored securely).
• user_type: This specifies the type of user (e.g., organiser, attendee, administrator).
3. Venue: This table stores information about event venues.
• venue_id: This is a unique identifier for each venue, often an auto-incrementing integer value.
• name: The name of the venue.
• address: The full address of the venue.
• capacity: The maximum number of people the venue can hold.
• city: It is the name of the city where the venue is located.
• state: It is the name of the state where the venue is located.
• zip_code: The zip code of the venue’s location.
4. Ticket: This table stores information about individual event tickets.
• ticket_id: This is a unique identifier for each ticket, often an auto-incrementing integer value.

Unit: 5 – ER Diagrams 12
DCA1208: Database Management System

• event_id: The event this ticket is associated with (foreign key referencing the Event table).
• order_id: The order this ticket belongs to (foreign key referencing the Order table).
• price: The price paid for the ticket.
• type: It shows the type of ticket, for example, “General Admission” or “VIP.”
• seat_number: The seat number associated with this ticket (may be null for general
admission).
5. Order: This table stores information about user ticket orders.
• order_id: This is a unique identifier for each order, often an auto-incrementing integer value.
• user_id: The user who placed the order (foreign key referencing the User table).
• date: The date the order was placed.
• total_price: The total price of all tickets in the order.
• payment_id: The payment method used for the order (foreign key referencing the Payment
table).
6. Payment: This table stores information about payments made for orders.
• payment_id: This is a unique identifier for each payment, often an auto-incrementing integer
value.
• order_id: The order associated with the payment (foreign key referencing the Order table).
• payment_method: The method used for payment (e.g., “Credit Card”, “PayPal”).
• transaction_id: The unique identifier provided by the payment gateway for the transaction.
7. Speaker: This table stores information about speakers participating in events.
• speaker_id: This is a unique identifier for each speaker, often an auto-incrementing integer
value.
• name: The speaker’s full name.
• bio: A brief biography or description of the speaker.
• event_id: The event the speaker participates in (foreign key referencing the Event table).

Unit: 5 – ER Diagrams 13
DCA1208: Database Management System

2. ER diagram for an AIRLINE database schema.

This ER (Entity-Relationship) diagram represents a flight reservation system, showing how key
components such as airports, airplanes, flights, and reservations are interconnected. Let’s go step by
step to explain the entities, attributes, and relationships:

Entities and Attributes

1. Airport:
o Attributes include Airport_code, City, State, Name.
o Represents airports where flights depart and arrive.
o Linked to Flight_Leg through DEPARTURE_AIRPORT and ARRIVAL_AIRPORT.

2. Airplane_Type:
o Attributes include Type_name, Max_seats, Company.
o Represents a classification of aeroplanes based on capacity and manufacturer.

3. Airplane:
o Attributesinclude Airplane_id, Total_no_of_seats.
o Represents specific airplanes, each belonging to a particular Airplane_Type.

Unit: 5 – ER Diagrams 14
DCA1208: Database Management System

4. Flight:
o Attributes: Number, Airline, Weekdays.
o Represents an overarching flight, which may consist of multiple legs.

5. Flight_Leg:
o Attributes: Leg_no, Scheduled_dep_time, Scheduled_arr_time.
o Represents a segment of a flight, such as one portion of a journey from one airport to another.

6. Fare:
o Attributes: Code, Amount, Restrictions.
o Represents the cost and restrictions of tickets for flights.

7. Leg_Instance:
o Attributes: Date, No_of_avail_seats.
o Represents a specific instance of a flight leg on a given date.

8. Reservation:
o Attributes: Customer_name, Cphone.
o Represents the details of a booking made by a customer.

9. Seat:
o Attributes: Seat_no.
o Represents a specific seat assigned to a customer as part of their reservation.

Unit: 5 – ER Diagrams 15
DCA1208: Database Management System

3. ER Diagram of Hospital Management System

This ER Diagram represents a Hospital Management System and explains the relationships between
different entities in managing hospital operations, such as patients, doctors, nurses, rooms, bills, and
records. Let’s go step by step:

Fig 2. ER Diagram of Hospital Management System

Entities and Attributes

1. Patient:
o Attributes: P-ID (Patient ID), Name, DOB, Gender, Age, Mob-No.
o Represents patients in the hospital.

2. Doctor:
o Attributes: Dept (Department), Qualification.
o Represents doctors who consult patients.

3. Nurse:
o Inherits from Employee (has attributes E-ID, Name, Salary, Sex, Mob-No, Address, State, City,
Pin-no).
o Represents nurses who govern rooms and assist doctors.

4. Receptionist:

Unit: 5 – ER Diagrams 16
DCA1208: Database Management System

o Also inherits from Employee.

o Represents receptionists who maintain patient records.

5. Rooms:
o Attributes: R-ID (Room ID), Type, Capacity, Availability.
o Represents rooms available in the hospital, assigned to patients.

6. Bills:
o Attributes: B-ID (Bill ID), Amount.
o Represents bills generated for patients.

7. Test Report:
o Attributes: Result, Test-Type, P-ID (Patient ID), R-ID (Room ID).
o Represents diagnostic or medical test results for patients.

8. Records:
o Attributes: Record-no, App-no.
o Represents records maintained by receptionists for patients’ history, appointments, and
other hospital-related activities.

Unit: 5 – ER Diagrams 17
DCA1208: Database Management System

3. CONVERTING ER DIAGRAMS INTO TABLES

Each diagram component is systematically mapped to database tables and columns when converting
an ER diagram into a relational database schema.

Here’s how the mapping is typically done, step by step:

Mapping of Mapping of Binary

Mapping of Weak
Regular Entity 1:1 Relationship
Entity Types
Types Types

Mapping of Mapping of Binary Mapping of Binary

Multivalued M Relationship 1 Relationship
Attributes Types Types

Mapping of N-ary
Relationship
Types

Fig4. Steps in Converting ER Diagrams into Tables

Step 1: Mapping of Regular Entity Types

• Entities in the ER diagram are mapped to tables in the relational database.
• Each entity's attribute becomes a column in the table, with the key attribute becoming the
table's primary key.
• Example: If you have an entity Customer with attributes CustomerID, Name, and Address, you
would create a table named Customer with these columns, and CustomerID would be the
primary key.

Step 2: Mapping of Weak Entity Types

• Weak entities cannot be uniquely identified by their own attributes alone and rely on a related
strong entity.

Unit: 5 – ER Diagrams 18
DCA1208: Database Management System

• A table is created for the weak entity, including its attributes and the primary key of the related
strong entity.
• The primary key of the weak entity table is a combination of its own attributes (if any) and the
primary key of the strong entity.

• Example: If you have a weak entity Dependent related to Employee, you create a Dependent
table with attributes like DependentName and EmployeeID, where EmployeeID comes from the
Employee table. The primary key would be a combination of DependentName and EmployeeID.

Step 3: Mapping of Binary 1:1 Relationship Types

• In a 1:1 relationship, each entity instance is associated with at most one instance of another
entity.
• The relationship can be mapped by adding the primary key of one entity as a foreign key in
the table of the other entity.
• If the relationship is total (i.e., all instances must participate), combining the two entities into a
single table may be better.
• Example: If there is a 1:1 relationship between an Employee and an Office, you could add
OfficeID as a foreign key in the Employee table or vice versa, depending on the scenario.

Step 4: Mapping of Binary 1 Relationship Types

• In a 1 relationship, one entity is associated with multiple instances of another entity.
• The primary key of the "one" side entity is added as a foreign key in the table corresponding to
the "many" side entity.
• Example: If the Department and Employee are in a 1

relationship (a department has many employees), add DeptID as a foreign key in the Employee table.

Step 5: Mapping of Binary M Relationship Types

• For M relationships, where multiple instances of one entity are associated with multiple
instances of another, a new table (called a junction table or associative entity) is created.
• This new table includes the primary keys of the related entities as foreign keys and may also
include other attributes specific to the relationship.
• The combination of these foreign keys often forms the primary key of the junction table.
• Example: To establish a relationship between a Student and a Course, a StudentCourse table is
created with StudentID and CourseID as foreign keys and a primary key made up of both.

Unit: 5 – ER Diagrams 19
DCA1208: Database Management System

Step 6: Mapping of Multivalued Attributes

• Multivalued attributes can have multiple values for a single entity instance.
• A new table is created for the multivalued attribute, including the primary key of the original
entity and the multivalued attribute itself.
• The primary key of this new table is usually a combination of the original entity's primary key
and the multivalued attribute.
• Example: If Customer has a multivalued attribute PhoneNumber, create a CustomerPhone table
with CustomerID and PhoneNumber. The primary key would be the combination of CustomerID
and PhoneNumber.

Step 7: Mapping of N-ary Relationship Types

• For N-ary relationships (relationships involving more than two entities), a new table is created
to represent the relationship.
• This table includes the primary keys of all participating entities as foreign keys.
• The combination of these foreign keys typically forms the table's primary key.
• Example: In a relationship involving Supplier, Part, and Project, a new table (e.g.,
SupplierPartProject) would be created with foreign keys SupplierID, PartID, and ProjectID.
Additional attributes specific to the relationship can also be added to this table.

By following these steps, you can systematically map an ER diagram into a set of relational tables that
accurately represent the structure and constraints of the original model.

Let us understand better with an example:

Consider the ER conceptual schema diagram for the COMPANY database as shown.

Unit: 5 – ER Diagrams 20
DCA1208: Database Management System

Fig 5. ER conceptual schema diagram for the COMPANY database

Step 1: Mapping of Regular Entity Types: we create the relations EMPLOYEE, DEPARTMENT, and
PROJECT in Figure 9.2 to correspond to the regular entity types EMPLOYEE, DEPARTMENT, and
PROJECT in Figure. The foreign key and relationship attributes, if any, are not included yet; they will
be added during subsequent steps. These include the attributes Super_ssn and Dno of EMPLOYEE,
Mgr_ssn and Mgr_start_date of DEPARTMENT, and Dnum of PROJECT. In our example, we choose Ssn,
Dnumber, and

Pnumber as primary keys for the relations EMPLOYEE, DEPARTMENT, and PROJECT, respectively.
The knowledge that the Dname of the DEPARTMENT and the Pname of the PROJECT are secondary
keys are kept for possible use later in the design.

The relations created from mapping entity types are sometimes called entity relations because each
tuple represents an entity instance. The result after this mapping step is shown below.

Unit: 5 – ER Diagrams 21
DCA1208: Database Management System

EMPLOYEE

DEPARTMENT

PROJECT

Fig6. Entity relations after step 1.

Step 2: Mapping of Weak Entity Types:

In this step, we create the relation DEPENDENT to correspond to the weak entity type DEPENDENT.
We include the primary key Ssn of the EMPLOYEE relation—which corresponds to the owner entity
type—as a foreign key attribute of DEPENDENT; we rename it Essn, although this is not necessary.

The primary key of the DEPENDENT relation is the combination {Essn, Dependent_name} because
Dependent_name is the partial key of DEPENDENT.

DEPARTMENT

Fig7. Additional weak entity relation after step 2.

Unit: 5 – ER Diagrams 22
DCA1208: Database Management System

Fig 8: Result of mapping the COMPANY ER schema into a relational database schema.

Step 3: Mapping of Binary 1:1 Relationship Types:

3.1 Foreign key approach: In our example, we map the 1:1 relationship type MANAGES from Figure
9.1 by choosing the participating entity type DEPARTMENT to serve in the role of S because its
participation in the MANAGES relationship type is total (every department has a manager). We
include the primary key of the EMPLOYEE relation as a foreign key in the DEPARTMENT relation and
rename it Mgr_ssn. We also include the simple attribute Start_date of the MANAGES relationship type
in the DEPARTMENT relation and rename it Mgr_start_date, as seen in Figure 8 above.

Step 4: Mapping of Binary 1: N Relationship Types:

Our example maps the 1: N relationship types of WORKS_FOR, CONTROLS, and SUPERVISION. For
WORKS_FOR, we include the primary key Dnumber of the DEPARTMENT relation as a foreign key in
the EMPLOYEE relation and call it Dno. For SUPERVISION, we include the primary key of the
EMPLOYEE relation as a foreign key in the EMPLOYEE relation itself—because the relationship is
recursive— and call it Super_ssn. The CONTROLS relationship is mapped to the foreign key attribute
Dnum of PROJECT, which references the primary key Dnumber of the DEPARTMENT relation as
depicted in Fig 8.

Unit: 5 – ER Diagrams 23
DCA1208: Database Management System

Step 5: Mapping of Binary M: N Relationship Types:

In our example, we map the M: N relationship type WORKS_ON by creating the relation WORKS_ON
in Figure 8. We include the primary keys of the PROJECT and EMPLOYEE relations as foreign keys in
WORKS_ON and rename them Pno and Essn, respectively. We also include an attribute Hours in
WORKS_ON to represent the Hours attribute of the relationship type. The primary key of the
WORKS_ON relation is the combination of the foreign key attributes {Essn, Pno}.

This relationship relation is shown below:

Fig 9: Relationship relation after step 5.

Step 6: Mapping of Multivalued Attributes:

In our example, we create a relation DEPT_LOCATIONS. The attribute Dlocation represents the
multivalued attribute LOCATIONS of DEPARTMENT, while Dnumber—as foreign key—represents
the primary key of the DEPARTMENT relation. The primary key of DEPT_LOCATIONS is the
combination of {Dnumber, Dlocation}. A separate tuple will exist in DEPT_LOCATIONS for each
location that a department has.

Fig 10: Relation representing multivalued attribute after step 6.

Step 7: Mapping of N-ary Relationship Types:

For example, consider the relationship type SUPPLY in Figure 11. This can be mapped to the relation
SUPPLY shown in Figure 12, whose primary key combines the three foreign keys {Sname, Part_no,
Proj_name}.

Unit: 5 – ER Diagrams 24
DCA1208: Database Management System

Fig 11. The SUPPLY relationship.

Fig 12. Mapping the n-ary relationship type SUPPLY

Unit: 5 – ER Diagrams 25
DCA1208: Database Management System

4. SUMMARY

The Entity-Relationship (ER) Model is a fundamental conceptual framework used in database design
to visually represent data and their relationships within a system. At its core, the ER model helps
structure and define entities (real-world objects or concepts) and their relationships.

This model is widely used in the early stages of database design, providing a clear and concise way to
map out the data requirements and relationships in a system before implementation. The ER model
typically includes entities, attributes (characteristics of entities), and relationships, which define how
entities are connected. This model serves as a blueprint for creating a relational database schema.

ER diagrams are visual representations of the ER model with several vital components. The primary
components include entities, depicted as rectangles, and attributes, represented as ovals connected
to their respective entities.

Relationships between entities are shown as diamonds, with lines connecting them to the involved
entities. Relationships can have their own attributes, known as descriptive attributes. They may also
have cardinality constraints, which define the nature of the relationship (e.g., one-to-one, one-to-
many, or many-to-many). Other components include

primary keys, uniquely identifying each entity instance, and foreign keys, which establish connections
between different entities.

The ER model uses a set of standard symbols to represent various components and their interactions.
Rectangles usually symbolise entities, while attributes are shown as ovals attached to their respective
entities.

Diamonds represent relationships between entities, with lines connecting them to those involved.
Crow’s foot notation is often used to denote cardinality constraints, where a single line represents a
one-to-one relationship, a line with a crow’s foot denotes a one-to-many relationship, and a crow’s
foot at both ends indicates a many-to-many relationship. Double rectangles and double ovals
represent weak entities and multivalued attributes, respectively.

Once an ER diagram is completed, the next step in database design is converting the ER model into a
relational schema, where entities, attributes, and relationships are transformed into tables, columns,
and foreign keys. Entities in the ER diagram typically become tables, with their attributes as columns.

Unit: 5 – ER Diagrams 26
DCA1208: Database Management System

Relationships are converted into foreign keys, linking related tables or, in some cases, into separate
tables to handle many-to-many relationships. This process involves ensuring that all entities and
relationships are appropriately normalised to avoid redundancy and ensure data integrity.
Converting ER diagrams into tables is crucial in transitioning from conceptual design to actual
database implementation.

Unit: 5 – ER Diagrams 27
DCA1208: Database Management System

5. GLOSSARY

The Entity-Relationship (ER) Model is a conceptual framework used in

database design to represent data objects (entities), attributes, and
ER Model - relationships. It provides a visual diagram (ER diagram) to map out the

data structure before implementing a relational database.

Is-a hierarchies represent inheritance relationships in database

modelling, where a subtype entity inherits attributes and relationships
Is-a Hierarchies - from a supertype entity. This is commonly used when entities share

common characteristics and distinct attributes.

Constraints in databases are rules that enforce data integrity and

consistency. They define limitations on the data types, relationships, and

Constraints - operations allowed on the data, such as primary key constraints ensuring
unique identification or foreign key constraints maintaining referential
integrity.

Relationship types define the associations between entities in an ER

Relationship model, characterised by cardinality (e.g., one-to-one, one-to-many, many-

- to-many) and the nature of the interaction. These relationships are
Types
represented by diamonds in ER diagrams, linking related entities.

Multivalued attributes are attributes in an ER model that can have

Multivalued multiple values for a single entity instance. For example, a Person entity
- might have a multivalued attribute like Phone Numbers, where one
Attributes
person can have several phone numbers.

Regular entity types, also known as strong entities, can exist

Regular Entity independently of other entities in a database. They have a primary key to
- identify each instance uniquely and are typically represented by a single
Types
rectangle in ER diagrams.

Unit: 5 – ER Diagrams 28
DCA1208: Database Management System

A primary key is a unique identifier for each record in a database table,

Primary Key - ensuring that no two records have the same key value. It is essential for
establishing relationships between tables and enforcing entity integrity.

A foreign key is an attribute in one table that links to the primary key of
another table, creating a relationship between the two tables. It ensures
Foreign Key - referential integrity by ensuring that the value in the foreign key column

exists in the referenced table.

Unit: 5 – ER Diagrams 29
DCA1208: Database Management System

6. SELF-ASSESSMENT QUESTIONS

SELF-ASSESSMENT QUESTIONS – 1
Fill in the blanks:
1 What is the primary function of an Entity-Relationship (ER) model in database design?
A) To define the physical storage structure of the database
B) To analyse an application's semantic features independently of events
C) To optimise query performance
D) To manage user access control
2 In an ER diagram, what symbol represents relationships between entities?
A) Rectangle
B) Oval
C) Diamond
D) Circle
3 Which of the following best describes a composite attribute?
A) An attribute that has multiple values for a single entity instance
B) An attribute that cannot be further subdivided
C) An attribute that is atomic and has no internal structure
D) An attribute that can be subdivided into smaller parts, such as an address
4 Which of the following is true about a primary key in an ER model?
A) It can have duplicate values within the same domain
B) It must always have a value and be unique within its domain
C) It can be composed of multiple values from different entities
D) It is only used to link to other tables in the database
5 What is a "borrowed key" attribute in the context of is-a hierarchies?
A) A key borrowed from another table to create a foreign key relationship
B) A key used to reference external databases
C) A key that is inherited from a more general entity in an is-a relationship
D) A key that is used temporarily during database transactions
6 What does mapping cardinality in an ER model indicate?
A) The types of attributes in an entity

Unit: 5 – ER Diagrams 30
DCA1208: Database Management System

B) The number of instances in one entity associated with instances in another entity
C) The total number of entities in the database
D) The hierarchical relationship between entities
7 Which of the following best describes a one-to-one relationship?
A) One entity instance in E1 is associated with many instances in E2
B) Many instances in E1 are associated with one instance in E2
C) Each instance in one entity is associated with at most one instance in another entity
D) Many instances in E1 are associated with many instances in E2
8 How is a weak entity represented in an ER diagram?
A) By a single rectangle
B) By a double rectangle
C) By a diamond
D) By a double diamond
9 What is the purpose of identifying relationships in an ER model?
A) To indicate a derived attribute
B) To connect multivalued attributes to an entity
C) To link weak entities to their corresponding strong entities
D) To establish a one-to-many relationship between two entities
10 Which symbol in an ER diagram represents a multivalued attribute?
A) Oval
B) Double oval
C) Rectangle
D) Dashed oval
11 What is the first step in converting an ER diagram into a relational database schema?
A) Mapping of Binary 1:1 Relationship Types
B) Mapping of Weak Entity Types
C) Mapping of Regular Entity Types
D) Mapping of Multivalued Attributes
12 How are weak entities typically mapped in a relational database?
A) By creating a table with its own primary key
B) By combining it with a strong entity into a single table
C) By creating a table without any foreign key references

Unit: 5 – ER Diagrams 31
DCA1208: Database Management System

D) By creating a table that includes the primary key of the related strong entity as part of its
primary key
13 What approach maps a binary many-to-many (M) relationship in a relational database?
A) Creating two tables, one for each entity involved in the relationship
B) Adding a foreign key in one of the existing tables
C) Creating a new table that includes the primary keys of the related entities as foreign keys
D) Using a single table with composite primary keys
14 When mapping an ER diagram to a relational schema, which step involves creating a new table
for multivalued attributes?
A) Step 3: Mapping of Binary 1:1 Relationship Types
B) Step 5: Mapping of Binary M Relationship Types
C) Step 6: Mapping of Multivalued Attributes
D) Step 7: Mapping of N-ary Relationship Types
15 When mapping a binary one-to-many (1) relationship, where is the primary key of the "one"
side entity added?
A) In the table corresponding to the "many" sides entity as a foreign key
B) In the table corresponding to the "one" side entity as a foreign key
C) In a separate junction table
D) As a composite key in both related tables

Unit: 5 – ER Diagrams 32
DCA1208: Database Management System

7. TERMINAL QUESTIONS

1. Define an Entity-Relationship Model.

2. List the Components of the ER Diagram.
3. Differentiate between Simple and composite attributes.
4. What are Is-a hierarchies?
5. Define Primary Keys.
6. Explain Borrowed Key Attributes.
7. Differentiate between Candidate Key and Concatenated Key.
8. Describe a few important Symbols Used in the ER Model.
9. Identify the steps in Converting ER Diagrams into Tables.
10. Consider an ER conceptual schema diagram for the COMPANY database convert into tables with
specific steps.

Unit: 5 – ER Diagrams 33
DCA1208: Database Management System

8. ANSWERS

8.1. Self-Assessment Answers:

1. B) To analyse an application's semantic features independently of events
2. C) Diamond
3. D) An attribute that can be subdivided into smaller parts, such as an address
4. B) It must always have a value and be unique within its domain
5. C) A key that is inherited from a more general entity in an is-a relationship
6. B) The number of instances in one entity associated with instances in another entity
7. C) Each instance in one entity is associated with at most one instance in another entity
8. B) By a double rectangle
9. C) To link weak entities to their corresponding strong entities
10. B) Double oval
11. C) Mapping of Regular Entity Types
12. D) By creating a table that includes the primary key of the related strong entity as part of its
primary key
13. C) Creating a new table that includes the primary keys of the related entities as foreign keys
14. C) Step 6: Mapping of Multivalued Attributes
15. A) In the table corresponding to the "many" side entity as a foreign key

8.2. Terminal Answers:

Answer 1: An Entity-Relationship (ER) Model is a conceptual framework used in database design to
represent a database's structure visually. It shows entities, their attributes, and their relationships,
helping map out data requirements before implementing the database. Refer to section 2 for more
details.

Answer 2: The key components of an ER diagram include entities (represented by rectangles),

attributes (ovals), relationships (diamonds), and various constraints such as primary keys and
foreign keys, as well as cardinality and participation constraints. Refer to section 2.1 for more
details.

Unit: 5 – ER Diagrams 34
DCA1208: Database Management System

Answer 3: Simple attributes are indivisible, atomic values like a person's first name, while composite
attributes can be broken down into smaller components, such as an address, subdivided into street,
city, and zip code. Refer to section 2.1 for more details.

Answer 4: Is-a hierarchies represent inheritance relationships in databases, where a subtype entity
inherits attributes and relationships from a supertype entity. This hierarchy allows for shared
attributes among related entities, modelling a generalised-to-specific relationship. Refer to section
2.1 for more details.

Answer 5: Primary keys are unique identifiers for each record in a database table. They ensure that
each record can be uniquely distinguished from all others, are mandatory, and should never change
over time. Refer to section 2.1 for more details.

Answer 6: Borrowed key attributes refer to primary keys from a strong entity inherited by a weak
entity in an "is-a" relationship. These borrowed keys help uniquely identify instances of the weak
entity by combining them with its own attributes. Refer to section 2.1 for more details.

Answer 7: A candidate key is any attribute or combination of attributes that can uniquely identify a
record in a table, while a concatenated key specifically refers to a key formed by combining multiple
attributes to create a unique identifier. Refer to section 2.1 for more details.

Answer 8: Key symbols in an ER model include rectangles for entities, ovals for attributes, diamonds
for relationships, double rectangles for weak entities, double ovals for multivalued attributes, and
underlined ovals for key attributes. Refer to section 2.2 for more details.

Answer 9: Steps include mapping regular entities to tables, mapping weak entities by including
foreign keys from strong entities, mapping binary 1:1 and 1 relationships by adding foreign keys,
mapping M relationships using junction tables, and handling multivalued and N-ary relationships
with separate tables. Refer to section 3 for more details.

Answer 10: To convert an ER diagram for the COMPANY database into tables, map each entity to a
table with attributes as columns. Include primary keys, map weak entities by adding foreign keys,
handle 1:1 and 1 relationships by adding foreign keys, create junction tables for M relationships, and
map multivalued attributes into separate tables. Refer to section 3 for more details.

Unit: 5 – ER Diagrams 35
DCA1208: Database Management System

9. REFERENCES

1. Fundamentals of Database Systems, Ramez Elmasri and Shamkant B. Navathe, 7th edition,
Pearson, 2016, ISBN-10: 0133970779.
2. Database Management Systems, Raghu Ramakrishnan and Johannes Gehrke,3rd Edition,
McGraw-Hill Education,2002 (for the 3rd Edition), ISBN-13: 978-0072465631
3. Database System Concepts, Abraham Silberschatz, Henry F. Korth, S. Sudarshan,7th Edition,
McGraw-Hill Education, 2019, ISBN-10: 0073523321

Unit: 5 – ER Diagrams 36