When MySQL opened I clicked on the Readings connection I created last week and entered my root password:
******
I clicked on Add Schema under Actions, typed catalog into the Name field on the dialog box that popped up, and clicked Apply. Another dialog box appeared, showing the command that was about to be performed. I clicked Apply again and the schema was created.
As part of last week's assignment, I had conceived of potential fields for this database, as can be seen here:
| Name | Repeating | Unique | Data Type | Max Length | Notes |
| id | N | Y | Integer | 4 |
|
| title | N | Y | Text | char255 |
|
| author | Y | N | Text | char100 |
|
| form_genre | Y | N | Text | char30 |
|
| publisher | Y | N | Text | char30 |
|
| source | Y | N | Text | char100 | See Note #1 |
| volume_issue | Y | N | Text | char10 |
|
| edition | Y | N | Text | char30 |
|
| access_date | Y | N | Date | YYYY-MM-DD |
|
| pub_date | Y | N | Text | char10 | Publication date |
| page_range | N | N | Text | char30 |
|
| class_number | Y | N | Text | char10 |
|
| class_title | Y | N | Text | char50 |
|
| subject | Y | N | Text | char20 |
|
| abstract | N | Y | Text | varchar |
|
| file_name | N | Y | Text | char50 |
|
| location | Y | N | Text | varchar | Directory location or URL |
| notes | N | N | Text | varchar |
|
While these columns were a start, I took several of RPM's recommendations and made changed when I created the tables in MySQL.
The main readings screen was still on display with the catalog schema on the lower left. I double-clicked on catalog to ensure I was working within it and then double-clicked Add Table on the upper left. This opened a dialog box to create my first table. I named the table accesspoints and then navigated to the Columns tab at the top of the box. I entered four columns:
accesspointsid
readingsid
apname
apvalue
A drop-down menu and series of check boxes followed each column, letting me choose the characteristics of the field. Based upon my understanding of tables, I chose integers for the ids and varchar for the apname and apvalue columns. For accesspointsid, I selected PK (primary key), NN (non-null), UQ (unique), and AI (auto-increment). I also selected NN for readingsid, as this attribute will always need to be present. I clicked apply, bringing up the dialog box showing the MySQL command, and clicked Apply again. The table was created.
I then double-clicked Add Table again to create my second table: readings. I entered the table name into the Name field and clicked on the Columns tab. I created a longer list of columns for this table:
readingsid
title
source
imprint
page_range
file_name
notes
link
I decided to make all of the columns for both tables lower case because MySQL is case sensitive. I also either combined words without spaces (as in readingsid) or joined them with an underscore (as in page_range) so that spaces did not cause any issues with querying. As with the accesspoints table, I chose the Datatype and characteristics of each of these columns. I made all of the columns varchar (per RPM) with the exception of readingsid, whose integer datatype carried over from the accesspoints table. readingsid became my PK, NN, UQ, AI attribute in this table; with title also being NN. I used fewer field than I did in my intitial design for reasons of simplicity.
After inputting all of this information, I clicked Apply, which brought up the MySQL command. I clicked Apply again and both of my tables were created.
The final results can be seen here:
No comments:
Post a Comment