|Lesson 2||How data is stored |
|Objective ||Identify table structures used in 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.
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:
customers, books, and 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
This forms a relational database . In the real world, though, the customer and inventory databases would probably be
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.
Relational database: database that is organized and accessed based on relationships between tables, rows, and columns.
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 through the customer table and prepare a sales report for each customer.
iterate: To execute program statements zero or more times in a loop structure. In the next lesson, common database systems will be discussed.