SQL

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.

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

 

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 provides online users with a variety of free educational content.  The company’s mission, “Simply Easy Learning,” is strictly educational.

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.

  

• 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 is a free site that provides tutorials and documentation for a variety of different languages and elements of web development.  

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.

  

• 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.