Lesson 2: Preparing the consolidated database

In this lesson you connect to the consolidated database, create the CustomerProducts table, and alter the Customers table to include regional information.

Prerequisites

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 Privileges section at the start of this tutorial: Tutorial: Using MobiLink with a SQL Anywhere consolidated database.

Context and remarks

In a synchronization system, the primary key of a table is the only way to uniquely identify a row in different databases and the only way to detect conflicts. Every table that is being mobilized must have a primary key. The primary key must never be updated. You must also guarantee that a primary key value inserted at one database is not inserted at another database.

In a later lesson, the remote schema is created from the consolidated schema so the remote schema has the same primary keys as the consolidated schema.

Columns were specifically chosen to ensure unique primary keys for all databases. For the Customers table, the primary key consists of the ID column. Any value inserted into the remote Customers table must have a unique customer ID number (the Region value is always the same). This practice ensures uniqueness in each remote Customers table. The primary key in the consolidated Customers table prevents conflicts if multiple salespeople upload data. Each upload from a region is unique from another region because their Region values are different.

Task

Click Start » Programs » SQL Anywhere 16 » Administration Tools » Sybase Central.
Click Connections » Connect With SQL Anywhere 16.
Perform the following tasks in the Connect window:
1. In the Action dropdown list, choose Connect With An ODBC Data Source.
2. In the ODBC Data Source name field, type SQL Anywhere 16 Demo.
3. Click Connect.
Connect to your consolidated database in Interactive SQL.

At a command prompt, run the following command:
dbisql -c "DSN=SQL Anywhere 16 Demo"

In Interactive SQL, execute the following statements to create and insert data in the CustomerProducts table:

CREATE TABLE CustomerProducts
    (ID int default AUTOINCREMENT PRIMARY KEY,
    SalesOrderID int NOT NULL, 
    CustomerID int NOT NULL, 
    ProductID int);

INSERT INTO CustomerProducts (SalesOrderID,CustomerID,ProductID)
    SELECT SalesOrders.ID, SalesOrders.CustomerID, SalesOrderItems.ProductID
    FROM SalesOrders, SalesOrderItems
    WHERE SalesOrders.ID = SalesOrderItems.ID;

In Interactive SQL, execute the following statements to add regional information for each customer to the Customers table:

ALTER TABLE Customers
    ADD Region VARCHAR(255);

UPDATE Customers
    SET Region = (SELECT TOP 1 SalesOrders.Region
    FROM SalesOrders 
    WHERE Customers.ID = SalesOrders.CustomerID
    ORDER BY Region);
COMMIT;

Results

A connection is made to the SQL Anywhere Demo database, a table named CustomerProducts is created, and changes are made to the Customers table to include regional information.

Next

Proceed to Lesson 3: Creating a synchronization model.

Lesson 2: Preparing the consolidated database

Task

See also