Моделирование бизнес-процессов / Моделирование бизнес-процессов / ER-диаграмы / 0849315484 Entity-Relationship Diagrams

Figure 4.9: Sample Problem: Alternate Presentation of Attributes with Explanation and Sample Data

Step 6: State the exact nature of the relationships in structured English from all sides. For example, if a relationship is A:B::1:M, then there is a relationship from A(1) to B(M) and from B(M) back to A(1).

Pattern 1 — M:1, from the M side, full participation

x, which are recorded in the database, must be related to one and only one y. No x are related to more than one y.

x = passenger, y = flight, relationship = fly

Passengers, which are recorded in the database, must fly on one and only one flight. No passenger flies on more than one flight.

Pattern 3 — 1:M, from the 1 side, full participation

x, which are recorded in the database, must be related to many (one or more) y's.

x = flight, y = passenger, relationship = fly

Flights, which are recorded in the database, must fly many (one or more) passengers.

Attribute descriptions follow previous patterns.

Step 7: Present the "as designed" database to the user, complete with the English for entities, attributes, keys, and relationships. Refine the diagram as necessary.

Step 8: Show some sample data.

See Figure 4.9.

[1]Modeled after Elmasri and Navathe (2000).

Mapping Relationships to a Relational Database

In this section we will continue with the mapping rules that we began at the end of Chapter 2. In Chapter 2 we learned how to map entities, entities with composite attributes, and entities with multi-valued attributes. In this chapter, having covered structural constraints of relationships, we will learn how to map relationships.

1.Identify the entities: Passenger, Flight

2.Add attributes to entities, identifying primary keys:

Passenger (name[last, first, mi]. frequent flier #, # articles of luggage) Flight (flight #, destination, depart time, arrive time)

3.What relationship is there between Passengers and Flights? Passengers fly on flights.

Our first mapping rule for mapping relationships maps binary M:N relationships.

M3a — For binary M:N relationships: For each M:N relationship, create a new table (relation) with the primary keys of each of the two entities (owner entities) that are being related in the M:N relationship. The key of this new table will be the concatenated keys of the owner entities. Include any attributes that the M:N relationship may have in this new table.

For example, refer to Figure 4.6. If the STUDENT and COURSE tables have the following data:

STUDENT

name.first name.last		name.mi	student_number		address
Richard	Earp	W	589		222 2nd
					St.
Boris	Backer		909		333
					Dreistrasse
Helga	Hogan	H	384		88 Half
					Moon Ave.
Arpan	Bagui	K	876		33 Bloom
					Ave
Hema	Malini		505		100
					Livingstone
	COURSE
cname	c_number credit_hrs
Database	COP4710		4
Visual Basic	CGS3464		3

Elements of Stats	STA3023	3
Indian History	HIST2022	4

Before performing the M3a mapping rule, one must first insure that the primary keys of the entities involved have been established. If student_number and c_number are the primary keys of STUDENT and COURSE, respectively, then to map the M:N relationship, we create a relation called ENROLL, as follows:

ENROLL

c_number student_number

COP4710 589

CGS3464 589

CGS3464 909

STA3023 589

HIST2022 384

STA3023 505

STA3023 876

HIST2022 876

HIST2022 505

Both c_number and student_number together are the primary key of the relation, ENROLL.

Our next set of mapping rules for mapping relationships maps binary 1:1 relationships:

M3b — For binary 1:1 relationships: Include the primary key of Entity A into EntityB as the foreign key.

The question is: which is EntityA and which is EntityB? This question is answered in the next three mapping rules: M3b_1, M3b_2, and M3b_3.

M3b_1 — For binary 1:1 relationships, if one of the sides has full participation in the relationship, and the other has partial participation, then store the primary key of the side with the partial participation constraint on the side with the full participation constraint. Include any attributes of the relationship on the side that gets the primary key (the primary key now becomes the Foreign key in the new relation).

For example, refer to Figure 4.2. It says:

An automobile, recorded in the database, must be driven by one and only one student.

and

A student may drive one and only one automobile.

Here, the full participation is on the AUTOMOBILE side since "An automobile ‘must’ be driven by a student."

So we take the primary key from the partial participation side, STUDENT, and include it in the AUTOMOBILE table. The primary key of STUDENT is student_number, so this will be stored in the AUTOMOBILE relation as the foreign key. A relational database realization of the ER diagram in Figure 4.2 with some data would look like this:

AUTOMOBILE

vehicle_id	make	body_style color year			student_number
A39583	Ford	Compact	Blue	1999	589
B83974	Chevy	Compact	Red	1989	909
E98722	Mazda	Van	Green	2002	876
F77665	Ford	Compact	White	1998	384

STUDENT

name.first	name.last	name.mi	student_number	address
Richard	Earp	W	589	222 2nd St
Boris	Backer		909	333
				Dreistrasse
Helga	Hogan	H	384	88 Half
				Moon Ave
Arpan	Bagui	K	876	33 Bloom
				Ave
Hema	Malini		505	100
				Livingstone

Since STUDENT has a multi-valued attribute school, we need the table below to map the multi-valued attribute.

Name-School

student_number	school
589	St. Helens
589	Mountain
589	Volcano
909	Manatee U
909	Everglades High
384	PCA

384	Pensacola High
876	UWF
505	Cuttington
505	UT

In this case, if the relationship had any attributes, it would be included in the relation, AUTOMOBILE.

M3b_2 — For binary 1:1 relationships, if both sides have partial participation constraints, there are three alternative ways to implement a relational database:

M3b_2a — First alternative — you may select either one of the relations to store the key of the other (and live with some null values).

M3b_2b — Second alternative — depending on the semantics of the situation, you can create a new relation to house the relationship that would contain the key of the two related entities (as is done in M3a).

Again refer to Figure 4.1, here we assume that the participation constraints are partial from both sides, and assume that there is no school attribute. Then Figure 4.1 would read:

An automobile may be driven by one and only one student.

and

A student may drive one and only one automobile.

The relational realization could be [take the vehicle_id (primary key of AUTOMOBILE) and store it in STUDENT, as shown below]:

	AUTOMOBILE
vehicle_id make body_style			color	year
A39583	Ford	Compact	Blue	1999
B83974	Chevy	Compact	Red	1989
E98722	Mazda	Van	Green	2002
F77665	Ford	Compact	White	1998
G99999	Chevy	Van	Grey	1989
			STUDENT
name.first	name.last	name.mi	student_number			address	vehicle_id
Richard	Earp	W		589		222 2nd St	A39583
Boris	Backer			909		333	B83974

				Dreistrasse
Helga	Hogan	H	384	88 Half	F77665
				Moon Ave
Arpan	Bagui	K	876	33 Bloom	E98722
				Ave
Hema	Malini		505	100	Livingstone

In the STUDENT relation, vehicle_id is the foreign key.

M3b_2c — The third way of implementing this 1:1 binary relationship with partial participation on both sides would be to create a new table (relation) with just the keys from the two tables STUDENT and AUTOMOBILE, in addition to the two tables, STUDENT and AUTOMOBILE. In this case we would map the relations as we did in the binary M:N case; and if there were any null values, these would be left out of the linking table, as shown below:

STUDENT-AUTOMOBILE

vehicle_id student_number

A39583 589

B83974 909

E98722 876

F77665 384

In this case, the two relations STUDENT and AUTOMOBILE would remain as:

STUDENT

name.first	name.last	name.mi	student_number	address
Richard	Earp	W	589	222 2nd St
Boris	Backer		909	333
				Dreistrasse
Helga	Hogan	H	384	88 Half
				Moon Ave
Arpan	Bagui	K	876	33 Bloom
				Ave
Hema	Malini		505	100
				Livingstone

AUTOMOBILE

vehicle_id make body_style color year

A39583	Ford	Compact	Blue	1999
B83974	Chevy	Compact	Red	1989
E98722	Mazda	Van	Green	2002
F77665	Ford	Compact	White	1998
G99999	Chevy	Van	Grey	1989

M3b_3 — For binary 1:1 relationships, if both sides have full participation constraints, you may use the semantics of the relationship to select which of the relations should contain the key of the other. It would be inappropriate to include foreign keys in both tables as you would be introducing redundancy in the database. Include any attributes on the relationship, on the relation that is getting the foreign key.

Now assuming full participation on both sides of Figure 4.1, the two tables STUDENT and AUTOMOBILE could be:

STUDENT

name.first	name.last	name.mi	student_number	address
Richard	Earp	W	589	222 2nd St
Boris	Backer		909	333
				Dreistrasse
Helga	Hogan	H	384	88 Half
				Moon Ave
Arpan	Bagui	K	876	33 Bloom
				Ave
Hema	Malini		505	100
				Livingstone

AUTOMOBILE

vehicle_id make body_style			color	year	student_number
A39583	Ford	Compact	Blue	1999	589
B83974	Chevy	Compact	Red	1989	909
E98722	Mazda	Van	Green	2002	876
F77665	Ford	Compact	White	1998	384
G99999	Chevy	Van	Grey	1989	505

In this above case, the student_number was included in AUTOMOBILE, making student_number a foreign key in AUTOMOBILE. We could have also taken the primary key from AUTOMOBILE, vehicle_id, and included that in STUDENT table.

In this case, if the relationship had any attributes, these would have been stored in AUTOMOBILE, along with student_number.

The next set of mapping relationships maps binary 1:N relationships:

M3c — For binary 1:N relationships, we have to check what kind of participation constraints the N side of the relationship has:

M3c_1 — For binary 1:N relationships, if the N-side has full participation, include the key of the entity from the 1 side, in the relation on the N side as a foreign key.

For example, in Figure 4.4 if we assume full participation on the student side, we will have:

Dorm rooms may have zero or more students.

and

Students must live in one and only one dorm room. The relational realization would be:

STUDENT

name.first	name.last	name.mi	student_number	dorm
Richard	Earp	W	589	A
Boris	Backer		909	C
Helga	Hogan	H	384	A
Arpan	Bagui	K	876	A
Hema	Malini		505	B

DORM

dname supervisor

ASaunders

BBacker

CHogan

DEisenhower

Here, the full participation is on the N side, that is, on the STUDENT entity side. So, we take the key from DORM, dname, and include it in the STUDENT relation. In this case, if the relationship had an attribute, it would be included in STUDENT, the N side.

M3c_2 — For binary 1:N relationships, if the N-side has partial participation, the 1:N relationship is handled just like a binary M:N relationship with a separate table for the relationship. The key of the new relation consists of a concatenation of the keys of the related entities. Include

any attributes that were on the relationship, on this new table.

Checkpoint 4.4

1.State the mapping rules that would be used to map Figure 4.5? Map Figure 4.5 to a relational database and show some sample data.

2.State the mapping rules that would be used to map Figure 4.8? Map Figure 4.8 to a relational database and show some sample data.

Chapter Summary

This chapter discussed cardinality and participation ratios in ER diagrams. Several examples and diagrams of binary relationships with structural constraints (developed in the Chen-like model) were discussed. Tighter English grammar was presented for each of the diagrams, and steps 7 and 8 of the ER design methodology were defined. The final section of the chapter discussed mapping relationships.