2003 SJSU -- CmpE L4-S1 ER Diagrams

Database Design

Dr. M.E. Fayad, Professor

Computer Engineering Department, Room #283I

College of Engineering

San José State University

One Washington Square

San José, CA 95192-0180

http://www.engr.sjsu.edu/~fayad

2003 SJSU – CmpE --- M.E. Fayad L4-S2 ER Diagrams

2

Lesson 4:Entity Relationship

Diagrams (ERD)

2003 SJSU – CmpE --- M.E. Fayad L4-S3 ER Diagrams

Lesson Objectives

Objectives

3

Understand the notation of ERDs Learn how to do ER Analysis Illustrate examples for ERDs

2003 SJSU – CmpE --- M.E. Fayad L4-S4 ER Diagrams

E-R diagrams (ERDs):– Identify major ingredients, entities, of a

complicated situation– Identify characteristics, attributes, of the

major ingredients– Uncover and analyze interconnections,

relationships, among the major ingredients– Assess various aspects of the

relationships4

What Is Entity-Relationship Diagrams?

2003 SJSU – CmpE --- M.E. Fayad L4-S5 ER Diagrams

ERD – Chen 1976 Precursor to the class diagram – Will be covered

later. No generalization

ERD– Entities: Information holding structures– Relationships: Connections between entities– Cardinalities: Upper bounds– Modalities: Lower bounds– Attributes: Properties, characteristics, …

associated with an entity. 5

What Is Entity-Relationship Diagrams?

2003 SJSU – CmpE --- M.E. Fayad L4-S6 ER Diagrams

In short, ERD strives– to take a close look at

the ingredients of a puzzle

– in order to identify major and minor ingredients

– and to figure out how they relate to each other 6

What Is Entity-Relationship Diagrams?

2003 SJSU – CmpE --- M.E. Fayad L4-S7 ER Diagrams

ERD can be summarized as the process of uncovering, analyzing, and explicating (i.e., stating clearly and fully) the business rules governing a complicated situation

7

Entity-Relationship Diagrams

2003 SJSU – CmpE --- M.E. Fayad L4-S8 ER Diagrams

Business rules are precise statements, derived from a detailed description of the organization's operations, that define one or more of the following modeling components:

– Entities– Relationships– Attributes– Connectivities– Cardinalities– Constraints

1Rob, P.; Coronel, C. Instructor’s Manual to Accompany Database Systems: Design, Implementation, and Management. Fourth Edition. CD-ROM 8

Business Rules (1)

2003 SJSU – CmpE --- M.E. Fayad L4-S9 ER Diagrams

Examples of business rules1

– An invoice contains one or more invoice lines, but each invoice line is associated with a single invoice. That is,

• An invoice contains one or more invoice lines.• Each invoice line is associated with a single invoice.

– A store employs many employees, but each employee is employed by only one store.

– A college has many departments, but each department belongs to a single college. (This business rule reflects a university that has multiple colleges such as Business, Liberal Arts, Education, Engineering, etc.)

1From: Rob, P.; Coronel, C. Instructor’s Manual to Accompany Database Systems: Design, Implementation, and Management. Fourth Edition. CD-ROM 9

Business Rules (2)

2003 SJSU – CmpE --- M.E. Fayad L4-S10 ER Diagrams

Examples of business rules1 – A driver may be assigned to drive many different

vehicles, and each vehicle can be driven by many drivers. (Note: Keep in mind that this business rule reflects the assignment of drivers over a period of time.)

– A client may sign many contracts, but each contract is signed by only one client.

– A sales representative may write many contracts, but each contract is written by one sales

representative 10

Business Rules (3)

2003 SJSU – CmpE --- M.E. Fayad L4-S11 ER Diagrams

Some business rules for a library– Books and other materials exist in various

editions and numbers of copies– Books may or may not circulate– Books that circulate may do so for varying

periods• Periods may be determined by

– Book status alone– Borrower status alone– Combination of book status and borrower status 11

Business Rules (4)

2003 SJSU – CmpE --- M.E. Fayad L4-S12 ER Diagrams

More business rules for a library• Users may have various statuses• Users may have varying numbers of

items currently charged out to them• Users may or may not owe fines

–Fine status may or may not affect eligibility to borrow

12

Business Rules (5)

2003 SJSU – CmpE --- M.E. Fayad L4-S13 ER Diagrams

More business rules for a library• Staff members have attributes of

– Salary or wage rates– Annual leave– Sick leave– Work schedules– Job titles– Special skills (e.g., languages, puppetry)– Hire dates– Total length of service– Professional organization memberships

13

Business Rules (6)

2003 SJSU – CmpE --- M.E. Fayad L4-S14 ER Diagrams

More business rules for a library• Physical facilities include

– Branches– Furniture– Machines (e.g., computers, microform readers,

automobiles, players for CD-ROMs, audiotapes, videotapes)

– Library-management software– Catalog records (e.g., computerized databases,

catalog cards)– Records of inventory, acquisitions, circulation,

personnel 14

Business Rules (7)

2003 SJSU – CmpE --- M.E. Fayad L4-S15 ER Diagrams

More business rules for a library• Related organizations may include

– City, university, or corporate administrations

– Employee unions

– Friends of the Library organizations

– Vendors of books, serials, and other products and services used by the library

– Networks of which the library is a member (e.g., OCLC, Amigos, Association of Research Libraries, Texas State Library network, Central Texas Library System) 15

Business Rules (8)

2003 SJSU – CmpE --- M.E. Fayad L4-S16 ER Diagrams

E-R analysis relies heavily on diagrams to represent

– Entities (rectangles)– Attributes (ellipses)– Relationships (diamonds)– Connections (lines, which may

have arrowheads or crowsfeet to represent degrees of connectivity or relationship)

16

E-R Diagrams (1)

2003 SJSU – CmpE --- M.E. Fayad L4-S17 ER Diagrams

Data models are tools used in analysis to describe the data requirements and assumptions in the system from a top-down perspective. There are three basic elements in ER models:

– Entities are the "things" about which we seek information.

– Attributes are the data we collect about the entities.

– Relationships provide the structure needed to draw information from multiple entities.

17

Entity Relationship Diagrams

2003 SJSU – CmpE --- M.E. Fayad L4-S18 ER Diagrams

Developing an ERD requires an understanding of the system and its components. Before discussing the procedure, let's look at a narrative created by Professor Harman.

Consider a hospital: Patients are treated in a single ward by the doctors assigned to them. Usually each patient will be assigned a single doctor, but in rare cases they will have two.

Heathcare assistants also attend to the patients, a number of these are associated with each ward. 18

Developing ERDs

2003 SJSU – CmpE --- M.E. Fayad L4-S19 ER Diagrams

Initially the system will be concerned solely with drug treatment. Each patient is required to take a variety of drugs a certain number of times per day and for varying lengths of time.

The system must record details concerning patient treatment and staff payment. Some staff are paid part time and doctors and care assistants work varying amounts of overtime at varying rates (subject to grade).

The system will also need to track what treatments are required for which patients and when and it should be capable of calculating the cost of treatment per week for each patient (though it is currently unclear to what use this information will be put). 19

Developing ERDs

2003 SJSU – CmpE --- M.E. Fayad L4-S20 ER Diagrams

1. Define Entities: these are usually nouns used in descriptions of the system, in the discussion of business rules, or in documentation; identified in the narrative (see highlighted items above).

2. Define Relationships: these are usually verbs used in descriptions of the system or in discussion of the business rules (entity ______ entity); identified in the narrative (see highlighted items above).

3. Add attributes to the relations; these are determined by the queries,and may also suggest new entities, e.g. grade; or they may suggest the need for keys or identifiers. 20

How do we start an ERD? (1)

2003 SJSU – CmpE --- M.E. Fayad L4-S21 ER Diagrams

What questions can we ask?

a. Which doctors work in which wards?

b. How much will be spent in a ward in a given week?

c. How much will a patient cost to treat?

d. How much does a doctor cost per week?

e. Which assistants can a patient expect to see?

f. Which drugs are being used? 21

How do we start an ERD? (2)

2003 SJSU – CmpE --- M.E. Fayad L4-S22 ER Diagrams

4. Add cardinality to the relations Many-to-Many must be resolved to two one-to-manys with an additional entity

Usually automatically happens Sometimes involves introduction of a link entity (which

will be all foreign key) Examples: Patient-Drug

5. This flexibility allows us to consider a variety of questions such as: a. Which beds are free?

b. Which assistants work for Dr. X? c. What is the least expensive prescription? d. How many doctors are there in the hospital? e. Which patients are family related?

6. Represent that information with symbols. 22

How do we start an ERD? (3)

2003 SJSU – CmpE --- M.E. Fayad L4-S23 ER Diagrams

Overview Concepts Rules Guidelines

<See Documents with Lecture 04>

23

ERDs

2003 SJSU – CmpE --- M.E. Fayad L4-S24 ER Diagrams

It is especially important to identify and note many-to-many (M:N) relationships: for example1

– Note: The small circle denotes optionality (a person need not be a customer; but to be a customer, he or she must rent at least one tape)

24

Crucial Use of E-R Analysis (1)

1From: Rob, P.; Coronel, C. Instructor’s Manual to Accompany Database Systems:

Design, Implementation, and Management. Fourth Edition. CD-ROM

2003 SJSU – CmpE --- M.E. Fayad L4-S25 ER Diagrams

The typical resolution of many-to-many (M:N) relationships is by a bridge table (also called a composite entity)

25

Crucial Use of E-R Analysis (2)

2003 SJSU – CmpE --- M.E. Fayad L4-S26 ER Diagrams

A further explication of the resolution of the M:N relationship

26

Crucial Use of E-R Analysis (3)

2003 SJSU – CmpE --- M.E. Fayad L4-S27 ER Diagrams

T/F

1. ERD supports generalization.

2. Cardinality of one is symbolized by a bar -- | --written across the relationship line.

3. An entity type must have at least one relevant attribute type.

4. Entity Type Name is Synonyms and Homonyms.

5. Cardinality: A business rule indicating the number of times a particular object or activity may occur. 27

Discussion Questions

2003 SJSU – CmpE --- M.E. Fayad L4-S28 ER Diagrams

Task 1: Read Chapter 4

Task 2: Assignment #4 is Ch. 4.1, 4.2, 4.3

28

Tasks for Next Lecture