BRIGHAM YOUNG UNIVERSITY

  Humanities Technology and Research Support Center

CHum 310
Introduction to Online Relational Databases

Database management systems are widely used in businesses, educational institutions and other organizations to manage the information critical to their operations. Today many databases are used on the world wide web to make information available to millions of web users at the click of a mouse. Examples include online stores, government information sites, and the personal information stored by your school, employer or university.

Many of the online database systems used today share two traits in common: they are relational databases and they use some variant of a common query language called SQL (Structured Query Language) to access and manage the data they contain.

For a simple introduction to these concepts, look at the following links to a very concise tutorial on databases at Macromedia's web site:

What is a relational database?
  http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/db_basi3.htm

Understanding relational databases.
  http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/db_basi4.htm

About SQL.
  http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/db_basi5.htm

Most important SQL language elements.
  http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/db_basi6.htm

Basic SQL syntax (SQL is case-insensitive, but language elements are shown in ALL CAPS by convention, to differentiate them from the variable data.)

SELECT fields FROM tableName WHERE field=value;

INSERT INTO tableName (field1, field2, field3, etc) VALUES (value1, value2, value3, etc);

UPDATE tableName SET field1=value1,field2=value2,etc WHERE field=value;

DELETE from tableName WHERE field=value;

Practice the SELECT command

II. Look at the chum381 database that has been set up for you to practice on.

Look at mysql in terminal (/Applications/Utilities/Terminal)

1. log in to the server that hosts the database:
   ssh chum310@hummac.byu.edu
   enter the chum310 password when prompted (Type 'yes' if asked a yes/no question here.)
   
2. Load the MySQL program:
   mysql -p (Type the password when prompted.)
   
3. Once you've entered the mysql program, select the correct database (the semicolon is required):
   show databases; (list the available databases)
   
4. Select the database you want:
   use chum381db;
   
5. Let's see what tables are in this database:
   show tables;
   
6. Look at how a table is structured:
   describe people;
   
7. Now you can use any of the commands above to query (lookup data) in the database, insert records in the database, or update data in existing records.

Exercise

Practice using SQL commands in the chum381db database

1. Add yourself to the people table. For example, I would add myself like this:
   INSERT INTO people (PIN,lname,fname) VALUES (1234,'Asay','Devin');
2. Now query the database to make sure it was successful:
   SELECT * from people;
   (This will list all of the data in the table. The * is SQL shorthand for 'all of the fields in the table'.)
3. Change your PIN with the UPDATE command:
   UPDATE people SET pin=4321 WHERE lname='asay';
4. ONE PERSON from each group: use the INSERT command to insert information about your project into the project database:
       EVERYONE ELSE: if you wish, you can make up a fictional project and insert it into the database.
       HINT: use the command
         DESCRIBE project;
       to see what kind of information the project table wants. Remember that the proj_id field is auto-incrementing, so you don't need to supply a value for that field.
5. Look up the proj_id number that was assigned to your group then use that proj_id as the entry for the group field for your record in the people table.
6. Choose a department (or add a department, then choose the one you added) to belong to. Use that department's dept_id to UPDATE your people record in the dept field.
7. Do a SELECT query that will return all of the first and last names of all the people in your project group.
   The WHERE clause should look something like: WHERE projgroup = 1. (Substitute the proj_id number you want for 1.)

AFTER THE EXERCISE...

While a MySQL database can be managed this way, it becomes tedious and cumbersome as the database grows larger. Fortunately there are a number of graphical interfaces for MySQL. One of the best is phpMyAdmin, web-based graphical interface to MySQL databases that allows us to visualize the data in a much more user-friendly format.

To see the database in phpMyAdmin:

Open a web browser and type in the address field:

http://hummac.byu.edu/phpmyadmin
Supply the chum310 user name and password when prompted.
On the left-hand column, choose chum381db from the popup list of databases. You'll see a list of all of the tables in the database. Notice that for each table you can browse, select (query), insert, etc. This interface simply implements the SQL commands using point-and-click instead of entering commands in a command line. Notice that you can even enter SQL statements if you choose, by clicking the SQL tab.