Chapter 3: Conceptual Database Design- E-R Modeling
Database design is the process of coming up with
different kinds of specification for the data to be stored in
the database. The database design part is one of the middle
phases we have in information systems development where
the system uses a database approach. Design is the part on
which we would be engaged to describe how the data should
be perceived at different levels and finally how it is going to
be stored in a computer system.
3.1 Database Development Life Cycle
As it is one component in most information system
development tasks, there are several steps in designing a
database system. Here more emphasis is given to the design
phases of the system development life cycle. The major
steps in database design are;
1. Planning: that is identifying information gap in an
organization and propose a database solution to solve the
problem.
2. Analysis: that concentrates more on fact finding about the
problem or the opportunity. Feasibility analysis, requirement
determination and structuring, and selection of best design
method are also performed at this phase.
3. Design: in database designing more emphasis is given to
this phase. The phase is further divided into three sub-phases.
Conceptual Design, Logical Design, Physical Design:
4. Implementation: the testing and deployment of the
designed database for use.
5. Operation and Support: administering and maintaining
the operation of the database system and providing support to
users. In developing a good design, one should answer such
questions as:
What are the relevant Entities for the Organization
What are the important features of each Entity
What are the important Relationships
What are the important queries from the user
What are the other requirements of the Organization and the
Users
Levels of Database Design
Database development system has several phases, from those different
phases, the prime interest of a database development system will be
the Design part which is again sub divided into other three sub-phases.
These sub-phases are:-
Conceptual Database Design
It is the process of constructing a model of the information
used in an enterprise, independent of any physical
considerations.
It is the source of information for the logical design phase.
It is community User’s view
Mostly uses an Entity Relationship Model to describe the
data at this level.
After the completion of Conceptual Design one has to go for
improvement of the schema, which is verification of Entities,
Attributes, and Relationships
Logical Database Design
Logical design is the process of constructing a model of the
information used in an enterprise based on a specific data
model (e.g. relational, hierarchical or network or object), but
independent of a particular DBMS and other physical
considerations.
Normalization process
Collection of Rules to be maintained
Discover new entities in the process
Revise attributes based on the rules and the discovered
Entities
Physical Database Design
It is the process of producing a description of the
implementation of the database on secondary storage.
defines specific storage or access methods used by
database
Describes the storage structures and access methods
used to achieve efficient access to the data.
Includes estimate of storage space
3.2 Basic concepts of E-R model
Conceptual design revolves around discovering and analyzing
organizational and user data requirements
The important activities are to identify
Entities: real world physical or logical object
Attributes: properties used to describe each Entity.
Relationships: the association between Entities.
Constraints: rules that should be obeyed while manipulating the data.
And based on these components develop the ER model using ER diagrams
Entity-Relationship (ER) Model
The ER data model allows us to describe the data involved
in a real-world enterprise in terms of objects and their
relationships and is widely used to develop an initial
database design.
An Entity-Relationship (E-R) Model is a high-level data
modeling technique used in database design to define the
data elements and their relationships. The main components
of an E-R model include:
1. Entities: Objects or things in the real world that can be
distinctly identified.
Strong Entities: Exist independently and have a unique
identifier (Primary Key).
Weak Entities: Depend on a strong entity for
identification and lack a unique key.
Representation: Rectangles in an E-R diagram.
2. Attributes: Properties or characteristics of an entity or a
relationship.
Simple Attributes: Cannot be divided further (e.g.,
Name, Age).
Composite Attributes: Can be divided into smaller
sub-parts (e.g., Full Name → First Name + Last Name).
Derived Attributes: Values calculated from other
attributes (e.g., Age derived from Date of Birth).
Multi-valued Attributes: Can have multiple values
(e.g., Phone Numbers).
Representation: Ovals connected to their respective entities
or relationships.
3. Relationships: Associations between two or more entities.
Types:
One-to-One (1:1): One entity is associated with exactly one other
entity.
One-to-Many (1:N): One entity is associated with multiple entities.
Many-to-Many (M:N): Multiple entities are associated with
multiple entities.
Representation: Diamonds connecting related entities.
4. Keys
Primary Key: Unique identifier for an entity.
Foreign Key: Attribute in one entity that references the primary key of
another entity.
5. Cardinality: Describes the number of instances of one entity related
to another entity.
Types include:
One: Single instance.
Many: Multiple instances.
Represented using annotations like (1, N) near relationships.
6. Participation
Total Participation: All instances of an entity participate in the
relationship.
Partial Participation: Some instances of an entity participate in the
relationship.
7. Generalization and Specialization
Generalization: Combining two or more entity types into a single
generalized entity.
Specialization: Dividing a generalized entity into specialized entities
based on specific attributes or roles.
8. Aggregation
Definition: Represents a relationship between a relationship and an
entity.
Use: To express higher-level abstractions.
Graphical Representation in ER Diagramming
An ER schema diagram for the company database
From the above ER-model it consists 2 entities then at least it have two
tables.
Student and course
Considering the relationship between student and course as many-many
there is other table
Enrollment.
An Entity Relationship Diagram Methodology: Steps to Develop Entity Relationship Diagram (ER-D)
Identify the roles, events, locations, tangible things or concepts
1. Identify Entities
about which the end-users want to store data.
Name the information details (fields) which are essential to the
2. Identify Attributes
system under development.
Find the natural associations between pairs of entities using a
3. Find Relationships
relationship matrix.
Determine the number of occurrences of one entity for a single
4. Fill in Cardinality
occurrence of the related entity.
5. Define Primary Identify the data attribute(s) that uniquely identify one and only
Keys one occurrence of each entity.
Eliminate Many-to-Many relationships and include primary and
[Link]-Based ERD
foreign keys in each entity.
7. Draw fully ERD Adjust the full ERD.
Does the final Entity Relationship Diagram accurately depict the
8. Check Results
3.3 Enhanced E-R models
Object-oriented extensions to E-R model
EER is important when we have a relationship between two
entities and the participation is partial between entity
occurrences. In such cases EER is used to reduce the
complexity in participation and relationship complexity.
ER diagrams consider entity types to be primitive objects
EER diagrams allow refinements within the structures of
entity types
EER Concepts
• Generalization
• Specialization
• Sub classes
• Super classes
• Attribute Inheritance
• Constraints on specialization and generalization
1. Generalization
Generalization occurs when two or more entities represent
categories of the same real-world object.
Generalization is the process of defining a more general
entity type from a set of more specialized entity types.
A generalization hierarchy is a form of abstraction that
specifies that two or more entities that share common
attributes can be generalized into a higher level entity type.
Is considered as bottom-up definition of entities.
Generalization hierarchy depicts relationship between higher
level superclass and lower level subclass.
Generalization hierarchies can be nested. That is, a
subtype of one hierarchy can be a super type of another.
The level of nesting is limited only by the constraint of
simplicity.
Example: Account is a generalized form for saving and
Current Accounts
2 Specialization
Is the result of subset of a higher level entity set to form a lower level
entity set.
The specialized entities will have additional set of attributes
(distinguishing characteristics) that distinguish them from the
generalized entity.
Is considered as Top-Down definition of entities.
Specialization process is the inverse of the Generalization process.
Reasons for Specialization
Attributes only partially applying to super classes
Relationship types only partially applicable to the superclass
In many cases, an entity type has numerous sub-groupings of its entities
that are meaningful and need to be represented explicitly. This need
requires the representation of each subgroup in the ER model. The
generalized entity is a superclass and the set of specialized entities will
be subclasses for that specific Superclass.
Example: Saving Accounts and Current Accounts are Specialized entities for
the generalized entity Accounts. Manager, Sales, Secretary: are specialized
employees.
3. Subclass/Subtype
An entity type whose tuples have attributes that distinguish its
members from tuples of the generalized or Superclass entities.
When one generalized Superclass has various subgroups with
distinguishing features and these subgroups are represented by
specialized form, the groups are called subclasses.
Subclasses can be either mutually exclusive (disjoint) or overlapping
(inclusive).
A mutually exclusive category/subclass is when an entity instance can
be in only one of the subclasses.
E.g.: An EMPLOYEE can either be SALARIED or PART-TIMER but
not both.
An overlapping category/subclass is when an entity instance may be in
two or more subclasses.
E.g.: A PERSON who works for a university can be both EMPLOYEE
and a STUDENT at the same time.
4. Superclass /Super type
An entity type whose tuples share common attributes. Attributes that
are shared by all entity occurrences (including the identifier) are
associated with the super type.
Is the generalized entity
A superclass is part of a generalization hierarchy.
5. Relationship Between Superclass and Subclass
The relationship between a superclass and any of its subclasses is
called a superclass/subclass or class/subclass relationship
An instance can not only be a member of a subclass. i.e. Every
instance of a subclass is also an instance in the Superclass.
A member of a subclass is represented as a distinct database object, a
distinct record that is related via the key attribute to its super-class
entity.
An entity cannot exist in the database merely by being a member of a
subclass; it must also be a member of the super-class.
An entity occurrence of a sub class not necessarily should belong to
any of the subclasses unless there is full participation in the
specialization.
The relationship between a subclass and a Superclass is an “IS A” or
“IS PART OF” type.
Subclass IS PART OF Superclass
Manager IS AN Employee
All subclasses or specialized entity sets should be connected with the
superclass using a line to a circle where there is a subset symbol
indicating the direction of subclass/superclass relationship.