# Tutorial B: Creating and Using Schemas

Now that you’ve seen what the Schema Wizard can create, you are ready to create a dbWeb schema manually. This tutorial helps you create an order summary schema, an order detail schema, and links between them based on the dbnwind sample database.

# Creating the Order Summary Schema

After you start the dbWeb Administrator, you can start building your schemas. The following sections provides steps for using the features of the dbWeb Administrator.

To begin defining a new schema

1. In the Start Programs menu, click dbWeb Administrator.

2. In the Data Sources window, select the dbnwind data source and click New Schema.

3. From the New Schema window, click New Schema.

After selecting your data source, you are ready to define forms, set properties on the columns in the forms, and establish links.

Specifying Schema Information

Your users need to be able to query the Dbnwind database in order to answer questions like "Who placed an order for fifty units of Gustaf’s Rye Crisp Bread?" or "How many units of Chef Anton’s Cajun Seasoning did the Great Lakes Food Market order?"

The answers to questions like these reside in the Orders, Customers, Order Details, and Products tables of the Dbnwind database. You need to create schemas that query all these tables and present the information in a manner useful to your users. These schemas should reflect a conventional ordering system that initially displays a tabular form listing customer orders sorted by order ID. Users should then be able to select a row of this table and view line-item details of the order.

In the following example, you will create two dbWeb schemas named Order_Summary and Order_Details that will allow queries based on customer information. The Order_Summary schema is actually very similar to the one you created in the previous section using the Schema Wizard.

To enter schema information

1. In the New Schema dialog box, click New Schema to display the Schema tab.

2. In the Schema name box, type "Order_Summary", and then press TAB to advance to the next entry field.

Note: Each dbWeb schema requires a unique name. The schema name must begin with a letter, but you can use a combination of letters, numbers, the underscore character(_), and the dash (-) for the rest of the name.

3. In the Browser title bar box, type "Northwind Customer Orders", and then press the TAB key to advance to the next entry field.

4. If you would like anyone browsing your data on the World Wide Web or your company’s internal network to provide feedback through electronic mail, enter an e-mail address in the Mail comments to box. This text will appear at the bottom of all forms. Using HTML syntax, you can also make this text a hyperlink that opens a Web browser’s mail editor when clicked.

5. If you want browsers to see more information about the data forms you design with Microsoft dbWeb, enter the resource information in the Page help URL box. Resources are not limited to Web sites, but also include gopher sites, Telnet sites, ftp sites, and file resources on the Internet.

6. In the Default max rows box, enter "100". This is the maximum number of rows returned from a query for this schema. If the value specified here is less than the maximum number of returned rows for the data source, then Microsoft dbWeb will respect this value and return no more than this number of rows on all queries made with this schema. If the value specified is greater than the maximum number of returned rows for the data source, then Microsoft dbWeb will ignore that value and default to the value that you specified for the data source.

7. In the Allow actions on data check box, leave all the check boxes cleared to prevent users of the dbnwind database from changing the data. If you select any of these options, you enable the Ins/Upd/Del tab in the Schema window and users can change your data.

8. In the Database object type area, make sure Table(s) or view(s) is selected, because the underlying data objects used for this Order_Summary schema are database tables.

9. In the SQL area, select Select distinct records only. By default, Microsoft dbWeb generates SQL queries without the DISTINCT option in the SELECT clause. This would cause the query to return repeating records in its results. While this effect is desirable in some cases, the Northwind users are interested in viewing only distinct orders.

The Schema window on your screen should now look like this.

Figure 9. Schema window: Schema information tab panel

Adding Tables

Using Microsoft dbWeb, you can choose which tables in your data source you want users to browse. In the Order_Summary schema, you can provide access to customer and order information by adding the Customers and Orders tables from the Dbnwind database.

To add tables

1. Click the Tables tab.

2. From the Tables in datasource list, select the Customers table and click Add.

3. Select the Orders table and click Add.

The Schema window on your screen should now look like this:

Figure 10. Schema window: Tables tab panel

Joining Tables

With Microsoft dbWeb, you can join tables on a common column. When including more than one table in a schema, you must join all tables.

You must join columns of similar or the same data type. For instance, you can join a column of type "smallint" (small integer) and another of type "integer." Microsoft dbWeb will not prevent you from joining columns of dissimilar data type, though such joins will most likely result in a data type mismatch error from the underlying database.

Apart from defining the relationship between tables in a schema, you can also impose filters or constraints on the data in a table so that only a subset of all the rows in that table are accessible. This feature is useful when you want to control what information is available to users browsing your data.

While it is not necessary to define data constraints in this Northwind example, you will need to establish relationships between the tables you have included in the Order_Summary schema.

Tip: For a diagram of the dbnwind database, see Appendix B#appb.

To join tables

1. Click the Joins tab.

2. Click New Join to display the Joins dialog box.

Figure 11. Joins dialog box

3. From the Tables list, double-click Customers to display the list of data columns that comprise the table, and then select Customer ID.

4. From the Related table list, double-click Orders to display the list of data columns that comprise the table, and then select Customer ID.

5. From the drop-down list box between the two boxes, select the equal sign (=) comparison operator to establish the relationship between the columns you have just selected.

6. Click OK to create the new join.

The Schema window on your screen should now look like this.

Figure 12. Schema window: Joins tab panel

Defining the Query Form

The query by example (QBE) form in Microsoft dbWeb is the entry point to browsing your database. Through this form, users can interactively issue queries to your database and receive results in real time. Building a QBE form involves compiling a list of data columns that will participate in the query. You can also create new columns that are computed from existing columns in the schema. The expressions for these computed columns can perform simple arithmetic, manipulate character strings, call ODBC scalar functions, or call native database functions. You will need to set the properties for each column participating in the query.

In our Northwind example, the president would like her distributors to query for customer order information by the customer’s name: (Customers.[Company Name] and Customers.[Contact Name]), city (Customers.[City]), region or state (Customers.[Region]), postal code (Customers.[Postal Code]), and country (Customers.[Country]).

To set query information

1. Click the QBE tab.

2. From the Data columns in selected tables list, double-click Customers to display the data columns that comprise the table, and then select Company Name.

3. Click Add to add that column to the QBE data columns list on the right

4. Repeat steps 1 and 2 for the Contact Name, City, Region, Postal Code, and Country data columns in the Customers table.

When you are done, the QBE tab panel of the Schema window on your screen should resemble the following:

Figure 13. Schema window; QBE tab panel

Because you do not need to set any properties at this time for the columns you chose, you are ready to define the tabular output for this query.

Defining Tabular Output

The tabular form in Microsoft dbWeb displays the results from queries to the data source. Through this form, browsers of your database can view the data they queried in a tabular format. Building a tabular form involves compiling a list of data columns that will appear in the tabular form from the pool of available columns in the schema. You can also create new columns that are computed from existing columns in the schema. The expressions for these computed columns may perform simple arithmetic, manipulate character strings, call ODBC scalar functions, or call native database functions. You then need to set the properties for each column appearing in the form.

In our Northwind example, the Order_Summary schema’s tabular form will list the various orders Northwind customers have placed with the company. It is not necessary at this level to show line-item details. You will accomplish this later when you build the Order_Detail schema. For now, you are only concerned with showing general order information by customer and order ID.

To display order information

1. Click the Tabular tab.

2. From the Data columns in selected tables list, double-click Customers to display the data columns that comprise the table, and then select Company Name.

3. Click Add to move the column to the Tabular data columns list on the right.

4. From the Data columns in selected tables list, double-click Orders to display the data columns that comprise the table, and then select OrderID.

5. Click Add to move the column to the Tabular data columns list on the right.

6. Repeat steps 4 and 5 for the Order Date, Required Date, and Shipped Date columns in the Orders table.

Setting Column Properties

Microsoft dbWeb allows you to set properties on the columns that will appear in the Tabular form. These properties affect the way the form appears in a Web browser. In this example, you will change settings that govern the way dates appear on the form, the width of each table column, the label of each column, and whether values listed under a particular column behave as Automatic Links.

To set column properties for Customers.Company Name

1. In the Tabular data columns list, select Customers.Company Name and click Properties to display the properties sheet.

Figure 14. Tabular form properties sheet

Tip: You can also access the properties sheet by selecting the data column whose properties you wish to change. While keeping the mouse pointer on the selected data column, hold down your right mouse button to display a shortcut menu. From this menu, click Properties. Double-clicking on the data column name produces the same effect.

2. From the Properties drop-down box at the top of the properties sheet, select All Properties.

3. Make sure Display when is set to Always.

4. The Display when property is unique to the Tabular form properties sheet. If you selected a column to appear in the Tabular form but did not include it in the QBE form, it will always display in the Tabular form. If the column appears in both the QBE and Tabular forms, the dbWeb Administrator presents you with several settings for this property. You may or may not have the column display in the Tabular form by default. Either way, browsers of your data can override your default setting by selecting or clearing the check box next to the column name in the QBE form. Setting this property to Always disables the check box and displays the column in the Tabular form.

5. To change the width of the column, select the box on the right of the Column width box, type "25", and press ENTER.

6. To change the header title, select the box on the right of the Column label box, type "Company", and press ENTER.

7. To force Microsoft dbWeb to use Company Name as the primary sort key on the query results, select the box on the right of Sort Priority, type "1", and press ENTER.

Now that you have begun to familiarize yourself with changing column properties, you can proceed to set the properties for the other columns in the Order_Summary Tabular form. Note that you do not have to close and reopen the properties sheet after you have finished editing the properties for one column and wish to set those for another column within the same form. You only need to move the properties sheet aside to expose the Tabular data columns listbox of the Tabular tab panel so that you can click and select the data column whose properties you wish to set. The properties sheet remains visible at all times and will immediately display the properties for the data column you have just selected.

To set column properties for Orders.Order ID

1. Set Column width to 7.

2. Set Column label to Order #.

3. Set Sort Priority to 2, forcing Microsoft dbWeb to use Order ID as the secondary sort key on the query results.

To set column properties for Orders.Order Date

1. Set Column width to 10.

2. Set Format to 9/30/95 from the drop-down menu.

Setting Format Properties

The Format property controls the way numerical, date, and string values are displayed in the Tabular form or Freeform. The dbWeb Administrator presents you with a list of various formats in a drop-down menu. While this simplifies formatting of column data, you can still specify your own format string by typing it directly in the Format property setting.

Format codes for data columns of numeric (integer, small integer, money, float, double, and so on) data types use the pound sign (#) and zero (0) as a place identifier. The semi-colon (;) separates the different formats for positive, negative, and null values. Any other character is considered a literal and will be displayed as is. Examples of numeric format code strings are listed below.

Format

Description

#

Display an integer. If the value is a float, the value is rounded off to the closest whole number. This formatting applies regardless of whether the value is positive, negative, or null.

$#,##0.00;($#,##.00);None

Format as a monetary value. If the value is negative, enclose it in parentheses.

#,###;#,###;#,###

Add comma separator to thousands place.

To set column properties for Orders.Required Date

1. Set Column label to Needed by.

2. Set Format to 9/30/95 from the drop-down menu.

To set column properties for Orders.Shipped Date

1. Set Column label to Shipped on.

2. Set Format to 9/30/95 from the drop-down menu.

Displaying Computed Values

Microsoft dbWeb does not limit you to the columns in the schema’s member tables. You can create computed columns derived from existing columns or from expressions that evaluate some value. These computed columns behave just like regular columns. You can set their properties, and they can also participate in queries.

In our Northwind example, you need to include a computed column in the Tabular form for the Order_Summary schema. This computed column will serve as a Automatic Link to the Order_Details schema which you will create later.

To create a computed column

1. From the Tabular tab in the Schema window, click Computed Column to open the Computed column data dialog box.

Figure 15. Computed Column Expression Builder dialog box

2. On the right side, in the Computed Column area, type "‘detail’". Make sure you enclose the word in single quotes.

3. Click OK.

4. Set Column width to 8 and Column label to Expand.

The Schema window should now resemble the following diagram.

Figure 16. Schema window: Tabular tab panel

Defining an Expanded View

You can expand on the information contained in a row of the Tabular form by using the Freeform. Because the Freeform uses the entire page to vertically display a single row of data, you can include as much information in the query result with little worry of exceeding the page width; hence the many-to-one row relationship between the Tabular form and the Freeform.

When building a schema that uses the standard Tabular forms and Freeforms, you must bear in mind that the number of rows in your query result determines which form Microsoft dbWeb uses to display the data. If your query yields two or more rows, Microsoft dbWeb automatically picks the tabular form to display these rows. Otherwise, Microsoft dbWeb will display the single row in the freeform.

In our Northwind example, you intend to use the Order_Summary Freeform to display additional information about the order. These details include the customer name and address as well as details about the order shipment.

To build a Freeform

1. In the Schema window, click the Freeform tab.

2. To add all the columns in the Customers and Orders table, click Add All.

Figure 17. Schema window: Freeform tab panel

3. Set the Column label and Format properties of the Orders.Order Date, Orders.Required Date and Orders.Shipped Date data columns to the same values as their tabular counterparts.

Important: When switching between the Freeform and Tabular tabs in the Schema dialog box, make sure you change only the properties that correspond to similar databases.

4. Set the Format property of the Freight column to $#,##0.00;($#,##0.00)None.

Linking Information

Now that you have successfully created the basic forms for this schema, you must now create the Automatic Links that link these forms together. Automatic Links allow hypertext-style navigation within a data source. They are hypertext column data that can be linked to resources on the World Wide Web. These resources are not limited to other Web pages; they can also include other dbWeb schemas. By incorporating Automatic Links into your schema, you allow browsers of your data to find more information simply by clicking on the hyperlinks. You can create Automatic Links in either the Tabular form or Freeform, using the properties sheet to create an Automatic Link for a specific data column in either form.

For the Order_Summary example, you will first create Drilldown Automatic Links that narrow down your query from a result set of several rows to one with a single row. This establishes a link between the tabular form and the freeform you just created. You would like your users to view order details from the tabular list of orders just by clicking on an Automatic Link.

To create an Automatic Link

1. From the Freeform tab in the Freeform data columns, select Customers.Company Name and click Properties.

2. Select the Automatic Link URL property and click ... to display the Automatic Link dialog box.

Figure 18. Automatic Link dialog box: Automatic Link type tab panel

3. In the Automatic Link type tab, select Drill down.

4. Click the Automatic Link Information tab.

Figure 19. Automatic Link dialog box: Automatic Link Information tab panel

5. Click New Criteria to open the Automatic Link Drilldown criteria builder.

6. Select Company Name from the Customers table and select the equal sign (=) as the comparison operator from the combo box, then click OK.

7. Click OK again to close the Automatic Link dialog box.

8. Repeat steps 1-5 to create another Drilldown Automatic Link for the Order ID column from the Orders table, using Order ID = as the Drilldown Automatic Link criteria.

9. Click OK, and then close the Properties dialog box.

10. Click Apply in the Schema dialog box.

# Creating the Order Detail Schema

Now you must create another schema that will display the line items of each customer order. This schema is very similar in structure to Order_Summary, except that it references the Order Details and Products tables of the Northwind corporate database. After creating the Order_Detail schema, you will use a Schema-to-Schema Automatic Link to establish a link between the Order_Summary and Order_Detail schemas.

To create the Order_Detail schema

1. Follow the same steps as the Order_Summary schema#ordsum in the beginning of Tutorial B, replacing Order_Summary with Order_Detail.

2. Use the Order Details and Products tables instead of Customers and Orders.

3. Join the Order Details.Product ID and the Products.Products ID tables.

4. In the QBE form, include the English Name data column from the Products table and the Unit Price and Quantity data columns from the Order Details table.

5. Create a computed column in the QBE form based on the following expression shown below:

[Order Details].[Unit Price]*[Order Details].Quantity

Remember to set the data type for the computed column to Double.

Note: Microsoft Access uses the bracket characters ([ and ]) as starting and ending delimiters. If you use another ODBC data source, you may have to use delimiter characters appropriate to that system.

 

Tip: You can create the expression in step five in one of two ways. One way is to type it directly in the expression text box. An easier way is to double-click the data columns from the outline control on the left. This action automatically types the name of the data columns in the text box. All you need to add at this point is the multiplication symbol (*) between the two data columns. This way, you reduce the potential for errors due to spelling mistakes.

6. Add the following data columns to the Tabular form in the following order: Order Details.Order ID, Order Details.Product ID, Products.English Name, Order Details.Unit Price, and Order Details.Quantity.

7. Create a computed column for the Tabular form and set the data type to Float: [Order Details].Discount * 100

Note: Microsoft Access uses the bracket characters ([ and ]) as starting and ending delimiters. If you use another ODBC data source, you may have to use delimiter characters appropriate to that system.

8. Set the properties for all the data columns in the Tabular form as follows:

Column

Property

Setting

Order ID

Column Width

6

Product ID

Column Width

4

English Name

Display When

Always
 

Column Width

30

Unit Price

Display When

Always
 

Column Width

10
 

Format

$#,##0.00;($#,##0.00);None

Quantity

Display When

Always
 

Column Width

8

[Order Details].Discount * 100

Column Width

8
 

Column Label

Discount
 

Format

0%

9. Under the Freeform tab, add all columns for both Order Details and Products tables. Assign properties for the added columns in the Freeform as follows:

Column

Property

Setting

Order Details.Unit Price

Format

$#,##0.00;($#,##0.00);None

Order Details.Discount

Format

0%

Products.Unit Price

Format

$#,##0.00;($#,##0.00);None

Order Details.Order ID

Automatic Link URL (Drilldown type)

Order Details.Order ID =

Order Details.Product ID

Automatic Link URL (Drilldown type)

Order Details.Product ID =

10. Click Apply.

# Linking Schemas

Now that you’ve created the Order_Detail schema, you are ready to link it with Order_Summary by using a schema-to-schema Automatic Link. Like all Automatic Links, a schema-to-schema Automatic Link is based on a data column. In the Order_Summary schema, you created a computed column in the Tabular form called "detail." Unlike the other computed columns that you have created throughout the example, detail is not derived from an existing data column but exists as a literal constant. The following steps describe how you can use this unique computed column to link the two schemas.

To link schemas

1. Open the Schema window for Order_Summary and click Tabular.

2. View the properties for the detail computed column.

3. Select the value cell for the Automatic Link URL property and click ... to display the Automatic Link dialog box.

4. In the Automatic Link type tab, select Schema link.

5. Click the Automatic Link Information tab.

Figure 20. Automatic Link dialog box; Order_Summary schema Automatic Link

6. In the Specify schema to jump to area, click ... and select Order_Detail, and click OK.

7. In the Specify criteria to use for schema link area, click New Criteria. Select Order_Summary.Orders.Order ID in the From current schema dialog box. Select Order_Detail.Order Details.Order ID in the Links to schema dialog box. Select the equal sign (=) as the comparison operator from the combo box, and then click OK.

8. Click the Automatic Link Label tab and type Detail. Because the computed column, detail, is not based on an existing table column, it does not contain data. The label you provide is a placeholder for the detail column in the Tabular form.

9. Click OK.

10. Click OK to close all open schema windows and save your changes. The only open window within the dbWeb Administrator window should be the Data Sources list.

Congratulations! You have just completed an ordering system. Thanks to you, users around the world can now access customer order information from your database.

# Testing Schemas

Now that you have built two complete schemas from scratch and linked them, you are ready to test them using your favorite Web browser.

To test your schemas

1. Type in the URL to display the QBE form.

Figure 21. Order_Summary: QBE Form

2. Type USA in the Ship Country field and click Submit Query to show the Tabular form with the results of the query

The first two columns, Company and Order #, have Drilldown Automatic Links. The Expand column has a schema-to-schema automatic link.

Figure 22. Order_Summary: Tabular Form

3. In the first row, click Order # 10528.

Figure 23. Order_Summary; Tabular Form after drilldown

4. Return to the previous Web page by clicking on the Back button (or equivalent control) in your Web browser, and then click the Detail link in the first row, where Order # is shown as 10528.

The resulting Tabular form that you see belongs to the Order_Detail schema and lists the items belonging to the customer order you just clicked. If the order you clicked contained only one item, you would have seen the Freeform instead of the Tabular form. You can click any of the Product ID Drilldown Automatic Links in this form to see how that Freeform looks.

Figure 24. Order_Detail; Tabular Form from schema Automatic Link