Difference between revisions of "Database Basics"

Revision as of 19:37, 24 September 2007

What is a Database?

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 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.

Understanding Database Tables

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.

Introducing Database Schema

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.

Columns and Datatypes

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.

Database Rows

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.