Overview of EntityRelationship Model EntityRelationship Model is a

Overview of Entity‐Relationship Model □ Entity‐Relationship Model is a detailed, logical representation of the data for an organization or for a business area. □ □ □ What are the entities and relationships in the enterprise? What information about these entities and their relationships should we store in the database? What are the integrity constraints or business rules that holds? The model must be as ‘open’ as possible and not tied to any technology or to any particular business methodology. Introduced in 1976 by Peter Chen. ISE 230 1

Overview of Entity‐Relationship Model (2) □ The E‐R model is usually expressed as an E‐R diagram. □ Widespread CASE tool‐support □ □ No standard notation for E‐R modeling/diagram. Chen, Martin, Crow Foot and many other notations. E‐R Model is the mainstream approach for data modeling. Communication tool between various stakeholders. ISE 230 2

Data Definitions in Data Modeling □ Term – word or phrase with specific meaning □ □ Fact – association between two or more terms □ □ □ Examples: course, rental car, flight, reservation ‘A course is a module of instruction in a particular subject area’ ‘A customer may request a model of a car from a rental branch on a particular date’ A good data definition is: □ □ □ ISE 230 Related to business, not technical, characteristics Meaningful and self‐documenting Composed of words from an approved list. 3

Entities and Entity Sets □ Entity: A person, place, object, event, or concept in the user environment about which the organization wishes to maintain data. □ □ Entity Type (or Entity Set) – collection of entities □ □ More examples on page 93 and 94 of 8 th Edition. Often corresponds to a table. Entity instance – A single occurrence of an entity type. □ Often corresponds to a row in a table. Player ISE 230 Match Team Ground 4

What Should an Entity Be? □ SHOULD BE: □ □ □ SHOULD NOT BE: □ □ □ An object that will have many instances in the database. An object that will be composed of multiple characteristics/attributes. A user of the database system. An output of the database system (e. g. , a report). Rules (conventions) for naming entities. □ ISE 230 Singular (e. g. Player) vs. Plural names (e. g. Players) 5

Attributes □ Property or characteristic of an entity set. □ □ An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set. Examples: Player name, Career runs, Number of centuries scored, etc. Domain: The set of permitted values for each attribute. Rules/Conventions for naming attributes too. ISE 230 6

Attribute Classes □ □ □ Simple vs. Composite Attribute Single‐Valued vs. Multi‐valued Attribute Stored vs. Derived Attributes Identifier Attributes Required vs. Optional Attributes ISE 230 10

Composite Attributes □ An attribute broken into many parts: compound data values. ISE 230 8

Multi‐valued Attributes □ Multiple data values for one attribute are allowed ISE 230 9

Derived Attributes □ Value can be computed from other attributes □ Example: Age, given Date of Birth Career Runs Centuries Player Name Player Average ISE 230 10

Identifiers (aka Keys) □ □ Identifier (or Key) is an attribute (or combination of attributes) that uniquely identifies individual instances of an entity type Simple vs. Composite Identifier Flight. Number Date Destination Flight. ID Flight ISE 230 11

Characteristics of Identifiers □ Will not change in value □ □ □ No intelligent/dynamic identifiers (e. g. , containing locations or people that might change) Example: Captain’s name as an identifier of the team (YYoounis. Elevenn)) Will not be null/empty It is recommended to substitute new, simple keys for long, composite keys Candidate Identifier is an attribute that could be a key □ ISE 230 It satisfies the requirements for being an identifier 12

Relationships □ □ □ Links/Association between entities – modeled as connecting lines. Relationship Types: Modeled as association between entity types Relationship Instances: Links between specific entity instances ISE 230 Player Inzimam‐ul‐Haq Team Brian Lara Lahore Badshah Rana Naveed West Indies Sachin Tendulkar India Pakistan 13

Cardinality of Relationships □ One‐to‐One □ □ One‐to‐Many □ □ Each entity in the relationship will have exactly one related entity An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity Many‐to‐Many □ ISE 230 Entities on both sides of the relationship can have many related entities on the other side 14

Cardinality Constraints □ □ Cardinality Constraints ‐ the number of instances of one entity that can or must be associated with each instance of another entity Minimum Cardinality □ □ □ If zero, then relationship is optional If one or more, then mandatory (MUST hold) Maximum Cardinality □ ISE 230 The maximum number of entity instances that could be association with the target entity in the relationship. 15

One‐to‐One Relationship □ One entity instance associated with only (maximum) one target entity instance A manager is associated with only one team at most, or there is no manager. ISE 230 One team is managed by only one manager at most or not managed at all. 20

One‐to‐One Relationship (2) □ Optional cardinalities A person is married to at most one other person, or may not be married at all ISE 230 17

Many‐to‐Many Relationship □ Many entity instances associated with either optionally or mandatory many target entity instances One or more players are associated with none or more teams. ISE 230 Many teams can have same or many players. 18

One‐to‐Many Relationship □ One entity instance associated with optionally or mandatory many target entity instances. ISE 230 19

Examples of multiple relationships □ Entities can be related to one another in more than one way □ ISE 230 Employees and departments 20

Examples of multiple relationships □ Professors and courses (fixed upper limit constraint) ISE 230 21

Degree of Relationships □ Degree of a relationship is the number of entity types that participate in it □ □ □ ISE 230 Unary Relationship Binary Relationship Ternary Relationship 22

Degree of relationships: Notations One entity related to another of the same entity type ISE 230 Entities of two different types related to each other Entities of three different types related to each other 27

Degree of relationships: Examples □ a) Unary relationships ISE 230 24

Degree of relationships: Examples □ b) Binary relationships ISE 230 25

Degree of relationships: Examples □ c) Ternary relationship Note: a relationship can have attributes of its own ISE 230 30

Binary Relationships with Attributes Here, the date completed attribute pertains specifically to the employee’s completion of a course…it is an attribute of the relationship ISE 230 31

An associative entity (CERTIFICATE) Associative entity is like a relationship with an attribute, but it is also considered to be an entity in its own right. Note that the many‐to‐many cardinality between entities in previous figure has been replaced by two one‐to‐many relationships with the associative entity. ISE 230 28

Ternary relationship as an associative entity ISE 230 29

Associative Entities □ When should a relationship with attributes instead be an associative entity? □ □ □ All relationships for the associative entity should be many The associative entity could have meaning independent of the other entities The associative entity preferably has a unique identifier, and should also have other attributes The associative entity may participate in other relationships other than the entities of the associated relationship Ternary relationships should be converted to associative entities ISE 230 30

Multi‐valued Attributes vs. Relationships simple composite ISE 230 31

Strong vs. Weak Entities □ Strong Entity Type: One that exists independently of other entity types. □ □ Strong entity instances always have a unique identifier Identifier is underlined with single‐line Examples: Student, Employee, Course Weak Entity Type: One whose existence depends on a strong entity (called Identifying Owner). □ □ ISE 230 It only has a partial identifier Partial identifier is underlined with double‐line Entity box has double line Example: Employee versus Employee. Credentials 32

Conclusion □ Summary □ □ Essential Reading □ □ Basic business rules are data names and definitions Data modeling notations frequently used today is the entity‐relationship data model. E‐R model constructs: entities, entity types, relationship types, attributes, identifiers, cardinality constraints. Modern Database Management (8 th Ed. ), Chapter 3 What is Next? □ ISE 230 More on ER and Enhanced E‐R Model 33