Lesson 2: Preparing the consolidated database

The OE database needs to be altered for use with MobiLink. Columns are dropped because they were created as user-defined types. You could translate these user-defined types into types that SQL Anywhere recognizes, but doing so is not relevant to this tutorial. You must also grant the OE user the privilege to create triggers because MobiLink needs to create triggers using OE's credentials.

Prérequis

This tutorial assumes that you have installed the Order Entry (OE) sample database. Information about installing the sample schema for Oracle 10g can be found in the Oracle documentation at [external link] http://docs.oracle.com/cd/B14117_01/server.101/b10771.pdf.

This lesson assumes you have completed all preceding lessons. See Lesson 1: Designing the schemas.

This lesson assumes you have the roles and privileges listed in the Permissions and privileges section at the start of this tutorial: Tutorial: Using MobiLink with an Oracle Database 10g.

Contexte et remarques

Information about installing the sample schema for Oracle 11g can be found in the Oracle documentation at [external link] http://docs.oracle.com/cd/B28359_01/server.111/b28328.pdf.

Task

Connect as the SYS user with SYSDBA privileges using the Oracle SQL Plus application. At a command prompt, run the following command:
sqlplus SYS/your password for sys as SYSDBA

To drop columns created as user-defined types, execute the following statements:

ALTER TABLE OE.CUSTOMERS DROP COLUMN CUST_ADDRESS;
ALTER TABLE OE.CUSTOMERS DROP COLUMN PHONE_NUMBERS;
ALTER TABLE OE.CUSTOMERS DROP COLUMN CUST_GEO_LOCATION;
ALTER TABLE OE.PRODUCT_INFORMATION DROP COLUMN WARRANTY_PERIOD;

To unlock the OE user and set the password to sql, execute the following statement:
ALTER USER OE IDENTIFIED BY sql ACCOUNT UNLOCK;
To allow the OE user to create triggers, execute the following statement:
GRANT CREATE ANY TRIGGER TO OE;

To drop the orders_customer foreign key and create a new foreign key that references the customer_id in the customers table, run the following commands:

ALTER TABLE OE.ORDERS DROP CONSTRAINT ORDERS_CUSTOMER_ID_FK;
ALTER TABLE OE.ORDERS ADD CONSTRAINT ORDERS_CUSTOMER_ID_FK
  FOREIGN KEY (CUSTOMER_ID) REFERENCES OE.CUSTOMERS (CUSTOMER_ID);

Résultat

Columns created as user-defined types are dropped and the OE user is now able to create triggers.

Suivant

Proceed to Lesson 3: Adding unique keys to the consolidated database.

Lesson 2: Preparing the consolidated database

Task

See also