Databases Reference
In-Depth Information
NOTE
MySQL also allows you to specify primary keys, foreign keys, and integrity constraints. To do so, you would use the SQL com-
mands that were presented in Chapter 4 for these purposes.
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, chang-
ing a field property (such as length), creating an index, and adding or changing a relationship. The following sec-
tions describe the data independence considerations for each type of change.
246
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 *.
In general, you don't need to change programs because you've changed the length of a field; the DBMS handles
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 allo-
cated space is inadequate, you'll need to change the program.
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.
In terms of data independence considerations, adding or changing a relationship is 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 relation-
ship 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, TerritoryDescription, RepNum)
Customer (CustomerNum, CustomerName, Street, City, State,
Zip, Balance, CreditLimit, TerritoryNum)
