Tuesday, December 6, 2011

November 13, 2011 - Creating a database

To start creating a database in MySQL, I opened my virtual machine and navigated to Applications, Programming, MySQL Workbench.

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