Office of Digital Humanities

Data Modeling and Databases

Objectives:

• Know what a relational database is and why it is useful.
• Understand the basic approach to modelling your app's data in relational database tables.
• Understand what SQL is.
• Become familiar with these basic SQL commands:
  CREATE, INSERT, DELETE, UPDATE, SELECT
• Know how to use the WHERE clause to limit the scope of INSERT, DELETE, UPDATE, and SELECT.

Introduction and Overview

1. Gaining an understanding of how to structure and store data might be the most important information that we get in this class, because regardless of what language you program in or what environment you target, all programs (or web sites) deal in some way with data. When we finish you should better understand how to think about and model data storage.

   This module of instruction will take 4 – 5 class periods and will cover the following topics:

   • What is a relational database?
   • Thinking about data storage: modeling your data.
   • Creating a database design for your project.
   • Fundamentals of SQL.
   • Creating your database in an SQL database.
   • MySQL - networked, multiuser. Access from server over the network.
   • SQLite - local, single user. Local file access.
   • Even though these are very different systems, the way to create and access the data is almost identical. What you choose will depend on the purpose of your app.
   • Accessing SQL databases in LiveCode.

Part 1 – Designing a relational database

2. Introduction to relational databases. http://livecode.byu.edu/database/IntroToDB.php




3. Modeling your app's data storage needs and designing a database to accommodate.

   • What discrete entities exist in your app's world?
   • What relationships need to exist between the various entities?
   • What "use cases" will your data need to support? (What "stories" can you tell about how your user will use your app? That will help determine how the data is structured.)

   Rules for creating a good database structure - Data is "atomic" (< Greek "indivisible")

   1. Table: Don't put data in a table that doesn't have anything to do with the table.
   2. Row: A row should have one and only one subject.
   3. Values (cells): There should only be one value in each cell (intersection of row, column).


4. ASSIGNMENT Part 1 - Design a relational database of tables that represent the data that your final project app will need to access and store. This should be based on our discussion in class about relational tables. Be sure to observe the guidelines for creating a good database structure. We will use this design for creating an actual database in our next lesson.

Part 2 – Creating and querying SQL databases

4. Do Exercise 1 - Creating a Database on the command line, and Exercise 2 - Working with Databases in a Graphical User Interface.

   http://livecode.byu.edu/database/creatingdbs.php

5. ASSIGNMENT: SQL SELECT tutorial - sqlzoo.net. Complete before next class period.

   Go to this site and go through the following sections. After each section there is a quiz. Complete each quiz with a score of 80% or higher. As proof submit a screen snap of the lefthand column of the SQL Tutorial page, which shows your progress through the tutorials and quizzes.

   0. SELECT basics - Some simple queries to get you started
   1. SELECT name - Some pattern matching queries
   2. SELECT from World - In which we query the World country profile table.
   3. SELECT from Nobel - Additional practice of the basic features using a table of Nobel Prize winners.
   4. SELECT within SELECT Tutorial - In which we form queries using other queries.
   5. SUM and COUNT - In which we apply aggregate functions.

6. ASSIGNMENT Part 2 - Using the schema you worked out for your data design create a database to hold that data and populate it with a few records of data.

   Instructor will work with each student to develop his or her database schema and implement it in either MySQL or SQLite using a GUI interface (or a command line interface if you desire).

Part 3 – Database access in LiveCode

7. Writing LiveCode code for interacting with your database.

   http://livecode.byu.edu/database/dbaccessinrev.php

8. You can choose SQLite or MySQL as the database management system for your app's data. Recall that SQLite is a free, single-user database management system, which comes pre-installed on most Linux and Mac OS X systems. In addition, SQLite comes included with LiveCode. MySQL is a multi-user, server-based system that is widely used in education and industry.

   If you started your database in MySQL and decide to move it to SQLite, you can export it and import it to SQLite. See this brief tutorial on how to export your MySQL database structure to a SQLite database. Obviously, exporting data from into SQLite to MySQL is also possible, using a similar process or a converter tool.

   LiveCode Ltd has created an online lesson on how to create and access SQLite databases in LiveCode.

9. ASSIGNMENT Part 3 - Creating SQL queries for your app's database. Consider the use cases that your app needs to fullfil (i.e., the user "stories" you need to be able to tell with your data.) Write SELECT queries that will return data that will be meaningful in the context of your app, or INSERT, DELETE and UPDATE commands that will allow your app to edit your database. Your stack should include four to five queries or database commands. In contrast to the elementary, step-by-step process we used in class, each of your database interactions should be accomplished by a single button or other control, so that the action appears seamless to the end user.