Enhanced E-R Model

EER models are useful tools for creating high-level model databases. The EER diagrams
became important because they provide all the features that are included in the ER
diagrams with the addition of the following elements:
 Specialization and Generalization
 Aggregation
 Category or Union types
 Subclass and superclass
History of EER diagram: Before working on various projects, an IBM engineer in the United
Kingdom created a new ER diagram in the 1970s. That is when the concept took root, grew,
and evolved into what is now known as the EER diagram.

Superclass and Subclass

A superclass is a high-level entity that can be further segmented into subclasses or
subsets. It is also referred to as a Parent class. A subclass can be referred to as a child or
derived class.
The link between subclasses and superclasses introduces the idea of inheritance. The 'd'
symbol is used to indicate the relationship between subclasses and superclasses.
For example: Triangles, Circles, and squares are the subclass of the Shape superclass.

Generalization and Specialization

Generalization and Specialization are common relationships added as enhancements to the
classical ER model. A subclass (specialized class) inherits from a superclass (generalized
class), similar to object-oriented concepts.
This is best understood using IS-A relationships like “Technician IS-A Employee” or “Laptop
IS-A Computer.”
Constraints- Disjointness
Disjointness Constraints Whether an instance of a supertype may simultaneously be a
member of two (or more) subtypes.
Disjoint Rule: An instance of the supertype can be only ONE of the subtypes.
Overlap Rule: An instance of the supertype could be more than one of the subtypes.
Completeness Constraints : Whether an instance of a supertype must also be a member of
at least one subtype
Total Specialization Rule: Yes (double line)
Partial Specialization Rule: No (single line)

Subtype Discriminator : An attribute of the supertype whose values determine

the target subtype(s)
Disjoint – a simple attribute with alternative values attribute with alternative
values to indicate the possible subtypes to indicate the possible subtypes
Overlapping – a composite attribute whose subparts attribute whose subparts pertain to
different subtypes. Each subpart contains a pertain to different subtypes. Each subpart
contains a boolean value to indicate whether or not the instance belongs to the associated
subtype

The Enhanced E-R Model and Business Rules

Enhanced Entity Relationship (EER) Model: The model resulting from extending the original
E-R model with new modeling constructs.
The EER model is used to capture important business rules such as constraints in
supertype/subtype relationships.
Representing Subtypes and Supertypes
Subtype: A subgroup of the entities in an entity type that is meaningful to the organization
and that shares common attributes or relationships distinct from other subgroupings.
Supertype: A generic entity type that has a relationship with one or more subtypes.
Attribute Inheritance: A property by which subtype entities inherit values of all attributes of
the supertype
Representing Specialization and Generalization
Generalization: The process of defining a more general entity types from a set of more
specialized entity types. For example, we could generalize three entity types CAR, TRUCK
and MOTORCYCLE as VEHICLE. This is a bottom-up process.
Specialization: The process of defining one or more subtypes of the supertype and forming
supertype/subtype relationships. Each subtype is formed based on some distinguishing
characteristic such as attributes or relationships specific to the subtype.
Specifying Constraints in Supertype/Subtype Relationships
Specifying Completeness Constraints
Completeness Constraint: A type of constraint that addresses the question whether an
instance of a supertype must also be a member of at least one subtype. The completeness
constraint has two possible rules: total specialization and partial specialization.
Total Specialization Rule: Specifies that each entity instance of the supertype must be a
member of some subtype in the relationship. Total specialization is indicated by double lines
extending from the supertype to circle from which single lines lead to the subtypes.
Partial Specialization Rule: Specifies that an entity instance of the supertype is allowed not
to belong to any subtype.
Specifying Disjointness Constraints
Disjointness Constraint: A constraint that addresses the question whether an instance of a
supertype may simultaneously be a member of two (or more) subtypes. The disjointness
constraint has two possible rules: the disjoint rule and the overlap rule.
Disjoint Rule: Specifies that if an entity instance (of a supertype) is a member of one
subtype, it cannot simultaneously be a member of any other subtype.
Overlap Rule: Specified that an entity instance can simultaneously be a member of two (or
more) subtypes.
Defining Subtype Discriminators
Subtype Discriminator: An attribute of the supertype whose values determine the target
subtype or subtypes. For an EMPLOYEE entity type for example, we could use a subtype
discriminator (an attribute of EMPLOYEE) called Employee_Type which is coded with one of
three values H (for Hourly), S (for Salaried), and C (for consultant).
Defining Supertype/Subtype Hierarchies
Supertype/Subtype Hierarchy: A hierarchical arrangement of supertypes and subtypes,
where each subtype has only one supertype.
Summary of Supertype/Subtype Hierarchies
1. Attributes should be assigned at the highest logical level that is possible in the
hierarchy; this assures that they can be shared by as many subtypes as possible
2. Subtypes that are lower in the hierarchy inherit the attributes not only from their
immediate supertypes, but from all supertypes higher in the hierarchy (along the
same line), up to the root.
Entity Clustering
Entity Cluster: A set of one or more entity types and associated relationships grouped into a
single abstract entity type.
Business Rules Revisited
While E-R and EER models can express structural constraints like participation
(total/partial) and disjointness (overlapping/exclusive) in supertype/subtype relationships,
many organizational rules fall outside the scope of these models.
Classification of Business Rules
We have seen various types of business rules (cardinality values, supertype/subtype
relationships, facts about attributes and entity types viz: definitions, ...)
There are three main types of business rules:
Derivation: A Derivation is a business rule that defines how to derive or calculate a value
based on other known values..
Structural Assertion: A Structural Assertion is a rule that describes the static structure of an
organization’s data. It includes definitions of entities, relationships, and attributes.
Action Assertion: An Action Assertion is a business rule that controls or restricts actions in
the system. It specifies when, how, and under what conditions certain actions can be
taken.
Structural Constraints and Operational Constraints
Structural constraints specify and determine how the entities take part in the relationships
and this gives an outline of how the interactions between the entities can be designed in a
database.
Two primary types of constraints are cardinality (specifying the number of instances in a
relationship) and participation (whether all or some entity instances are involved in the
relationship).
Cardinality is defined as one-to-one, one-to-many, or many-to-many, while participation can
be total or partial.

Operational Constraints
operational constraints refer to business rules or restrictions that control how the data can
be created, updated, or deleted.
Mainly Constraints on the relational database are of 4 types:

1. Domain Integrity: Domain integrity ensures that all values in a column are valid according
to the defined data type, format, and range.
Each attribute (column) must hold only values that belong to a specific domain (i.e., a set of
valid values).
Example:
 A column Age must contain only integers between 0 and 120.
 A column Email must follow the format [email protected].

2. Key Integrity
Key integrity ensures that each tuple (row) in a table is uniquely identifiable using a
primary key or candidate key.
No two rows can have the same value for the primary key. This prevents duplicate records.
Example:
 In a Student table, Student_ID is a primary key and must be unique and not null for
every student.
3. Entity Integrity
Entity integrity states that the primary key of a table cannot be NULL.
Each entity (row) must be uniquely identified, and a null primary key would mean that the
row cannot be uniquely identified.
Example:
 In the Employee table, Emp_ID (primary key) must have a value — it cannot be NULL.
4. Referential Integrity
Referential integrity ensures that a foreign key value in one table either matches a primary
key in another table or is NULL.
It preserves the consistency of relationships between tables.
Example:
 In an Orders table, Customer_ID is a foreign key referencing the Customers table.
Every Customer_ID in Orders must exist in Customers.

Relational Integrity Constraints

Relational Integrity Constraints
The constraints are fundamentally the set of rules used for reducing the information
that can be saved in the database. This is referred to as the relational integrity
constraints .
Key Constraint
An attribute that can uniquely recognize a row in a relation is known as the key of the table.
It helps maintain the uniqueness and validity of data.
 Every table must have a Primary Key
 The primary key uniquely identifies each row.

 It cannot be NULL .

 Ensures that no two rows have the same key value.

Domain Constraints:
A Domain Constraint is a rule that restricts the allowable values for a given attribute
(column) in a database. It ensures that data entered into a column is valid, relevant, and
within a specified range or type.
Domain Constraint controls what values can be stored in a column.
Example: Age cannot be less than 0.
Referential Integrity:
 Referential Integrity is a constraint in relational databases that ensures the validity of
relationships between tables.
It guarantees that a foreign key value in one table must match a primary key value in
another table, or be NULL.

Transforming EER Diagrams into Relations

Transforming Enhanced Entity-Relationship (EER) diagrams into relational schemas is
generally straightforward, with well-defined rules. However, it's crucial to understand the
manual process due to certain limitations of automated CASE tools.
 CASE tools struggle with complex data relationships like ternary relationships or
supertype/subtype hierarchies. Manual transformation is needed in such cases.
 Some EER scenarios have multiple valid ways to transform into relational schema. You may
need to choose the best design manually.
 You must be able to verify or validate what CASE tools produce.
 Emphasizes how conceptual models (real-world understanding) translate into database
design.
Three Step Entities
1. Regular Entities
Regular entities are entities that have an independent existence and generally
represent real-world objects, such as persons and products.
These entities are represented by rectangles with a single line.
2. Weak Entities
Weak entities are entities that cannot exist except with an identifying relationship
with an owner (regular) entity type.
These entities are identified by a rectangle with a double line.
3. Associative Entities
Associative entities are formed from many-to-many relationships between other
entity types.
These entities are represented by a rectangle with rounded corners.
Steps in EER -to-Relational Transformations Process:
Step 1. Mapping of Regular Entity Types: Create a table for each regular (strong) entity.
Include all simple attributes and mark the primary key.
EER Entity:
Student(StudentID, Name, Email, DOB)
Step 2. Mapping of Weak Entity Types
Create a table for each weak entity. Add the primary key of its owner as a foreign key and
use it (with the partial key) as the composite primary key.
Dependent(Name, Relationship, DOB) is a weak entity dependent on Student.
Step:3. Mapping of Binary
1:1 Relationships
Add the primary key of one entity as a foreign key to the other (usually the one with total
participation).
Each Student has one LibraryCard.
LibraryCard(CardID, IssueDate)
1:N Relationships
Add the primary key of the “1” side as a foreign key to the “N” side.
One Advisor (faculty) advises many Students.
M:N Relationships
Create a new relation (junction table). Include the primary keys of the participating entities
and any attributes of the relationship.
Entities: Student, Course
Relationship: Enrollment(StudentID, CourseID, Grade)
Step 4: Map Associative Entities
Associative entities represent many-to-many (M:N) relationships and are usually modeled
as separate relations with foreign keys and possibly their own attributes.
 STUDENT(StudentID, Name)
 COURSE(CourseID, Title)
 ENROLLMENT(StudentID, CourseID, Grade)

 ENROLLMENT is the associative entity.

Step 5: Map Unary (Recursive) Relationships

A unary relationship is a relationship within the same entity. It can be one-to-many or

many-to-many.

 One student can mentor many students.

 Each student can have only one mentor.
Step 6: Map Ternary (and n-ary) Relationships

Ternary relationships involve three entities. A new table is created including the primary
keys of all three entities as a composite primary key.
A student works on a project under the supervision of a faculty member.
 Student: Does the project
 Project: Assigned to a student
 Faculty: Guides the project
You cannot represent this accurately with only binary relationships — all three must be
linked together.
Step 7: Map Supertype/Subtype Relationships
When entities share common attributes, a supertype is used. Subtypes have unique
attributes. Can be total or partial, disjoint or overlapping.
PERSON as a supertype
STUDENT and TEACHER as subtypes
Create a table for the Supertype. Create separate tables for each Subtype.
Uses the same primary key as in the supertype (acts as foreign key too)

Normalization
Normalization in DBMS is a systematic process of arranging data in a relational database to
minimize redundancy and improve data integrity.
It involves decomposing large tables into smaller, related tables and defining relationships
among them using keys.
Types of Normal Forms:
First Normal Form (1NF)
First Normal Form (1NF) focuses on ensuring that the values in each column of a table are
atomic.
 All attributes (columns) contain only single, indivisible values
 Each record is unique and there are no repeating groups

StudentID Name Courses

101 Ayesha DBMS, Web Technology

StudentID Name Course

101 Ayesha DBMS

StudentID Name Course

101 Ayesha Web Technology

Second Normal Form (2NF)

it satisfies the conditions of 1NF and no partial dependency exists. A table is in 2NF if:
 It is already in 1NF
 Every non-prime attribute (an attribute that’s not part of a candidate key) is fully
functionally dependent on the entire primary key.

StudentID CourseID StudentName

101 DBMS Ayesha

StudentID StudentName

101 Ayesha

StudentID CourseID

101 DBMS

3. Third Normal Form (3NF):

 Must be in 2NF.
 No transitive dependency: Non-key attributes must not depend on other non-key
attributes.

 Move transitive dependencies to new tables.

StudentID CourseID InstructorName

101 DBMS Sharma

StudentID CourseID
101 DBMS
 CourseID InstructorName
DBMS Sharma

4. Boyce-Codd Normal Form (BCNF):

 Stronger version of 3NF.

 Every determinant must be a candidate key.

BCNF is a stricter form of 3NF that applies to tables with more than one candidate key.
BCNF requires that each non-trivial dependency in a table is a dependency on a candidate
key.

TeacherID Subject Department

T1 Math Science

Subject Department

Math Science

TeacherID Subject

T1 Math

Forth Normal Form (4NF):

A relation is in 4NF if:
 It is in BCNF
 And has no multi-valued dependencies (MVDs)
A multi-valued dependency exists when one attribute is independent of another but both
depend on the same key.

StudentID Course Language

101 DBMS Hindi

101 DBMS English

101 Web Tech Hindi

101 Web Tech English

StudentID Course

101 DBMS

101 Web Tech

StudentID Language

101 Hindi

101 English

Fifth Normal Form (5NF):

A relation is in 5NF if:
 It is in 4NF
 And cannot be further broken down into smaller relations without losing
information

StudentID StudentName Courses Marks DeptHead

201 Aamir DBMS, OS 80, 85 Dr. Rakesh

202 Priya Java, Python, DBMS 78, 82, 90 Dr. Meenakshi

203 Rohit OS, CN 76, 79 Dr. Rakesh

STEP 1: First Normal Form (1NF)

Rule: Remove repeating groups → Each cell must have atomic values.

StudentID StudentName Course Marks DeptHead

201 Aamir DBMS 80 Dr. Rakesh

201 Aamir OS 85 Dr. Rakesh

StudentID StudentName Course Marks DeptHead

202 Priya Java 78 Dr. Meenakshi

202 Priya Python 82 Dr. Meenakshi

202 Priya DBMS 90 Dr. Meenakshi

203 Rohit OS 76 Dr. Rakesh

203 Rohit CN 79 Dr. Rakesh

STEP 2: Second Normal Form (2NF)

Rule: Remove partial dependency
In 1NF, the composite key is (StudentID, Course).
Check if non-key columns depend fully on that key.
 StudentName and DeptHead depend only on StudentID, not on the whole key →
Partial dependency
So we split the table.

StudentID StudentName DeptHead

201 Aamir Dr. Rakesh

202 Priya Dr. Meenakshi

203 Rohit Dr. Rakesh

StudentID Course Marks

201 DBMS 80

201 OS 85

202 Java 78

202 Python 82

202 DBMS 90

203 OS 76
StudentID Course Marks

203 CN 79

STEP 3: Third Normal Form (3NF)

Rule: Remove transitive dependency
Check if non-key column depends on another non-key column.
In Student table:
 DeptHead depends on an implied Department (not shown), not directly on
StudentID.
So we introduce a Department table.
Table 1: Student

StudentID StudentName DeptID

201 Aamir D1

202 Priya D2

203 Rohit D1

Table 2: Department

DeptID DeptHead

D1 Dr. Rakesh

D2 Dr. Meenakshi

Table 3: StudentCourses

StudentID Course Marks

201 DBMS 80

201 OS 85

202 Java 78

202 Python 82

202 DBMS 90
StudentID Course Marks

203 OS 76

203 CN 79

Let’s assume multiple departments cannot have the same HOD, so DeptHead → DeptID.
In that case, we have:
 Two determinants: DeptID and DeptHead
 So we split into 2 tables to meet BCNF.