| Navigation: User's Guide and Tutorials > The Dictionary >====== The Basics of Database Design ====== |    |
Overview:
This lesson describes relational database concepts. The purpose of this lesson is to familiarize you with the meaning of certain terms commonly used when creating database applications.
Objectives:
At the completion of this lesson, you should:
·Understand the concept of files, records, and fields.
·Understand the concept of keys.
·Understand the common types of file relationships.
Introduction
This Lesson is a review of relational database theory. For a more complete discussion of Relational Database theory and design, see Clarion's support of Database Design.
Anatomy of a Data File
A data file is an ordered set of information organized into a single storage entity containing multiple entries called records. In SQL databases, a file may also be referred to as a table. This terminology is used interchangeably throughout the Clarion Environment.
Records
Records are an entire set of information in a data file related to a single item or entry in the file. Data files contain many items or records. In SQL databases, a record may also be referred to as a row.
Fields
Fields are individual pieces of information contained within a single record of a data file. In SQL databases, a field may also be referred to as a column.
Keys
Keys are file structures that provide ordered access of records based on the contents a specified field or fields. Keys are dynamically updated whenever the value in a key field changes.
File Relationships
Relational Databases allow data to be organized into multiple files that are related to each other. In a relational database, files are linked together through a logical relationship formed by matching values. A common field maintains the relationships between the files. Key files are built for the common fields that allow the program to rapidly access the related records when requested. The following example illustrates files organized in a relational database.
One to Many Relationships
This is often called a Parent/Child relationship and is the most common relationship found in relational databases. For every record in a primary file, there can be one or more (i.e., many) matching records found in the related file. For example, in a Contact Manager program, one Contact may have many phone numbers.
Many to Many Relationships
A many to many relationship exists when many records in the primary file can point to many records in the secondary file. Conversely, many records in the secondary file point to many records in the primary file.
One to One Relationships
This type of relationship is when there can be one and only one matching record found in the related file. This type of relationship can be used to store a large number of fields that are not necessarily used in most records, thus saving storage space.
Continue to Clarion's support of Database Design