Database Systems Entity-Relationship Diagrams

Learning Objectives • Database Life Cycle • Database Terminology – Pop Quiz

• Introduce Entity Relationship Diagrams

1

Database Life Cycle

Ph Phase 1 Phase 2 Phase 3 Phase 4

E-R Diagram Development (Modeling)

Phase 5 Phase 6

Entity • An entity is a business object that t a group, or category t d t represents off data – The subject of the table

• A table is a collection of related records that define an entity

2

Pop Quiz • What are examples of entities?

Attribute • An attribute is a sub-group of information ithi an entity tit within – Characteristics of an entity

• Also referred to as a field or column • Is used to define and store data

3

Pop Quiz • What are examples of attributes?

Record • A logically connected set of one or more fi ld th ib a person, place, l fields thatt d describe or thing • Also referred to as a tuple or row • Each record is one instance of an entity

4

Pop Quiz • What is an example of a record?

Identifiers • A field that can uniquely identify each i t ( d) off an entity tit instance (record) – Candidate Key – Primary Key – Foreign Key

5

Pop Quiz • What are the rules for identifiers?

Pop Quiz • What is an example of a primary key?

6

Relationships • What is a relationship? • In databases, it is an association between two entities • Must define the type of relationship between entities

Cardinality • Cardinality expresses the minimum and i b off entity tit occurrences maximum number associated with one occurrence of the related entity • Types of relationships – One to one (1:1) – One to many (1:M) – Many to Many (M:N)

7

Entity-Relationship Diagram • An E-R Diagram is used to represent titi and d their th i relationships l ti hi entities • Used to facilitate interaction between designers, developers, and users • Entity classes are shown by rectangles • Crow’s foot notation is the most common method for showing relationships between entities

Crows Foot Notation

8

Relationship Example

Customer

Galbi

Customer Orders Galbi

Establish Relationships • • • • • • •

Man  Woman Instructor  Student Student  Class Instructor  Class Department  Instructor Instructor  Skill Painter  Painting

9

Man  Woman a.

Man

Woman

b.

Man

Woman

c.

Man

Woman

d.

Man

Woman

Instructor  Student a.

Instructor

Student

b.

Instructor

Student

c.

Instructor

Student

d.

Instructor

Student

10

Student  Class a.

Student

Class

b.

Student

Class

c.

Student

Class

d.

Student

Class

Instructor  Class a.

Instructor

Class

b.

Instructor

Class

c.

Instructor

Class

d.

Instructor

Class

11

Department  Instructor a.

Department

Instructor

b.

Department

Instructor

c.

Department

Instructor

d.

Department

Instructor

Instructor  Skill a.

Instructor

Skill

b.

Instructor

Skill

c.

Instructor

Skill

d.

Instructor

Skill

12

Painter  Painting a.

Painter

Painting

b.

Painter

Painting

c.

Painter

Painting

d.

Painter

Painting

E-R Diagrams • Goal – Capture as much of the meaning of the data as possible

• Result – A better design that is scalable and easier to maintain

13

Review • • • •

Entity, attributes, records Identifiers Relationships Cardinality

Recursive Relationship • It is possible for an entity to have a l ti hi tto ititself—this lf thi iis called ll d a relationship recursive relationship

14

Resolving Many-to-Many Relationships • Many-to-many relationships should be id d avoided • We can resolve a many-to-many relationship by dividing it into two one-tomany relationships • Creates a new entity called a bridge entity (bridge table)

Bridge Tables • Used to breakup a Many-to-Many l ti hi relationship • Requires a new entity • Primary Key typically consists of – Surrogate Key – Composite Key made up from the primary key of each connecting table

15

Bridge Table Example

SALES ORDERS

SALES ORDERS

INV. ITEMS

ORDER ITEMS

INV. ITEMS

Bridge Table Example 2

Student

Student

Class

Student2Class

Class

16

Weak Entities • A weak entity is an entity that cannot exist i the th d t b ith t th i t in database without the existence off another entity • A strong entity is an entity that can exist in the database without the existence of other entities • Any entity that is not a weak entity is called a strong entity

Weak Entity Examples

Strong Entity

|-------------------------------Weak Entities-------------------------------|

17

Boyfriend  Girlfriend a.

Boyfriend

Girlfriend

b.

Boyfriend

Girlfriend

c.

Boyfriend

Girlfriend

d.

Boyfriend

Girlfriend

Boyfriend  Girlfriend

d d.

Boyfriend

Girlfriend

“d” is correct! There should be a one-to-one relationship If the relationship is not correct, then there will be problems. Same in databases, if you do not have proper relationships, then problems. th you will ill have h bl

18

Old Woman  Cat a.

Old Woman

Cat

b.

Old Woman

Cat

c.

Old Woman

Cat

d.

Old Woman

Cat

Old Woman  Cat

a a.

Old Woman

Cat

“a” is correct! There is a one-to-many relationship One old woman can own many cats but each Cat can be owned by only one old woman

19

Actor  Movie a.

Actor

Movie

b.

Actor

Movie

c.

Actor

Movie

d.

Actor

Movie

Actor  Movie

c c.

Actor

Movie

“c” is correct! There is a many-to-many relationship Once actor can appear in many movies –and– One movie can have many actors We cannot have a Many-to-Many relationship in our database…how do we resolve this issue?

20

Actor  Movie

Actor

Actor

Movie

actor2Movie

Movie

Professor  Job Skill a.

Professor

Job Skill

b.

Professor

Job Skill

c.

Professor

Job Skill

d.

Professor

Job Skill

21

Professor  Job Skill Professor • Professor French • Professor Kim • Professor Lee • Professor Park

Job Skill • Database • Visual Basic • eCommerce • Telecommunications

Professor • Professor French • Professor Kim • Professor Lee • Professor Park

Job Skill • Database • Visual Basic • eCommerce • Telecommunications

Professor  Job Skill

c c.

Professor

Job Skill

“c” is correct! There is a many-to-many relationship Each professor can have many skills –and– each skill can be performed by many professors

22

Professor  Job Skill

Professor

Professor

Job Skill

p professor2Skill

Job Skill

Professor  Job Skill Professor • Professor French • Professor Kim • Professor Lee • Professor Park Professor2Skill French, Database French, French eCommerce Kim, Database Kim, eCommerce Kim, Telecommunications Lee, eCommerce Park, Database Park, Telecommunications

Job Skill • Database • Visual Basic • eCommerce • Telecommunications Bridge Table Eliminates data redundancy breaking up a many-to-many many to many relationship into 2 separate one-to-many relationships

23

Customer  Product a.

Customer

Product

b.

Customer

Product

c.

Customer

Product

d.

Customer

Product

Customer  Product

c c.

customer

product

“c” is correct! There is a many-to-many relationship One customer can order many products –and– One product can be ordered by many customers

24

Customer  Product

customer

product

How do we breakup this many to many relationship?

Customer  Product

customer

customer

product

order

pproduct

Is this correct? Any problems?

25

Customer  Product

customer

order

product

How do we fix our problem now?

Customer  Product

customer

order

product

orderItems

Is order a strong or weak entity?

26

Instructor  Student a.

Instructor

Student

b.

Instructor

Student

c.

Instructor

Student

d.

Instructor

Student

Instructor  Student

c c.

Instructor

Student

“c” is correct! There is a many-to-many relationship One instructor can have many students –and– one student can have many instructors

27

Instructor  Student

Instructor

Instructor

Student

Class

Student

Is this correct? Any problems?

Instructor  Student

Instructor

Class

Student

How do we fix our problem now?

28

Instructor  Student

Instructor

Class

Student

Student2Class

Are we finished? This is where it gets tricky!

Instructor  Student Professor • Professor French • Professor Kim • Professor Lee • Professor Park

Class • Database • Visual Basic • eCommerce • Telecommunications

Student • Minseok • SoYoung • John • Clair

Student2Class • Minseok, Database • Minseok, eCommerce • SoYoung, Database • SoYoung, Telecommunications • John, Database • Clair, eCommerce

29

Instructor  Student

Instructor

Class

Student

Student2Class

Another M:N Relationship!

Instructor  Student Course

Instructor

Class

Student

Student2Class All M:N relationships have been resolved

30

Developing the E-R Diagram Six steps for developing an ERD 1 C d il d narrative i off the h organization’s i i ’ 1. Create a detailed description of operations 2. Identify the business rules based on the description of operations 3. Identify the main entities and relationships from the business rules 4. Develop the initial ERD 5. Identify the attributes and primary keys that adequately describe the entities 6. Review and redraw the ERD

Step 1: Detailed Narrative • Heather Sweeney is an interior designer who specializes in home kitchen design • She offers a variety of free seminars at home shows, kitchen and appliance stores, and other public locations • She earns revenue by selling books and videos that instruct people on kitchen design • She also offers custom-design consulting services

31

Step 2: Business Rules • Customers must register for the seminar to tt d attend. • Heather Sweeney Designs has a business rule that no more than one form letter or email per day is to be sent to a customer

The Seminar Customer List

32

Step 3: Identify Main Entities

seminar

customer

What is the relationship between seminar and customer?

Step 3: Identify Relationships

seminar

customer

What problem can you identify?

33

Step 4: Develop The Initial ERD

seminar

seminar2customer

customer

Step 5: Identify Attributes and Keys seminar seminarID date time location title

seminar2customer seminarID (FK) emailAddress (FK)

customer emailAddress lastName firstName Phone streetAddress City State zip

34

Step 6: Review and Redraw ERD • Business Rule – Heather Sweeney Designs has a business rule that no more than one form letter or email per day is to be sent to a customer

• Must implement business rule into the design…how?

Updating the ERD seminar seminarID date time location title

seminar2customer seminarID (FK) ( ) emailAddress (FK)

customer emailAddress lastName firstName Phone streetAddress City State zip

contact emailAddress (FK) Date contactNumber contactType seminarID (FK)

35

Sales Invoice

Identify New Entities • What entities can you identify? • What is the relationship between these new entities?

36

Invoice  Product

invoice

product

What problem can you identify?

Create The Bridge Table

invoice

lineItem

product

37

Identify Attributes and Keys invoice invoiceNumber invoiceDate paymentType subtotal shipping tax total

lineItem invoiceNumber(FK) lineNumber quantity unitPrice total productNumber(FK)

product productNumber description unitPrice quantityOnHand

What would the relationship be between these entities and the previous entities we created earlier?

Creating The Relationships

Customer

Invoice

Now let’s put it all together into a final E-R Diagram.

38

Completing the E-R Diagram seminar seminarID date time location title

seminar2customer seminarID (FK) emailAddress (FK)

customer emailAddress lastName firstName phone address

contact emailAddress (FK) Date contactNumber contactType seminarID (FK)

invoice invoiceNumber invoiceDate paymentType subtotal shipping tax total

lineItem invoiceNumber(FK) lineNumber quantity unitPrice total productNumber(FK)

product productNumber description unitPrice quantityOnHand

Summary • An E-R Diagram is used to represent entities and their relationships – Facilitates interaction between designers, developer, and users – Crows Foot Notation

• Establish proper relationships between entities – Cardinality – Bridge Tables

• Six steps for developing an ERD

39

Conclusion • Any Questions? • Next Time – Be prepared to discuss database security

40