Database Reference
In-Depth Information
NOTE
MySQL also allows you to specify primary keys, foreign keys, and integrity constraints. To do so, you would use SQL com-
mands that were presented in Chapter 4 for these purposes.
SUPPORT DATA INDEPENDENCE
A DBMS must provide facilities to support the independence of programs from the structure of a database.
One of the advantages of working with a DBMS is data independence, which is a property that lets you
change the database structure without requiring you to change the programs that access the database. What
types of changes could you or a DBA make to the database structure? A few of these changes are adding a
field, changing a field property (such as length), creating an index, and adding or changing a relationship.
The following sections describe the data independence considerations for each type of change.
250
Adding a Field
If you add a new field to a database, you don
t need to change any program except, of course, those programs
using the new field. However, when a program uses an SQL SELECT * FROM command to select all the fields
from a given table, you are presented with an extra field. To prevent this from happening, you need to change
the program to restrict the output to only the desired fields. To avoid the imposition of this extra work, you
should list all the required fields in an SQL SELECT command instead of using the *.
'
Changing the Length of a Field
In general, you don
ve changed the length of a field; the DBMS han-
dles all the details concerning this change in length. However, if a program sets aside a certain portion of the
screen or a report for the field, and the length of the field has increased to the point where the previously
allocated space is inadequate, you
'
t need to change programs because you
'
'
ll need to change the program.
Creating an Index
To create an index, you enter a simple SQL command or select a few options. Most DBMSs use the new index
automatically for all updates and queries. For some DBMSs, you might need to make minor changes in
already existing programs to use the new index.
Adding or Changing a Relationship
In terms of data independence considerations, adding or changing a relationship is the trickiest of all and is
best illustrated with an example. Suppose Premiere Products now has the following requirements:
Customers are assigned to territories.
Each territory is assigned to a single rep.
A rep can have more than one territory.
A customer is represented by the rep who covers the territory to which the customer is
assigned.
To implement these changes, you need to restructure the database. The previous one-to-many relation-
ship between the Rep and Customer tables is no longer valid. Instead, there
s now a one-to-many relationship
between the Rep table and the new Territory table, and a one-to-many relationship between the Territory
table and the Customer table, as follows:
'
Rep (RepNum, LastName, FirstName, Street, City, State,
Zip, Commission, Rate)
Territory (TerritoryNum, TerritoryDesc, RepNum)
Customer (CustomerNum, CustomerName, Street, City, State,
Zip, Balance, CreditLimit, TerritoryNum)
