Database Basics

The term "database" is used in many different ways, but for our purposes (and indeed, from SQL's perspective), a database is a collection of data stored in some organized fashion.

Databases contain tables, and data itself is stored in tables (not in databases). A table is a structured container that can store data of a specific type. A table might contain a list of customers, a product catalog, or any other list of information.

Tables are made up of columns. A column contains a particular piece of information within a table. Each column in a database has an associated data type. A data type defines what type of data the column can contain. For example, if the column were to contain a number (perhaps the number of items in an order), the data type would be a numeric data type. If the column were to contain dates, text, notes, currency amounts, and so on, the appropriate data type would be used to specify this fact.

Data in a table is stored in rows. Each record saved is stored in its own row. If you envision a table as a spreadsheet-style grid, the vertical columns in the grid are the table columns, and the horizontal rows are the table rows.

Every row in a table should have some column (or set of columns) that uniquely identifies it. A table containing customers might use a customer number column, a table containing orders might use the order ID, and an employee list table might use an employee ID or the employee Social Security number column. This column (or set of columns) that uniquely identifies each row in a table is called a primary key. You use the primary key to refer to a single row. Without a primary key, updating or deleting specific rows in a table becomes extremely difficult.

TIP

Although primary keys are not actually required, most database designers ensure that every table they create has a primary key so that future data manipulation is possible and manageable.

Any column in a table can be established as the primary key, as long as it meets the following conditions:

• No two rows can have the same primary key value.

• Every row must have a primary key value (a column cannot allow NULL values).

• The column containing primary key values can never be modified or updated.

• Primary key values can never be reused (if a row is deleted from the table, its primary key cannot be assigned to any new rows).