CS3042/CS3272 – Database Systems

Laboratory Exercise 1
Entity Relationship Diagram

Entity Relationship Diagram is a type of structural diagram. It contains different symbols and
connectors that visualize two important information: The major entities within the system scope, and
the inter-relationships among these entities.

1 The Components and Features of an ER Diagram

1.1 Entity
An entity is an object or concept used to store information. Think of entities as nouns. They are
represented as rectangles.

Examples: a customer, student, car or product.

Student

1.2 Attribute

Attributes are the properties/characteristics that describes an entity. Attributes are visualized by an
oval or circle in an ER diagram. There are two main characteristics of an entity named “attribute
value” and “attribute domain”.

Considering an attribute named “grade”, a particular value (45 marks for a grade) of the attribute is
called “value” while the set/range of possible values(0-100) is called “domain”. In an ER model, an
attribute name appears inside the oval that has a line to the corresponding entity box.

Grade

Student

There are four types of attributes that are used in Entity Relationship diagrams.

1.2.1 Simple Attribute

The attribute value is atomic and cannot be further divided. These attributes are represented by single
lined ovals that are connected to an entity or an attribute (composite attribute).

Examples: First name, Last name, Index number, City, Street

CS3042 Database Systems Lab 1

1.2.2 Composite Attribute

If an attribute consists of two or more components or sub-attributes, we call them “composite

attributes”. These sub-attributes may or may not be atomic depending on the application it is used.
The composite nature is depicted by single lined ovals by branching off the component attributes.

Examples: Name that comprises a First name and Last name

Simple
First Name Last Name

Name
Composite

Student

1.2.3 Multi-Valued Attribute

A multi-valued attribute has two or more possible values for a particular attribute. A lecturer/student
may have multiple phone numbers which makes phone number a multi-valued attribute for a student
entity. These attributes are illustrated using a double oval around the attribute name.

Examples: One person can have multiple phone numbers

Phone Number

Student

1.2.4 Derived Attribute

A derived attribute can be derived from an associated entity or attribute. Such an attribute is illustrated
using dotted oval and a line.

Examples: Age from date of birth, House number from the address

CS3042 Database Systems Lab 1

 NIC

Age Student Date of Birth

1.2.5 Key Attribute

An attribute or set of attributes that can uniquely identify an entity is identified as a key attribute. A key
attribute may be a composite attribute also.

Example: Student Id number for a Student

First Name Last Name

Name
Student ID

Student

1.3 Relationships

A relationship type is a set of associations among different entity types. A relationship or relationship
instance is an ordered pair of a specific entity and its association with another entity. Diamond symbol
is used to illustrate the relationship type in an ER diagram. Normally, the diagram is drawn so that
the relationship is read from left to right.

Example: A student is a member of a team

Member
Student Team
of

Alternatively, The components can be arranged from top to bottom.

CS3042 Database Systems Lab 1

1.3.1 Relationship with Attributes

There may be instances where the association between two entities itself has attributes but they cannot
be appropriately associated with the entities. Therefore these attributes should be illustrated
associated with the relationship.

Example: Customer places an order on the set of items in a shop.

Order ID Order Date

Customer Order Item

Total Cost

1.3.2 Recursive Relationship

When the same entity participates more than once in a relationship, we call it a recursive relationship.
In these situations, two separate lines are drawn between the relationship and the entity and labelled
each line with a different role.

Example: One group is assigned to supervise other teams.

Supervisor

Team Supervision

Supervisee

1.3.3 Strong and Week Relationships

An entity is called strong if the existence of that attribute does not depend on other entity types.
Otherwise, they are recognized as week relationships. Double lines are drawn around the identifying
relationship and the line connecting the two to indicate the weak entity type.

Example: Existence of the team for laboratory works depends on the existence of labs.

CS3042 Database Systems Lab 1

 Group In Labs

1.4 Cardinality

Cardinality is the number of instance of an entity from a relation that can be associated with the
relation. There are three types of cardinalities as follows.

1.4.1 One-to-One Relationship

When only one instance of an entity is associated with the relationship, it is referred as a one to one
relationship.

1 1
Student Has Profile

1.4.2 One-to-Many Relationship

This refers to the relationship between two entities X and Y in which an instance of X may be linked
to many instances of Y, but an instance of Y is linked to only one instance of X.

1 N
Department Enlist Student

1.4.3 Many-to-Many Relationship

A many-to-many relationship refers to the relationship between two entities X and Y in which X may
be linked to many instances of Y and vice versa.

N M
Student Register Course

CS3042 Database Systems Lab 1

2 Crow’s Foot Notation
Several entity relationship diagram notations are available. We will be using Crow’s foot notation among them.

2.1 Entity

In crow’s foot notation, an entity is represented by a rectangle, with its name on the top. The name is
singular (entity) rather than plural (entities).

Student

2.2 Attribute

The attributes are included inside the rectangle below the entity name. Similar to the previous section
attributes explain the characteristics of the attributes. Properties that help to uniquely identify the
entity(key attributes) are marked with “*”.

Student

* Student ID
First_name
Last_name

2.3 Relationships

In this notation, relationships have two indicators named “Multiplicity” and “Optional/Mandatory”.
These are indicated on both sides of the line using two separate marks “| / >” or “| / o”. The mark
which is closest to the entity represents “Multiplicity” and other represents “Optional. Mandatory”.

Multiplicity Mandatory/Optional

2.3.1 Multiplicity

This refers to the maximum number of times that an instance of one entity can be associated with
instances in the related entity. It can be one or many.

CS3042 Database Systems Lab 1

 One

Figure 1: Multiplicity of One

Many

Figure 2: Multiplicity of Many

Multiplicity of many

2.3.2 Optional/Mandatory

This describes the minimum number of times one instance can be related to others. It can
be zero or one.

Mandatory

Optional

2.4 Sample Entity Relationships

Student Profile

Department Student

CS3042 Database Systems Lab 1

3 Lab Work
1. Create a free account in “Lucid Chart” using you “CSE” email.

2. Watch online resources given under Lab 1 section on Moodle which explains to you how to
create ER diagrams using “Lucid Chart”.

3. Draw an ERD for the following problem using the general methodology of designing an ERD
mentioned in part 1 using “Lucid Chart”. ( Identify at least 4 entities)

Department of Motor Traffic in Sri Lanka is responsible for administering and issuing driver’s
license. It is mandatory to take a learner’s exam(written) at any branch of the department of motor
traffic for a person who is wishing to have a driver’s license. If the particular person fails the exam
he/she can retake the exam after paying a certain amount to the motor traffic department. If he passes
the exam, a temporary license is issued (learner’s type) with a unique license number. The person
with the learner’s license should take his/her driver’s exam(practical) at any branch before the expiry
date (which is usually set at 18 months after the license issue date). If he passes the exam, the branch
issues him/her a driver’s license. If he fails, he can retake the drivers’ exam again, before the
learner’s license expires after paying an additional amount to the motor traffic department.

4. Draw an ERD for following scenario using “Crow’s Foot Notation”.

Administration of a government University that still manages their system manually has decided to
launch a project to implement an online system to make their processes easy. To make the search
process easy and useful, the library administration wishes to have details like author, the field of
study, … etc. to be stored under a book, in addition to its title. Although a book can easily be uniquely
identified by its ISBN number, the library has multiple copies of the same book. And also they treat
different editions of the books differently. The books are mainly categorized as lending and non-
lending. The policy of lending is as described below.

Any student or a lecturer can lend any book for a time period of two weeks and the time period will
be extended only if there’s no reservation for it at the end of the lent period. However, if a book is
categorized under a field of study that a lecture is qualified, he can lend the book for a period of four
weeks. And if a certain student takes a course of a lecturer, the lecture can grant that student a four
weeks lending period for any book that’s categorized under any of his qualified fields.

If a certain book is not in the library, lecturers and the students can make a request to the
administration to buy the book. For a book to be approved for buying it needs at least five requests
from students or one request from lecturers. This requests can also be made for existing books as
well to increase the number of copies available. To make the buying process quick and easy, the
library administration also wishes to keep the records of bookshops that have books under certain
fields of study and the history of book purchases to the library from those bookshops.

You are hired as the database engineer of this project. The first challenge is to create a database
design that would encapsulate the above requirements and any other required assumption
(assumptions should be clearly indicated). Identify all the entities, attributes and relations in the
above description and draw an EER/ER diagram.

5. Export the two documents as “.pdf” and upload.

CS3042 Database Systems Lab 1