Entity Relationship Diagrams

Entity Relationship Diagrams: 

Entity Relationship Diagrams Mr.Prasad Sawant MIT PUNE

Software Analysis & Design: 

Software Analysis & Design Universe of Discourse REQUIREMENTS COLLECTION AND ANALYSIS FUNCTIONAL ANALYSIS APPLICATION PROGRAM DESIGN Description of requirements of users  data modelling, process modelling Data modelling is e xpressed using a high level model such as ENTITY-RELATIONSHIP (ER) T he ER Model represented pictorially (ER diagrams) ER Model contains detailed descriptions of: What are the entities and relationships in the enterprise? What information about these entities and relationships should we store in the database? What are the integrity constraints or business rules that hold?

Data modelling vs Process modelling: 

Data modelling vs Process modelling Process modelling (i.e. DFD) shows data stores, how, where, when data are used or changed in an System Data modelling (i.e ER) shows the definition, structure, & relationship within the data

Conceptual Data Modeling and the E-R Diagram: 

Conceptual Data Modeling and the E-R Diagram Goal Capture as much of the meaning of the data as possible A better design that is scalable and easier to maintain

Introduction to Entity-Relationship (E-R) Modeling: 

Introduction to Entity-Relationship (E-R) Modeling Notation uses three main constructs Data entities Attributes Relationships Entity-Relationship (E-R) Diagram A detailed, logical representation of the entities, associations and data elements for an organization or business

Entity-Relationship (E-R) Modeling Key Terms: 

Entity-Relationship (E-R) Modeling Key Terms Entity A person, place, object, event or concept in the user environment about which the organization wishes to maintain data Represented by a rectangle in E-R diagrams Entity Type A collection of entities that share common properties or characteristics Attribute A named property or characteristic of an entity that is of interest to an organization

Entity-Relationship (E-R) Modeling Key Terms: 

Entity-Relationship (E-R) Modeling Key Terms Candidate keys and identifiers Each entity type must have an attribute or set of attributes that distinguishes one instance from other instances of the same type Candidate key Attribute (or combination of attributes) that uniquely identifies each instance of an entity type

Entity-Relationship (E-R) Modeling Key Terms: 

Entity-Relationship (E-R) Modeling Key Terms Identifier A candidate key that has been selected as the unique identifying characteristic for an entity type Selection rules for an identifier Choose a candidate key that will not change its value Choose a candidate key that will never be null Avoid using intelligent keys Consider substituting single value surrogate keys for large composite keys

Slide 9: 

Notation Guide ENTITY TYPE WEAK ENTITY TYPE RELATIONSHIP TYPE IDENTIFYING RELATIONSHIP TYPE

Slide 10: 

… Notation Guide ATTRIBUTE KEY ATTRIBUTE MULTIVALUED ATTRIBUTE DERIVED ATTRIBUTE COMPOSITE ATTRIBUTE _____ . . .

Slide 11: 

ER Diagram Basics Relationship Attributes Entity Product Keeps Store descrip qty price pname manager Locations sname

Slide 12: 

Entity Sets A collection of similar entities (e.g. all employees) All entities in an entity set have the same set of attributes Each attribute has a domain Can map entity set to a relation easily SSN NAME SAL 321-23-3241 Kim 23,000 645-56-7895 Jones 45,000 EMPLOYEES

Slide 13: 

Entity Type Defines set of entities that have the same attributes (e.g. EMPLOYEE) Each Entity Type is described by its NAME and attributes The Entity Type describes the “Schema” or “Intension” for a set of entities Collection of all entities of a particular entity type at a given point in time is called the “Entity Set” or “Extension” of an Entity Type Entity Type and Entity Set are customarily referred to by the same name EMPLOYEE sal SSN name Notation

Slide 14: 

Attributes Key Attributes Attribute Types Notation

Slide 15: 

Key Attributes : Identifier Key (or uniqueness) constraints are applied to entity types Key attribute’s values are distinct for each individual entity in the entity set A key attribute has its name underlined inside the oval Key must hold for every possible extension of the entity type Multiple keys are possible EMPLOYEE SSN

Slide 16: 

Null Valued Attributes A particular entity may not have an applicable value for an attribute – Home-Phone: Not known if it exists – Height: Not known at present time Type of Null Values – Not Applicable – Unknown – Missing

Slide 17: 

Composite Vs. Simple Attributes Composite attributes can be divided into smaller parts which represent simple attributes with independent meaning Simple Attribute: Aircraft-Type Complex Attribute: Aircraft-Location which is comprised of : Aircraft-Latitude Aircraft-Longitude Aircraft-Altitude Notation … There is no formal concept of “composite attribute” in the relational model

Slide 18: 

Simple attributes can either be single-valued or multi-valued Single-valued: Gender = F Notation Multivalued: Degree = {BSc, MInfTech} Notation … An “attribute” in the relational model is always single valued - Values are atomic! Single Vs. Multivalued Attributes

Slide 19: 

Derived Vs. Stored Attributes Some attribute values can be derived from related attribute values: Age ® Date - B-day Y-Sal ® 12 * M-Sal EMPLOYEE M-sal B-days Y-sal Age Notation

Slide 20: 

Derived Vs. Stored Attributes Some attribute values can be derived from attributed values of related entities total-value ® sum (qty * price) Order Item price qty Total-Value

Slide 21: 

Representing Attributes Parenthesis ( ) for composite attributes Brackets { } for multi-valued attributes Assume a person can have more than one residence and each residence can have multiple telephones { AddressPhone ( { Phone ( AreaCode,PhoneNum ) }, Address ( StreetAddresss (Number, Street, AptNo) , City,State,PostalCode ) ) }

Entity-Relationship (E-R) Modeling Key Terms: 

Entity-Relationship (E-R) Modeling Key Terms Relationship An association between the instances of one or more entity types that is of interest to the organization Association indicates that an event has occurred or that there is a natural link between entity types Relationships are always labeled with verb phrases

Cardinality: 

Cardinality The number of instances of entity B that can be associated with each instance of entity A Minimum Cardinality The minimum number of instances of entity B that may be associated with each instance of entity A This is also called “modality”. Maximum Cardinality The maximum number of instances of entity B that may be associated with each instance of entity A

Naming and Defining Relationships: 

Naming and Defining Relationships Relationship name is a verb phrase Avoid vague names Guidelines for defining relationships Definition explains what action is being taken and why it is important Give examples to clarify the action Optional participation should be explained Explain reasons for any explicit maximum cardinality

Naming and Defining Relationships: 

Naming and Defining Relationships Guidelines for defining relationships Explain any restrictions on participation in the relationship Explain extent of the history that is kept in the relationship Explain whether an entity instance involved in a relationship instance can transfer participation to another relationship instance 10. 25

Slide 26: 

Relationships Relationship Types and Sets Relationship Degree Entity Roles and Recursive Relationships Relationship Constraints Attributes of Relationship Types

Slide 27: 

Relationship Types and Sets A Relationship is an association among two or more entities (e.g John works in Pharmacy department) A Relationship Type defines the relationship, and a Relationship Set represents a set of relationship instances A Relationship Type thus defines the structure of the Relationship Set Relationship Type and corresponding Set are customarily referred to by the same name

Slide 28: 

Relationship Degree The degree of a relationship type is the number of participating entity types – 2 entities: Binary Relationship 3 entities: Ternary Relationship n entities: N-ary Relationship – Same entity type could participate in multiple relationship types Part Supplier Supply Project Employees Departments Works_In Assigned_to Ternary Multiple Binary

Slide 29: 

Entity Roles Each entity type that participates in a relationship type plays a particular role in the relationship type The role name signifies the role that a participating entity from the entity type plays in each relationship instance, i.e. it explains what the relationship means Employees Works_In Departments employer worker Role Names

Slide 30: 

Recursive Relationships Same entity type can participate more than once in the same relationship type under different “roles” Such relationships are called “ Recursive Relationships ” Employees Supervisor Subordinate Recursive Relationship Supervision

Slide 31: 

Relationship Constraints What are Relationship Constraints ? Constraints on relationships are determined by the UoD, which these relationships are describing Constraints on the relationship type limit the possible combination of entities that may participate in the corresponding relationship set

Slide 32: 

Kinds of Constraints What kind of constraints can be defined in the ER Model? Cardinality Constraints Participation Constraints Together called “Structural Constraints” Constraints are represented by specific notation in the ER diagram

Slide 33: 

The “Cardinality Ratio” for a binary relationship specifies the number of relationship instances that an entity can participate in – Works-In is a binary relationship – Participating entities are DEPARTMENT : EMPLOYEE – One department can have Many employees - Cardinality Ratio is 1 : N Employees Works_In Departments Possible Cardinality Ratios

Slide 34: 

Possible Cardinality Ratios 1–to-1 (1 : 1) – Both entities can participate in only one relationship instance 1-to-Many, Many-to-1 (1 : N, N : 1) – One entity can participate in many relationship instances Many-to-Many (N: M) – Both entities can participate in many relationship instance . . . . . . . 1- to - Many . . . . . . . Many - to - 1 . . . . . . 1-to-1 . . . . . . . Many-to-Many

Slide 35: 

Example Cardinality Constraints How many Employees can work in a Department? One employee can work in only one department How many Employees can be employed by a Department? One department can employ many employees How many managers can a department have? One department can have only one manager How many departments can an employee manage? One employee can have manage only one department

Slide 36: 

Representing Cardinality One employee can work in only one department One department can employ many employees One department can have only one manager One employee can manage only one department N 1 1 1 Employees Works_In Departments Manages

Slide 37: 

Existence Dependency Existence dependency indicates whether the existence of an entity depends on its relationship to another entity via the relationship type – Every employee must work for a department - EMPLOYEE is existentially dependent on D EPARTMENT via the Works In relationship type Employees Works_In Departments

Slide 38: 

Kinds of participating constraints TOTAL Participation (Existence Dependency) Constraint : Every employee must work for a department PARTIAL Participation Constraint : Not every employee is a manager

Slide 39: 

Representing Participation Every employee must work for a department Every department must have a manager Every department must have employees Not every employee is a manager N 1 1 1 Employees Works_In Departments Manages