PHP 5/MySQL Programming- P81 doc
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (127.26 KB, 5 trang )
Interpreting the agent_specialty
Table with a Query
Of course, the agent_specialty table is not directly useful to the user, because it
contains nothing but foreign key references. You can translate the data to some-
thing more meaningful with an SQL statement:
SELECT agent_specialtyID,
agent.name AS ‘agent’,
specialty.name AS ‘specialty’
FROM agent_specialty,
agent,
specialty
378
P
H
P
5
/M
y
S
Q
L
P
r
o
g
r
a
m
m
i
n
g
f
o
r
t
h
e
A
b
s
o
l
u
t
e
B
e
g
i
n
n
e
r
Specialty ID Name
0 Electronics
1 Counterintelligence
2 Sabotage
3 Doily Design
4 Explosives
5 Flower Arranging
TABLE 11.7 THE SPECIALTY TABLE
Agent Specialty ID Agent ID Specialty ID
112
213
321
426
532
644
745
TABLE 11.8 THE AGENT_ SPECIALTY TABLE
WHERE agent.agentID = agent_specialty.agentID
AND specialty.specialtyID = agent_specialty.specialtyID;
It requires two comparisons to join the three tables. It is necessary to forge the
relationship between
agent and agent_specialty by common agentID values. It’s
also necessary to secure the bond between
specialty and agent_specialty by
comparing the
specialtyID fields. The results of such a query show that the cor-
rect relationships have indeed been joined, as you can see in Table 11.9.
The link table provides the linkage between tables that have many-to-many
relationships. Each time you want a new relationship between an agent and a
specialty, you add a new record to the
agent_specialty table.
Creating Queries That Use Link Tables
Whenever you want to know about the relationships between agents and special-
ties, the data is available in the
agent_specialty table. For example, if you need to
know which agents know flower arranging, you can use the following query:
SELECT
agent.name
FROM
agent,
specialty,
agent_specialty
WHERE agent.agentID = agent_specialty.agentID
AND agent_specialty.specialtyID = specialty.specialtyID
AND specialty.name = ‘Flower Arranging’;
379
C
h
a
p
t
e
r
1
1
D
a
t
a
N
o
r
m
a
l
i
z
a
t
i
o
n
Agent Specialty ID Agent Specialty
1 Bond Sabotage
2 Bond Doily Design
3 Falcon Counterintelligence
5 Cardinal Sabotage
6 Blackford Explosives
7 Blackford Flower Arranging
TABLE 11.9 QUERY I NTERPRETATION OF
AGENT
_ SPECIALTY TABLE
380
P
H
P
5
/M
y
S
Q
L
P
r
o
g
r
a
m
m
i
n
g
f
o
r
t
h
e
A
b
s
o
l
u
t
e
B
e
g
i
n
n
e
r
This query looks a little scary, but it really isn’t bad. This query requires data from
three different tables. The output needs the
name from the agent table. I don’t
want to remember what specialty number is associated with
Flower Arranging, so
I let the query look that up from the
specialty table. Since I need to know which
agent is associated with a particular specialty, I use the
agent_specialty table to
link up the other two tables. The
WHERE clause simply provides the joins.
The following phrase cements the relationship between
agents and agent_specialty:
agents.agentID = agent_specialty.agentID
Likewise, the following ensures the connection between specialties and
agent_specialty:
agent_specialty.specialtyID = specialties.specialtyID
The last part of the WHERE clause is the actual conditional part of the query that
only returns records where the specialty is flower arranging. (You know, flower
arrangement can be a deadly art in the hands of a skilled practitioner.)
It might be helpful to imagine the ER diagram when building queries. If two
tables have lines between them, use a
WHERE clause to represent each line. To repli-
cate a one-to-many join (with one line and two tables) you need one
WHERE line to
handle the connection. If creating a many-to-many join with a link table, you
need a compound condition to handle connecting each table to the link table.
You can then add any other conditions that help you narrow the query.
Summary
In this chapter you move beyond programming to an understanding of data, the
real fuel of modern applications. You learn how to take a poorly designed table
and convert it into a series of well-organized tables that can avoid a lot of data
problems. You learn about three stages of normalization and how to build an
Entity Relationship diagram. You can recognize three kinds of relationships
between entities and build normalized tables in SQL, including pointers for pri-
mary and foreign keys. You can connect normalized tables with
INNER JOIN SQL
statements. You know how to simulate a many-to-many relationship by building
a link table. The civilized world is safer for your efforts.
381
C
h
a
p
t
e
r
1
1
D
a
t
a
N
o
r
m
a
l
i
z
a
t
i
o
n
CHALLENGES
1. Locate ER diagrams for data you work with every day. (Check with your
company’s Information Technology department, for example.) Examine
these documents and see if you can make sense of them.
2. Examine a database you use regularly. Determine if it follows the require-
ments stated in this chapter for a well-designed data structure. If not,
explain what might be wrong with the data structure and how it could be
corrected.
3. Diagram an improved data structure for the database you examined in
Question 2. Create the required tables in SQL and populate them with sam-
ple data.
4. Design a database for data you use every day. (Be warned, most data prob-
lems are a lot more complex than they first appear.) Create a data diagram;
then build the tables and populate with sample data.
This page intentionally left blank
