Entity Relationship Diagrams

Entity Relationship Diagrams Mr.Prasad Sawant MIT PUNE

Universe of Discourse REQUIREMENTS COLLECTION AND ANALYSIS FUNCTIONAL ANALYSIS APPLICATION PROGRAM DESIGN Software Analysis & Design • Description of requirements of users data modelling, process modelling • Data modelling is expressed using a high level model such as ENTITY-RELATIONSHIP (ER) • The 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 • 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 • 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 • 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) ModelingKey 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) ModelingKey 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) ModelingKey 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

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

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

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

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 EMPLOYEES

name sal SSN EMPLOYEE Notation 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

Attributes • Key Attributes • Attribute Types Notation

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 SSN EMPLOYEE

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

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

Single Vs. Multivalued Attributes 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!

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

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

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) ModelingKey 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 • 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 • 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 • 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

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

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

Supply Works_In Assigned_to Relationship Degree Departments • 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 Binary Multiple Employees Supplier Project Ternary Part

Departments employer Role Names Works_In worker Employees 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 Supervision Recursive Relationship Supervisor Subordinate Recursive Relationships • Same entity type can participate more than once in the same relationship type under different “roles” • Such relationships are called “Recursive Relationships”

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

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

Departments Works_In Employees Possible Cardinality Ratios • 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

. . . . . . . . . . . . . . . . . . . . 1-to-1 Many-to-Many Many - to - 1 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

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

1 N Employees Departments 1 1 Works_In Manages 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

Departments Works_In Employees 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 DEPARTMENT via the WorksInrelationship type

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

1 N Employees Departments 1 1 Works_In Manages 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

Entity Relationship Diagrams