Chapter 4 High-level Database Models

(1)

1

Chapter 4 High-level Database Models

 Entity/Relationship Models (E/R diagram)

 How to transfer to a relational model

 Unified Modeling Language (UML)

 Object Definition Language (ODL)

 Hot to Transfer them to a relational model

(2)

Introduction

 现实世界：客观存在的世界。

 信息世界：现实世界在人们头脑中的反映。

 机器世界：信息世界的信息在机器世界中以数据的形

式存放。

reality-》information world-》 machine world E-R data model relational model√

UML object-relational model ODL object-oriented model

(3)

3

Purpose of E/R Model

 Sketch database schema designs

 Includes some constraints, but not operations.

 Designs are pictures called entity- relationship diagrams.

 convert E/R designs to relational DB designs.

(4)

Entity/Relationship Model

 Entity like objects, =things

 Entity set like class = set of similar Entity or objects

 Attribute=property of entities in an entity set, similar to fields of a struct.

 Relation=connect two or more entity set In diagrams,

entity set : rectangle;

attribute: oval,

(5)

5

Relationships

1. Binary (relation between two entity sets)

2. Multiway (relation between more than two entity sets)

 Multiplicity of relationships: Express the number of entities to which

another entity can be associated via a relationship set.

(6)

Binary & Multiway Relationships

Students

ID name address

Course

taking

No. name teacher Assisting TA

Course

Students ^TA

Enrolls

(7)

7

Example: 3-Way Relationship

students

TAs

courses name

name dept

SID

name Teacher SID

Tutoring

Tutoring means that TAs help

certain students on certain

courses.

Students

Name TAs

Name Courses Name

LI HONG Peter DB Wang LI Tim DB Wang Wei Peter Java

(8)

Multiplicity of Relationships

Many-many Many-one One-one

• In a many-many relationship, an entity of either set can be connected to many entities of the other set. E.g., a bar sells many beers; a beer is sold by many bars.

• In a many-one relationship, each entity of the first set is

connected to at most one entity of the second set. But an entity of the second set can be connected to zero, one, or many

entities of the first set.

• In a one-one relationship, each entity of either entity set is

students courses

students

deptartment departments

chairman

(9)

9

Representing “Multiplicity”

 Show a many-one relationship by an arrow entering the “one” side.

 Show a one-one relationship by arrows entering both entity sets.

 Rounded arrow = “exactly one,” i.e., each entity of the first set is related to exactly one entity of the target set.

(10)

Example:

Many-Many and

Many - One Relationship

students Likes Courses

Favorite

Notice: two relationships connect the same entity sets, but are different.

(11)

11

Example:

One-One Relationship

Chairmans are Depts.

A department has one chairman.

A chairman should be in exactly

one department.

(12)

Attributes on Relationships

students enroll courses grade

 Grade depends jointly on students and courses

 Credit depends only on courses.

credit

(13)

13

Converting Multiway to 2 way

 Motivation: some model can not solve multiway relationship.

 Method:

1. Creating a new connecting E.S. to represent the rows of a relationship set

2. Many-one relationships from the connecting E.S. to the others

(14)

Example

students

courses tutoring TAs

grade

students TAs courses Tutoring

TheS TheT TheC

grade

Students

Name TAs

Name Courses Name

LI HONG Peter DB Wang LI Tim DB Wang LI Tim Java

Gra de

78 67 90

• Relationship Set: like an entity set.

• Each row: an entity

• TheS: many-to relationship

(15)

15

Roles

 an E.S.participates more than once in a relationship.

 Label edges with roles to distinguish.

students Girlfriends boy girl

Buddies

1 2

Buddies RelationshipSet Buddy1 Buddy2

Bob Ann Joe Sue Ann Bob Joe Moe

… Married RelationshipSet

Boy Girl Bob Ann Joe Sue

… …

(16)

Subclasses

 Subclass = special case = fewer entities = more properties

 Example

Graduate students are a kind of students with a supervisor.

(17)

17

E/R Subclasses

 Assume subclasses form a tree (no multiple inheritance)

 Isa triangles indicate the subclass relation.

students

Gra_student

name SID

supervisor isa Point to the

superclass

(18)

Different Subclass Viewpoints

 E/R viewpoint: E/R entities have

representatives in all subclasses to which they belong

 Rule: if entity e is represented in a subclass, then e is represented in the superclass. Its properties are the union of the properties of these E.S.

 Object-oriented viewpoint: An object (entity) belongs to exactly one class. It inherits properties of its superclasses.

(19)

19

Examples for both viewpoints

 ER viewpoint:

Its properties are the union of the

properties of these E.S.

students

Gra_students

name SID

supervisor isa YeZhe

students

Gra_students

name SID

supervisor isa ^YeZhe

 OO viewpoint:

subclass inherited all properties of super class

(20)

Keys

A key is a set of attributes such that no two entities agree on all these attributes.

 In E/R model, every E.S. must have a key.

It could have more than one key, but one set of attributes is the “designated”key.

 In E/R diagrams, you should underline all attributes of the designated key.

(21)

21

Example

students

Gra_

students

name SID

supervisor isa

Suppose SID is key for students

SID is also key for Gra_students. In general, key at root is key for all.

(22)

Example: A Multiattribute Key

course

dept number hours room

Possibly, hours+room also

forms a key, but we have not designed it as such.

(23)

23

Weak Entity Sets

an E.S. E’s key comes not (completely) from its own attributes, but from the keys of one or more E.S’s to which E is linked by a supporting many-one relationship.

 Represented by putting double rectangle around E and a double diamond around each supporting relationship.

 Many-one-ness of supporting relationship (includes 1-1) essential.

 “Exactly one” also essential, or else we might not be able to extract key attributes by

following the supporting relationship.

(24)

Example of Weak Entity Sets

 name is almost a key for football

players, but there might be two with the same name.

 number is certainly not a key, since players on two teams could have the same number.

 But number, together with the team name related to the player by Plays- on should be unique.

(25)

25

In E/R Diagrams

Room Located- Building in

Name No

• Double diamond for supporting many-one relationship.

• Double rectangle for the weak entity set.

Note: must be rounded

because each room locates in a building.

(rooms) number and (building) name is a key for Room

(26)

Summarization of weak entity set

Suppose E is a weak entity set, R is a

supporting relationship, F is the another entity set:

 The key of E consists of its own attributes and key attributes of F.

 R must be a many-one relationship.

 The attributes that F supplies for the key of E must be key attributes of F.

 Weakness can be chained.

 connecting entity set is a weak entity set.

(27)

27

Design Techniques

1. Avoid redundancy.

2. Limit the use of weak entity sets.

3. Don’t use an entity set when an attribute will do.

(28)

Avoiding Redundancy

 Redundancy = saying the same thing in two (or more) different ways.

 Wastes space and (more importantly) encourages inconsistency.

 Two representations of the same fact become inconsistent if we change one and forget to change the other.

 Recall anomalies due to FD’s.

(29)

29

Example: Which one is better?

students name

study Depts

name addr

students name Dept.

Dept addr

students name

study Depts

name addr dept.

1

2

3

1:good

2:repeats dept address for each students in the department.

3:dept’s name said twice.

(30)

30

Entity Sets Vs. Attributes

students study Dept.

name name

Wrong ??

Students

name Dept.

Right !!

(31)

31

Intuitive Rule for E.S. vs.

Attribute

Make an entity set only if it either:

1. Is more than a name of something;

i.e., it has nonkey attributes or relationships with a number of different entity sets, or

2. Is the “many” in a many-one relationship.

(32)

Example

students study Dept.

name name

Addr.

•Dept. Deserves to be an E.S. because we record addr, a nonkey attribute.

•students deserves to be an E.S. because it is at the end of the “many” end.

(33)

33

Don’t overuse Weak E.S.

 Create unique ID’s to compensate:

Product-ID, Player-ID，etc.

 Weak E.S.’s are necessary when:

1. Such ID’s are not easily created; e.g.,

“species ID” as part of the standard nomenclature.

2. No global authority to create them, e.g., crews and studios

(34)

Constraints in the E/R Model

 Key constraints

 Single-value constraints

 Referential integrity constraints

 Other constraints

Constraints are part of the model.

(35)

35

Key constraints

 No two entities may agree in their values for all of the attributes that constitute a key.

 A key may consist of more than one attribute.

 There can also be more than one possible key for an entity set.

(36)

Single-value constraints

 Many ways to express:

• Each attribute of an entity set has a single value. (not null)

• A relationship R that is many-one from entity set E to entity set F

implies a single-value constraint. (at most one, or exactly one)

(37)

37

Referential integrity constraints

Dept.

Run

Chairman

•Every chairman must duty for a department.

•Every department has at most one chairman, that means sometimes, no chairman.

•A rounded arrowhead indicate exactly one (not allowed zero)

CS, MA DS

Tim, John

(38)

Other constraints

 Domain constraints restrict the value of an attribute to be in a limited set.

 General constrains, such as placing a constraint on the degree of a

relationship, number constraints and so on.

(39)

39

Summary of Symbols Used in E-R Notation

Isa

(40)

Summary of E/R diagram

 Entity-Relationship Diagrams

Entities & Attributes & Relationships Binary and Multiway of Relationships Multiplicity, role of relationship

Weak Entity Sets, Supporting Relationship Subclasses, key

 Good Design

Faithfully represent Avoid redundancy

Choose appropriate elements

(41)

41

From E/R Diagrams to Relations

 Entity set -> relation.

 Attributes -> attributes.

 Relationships -> relations whose attributes are only:

 The keys of the connected entity sets.

 Attributes of the relationship itself.

(42)

Entity Set -> Relation

Relation: Students(name, Dept)

students

name Dept

(43)

43

Relationship -> Relation

students Likes courses

Likes(student, course) Favorite

Favorite(student, course) Girlfriend

boy

Girl

Girlfriend(Boy, Girl)

name addr name Dept

Buddies 1 2

Buddies(name1, name2)

(44)

Combining Relations

 OK to combine into one relation:

1. The relation for an entity-set E

2. The relations for many-one relationships of which E is the “many.”

 Example: Students(name, addr) and Favorite(student, course) combine to make students1(name, addr,

favCourse).

(45)

45

Risk with Many-Many Relationships

 Combining Courses with Courses would be a mistake. It leads to redundancy, as:

name addr courses

Sally 123 Maple Mathematics Sally 123 Maple English

Redundancy

(46)

Handling Weak Entity Sets

 Relation for a weak entity set must

include attributes for its complete key (including those belonging to other

entity sets), as well as its own, nonkey attributes.

 A supporting relationship is redundant and yields no relation (unless it has attributes).

(47)

Example: Weak Entity Set ->

Relation

 Rooms(name,number,size)

 Buildings(name)

 Located-in(number,name)

47

rooms Located- Buildings in

size number name

Located-in becomes part of rooms

(48)

Subclasses: Three Approaches

1. Object-oriented : One relation per

subset of subclasses, with all relevant attributes.

2. Use nulls : One relation; entities have NULL in attributes that don’t belong to them.

3. E/R style : One relation for each subclass:

 Key attribute(s).

(49)

49

Example: Subclass -> Relations

students

Gra- student

isa

name SID

supervisor

(50)

Object-Oriented

students^: ^name ^SID

LI hone 030001

Graduates:

name SID Supervior YeZhe 030987 LF

Good for queries like “find the

supervisor of YeZhe”

(51)

Object-Oriented (cont.)

 Converting isa-hierarchies to relations is to enumerate all the possible

subtrees of the hierarchy.

51

students

Graduates isa

name SID

supervisor Auditer isa

from

Students(name,SID)

Graduates(name,SID,supervisor) Auditer(name,SID,from)

Allstudents(name,SID,supervisor,from)

(52)

E/R Style

Good for queries like “find all students (including Graduates students)”

LI Hone 030001 YeZhe 030987

students Graduates

Yezhe 030987 LF

(53)

53

Using Nulls

name SID Supervisor Li hone 030001 NULL Yezhe 030987 LF

Saves space unless there are lots

of attributes that are usually NULL.

students:

(54)

Homework

 Read section 4.6.1 and 4.6.2