CSEN 501 – CSEN501 - Databases I

Lecture 1:
The Entity-Relationship Model

Prof. Dr. Slim Abdennadher

Dr. Nada Sharaf

German University Cairo, Faculty of Media Engineering and Technology

©
Abdennadher Lecture 01
CSEN 501
Lecture Outline

Data Model
Entity-Relationship Diagram
Attributes
Keys
Relationships

©
Abdennadher Lecture 01
CSEN 501
Data Model
A data model describes conceptual structuring of data
stored in database.
Example: Data is set of records, each with ID, name,
address, and courses.
Example: Data is a graph where nodes represent proteins
and edges represent chemical bonds between proteins.
Data model provides a description of the database
structure including:
The data
The relationships within the data
The constraints on the data
Different representation methods:
Entity-Relationship Model
Relational Model
Object-Oriented Model
Object Relational Model
©
Abdennadher Semistructured Data Model Lecture 01
CSEN 501
Database Schema and Database instance

Database Schema describes how data is to be structured

defined during creation of the database
rarely change
Database instance: The data stored in the database at a
given moment.
rapidly change
updated continously

©
Abdennadher Lecture 01
CSEN 501
What is the Entity-Relationship Model?

A Picture Is Worth a Thousand Words

©
Abdennadher Lecture 01
CSEN 501
Entity-Relationship Model
First introduced in the mid 1970’s by Chen as an
improvement to the relational model.
The Entity-Relationship (E/R) model allows us to draw
diagrams of database designs.
Represent different types of data and how they relate to
each other.
The design is a drawing called the entity-relationship
diagram.
The Entity-Relationship model is an example of what is
called a semantic model
Description of conceptual and external levels of data
Independent of the internal and physical aspects
We will learn later how to convert an E/R diagram to a
relational schema (database design)
Ideas ⇒ E/R Design⇒ Relational Schema ⇒ Relational DBMS
©
Abdennadher Lecture 01
CSEN 501
Entity - Entity Set - Attributes

An Entity is an abstract object of some sort.

Banking system: Customer, Account, Loan.
Airline system : Aircraft, Passenger, Flight, Airport.
An Entity set is a collection of similar entities.
Entities have attributes.
An attribute is a property of the entities in an entity set.
In an E/R diagram:
A rectangle represents an entity set
An oval represents an attribute
A line connects an entity set (rectangle) to an attribute
(oval)

©
Abdennadher Lecture 01
CSEN 501
Attributes: An Example

What appears to be an attribute to one designer may be an

entity to another
©
Abdennadher Lecture 01
CSEN 501
Attributes: Domain and Values

Attributes have a value set or domain (set of possible values).

Null values: Sometimes the value of an attribute is unknown at the
present time or undefined for a particular instance.
Composite Attributes: can be decomposed into smaller elements.
Example: Address can be broken down into street, city, state, and zip.
Multivalued Attributes: have multiple values for an entity instance, e.g.
student with more than one e-mail address.
Derived Attributes: whose value can be calculated when needed.
Example: Age may be determined from the current date and the date of
birth.

©
Abdennadher Lecture 01
CSEN 501
Attributes in ER-Diagram
Multivalued Attribute: Double oval around the attribute’s
name
Composite Attribute: Writing the name in an oval and then
drawing ovals for the individual components which are
connected by lines to the composite attribute’s oval
Derived Attribute: Dashed oval around the attribute’s name

©
Abdennadher Lecture 01
CSEN 501
Entity Identification

The process of identifying entities is one of the most important

steps in developing a data model.
Some guidelines:
An informal questioning approach: the analyst asks targeted questions
to determine what information is necessary and whether or not that
information is recorded within the system.
Face to face discussions with user: the nouns should be recorded.
The existing documentation often contains clues.
Every fact that is required to support the business is almost certainly an
attribute. In turn each of these attributes will belong to an entity. If no
“parent” entity can be found for one or more of these low level facts,
then this indicates that your entity search is incomplete.

Entity identification can continue once the drawing of the data model diagram
has begun

©
Abdennadher Lecture 01
CSEN 501
Keys I

Each entity occurrence can be uniquely identified, by using

an attribute or a combination of attributes as a key.
A Super Key is the attribute (or group of attributes) that
serve to uniquely identify each entity occurrence.
Consider the following problem: the name and address of
an individual were used as the super key for identifying the
patients within a hospital.
Take the example of a patient called Mohammed
Mohammed living in New Cairo City. He has a son also
called Mohammed Mohammed living at the same address.
Name and Address would not necessarily provide a unique
identifier and confusion could easily arise.
In a hospital system patients each have a PatientNumber
as their super key.

©
Abdennadher Lecture 01
CSEN 501
Keys II

If two or more data items are used as the unique identifier,

then this represents a Compound Key.
Example: a compound key used to identify a book could be
“Title” together with “Author”.
A Candidate Key is a super key such that no proper subset
of its attributes is itself a super key.
An entity set might have several candidate keys.
Example: A book could be identified, either by “Title”
together with “Author” or by the widely used unique
identifier for books - the ISBN number.
The Primary Key is the candidate key actually chosen.

©
Abdennadher Lecture 01
CSEN 501
Relationships

Entities are often linked by associations or relationships.

A Relationship is a connection between two or more entity
sets.
In an E/R diagram
a diamond represents a relationship
a line connects the relationship to each entity set.

©
Abdennadher Lecture 01
CSEN 501
Relationship Link Phrase
Alternative representation of relationships:

Relationship link phrase should be a short description of

the nature of the relationship.
It is always read clockwise with respect to the entities that
it links.
Example: “Manager is responsible for department” and
“Department is responsibility of manager”.

©
Abdennadher Lecture 01
CSEN 501
Ternary Relationships
A relationship set need not be an association of precisely
two entities; it can involve three or more when applicable.
A relationship that involves three entities is called ternary
relationship.
Example: A store having multiple locations

©
Abdennadher Lecture 01
CSEN 501
Recursive Relationships

A Recursive Relationship is a relationship that associate

an entity type with itself.

©
Abdennadher Lecture 01
CSEN 501
Relationship Cardinality
One-to-one relationship: Each manager is responsible for only one department and each
department is the responsibility of only one manager.

One-to-many relationship (crowsfoot symbol): Each company employs one or more

employees, but each employee is employed by only one company.

Many-to-many relationship: Each lecturer teaches one or more courses and each course is
taught by one or more lecturers

©
Abdennadher Lecture 01
CSEN 501
Identifying Relationships

In order to identify the degree of the relationship between the entities X

and Y the following two questions need to be asked.
Question 1: Can an occurrence of X be associated with
more than one occurrence of Y?
Question 2: Can an occurrence of Y be associated with
more than one occurrence of X?
Four possible outcomes:
Question 1 Question 2
YES NO
No Yes
YES YES
NO NO
Option 1 and Option 2: A one-to-many relationship has been identified.
Option 3: A many-to-many relationship has been identified.
Option 4: A one-to-one link has been identified.

©
Abdennadher Lecture 01
CSEN 501
Relationship Participation
The final property of the relationship statement is the total and partial
participation.
A solid line shows that an entity occurrence must be associated with
each occurrence of the other entity.
Example: Each passenger must possess a ticket, and each ticket must
belong to a passenger.

A dotted line shows that an entity occurrence may be associated with

each occurrence of the other entity.
Example: Each book may be borrowed by a borrower, and each
borrower must borrow one or more books. belong to a passenger.

©
Abdennadher Lecture 01
CSEN 501
Weak Entities
A Weak Entity is an entity that cannot be uniquely
identified by its own attributes alone.
A weak entity can be identified uniquely only by
considering another entity.
Example: Each book has several editions, and certainly it
is nonsense to speak about an edition if this does not
happen in the context of a specific book.

A weak entity is depicted in the ERD by drawing a double

rectangle around the entity and making the relationship
diamond a double diamond.
©
Abdennadher Lecture 01
CSEN 501
ERD Example - Small Conference

We need to keep track of authors that submit papers to the conference.

An author should be identified uniquely and information such as his
name (first, middle and last name) and research specialization should
be recorded.
We need to keep track of the papers submitted to the conference. A
research paper is identified by its author, title, and date. One author
can submit one or more papers to the conference and some papers are
submitted by more than one author.
Each paper is related to one or more research topics. We need to
keep track of the research topics that the conference has covered. A
research topic should be uniquely identified. The founder(s) and
description of each topic should also be recorded.
Each author participating in the conference has an account through
which he can access all conference-related material. An account is
identified by a unique username. The password, issue date and
validity period of an account should also be recorded.

©
Abdennadher Lecture 01
CSEN 501
ERD Example - Small Conference

©
Abdennadher Lecture 01
CSEN 501
Objectives

In this lecture, you learned

The meaning of entity and entity set
How to represent entities on the entity relationship (E/R) diagram
The meaning of an attribute
How attributes are associated with entities
The meaning of an attribute domain
The meaning of multi-valued, composite, and derived attributes
The meaning of diffierent kinds of keys
The meaning of relationship
How to represent relationship sets and their attributes on an E/R
diagram
The meaning and representation of the cardinality of a relationship
The meaning of existence dependency

©
Abdennadher Lecture 01
CSEN 501