The entity relationship model
This presentation is the property of its rightful owner.
Sponsored Links
1 / 74

The Entity-Relationship Model PowerPoint PPT Presentation


  • 71 Views
  • Uploaded on
  • Presentation posted in: General

The Entity-Relationship Model. Instructor: Mohamed Eltabakh [email protected] Part II. Entities with Different Attribute Types (Recap). Multivalued Attribute: major. Primitive Attribute: sNumber. Composite Attribute: address. Student entity type with all its attributes. DoB. Age.

Download Presentation

The Entity-Relationship Model

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


The entity relationship model

The Entity-Relationship Model

Instructor: Mohamed Eltabakh

[email protected]

Part II.

CS3431: C-Term 2013


Entities with different attribute types recap

Entities with Different Attribute Types (Recap)

Multivalued Attribute: major

Primitive Attribute: sNumber

Composite Attribute: address

Student entity type

with all its attributes

DoB

Age

sNumber

Derived Attribute: Age

Age


Binary relationships recap

Binary Relationships (Recap)

pNumber

sName

sPrice

product

date

quantity

supplies

buys

sName

cName

supplier

consumer

sLoc

cLoc

Attributes can be attached to Entity Sets or Relationships


Multi way relationships recap

Multi-Way Relationships (Recap)

Model the relationship Supplier supplies Products to Consumers

Ternary relationship

(three-way)


Recursive relationship types and roles

Recursive Relationship Types and Roles

Refer to the same entity set in the relationship

Recursive relationship type :Part-Subpart

Roles:

There are Parts that play the role of superPart

There are Parts that play the role of subPart

If two entities in the same entity set have a relationship  Recursive relationship


Recursive relationships another example

Recursive Relationships: Another Example

  • Employees & Managers

ID

supervisor

Supervise

Employee

supervised

Name


More elements in er model

More Elements in ER Model

  • Key Constraints

  • Cardinality Constraints

  • Weak Entities

  • Subclass Entities (ISA Relationships)

  • Principles for Good Design


Keys of entity sets

Keys of Entity Sets

  • Remember entity set is a group of entitieswith the same type

  • Key of Entity Set

    • Set of attributes that uniquely identify each entity

  • Examples:

    • “Car” VIN

    • “Person” SSN

    • “WPI Student” University ID

    • “US Student”  UniversityName + UnivesityID

  • A key has to be unique within the scope of your application

    • Does not have to be globally unique

Customer

Car


Types of keys

Types of Keys

  • A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity

    • “Person” SSN, SSN + FirstName

    • “Account”  AccountNumber + AccountType

  • A candidate key of an entity set is a minimal super key

    • “Person” SSN

    • “Account”  AccountNumber

    • “US Student” SSN, UniversityName + UnivesityID

  • Each candidate key is a super key but not vice versa

  • A primary key is one from, possibly several, candidate keys  Pick one and declare it as “primary key”

    • “Student”  SSN, StudentID, FirstName + MiddleName + LastName


Keys summary

Keys Summary

  • Key combination of one or more attributes that uniquely identify an entity

  • Types:

    • Super key

    • Candidate key

    • Primary key

Only primary keys are modeled in ERD


Primary keys in erd

Primary Keys in ERD

  • Select only one key to be the primary key

  • Primary key is modeled by “underline” under its set of attributes

  • Good Practice:

    • Select singleton and number fields whenever possible


Multi attributes primary key

Multi-Attributes Primary Key

Key for Movie is <title, year>

Key for Student is sNumber

We can represent key for entity set consisting of more than one attribute (e.g.: Movie)


Keys of relationships

Keys of Relationships

  • Relationship without attributes

    • 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


Keys of relationships cont d

Keys of Relationships (Cont’d)

  • Relationship with attributes

    • Attributes of the relationship may (not always) participate inside the key + the external keys

  • (sNumber, cNumber) is the key of Taken

Grade

Date

project


Keys of relationships cont d1

Keys of Relationships (Cont’d)

  • Relationship with attributes

    • Attributes of the relationship may (not always) participate inside the key + the external keys

  • (sNumber, cNumber, Date) is the key of Taken

Grade

Date

project

In this ERD: student can take the same course on different dates


More elements in er model1

More Elements in ER Model

  • Key Constraints

  • Cardinality Constraints

  • Weak Entities

  • Subclass Entities (ISA Relationships)

  • Principles for Good Design


Cardinality constraints

Cardinality Constraints

  • Express the number of entities to which another entity can be associated via a relationship set

  • Most useful in describing binary relationship sets

  • For a binary relationship set the mapping cardinality must be one of the following types:

    • One to one

    • One to many

    • Many to one

    • Many to many


Mapping cardinalities

Mapping Cardinalities


Mapping cardinalities cont d

Mapping Cardinalities (Cont’d)


Representing cardinalities in erd

Representing Cardinalities in ERD

  • In a relationship:

    • “” : Represent “many”(including 0)

    • “” : Represent “one”(including 0)

    • “”: Represent “one” (must be one)

A course can be taken by “many” students.

A student is taking “many” courses.


One to many relationship

One-To-Many Relationship


One to many relationship1

One-To-Many Relationship

  • In the one-to-many relationship a loan is associated with at most onecustomer via borrower, a customer is associated with many(including 0) loans via borrower

A customer can take many loans

A loan can be taken by one (and at least one) customer


Many to one relationship

Many-To-One Relationship


Many to one relationship1

Many-To-One Relationship

  • In a many-to-one relationship a loan is associated with many(including 0) customers via borrower, a customer is associated with at most oneloan via borrower

A customer can take at most one loan

A loan can be taken by many customers


Many to many relationship

Many-To-Many Relationship

  • 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


Degree of cardinalities

Degree of Cardinalities

How : Expressed using (min, max)

(0, 5)

(3, 60)

  • Student can take manycourses, and a course can be taken by manystudents

  • Student can take 0 to 5 courses, and a course can be taken by 3 to 60 students


Cardinality constraints for recursive relationships

pNumber

pName

Part

superPart

subPart

Contains

quantity

Cardinality Constraints for Recursive Relationships

A Part may contain manysubparts

A Part can be subpart in manysuperParts


Cardinality constraints for recursive relationships1

Cardinality Constraints for Recursive Relationships

A Part can have manysubParts

A Part can be subpart for at most onesuperPart


Revisit this example

Revisit this example…

  • Employees & Managers ….. Add cardinalities

ID

supervisor

Supervise

Employee

supervised

Name

Semantics:

  • Manager can supervise many employees

  • Employee is supervised by one manager


Cardinality constraints for multi way relationships

Cardinality Constraints for Multi-way Relationships

pNumber

pName

Product

sName

cName

Supply

Supplier

Consumer

sLoc

cLoc

price

qty

Every Supplier supplies some Product to some Consumer

To add degree constraints, introduce a new entity set and create multiple binary relationships !!!


Adding cardinality constraints to multi way relationships

Adding Cardinality Constraints to Multi-way Relationships

pNumber

pName

Product

cName

sName

in

Consumer

Supplier

cLoc

sLoc

consumes

supplies

Supp_Cons_

Prod

What is the key of this entity ???

(Weak Entity)

price

qty


More elements in er model2

More Elements in ER Model

  • Key Constraints

  • Cardinality Constraints

  • Weak Entities

  • Subclass Entities (ISA Relationships)

  • Principles for Good Design


Weak entity sets

Weak Entity Sets

  • An entity set that does not have a primary key is referred to as a weak entity set

    • 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

    • It must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set

Course number is unique only within the department

Weak entity set

Identifying entity set


Weak entity sets1

Weak Entity Sets

  • Discriminator(or partial key) of a weak entity set

    • The set of attributes that uniquely identify a weak entity given its identifying entity

  • Primary key of a weak entity set

    • 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

    • Cannot have a course without a corresponding department

  • (dNumber, cNumber) is the primary key for Course

discriminator


Representing a weak entity set

Representing a Weak Entity Set

  • Weak entity set is represented by double rectangles

  • Weak relationship (supporting relationship) is represented by double diamonds

  • Weak relationship is one-many from the weak entity to the identifying entity


Again it depends on your application assumptions

Again: It Depends on Your Application/Assumptions

  • If you assume the course number is unique within a department

    • “Course” is a weak entity set

  • If you assume the course number is unique across all departments

    • “Course”  is a strong entity set

offers

Course

Stating your assumptions in text is very important !!!


Revisit previous example

Revisit Previous Example …

pNumber

pName

Product

cName

sName

in

Consumer

Supplier

cLoc

sLoc

consumes

supplies

Supp_Cons_

Prod

Weak Entity

price

qty


What about an exercise

What about an Exercise !!!

Lets interactively design a database for a Hotel


Example hotel database

Example: Hotel Database

  • A Hotel has many branches

    • Hotel  name, logo, address of HQ, Tel., manager, star rating

    • Branch  Id, address, Tel., Total capacity

  • Each branch has many rooms with different types and numbers. A room type defines

    • Room size, Number of beds

    • Has TV or not, Has Balcony or not

  • Guests can stay in a hotel for a period of time

    • Guests have unique ID, name, address, Tel.

    • We need to capture, the length of the stay, start date, end date, money paid


The entity relationship model

Ver. 1

Name

Branch

Room

Hotel

Num Beds

Capacity

Capacity

Has Balcony

Has TV

Manager

Rating

Tel.

HQ Add.

Tel.

Add.

Type

ID

Num

  • Observations:

  • Room type is modeled as attribute (causes redundancy)

  • Room number, is it numeric like 1001? If so, how come to be unique across branches?


The entity relationship model

Ver. 2

Name

Branch

Type

Room

Hotel

Num Beds

Capacity

Capacity

Has Balcony

Has TV

Tel.

Rating

Manager

HQ Add.

Add.

Type

Tel.

Num

ID

  • Observations:

  • Lets add relationships


The entity relationship model

Ver. 3

contains

has

Of type

Name

Type

Room

Branch

Hotel

Num Beds

Has Balcony

Has TV

Capacity

Capacity

Tel.

Rating

Manager

HQ Add.

Tel.

Type

Add.

ID

Num

Common mistake: Do not add “Branch ID” as an attribute to “Room” entity set. It is already captured by the weak relationship “contains”.


Back to the requirements

Back to the Requirements

  • A Hotel has many branches

    • Hotel  name, logo, address of HQ, Tel., manager, star rating

    • Branch  Id, address, Tel., Total capacity

  • Each branch has many rooms with different types and numbers. A room type defines

    • Room size, Number of beds

    • Has TV or not, Has Balcony or not

  • Guests can stay in a hotel for a period of time

    • Guests have unique ID, name, address, Tel.

    • We need to capture, the length of the stay, start date, end date, money paid


The entity relationship model

Ver. 4

contains

has

Of type

Name

Length of stay

Money Paid

Name

Start date

End date

Branch

Guest

Type

Room

Hotel

  • Observations:

  • “Stay” attributes should not be part of “Guest”

Num Beds

Capacity

Has TV

Capacity

Has Balcony

Tel.

Rating

Manager

HQ Add.

Tel.

Add.

Type

Add.

Tel.

ID

Num

ID


The entity relationship model

Ver. 5

contains

has

Of type

Name

Length of stay

Money Paid

Name

Start date

End date

Branch

Guest

Type

Room

Hotel

Num Beds

Capacity

Capacity

Has Balcony

Has TV

Manager

Rating

Tel.

HQ Add.

Tel.

Add.

Add.

Tel.

Type

ID

ID

Num

  • Observations:

  • Still not quite right..

  • “Stays-in” 1-M or M-M??

  • (Guest should be able to stay in diff. rooms)

Stays in


The entity relationship model

Ver. 6

contains

has

Of type

Name

Length of stay

Money Paid

Name

Start date

End date

Branch

Guest

Type

Room

Hotel

Num Beds

Capacity

Capacity

Has Balcony

Has TV

Manager

Rating

Tel.

HQ Add.

Tel.

Add.

Add.

Tel.

Type

ID

ID

Num

  • Observations:

  • Not done yet…

  • In this model, a guest cannot stay in the same room over diff visits!!!

Stays in


The entity relationship model

Ver. 7

contains

has

Of type

Name

Length of stay

Money Paid

Name

Start date

End date

Branch

Guest

Type

Room

Hotel

Num Beds

Capacity

Capacity

Has Balcony

Has TV

Manager

Rating

Tel.

HQ Add.

Tel.

Add.

Add.

Tel.

Type

ID

ID

Num

  • Observations:

  • Start_date part of key

  • Length of stay  derived attribute

Stays in


More elements in er model3

More Elements in ER Model

  • Key Constraints

  • Cardinality Constraints

  • Weak Entities

  • Subclass Entities (ISA Relationships)

  • Principles for Good Design


Isa relationship types

ISA Relationship Types

  • Similar to “subclass” concept in Object-Oriented languages

  • Entity sets share some common attributes but differ in others

  • Sometimes called “Specialization/Generalization”

  • Example

    • Students can be UGStudents or GradStudents

      • UGStudents take undergrad Classes

      • GradStudents can be TAs or RAs

      • GradStudents are advised by Professors


Isa example

ISA Example

  • All attributes of “student” are inherited in the other entity sets

  • Each entity set, e.g., “Freshman”, can have its own additional attributes


Isa relationship types cont d

ISA Relationship Types (Cont’d)

  • Top-down design process

    • Build entities with the common attributes, then build sub-entities with distinctive attributes from other entities in the set

  • 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

  • In ERD, represented by a trianglecomponent labeled ISA (E.g. customer “is a”person)

  • Attribute inheritance

    • Lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked


More complete example

More Complete Example


More complete example1

More Complete Example

Attributes of Person:

SSN, Name, DOB

Attributes of Student:

SSN, Name, DOB, GPA, StartDate

SSN, Name, DOB, Salary, Department, Specialization

Attributes of Technician:


Multiple isa relationships

Multiple ISA Relationships

  • Can have multiple specializations of an entity set based on different features

Permanent Emp

ISA

Temporary Emp


Isa relationship constraints

ISA Relationship: Constraints

  • Three types of constraints

    • Membership: To which entity set an entity belongs

    • Overlapping: can an entity belong to multiple subclasses or not

    • Completeness: Does each super entity have to belong to one (or more) subclasses


Isa relationship membership

ISA Relationship: Membership

  • Constraint on which entities can be members of a given lower-level entity set

    • Denoted in ERD on the ISA edge

Year

Year = 1

Year = 4

Year = 2

Year = 3


Isa relationship overlapping

ISA Relationship: Overlapping

  • Constraint on whether or not entities may belong to more than one lower-level entity set within a single generalization.

    • Disjoint

      • An entity can belong to only one lower-level entity set

    • Overlapping

      • 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”

disjoint


Isa relationship completeness

ISA Relationship: Completeness

  • 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

    • Total: An entity must belong to one of the lower-level entity sets

    • Partial: An entity need not belong to one of the lower-level entity sets

Total


Another example

Another Example

Partial, Overlapping


Isa relationship multiplicity

ISA Relationship: Multiplicity

  • ISA relationship is always 1-1 (even though its notation is arrows without heads)


Isa relationship keys

ISA Relationship: Keys

  • Key of sub-entities is inherited from the superentities

SSN is the primary key for Person, Student, Employee, Freshman, Technician, and all other sub-entities


More elements in er model4

More Elements in ER Model

  • Key Constraints

  • Cardinality Constraints

  • Weak Entities

  • Subclass Entities (ISA Relationships)

  • Principles for Good Design


Summary of symbols used in erd

Summary of Symbols used in ERD


Coming up with a good design for your application

Coming up with a good design for your application

  • No single right design, there can be many…

  • Put clear, reasonable assumptions and make a design that captures the assumptions

    • Without stating the assumptions, others can claim your design is wrong !!!

  • It is like art, common sense and experience make a difference

  • The simplest design that captures the requirements is the best


Guidelines toward a good design i

Guidelines Toward a Good Design (I)

  • Convey “real” application requirements

  • Utilize meaningful names for

    • Entity sets, attributes, relationships

  • Avoid redundancy, do not store the same data in multiple places

  • Be as precise as possible (E.g., cardinality constraints)

  • Don’t over specify (limits input)

  • Know when to add attributes to entity sets vs. relationships


Examples

Examples

Num

SSN

take

Loan

Customer

offer

lend

X

Bank

- The room “capacity, Num Beds, has TV” attributes they all depend on the type. So why repeat them with each room.

- The “type” should be a separate entity set (slide 42)

ID

Room

Type

  • The relationship “lend” is redundant and should not be there

  • The relation between a customer and a bank is already captured by the two other relationships

Num Beds

Capacity

Has TV

Num


M m relationships vs an entity set

M-M Relationships vs. An Entity Set

  • M-M Relationship between E1 and E2 can be always broken to:

    • A new entity set E3 (usually weak entity set)

    • 1-M relationship between E1 and E3

    • 1-M relationship between E2 and E3

  • Both are correct use either one

Num

ID

Date

grade

Num

ID

Course

taking

Student

Course

Student

include

Involve

Registration

Date

grade


Do not overuse isa relationship

Do not overuse ISA relationship

  • There are always some commonalities between things  this does not mean they should inherit from common ancestor

  • Use it only if there is a substantial overlap in attributes (and possibly relationships)

Prof

Student

- No need for an entity set “Person” from which both “Prof” and “Student” inherit


Strong vs weak entity sets

Strong vs. Weak Entity Sets

  • Avoiding weak entities is better (If no semantics is lost)

  • You may add unique keys

has

Name

Name

has

Branch

Hotel

Hotel

Branch

ID

ID

- Should always favor the left design over the right one (unless explicitly stated otherwise)


Do not overuse multi way relationships

Do not overuse multi-way relationships

  • They are harder to understand and interpret

  • Can be broken by introducing new entity set and several 1-M relationships

Avoid multi-way relationship

Avoid weak entity set


Erd cannot capture everything

ERD Cannot Capture Everything…

  • Some business constraints will not be captured in the design. For example:

    • For a customer to get a load, the sum of the previous loans to him/her must be < MaxLoan

    • A student cannot take the same course more than 2 times

    • A student cannot re-take a course that (s)he already passed


Find the wrong things

Find the wrong things ???

Age

Model

Make

VIN

Name

DoB

ID

ColorName

car

buys

Customer

colorID

Date

Price

contains

Date

takes

CarMiles

FeatureName

Car-feature

Loan

Bank

amount

= A customer can buy many cars

= A customer may take a loan to buy a specific car

number


From the previous example

From the Previous Example

  • ColorId & ColorName (cause redundancy & inconsistency)

  • Car can have one feature (wrong cardinality)---should be many

  • Car-feature has one attribute (should not be an entity)---make it attr.

  • CarMiles should be attached to the car (not to the relationship)

  • Age should be a derived attribute

  • A car should be bought by one (or zero) customers (the arrow head should be closed)

  • Loan and Car are not linked together (buys should be 3-way)

    • Or create a new entity set “Contract” and link it to the three entity sets


Summary of er model

Summary of ER Model

  • Concepts

    • Entity, Entity Sets, Weak Entity Sets

    • Relationships Types

      • binary, ternary, multi-way, recursive, weak, ISA

    • Attributes

      • For entity sets or relationship types

      • Simple, composite, derived, multi-valued

  • Constraints – key, cardinality

  • Guidelines for Good Design


  • Login