Points to emphasize
How to use Entity-Relationship (ER) modeling in database design.
The basic concepts associated with the Entity-Relationship (ER) model.
A diagrammatic technique for displaying an ER model using the Unified Modeling Language (UML).
How to identify and resolve problems with ER models called connection traps.
How to build an ER model from a requirements specification.
What To Model?
Static Information Data -- Entities
Associations -- Relationships among entities Dynamic Information
Processes -- Operations/transactions
Integrity constraints -- Business rules/regulations and data meanings
What is Data Model?
A collection of tools for describing:- data, data relationships, data semantics, data constraints Data Model:- A data model is a collection of concepts that can be used to describe the structure of database.
Schema:- The description of a database is called the database schema.
System model tools:
Data Flow Diagram (DFD)
Hierarchical Input Process and Output (HIPO) State Transition Diagrams (STD)
Entity Relationship (ER) Diagrams
Entity-Relationship Model (ER Model)
A data model in which information stored in the database is viewed as sets of entities and sets of relationships among entities and it is diagram-based representation of domain knowledge, data properties etc...., but it is more intuitive and less mechanical. Entity – Relationship is a popular high-level conceptual data model.
Components of E R model
a. Entity b. Relationship c. Attributes
Entity:- The basic object that the ER model represents is an entity, which is a “thing” in the real world with an independent existence.
Entity set:-A set of entities of the same type.
• Entity sets need not be disjoint. Example: A person entity could be in both the customer and employee sets
Types of Entities
• Entities with Physical existence Example: Student, Customer, Book etc
• Entities with Conceptual existence Example: Sale, University course etc Relationship:-
An association among two or more Entities.
Example:The Relationship between a Faculty and Student i.e. Faculty take course for Student Relationship Set
A set of Relationships of the same type Attribute:-
The particular properties of entity that describe it
Example: A student entity might have attributes such as: roll number, name, age, address etc
• As all entities in an entity set have the same attributes, entity sets also have attributes - the attributes of the contained entities. The value of the attribute can be different for each entity in the set.
Types of Attributes:- i) Composite attributes ii) Simple attributes iii) Single-valued attributes iv) Multivalued attributes v) Stored attributes vi) Derived attributes
Relationship Degree:-
The number of entity types associated with that relationship (fig. 5.1.1).
Types of Relationships (fig. 5.1.2):-
The one-to-one relationship has the cardinality or degree of one and only one in both direction.
The one tomany or-to-one relationship has a cardinality in one direction of one or more and in the other direction of one and only one.
The many –to-many relationship
Multiplicity: Multiplicity constrains the way that entities are related - it is a representation of the policies (or business rules) established by the user or enterprise. Multiplicity actually consists of two separate constraints.
Cardinality: Cardinality describes the maximum number of possible relationship occurrences for an entity participating in a given relationship type i.e. How many relationship instances is an entity permitted to be linked to.
Participation: Participation determines whether all or only some entity occurrences participate in a relationship i.e. how is an entity linked to the relationship.
Total participation (indicated by double line): Every entity in the entity set participates in atleast one relationship in the relationship set.
Partial participation: Some entities may not participate in any relationship in the relationship set Note: Cardinality limits can also express participation constraints.
Weak and Strong Entity Set
A Strong Entity set has a primary key. All tuples in the set are distinguishable by that key.
A Weak entity set has no primary key unless attributes of the strong entity seton which it depends are included.
Tuples in a weak entity set are partitioned according to their relationship with tuples in a strong entity set.
Tuples within each partition are distinguishable by a discriminator, which is a set of attributes.
The discriminator (or partial key) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set.
Weak Entity set is represented by double rectangles.
Underline the discriminator of a weak entity set with a dashed line.
The primary key of the Strong entity set is not explicitly stored with the Weak entity set, since it is implicit in the identifying relationship.
Reasons to have weak entities
We want to avoid the data duplication and consequent possible inconsistencies caused by duplicating the key of the strong entity.
Weak entities reflect the logical structure of an entity being dependent on another entity.
Weak entities can be deleted automatically when their strong entity is deleted.
Weak entities can be stored physically with their strong entities.
Existence Dependencies
If the existence of entity x depends on the existence of entity y, then x is said to be existence dependent on y. y is a dominant entity, x is a subordinate entity. If y entity is deleted, then all its associated x entities must be deleted.
ER diagram symbols (fig. 5.1.3)
Keys
A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity.
A candidate key of an entity set is a minimal super key.
Although several candidate keys may exist, one of the candidate keys is selected to be the primary key.
Aggregation
Aggregation is an abstraction through which relationships are treated as higher-level entities.
Thus the relationship between entities A and B is treated as if it were an entity C.
Utility of E-R Model
It maps well to the relational model. The constructs used in the ER model can easily be transformed into relational tables.
It is simple and easy to understand with a minimum of training. Therefore, the model can be used by the database designer to communicate the design to the end user.
In addition, the model can be used as a design plan by the database developer to implement a data model in specific database management software.
E-R Design Decisions
The use of an attribute or entity set to represent an object.
Whether a real-world concept is best expressed by an entity set or a relationship set.
The use of a ternary relationship versus a pair of binary relationships.
The use of a strong or weak entity set.
The use of specialization/generalization - contributes to modularity in the design.
The use of aggregation - can treat the aggregate entity set as a single unit without concern for the details of its internal structure
Tools to create E-R Diagrams
Several Computer Aided Software Engineering (CASE) tools exist to help in creating E-R Diagrams and the resulting physical database elements. Products include:
IEW, IEF, DEFT, ER-WIN, Visio