Chapter Three

Database Modeling

1
Fundamentals of Database Systems
 Chapter 3 - Objectives
 The Entity Relationship (ER) Model
The high-level conceptual model
Entities, Attributes, and Keys
Relationships, Associations, and Constraints
The ER Diagrams
Mapping ER-models to relational tables
 Enhanced Entity Relationship (EER) Model
 The Relational Database Model
The Relational Model Concepts
The Relational Constraints and Relational

2 Fundamentals of Database Systems

 3.1.1 The High-Level Conceptual Data Model
for Database Design
1. Requirements Collection and Analysis
 The first step in the database design process where database
designers gather data and functional requirements by
interviewing users.
 A detailed set of user data requirements. These must be
documented clearly and completely.
 Functional Requirements: These include the user-defined
operations (transactions) that will be applied to the database,
such as retrievals and updates.

3 Fundamentals of Database Systems

 3.1.1 The High-Level Conceptual Data Model
for Database Design

2. Conceptual Design
 Create a conceptual schema using a high-level data model to
represent the data requirements. This schema describes entity types,
relationships, and constraints.
 Benefits:
 Easier to communicate with non-technical users since it excludes
implementation details.
 Ensures all data requirements are met without conflicts.
 Schema Refinement: High-level queries and operations are identified
during functional analysis to verify the schema. If functional
requirements can't be met, the schema is adjusted.

4 Fundamentals of Database Systems

 3.1.2. Entities, Attributes, and Keys
Entities
 An entity is an object or thing in the real world with a distinct,
independent existence.
 It could have physical existence (e.g., person, car, house).
 It could have conceptual existence (e.g., company, job, university
course).
Attributes
 Attributes describe the properties of an entity.
 For example, an EMPLOYEE entity could have attributes like
Name, Age, Address, Salary, and Job.
 Each entity has values for its attributes that are stored in the database.

5 Fundamentals of Database Systems

 3.1.2. Entities, Attributes, and Keys
Attributes in the ER Model
Composite vs Simple (Atomic) Attributes:
 Composite Attributes: These can be divided into smaller subparts
with independent meanings.
 Example: The Address attribute can be subdivided into
Street_address, City, State, and Zip.
 A composite attribute like Street_address can further be divided
into Number, Street, and Apartment_number.
 If a composite attribute is used only as a whole, there’s no need to
divide it into components (e.g., if only the entire address is required).
 Simple (Atomic) Attributes: These cannot be subdivided.
 Example: Age or Salary are simple, indivisible attributes

6 Fundamentals of Database Systems

 3.1.2. Entities, Attributes, and Keys

Single-Valued vs Multivalued Attributes:

 Single-Valued Attributes: Each entity has only one value
for this attribute.
 Example: Age is a single-valued attribute for a person (each
person has only one age value).
 Multivalued Attributes: An entity can have multiple
values for this attribute.
 Example: A Car entity might have a Colors attribute with
multiple values (e.g., two-tone colors).
 Example: A person can have multiple College_degrees.

7 Fundamentals of Database Systems

 3.1.2. Entities, Attributes, and Keys

Stored vs Derived Attributes:

 Stored Attributes: These are explicitly stored in the
database.
 Example: Birth_date is a stored attribute for a person.
 Derived Attributes: These can be computed from other
attributes.
 Example: Age can be derived from Birth_date.
 Another example is Number_of_employees for a
Department, which can be derived by counting the employees
related to the department.
8 Fundamentals of Database Systems
 3.1.2. Entities, Attributes, and Keys

NULL Values:
 NULL: Represents missing or inapplicable values for an attribute.
 Not Applicable NULL: Used when the attribute doesn’t apply to
the entity (e.g., Apartment_number for single-family homes).
 Unknown NULL: Used when the value exists but is either unknown
or missing (e.g., unknown Home_phone number).
Complex Attributes:
 Complex attributes allow arbitrary nesting of composite and
multivalued attributes.
 Example: A Person might have multiple Phone_numbers, each
with components like Country_code, Area_code, and Number.

9 Fundamentals of Database Systems

 3.1.2. Entities, Attributes, and Keys

Entity Types and Entity Sets

 Entity Type: A collection of entities sharing the same attributes. Each entity in
the set has its own distinct values for these attributes. For instance, the
EMPLOYEE entity type describes all employee entities in a company, with each
employee having individual values for attributes such as name, age, and address.
 Entity Set: The collection of all entities of a specific type at a given point in
time. For example, the EMPLOYEE set includes all employee entities, each
with different attribute values.
 Though entity type and entity set are different concepts, they are often
referred by the same name. However, more modern models like object-
relational models often distinguish between the two.

10 Fundamentals of Database Systems

 3.1.2. Entities, Attributes, and Keys
 Entity Type Representation: In an ER diagram, an entity type is depicted as a
rectangular box enclosing the entity type's name.
 Attributes: Attributes associated with the entity are shown as ovals attached to the
entity by straight lines.
 Composite Attributes: If an attribute is composite, its sub-components are also
connected by straight lines.
 Multivalued Attributes: These are represented by double ovals.
 Entity Set and Entity Type:
 An entity type defines the schema or intension, which describes the common
structure of a group of entities.
 The actual collection of entities in an entity type is called the entity set or the
extension of the entity type.
 Example: In the CAR entity type follows this notation, with attributes connected as
described.

11 Fundamentals of Database Systems

 3.1.2. Entities, Attributes, and Keys
Representation of Entity Types
 Example: In an ER diagram, a CAR entity type might have attributes like
Vehicle_id, Model, and Registration, which would be attached to the CAR
rectangle by lines.

12 Fundamentals of Database Systems

 3.1.2. Entities, Attributes, and Keys
Key Attributes of an Entity Type
 A key attribute is an attribute whose value is unique for each entity in the
entity set, allowing it to identify entities uniquely.
 Key Constraint: The uniqueness constraint ensures that no two entities in
the entity set can have the same value for the key attribute. For example:
 In a COMPANY entity type, the Name attribute can be a key because no
two companies can share the same name.
 In a PERSON entity type, the Ssn (Social Security number) serves as a
typical key.
 Composite Key: A key can be formed by a combination of multiple attributes.
For example:
 In the CAR entity type, the Registration attribute is a composite key,
made up of State and Number attributes. Neither attribute alone is a key,
but together they form a unique identifier.
13 Fundamentals of Database Systems
 3.1.2. Entities, Attributes, and Keys
Key Attributes of an Entity Type
 Minimality of a Key: A composite key must be minimal, meaning that all component
attributes are necessary to maintain uniqueness. Superfluous attributes should not be
included.
Diagrammatic Representation:
 Key attributes are underlined in the ER diagram.
 If multiple attributes are underlined separately, each is a key attribute on its own.
 Weak Entity Types: If an entity type does not have a key attribute, it is called a weak
entity type.
 Primary Key in Relational Models: Unlike in relational models , the ER model does
not designate a primary key. The primary key will be chosen during the mapping of the
ER model to a relational schema
 Key Constraint as a Global Property: The key constraint applies to any entity set of
the entity type at all points in time. It is not tied to a particular instance but is a
fundamental constraint derived from the real-world scenario being modeled by the
database.
14 Fundamentals of Database Systems
 3.1.2. Entities, Attributes, and Keys
Example of Entity and Attribute Values:
 EMPLOYEE e1 has attributes:
 Name: John Smith
 Address: 2311 Kirby, Houston, Texas 77001
 Age: 55
 Home_phone: 713-749-2630
 COMPANY c1 has attributes:
 Name: Sunco Oil
 Headquarters: Houston
 President: John Smith
 These attributes and entities form the core data in a database, which is
described using the ER model. Different types of attributes help to model
complex real-world scenarios effectively.

15 Fundamentals of Database Systems

 Activity Time: 15 minutes
Instructions
Part 1: Brainstorming
In pairs, identify 3-4 entities and their attributes for ONE of these
scenarios:
Scenario A: University Library System
Think about what "things" need to be tracked in a library and what information each thing has.
Example to get you started:
ENTITY: BOOK
ATTRIBUTES: ISBN, Title, Author, Publication_Date, Genre, Available_Copies
Now you continue...
Scenario B: Hospital Patient Management
What entities are involved in patient care and hospital operations?
Example to get you started:
ENTITY: PATIENT
ATTRIBUTES: Patient_ID, Name, Date_of_Birth, Blood_Type, Emergency_Contact
Now you continue...
16 Fundamentals of Database Systems
 3.1.2. Entities, Attributes, and Keys

17 Fundamentals of Database Systems

 3.1.3. Relationships, Associations, and
Constraints

Relationships in the ER Model

 In the ER model, a relationship represents an association between two or
more entities. It defines how entities interact with each other.
 Representation: Relationships are typically represented as diamonds in ER
diagrams, connected by lines to the entities they relate.

 In UML (Unified Modeling Language) or more object-oriented approaches, an

association is a broader concept, often representing more abstract or complex
links between objects.
 Representation: In UML, associations are drawn as solid lines between classes
(or objects), and can have roles, multiplicities, and direction.

18 Fundamentals of Database Systems

 3.1.3. Relationships, Associations, and
Constraints
Relationships in the ER Model
1. Relationship Types:
 Defines associations between entities. For instance, a relationship type
WORKS_FOR associates each EMPLOYEE with the DEPARTMENT they
work for.
 Represented by diamonds in ER diagrams, connected by lines to the
participating entity types (rectangles).
2. Relationship Degree
 Degree: Refers to the number of entity types participating in a relationship:
 Binary: Two entities (e.g., WORKS_FOR between EMPLOYEE and
DEPARTMENT).

19 Fundamentals of Database Systems

 3.1.3. Relationships, Associations, and
Constraints
Relationships in the ER Model
2. Ternary: Three entities (e.g., SUPPLY relationship between SUPPLIER,
PART, and PROJECT).
 Higher degrees can occur, but binary relationships are the most common.

20 Fundamentals of Database Systems

 3.1.3. Relationships, Associations, and
Constraints
 Relationship Role Names:

o Each participating entity in a relationship plays a role. For example, in the

WORKS_FOR relationship, EMPLOYEE plays the role of a worker, and
DEPARTMENT plays the role of employer.

o Role names are not necessary when entities are distinct but are critical in recursive
relationships, where an entity type participates more than once in different roles.
Recursive (Self-referencing) Relationships:

 A recursive relationship involves the same entity type participating in different roles.

 Example: In an ER model, when an attribute such as Supervisor_ssn in an EMPLOYEE entity type references another instance of
the same entity (EMPLOYEE), it is called a recursive or self-referencing relationship. Here, an EMPLOYEE is related to
another EMPLOYEE, specifically in a supervisor-subordinate relationship. The SUPERVISION relationship involves the
EMPLOYEE entity playing both the supervisor and supervisee roles.

 In Figure 3, e1 supervises e2 and e3, e4 supervises e6 and e7, and e5 supervises e1 and e4. Relationships are marked as
‘1’ for supervisor and ‘2’ for supervisee.

21 Fundamentals of Database Systems

 3.1.3. Relationships, Associations, and
Constraints

Relationships as Attributes:

 A binary relationship can sometimes be viewed as an attribute. For example:

▪ The Department attribute of the EMPLOYEE entity references the

DEPARTMENT entity that an employee works for.
▪ Alternatively, the Employees attribute of the DEPARTMENT entity
references the set of employees working in that department.

 Both attributes are inverses of each other and represent the same
relationship (WORKS_FOR) from different perspectives.
22 Fundamentals of Database Systems
 3.1.3. Relationships, Associations, and
Constraints
Benefits of Modeling Relationships as Attributes
 Simplifies Data Structure: Modeling relationships as
attributes can simplify the database schema, especially in
cases where the relationship is straightforward and
doesn’t require additional attributes.
 Enhances Readability: It makes it easier to
understand the association between entities at a glance.
 Allows for Efficient Queries: Queries can be more
straightforward when the relationships are encapsulated
as attributes, making it easier to fetch related data
without needing complex joins.
23 Fundamentals of Database Systems
 3.1.3. Relationships, Associations, and
Constraints
3. Structural Constraints on Relationships
 Cardinality Ratios:
 Specifies the number of relationship instances an entity can participate in.
 Common types:
 1:1 (One-to-One): A MANAGES relationship (e.g., a DEPARTMENT can have
only one manager, and an EMPLOYEE can manage one department).

24 Fundamentals of Database Systems

 3.1.3. Relationships, Associations, and
Constraints
3. Structural Constraints on Relationships

 (One-to-Many): A WORKS_FOR relationship (e.g., a DEPARTMENT can

have many employees, but each EMPLOYEE works for only one department).

 (Many-to-Many): A WORKS_ON relationship (e.g., an EMPLOYEE can work

on multiple projects, and each PROJECT can have multiple employees).

25 Fundamentals of Database Systems

 3.1.3. Relationships, Associations, and
Constraints
Class Activity: Identify the Cardinality Ratio
 Instruction:
Decide whether each relationship is 1:1, 1:N, or M:N.
 Person – Passport
Each person has one passport, and each passport belongs to one person.
→ Cardinality: ?
 Teacher – Course
A teacher can teach several courses, but each course is taught by one teacher.
→ Cardinality: ?
 Student – Course
A student can enroll in many courses, and each course can have many students.
→ Cardinality: ?

26 Fundamentals of Database Systems

 3.1.3. Relationships, Associations, and
Constraints
Participation Constraints:
 Describes the minimum and maximum participation of entities
in a relationship:
 Total Participation: All entities in the set must participate
in the relationship (e.g., every EMPLOYEE must work for a
department).
 Partial Participation: Only some entities in the set
participate (e.g., not all EMPLOYEES manage departments).
Total participation is depicted by a double line connecting the
entity to the relationship, while partial participation uses a
single line.

27 Fundamentals of Database Systems

 3.1.3. Relationships, Associations, and
Constraints
Structural Constraints with (min, max) Notation:
 Instead of the traditional cardinality ratio and line
notation for participation constraints, a pair of integer
values (min, max) can be associated with each entity's
participation in a relationship type.
 Interpretation of (min, max):
 min: Minimum number of relationship instances an entity must
participate in.
 max: Maximum number of relationship instances an entity can
participate in.
28 Fundamentals of Database Systems
 3.1.3. Relationships, Associations, and
Constraints

 If min = 0, the entity’s participation in the relationship is

optional or partial, meaning it may or may not participate.
 If min > 0, it indicates mandatory or total participation,
meaning the entity must be involved in at least this minimum
number of relationship instances.
 If max = 1, the entity can participate in only one instance of
the relationship, representing a one-to-one constraint.
 If max > 1, the entity can participate in multiple instances of
the relationship, allowing one-to-many or many-to-many
relationships, depending on the other entity’s constraints.

29 Fundamentals of Database Systems

 3.1.3. Relationships, Associations, and
Constraints
Attributes of Relationships
 Relationships can also have attributes.
 For example, the WORKS_ON relationship between EMPLOYEE and
PROJECT could have an attribute Hours to record how many hours an
employee works on a project.
 Attributes in 1:1 or 1
Example:
 Relationship: Manages between Employee and Department
 Attribute: Start_Date (the date when an employee started managing a department)

 relationships can sometimes be moved to one of the entity types, but for M
relationships, they must remain as relationship attributes. — they depend on
both entities.
Example:
 Relationship: Works_On between Employee and Project
 Attribute: Hours (how many hours an employee works on a project)
30 Fundamentals of Database Systems
 3.1.3. Relationships, Associations, and
Constraints

Weak Entity Types and Identifying Relationships

 Weak Entity Types depend on other entities for their
existence and are identified using identifying
relationships.
•It does not have a primary key by itself.
•It has a partial key, which only becomes make sense( unique) when
combined with the key of the strong entity.

31 Fundamentals of Database Systems

 3.1.3. Relationships, Associations, and
Constraints
 INTERVIEW is a weak entity because it depends on both CANDIDATE and
COMPANY.
An interview is only meaningful if we know which candidate and which company
are involved.

32 Fundamentals of Database Systems

  The identifying relationship links a weak entity to its owner
(strong) entities.
It shows how the weak entity is identified using the parent
entities’ keys.
In the diagram:
The relationship “CCI” (Candidate–Company Interview) is the
identifying relationship.
It connects CANDIDATE and COMPANY to the weak entity
INTERVIEW.
 A partial key is an attribute of a weak entity that uniquely
identifies it only when combined with the primary key of the
related strong entity.
In the diagram:
•The partial key is Dept_date (underlined with a dashed line).
•Dept_date alone cannot identify an interview; you need both the
Candidate ID and Company ID.
33 Fundamentals of Database Systems
 The ER Diagrams

 ER Diagram Representation:
 Regular entity types (EMPLOYEE, DEPARTMENT) are in
rectangles.
 Relationship types (WORKS_FOR, MANAGES) are in
diamonds.
 Attributes are in ovals, with component attributes connected to
composite attributes.
 Weak entity types are in double rectangles, with their
identifying relationships in double diamonds.

34 Fundamentals of Database Systems

 ER Diagram Representation:

35 Fundamentals of Database Systems

 The ER Diagrams

 Naming Conventions:
 Use singular names for entity types; relationship and attribute
names are capitalized.
 Nouns in narrative descriptions guide entity names, while verbs
indicate relationship names.
 Aim for readability in relationship names to ensure diagrams
flow logically from left to right and top to bottom.
 Example Naming: The DEPENDENTS_OF
relationship could be renamed to HAS_DEPENDENTS
for clearer reading.

36 Fundamentals of Database Systems

 The ER Diagrams

Design Choices for ER Conceptual Design

 Choosing whether to model a concept as an entity type, attribute, or
relationship type can be challenging.
 The schema design process is iterative, involving the refinement of an
initial design until the most suitable configuration is reached.
Refinements in Design Choices:
•From Attribute to Relationship:
•If an attribute references another entity type, it may be refined into a
relationship. Pairs of inverse attributes can also be transformed into
binary relationships.
•When an attribute is replaced by a relationship, it should be removed
from the entity type to prevent redundancy.

37 Fundamentals of Database Systems

 The ER Diagrams

Suppose you have two entities:

[Link]
[Link]
Each Student has an attribute, advisor_id, which references the Professor entity,
indicating the professor assigned as the student's academic advisor.
Refining advisor_id into a Relationship

Transforming advisor_id into an Advises relationship with specified cardinalities provides

a more precise and flexible model.
 This relationship supports additional attributes, like advising_start_date or
advising_end_date, which help track the advising timeline.
 By defining cardinality constraints, each Student is limited to one advisor, while each
Professor can advise multiple students, and
 Referential integrity is ensured by preventing deletion of a Professor who is currently
advising students.

38 Fundamentals of Database Systems

 The ER Diagrams
 two apparently opposite ideas:
 Modeling relationships as attributes can simplify the
schema.
 Situation: Simple, direct link between two entities
(no extra info, one-to-one or one-to-many)
 Turning attributes into relationships avoids
redundancy and improves structure.
Situation: Complex association where:
– The relationship has its own attributes,
– The link can involve multiple entities, or
– It’s many-to-many (M:N)

39 Fundamentals of Database Systems

 The ER Diagrams
•Design Choices for ER Conceptual Design
•Promoting Attributes to Entity Types:
•Attributes that are shared among multiple entity types may be
promoted to an independent entity type. For instance, if Department
is an attribute for STUDENT, INSTRUCTOR, and COURSE, it may
be modeled as a separate entity type with its own attributes (e.g.,
Dept_name).
•Demoting Entity Types to Attributes:
•Conversely, if an entity type (e.g., DEPARTMENT) has only one
attribute and relates to a single other entity (e.g., STUDENT), it can
be reduced to an attribute of that entity.
•Further Considerations:
Additional refinements may include decisions about the degree of a
relationship, as well as specialization and generalization
40 Fundamentals of Database Systems
 The ER Diagrams

Representation of relationships in ER models

 Ternary relationships are represented in ER diagrams with three
edges connecting the relationship type to each participating entity
type. For example, the ternary relationship type SUPPLY connects
SUPPLIER, PART, and PROJECT.
 Example of Ternary Relationship:
 The relationship set for SUPPLY consists of instances like (s, j, p),
where s is a SUPPLIER supplying a PART p to a PROJECT j.

41 Fundamentals of Database Systems

 The ER Diagrams

 Binary Relationships:
 Three binary relationships—CAN_SUPPLY, USES, and
SUPPLIES—can be illustrated instead of a ternary
relationship.
 CAN_SUPPLY: Connects SUPPLIER and PART,
indicating when a supplier can provide a part (regardless
of the project).
 USES: Connects PROJECT and PART, indicating when
a project uses a particular part.
 SUPPLIES: Connects SUPPLIER and PROJECT,
indicating when a supplier supplies a part to a project.
42 Fundamentals of Database Systems
 The ER Diagrams

 Differences in Representation:
 The existence of instances in the three binary relationships
does not guarantee that an instance exists in the ternary
relationship SUPPLY. The meaning of each relationship
type varies:
 A ternary relationship captures a specific interaction
involving all three entity types simultaneously, while
binary relationships may indicate separate interactions
without the same interdependency.

43 Fundamentals of Database Systems

 The ER Diagrams

Difficulty in Representing Specific Constraints

 Each of the binary relationships (CAN_SUPPLY, USES,
SUPPLIES) has distinct constraints:
 CAN_SUPPLY: Which suppliers can provide which parts.
 USES: Which parts are required for specific projects.
 SUPPLIES: Which suppliers are providing parts to specific projects.
 In a ternary relationship, it’s challenging to apply constraints that
are specific to each pair within the three entities. For instance, a
ternary relationship can’t easily enforce that a SUPPLIER can
supply a PART in general without also requiring a PROJECT to
be involved.

44 Fundamentals of Database Systems

 The ER Diagrams
Attribute Specificity
•Binary relationships allow attributes to be associated with each relationship independently,
which can be useful for tracking different types of information:
 For CAN_SUPPLY (SUPPLIER-PART), you might want to include attributes
like minimum_order_quantity or standard_delivery_time.
 For USES (PROJECT-PART), you might want to specify quantity_needed or
expected_delivery_date.
 For SUPPLIES (SUPPLIER-PROJECT), you could track contract_start_date or
contract_end_date.
•A ternary relationship could have attributes too, but they would apply only when all three
entities are linked together. It would be difficult to track information relevant only to
certain pairs within the ternary relationship without involving all three entities
simultaneously.

45 Fundamentals of Database Systems

 The ER Diagrams

46 Fundamentals of Database Systems

 The ER Diagrams

 Considerations for Choosing Relationship Types:

When deciding whether to represent a relationship as a higher-degree
relationship or as binary relationships, consider:
 The nature of the relationship: Does it inherently involve all
participating entities in a way that cannot be captured by
separate binary relationships?
 The specific information and constraints that need to be
represented.
 The implications for database design, such as complexity
and potential for data redundancy.

47 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
 The mapping procedure aims to create tables with simple, single-valued
attributes while adhering to relational model constraints, including
primary keys, unique keys, and referential integrity.
Step 1: Mapping Regular Entity Types
 For each regular (strong) entity type E in the ER schema:
 Create a relation R with all simple attributes of E, excluding
composite attribute components.
 Select one of the key attributes of E as the primary key for R. If the
key is composite, the simple attributes forming it become the primary
key.
 Store additional key information for future use (e.g., indexing).

48 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
Example Explanation
 Let’s use a practical example to clarify:
 Suppose we have a Person entity with a composite attribute Name. The
Name attribute might be composed of sub-attributes:
 First Name
 Middle Name
 Last Name
 In the ER model, Name is treated as a single, composite attribute for
ease of conceptual understanding. However, when converting this into a
relational model (i.e., creating a table for Person), storing Name as a
single column could be limiting and less precise. Therefore, instead of
having a single column called Name, we would:

49 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
 Break Name into its simple components and create separate
columns: First_Name, Middle_Name, and Last_Name.
 This approach makes the data easier to work with, more flexible for
querying (e.g., searching by Last Name), and aligns with relational
database principles, which prefer data to be stored in atomic form.

only the simple components of composite attributes should be

included when creating relational tables.
This means that instead of storing a composite attribute as a single
field, we store each sub-attribute separately to make the data atomic,
clear, and more useful in a relational database context.

50 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
Example:
 Relations created: EMPLOYEE, DEPARTMENT, PROJECT.
 Primary keys chosen: Ssn for EMPLOYEE, Dnumber for
DEPARTMENT, Pnumber for PROJECT.
Step 2: Mapping Weak Entity Types
 For each weak entity type W with an owner entity type E:
 Create a relation R including all simple attributes of W and
the primary key(s) of E as foreign key attributes.
 The primary key of R combines the owner’s primary key(s)
and W’s partial key (if any).

51 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
Step-by-Step Explanation of Mapping Weak Entity Types
 Identify Weak Entity Type:
 A weak entity type is an entity that cannot be uniquely identified by its own
attributes alone and relies on a related (or "owner") entity type for identification.
 For a weak entity type W, there is always an associated owner entity type E that
provides part of the unique identification for each instance of W.
 Create a Relation for the Weak Entity:
 Create a relation (table) R for the weak entity type W.
 Include all simple attributes of W as columns in this relation.
 Add Foreign Key to Link with the Owner Entity:
 Add the primary key of the owner entity type E as a foreign key in R. This
establishes a link between each instance of the weak entity W and its owner entity
E.
 Define the Primary Key of the Weak Entity’s Relation:
 The primary key of R is a combination of the owner’s primary key (from E) and
the partial key of W (if any).
52 Fundamentals of Database Systems
Mapping ER-models to Relational Tables
Example Scenario
Owner Entity Type (E): Course
 Attributes: CourseID (Primary Key), CourseName
Weak Entity Type (W): Enrollment
 Attributes: StudentID (Partial Key), EnrollmentDate
 Note: Enrollment cannot be uniquely identified without referencing
the Course it belongs to.
Step-by-Step Mapping
1: Identify the Weak Entity Type
Weak Entity Type (W): Enrollment
Owner Entity Type (E): Course
 Reason: Enrollment cannot be uniquely identified by StudentID alone
since multiple students can enroll in the same course.

53 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
2: Create a Relation for the Weak Entity
 Create a relation (table) for the weak entity type Enrollment. This table will include
all its attributes:
Table Name: Enrollment
 Columns:
 StudentID (Partial Key)
 EnrollmentDate
3: Add Foreign Key to Link with the Owner Entity
 Add the primary key of the owner entity type Course as a foreign key in the
Enrollment table. This establishes the relationship between the Enrollment and
Course tables.
 Foreign Key: CourseID (from the Course table)
4: Define the Primary Key of the Weak Entity’s Relation
 The primary key of the Enrollment table will be a combination of the foreign key
from the Course entity and the partial key from the Enrollment entity. Thus:
 Primary Key: (CourseID, StudentID)
54 Fundamentals of Database Systems
Mapping ER-models to Relational Tables
Course Table

CourseID (PK) CourseName

C001 Database Systems
C002 Web Development

Enrollment Table

CourseID (FK) StudentID (PK) EnrollmentDate

C001 S001 2024-09-01
C001 S002 2024-09-02
C002 S001 2024-09-01

This mapping allows for a clear relationship between the weak entity and its owner, ensuring that
each enrollment is associated with a specific course.

55 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
Class Activity: How do you map the following strong and weak entity
type:

56 Fundamentals of Database Systems

Mapping ER-models to Relational Tables

Step 3: Mapping Binary 1:1 Relationship Types

 Identify relations S and T corresponding to the entity types in
the 1:1 relationship R.
 Approaches:
 Foreign Key Approach: Include a foreign key in one relation
(preferably the one with total participation).
 Merged Relation Approach: Merge both entities into a single relation
if both participations are total.
 Cross-Reference Relation Approach: Create a separate relation to
cross-reference primary keys of S and T.

57 Fundamentals of Database Systems

Mapping ER-models to Relational Tables

Example Scenario
 Entity Type S: Person
 Attributes: PersonID (Primary Key), Name
 Entity Type T: Passport
 Attributes: PassportID (Primary Key), IssueDate
 Relationship R: Each person can have only one
passport, and each passport belongs to only one
person. This is a 1:1 relationship.

58 Fundamentals of Database Systems

Mapping ER-models to Relational Tables

1: Identify Relations
 Person Relation (S)
 Columns: PersonID (PK), Name
 Passport Relation (T)
 Columns: PassportID (PK), IssueDate
2: Mapping Approaches
 Approach 1: Foreign Key Approach
 In this approach, we include a foreign key in one of the relations.
We will add a foreign key to the Passport relation since a
passport has total participation in the relationship (every passport
must belong to a person).
59 Fundamentals of Database Systems
Mapping ER-models to Relational Tables
Person Table (S)

PersonID (PK) Name

S001 Alice
S002 Bob

Passport Table (T) with Foreign Key

PassportID (PK) IssueDate PersonID (FK)

P001 2024-01-01 S001
P002 2024-02-01 S002

60 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
Approach 2: Merged Relation Approach
 If both entities have total participation in the relationship, we can merge
them into a single relation. In this case, we combine Person and
Passport into one table.

Merged Table
PersonID PassportID
Name IssueDate
(PK) (PK)
S001 Alice P001 2024-01-01
S002 Bob P002 2024-02-01

61 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
Approach 3: Cross-Reference Relation Approach
In this approach, we create a separate relation to cross-reference the
primary keys of both relations. This is useful when the entities can
exist independently, but we still want to maintain the relationship.

Cross-Reference Table

PersonID (PK, FK) PassportID (PK, FK)

S001 P001
S002 P002

62 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
Person Table (S)
PersonID (PK, FK) Name
S001 Alice
S002 Bob

Passport Table (T)

PassportID (PK) IssueDate

P001 2024-01-01
P002 2024-02-01

63 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
Example:
 The MANAGES relationship maps to DEPARTMENT, with the EMPLOYEE primary
key as a foreign key.
Step 4: Mapping Binary 1 Relationship Types
 Approaches:
 Foreign Key Approach: Include the primary key of the 1-side as a
foreign key in the N-side relation.
 Cross-Reference Relation Approach: Create a separate relation for the
relationship, but it's generally less preferred.
Example:
 The WORKS_FOR relationship includes the primary key
Dnumber of DEPARTMENT as a foreign key in EMPLOYEE.
64 Fundamentals of Database Systems
Mapping ER-models to Relational Tables
Example Scenario
 Entity Type D: Department
 Attributes: Dnumber (Primary Key), Dname
 Entity Type E: Employee
 Attributes: EmpID (Primary Key), EmpName
 Relationship R: WORKS_FOR
 A department can have many employees, but each employee works for
only one department.
1: Identify Relations
 Department Relation (D)
 Columns: Dnumber (PK), Dname
 Employee Relation (E)
 Columns: EmpID (PK), EmpName
65 Fundamentals of Database Systems
Mapping ER-models to Relational Tables
2: Mapping Approaches
Approach 1: Foreign Key Approach
 In this approach, we include the primary key of the 1-side
(Department) as a foreign key in the N-side (Employee) relation.
Department Table (D)
Dnumber (PK) Dname
D001 Human Resources
D002 IT
D003 Marketing

66 Fundamentals of Database Systems

Mapping ER-models to Relational Tables

Employee Table (E) with Foreign Key

EmpID (PK) EmpName Dnumber (FK)

E001 Alice Smith D001
E002 Bob Johnson D001
E003 Carol Williams D002
E004 David Brown D003
E005 Eva White D002

67 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
Approach 2: Cross-Reference Relation Approach
 In this approach, we create a separate relation to represent the
relationship itself. However, this approach is generally less preferred for 1
 relationships, as it can complicate the design without significant benefit.
Cross-Reference Table (WORKS_FOR)
EmpID (PK, FK) Dnumber (PK, FK)
E001 D001
E002 D001
E003 D002
E004 D003
E005 D002

68 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
Approach 2: Cross-Reference Relation Approach
Department Table (D)
Dnumber (PK) Dname
D001 Human Resources
D002 IT
D003 Marketing

Employee Table (E)

EmpID (PK) EmpName

E001 Alice Smith
E002 Bob Johnson
E003 Carol Williams
E004 David Brown
E005 Eva White
69 Fundamentals of Database Systems
Mapping ER-models to Relational Tables

Step 5: Mapping Binary M Relationship Types

 Create a new relation S to represent R with foreign keys for
both participating entity types’ primary keys.
 Include any simple attributes of the M relationship type.
Example:
 The WORKS_ON relationship results in a relation that
includes foreign keys for both EMPLOYEE and PROJECT,
with a composite primary key of those foreign keys.

70 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
Table Structure
[Link] Table:
•Attributes: Emp_ID, Name, Position
•Primary Key: Emp_ID
[Link] Table:
•Attributes: Proj_ID, Proj_Name, Location
•Primary Key: Proj_ID
3.WORKS_ON Table (join table for the M
relationship):
•Attributes: Emp_ID (foreign key referencing EMPLOYEE), Proj_ID
(foreign key referencing PROJECT), Hours
•Primary Key: Combination of Emp_ID and Proj_ID

71 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
EMPLOYEE Table
Emp_ID Name Position
1001 John Smith Manager
1002 Alice Brown Engineer
1003 Bob White Technician

PROJECT Table
Proj_ID Name Location
2001 Project Alpha New York
2002 Project Beta Chicago
2003 Project Gamma San Francisco

72 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
WORKS_ON Table

Emp_ID Proj_ID Hours

1001 2001 20
1002 2001 15
1001 2002 25
1003 2003 30
1002 2003 20

73 Fundamentals of Database Systems

Mapping ER-models to Relational Tables

Step 6: Mapping Multivalued Attributes

 For each multivalued attribute A:
 Create a new relation R with an attribute for A and the primary key of
the entity or relationship type that has A.
 The primary key of R is a combination of A and the primary key.
 Example:
 The DEPT_LOCATIONS relation includes Dlocation
(multivalued attribute) and the primary key of DEPARTMENT.

74 Fundamentals of Database Systems

Mapping ER-models to Relational Tables

Table Structure
[Link] Table:
•Attributes: Dnumber, Dname
•Primary Key: Dnumber
2.DEPT_LOCATIONS Table:
•Attributes: Dnumber (foreign key referencing
DEPARTMENT), Dlocation
•Primary Key: Combination of Dnumber and Dlocation

75 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
DEPARTMENT Table:
Dnumber Dname
1 Sales
2 Research
3 Accounting

DEPT_LOCATIONS Table:
Dnumber Dlocation
1 New York
1 Boston
2 Los Angeles
2 San Francisco
3 Chicago
76 Fundamentals of Database Systems
Mapping ER-models to Relational Tables
Step 7: Mapping N-ary Relationship Types
 For each n-ary relationship R (where n>2n > 2n>2):
 Create a new relation SSS with foreign keys from all participating
entity types.
 The primary key of SSS is usually a combination of all foreign keys, but
it may exclude keys from entities with cardinality constraints of 1.
Example:
 The ternary relationship SUPPLY involves creating a relation
with foreign keys for SUPPLIER, PART, and PROJECT, with a
primary key combining these keys.

77 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
Table Structure
[Link] Table:
•Attributes: Supplier_ID, Supplier_Name
•Primary Key: Supplier_ID
[Link] Table:
•Attributes: Part_ID, Part_Name
•Primary Key: Part_ID
[Link] Table:
•Attributes: Project_ID, Project_Name
•Primary Key: Project_ID
[Link] Table (ternary relationship):
•Attributes: Supplier_ID (foreign key referencing SUPPLIER),
Part_ID (foreign key referencing PART),
Project_ID (foreign key referencing PROJECT), Quantity (or any

78 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
SUPPLIER Table
Supplier_ID Supplier_Name
101 Supply A
102 Supply B
103 Supply C

PART Table:
Part_ID Poject_Name
201 Part X
202 Part Y
203 Part Z

79 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
PROJECT Table
Project_ID Project_Name
301 Project Alpha
302 Project Beta
303 Project Gamma

Supply Table:
Supplier_ID Part_ID Project_ID Quantity
101 201 301 50
102 202 302 30
103 203 303 20

80 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
ER Conceptual Schema Diagram for CAMPANY Database

81 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
Result of mapping Company ER schema into relational
database Schema

82 Fundamentals of Database Systems

Mapping ER-models to Relational Tables
Some other Mappings

83 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model

Subclasses, Superclasses, and Inheritance in EER Models

Key Concepts:
 Superclass and Subclass:
 A superclass (or supertype) represents a general entity type, encompassing a broad
category of entities.
 A subclass (or subtype) is a more specific grouping of entities within the superclass
that share common attributes or relationships.
 Specialization and Generalization:
 Specialization is the process of defining one or more subtypes of the superclass and
forming new subclasses.
 Generalization is the reverse process, where commonalities among various subclasses
are abstracted into a superclass.

84 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
 Category/Union Type:
 represents a group of different entity types that need to be treated
together as a single group for certain purposes.
Example: We might have separate entity types for "Doctor," "Nurse,"
and "Technician." Each of these entity types has specific attributes unique
to their roles.
 However, we might want to create a union type called "Medical Staff,"
which allows us to group doctors, nurses, and technicians together
under a single category.
 This union type would enable queries or relationships (e.g.,
assignments to departments, scheduling shifts) that apply to all medical
staff, regardless of their specific role.
 This way, operations relevant to the general group (like hospital
resource management) can be handled more efficiently.
85 Fundamentals of Database Systems
Enhanced Entity Relationship (EER) Model
 Attribute and Relationship Inheritance:
 Entities in a subclass inherit attributes and relationships from the
superclass. For instance, an ENGINEER who is also a
SALARIED_EMPLOYEE inherits characteristics from both
subclasses.
 An entity in a subclass possesses values for its specific attributes while
also inheriting those of its superclass.
 EER Diagrams:
 The class/subclass relationship is crucial, as it indicates that a subclass
entity is a member of both the subclass and superclass.
 EER diagrams use specific notations (such as circle notation) to depict
these relationships clearly.

86 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model

 Class/Subclass Relationships:
 The relationship between a superclass and its subclasses is termed a
superclass/subclass or supertype/subtype relationship.
 For example, entities like SECRETARY, ENGINEER, and MANAGER can
be subclasses of the EMPLOYEE superclass.
 Database Representation:
 In database systems, subclass members can be represented as distinct records
linked to their superclass entities through key attributes.
 A subclass entity cannot exist independently; it must always be associated
with its superclass.

87 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
EER Diagram for Subclass and Specialization

88 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
Inheritance
Specialization and Generalization
 Specialization
Specialization is the process of creating a set of subclasses from a given
entity type, known as the superclass. Subclasses are formed based on
distinguishing characteristics of the entities within the superclass.
EER Diagram Representation:
 In an EER diagram, subclasses are linked to a circle representing the
specialization, which is itself connected to the superclass.
 Lines from the subclass to the specialization circle indicate the
superclass/subclass relationship, often denoted by a subset symbol.

89 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model

Superclass: EMPLOYEE
 Attributes: Fname, Minit, Lname, Name, Ssn, Birth_date, Address
The Employee entity serves as the superclass,
 holding attributes common to all types of employees, like
name, social security number, birth date, and address.
 The subclasses inherit these attributes from
Employee but add their own specific attributes or relationships.

90 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
{SECRETARY, TECHNICIAN, ENGINEER} Specialization
 This represents a disjoint specialization (denoted by the "d"
symbol), meaning each employee can be one of these types but not more
than one at the same time.
Secretary: Has an additional attribute Typing_speed, specific to the role
of a secretary.
Technician: Has an additional attribute Tgrade, likely representing a
grade or level of technical expertise.
Engineer: Has an additional attribute Eng_type, specifying the type of
engineering specialization (e.g., software, electrical, mechanical).
 This specialization is a disjoint set, so an Employee can be a
Secretary, Technician, or Engineer, but not more than one at the
same time.

91 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
{MANAGER} Specialization
Manager is also a disjoint specialization of Employee. Unlike the previous group,
Manager appears as a separate specialization on its own. Manager has a relationship
named MANAGES, which connects the Manager entity to the Project entity type,
meaning a manager is responsible for managing one or more projects.
 This specialization implies that some employees may have managerial roles, distinguishing
them by their unique responsibility for projects.
{SALARIED_EMPLOYEE, HOURLY_EMPLOYEE} Specialization
This specialization distinguishes Employee by type of employment, based on salary
structure.
 Salaried_Employee: Has an additional attribute Salary, representing fixed
compensation on a periodic basis.
 Hourly_Employee: Has an additional attribute Pay_scale, indicating the pay rate per
hour.
This is a disjoint specialization, meaning an employee can either be a Salaried_Employee
or an Hourly_Employee but not both simultaneously.
92 Fundamentals of Database Systems
Enhanced Entity Relationship (EER) Model
Union Type: BELONGS_TO with TRADE_UNION
 The Hourly_Employee subclass has a BELONGS_TO relationship
with Trade_Union.
 This relationship signifies that an hourly employee may be part of a
Trade_Union.
 This union type relationship allows certain categories of employees
(like hourly employees) to be associated with external entities, such as
labor unions, reflecting organizational membership that may apply only to
specific types of employees.

93 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
Instance Representation:
 Instances of subclasses represent the same real-world entities
as those in the superclass. For instance, an employee
designated as both EMPLOYEE and SECRETARY indicates
that the same entity holds roles in both classes.
 This superclass/subclass relationship resembles a 1:1
relationship at the instance level, where the entity in the
subclass is seen as the same entity in the superclass, fulfilling a
specialized role (e.g., an EMPLOYEE who is specifically a
SECRETARY).

94 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
Specialization Instance

95 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model

Reasons for Specialization:

 Attribute Relevance: Some attributes apply only to
specific subclasses, enhancing data organization and
integrity.
 Role Distinction: It allows the same entity to be
understood in different contexts, providing clarity and
structure to the database design.

96 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model

Subclasses
 Definition: A subclass is a grouping of entities that share
specific attributes from a superclass. While subclass members
may have unique attributes, they often share many attributes
with the superclass.
 Example: In the EMPLOYEE superclass:
 SECRETARY subclass has a specific attribute Typing_speed.
 ENGINEER subclass has a specific attribute Eng_type.
 Both subclasses inherit shared attributes from the EMPLOYEE
superclass.

97 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
 Predicate-defined Subclasses: These subclasses are determined by specific
conditions on attributes of the superclass.
 Predicate-defined subclasses, also called "condition-defined" subclasses, are
subclasses formed by applying specific conditions (predicates) on the attributes
of a superclass. In these subclasses, only those entities from the superclass that
meet the conditions are included in the subclass.

For example, if the EMPLOYEE type has an attribute Job_type, a condition

such as
 Job_type = 'Secretary' can define membership in the SECRETARY
subclass.
 This condition acts as a constraint, specifying which entities qualify as
members of that subclass.

98 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model

Reasons for Using Subclasses

 Specific Attributes: Subclasses can contain attributes that apply
only to them, providing more granularity (level of detail in
data representation) in data representation.
 Relationship Participation: Certain relationships may only
involve entities from the subclass.
 For instance, if only HOURLY_EMPLOYEES can belong to a trade
union, this can be represented by connecting the
HOURLY_EMPLOYEE subclass to a TRADE_UNION entity type
through the BELONGS_TO relationship.

99 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
Generalization
 Definition: Generalization is the process of abstracting
common features from several entity types to form a single
superclass. The original entity types become subclasses of this
generalized entity.
 Example: The entity types CAR and TRUCK can be
generalized into the entity type VEHICLE. Both CAR and
TRUCK become subclasses of VEHICLE.
 Inverse Process: Generalization is functionally the inverse of
specialization, where the latter focuses on distinguishing
features, while the former focuses on commonalities.
100 Fundamentals of Database Systems
Enhanced Entity Relationship (EER) Model

Diagrammatic Notation
 In some methodologies, arrows may be used to differentiate
between specialization (pointing to subclasses) and generalization
(pointing to the superclass). However, this notation is not
universally adopted due to its subjective nature.
Constraints and Characteristics of Specialization and
Generalization
 Specialization and Generalization Constraints: Several
specializations may be defined on the same entity type, allowing
entities to belong to multiple subclasses. A specialization may
consist of a single subclass without the need for circle notations.
101 Fundamentals of Database Systems
Enhanced Entity Relationship (EER) Model
Subclasses, Specialization, and Generalization: Additional
Concepts
User-defined Subclasses
 A user-defined subclass does not have a specific condition for
determining membership. Instead, membership is determined by users
when they add entities to the subclass.
 Membership Specification: Users specify membership for each entity
individually, rather than relying on automatic evaluation based on
conditions.
Disjointness Constraint
 The disjointness constraint specifies that subclasses within a
specialization must be disjoint sets. This means an entity can belong to at
most one subclass.

102 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
 there's no such inherent rule or attribute condition that determines
membership. Instead, each entity is evaluated on a case-by-case basis by the
user, who decides whether it should belong to that subclass.
Example
 Consider an Employee entity in a company’s [Link] might have a user-
defined subclass called "ExecutiveTeam". Not all employees automatically
qualify to be part of this subclass; instead, employees are manually added
based on decisions by the management.
 Employee: The superclass representing all employees in the organization.
 ExecutiveTeam: A user-defined subclass with no specific attributes or
conditions that automatically include employees. Members of this subclass are
chosen by management.
Disjointness Constraint
 The disjointness constraint specifies that subclasses within a specialization must
be disjoint sets. This means an entity can belong to at most one subclass.
103 Fundamentals of Database Systems
Enhanced Entity Relationship (EER) Model
Example:
Consider a superclass called Employee with two disjoint
subclasses:
•FullTimeEmployee
•PartTimeEmployee
With a disjointness constraint, an Employee entity can only be
classified as either a FullTimeEmployee or a PartTimeEmployee
but not both. If someone is classified as a FullTimeEmployee,
they cannot simultaneously be a PartTimeEmployee, and vice
versa.
This constraint prevents any overlap between these subclasses,
ensuring clear distinctions between the roles.

104 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
Attribute-defined Specialization: If a specialization is
defined by an attribute, it implies the disjointness constraint,
especially if the attribute used for defining membership is single-
valued.
 Since a single-valued attribute limits each entity to only one
possible value, the entity can only satisfy the membership
condition of one subclass, ensuring that it can’t belong to
multiple subclasses simultaneously.
 Illustration: In an EER diagram, disjoint subclasses may be
represented with a “d” notation in the circle

105 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
Specialization Types
 Total vs. Partial Specialization:
 Total Specialization: Every entity in the superclass must belong to at least
one subclass. Represented by a double line connecting the superclass to
the specialization circle.
 Example: If every EMPLOYEE must be either an
HOURLY_EMPLOYEE or a SALARIED_EMPLOYEE, this
represents a total specialization.
 Partial Specialization: Entities in the superclass are not required to
belong to any subclass. Represented by a single line connecting the
superclass to the specialization circle.
 Example: Some EMPLOYEE entities may not belong to any of the
subclasses {SECRETARY, ENGINEER, TECHNICIAN}.

106 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
Specialization Constraints
 Combinations of Constraints:
 Disjoint, total
 Disjoint, partial
 Overlapping, total
 Overlapping, partial
 The appropriate constraint type depends on the real-
world meanings that apply to each specialization

107 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model

Union
 Union Type (Category): Represents a collection of
entities from different entity types.
 Example: The OWNER category may consist of
entities from the PERSON, BANK, and COMPANY
types.
 Representation in EER Diagrams: Superclasses
connected to the union type circle with the ∪ symbol,
indicating the set union operation.
108 Fundamentals of Database Systems
Enhanced Entity Relationship (EER) Model

 Total vs. Partial Categories:

 A total category includes all entities from its
superclasses (double line).
 A partial category includes only a subset (single
line).
 Key Attributes: Superclasses may have different or
same key attributes, influencing how the category is
represented.

109 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
Aggregation and Association
 Aggregation:
 Definition: An abstraction concept used to build composite objects from their
component objects.
 An aggregation relationship is a special type of association in entity-
relationship (ER) and enhanced entity-relationship (EER) models that represents a
whole-part relationship between two entities.
 An ER diagram is not capable of representing the relationship between an entity
and a relationship which may be required in some scenarios.
 In those cases, a relationship with its corresponding entities is aggregated into a
higher-level entity. Aggregation is an abstraction through which we can represent
relationships as higher-level entity sets

110 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
 For Example, an Employee working on a project may require some machinery.
So, REQUIRE relationship is needed between the relationship WORKS_FOR
and entity MACHINERY. Using aggregation, WORKS_FOR relationship with its
entities EMPLOYEE and PROJECT is aggregated into a single entity and
relationship REQUIRE is created between the aggregated entity and
MACHINERY.

111 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
Aggregation and Association

 In an Enhanced Entity-Relationship (EER) model, aggregation is typically depicted using

a diamond symbol with a double outline around the relationship that aggregates
multiple entities or relationships to represent a higher-level, "whole" concept.
 This type of representation is especially useful for modeling complex relationships
where entities or relationships combine to form a composite structure.
•Aggregated Entity or Relationship: The entities or relationships that contribute to
the whole are connected to a central aggregation diamond symbol.
•Double Outlined Diamond: A double diamond is used to indicate an aggregation
relationship, setting it apart from standard relationships in ER diagrams.
•Attributes Representing the Whole: The attributes that define the whole object or
concept are often shown attached to the diamond to reflect the aggregated entity as a
single unit.

112 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model

 Aggregation and Association

 Types of Aggregation in EER:
1. Aggregating Attribute Values: Combining attributes
of an object to form the whole object. This form of
aggregation combines individual attribute values of an
object to represent the object as a whole.
 For example, a "Student" entity may aggregate attributes
like "Name," "ID," and "Date of Birth" to form a
complete representation of the student.

113 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model

2. Representing Aggregation as Ordinary Relationships:

Viewing aggregation relationships in the same way as
relationships between entities.
 This method allows the relationship between the parts
(component entities) and the whole (composite entity) to
be visually represented, making it easier to understand and
manipulate.

114 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
Example
 Consider a situation where a Car entity is related to a Wheel entity. Here:
 Car represents a composite object, which is made up of Wheels.
 Wheel is a component of Car.
 In a traditional ER model, we might show the Car and Wheel entities with a
relationship like "has" or "composed of." This aggregation relationship shows
that a Car is composed of Wheels, where the Car entity is the "whole" and Wheel
is the "part."
 By representing this as an ordinary relationship, we depict the connection
between Car and Wheel without adding any special notation for aggregation. In
this setup:
 We show Car and Wheel as separate entities. We create a standard relationship,
labeled "has" or "contains," between Car and Wheel.

115 Fundamentals of Database Systems

Enhanced Entity Relationship (EER) Model
3. Combining Objects into Higher-Level
Aggregate Objects: Although not directly supported
in standard EER models, this type of aggregation
involves combining multiple objects or entities into
a higher-level composite.

 This approach is beneficial for organizing complex

systems into hierarchies, though it requires
extensions or additional modeling techniques to
fully implement in EER.
116 Fundamentals of Database Systems
Enhanced Entity Relationship (EER) Model
Example: University Department Structure
 Consider the following scenario within a university's structure:
 Entities:
 Department: Represents a department within the university, such as "Computer
Science" or "Mathematics."
 Professor: Represents a faculty member working in a department.
 Course: Represents a course offered by the department.
 Student: Represents students enrolled in courses offered by the department.
 Higher-Level Aggregate Object:
 We can combine these entities (Department, Professor, Course, and Student) into a
higher-level aggregate object called University Department.
 Aggregation Relationships:
 The University Department aggregate object can "contain" multiple Professors,
Courses, and Students.
 Each of these entities retains its own attributes (e.g., Professor has attributes like
Name and Rank, Course has CourseID and Credits, etc.), but they are logically grouped
under the University Department.
Fundamentals of Database Systems
117
Enhanced Entity Relationship (EER) Model
 Relationship Types:
 The relationship between primitive objects and their
aggregate is called IS-A-PART-OF, while the inverse is IS-
A-COMPONENT-OF.
 Example: In an ER schema for job interviews:
 The COMPANY class aggregates attributes like Cname
(company name) and Caddress (company address).
 The JOB_APPLICANT class aggregates attributes like
Ssn, Name, Address, and Phone.
 INTERVIEW may be treated as a class to associate with
JOB_OFFER.
118 Fundamentals of Database Systems
Enhanced Entity Relationship (EER) Model
 Association:
 Definition: Used to connect objects from several independent classes,
similar to the second use of aggregation.
 Representation: In the EER model, associations are represented by
relationship types, and in UML, by associations.
 Relationship Type: This abstract relationship is called IS-
ASSOCIATED-WITH.
 Key Distinctions:
 Aggregation vs. Association:
 In aggregation, deleting the aggregate object (e.g., CAR) results in the deletion
of its component objects (ENGINE, CHASSIS, TIRES).
 In association, when an instance of the association is deleted, the participating
objects may continue to exist.

119 Fundamentals of Database Systems

Mapping EER model to relational model
Mapping Specialization or Generalization
 The mapping of EER constructs to relations involves several
options for handling subclasses in specialization and
generalization. This extends the ER-to-relational mapping
algorithm and includes:
Step 8: Options for Mapping Specialization or Generalization
 Option 8A: Multiple Relations (Superclass and Subclasses):
 Create a relation for the superclass with its attributes.
 Create a relation for each subclass that includes the superclass key.
 Works for any specialization constraints (total/partial,
disjoint/overlapping).

120 Fundamentals of Database Systems

Mapping EER model to relational model

 Option 8B: Multiple Relations (Subclass Relations Only):

 Create a relation for each subclass with the superclass attributes.
 Only applicable for total specializations with disjoint subclasses.
 Option 8C: Single Relation with One Type Attribute:
 Create a single relation for the superclass and all subclasses.
 Includes a type attribute to indicate subclass membership.
 Works only for disjoint subclasses.
 Option 8D: Single Relation with Multiple Type Attributes:
 Similar to option 8C, but uses Boolean attributes for each subclass.
 Applicable for overlapping subclasses.

121 Fundamentals of Database Systems

Mapping EER model to relational model

 Options for Mapping Specializations or Generalization

122 Fundamentals of Database Systems

Mapping EER model to relational model

Mapping the EER specialization lattice

123 Fundamentals of Database Systems

 The Relational Database Model
 The Relational Database Model is a foundational concept in
database management systems that organizes data into structured
formats using tables (or relations).
The Relational Model Concepts
 A relation in the relational model is essentially a collection of
data in a table format, resembling a flat file with a linear,
structured format. Each row in this table represents a distinct
record, also known as a tuple, and each column holds specific
types of data called attributes.
 Unlike traditional files, relations in a database are structured
with more rigorous constraints and relational integrity.

124 Fundamentals of Database Systems

 The Relational Database Model
 Interpretation of Data in Relations:
 Each row (tuple) in a table contains related data values about a
particular entity or concept, while the table name and column
headers (attributes) define the context and meaning of each
value.
 For instance, a table named STUDENT might contain rows that
represent individual students, with attributes like Name,
Student_number, Class, and Major guiding the interpretation of
each column.

125 Fundamentals of Database Systems

 The Relational Database Model
 Terminology:
 Domain: Each attribute is defined by a domain, which is
the set of permissible values it can contain. For instance,
Social_security_numbers is a domain representing nine-
digit SSNs, and Grade_point_averages could represent
values from 0 to 4.
 Tuple: A row within a table, containing values for each
attribute.
 Attribute: A column in the table, labeled with a specific
name that describes its purpose (e.g., Name or Gpa).

126 Fundamentals of Database Systems

 The Relational Database Model
 Terminology:
 Q Relation Schema: The structure of a relation,
represented by the relation name (e.g., STUDENT) and
its attributes (e.g., Name, Ssn, Gpa).
 Degree: The number of attributes in a relation,
determining its arity (e.g., STUDENT with seven
attributes has a degree of seven).

127 Fundamentals of Database Systems

 The Relational Database Model
 Domains in Detail:
 Domains define the format and possible values of each attribute,
often represented with specific data types (e.g., integer for Age).
 Examples include:
 Usa_phone_numbers: Ten-digit phone numbers in the U.S.
 Academic_department_codes: Short codes like CS or PHYS.
 Employee_ages: Age values as integers between 15 and 80.

128 Fundamentals of Database Systems

 The Relational Database Model
 Relation Schema Example:
 For a relation schema STUDENT(Name, Ssn, Home_phone,
Address, Office_phone, Age, Gpa), each attribute has a domain,
such as Names for Name and Grade_point_averages for Gpa. This
schema describes the structure of data for students.
 Relation State:
 The relation state refers to the set of tuples in a table at a given
time, conforming to the relation schema. A state shows current
data, while the schema defines potential data structure.
 Each tuple within a relation can also have a special NULL value,
indicating missing or inapplicable data for an attribute.

129 Fundamentals of Database Systems

 The Relational Database Model
Attributes and Tuples of a relation STUDENT

130 Fundamentals of Database Systems

 The Relational Constraints and Relational
Database Schemas
•Key Constraints and Uniqueness:
 Superkey: A set of attributes where no two tuples can have the
same value for all attributes in that set.
Every relation has at least one default superkey, including all its
attributes.
 Key: A minimal superkey; it cannot have any attributes
removed while retaining its uniqueness.
For example, in a STUDENT relation, {Ssn} is a key, while {Ssn,
Name} is a superkey but not a minimal key.
 Candidate Keys: Relations may have multiple candidate keys,
each uniquely identifying tuples.
 Typically, one candidate key is designated as the primary key,
which is conventionally underlined.
131 Fundamentals of Database Systems
 The Relational Constraints and Relational
Database Schemas
 NULL Constraints:
 Constraints on NULL values, allowing attributes in a tuple to
remain undefined or represent unknown information. However,
their usage is minimized to avoid ambiguities during data
operations.
 Entity and Referential Integrity Constraints:
 Entity Integrity: Ensures that no primary key attributes are
NULL, guaranteeing unique identification for each tuple.
 Referential Integrity: Maintains consistency across relations by
ensuring that foreign key values in one relation match primary
key values in another.
 Data Dependencies and Database Design:
 Functional and Multivalued Dependencies: Essential for database
132 Fundamentals of Database Systems
normalization to ensure data integrity and efficiency,
 Relational Database Schemas and Integrity
Constraints

 Primary Key Selection:

 When a relation schema has multiple candidate keys, one
candidate key is designated as the primary key.
 The choice is somewhat arbitrary but often favors a key with
fewer attributes for simplicity.
 Other candidate keys are labeled as unique keys and are not
underlined in schema notation.
 NULL Constraints:
 Constraints may specify whether attributes can contain NULL
values. For instance, a NOT NULL constraint ensures that every
tuple must have a non-NULL value for a specified attribute.

133 Fundamentals of Database Systems

 Relational Database Schemas and Integrity
Constraints

 Relational Database Schema and State:

 A Relational Database Schema (S) is a set of relation schemas (S = {R1, R2,
..., Rm}) with a set of integrity constraints (IC).
 A Relational Database State (DB) refers to a set of relation states (DB = {r1,
r2, ..., rm}) that conform to the integrity constraints in IC.
 A valid state of a database satisfies all integrity constraints, while an invalid
state does not.
 Entity Integrity and Referential Integrity:
 Entity Integrity: Requires that the primary key attribute in a relation cannot be
NULL. This ensures each tuple can be uniquely identified.
 Referential Integrity: Ensures consistency between tuples in different relations.
For instance, in an EMPLOYEE relation, the department number (Dno) of
each employee must match an existing Dnumber in the DEPARTMENT
relation.

134 Fundamentals of Database Systems

 Relational Database Schemas and Integrity
Constraints

 Defining Constraints with DDL:

 The Data Definition Language (DDL), often SQL,
supports defining primary keys, unique keys,
NOT NULL constraints, and entity and
referential integrity.
 These constraints are typically enforced by the
DBMS to maintain valid database states, helping
manage consistency across database interactions
and updates.
135 Fundamentals of Database Systems
 Relational Database Schemas and Integrity
Constraints

•Attribute Naming and Concept Representation:

•Attributes representing the same real-world concept may
not have the same name in different relations,
•like Dno in EMPLOYEE and Dnumber in
DEPARTMENT.
•Alternatively, distinct concepts may have identical names
across relations if they represent
•different ideas (e.g., Name in PROJECT vs Dname in
DEPARTMENT).
136 Fundamentals of Database Systems