Page 220 -

219

Q5-6 How Is a Data Model Transformed into a Database Design?
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 row.
To determine the adviser’s students, we search the AdviserName column in the Student table to
determine which rows have that adviser’s name. If a student changes advisers, we simply change
the value in the AdviserName column. Changing Jackson to Jones in the first row, for example, will
assign student 100 to Professor Jones.
For this data model, placing StudentNumber in Adviser would be incorrect. If we were to do
that, we could assign only one student to an adviser. There is no place to assign a second adviser.
This strategy for placing foreign keys will not work for N:M relationships, however.
Consider the data model in Figure 5-27a; here advisers and students have a many-to-many
relationship. An adviser may have many students, and a student may have multiple advisers
(for multiple majors).
To see why the foreign key strategy we used for 1:N relationships will not work for N:M rela-
tionships, examine Figure 5-27b. If student 100 has more than one adviser, there is no place to
record second or subsequent advisers.
To represent an N:M relationship, we need to create a third table, as shown in Figure 5-27c.
The third table has two columns, AdviserName and StudentNumber. Each row of the table means
that the given adviser advises the student with the given number.
As you can imagine, there is a great deal more to database design than we have presented
here. Still, this section should give you an idea of the tasks that need to be accomplished to
create a database. You should also realize that the database design is a direct consequence
of decisions made in the data model. If the data model is wrong, the database design will be
wrong as well.

Users’ Role in the Development of Databases
As stated, a database is a model of how the users view their business world. This means that the
users are the final judges as to what data the database should contain and how the records in that
database should be related to one another.
The easiest time to change the database structure is during the data modeling stage. Changing
a relationship from one-to-many to many-to-many in a data model is simply a matter of changing
the 1:N notation to N:M. However, once the database has been constructed and loaded with data
and forms, reports, queries, and application programs have been created, changing a one-to-many
relationship to many-to-many means weeks of work.
You can glean some idea of why this might be true by contrasting Figure 5-26c with
Figure 5-27c. Suppose that instead of having just a few rows, each table has thousands of rows;
in that case, transforming the database from one format to the other involves considerable work.
Even worse, however, is that someone must change application components as well. For example,
if students have at most one adviser, then a single text box can be used to enter AdviserName.
If students can have multiple advisers, then a multiple-row table will need to be used to enter
AdviserName and a program will need to be written to store the values of AdviserName into the
Adviser_Student_Intersection table. There are dozens of other consequences, consequences that
will translate into wasted labor and wasted expense.
Thus, user review of the data model is crucial. When a database is developed for your use, you
must carefully review the data model. If you do not understand any aspect of it, you should ask for
clarification until you do. Entities must contain all of the data you and your employees need to do your