Introduction to Databases
Relational databases are a powerful tool, traditionally used by big business. As a result, many people have never bothered to learn about databases, and what they can do. So this article is written as a basic introduction to the concepts and practical applications of databases. So lets get started;
Not just for Big Business Anymore
Large web sites are increasingly using databases to drive their content. Practical uses like User Logins, Bulletin Board Systems, and User Surveys are all made possible by organizing information into a database.
When you break it down to the basics, databases are just software applications that organize data and information into a format that is efficiently accessed. Rather than simply storing data and information, a database can be used to create a relationship between the data; facilitating the searching, and sorting of that data.
How it is done: Tables
Databases store their information in "tables", which are data structures organized with two dimensions. You can think of a table as a spreadsheet: there are a fixed number of columns, and a variable number of rows. Each row is an individual entry, and each column identifies a certain characteristic of that entry. (The cells that result from the grid created by the columns and rows each hold a single piece of data.) So, to use a practical example, think about a Baseball Card collection. If you converted you collection of cards into a table in a database, each card would an entry in the database, and correspond to a row in the table. Each statistic on the card (name, Batting average, picture, team name etc) would be a separate column in the table.
A database is made up of several tables, each one being set up to handle a specific kind of information. Each table is given a unique name, by which your web site, program or user can reference its data. This is where databases get their power; they have the ability to combine data from interrelated tables to answer a single query.
To extract information from a database, a web site, program or user of the database `queries' in Structured Query Language (SQL). SQL is an easy to learn language that is used to search a database, and to control the format that information is outputted in. An SQL query will tell the user, program or web site what table and in what format the information can be found in. An SQL query will return results in a format much like it is found inside the database; with both rows and columns. The result is a table of matches to the criteria searched for, and the properties of each match. The table of results from a query will not (most of the time)contain the exact same structure as the source tables inside the database, but only those records and fields that are applicable to the search.
We mentioned earlier that the important feature of a database is the relationships made between tables. This relationship is facilitated by `key values' in the tables. Key values are columns in a table that are used to reference a particular record, or row. For example the key value in the Baseball Card Collection table mentioned earlier could be the `Player_Name' column, which the creator of the table can be sure is unique for each record. The unique identifier creates a simple way to reference any field in any record-ie: a quick way to pull the batting average of Sammy Sosa in 1999.
By using a unique key in each table, database access will become more efficient, and faster. The database can organize it's internal index appropriately when it expects queries based on a key value or column. In most cases, the column that contains the key values will hold integers, as they are the easiest to set up, and hence facilitate the greatest speed inside the database. However, in certain instances it may be necessary to use a string or character as the key value.
More than just Queries
SQL is meant to do much more than just retrieve information already stored in a database. SQL is also used to load, and unload data into the database, as well as create new records, update existing ones. It is necessary to note that the SQL commands are used to ad and update rows and values in the tables-not to add or delete columns.
Think for Yourself
There is a lot to learn about relational databases; much more than one article can teach. Use what you've read about here as a jumping off point for learning more. If you operate a web site, you can be confident that it can be drastically improved by using a database to drive it. Check out these sites to learn more about Relational Databases and SQL:
PCstats.com is a database driven site. We use Cold Fusion to drive our sites, provide interactive content, and to communicate with our users. Two great examples of databases, and what they can do are our Bulletin Boards, and our CPU database.