Chapter 3 :
Conceptual Data
Module -1 Modelling Using
Entities and
Relationships
Outline :
Using High-Level Conceptual Data Models for Database Design
A Sample Database Application
Entity Types, Entity Sets, Attributes, and Keys
Relationship Types, Relationship Sets, Roles, and Structural Constraints
Weak Entity Types
ER Diagrams, Naming Conventions, and Design Issues
Specialization and Generalization
Overview :
• There are 2 main activities.
• Database design
• Applications design
• In this chapter → focusing on designing/creating a database.
• Conceptual Schema / Conceptual design →one of the important phase
while designing database.
• Entity Relationship Model (ER Model)→ it is one of the popular high
level conceptual data model.
3.1 High-Level
Conceptual Data Models
for Database Design
Phases involved :
1. Requirements collection and analysis.
• Data requirements
• Functional requirements
2. Conceptual schema / Conceptual
Design
3. Logical design /data model mapping
4. Physical design phase
3.2 Entities and Attributes
Entity :
• It is a thing in the real world.
• An object with physical or conceptual existence.
• Physical existence( person, car, employee), conceptual existence (company,job,university)
Attributes :
• Particular properties that describe entity.
• Example : Employee { Name, age,salary}
Types of attributes:
1. Composite versus simple (atomic) attributes
2. Single-valued versus multivalued attributes (age, colors)
3. Stored versus derived attributes (Birth_date, age from current date)
4. NULL values
5. Complex attributes
Complex attributes
Composite attributes
3.2.1 Entity Types, Entity Sets, Keys, and Value Sets
Entity Type :
• Collection of entities with same attributes.
• Example : EMPLOYEE entity type or PROJECT entity type.
Entity Set :
• Collection of all entities with same entity type.
Key Attributes :
• Each entity type is always having one or more unique/distinct values for entity set.
• Example : USN of STUDENT
Value Sets:
• Specifies the set of values assigned for that attribute.
• Example : Employee age between 18 and 60.
3.3 Naming
Convention or
Notation for
ER Diagram
3.3 Naming
Convention or
Notation for
ER Diagram
3.4 Sample Database Application
Database : COMPANY
Company database keep’s track of a company’s employees, departments, and projects.
• Employees, departments, and projects
• Company is organized into departments
• Department controls a number of projects
• Employee: store each employee’s name, Social Security number, address, salary,
sex (gender), and birth date
• Keep track of the dependents of each employee
Preliminary design phase of Entity type COMPANY
ER model describes data as:
• Entities
• Relationships
• Attributes
ER schema
diagram for
the company
database
3.5 Weak Entity Types
• An entity type that does not have a key attribute of their own.
• Key attribute → combination of partial key of weak entity type and particular entity from
strong entity type.
• Example :
DEPENDENT is a weak entity type,
with EMPLOYEE as its identifying entity type,
via the identifying relationship type DEPENDENT_OF.
Identifying
entity type
Weak entity type identifying relationship type
3.6 Specialization and Generalization
3.6.1 Specialization :
• It is the process of
defining a set of
subclasses of a entity
type.
• This entity type is called as
superclass.
3.6.2 Generalization
• It is the reverse of the
specialization process.
• Several classes with
common features are
generalized into a
superclass.
• VEHICLE becomes
superclass.
• CAR and TRUCK becomes
subclasses of VEHICLE.
