Database Basics

The chances are that if you have ever logged into a web site or purchased an item on the internet you have interacted with a database in some way. Anything that involves the retreval or storage of information on a computer system is most likely to involve a database. In fact, databases are the core of just about every application that relies on data of some form to complete a task.

The first step in learning MySQL is to understand the difference between a database and a database management system (DBMS). The term database refers to the entity which stores the actual data (such as ID numbers, names and address for example) in a structured way. A database management system (DBMS) on the other hand, refers to the software used to store, access and manipulate the data stored in the database. All interactions with the database are always performed via the DBMS.

Modern databases and database management systems are not restricted to storing just text. Today, databases are used to store such items images, videos and software objects.

Database Tables provide the most basic level of data structure in a database. Each database can contain multiple tables and each table is designed to hold information of a specific type. For a example, a database may contain a customer table which contains the name, address and telephone number for all the customers of a particular business. The same database may also include a products table used to store the product descriptions with associated product codes for the items sold by the business.

Each table in a database is assigned a name which must be unique within that particular database. A table name, once assigned to a table in one database, may only be re-used in different databases.

Database Schema define the characteristics of the data stored in a database table. For example, the table schema for a customer database table might define that the customer name is a string of no more than 20 characters in length, and that the customer phone number is a numerical data field of a certain format.

Schema are also used to define the structure of entire databases and the relationship between the various tables contained in each database.

It is helpful at this stage to begin to view a database table as being similar to a spreadsheet where data is stored in rows and columns.

Each column represents a data field in the corresponding table. For example, the name, address and telephone data fields of a table are all columns.

Each column, in turn, is defined to contain a certain datatype which dictates the type of data the column can contain. A column designed to store numbers would, therefore, be defined as a numerical datatype.

Each new record that is saved to a table is stored in a row. Each row, in turn, consists of the columns of data associated with the saved record.

Once again, consider the spreadsheet analogy described earlier in this chapter. Each entry in a customer table is equivalent to a row in a spreadsheet and each column contains the data for each customer (name, address, telephone etc). When a new customer is added to the table, a new row is created and the data for that customer stored in the corresponding columns of the new row.

Rows are also somethings referred to as records and these terms can generally be used interchangeably.

Each database table must contain one or more columns that can be used to uniquely identify each row in the table. This is known in database terminology as the Primary Key. For example, a table may use the bank account number column as the primary key. Alternatively, a customer table may use the customer's social security number of the primary key.

Primary keys allow the database management system to uniquely identify a specific row in a table. Without a primary key it would not be possible to retrieve or delete a specific row in a table because there can be no certainty that the correct row has been selected. For example, suppose a table existed where the customer's last name had been defined as the primary key. Imagine then the problem that might arise if more than one customer called "Smith" was recorded in the database. Without some guaranteed way to uniquely identify a specific row it would be impossible to ensure the correct data was being accessed at any time.

Primary keys can comprise a single column or multiple columns in a table. To qualify as a single column primary key, no two rows can contain matching primary key values. When using multiple columns to construct a primary key, individual column values do not need to be unique, but all the columns combined must be unique.

Finally, whilst primary keys are not mandatory in database tables their use is strongly recommended.

As discussed previously, a database management system (DBMS) provides the means to access the data stored in a database. One of the key methods for achieving this is via a language called the Structured Query Language. This is usually abbreviated to SQL and pronounced Sequel.

SQL is essentially a very simple and easy to use language designed specifically to enable the reading and writing of database data. Because SQL contains a small set of keywords it can be learned quickly. In addition, SQL syntax is more or less identical between most DBMS implementations, so having learned SQL for one system, it is likely that your skills will transfer to other database management systems.

Throughout the remainder of this book particular attention will be paid to explaining the key SQL commands so that the reader will be proficient in using SQL to read and write database data.