CPL

Entity Relationship Modeling

* How relationships between entities are defined and refined, and how such relationships are incorporated into the database design process
* How ERD components affect database design and implementation
* How to interpret the modeling symbols for the four most popular ER modeling tools
* That real-world database design often requires that you reconcile conflicting goals

Entity Relationship Model and Diagram

* ER model forms the basis of an ER diagram
* ERD represents the conceptual database as viewed by end user
* ERDs depict the ER model�s three main components:
o Entities
o Attributes
o Relationships
* Several different diagramming conventions

Entities

* Refers to the entity set and not to a single entity occurrence
* Corresponds to a table and not to a row in the relational environment
* In both the Chen and Crow�s Foot models, an entity is represented by a rectangle containing the entity�s name
* Entity name, a noun, is usually written in capital letters

Attributes

* Characteristics of entities
* Domain is set of possible values
* Primary keys underlined

Attributes (cont)
Attributes (cont)

Simple
Cannot be subdivided
Age, sex, GPA
Composite
Can be subdivided
Address: street city state zip
Single-valued
Has only a single value
Social security number
Multi-valued
Can have many values
Person may have several college degrees
Derived
Can be calculated from other information
Age can be derived from D.O.B.

Multivalued Attributes

Resolving Multivalued Attribute Problems

Although the conceptual model can handle multivalued attributes, you should not implement them in the relational DBMS

* Within original entity, create several new attributes, one for each of the original multivalued attribute�s components
o Can lead to major structural problems in the table
* Create a new entity composed of original multivalued attribute�s components

Creating New Attributes

Creating New Entity Set

Relationships

* Associations between entities
* Established by Business Rules
* Connected entities termed participants
* Connectivity describes relationship classification:
o 1:1, 1:M, M:N
* Cardinality
o Number of entity occurences associated with one occurence of related entity

Connectivity and Cardinality in an ERD

Relationship Strength

* Existence Dependent
o Entity's existence depends on existence of another related entities
o Existence-independent entities can exist apart from related entities
o Employee claims Child
Child is dependent on employee
* Weak (non-identifying)
o One entity is existence-independent on another
o PK of dependent entity doesn't contain PK component of parent entity
o Book is somewhat confused on this
* Strong (identifying)
o One entity is existence-dependent on another
o PK of related entity contains PK component of parent entity

Relationship Participation

* Optional
o Entity occurrence does not require a corresponding occurrence in related entity
o Shown by drawing a small circle on side of optional entity on ERD
* Mandatory
o Entity occurrence requires corresponding occurrence in related entity
o If no optionality symbol is shown on ERD, it is mandatory

Weak Entity

* Existence-dependent on another entity
* Has primary key that is partially or totally derived from parent entity

Weak Entity
Mandatory Class Course relationship

Optional Class Entity in Professor Teaches Class

Degree of Relationship

A relationships degree indicates the number of associated entities.

Implementation of a Ternary Relationship

Composite Entity

* Used to replace M:N relationships with 1:N relationships
* Bridge entities composed of primary keys of each entity needing connection

Entity Subtypes and Supertypes
Generalization Hierarchy

* Depicts relationships between higher-level supertype and lower-level subtype entities
* Supertype has shared attributes
* Subtypes have unique attributes
* Disjoint relationships
o Unique subtypes
o Non-overlapping
o Indicated with a `G'
* Overlapping subtypes use `Gs' Symbol

Nulls Created by Unique Attributes

Generalization Hierarchy: Disjoint

Generalization Hierarchy: Overlapping and Disjoint

Generalization
Supertype/Subtype relationship in an ERD

Comparison of ER Modeling Symbols

ER Modeling Symbols
Developing an E-R Diagram

* Iterative Process
1. Develop general narrative of organizational operations
2. Draw Basic E-R Model
3. Modify E-R model to incorporate newly discovered components/relationships
* Repeat until designers and users agree E-R model comple

Dealing with Conflicting Goals in Database Design

* Database must be designed to conform to design standards
* High-speed processing may require design compromises
* Quest for timely information may be the focus of database design
Other concerns:
o Security
o Performance
o Shared access
o Integrity
o Capabilities of actual DBMS