Script School Classroom

School Map:  Home / Enroll $ Student Records; Class  
$ News @ LIVE Tech Radio * Support/FAQ | Store | FORUMS

What is a Relational Database and What is it Good For?

Databases, hmm... sound boring? Wrong! Databases allow You to do some of the most exciting things possible in a web site when used in combination with a server side scripting language. What sorts of things? Dynamic things... Like customized pages that get written on the fly, unique for each user! It gives You methods to bring together information to create wholly new perspectives that will entertain and inform Your visitors in unprecedented ways. And of course, it helps You put Your computer system and applications to work for You, doing the 'mundane' work, the mind numbing work of record keeping and organization. A good Database design can also let You see Your business in new ways, with graphs and charts that turn raw data into real information human beings can use.

So, what is a Database? Well, the simplest database is just a list, say a shopping list. And if on that shopping list You write 'buy 2 cookies' instead of 'cookie' 'cookie' well, then You are starting to Optimize Your Database! Now imagine that instead of writing a whole new shopping list every time You go shopping... and having to remember every item You usually buy... what if You had a regular place for each item and for each shopping trip, You just made a new line with say, the date, and then just put a '2' under 'cookies'? Now You're starting to develop something really useful and efficient - a TABLE of data. Notice something else? Now You also have a history, and You can go back and see all Your purchases over time. I'm sure You can imagine adding other aspects, such as price, store, seasonal sales, favorite salesperson...

What are 'Relational Databases'? Relational Database Management Systems (aka: RDMS) give You a way to have efficient storage and retrieval of data in Databases organized into TABLES - and - to be able to RELATE those TABLES to each other so that duplication of data is kept to a minimum. For example, if You had a list of Customers containing their addresses, You wouldn't want to repeatedly write their addresses in Your Sales TABLE every time they bought something. That would take up a lot of extra space for storage, and also opens the possibility of storing conflicting information - if their address changes. It is far more efficient to RELATE or JOIN address data, stored separately on another TABLE to Your Sales TABLE when You wish to have it all appear together.

TABLE EXAMPLEGetting it all on the TABLE... As You can see, Information in a Database is typically organized into TABLES. Tables are usually grids of vertical Columns and horizontal Rows in two dimensions - flat, like a spreadsheet. (Some Database Systems have any number of dimensions in their tables - we won't be discussing them further here.) Each Column, called a FIELD, is set up to contain a particular type of information, such as a date or line of characters, or perhaps, the number of cookies to buy as mentioned above.  Each Row is a RECORD - a discrete block of information, divided into FIELD columns.  Each RECORD row, for example, may contain all of a particular customer's contact information - their address, phone number, web site URL etc., each item in it's own FIELD column.   A Table can have many RECORD Rows - depending on the Database System used, even Millions of RECORD Rows - with the same FIELDS for every RECORD.  Typically, every TABLE has a KEY FEILD column which is used to identify each RECORD - which can also be used to RELATE the information in that Record to information in other TABLES.  KEYS are unique identifiers, and are typically a series of sequenced numbers.  As mentioned earlier, we want to RELATE a customer's address to their orders instead of writing their address over and over again - and - this way we only have to change it in ONE place... so, we would get the customer's KEY from the Customer TABLE, and put that KEY in the Sales TABLE every time they ordered - instead of their address.  Then we can QUERY or pull the information we want out of the Database with the customer's KEY.  The KEY is unique on the Customer TABLE, but may appear many times within the sales TABLE.  This is known as a 'one to many' RELATIONSHIP.  Other types of Relationships in Databases are possible and we will discuss them later.One-to-Many Example

QUERY that Data Mine! When databases are properly planned they allow data to be pulled out later in useful ways. Patterns of information that may be difficult to see from simply gazing at the raw data can become apparent through data mining - or simply asking the right questions... through QUERIES. For example, it may not be immediately obvious that many customers order a particular frozen treat after a number of sunny days... but if weather data can be correlated with orders, such valuable insight may allow a business to better prepare their inventories. Often data mining goes quite deep, perhaps joining news events of a particular variety with changes in the stock market.

Which Flavor? OK, so now You're psyched to get into databases... and You've heard about a few different 'flavors' of database systems... everything from Microsoft's Access to Oracle, and You're a bit confused as far as which will suit Your purposes. We are going to limit this discussion to Database Management Systems that are designed for the web, and are free. While this class will concentrate on MySQL, which meets both of these requirements, You can take a look at: http://www.geocities.com/mailsoftware42/db/ to see a comparison of the features and capabilities of several popular Free, Open Source, Database Systems - MySQL, PostgreSQL, SAP DB, and Firebird. The SQL or 'Structured Query Language' which we will be discussing is generally compatible with all of them.

MySQL was designed with the web in mind. It is a 'major' Database System - full fledged, fast, stable, and secure - yet 'streamlined' - delivering only those services and features that a typical web site really needs, all in a small 'footprint' on the harddrive. MySQL has become, by far, the most popular Database System in use on the web, about as close to a 'standard' as there is.  To go right to the 'horses mouth' for an excellent manual and the latest versions, news and depth: http://www.mysql.com/

Note: At the time of this writing, PHP 5 has just been released in Beta.  There is some controversy over whether the MySQL library functions will remain 'native' in future versions of PHP - or will have to be compiled in 'as needed' the way some other non-native library functions are in PHP.  Apparently, the reason is that MySQL AB, the company that offers MySQL, has changed their licence arrangements causing an incompatibility with the PHP licence.  There are plans to embed SQLite directly into PHP 5 thus avoiding the need for a separate Database Program on the server.  SQLite is a simple Relational Database Management System that is like a bare bones, stripped down version of MySQL. It uses generally standard Structured Query Language (SQL), with some pertinent proprietary additions. SQLite is said to be about two times faster than MySQL and other 'major' Database Systems. If it's embedded into PHP, Script School will be discussing SQLite in great detail in future Courses.
For more about SQLite and comparisons to MySQL: http://www.hwaci.com/sw/sqlite/
For a look at it's SQL syntax: http://www.hwaci.com/sw/sqlite/lang.html
If You want to begin using SQLite, there is a PEAR http://pear.php.net/manual/en/ library available: http://pear.php.net/package-info.php?pacid=193

The Concepts You will Learn in this Course are compatible with all Database Systems that use SQL.

Gather Your Tools... In Today's Class we will be gathering all of the tools necessary to use MySQL with the server side programming language PHP. You will either be connecting to a hosting company with MySQL and PHP already set up for You - OR - You will set up a complete Software Development Kit (SDK) on Your Local machine. Either way will let You work with MySQL through the free, open source Server Side Scripting Language PHP - usually within the free, open source web server environment of Apache.

Why SDK? If You're going to need a public server anyway, why bother to install an SDK locally? Mainly for speed and convenience, and to be sure You always have a local back-up of Your work. Having to upload a whole script every time You make a change that needs to be tested can really add a lot of time to the development process. Or if You only have one copy of Your work on Your public server, and You are 'working it live', on the server, there will be times when bugs crop up that will break Your site... most hosts will not restore a site to an earlier version for You if a hard to find bug crawls in... and if the server goes down, and it's harddrive permanently crashes and looses all Your hard work... You may not be a happy camper! Even if a hosting company backs up Your data, generally it is only to cover their mistakes, not Yours...

SDK Tour Complete, free SDK's for MySQL and PHP within Apache are available on the web:

If none of the above suit Your circumstances, do a quick search for: "install PHP MySQL Apache SDK"

Get it on Once You have downloaded and installed a basic SDK package, You can, and probably should, match the various parts to those in use on Your main, public server at Your hosting company, or perhaps Your companies' in house server. OK, go ahead and download and install Your SDK of choice... Go to the place You downloaded it if You have installation problems, or try another SDK... For the brave and those with plenty of extra time among You using Linux, and even those on Windows - if You have the right tools - You can also download source code and compile it locally... ultimately the best way of all. See: www.php.net, www.apache.org and www.mysql.com for the raw source code or to update the binaries in Your SDK.  Finally, there is one more tool to download and install if it doesn't come with Your SDK or if You have installed Your own components from source code: phpMyAdmin, available at: http://www.phpMyAdmin.net/ phpMyAdmin provides a powerful browser based interface for MySQL which You can use throughout this course and afterwards to make Your work much easier.

Getting Connected After You have installed Your SDK or established a service with a hosting company that provides You with MySQL and PHP, there are several important parameters You will be using. These are Your host connection address, Your username and Your password. If You are using a hosting company these will be provided to You, if You are using an SDK these may default to localhost, root and admin or something similar and should be changed to more secure settings. Follow the instructions that come with Your SDK to alter these. Or use the phpMyAdmin interface.

Set Username and Password With a tool like phpMyAdmin it is easy to set local Usernames, Passwords and various Privileges: From the phpMyAdmin Home page, click Users, and follow the obvious options. For greater depth please see the MySQL Manual: http://www.mysql.com/doc/en/Privilege_system.html

Databases vs. Database Management Systems
MySQL is a Database management system, not a Database. With a MySQL server, You can operate many simultaneous Databases for different purposes, including web sites and stand alone applications. In the next several sections we will concentrate on creating a Database and within it, a test Table.

Field Types Before we create our Database and the Table it will contain, we must discuss some basic Field Types.
We will start with three: TINYINT, MEDUIMINT and DATE.
TINYINT: is for very small integers. The signed range is -128 to 127. The unsigned, or positive range is 0 to 255.
MEDUIMINT: has a signed range from -8388608 to 8388607. The unsigned, positive range is 0 to 16777215.
DATE: as 'YYYY-MM-DD'

For a full explanation of these Field Types in MySQL see: http://www.mysql.com/doc/en/Column_types.html and http://www.mysql.com/doc/en/DATETIME.html

Indexes We also need to briefly examine indexing. Indexes allow MySQL to find data more rapidly - a Primary index is a Unique index field, meaning that rows cannot contain duplicate values - in that Field, in that Table. The Primary Index is the main or first index used. Other KEY Fields may also be Unique or simply be Indexed. See the MySQL manual for further discussion: http://www.mysql.com/doc/en/MySQL_indexes.html

Another aspect we'll need to discuss now is called 'auto_increment'. It is for the first index KEY and as the name implies, it automatically increments the KEY value for us to use - when nothing or 'NULL' is entered into the Field. You can override the auto_increment by entering Your own value, so long as it is not already in use in that Field, in that Table.

Create Database/Create Table

Using phpMyAdmin:
In the text area of "Create new database" Type in a name for Your Database...

Name Conventions:

Avoid spaces in the name and numbers at the beginning of the name - similar to variable naming in most scripting languages... You can use any characters that are allowed in directory names except forwardslash: /, backslash: \ or . (dot) The name can be up to 64 characters long.

Table names are the same but allow backslash: \ although this is not recommended.

Perhaps use a Database name like: ssMySQLclass
Then click the Create Button. You will see another page to create Your first table:
  1. enter a name for the Table and the number of fields it will contain...
    such as: ShoppingList and 3 for fields.
  2. Click Go.
  3. Field 1 name:id Type:MEDIUMINT Attributes:UNSIGNED Extra:auto_increment and Primary
  4. Field 2 name:shoppingday Type:DATE
  5. Field 3 name:cookies Type:TINYINT Attributes:UNSIGNED
  6. Click Save.
Then You can Click Insert to start to fill Your Table with some test data. You can leave the id field blank, for the shoppingday Field scroll down the function list to CURDATE for the current date or enter a date in YYYY-MM-DD format. Enter the number of cookies You want to buy on that date. Then Click Browse... You will see that the auto_increment Field id has entered 1, the shoppingday Field contains the current date or the date You entered and the number of cookies You want to buy will be shown - if You entered a reasonable number there! Recall that the TINYINT Field Type will only allow You to buy up to 255 cookies!  Hey, more than that, and You might not eat Your dinner! The point is, to match Your Field Types to Your actual needs - using a Field Type with less capacity than You need has obvious consequences, using one larger than you really need will not be as efficient!

Using PHP:
If You are not familiar with PHP You can take the Script School Course on it at: http://www.scriptschool.com/php/  The rest of this Course will utilize PHP extensively.

We are about to use several PHP functions that are designed to interface with MySQL. The PHP manual is available at: http://www.php.net/manual/en/ Each function will also be a link to it's description in the PHP manual.

Power tip: if you would like to search for a function from the php.net site just put the function name after the domain name. Let's say you want to search for mysql_query you would put this into your browser: http://www.php.net/mysql_query   and you would go directly to the manual page for the PHP mysql_query function.

It will become apparent that Queries are directives and are not merely questions! 

The SQL (Structured Query Language) we are using in our Queries
is generally compatible in all Relational Database Management Systems (RDMS), however there are occasionally some proprietary differences, and if you are applying the material in this course to a system other than MySQL, You should check the manual for Your RDMS.

The host, username and password is provided by your hosting company or company's administrator, or can be altered on a local machine using an application like phpMyAdmin. If You already created this Database with phpMyAdmin, delete it with phpMyAdmin before proceeding with the following PHP 4.x code example.
(see note above for anticipated changes in PHP 5)

<?php

// Connect to Your MySQL server:
$db = mysql_connect("host","username", "password")
or die("Could not connect:". mysql_error());
print("Connected successfully");

/* The syntax for Database Creation in MySQL is:
   CREATE DATABASE [IF NOT EXISTS] db_name
*/

// So in php we can use the following Query:
$query_create_db = "CREATE DATABASE IF NOT EXISTS ssMySQLclass";

// And then implement it:
$result_create_db = mysql_query($query_create_db,$db)
or die("Could not Create Database:". mysql_error());
print("Database Created");

// Before we can use a database, we have to connect to it:
mysql_select_db("ssMySQLclass",$db)
or die("Could not Connect to database:". mysql_error());
print("Connected to Database");

// Then write a query to create the Table:
$query_create_table = "CREATE TABLE shoppinglist (
id MEDIUMINT(8) UNSIGNED NOT NULL auto_increment, shoppingday DATE NOT NULL default '0000-00-00',
cookies TINYINT(4) UNSIGNED NOT NULL default '0', PRIMARY KEY (id)
)
TYPE=MyISAM";

// And Implement it:
$result_create_table =
mysql_query($query_create_table,$db)
or die("Could not Create Table:". mysql_error());
print("Table Created");

/*  For the full CREATE TABLE syntax, see the MySQL manual:
     http://www.mysql.com/doc/en/CREATE_TABLE.html
*/

?>

Save plain text script file (from <?php to ?> inclusive) on Your server as: make_ssMySQLclass_db.php
Implement it by opening it in a browser.

Note: Since we used IF NOT EXISTS in the create Database Query, the database won't throw an error at that point if the script is run again, however... In MySQL Version 3.23 and later it is possible to also add IF NOT EXISTS to the CREATE TABLE Query to avoid errors when the Table already exists.

Alternative Methods:
If You like to get into the nitty gritty nuts and bolts of command line interfaces, MySQL has a built in command line query interface: http://www.mysql.com/doc/en/mysql.html And a built-in administration interface to create databases and perform other tasks: http://www.mysql.com/doc/en/mysqladmin.html

Accomplishments and Expectations
This week's class presented simplified, streamlined methods to get You started in an exciting aspect of programming - Databases. The subject is huge and we have barely been able to scratch the surface, so far. Please dig deeper into the material in the MySQL and PHP manuals to gain further insight into the many details that were alluded to. This is a subject that can and does fill many large books, Script School hopes that this week's brief introductory class has gotten You started on a happy journey into proficiency in Databases. Next week we will create a more advanced Database that we will be using throughout the rest of this course in a practical PHP web based application to keep track of suppliers for Your business.

Project To-Do Assignments for Week #1

Basic To-Do Assignment for Week #1: Use the tools available on your host or PHP to create a test Database and connect to it.

Advanced To-Do Assignment for Week #1: Install an SDK on Your local workstation and use phpMyAdmin to create Your test Database and test Your connection to it.

Need help with your assignment or have questions about the first week's course?
 
 

School Map:  Home / Enroll $ Student Records; Class  
$ News @ LIVE Tech Radio * Support/FAQ | Store

Advertising | Link To Us | Privacy 
Copyright 2000-2002 Script School Productions / KMR Enterprises 
No part of this website may be reproduced, copied and/or distributed in any medium 
without express written permission