The 24-Mar assignment in Database Systems I class was to incorporate the feedback on the Database Design that we presented on 10-March and create the corresponding tables and sample data in our MySQL database. (The design I presented is documented in my Data Design files on bob.marlboro.edu).
This page documents the tables and sample data that I'm experimenting with as part of my Virtual Wanderer Capstone Project.
I created the Virtual Wanderer database "vw", and used files containing SQL commands to create tables that within the database.
##### as root (and kid) on carson-city.marloro.edu, my Linux server ###### [kid@carson-city kid]$ su Password: [root@carson-city kid]# cd /usr/local/mysql/bin [root@carson-city bin]# ./mysqladmin create vw [root@carson-city bin]# ./mysql -t vw < /home/kid/db1/mar24/vwtable_create.sql > /home/kid/db1/mar24/vwtable_output.txt [kid@carson-city mar24]$ cd /home/kid/db1/mar24/ [kid@carson-city mar24]$ cat vwtable_output.txt +--------------+ | Tables_in_vw | +--------------+ | destinations | | navlogs | | triplegs | | vessels | | views | | waypoints | +--------------+ +--------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+ | destinations | MyISAM | Fixed | 0 | 0 | 0 | 107374182399 | 1024 | 0 | NULL | 2001-03-25 19:32:59 | 2001-03-25 19:32:59 | NULL | | | | navlogs | MyISAM | Fixed | 0 | 0 | 0 | 914828034047 | 1024 | 0 | NULL | 2001-03-25 19:32:59 | 2001-03-25 19:32:59 | NULL | | | | triplegs | MyISAM | Fixed | 0 | 0 | 0 | 227633266687 | 1024 | 0 | NULL | 2001-03-25 19:32:59 | 2001-03-25 19:32:59 | NULL | | | | vessels | MyISAM | Fixed | 0 | 0 | 0 | 193273528319 | 1024 | 0 | NULL | 2001-03-25 19:32:59 | 2001-03-25 19:32:59 | NULL | | | | views | MyISAM | Fixed | 0 | 0 | 0 | 1172526071807 | 1024 | 0 | NULL | 2001-03-25 19:32:59 | 2001-03-25 19:32:59 | NULL | | | | waypoints | MyISAM | Fixed | 0 | 0 | 0 | 1481763717119 | 1024 | 0 | NULL | 2001-03-25 19:32:59 | 2001-03-25 19:32:59 | NULL | | | +--------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+ [kid@carson-city mar24]$ mysql -u bluhrs -ppasswd vw ..... mysql> select * from waypoints; Empty set (0.00 sec) .... #### from my bluhrs account on bob.marlboro.edu ####################### bash-2.04$ mysql -h carson-city -u bluhrs -ppasswd vw ..... mysql> show tables; +--------------+ | Tables_in_vw | +--------------+ | destinations | | navlogs | | triplegs | | vessels | | views | | waypoints | +--------------+ 6 rows in set (0.00 sec) mysql> exit
Next, I connected to the "vw" database on carson-city.marloro.edu, my Linux server, from my account on bob.marloro.edu and used a series of comma-separated-values files with the LOAD DATA LOCAL INFILE SQL command to insert add a sample set of records to each of the tables.
Since I'm still in the experimenting stage, I just used a DELETE FROM table WHERE id=0; to clean them up. Any suggestions would welcome at this point. I'm also not sure why there were warnings each time.
The following CSV-files were used to populate the tables the database.
WAYPOINTS Table
#### from my bluhrs account on bob.marlboro.edu #######################
# Connect from bob.marlboro.edu to the vw databased on my Linux server:
bash-2.04$ pwd
/home/bluhrs/html/db1/mar24
bash-2.04$ mysql -h carson-city -u bluhrs -ppasswd vw
.....
mysql> LOAD DATA LOCAL INFILE '/home/bluhrs/html/db1/mar24/wayptdata.csv'
-> INTO TABLE waypoints FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\r';
Query OK, 10 rows affected (0.01 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 18
mysql> select * from waypoints;
+-------------+------------------------------------------+-------------+-------------+--------------+--------------+---------------------+-------------------------------------------------------+--------------+
| waypoint_id | waypoint_name | lat_degrees | lat_minutes | long_degrees | long_minutes | image_file | descript_URL | last_edit |
+-------------+------------------------------------------+-------------+-------------+--------------+--------------+---------------------+-------------------------------------------------------+--------------+
| 1 | Govenors Is. Nun Buoy #2 | 43 | 37.169 | 71 | 26.03 | images/lakew/lwgis2 | http://server.marlboro.edu/capstone/lakew/lwgis2.html | 000000000000 |
| 2 | (Alton Bay) Little Mark Is. Nun Bouy #22 | 43 | 31.902 | 71 | 15.526 | images/lakew/lwlm22 | http://server.marlboro.edu/capstone/lakew/lwlm22.html | 000000000000 |
| 3 | Rattlesnake Is. Can Buoy #25 NW Shore | 43 | 34.632 | 71 | 18.232 | images/lakew/lwri25 | http://server.marlboro.edu/capstone/lakew/lwri25.html | 000000000000 |
| 4 | Rattlesnake Is. off NE Shore | 43 | 33.721 | 71 | 16.124 | images/lakew/lwrine | http://server.marlboro.edu/capstone/lakew/lwrine.html | 000000000000 |
| 5 | Silver Sands Marina Pier (Sanders Bay) | 43 | 34.964 | 71 | 24.363 | images/lakew/lwssnd | http://server.marlboro.edu/capstone/lakew/lwssnd.html | 000000000000 |
| 6 | Witches - East side | 43 | 35.95 | 71 | 23.478 | images/lakew/lwwite | http://server.marlboro.edu/capstone/lakew/lwwite.html | 000000000000 |
| 7 | Witches - West side | 43 | 35.974 | 71 | 24.342 | images/lakew/lwwitw | http://server.marlboro.edu/capstone/lakew/lwwitw.html | 000000000000 |
| 8 | Welch Is. Can Buoy #76 | 43 | 35.908 | 71 | 21.079 | images/lakew/lwwl76 | http://server.marlboro.edu/capstone/lakew/lwwl76.html | 000000000000 |
| 9 | Weirs Beach Bay Can Buoy #1 | 43 | 36.726 | 71 | 26.985 | images/lakew/lwwrb1 | http://server.marlboro.edu/capstone/lakew/lwwrb1.html | 000000000000 |
| 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 000000000000 |
+-------------+------------------------------------------+-------------+-------------+--------------+--------------+---------------------+-------------------------------------------------------+--------------+
mysql> delete from waypoints where waypoint_id=0;
mysql> show columns from waypoints;
+---------------+---------------+------+-----+---------+-------+---------------------------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+---------------+---------------+------+-----+---------+-------+---------------------------------+
| waypoint_id | int(11) | | PRI | 0 | | select,insert,update,references |
| waypoint_name | char(64) | YES | | NULL | | select,insert,update,references |
| lat_degrees | int(11) | YES | | NULL | | select,insert,update,references |
| lat_minutes | float | YES | | NULL | | select,insert,update,references |
| long_degrees | int(11) | YES | | NULL | | select,insert,update,references |
| long_minutes | float | YES | | NULL | | select,insert,update,references |
| image_file | char(128) | YES | | NULL | | select,insert,update,references |
| descript_URL | char(128) | YES | | NULL | | select,insert,update,references |
| last_edit | timestamp(12) | YES | | NULL | | select,insert,update,references |
+---------------+---------------+------+-----+---------+-------+---------------------------------+
VIEWS Table
mysql> LOAD DATA LOCAL INFILE '/home/bluhrs/html/db1/mar24/viewdata.csv'
-> INTO TABLE views FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\r';
Records: 46 Deleted: 0 Skipped: 0 Warnings: 51
mysql> select * from views;
+---------+-------------------+-----------------------+------------------------+----------------------------------------------------------+--------------+
| view_id | assoc_waypoint_id | heading_from_waypoint | image_file | descript_URL | last_edit |
+---------+-------------------+-----------------------+------------------------+----------------------------------------------------------+--------------+
| 1 | 1 | 10 | images/lakew/lwgis2-v1 | http://server.marlboro.edu/capstone/lakew/lwgis2-v1.html | 000000000000 |
| 2 | 1 | 90 | images/lakew/lwgis2-v2 | | 000000000000 |
| 3 | 1 | 170 | images/lakew/lwgis2-v3 | | 000000000000 |
| 4 | 1 | 250 | images/lakew/lwgis2-v4 | | 000000000000 |
| 5 | 1 | 330 | images/lakew/lwgis2-v5 | | 000000000000 |
| 6 | 2 | 10 | images/lakew/lwlm22-v1 | http://server.marlboro.edu/capstone/lakew/lwlm22-v1.html | 000000000000 |
| 7 | 2 | 90 | images/lakew/lwlm22-v2 | | 000000000000 |
| 8 | 2 | 170 | images/lakew/lwlm22-v3 | | 000000000000 |
| 9 | 2 | 250 | images/lakew/lwlm22-v4 | | 000000000000 |
| 10 | 2 | 330 | images/lakew/lwlm22-v5 | | 000000000000 |
| 11 | 3 | 10 | images/lakew/lwri25-v1 | http://server.marlboro.edu/capstone/lakew/lwri25-v1.html | 000000000000 |
| 12 | 3 | 90 | images/lakew/lwri25-v2 | | 000000000000 |
| 13 | 3 | 170 | images/lakew/lwri25-v3 | | 000000000000 |
| 14 | 3 | 250 | images/lakew/lwri25-v4 | | 000000000000 |
| 15 | 3 | 330 | images/lakew/lwri25-v5 | | 000000000000 |
| 16 | 4 | 10 | images/lakew/lwrine-v1 | http://server.marlboro.edu/capstone/lakew/lwrine-v1.html | 000000000000 |
| 17 | 4 | 90 | images/lakew/lwrine-v2 | | 000000000000 |
| 18 | 4 | 170 | images/lakew/lwrine-v3 | | 000000000000 |
| 19 | 4 | 250 | images/lakew/lwrine-v4 | | 000000000000 |
| 20 | 4 | 330 | images/lakew/lwrine-v5 | | 000000000000 |
| 21 | 5 | 10 | images/lakew/lwssnd-v1 | http://server.marlboro.edu/capstone/lakew/lwssnd-v1.html | 000000000000 |
| 22 | 5 | 90 | images/lakew/lwssnd-v2 | | 000000000000 |
| 23 | 5 | 170 | images/lakew/lwssnd-v3 | | 000000000000 |
| 24 | 5 | 250 | images/lakew/lwssnd-v4 | | 000000000000 |
| 25 | 5 | 330 | images/lakew/lwssnd-v5 | | 000000000000 |
| 26 | 6 | 10 | images/lakew/lwwite-v1 | http://server.marlboro.edu/capstone/lakew/lwwite-v1.html | 000000000000 |
| 27 | 6 | 90 | images/lakew/lwwite-v2 | | 000000000000 |
| 28 | 6 | 170 | images/lakew/lwwite-v3 | | 000000000000 |
| 29 | 6 | 250 | images/lakew/lwwite-v4 | | 000000000000 |
| 30 | 6 | 330 | images/lakew/lwwite-v5 | | 000000000000 |
| 31 | 7 | 10 | images/lakew/lwwitw-v1 | http://server.marlboro.edu/capstone/lakew/lwwitw-v1.html | 000000000000 |
| 32 | 7 | 90 | images/lakew/lwwitw-v2 | | 000000000000 |
| 33 | 7 | 170 | images/lakew/lwwitw-v3 | | 000000000000 |
| 34 | 7 | 250 | images/lakew/lwwitw-v4 | | 000000000000 |
| 35 | 7 | 330 | images/lakew/lwwitw-v5 | | 000000000000 |
| 36 | 8 | 10 | images/lakew/lwwl76-v1 | http://server.marlboro.edu/capstone/lakew/lwwl76-v1.html | 000000000000 |
| 37 | 8 | 90 | images/lakew/lwwl76-v2 | | 000000000000 |
| 38 | 8 | 170 | images/lakew/lwwl76-v3 | | 000000000000 |
| 39 | 8 | 250 | images/lakew/lwwl76-v4 | | 000000000000 |
| 40 | 8 | 330 | images/lakew/lwwl76-v5 | | 000000000000 |
| 41 | 9 | 10 | images/lakew/lwwrb1-v1 | http://server.marlboro.edu/capstone/lakew/lwwrb1-v1.html | 000000000000 |
| 42 | 9 | 90 | images/lakew/lwwrb1-v2 | | 000000000000 |
| 43 | 9 | 170 | images/lakew/lwwrb1-v3 | | 000000000000 |
| 44 | 9 | 250 | images/lakew/lwwrb1-v4 | | 000000000000 |
| 45 | 9 | 330 | images/lakew/lwwrb1-v5 | | 000000000000 |
| 0 | NULL | NULL | NULL | NULL | 000000000000 |
+---------+-------------------+-----------------------+------------------------+----------------------------------------------------------+--------------+
mysql> delete from views where view_id=0;
mysql> show columns from views;
+-----------------------+---------------+------+-----+---------+-------+---------------------------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+-----------------------+---------------+------+-----+---------+-------+---------------------------------+
| view_id | int(11) | | PRI | 0 | | select,insert,update,references |
| assoc_waypoint_id | int(11) | YES | | NULL | | select,insert,update,references |
| heading_from_waypoint | int(11) | YES | | NULL | | select,insert,update,references |
| image_file | char(128) | YES | | NULL | | select,insert,update,references |
| descript_URL | char(128) | YES | | NULL | | select,insert,update,references |
| last_edit | timestamp(12) | YES | | NULL | | select,insert,update,references |
+-----------------------+---------------+------+-----+---------+-------+---------------------------------+
DESTINATIONS Table
mysql> LOAD DATA LOCAL INFILE '/home/bluhrs/html/db1/mar24/destdata.csv'
-> INTO TABLE destinations FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\r';
Records: 23 Deleted: 0 Skipped: 0 Warnings: 28
mysql> select * from destinations;
+------------------+------------------+-----------------------+------------------------+---------+--------------+
| from_waypoint_id | dest_waypoint_id | heading_from_waypoint | distance_from_waypoint | view_id | last_edit |
+------------------+------------------+-----------------------+------------------------+---------+--------------+
| 5 | 6 | 60 | 1.5 | 2 | 000000000000 |
| 6 | 5 | 240 | 1.5 | 6 | 000000000000 |
| 5 | 7 | 35 | 1.8 | 1 | 000000000000 |
| 7 | 5 | 215 | 1.8 | 5 | 000000000000 |
| 5 | 9 | 350 | 3.2 | 8 | 000000000000 |
| 9 | 5 | 170 | 3.2 | 6 | 000000000000 |
| 6 | 7 | 340 | 0.8 | 8 | 000000000000 |
| 7 | 6 | 160 | 0.8 | 6 | 000000000000 |
| 7 | 1 | 330 | 1.8 | 8 | 000000000000 |
| 1 | 7 | 150 | 1.8 | 6 | 000000000000 |
| 1 | 9 | 80 | 0.9 | 7 | 000000000000 |
| 9 | 1 | 260 | 0.9 | 3 | 000000000000 |
| 6 | 8 | 120 | 2.1 | 7 | 000000000000 |
| 8 | 6 | 300 | 2.1 | 3 | 000000000000 |
| 8 | 3 | 140 | 2.8 | 6 | 000000000000 |
| 3 | 8 | 320 | 2.8 | 8 | 000000000000 |
| 3 | 4 | 130 | 2.1 | 6 | 000000000000 |
| 4 | 3 | 310 | 2.1 | 8 | 000000000000 |
| 3 | 2 | 160 | 3.9 | 5 | 000000000000 |
| 2 | 3 | 340 | 3.9 | 1 | 000000000000 |
| 4 | 2 | 180 | 2.2 | 5 | 000000000000 |
| 2 | 4 | 0 | 2.2 | 1 | 000000000000 |
| 0 | NULL | NULL | NULL | NULL | 000000000000 |
+------------------+------------------+-----------------------+------------------------+---------+--------------+
mysql> delete from destinations where from_waypoint_id=0;
mysql> show columns from destinations;
+------------------------+---------------+------+-----+---------+-------+---------------------------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+------------------------+---------------+------+-----+---------+-------+---------------------------------+
| from_waypoint_id | int(11) | YES | | NULL | | select,insert,update,references |
| dest_waypoint_id | int(11) | YES | | NULL | | select,insert,update,references |
| heading_from_waypoint | int(11) | YES | | NULL | | select,insert,update,references |
| distance_from_waypoint | float | YES | | NULL | | select,insert,update,references |
| view_id | int(11) | YES | | NULL | | select,insert,update,references |
| last_edit | timestamp(12) | YES | | NULL | | select,insert,update,references |
+------------------------+---------------+------+-----+---------+-------+---------------------------------+
TRIPLEGS Table
mysql> LOAD DATA LOCAL INFILE '/home/bluhrs/html/db1/mar24/triplegsdata.csv'
-> INTO TABLE triplegs FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\r';
Records: 14 Deleted: 0 Skipped: 0 Warnings: 50
mysql> select * from triplegs;
+--------+---------+-------------------+-----------------+-------------+--------------+---------------------+---------------------+-------+--------------+--------------+
| leg_id | trip_id | start_waypoint_id | end_waypoint_id | leg_heading | leg_distance | start_time | end_time | speed | layover_time | last_edit |
+--------+---------+-------------------+-----------------+-------------+--------------+---------------------+---------------------+-------+--------------+--------------+
| 1 | 1 | 5 | 7 | 35 | 1.8 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 20 | 00:00:00 | 000000000000 |
| 2 | 1 | 7 | 1 | 330 | 1.8 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 20 | 00:00:00 | 000000000000 |
| 3 | 1 | 1 | 9 | 80 | 0.9 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 20 | 00:00:00 | 000000000000 |
| 4 | 1 | 9 | 5 | 170 | 3.2 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 20 | 00:00:00 | 000000000000 |
| 5 | 2 | 5 | 6 | 60 | 1.5 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 20 | 00:00:00 | 000000000000 |
| 6 | 2 | 6 | 8 | 120 | 2.1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 20 | 00:00:00 | 000000000000 |
| 7 | 2 | 8 | 3 | 140 | 2.8 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 20 | 00:00:00 | 000000000000 |
| 8 | 2 | 3 | 4 | 130 | 2.1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 20 | 00:00:00 | 000000000000 |
| 9 | 2 | 4 | 2 | 180 | 2.2 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 20 | 00:00:00 | 000000000000 |
| 10 | 2 | 2 | 3 | 340 | 3.9 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 20 | 00:00:00 | 000000000000 |
| 11 | 2 | 3 | 8 | 320 | 2.8 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 20 | 00:00:00 | 000000000000 |
| 12 | 2 | 8 | 6 | 300 | 2.1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 20 | 00:00:00 | 000000000000 |
| 13 | 2 | 6 | 5 | 240 | 1.5 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 20 | 00:00:00 | 000000000000 |
| 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 000000000000 |
+--------+---------+-------------------+-----------------+-------------+--------------+---------------------+---------------------+-------+--------------+--------------+
mysql> delete from triplegs where leg_id=0;
mysql> show columns from triplegs;
+-------------------+---------------+------+-----+---------+-------+---------------------------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+-------------------+---------------+------+-----+---------+-------+---------------------------------+
| leg_id | int(11) | | PRI | 0 | | select,insert,update,references |
| trip_id | int(11) | YES | | NULL | | select,insert,update,references |
| start_waypoint_id | int(11) | YES | | NULL | | select,insert,update,references |
| end_waypoint_id | int(11) | YES | | NULL | | select,insert,update,references |
| leg_heading | int(11) | YES | | NULL | | select,insert,update,references |
| leg_distance | float | YES | | NULL | | select,insert,update,references |
| start_time | datetime | YES | | NULL | | select,insert,update,references |
| end_time | datetime | YES | | NULL | | select,insert,update,references |
| speed | float | YES | | NULL | | select,insert,update,references |
| layover_time | time | YES | | NULL | | select,insert,update,references |
| last_edit | timestamp(12) | YES | | NULL | | select,insert,update,references |
+-------------------+---------------+------+-----+---------+-------+---------------------------------+
NAVLOGS Table
mysql> LOAD DATA LOCAL INFILE '/home/bluhrs/html/db1/mar24/navlogsdata.csv'
-> INTO TABLE navlogs FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\r';
Records: 3 Deleted: 0 Skipped: 0 Warnings: 10
mysql> select * from navlogs;
+---------+----------------------------+-----------+---------------------+-----------------------------------------------------+--------------+
| trip_id | trip_name | vessel_id | start_time | log_URL | last_edit |
+---------+----------------------------+-----------+---------------------+-----------------------------------------------------+--------------+
| 1 | Sanders Bay to Weirs Beach | 3 | 0000-00-00 00:00:00 | http://server.marlboro.edu/capstone/lakew/log1.html | 000000000000 |
| 2 | Sanders Bay to Alton Bay | 1 | 0000-00-00 00:00:00 | http://server.marlboro.edu/capstone/lakew/log2.html | 000000000000 |
| 0 | NULL | NULL | NULL | NULL | 000000000000 |
+---------+----------------------------+-----------+---------------------+-----------------------------------------------------+--------------+
mysql> delete from navlogs where trip_id=0;
mysql> show columns from navlogs;
+------------+---------------+------+-----+---------+-------+---------------------------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+------------+---------------+------+-----+---------+-------+---------------------------------+
| trip_id | int(11) | | PRI | 0 | | select,insert,update,references |
| trip_name | char(64) | YES | | NULL | | select,insert,update,references |
| vessel_id | int(11) | YES | | NULL | | select,insert,update,references |
| start_time | datetime | YES | | NULL | | select,insert,update,references |
| log_URL | char(128) | YES | | NULL | | select,insert,update,references |
| last_edit | timestamp(12) | YES | | NULL | | select,insert,update,references |
+------------+---------------+------+-----+---------+-------+---------------------------------+
VESSELS Table
mysql> LOAD DATA LOCAL INFILE '/home/bluhrs/html/db1/mar24/vesseldata.csv'
-> INTO TABLE vessels FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\r';
Records: 4 Deleted: 0 Skipped: 0 Warnings: 7
mysql> select * from vessels;
+-----------+---------------+-----------+--------------+
| vessel_id | vessel_name | avg_speed | last_edit |
+-----------+---------------+-----------+--------------+
| 1 | Sbout Time | 20 | 000000000000 |
| 2 | Catskill Maid | 8 | 000000000000 |
| 3 | DriftAbout | 2 | 000000000000 |
| 0 | NULL | NULL | 000000000000 |
+-----------+---------------+-----------+--------------+
mysql> delete from vessels where vessel_id=0;
mysql> show columns from vessels;
+-------------+---------------+------+-----+---------+-------+---------------------------------+
| Field | Type | Null | Key | Default | Extra | Privileges |
+-------------+---------------+------+-----+---------+-------+---------------------------------+
| vessel_id | int(11) | | PRI | 0 | | select,insert,update,references |
| vessel_name | char(32) | YES | | NULL | | select,insert,update,references |
| avg_speed | float | YES | | NULL | | select,insert,update,references |
| last_edit | timestamp(12) | YES | | NULL | | select,insert,update,references |
+-------------+---------------+------+-----+---------+-------+---------------------------------+
I also played with some queries that I thought might be made on tables in this database (and to verify these queries to be sure they worked before I used them in my Perl-DBI/Java JDBC assignment).
bash-2.04$ pwd /home/bluhrs/html/db1/mar24 bash-2.04$ date Sun Mar 25 20:27:56 EST 2001 bash-2.04$ mysql -h carson-city -u bluhrs -ppasswd vw
To collect and display the information for a selected WAYPOINT, you'll lookup that wayppoint from it's WAYPOINT_ID, assuming the user will start by selecting the starting waypoint from a list of waypoint names (and their associated waypoint id#'s):
mysql> SELECT waypoint_name, lat_degrees, lat_minutes, long_degrees,
-> long_minutes, image_file, descript_URL
-> FROM waypoints WHERE waypoint_id=3;
+---------------------------------------+-------------+-------------+--------------+--------------+---------------------+-------------------------------------------------------+
| waypoint_name | lat_degrees | lat_minutes | long_degrees | long_minutes | image_file | descript_URL |
+---------------------------------------+-------------+-------------+--------------+--------------+---------------------+-------------------------------------------------------+
| Rattlesnake Is. Can Buoy #25 NW Shore | 43 | 34.632 | 71 | 18.232 | images/lakew/lwri25 | http://server.marlboro.edu/capstone/lakew/lwri25.html |
+---------------------------------------+-------------+-------------+--------------+--------------+---------------------+-------------------------------------------------------+
To collect and display the VIEWS associated with a WAYPOINT, you'll have the ASSOC_WAYPOINT_ID from WAYPOINTS.WAYPOINT_ID, then read the view information from the VIEWS table:
mysql> SELECT heading_from_waypoint, image_file, descript_URL
-> FROM views WHERE assoc_waypoint_id=3;
+-----------------------+------------------------+----------------------------------------------------------+
| heading_from_waypoint | image_file | descript_URL |
+-----------------------+------------------------+----------------------------------------------------------+
| 10 | images/lakew/lwri25-v1 | http://server.marlboro.edu/capstone/lakew/lwri25-v1.html |
| 90 | images/lakew/lwri25-v2 | |
| 170 | images/lakew/lwri25-v3 | |
| 250 | images/lakew/lwri25-v4 | |
| 330 | images/lakew/lwri25-v5 | |
+-----------------------+------------------------+----------------------------------------------------------+
To get only 3 VIEWS according to the HEADINGS away from a selected WAYPOINT you'll have WAYPOINTS.WAYPOINT_ID to use as the the ASSOC_WAYPOINT_ID and will want to select 3 (or so) views according to a heading (direction of that view from the current waypoint).
mysql> SELECT heading_from_waypoint, image_file, descript_URL
-> FROM views WHERE assoc_waypoint_id=3 AND heading_from_waypoint > '60'
-> ORDER BY heading_from_waypoint LIMIT 3;
+-----------------------+------------------------+--------------+
| heading_from_waypoint | image_file | descript_URL |
+-----------------------+------------------------+--------------+
| 90 | images/lakew/lwri25-v2 | |
| 170 | images/lakew/lwri25-v3 | |
| 250 | images/lakew/lwri25-v4 | |
+-----------------------+------------------------+--------------+
TO PAN LEFT of RIGHT, I can just keep all the VIEWS-rows associated with a WAYPOINT in an array of sorts and "move" left or right in it as though it were a circular queue.
To find the DESTINATIONS associated with a VIEW at a WAYPOINT you'll have the VIEWS.VIEW_ID and WAYPOINTS.WAYPOINT_ID
e.g. At waypoint #3, view #6 corresponds to the destination that is waypoint #4
mysql> SELECT dest_waypoint_id,heading_from_waypoint, distance_from_waypoint
-> FROM destinations WHERE from_waypoint_id=3 AND view_id=6;
+------------------+-----------------------+------------------------+
| dest_waypoint_id | heading_from_waypoint | distance_from_waypoint |
+------------------+-----------------------+------------------------+
| 4 | 130 | 2.1 |
+------------------+-----------------------+------------------------+
### THEN, using the dest_waypoint_id as the waypoint_id:
mysql> SELECT waypoint_name, descript_URL
-> FROM waypoints WHERE waypoint_id=4;
+------------------------------+-------------------------------------------------------+
| waypoint_name | descript_URL |
+------------------------------+-------------------------------------------------------+
| Rattlesnake Is. off NE Shore | http://server.marlboro.edu/capstone/lakew/lwrine.html |
+------------------------------+-------------------------------------------------------+