SQLConfiguration SQLConfiguration

Database Concepts  «Prev  Next»
Lesson 2How data is stored
ObjectiveIdentify table structures used in relational databases.

Table Structures Relational Databases

A database is a collection of information that is usually related.
For example, a customer database might contain the customer's name, address, phone number, and account number. Each data set for a customer is called a customer record, and each piece of information in the data set is a data element, or field. A field is where one item of information is stored as a record. For example, an employee database might have one record per employee. The individual pieces of information such as last name, first name, social security number, etc., are fields. So a database contains tables, tables contain rows, and records contain columns.

  1. This image shows a portion of a customer database. Each row in the table represents a single record, which contains specific information for each customer.
  2. Each table column contains a specific record field. Every piece of information for each customer is stored in the individual fields. The information contained in this database includes the customer number [CustNo], last name [LName], first name [FName], address [ADDR1], and more, which is not visible in this image.
  3. The Customer Number is used to uniquely identify each record and is called a "unique key."

How information is stored
This example of a customer table contains one record per customer. The CustomerNo uniquely identifies each customer. Most databases consist of more than one table, and a common field such as the customer number, ties the tables together and produces a relationship between them. For example, in the course project, VirtualBookShelf, the database contains three tables:
  1. customers,
  2. books, and
  3. sales.

This is a simple database, but the idea is that the customer and book tables contain different information for different purposes. The sales table will contain information from these two tables when a purchase is made. When a purchase is made, information from these two tables, such as customer number and item number are written to the sales table.
This forms a relational database [2]. In the real world, though, the customer and inventory databases would probably be separate. The following graphic shows a sales transaction table that has a customer number column, which also matches the customer number column in the customer table.

Sale Table
The field Customer Number forms a relationship between the tables. CustNo 00001 in the sales table represents the same customer in the customer table. Such relationships can exist among several tables in a database. The sales table can contain more than one entry per customer for each purchase a customer makes. Using a structure such as this, an application can iterate[1] through the customer table and prepare a sales report for each customer. In the next lesson, common database systems will be discussed.

[1] iterate: To execute program statements zero or more times in a loop structure.
[2] Relational database: This is a database that is organized and accessed based on relationships between tables, rows, and columns.