User's Guide Part III. Using SQL Anywhere Chapter 16. Designing Your DatabaseThis section introduces some of the terms and concepts that are important in talking about relational databases.
In a relational database, all data is held in tables, which are made up of rows and columns.
Each table has one or more columns, and each column is assigned a specific data type, such as an integer number, a sequence of characters (for text), or a date. Each row in the table has a value for each column.
A typical fragment of a table containing employee information may look as follows:
| emp_ID | emp_lname | emp_fname | emp_phone |
|---|---|---|---|
| 10057 | Huong | Zhang | 1096 |
| 10693 | Donaldson | Anne | 7821 |
The tables of a relational database have some important characteristics:
The following table lists some of the formal and informal relational database terms describing tables and their contents, together with their equivalent in other nonrelational databases. This manual uses the informal terms.
| Formal | Informal | Equivalent |
|---|---|---|
| relational term | relational term | nonrelational term |
| Relation | Table | File |
| Attribute | Column | Field |
| Tuple | Row | Record |
Primary and foreign keys enable each row in the database tables to be identified, and enable relationships between the tables to be defined. These keys define the relational structure of a database.
Each table in a relational database has a primary key. The primary key is a column, or set of columns, that allows each row in the table to be uniquely identified. No two rows may have the same value of a primary key.
In a table holding information about employees, the primary key may be an ID number assigned to each employee.
In the sample database, the table of sales order items has the following columns:
The information in one table is related to that in other tables by foreign key relations.
The sample database has one table holding employee information and one table holding department information. The department table has the following columns:
To find out a particular employee's department, there is no need to put the name of the employee's department into the employee table. Instead, the employee table contains a column holding the department ID of the employee's department. This is called a foreign key to the department table. A foreign key references a particular row in the table containing the corresponding primary key.
In this example, the employee table (which contains the foreign key in the relationship) is called the foreign table or referencing table. The department table (which contains the referenced primary key) is called the primary table or the referenced table.
If a primary key is not assigned, the combination of all columns in the table becomes the primary key. This can lead to a very large transaction log.
A relational database holds more than a set of related tables. Among the other objects that make up a SQL Anywhere relational database are:
All these objects are in some way built on top of the base tables that hold the information. A base table is a table that is stored permanently in the database. This chapter discusses only how to decide what tables you need and what each table needs to hold.