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 2 -3 class periods and will cover the following topics:
Introduction to relational databases. http://livecode.byu.edu/database/IntroToDB.php
Modeling your app's data storage needs and designing a database to accommodate.
Rules for creating a good database structure - Data is "atomic" (< Greek "indivisible")
Do Exercise 1 - Creating a Database on the command line, and Exercise 2 - Working with Databases in a Graphical User Interface.
ASSIGNMENT: SQL SELECT tutorial - sqlzoo.net. Complete before next class period.
Go to this site and go through the following sections:
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).
Writing LiveCode code for interacting with your database.
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.
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.