Page 217 - Using MIS

Q6 How Is a Data Model Transformed into a Database Design? 185

For example, consider the E-R diagram in Figure 5-26a. The Adviser entity has a 1:N rela-
tionship to the Student entity. To create the database design, we construct a table for Adviser
and a second table for Student, as shown in Figure 5-26b. The key of the Adviser table is
AdviserName, and the key of the Student table is StudentNumber.
Further, the EmailAddress attribute of the Adviser entity becomes the EmailAddress column
of the Adviser table, and the StudentName and MidTerm attributes of the Student entity become
the StudentName and MidTerm columns of the Student table.
The next task is to represent the relationship. Because we are using the relational model, we
know that we must add a foreign key to one of the two tables. The possibilities are: (1) place the
foreign key StudentNumber in the Adviser table or (2) place the foreign key AdviserName in the
Student table.
The correct choice is to place AdviserName in the Student table, as shown in Figure 5-26c.
To determine a student’s adviser, we just look into the AdviserName column of that student’s

Adviser Student
AdviserName StudentNumber
EmailAddress StudentName
MidTerm
(a) 1:N Relationship Between Adviser and Student Entities

Adviser Table—Key is AdviserName
AdviserName EmailAddress
Jones Jones@myuniv.edu
Choi Choi@myuniv.edu
Jackson Jackson@myuniv.edu

Student Table—Key is StudentNumber
StudentNumber StudentName MidTerm
100 Lisa 90
200 Jennie 85
300 Jason 82
400 Terry 95
(b) Creating a Table for Each Entity
Adviser Table—Key is AdviserName
AdviserName EmailAddress

Jones Jones@myuniv.edu
Choi Choi@myuniv.edu Foreign Key
Jackson Jackson@myuniv.edu Column
Represents
Student—Key is StudentNumber Relationship

StudentNumber StudentName MidTerm AdviserName
100 Lisa 90 Jackson
200 Jennie 85 Jackson
300 Jason 82 Choi
400 Terry 95 Jackson
Figure 5-26
Representing a 1:N Relationship (c) Using the AdviserName Foreign Key to Represent the 1:N Relationship