Database Schema

1 Introduction to Query

Database Schema

A database schema is used to define the database. There are three parts to a schema. Part one gives the name of the database and passwords used for access to the database. Part two lists each data item, specifying its size and type. (The type of a data item refers to the type of data that will be stored in it, such as integer data. For a full description, refer to the appendix titled "DBML Syntax" in the Eloquence DBMS Manual.) Part three describes the individual data sets and the data items they contain.

Now let's look at the schema for the Sales Analysis database.

1  BEGIN DATA BASE     SAD;  <<CUSTOMER SALES ANALYSIS DATABASE>>
2
3  PASSWORDS:
4               10       SALESMAN;
5               15       MANAGER;
6                3       SECRTARY;  <<WILL HAVE READ ACCESS ONLY>>
7
8  ITEMS:
9               ADDRESS,     2X30;  <<2 LINES OF ADDRESS ALLOWED>>
10              CITY,         X16;
11              COUNTRY,      X12;
12              DATE,         I;    <<PATH FOR ORDER-DATE, SHIP-DATE>>
13              NAME,         X30;
14              OPTION-DESC,  X10;
15              OPTION-PRICE, L;
16              OPTION-TYPE,  I;
17              ORDER-DATE,   I;    <<MUST BE YYMM>>
18              ORDER-NO,     X10;
19              PRICE,        L;
20              PRODUCT-NO,   I;
21              PROD-DESC,    X30;
22              REGION,       X6;
23              REGION-DESC,  X30;
24              REGION-TYPE,  I;
25              SALESPERSON,  X4;
26              SHIP-DATE,    I;    <<MUST BE YYMM>>
27              STATE,        X6;
28              ZIP-CODE,     X8;
29
30 SETS:
31
32    NAME:     DATE,AUTOMATIC(3/10,15);
33    ENTRY:    DATE(2);
34    CAPACITY: 50;   <<CAPACITY IS OPTIONAL>>
35
36    NAME:     ORDER,A(3/10,15);
37    ENTRY:    ORDER-NO(2);
38    CAPACITY: 100;
39
40    NAME:     PRODUCT,MANUAL(3,10/15);
41    ENTRY:    PRODUCT-NO(1),
42              PROD-DESC;
43    CAPACITY: 10;
44
45    NAME:     LOCATION,M(3,10/15);
46    ENTRY:    REGION(1),
47              REGION-DESC,
48              REGION-TYPE;
49    CAPACITY: 20;
50
51    NAME:     OPTION,D(3/10,15);
52    ENTRY:    ORDER-NO(ORDER),
53              OPTION-DESC,
54              OPTION-PRICE,
55              OPTION-TYPE;
56    CAPACITY: 300;
57
58    NAME:     CUSTOMER,DETAIL(3/10,15);
59    ENTRY:    ORDER-NO(ORDER),
60              NAME,
61              ADDRESS,
62              CITY,
63              STATE,
64              COUNTRY,
65              ZIP-CODE,
66              ORDER-DATE(DATE),
67              SHIP-DATE(DATE),
68              REGION(LOCATION),
69              PRODUCT-NO(PRODUCT),
70              PRICE,
71              SALESPERSON;
72    CAPACITY: 100;
73
74          END.

Line 1: The name of the database is given--SAD. When you use Query, you must give the database name.
Lines 3, 4, 5, and 6: The passwords are given. The numbers (10, 15, 3) are used later in the schema. You use the words SALESMAN, MANAGER, SECRTARY when you first begin using Query (as explained in page 23 ).
Lines 8 through 28: The name, size and type of each data item is given. 2X30 specifies a two item array, of which each item is 30 characters long. X16 specifies a data item 16 characters long. I specifies that the data item contains an integer. L specifies that the data item contains a long precision number. If a data item is a number, then you can only enter a number for its value; otherwise an error will occur. If data item is a string (specified by X) then you may enter up to the specified number of characters. (Entering a value is described in page 57 .)
Lines 32, 33, and 34: The automatic data set DATE is described. Line 32 gives the name (DATE), the type (AUTOMATIC), the read/write passwords (3/10, 15). The passwords (3/10, 15) mean that anyone who uses the password SECRTARY (3) can read and list the data in this data set, but cannot write new values, change values or delete values. Anyone who uses the password SALESMAN or MANAGER can read or write (add, delete, modify) the values in this data set. Line 33 gives the name of data item (DATE) and the number of data items in detail data sets to which are pointed (2). Line 34 gives the maximum number of data entries that are possible in this data set.
Lines 36 through 49: The other master data sets are described.
Lines 51 through 56: The detail data set OPTION is described.
Line 52: This specifies the name of the first data item (ORDER_NO) which is a key item pointed to from the master data set ORDER.
Lines 58 through 72: The detail data set CUSTOMER is described.
Line 74: This specifies the end of a schema.

Within the schema, comments are enclosed in signs. These comments aid you in understanding the schema. They also provide additional information about data item values. For example, line 12 has the following comment:

<<PATH FOR ORDER-DATE, SHIP-DATE>>

This tells the reader of the schema that this data item is used as the link between ORDER-DATE data item and SHIP-DATE data item.

Lines 17 and 26 both have the following comment:

<<MUST BE YYMM>>

This tells the reader that the values entered for the data must be formatted as YYMM. (YY means a two digit representation of the year. MM means a two digit representation of the month. For example, October 1986 represented in YYMM format would be 8610.) This is not required by the schema, but because reports are written expecting this format, you should use it.

In the schema, some data item names contain a dash (for example, ZIP-CODE). When you use this data item in Query, the dash must be changed to an underscore. Thus, ZIP-CODE would be written as ZIP_CODE.

Eloquence Query Manual - 19 DEC 2002