Database Reference
In-Depth Information
| 5 | Lakes, Rivers, Ponds |
| 6 | Marshes, Swamps |
| 7 | Mountains |
| 8 | Oceans |
| 9 | Urban |
+------------+----------------------+
Create a second table named
bird_habitats
. Name the first column
bird_id
and the second column
habitat_id
. Set the column type for both of
them to
INT
. Don't make either column an indexed column.
When you're finished creating both of these tables, execute the
DESCRIBE
and
SHOW CREATE TABLE
statements for each of the two tables. Notice what in-
formation is presented by each statement, and familiarize yourself with the struc-
ture of each table and the components of each column.
Use the
RENAME TABLE
statement to rename the
bird_habitats
to
birds_habitats
(i.e., make
bird
plural). This SQL statement was covered in
3. Using the
ALTER TABLE
statement, add an index based on both
bird_id
and
using the
INDEX
keyword, use
UNIQUE
so that duplicates are not allowed. Call
the index
birds_habitats
.
Execute the
SHOW CREATE TABLE
statement for this table when you're fin-
ished altering it.
At this point, you should enter some data in the
birds_habitats
table. Ex-
ecute these two
SELECT
statements, to see what data you have in the
birds
and
habitat_codes
tables:
SELECT
bird_id
,
common_name
FROM
birds
;
SELECT
*
FROM
habitat_codes
;
The results of the first
SELECT
statement should show you a row for a loon and
one for a duck, along with some other birds. Both the loon and the duck can be
found in lakes, but ducks can also be found in marshes. So enter one row for the
loon and two rows for the duck in the
birds_habitats
table. Give the value
of the
bird_id
for the loon, and the value of
habitat_id
for
Lakes, Rivers,
Ponds
. Then enter a row giving the
bird_id
for the duck, and the value again
of the
habitat_id
for lakes. Then enter a third row giving again the
bird_id
for the duck and this time the
habitat_id
for
Marshes, Swamps
. If you created
