Chapter 4 and 5 Summary

Chapter 4 summary
 ER model stands for an Entity-Relationship model. This model is used to define the data
elements and relationship for a specified system.

 It develops a conceptual design for the database. It also develops a very simple and easy to
design view of data.

 In ER modeling, the database structure is portrayed as a diagram called an entity-relationship

diagram.

 Entity-table that hold specific information (data)

 Entities, defined as tables that hold specific information (data)
 Entities, defined as tables that hold specific information (data)
 Relationships- associations or interactions between entities

 Attribute-characteristics of entities.

Types of attributes

 Required attribute- attribute that must have a value

 Optional attribute- an attribute that must not have a value thus can be left empty
 The key attribute is used to represent the main characteristics of an entity. It represents a
primary key. The key attribute is represented by an ellipse with the text underlined
 An attribute that composed of many other attributes is known as a composite attribute. The
composite attribute is represented by an ellipse, and those ellipses are connected with an
ellipse.
 An attribute that composed of many other attributes is known as a composite attribute. The
composite attribute is represented by an ellipse, and those ellipses are connected with an
ellipse.
 Single valued attribute- an attribute that can only have one single value.
 Multivalued Attribute- An attribute can have more than one value. These attributes are known
as a multivalued attribute. The double oval is used to represent multivalued attribute.
 An attribute can have more than one value. These attributes are known as a multivalued
attribute. The double oval is used to represent multivalued attribute.

Domain- is a set of possible value for a given attribute

3. Relationship-A relationship is used to describe the relation between entities.

Connectivity and cardinality

Connectivity- describes the relationship classification

Cardinality- expresses the minimum and maximum number of entity occurrence associated with one occurrence of
the related entity.

Existence dependence
Existence dependence – occurs when an entity is associated with another entity occurrence

Existence independence – occurs when an entity is existing apart from all of its related entities. It is also called
strong or regular entity.

Relationship strength
 Relationships are the glue that holds the tables together. They are used to connect related information
between tables.
 Relationship strength is based on how the primary key of a related entity is defined.
 A weak, or non-identifying, relationship exists if the primary key of the related entity does not contain a
primary key component of the parent entity
 A strong, or identifying, relationship exists when the primary key of the related entity contains the
primary key component of the parent entity

Relationship participation
Optional relationship- one entity occurrence does not require the corresponding entity occurrence in
particular relationship.

Mandatory relationship- one entity occurrence requires the corresponding entity occurrence in a
particular relationship.

Relationship Degree
 Ternary relationship: a relationship type that involves many to many relationships between
three tables.
 Binary relationship: occurs when two entities are associated in a relationship
 Unary relationship: one in which a relationship exists between occurrences of the same entity
set.
 Recursive relationship- is one in which relationships can exist between occurrence of the same
entities.

Database design Challenges

Design standards- the database design must conform to the to the design standards

Processing speed- the processing speed must be higher to minimize access time

Information requirements
 Chapter 5
Extended entity relationship model

 Result of adding more semantic constructs to original entity relationship (ER) model
 Diagram using this model is called an EER diagram (EERD)

Entity Supertypes and Subtypes

Entity supertype: -Generic entity type related to one or more entity subtypes

-Contains common characteristics

Entity subtypes – Contains unique characteristics of each entity subtype

Specialization Hierarchy

 Depicts arrangement of higher-level entity supertypes and lower-level entity

subtypes
 Relationships described in terms of “IS-A”relationships
 Subtype exists only within context of supertype
 Every subtype has only one supertype to which it is directly related
 Can have many levels of supertype/subtype relationships

Inheritance

 Enables entity subtype to inherit attributes and relationships of supertype

 All entity subtypes inherit their primary key attribute from their supertype
 At implementation level, supertype and its subtype(s) maintain a 1:1 relationship
 Entity subtypes inherit all relationships in which supertype entity participates
 Lower-level subtypes inherit all attributes and relationships from all upper level-supertypes

Subtype Discriminator

Attribute in supertype entity

 Determines to which entity subtype each supertype occurrence is related

 Default comparison condition for subtype discriminator attribute is equality comparison
 Subtype discriminator may be based on other comparison condition

Disjoint and Overlapping Constraints

 Disjoint subtypes – Also known as non-overlapping subtypes – Subtypes that contain unique
subset of supertype entity set
  Overlapping subtypes – Subtypes that contain nonunique subsets of supertype entity set

Completeness Constraint

 Specifies whether entity supertype occurrence must be a member of at least one subtype
 Partial completeness – Symbolized by a circle over a single line – Some supertype occurrences
that are not members of any subtype
 Total completeness – Symbolized by a circle over a double line – Every supertype occurrence
must be member of at least one subtype

Specialization and Generalization

Specialization

 Identifies more specific entity subtypes from higher-level entity supertype

 Top-down process
 Based on grouping unique characteristics and relationships of the subtypes

Generalization

 Identifies more generic entity supertype from lower-level entity subtypes

 Bottom-up process
 Based on grouping common characteristics and relationships of the subtypes

Entity Clustering

 “Virtual” entity type used to represent multiple entities and relationships in ERD
 Considered “virtual” or “abstract” because it is not actually an entity in final ERD
 Temporary entity used to represent multiple entities and relationships
 Eliminate undesirable consequences – Avoid display of attributes when entity clusters are used

Entity Integrity: Selecting Primary Keys

 Primary key most important characteristic of an entity – Single attribute or some combination of
attributes
 Primary key’s function is to guarantee entity integrity
 Primary keys and foreign keys work together to implement relationships
 Properly selecting primary key has direct bearing on efficiency and effectiveness

Natural Keys and Primary Keys

 Natural key is a real-world identifier used to uniquely identify real-world objects – Familiar to
end users and forms part of their day-to-day business vocabulary

Primary Key Guidelines

  Attribute that uniquely identifies entity instances in an entity set – Could also be combination of
attributes
 Main function is to uniquely identify an entity instance or row within a table
 Guarantee entity integrity, not to “describe” the entity
 Primary keys and foreign keys implement relationships among entities – Behind the scenes,
hidden from user

When to Use Composite Primary Keys

Composite primary keys useful in two cases: – As identifiers of composite entities

 Where each primary key combination allowed once in M:N relationship – As identifiers of weak
entities Where weak entity has a strong identifying relationship with the parent entity
 Automatically provides benefit of ensuring that there cannot be duplicate values

When To Use Surrogate Primary Keys

 Especially helpful when there is:

 No natural key
 Selected candidate key has embedded semantic contents
 Selected candidate key is too long or cumbersome

Design Cases

• Four special design cases that highlight:

–Importance of flexible design

– Proper identification of primary keys

– Placement of foreign keys

a. Design Case #1: Implementing 1:1 Relationships

 Foreign keys work with primary keys to properly implement relationships in relational
model
 Put primary key of the “one” side on the “many” side as foreign key – Primary key:
parent entity – Foreign key: dependent entityIn 1:1 relationship two options: – Place a
foreign key in both entities (not recommended) – Place a foreign key in one of the
entities
 Primary key of one of the two entities appears as foreign key of other
b. Design Case #2: Maintaining History of Time-Variant Data
 Existing attribute values replaced with new value without regard to previous value Time-
variant data: – Values change over time – Must keep a history of data changes
 Keeping history of time-variant data equivalent to having a multivalued attribute in your
entity
  Must create new entity in 1:M relationships with original entity
 New entity contains new value, date of change

Design Case #3: Fan Traps

 Design trap occurs when relationship is improperly or incompletely identified – Represented in a

way not consistent with the real world
 Most common design trap is known as fan trap
 Fan trap occurs when one entity is in two 1:M relationships to other entities – Produces an
association among other entities not expressed in the model

Design Case #4: Redundant Relationships

 Redundancy is seldom a good thing in database environment

 Occur when there are multiple relationship paths between related entities
 Main concern is that redundant relationships remain consistent across model
 Some designs use redundant relationships to simplify the design