Database Reference
In-Depth Information
assigned the task of collecting insects for a class project at school. For each insect, Junior
is to record its name (“ant,” “bee,” and so forth), and its date and location of collection.
You have expounded the benefits of MySQL for record-keeping to Junior since his early
days, so upon your arrival home from work that day, he immediately announces the
necessity of completing this project and then, looking you straight in the eye, declares
that it's clearly a task for which MySQL is well-suited. Who are you to argue? So the two
of you get to work. Junior already collected some specimens after school while waiting
for you to come home and has recorded the following information in his notebook:
Name
Date
Origin
millipede
2014-09-10
driveway
housefly
2014-09-10
kitchen
grasshopper
2014-09-10
front yard
stink bug
2014-09-10
front yard
cabbage butterfly
2014-09-10
garden
ant
2014-09-10
back yard
ant
2014-09-10
back yard
termite
2014-09-10
kitchen woodwork
Looking over Junior's notes, you're pleased to see that even at his tender age, he has
learned to write dates in ISO format. However, you also notice that he's collected a
millipede and a termite, neither of which actually are insects. You decide to let this pass
for the moment; Junior forgot to bring home the written instructions for the project,
so at this point it's unclear whether these specimens are acceptable. (You also note with
some alarm Junior's discovery of termites in the house and make a mental note to call
the exterminator.)
As you consider how to create a table to store this information, it's apparent that you
need at least
name
,
date
, and
origin
columns corresponding to the types of information
that Junior is required to record:
CREATE
TABLE
insect
(
name
VARCHAR
(
30
)
NOT
NULL
,
#
type
of
insect
date
DATE
NOT
NULL
,
#
date
collected
origin
VARCHAR
(
30
)
NOT
NULL
#
where
collected
);
However, those columns are insufficient to make the table easy to use. Note that the
records collected thus far are not unique; both ants were collected at the same time and
place. If you put the information into an
insect
table that has the structure just shown,
neither ant row can be referred to individually because there's nothing to distinguish
one from another. Unique IDs would be helpful to make the rows distinct and to provide
