Identify 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.
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:
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. 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.
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. In the next lesson, common database systems will be discussed.
iterate: To execute program statements zero or more times in a loop structure.
Relational database: This is a database that is organized and accessed based on relationships between tables, rows, and columns.