Author Archives: Gina Stalica '16

Databases Debunked

This semester, I have explored databases from both technical and sociological standpoints.  As part of my project, I have reviewed and created existing MySQL tutorials.  The goal of these blog posts is to demystify databases for Bowdoin – more specifically, DCSI – students.

Here, I’ve created a summary of useful terms to provide a brief, basic orientation to databases.

I have analyzed three different existing tutorial resources:

  1. MySQL Tutorial from TutorialsPoint
  2. SQL Tutorial from W3Schools
  3. MySQL Tutorial from Oracle

I have also created two walkthrough tutorials of my own:

  1. MySQL Download and Setup for Mac OS X
  2. Creating a Database Using Sequel Pro

Databases are powerful tools.  While they may seem daunting to use, any DCSI student is capable of learning how to create and use them on their own.  Enjoy!

– Gina Stalica ’16

Databases Debunked: Helpful Terms

What’s in a database?  A database is a system for storing, retrieving, and analyzing data.  A database is made up of tables.  Tables are made up of rows (entries) and columns (attributes).  Each entry has its own unique identifier, usually a number, called a primary key.  Primary keys exist in their own column in each table.

Why do primary keys matter?  This brings us back to the question of why we need databases.  Primary keys ensure that each entry (row) in a database is unique.  In comparison, when we consider a database alternative, like Microsoft Excel, each cell is unique.  Each cell can be changed independently of any other element in the spreadsheet, including the other cells in its row.  Really, primary keys allow for the relations that make up relational databases.

What’s SQL?  SQL stands for Structured Query Language.  It’s the language used to make databases understandable by computers or other machines.

Well then, what’s MySQL?  MySQL is relational database management system (RDBMS) , a software product that understands SQL.  DCSI has chosen to explore databases using MySQL because it’s is free, open-source, and widely used.


A database is collection of organized data, made up of tables.  Each table represents a real-world entity.  A relational database keeps track of relations between information stored in different tables according to their common elements.  Tables are organized into rows and columns, where rows represent one instance, or record, of a real-world entity and columns represent characteristics, or attributes, of the record.  A single cell in a table represents a field, an intersection between a row and column.

In order to maintain the relations of a relational database, every record in a table has a unique primary key, an unchanging value held in a table’s column.

To put these terms into context, we can consider a fictional Bowdoin database.  The database might contain tables for Students, Dorm Rooms, Professors, Classes, and more.

bowdoinDB

A Student table might contain records of each student at Bowdoin, where each row represents an individual student.  Each column in the Student table might represent different attributes of each Student, for example, their unique student ID number, their first name, their last name, the SU box number, and more.  Conveniently, since each Bowdoin student has a unique

studentTable

 

A good way to think about the relationship between primary keys are foreign keys is to consider


 

Definitions

  • Database:  “A system that allows for the efficient retrieval and storage of information” (Ramsay)
  • Relational database management system:  The
  • Domain:  “The universe of discourse” (Ramsay)
  • Entities:  The elements of a domain, represented as rows.  Essentially, the major components of categorization.
  • Attributes:  The categories within an attribute, represented as columns.
  • Fields:  The intersection of rows and columns.
  • Primary key:  A unique value held by each individual record in a table.  Representationally, it is held in its own column.
  • Foreign key:  Stores a reference back to the entity.
  • Association:  An entity that represents an abstracted relationship between two foreign keys.
  • Schema:  A representation of database design that is understandable to computers/machines. (Ramsay)
  • Structured Query Language (SQL):  The language used to translate database design into a representation understandable to computers/machines. (Ramsay)

These definitions and analogies are inspired by the Digital Humanities Coursepack by Harvey Quaemen, Jon Bath, and John Yobb and by Stephen Ramsay’s “Databases” in “A Companion to the Digital Humanities”.  Thank you!

TutorialsPoint MySQL Tutorial

TutorialsPoint provides online users with a variety of free educational content.  The company’s mission, “Simply Easy Learning,” is strictly educational.

Screen Shot 2016-03-28 at 9.04.11 PM

TutorialsPoint provides beginner SQL users with an introduction to MySQL in a PHP environment here.  The tutorial begins at a most basic level with an introduction to databases, MySQL, and its syntax.  As users click through the pages of the tutorial, they will learn the basic definitions, relational database management systems (RDBMS), and their related content.  It presents a definition of terms useful to understanding databases, allowing users to understand the workings of databases before diving into specific syntax for creating a new MySQL instance or managing data.

Pros:

  • Download/setup assistance:  Users are guided in downloading and installing MySQL on Linux/Unix and Windows, setting up MySQL accounts, and getting started on creating their own database.
  • Quick reference:  The tutorial provides a “Useful Functions” section, which allows for quick syntax reference supplemented with examples.

    Function example section

    Function example section

  • Connection to web development:  Users who are experienced with web development, specifically those using PHP, will likely find this a great resource for integrating databases into their sites.

Cons:

  • No Mac assistance:  The site unfortunately does not provide instruction for downloading and installing MySQL on OS X.
  • Assumption of experience working from command-line or using PHP:  TutorialsPoint suggests that the information presented will be most easily understood by users who are at all familiar with web development and/or working in a Linux environment.  The specifics of MySQL syntax are presented for both PHP files and the command line, which may be limiting to new users unfamiliar with these environments.

tl;dr?  Ultimately, this resource is both comprehensible and thorough enough to be useful to anyone who is beginning to use SQL, regardless of their level of programming experience; however, new users may likely find other tutorials more useful as a more general starting point for database creation and design.  TutorialsPoint provides an introduction to MySQL that will be most useful for users experienced with web design who are looking to add database functionality to their websites.

W3Schools SQL Tutorial

W3Schools is a free site that provides tutorials and documentation for a variety of different languages and elements of web development.  

Screen Shot 2016-03-10 at 9.51.00 PM

Here, W3Schools offers a step-by step guide to SQL, its uses, and its syntax.  It begins with a high-level overview of databases, their importance, and key definitions.  this introduction makes for a smooth transition into the majority of the content of the tutorial: short chapters that include brief lessons for an extensive range of SQL statements, beginning at a very comfortable, basic level.  The tutorial is supplemented with a SQL quiz to test users’ understanding of the material explored.

Pros:

  • More than just MySQL:  This tutorial teaches general SQL syntax and can be used beyond MySQL, which is just one of many relational databases.
  • Try it yourself:  W3Schools provides users with an integrated, online environment in which to run tests of SQL code.  This tool is great for trying out SQL syntax without having to create a database yourself.

    W3Schools’ “try it yourself” functionality

  • Specific syntax documentation:  The thorough documentation of SQL syntax will be useful both for new database designers and database pros.  New users will appreciate the ability to move step-by-step through the syntax, while pros will be able to easily locate the specific information they need without completing the entire tutorial.

Cons:

  • Slowness:  Users who are interested in learning SQL syntax from the start may find it tedious or ineffective to click through each step in the tutorial.
  • No download/setup assistance:  This tutorial will not be useful to users who are looking for guidance as to how to use SQL, MySQL, or Sequel Pro outside of the W3Schools website.  This leads to another potential drawback for users…
  • No command line how-to:  For Bowdoin Computer Scientists or other users who are experienced working in Linux/Unix environments, W3Schools does not address the way in which SQL can be used from the command line, which may be limiting.

tl;dr?  W3Schools’ tutorial begins at a very basic, comfortable level and will likely be most useful to students who are just beginning to experiment with databases (or really, coding in general).  For students looking for help actually creating a database or integrating databases into their already existing websites or other projects, this tutorial may fall short.  Its strengths lie in its simplicity and try-it-yourself functionality.

Oracle MySQL Tutorial

This tutorial comes from the official MySQL website, which is technically under the ownership of Oracle.  Oracle is a global software and technology company, whose services are widely used – likely by many of the technologies you use every day.  MySQL alone is used to power the functionality of Twitter, Facebook, YouTube, and much more.

Screen Shot 2016-03-30 at 10.23.50 PM

While the MySQL documentation available on this site extends far, far beyond its tutorial, the tutorial itself can be found here.  Oracle offers users very direct walkthroughs of connecting to the MySQL server, creating and using databases, entering queries, getting information about databases, using MySQL in batch mode, and using MySQL with Apache.  The guidance is straightforward, yet thorough, but it certainly assumes a certain level of user knowledge.

Pros:

  • Reputable:  Since Oracle created MySQL, the company certainly knows MySQL best.  This is the most reliable MySQL resource available.  This is especially important to users who may be troubleshooting, as the site is far more likely to be correct than your average StackOverflow post.
  • Straight to the point:  As was mentioned, Oracle knows how MySQL should work better than any other resource.  This tutorial is reliable without any excess information.
  • Thorough directions:  Though the tutorial is straightforward, it does cover all of the essential information needed to get started with MySQL – and, likely, needed to fix certain problems that even seasoned database designers may need.  With every explanation comes an example straight from the
Oracle provides useful examples

Oracle provides users with useful examples

Cons:

  • Straight to the point:  One may notice that this very quality was also listed as a “Pro.”  Oracle assumes that users have an understanding of such topics as batch mode and Apache.  It does not take time to explain anything extremely thoroughly, which may prove to be limiting to users who are new to programming or MySQL.
  • Only command line documentation:  This tutorial will really only be useful for those who are looking to use MySQL from the command line in a Linux/Unix or Windows environment.  Anyone who is looking to use MySQL largely from a GUI (a Graphic User Interface, like Sequel Pro or Windows MySQL Workbench) will benefit from other resources.

tl;dr?  Oracle provides a reliable, straightforward tutorial for connecting to MySQL server, creating and using databases, entering queries, and more, all in a Unix/Linux environment.  For users who are looking to use databases using a GUI, other resources will likely be more helpful.

Sequel Pro: Creating a Database

This walkthrough has been created using this tutorial as a resource.

  1. Start your machine’s MySQL Server instance through System Preferences.  See this walkthrough for assistance in doing so.
  2. Download Sequel Pro here.
  3. Open Sequel Pro.Screen Shot 2016-04-24 at 8.33.09 PM
  4. Create a Standard connection to your MySQL Server, using IP address 127.0.0.1 as the Host and your MySQL root credentials for the Username and Password.  No extra information is necessary.
    Screen Shot 2016-04-24 at 9.16.37 PM
  5. Once connected, you will automatically be in the “Query” section of Sequel Pro, where you will type and run MySQL commands.  In this demo, I will create a database of classmates, but feel free to mimic the syntax used in order to create a database of your choosing.  To create your database, type CREATE DATABASE classmate_database; then press “Run Previous” just below in order to run your code and create your database.
    Screen Shot 2016-04-26 at 8.40.47 PMTo check that you’ve successfully created the database, navigate to the “TABLES” section of the Sequel Pro window and locate classmate_database.
    Screen Shot 2016-04-26 at 8.43.40 PM
  6. In order to work with the database your have just created, use the command “USE” and type USE classmate_database; and run the command just as you did before.
    Screen Shot 2016-04-26 at 8.49.41 PM
  7. Now, you’ll need to create a table in order to add some actual content to your database.  To learn more about the organization of a database or, more specifically, the relationship between a database and its tables, check out this post.  To create the table, unsurprisingly, we’ll use the command “CREATE TABLE” to create a table of classmates and proceed to indicate the primary key column and an initial (unique) column for the database.  Type and run the following code:
    CREATE TABLE classmates (
            student_id INT NOT NULL,
            student_lastName VARCHAR(30) NOT NULL,
            PRIMARY KEY (student_id),
            UNIQUE (student_lastName)
    );
    Screen Shot 2016-04-26 at 9.24.17 PM
    Let’s take some time to understand the code we’ve just used.  CREATE DATABASE is self explanatory; however, it’s important to notice the way we set student_id as the PRIMARY KEY here and set student_lastName as a UNIQUE key.  Both primary and unique keys are indices whose members must all be unique.  Here, that means that no students can have the same ID and that no students can have the same last name (which may not be a realistic assumption).  The primary key is special in that it is used for identification.  In student_id, INT specifies that the column’s values will be integers and NOT NULL indicates that the value cannot be null.  If a null value is attempted for this column, an error will occur.  In student_lastName, VARCHAR(30) specifies that the column’s value will be composed characters with a maximum length of 30.  Here, VARCHAR is used instead of MySQL’s CHAR because VARCHAR allows user to indicate a string of variable length, while CHAR’s declaration indicates a string of static length.  We use VARCHAR here so that last names of different lengths can be inserted into the column.
  8. Now is a good time to check that your table has been created.  Click on the “Content” button on the top toolbar on the Sequel Pro window.  If you find student_id and student_lastName as columns, you have successfully created the beginnings of your database!
    Screen Shot 2016-04-26 at 9.53.31 PMYou can resume coding by returning to the “Query” button on the top toolbar.
  9. Now, let’s add some actual values to our table using the “INSERT INTO” and “VALUES” command by typing and running the following code:
    INSERT INTO classmates
            (student_id, student_lastName)
    VALUES
            (1, “Lastname1”),
            (2, “Lastname2”),
            (3, “Lastname3”);
    Be sure to contain the last name values in quotes and to assign unique id number values to each student.
    Screen Shot 2016-04-26 at 10.06.35 PMTo check to make sure that your data was entered correctly, again navigate to the “Content” button.  If you click to drop the student_id or student_lastName columns down, you should find your data!
    Screen Shot 2016-04-26 at 10.06.50 PM

MySQL Download and Setup: Mac OS X

The MySQL download can be found here.

  1. Download the Mac OS X 10.10 (x86, 64-bit), DMG Archive.Screen Shot 2016-03-31 at 10.14.22 AM
  2. When download is complete, open the installation packages by double-clicking the package icon.  There will be a pop-up window that provides a temporary password for MySQL’s “root” user.  This password will be a slew of random characters that is crucial to getting MySQL up and running smoothly.  Be certain to take note of this password!
  3. Start your MySQL server instance from your computer’s System Preferences.  To do so, click the Apple icon in the upper right hand corner of your desktop screen.  Select System Preferences.
    Screen Shot 2016-03-31 at 10.47.37 AM
    In System Preferences, locate and double-click the MySQL icon.
    Screen Shot 2016-03-31 at 10.49.04 AM
    Press the “Start MySQL Server” button.
    Screen Shot 2016-03-31 at 10.49.19 AM
    You may be asked to enter a username and password in order to make this change.  This will be your normal desktop username and password. For example, if you are on a Bowdoin computer, this will be your Bowdoin username and password.
  4. From here, you will need to reset your MySQL root user password, which will require working from your computer’s Terminal with root user privileges.  To open up a Terminal window, open up Mac’s Finder.
    Screen Shot 2016-04-06 at 9.15.55 PM
    Locate the Applications folder, from there locate the Utilities folder, and the Terminal should be there.  Double-click the Terminal to open a window.Screen Shot 2016-04-06 at 9.16.42 PM
  5. In the Terminal, type the command: /usr/local/mysql/bin/mysql -u root -p
    Screen Shot 2016-04-07 at 10.58.12 PM
  6. Enter your temporary password and press Enter.  Note that the key icon will not move as you type in your password.  This is normal!
  7. From here, you are connected to the MySQL server from the root!  Screen Shot 2016-04-07 at 10.58.29 PMNow it’s time to change your password to something a bit more memorable (and less nonsensical).  Type the command: SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘typeyourpasswordhere’);  Be sure to type the command yourself.  Copying and pasting may cause a problematic difference in formatting.  It’s also worth noting the use of the semicolon here.  In SQL, like many other programming languages, the semicolon indicates the end of a statement, much like a period indicates the end of a sentence.Screen Shot 2016-04-07 at 11.13.44 PM

That’s all there is to it.  If you’ve made it this far without a glitch, congratulations!  You’ve successfully download MySQL, started the server, and have access via the root user with a newly created password.