A database is an electronic storage place for data. Databases are designed to ensure that data is valid and consistent, and that it can be accessed, modified, and shared.
A database management system (DBMS) governs the activities of a database and enforces rules that ensure data integrity. A relational DBMS stores and organizes data in tables.
You can use PocketBuilder to work with the following database components:
Tables and columns
Keys
Indexes
Database views
Extended attributes
Additional database components
A database usually has many tables, each of which contains rows and columns of data. Each row in a table has the same columns, but a column’s value for a particular row could be empty or NULL if the column’s definition allows it.
Tables often have relationships with other tables. For example, in the ASADemo_10 database, the Department table has a dept_id column, and the Employee table also has a dept_id column that identifies the department in which the employee works. When you work with the Department table and the Employee table, the relationship between them is specified by a join of the two tables.
About the demo database
The ASADemo_10 database is a
migrated version of the Adaptive Server Anywhere 9 Sample database
that you can use with SQL Anywhere 10. The PocketBuilder setup program
installs this database in the Code Examples\SA10DemoData directory.
You must create an ODBC data source name for this database in the
ODBC Administrator before you can use it. Table and column names
in this database are different than the names in the SQL Anywhere
10 Demo database that installs with SQL Anywhere.
Relational databases use keys to ensure database integrity.
Primary keys A primary key is a column or set of columns that uniquely identifies each row in a table. For example, two employees might have the same first and last names, but they have unique ID numbers. The emp_id column in the Employee table is the primary key column.
Foreign keys A foreign key is a column or set of columns that contains primary key values from another table. For example, the dept_id column is the primary key column in the Department table and a foreign key in the Employee table.
Key icons In PocketBuilder, columns defined as keys are displayed with key icons that include a P for primary or an F for foreign. PocketBuilder automatically joins tables that have a primary/foreign key relationship, with the join on the key columns.
Figure 16-1: Links between tables in Object Layout view
For more information, see “Working with keys”.
An index is a column or set of columns you identify to improve database performance when searching for data specified by the index. You index a column that contains information you will need frequently. Primary and foreign keys are special examples of indexes.
You specify a column or set of columns with unique values as a unique index, represented by an icon with a single key.
You specify a column or set of columns with values that are not unique as a duplicate index, represented by an icon with two keys.
For more information, see “Working with indexes”.
If you often select data from the same tables and columns, you can create a database view of the tables. You give the database view a name, and each time you refer to it, the associated SELECT command executes to find the data.
Database views are listed in the Objects view of the Database painter and can be displayed in the Object Layout view, but a database view does not physically exist in the database in the same way that a table does. Only its definition is stored in the database, and the view is re-created whenever the definition is used.
Database administrators often create database views for security purposes. For example, a database view of the Employee table that is available to users who are not in Human Resources might show all columns except Salary.
For more information, see “Working with database views”.
Extended attributes enable you to store information about a table’s columns in special system tables. Unlike tables, keys, indexes, and database views (which are DBMS-specific), extended attributes are specific to PocketBuilder. The most powerful extended attributes determine the edit style, display format, and validation rules for the column.
For more information about extended attributes, see “Specifying column extended attributes”. For more information about the extended attribute system tables, see Appendix A, “Extended Attribute System Tables.”
