SQL Anywhere sample database

For consistency and simplicity, many of the examples throughout the documentation use the SQL Anywhere sample database, %SQLANYSAMP16%\demo.db. By default this file is installed in the SQL Anywhere samples directory (for example, C:\Users\Public\Documents\SQL Anywhere 16\Samples on Windows 7).

The sample database uses the following default user ID and password:

User ID = DBA
Password = sql (passwords in SQL Anywhere are case sensitive)

Caution

The sample database always has the same user ID and password; it is recommended that you change the DBA user ID and password to restrict access to the database. See Changing a password (Sybase Central).

The sample database uses the following ODBC data source: SQL Anywhere 16 Demo.

The sample database represents a small company that sells athletic clothing. It contains internal information about the company (employees, departments, and finances), product information, and sales information (sales orders, customers, and contacts). All data in the database is fictional.

The following figure displays the tables in the sample database and how they are related to each other. The boxes represent tables, and the arrows represent foreign key relationships.

For instructions on how to connect to demo.db, see Tutorial: Connecting to the sample database.

The SQL Anywhere sample database, including tables, column names, primary keys, and foreign key relationships.

Roles in the sample database

The sample database contains one user-extended role (GROUPO) and three user-defined roles (READ_ROLE, MODIFY_ROLE, and EXEC_ROLE).

GROUPO is a user that has been extended as a role. GROUPO is the owner of the tables described above. It is also the owner of a number of sample views, materialized views, and stored procedures that are defined in the sample database. Any new user that is granted the role GROUPO can reference these database objects without the owner qualifier. For example, they can refer to the Departments table without adding the owner prefix (GROUPO.Departments).

Any user that is granted the role READ_ROLE has read access to the rows in the tables described above. Users with this role can perform a SELECT on any of these tables.

Any user that is granted the role MODIFY_ROLE has write access to the rows in the tables described above. Users with this role can perform an INSERT, UPDATE, or DELETE on any of these tables.

Any user that is granted the role EXEC_ROLE can execute any of the sample stored procedures that are defined in the sample database with the exception of ManageContacts (ShowContacts, ShowCustomers, ShowCustomerProducts, ShowProductInfo, ShowSalesOrders, ShowSalesOrderDetail, ShowSalesOrderItems, and debugger_tutorial). The ManageContacts stored procedure is used to add, delete and update contacts in the Contacts table.

Any user with only roles READ_ROLE, MODIFY_ROLE, or EXEC_ROLE cannot perform schema changes on the database. For example, they cannot add or remove columns from existing tables nor can they create or delete tables. Additional privileges are required for this.

In a large enterprise, there would be many such roles. For example, a Sales department might own the sales-related and customer-related tables, a Manufacturing or Purchasing department might own the products-related tables, a Human Resources department might own the employees-related and departments-related tables, and so on. Each of these departments would have their own collection of roles set up with various access rights to the tables they own. However, the sample database reflects a small enterprise and the use of roles has been deliberately kept simple.

The user "DBA" has all privileges for the sample database and can alter the database schema, create new users, add roles, and so on. This user has been granted the GROUPO user-extended role so that it can reference the sample tables and stored procedures without qualifying them with an owner ID.

Two additional users have been created to demonstrate the roles described above.

The user "UPDATER" (password "update") has been granted the roles READ_ROLE, MODIFY_ROLE, and EXEC_ROLE. This user can perform a SELECT, INSERT, UPDATE, or DELETE on any of the sample tables. This user has EXECUTE privilege on all of the sample stored procedures. The GROUPO user-extended role is also granted so that this user can reference the sample tables and stored procedures without qualifying them with an owner ID.

The user "BROWSER" (password "browse") has been granted the roles READ_ROLE, and EXEC_ROLE. This user can perform a SELECT on any of the sample tables but cannot change the rows in the tables in any way. This user has EXECUTE privilege on all of the sample stored procedures with the exception of the ManageContacts stored procedure since this procedure is used to add, delete and update contacts in the Contacts table (the role MODIFY_ROLE is required to execute ManageContacts). The GROUPO user-extended role is also granted so that this user can reference the sample tables and stored procedures without qualifying them with an owner ID.