The Entity Relationship Model Part II Instructor Mohamed

  • Slides: 50
Download presentation
The Entity. Relationship Model Part II. Instructor: Mohamed Eltabakh meltabakh@cs. wpi. edu CS 3431

The Entity. Relationship Model Part II. Instructor: Mohamed Eltabakh [email protected] wpi. edu CS 3431 -B 11 1

Entities with Different Attribute Types (Recap) Multivalued Attribute: major Composite Attribute: address Derived Attribute:

Entities with Different Attribute Types (Recap) Multivalued Attribute: major Composite Attribute: address Derived Attribute: Age 2

Binary Relationships (Recap) 3

Binary Relationships (Recap) 3

Multi-Way Relationships (Recap) Model the relationship Supplier supplies Products to Consumers Ternary relationship (three-way)

Multi-Way Relationships (Recap) Model the relationship Supplier supplies Products to Consumers Ternary relationship (three-way) 4

Recursive Relationship Types and Roles (Recap) Refer to the same entity in the relationship

Recursive Relationship Types and Roles (Recap) Refer to the same entity in the relationship Recursive relationship type : Part-Subpart Roles: There are Parts that play the role of super. Part There are Parts that play the role of sub. Part 5

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 6

Keys of Entity Sets l Remember entity set is a group of entities with

Keys of Entity Sets l Remember entity set is a group of entities with the same type l Key of Entity Set l l Examples: l l “Car” VIN “Person” SSN “WPI Student” WPI ID A key has to be unique within the scope of your application l l Set of attributes that uniquely identify each entity Does not have to be globally unique Example: l “US Student” SSN, University. Name + Univesity. ID 7

Types of Keys l A super key of an entity set is a set

Types of Keys l A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity l l l “Person” SSN, SSN + First. Name “Account” Account. Number + Account. Type A candidate key of an entity set is a minimal super key l l l “Person” SSN “Account” Account. Number “US Student” SSN, University. Name + Univesity. ID l Each candidate key is a super key but not vice versa l A primary key is one from, possibly several, candidate keys Pick one and declare it as “primary key” l “Student” SSN, Student. ID, First. Name + Middle. Name + Last. Name 8

Primary Keys in ERD l Select only one key to be the primary key

Primary Keys in ERD l Select only one key to be the primary key l Primary key is modeled by “underline” under its set of attributes l Good Practice: l Select singleton and number fields whenever possible 9

Multi-Attributes Primary Key for Movie is <title, year> Key for Student is s. Number

Multi-Attributes Primary Key for Movie is <title, year> Key for Student is s. Number We can represent key for entity type consisting of more than one attribute (e. g. : Movie) 10

Keys of Relationships l Relationship without attributes l l The combination of primary keys

Keys of Relationships l Relationship without attributes l l The combination of primary keys of the participating entity sets forms a key of a relationship set (customer_id, load_number) is the key of borrower 11

Keys of Relationships (Cont’d) l Relationship with attributes l l Attributes of the relationship

Keys of Relationships (Cont’d) l Relationship with attributes l l Attributes of the relationship may (not always) participate inside the key + the external keys (s. Number, c. Number, Date) is the key of Taken 12

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 13

Cardinality Constraints l Express the number of entities to which another entity can be

Cardinality Constraints l Express the number of entities to which another entity can be associated via a relationship set l Most useful in describing binary relationship sets l For a binary relationship set the mapping cardinality must be one of the following types: l l One to one One to many Many to one Many to many 14

Mapping Cardinalities 15

Mapping Cardinalities 15

Mapping Cardinalities (Cont’d) 16

Mapping Cardinalities (Cont’d) 16

Representing Cardinalities in ERD l In a relationship: l l l “ “ “

Representing Cardinalities in ERD l In a relationship: l l l “ “ “ ” : Represent “many” (including 0) ” : Represent “one” (including 0) ”: Represent “one” (must be one) A student is taking “many” courses. A course can be taken by “many” students. 17

One-To-Many Relationship l In the one-to-many relationship a loan is associated with at most

One-To-Many Relationship l In the one-to-many relationship a loan is associated with at most one customer via borrower, a customer is associated with many (including 0) loans via borrower One-to-Many from Customer to Loan 18

Many-To-One Relationship l In a many-to-one relationship a loan is associated with many (including

Many-To-One Relationship l In a many-to-one relationship a loan is associated with many (including 0) customers via borrower, a customer is associated with at most one loan via borrower 19

Many-To-Many Relationship l In a many-to-many relationship a loan is associated with many (including

Many-To-Many Relationship l In a many-to-many relationship a loan is associated with many (including 0) customers via borrower, a customer is associated with many loan via borrower 20

Degree of Cardinalities How : Expressed using (min, max) l Student can take many

Degree of Cardinalities How : Expressed using (min, max) l Student can take many courses, and a course can be taken by many students l Student can take 0 to 5 courses, and a course can be taken by 3 to 60 students 21

Cardinality Constraints for Recursive Relationships p. Number p. Name Part super. Part sub. Part

Cardinality Constraints for Recursive Relationships p. Number p. Name Part super. Part sub. Part Contains quantity A Part may contain many subparts A Part can be subpart in many super. Parts 22

Cardinality Constraints for Recursive Relationships A Part can have many sub. Parts A Part

Cardinality Constraints for Recursive Relationships A Part can have many sub. Parts A Part can be subpart for at most one super. Part 23

Cardinality Constraints for Multi-way Relationships p. Number p. Name Product s. Name c. Name

Cardinality Constraints for Multi-way Relationships p. Number p. Name Product s. Name c. Name Supply Supplier Consumer s. Loc c. Loc price qty Every Supplier supplies some Product to some Consumer To add degree constraints, introduce a new entity set and create multiple binary relationships !!! 24

Adding Cardinality Constraints to Multi-way Relationships p. Number p. Name Product c. Name s.

Adding Cardinality Constraints to Multi-way Relationships p. Number p. Name Product c. Name s. Name in Supplier Consumer s. Loc consumes supplies c. Loc Supp_Cons_ Prod price qty What is the key of this entity ? ? ? (Weak Entity) 25

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 26

Weak Entity Sets l An entity set that does not have a primary key

Weak Entity Sets l An entity set that does not have a primary key is referred to as a weak entity set l l Its attributes are not enough to form a key The existence of a weak entity set depends on the existence of an identifying entity set l It must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set Identifying entity set Weak entity set Course number is unique only within the department 27

Weak Entity Sets l Discriminator (or partial key) of a weak entity set l

Weak Entity Sets l Discriminator (or partial key) of a weak entity set l l Primary key of a weak entity set l l The composition of the primary key of the identifying entity set + the weak entity set’s discriminator Identifying entity has to exist for each weak entity l l The set of attributes that uniquely identify a weak entity given its identifying entity Cannot have a course without a corresponding department (d. Number, c. Number) is the primary key for Course discriminator 28

Representing a Weak Entity Set l Weak entity set is represented by double rectangles

Representing a Weak Entity Set l Weak entity set is represented by double rectangles l Weak relationship (supporting relationship) is represented by double diamonds l Weak relationship is one-many from the weak entity to the identifying entity 29

Again: It Depends on Your Application/Assumptions l If you assume the course number is

Again: It Depends on Your Application/Assumptions l If you assume the course number is unique within a department l l “Course” is a weak entity set If you assume the course number is unique across all departments l “Course” is a strong entity set 30

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 31

What about an Exercise !!! Lets interactively design a database for a hospital 32

What about an Exercise !!! Lets interactively design a database for a hospital 32

ISA Relationship Types l Similar to “subclass” concept in Object-Oriented languages l Entity sets

ISA Relationship Types l Similar to “subclass” concept in Object-Oriented languages l Entity sets share some common attributes but differ in others l Sometimes called “Specialization/Generalization” l Example l Students can be UGStudents or Grad. Students l l l UGStudents take undergrad Classes Grad. Students can be TAs or RAs Grad. Students are advised by Professors 33

ISA Relationship Types (Cont’d) l Top-down design process l Build entities with the common

ISA Relationship Types (Cont’d) l Top-down design process l Build entities with the common attributes, then build sub-entities with distinctive attributes from other entities in the set l These sub-entities become lower-level entity sets that have attributes or participate in relationships that do not apply to the general higher-level entity set l In ERD, represented by a triangle component labeled ISA (E. g. customer “is a” person) l Attribute inheritance l Lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked 34

ISA Relationship: Example I 35

ISA Relationship: Example I 35

ISA Relationship: Example II 36

ISA Relationship: Example II 36

ISA Relationship: Generalization l Bottom-up design process l l Specialization and generalization are simple

ISA Relationship: Generalization l Bottom-up design process l l Specialization and generalization are simple inversions of each other l l Combine a number of entity sets that share the same features into a higher-level entity set They are represented in an E-R diagram in the same way The terms specialization and generalization are used interchangeably. 37

Multiple ISA Relationships l Can have multiple specializations of an entity set based on

Multiple ISA Relationships l Can have multiple specializations of an entity set based on different features l E. g. permanent_employee vs. temporary_employee, in addition to officer vs. secretary vs. teller l Each particular employee would be l l A member of one of permanent_employee or temporary_employee, And also a member of one of officer, secretary, or teller 38

Multiple ISA Relationships: Example 39

Multiple ISA Relationships: Example 39

ISA Relationship: Constraints l Constraint on which entities can be members of a given

ISA Relationship: Constraints l Constraint on which entities can be members of a given lower-level entity set l l l Example: all customers over 65 years are members of senior-citizen entity set; seniorcitizen ISA person Denoted in ERD on the ISA edge Constraint on whether or not entities may belong to more than one lower-level entity set within a single generalization. l Disjoint l l Overlapping l l An entity can belong to only one lower-level entity set An entity can belong to more than one lower-level entity set Denoted in ERD by writing “disjoint” or “overlapping” next to ISA triangle, by default “disjoint” 40

ISA Relationship: Constraints (Cont’d) l Completeness constraint -- specifies whether or not an entity

ISA Relationship: Constraints (Cont’d) l Completeness constraint -- specifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within a generalization l Total : An entity must belong to one of the lower-level entity sets l Partial: An entity need not belong to one of the lower-level entity sets 41

Example Overlapping & Partial 42

Example Overlapping & Partial 42

ISA Relationship: Keys & Multiplicity l l Key of sub-entities is inherited from the

ISA Relationship: Keys & Multiplicity l l Key of sub-entities is inherited from the super-entities Multiplicity is 1: 1 person_id is the primary key 43

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities

More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 44

Summary of Symbols used in ERD 45

Summary of Symbols used in ERD 45

Coming up with a good design for your application l No single right design,

Coming up with a good design for your application l No single right design, there can be many… l Put clear, reasonable assumptions and make a design that captures the assumptions l l Without stating the assumptions, others can claim your design is wrong !!! It is like art, common sense and experience make a difference 46

Guidelines Toward a Good Design (I) l Convey “real” application requirements l Utilize meaningful

Guidelines Toward a Good Design (I) l Convey “real” application requirements l Utilize meaningful names l Try simpler construct first l Avoid redundancy, do not store the same data in multiple places l Be as precise as possible (E. g. , cardinality constraints) l Don’t over specify (limits input) 47

Guidelines Toward a Good Design (II) l Do not overuse non-binary relationships l l

Guidelines Toward a Good Design (II) l Do not overuse non-binary relationships l l Do not create entity sets with single attributes l l They are harder to understand interpret They may be better as attributes of other entity sets Do not overuse ISA relationships l There always some commonalities between things this does not mean they should inherit from common ancestor 48

Guidelines Toward a Good Design (III) l Choose meaningful relationships l Know when to

Guidelines Toward a Good Design (III) l Choose meaningful relationships l Know when to add attributes to entity sets vs. relationships l Some business constraints will not be captured in the design l E. g. , For a customer to get a load, the sum of the previous loans to him/her must be < Max. Loan 49

Summary of Entity-Relationship Model l Concepts l l Entity, Entity Sets, Weak Entity Sets

Summary of Entity-Relationship Model l Concepts l l Entity, Entity Sets, Weak Entity Sets Relationships Types l l binary, ternary, multi-way, recursive, weak, ISA Attributes l For entity sets or relationship types l Simple, composite, derived, multi-valued l Constraints – key, cardinality l Guidelines for Good Design 50