Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 » SQL Anywhere 12 - Introduction » Data management technologies » Relational database concepts


Other database objects

There is more to a relational database than simply a set of related tables. You can also find the following objects in a relational database:

  • Indexes   Indexes allow quick look up of information. Conceptually, an index in a database is like an index in a book. In a book, the index relates each indexed term to the page or pages on which that word appears. In a database, the index relates each indexed column value to the physical location at which the row of data containing the indexed value is stored.

    Indexes are an important design element for high performance. You usually must create indexes explicitly, but indexes for primary, foreign keys, and for unique columns are created automatically. Once created, the use of indexes is transparent to the user. See Indexes.

  • Text indexes   Text indexes store complete positional information for every instance of every term in every indexed column. When you perform a full text search, a text index is used to find matching rows. For this reason, queries that use text indexes can be faster than those that must scan all the values in the table. See How to manage text indexes, and Full text search.

  • Login policies   Login policies consist of a set of rules that are applied when you create a database connection for a user. See Managing login policies.

  • Views   Views are temporary tables. They look like tables to client applications, but they do not hold data. Instead, whenever they are accessed, the information in them is computed from the underlying tables.

    The tables that actually hold the information are sometimes called base tables to distinguish them from views. A view is defined with a SQL query on base tables or other views.

    See Working with views.

  • Materialized views   SQL Anywhere also supports materialized views. A materialized view is a view whose result set has been computed and stored on disk, similar to a base table. Conceptually, a materialized view is both a view (it has a query specification) and a table (it has persistent materialized rows). So, many operations that you perform on tables can be performed on materialized views as well. For example, you can build indexes on, and unload from, materialized views.

    Materialized views are ideal for environments where the database is large, frequent queries result in repetitive aggregation and join operations on large amounts of data, and access to up-to-the-moment data is not a critical requirement. See Working with materialized views.

  • Stored procedures and triggers   These are routines held in the database that act on the information in the database.

    You can create and name your own stored procedures to execute specific database queries and to perform other database tasks. Stored procedures can accept parameters and return result sets. For example, you might create a stored procedure that returns the names of all customers who have spent more than the amount that you specify as a parameter in the call to the procedure.

    A trigger is a special stored procedure that automatically fires whenever a user updates, deletes, or inserts data, depending on how you define the trigger. You associate a trigger with a table or columns within a table. Triggers are useful for automatically maintaining business rules in a database.

    You can also install Java classes into the database. Java classes provide a powerful way of building logic into your database. See Creating a Java class for use with SQL Anywhere.

    See Using procedures, triggers, and batches.

  • Users and groups   Each user of a database has a user ID and password. You can set permissions for each user so that confidential information is kept private and users are prevented from making unauthorized changes. Users can be assigned to groups to make the administration of permissions easier.

    See Managing user IDs, authorities, and permissions.

In addition to these common database objects, SQL Anywhere also provides advanced features: