Lesson 2: Preparing the consolidated database

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

This tutorial assumes that you have installed the Order Entry (OE) sample database. Information about installing the sample schema can be found in the Oracle documentation or at [external link] http://www.oracle.com/technology/obe/obe1013jdev/common/OBEConnection.htm.

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. Next, you must grant permission to the OE user to create triggers because MobiLink needs to create triggers using OE's credentials.

 Prepare the consolidated database

  1. 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

  2. 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;

  3. To unlock the OE user and set the password to sql, execute the following statement:

    ALTER USER OE IDENTIFIED BY sql ACCOUNT UNLOCK;

  4. To allow the OE user to create triggers, execute the following statement:

    GRANT CREATE ANY TRIGGER TO OE;

  5. 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);

  6. Proceed to Lesson 3: Adding unique keys.

 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1