1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<!--
/* Copyright 2002-2004 Apache Software Foundation
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
-->
<HTML>
<HEAD>
<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=utf-8">
<TITLE></TITLE>
<META NAME="GENERATOR" CONTENT="OpenOffice.org 1.1.1 (Linux)">
<META NAME="CREATED" CONTENT="20041121;23172400">
<META NAME="CHANGED" CONTENT="20041205;321800">
<STYLE>
<!--
@page { size: 8.5in 11in; margin-left: 1.25in; margin-right: 1.25in; margin-top: 1in; margin-bottom: 1in }
H1 { margin-bottom: 0.08in }
H1.western { font-family: "Bitstream Vera Sans", sans-serif; font-size: 16pt }
H1.cjk { font-family: "HG Mincho Light J", "MS Mincho", "HG Mincho J", "HG Mincho L", "HG Mincho", "Mincho", "MS PMincho", "HG Mincho Light J", "MS Gothic", "HG Gothic J", "HG Gothic B", "HG Gothic", "Gothic", "MS PGothic", "Andale Sans UI", "Arial Unicode MS", "Lucida Sans Unicode", "Tahoma"; font-size: 16pt }
H1.ctl { font-family: "Tahoma", "Lucidasans", "Lucida Sans", "Arial Unicode MS"; font-size: 16pt }
P { margin-bottom: 0.08in }
H2 { margin-bottom: 0.08in }
H2.western { font-family: "Bitstream Vera Sans", sans-serif; font-size: 14pt; font-style: italic }
H2.cjk { font-family: "HG Mincho Light J", "MS Mincho", "HG Mincho J", "HG Mincho L", "HG Mincho", "Mincho", "MS PMincho", "HG Mincho Light J", "MS Gothic", "HG Gothic J", "HG Gothic B", "HG Gothic", "Gothic", "MS PGothic", "Andale Sans UI", "Arial Unicode MS", "Lucida Sans Unicode", "Tahoma"; font-size: 14pt; font-style: italic }
H2.ctl { font-size: 14pt; font-style: italic }
-->
</STYLE>
</HEAD>
<BODY LANG="en-US" DIR="LTR">
<P ALIGN=CENTER STYLE="margin-bottom: 0in"><FONT FACE="Times, serif"><FONT SIZE=4 STYLE="font-size: 16pt"><B>Creating
a Jakarta Commons SQL datamodel using XmlBeans and Velocity</B></FONT></FONT></P>
<P ALIGN=CENTER STYLE="margin-bottom: 0in"><FONT FACE="Times, serif"><FONT SIZE=3><I><B>Philip
Mark Donaghy</B></I></FONT></FONT></P>
<H1 CLASS="western">1 <FONT FACE="Times, serif">About Vxsdb</FONT></H1>
<P><FONT SIZE=3><FONT FACE="Times, serif">Vxsdb is a sample XmlBeans
application which derives a Jakarta Commons SQL data model from an
xml schema. It was conceived from things I have learned from using
XmlBeans and talking to people at the ASF conference.</FONT></FONT></P>
<P><FONT FACE="Times, serif">Vxsdb uses Jakarta Velocity as its
templating engine. At runtime a SchemaTypeSystem XmlBeans object is
created from compiling an xml schema. This object is placed in a
Velocity context. A template processes the objects global elements,
global types and generates a Jakarta Commons SQL xml datamodel
descriptor of tables and columns.</FONT></P>
<H2 CLASS="western">1.1 Requirements</H2>
<P><FONT FACE="Times, serif">Java 1.5</FONT></P>
<H2 CLASS="western">1.2 Getting Started</H2>
<P><FONT FACE="Times, serif">The basic utilization compiles the
sample and produces a database schema for a given xml schema.</FONT></P>
<P><FONT FACE="Times, serif">Execute the command “ant”</FONT></P>
<P><FONT FACE="Times, serif">This compiles the sample.</FONT></P>
<P><FONT FACE="Times, serif">Execute the command “ant -f run.xml”</FONT></P>
<P><FONT FACE="Times, serif">This creates the Jakarta Commons SQL
datamodel.xml and creates the create.sql ddl.</FONT></P>
<H2 CLASS="western"><FONT FACE="Bitstream Vera Sans">1.3
Configuration</FONT></H2>
<P><FONT FACE="Times, serif">Vxsdb can be configured by modifying the
command line parameters to specify another template, output or xml
schema.</FONT></P>
<P><FONT FACE="Times, serif">ant -f run.xml -Dtemplate=datamodel.vm
-Doutput=database.xml -Dschema=easypo.xsd</FONT></P>
<H2 CLASS="western">1.4 Generating database schema</H2>
<P><FONT FACE="Times, serif">Included in the sample run.xml is an Ant
target which generates the ddl used to create the database tables.
The default database is PostgreSQL.</FONT></P>
<P><FONT FACE="Times, serif">ant -f run.xml</FONT></P>
<P><FONT FACE="Times, serif">To change the target database use,</FONT></P>
<P><FONT FACE="Times, serif">ant -f run.xml -DtargetDatabase=oracle</FONT></P>
<H2 CLASS="western">1.5 Determining Tables, Columns, and Table
relationships</H2>
<P><FONT FACE="Times, serif">Tables are created from all global
elements and most global types.</FONT></P>
<P><FONT FACE="Times, serif">For each schema property the sample
application determines if it is a column, an exported key (foreign
key to another table), or an imported foreign key (another entity
who's table contains a foreign back to this entity).</FONT></P>
<P><FONT FACE="Times, serif">If a schema property (attribute or
element) is a simple type without restriction a column is created and
one of the following Commons SQL values is assigned to the property.
DATE, DECIMAL, FLOAT, INTEGER, TIME, TIMESTAMP, or VARCHAR.</FONT></P>
<P><FONT FACE="Times, serif">If a schema property is a simple type
with a restriction then a column is created and one of the above
values is assigned in addition to a size attribute.</FONT></P>
<P><FONT FACE="Times, serif">If a schema property is a complex type
with no maxOccurs or minOccurs (meaning that it is required) then a
foreign key column is entered into the database table descriptor and
the required attribute is set to true.</FONT></P>
<P><FONT FACE="Times, serif">If a schema property is a complex type
with maxOccurs equal to one then a foreign key column is entered into
the database table.</FONT></P>
<P><FONT FACE="Times, serif">If a schema property is a complex type
with maxOccurs greater than one or unbounded then a imported key
(foreign key from this type's table) is required. This presents a
problem. The Velocity template may have already encountered this
complex type and created its table descriptor without even knowing
about this relationship. This is known as the Imported Key Question.</FONT></P>
<H2 CLASS="western">1.6 Solving the Imported Key Question</H2>
<P><FONT FACE="Times, serif">Iterate the SchemaTypeSystem in the Java
class to determine imported keys.</FONT></P>
<P><FONT FACE="Times, serif">Cliff told me that ms has a solution to
this which may be included in the xml schema.</FONT></P>
<H2 CLASS="western">1.7 Additional ideas</H2>
<P><FONT FACE="Times, serif"><FONT SIZE=3>Combining XmlBeans and
Velocity templates can result in other interesting xml schema related
technologies.</FONT></FONT></P>
<P><FONT FACE="Times, serif">Using a similar approach to this sample
XmlBeans and Velocity can harness the xml schema and generate
documentation, a relational database model, or even data access
objects.</FONT></P>
<H2 CLASS="western">1.8 Sample datamodel.xml</H2>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><?xml
version="1.0" encoding="UTF-8"?></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><database
name="sample"></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
Global Elements --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><table
name="PURCHASE_ORDER"></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="PK" type="INTEGER" required="true"
primaryKey="true"/></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
T=customer@http://openuri.org/easypo --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="FK_CUSTOMER" required="true" type="INTEGER"
/></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><foreign-key
foreignTable="CUSTOMER"></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><reference
local="FK_CUSTOMER" foreign="PK"/></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2></foreign-key></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
T=dateTime@http://www.w3.org/2001/XMLSchema --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="DATE" type="TIMESTAMP" /></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
Foreign key imported from table named LINE_ITEM --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
T=shipper@http://openuri.org/easypo --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="FK_SHIPPER" type="INTEGER" /></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><foreign-key
foreignTable="SHIPPER"></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><reference
local="FK_SHIPPER" foreign="PK"/></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2></foreign-key></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2></table></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
Global Types --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><table
name="CUSTOMER"></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="PK" type="INTEGER" required="true"
primaryKey="true"/></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
T=string@http://www.w3.org/2001/XMLSchema --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="NAME" type="VARCHAR" /></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
T=string@http://www.w3.org/2001/XMLSchema --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="ADDRESS" type="VARCHAR" /></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
T=int@http://www.w3.org/2001/XMLSchema --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="AGE" type="TINYINT" /></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
T=int@http://www.w3.org/2001/XMLSchema --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="MOO" type="TINYINT" /></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
T=int@http://www.w3.org/2001/XMLSchema --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="POO" type="TINYINT" /></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2></table></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><table
name="LINE_ITEM"></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="PK" type="INTEGER" required="true"
primaryKey="true"/></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
T=string@http://www.w3.org/2001/XMLSchema --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="DESCRIPTION" type="VARCHAR" /></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
T=decimal@http://www.w3.org/2001/XMLSchema --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="PER_UNIT_OUNCES" type="DECIMAL" /></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
T=decimal@http://www.w3.org/2001/XMLSchema --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="PRICE" type="DECIMAL" /></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
T=integer@http://www.w3.org/2001/XMLSchema --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="QUANTITY" type="INTEGER" /></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
Exported key --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="FK_PURCHASE_ORDER" required="true"
type="INTEGER" /></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><foreign-key
foreignTable="PURCHASE_ORDER"></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><reference
local="FK_PURCHASE_ORDER" foreign="PK"/></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2></foreign-key></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2></table></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><table
name="SHIPPER"></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="PK" type="INTEGER" required="true"
primaryKey="true"/></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
T=string@http://www.w3.org/2001/XMLSchema --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="NAME" type="VARCHAR" /></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><!--
T=decimal@http://www.w3.org/2001/XMLSchema --></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2><column
name="PER_OUNCE_RATE" type="DECIMAL" /></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2></table></FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2></database></FONT></FONT></P>
<H2 CLASS="western">1.9 Sample create.sql</H2>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>drop
table SHIPPER;</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>drop
table LINE_ITEM;</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>drop
table CUSTOMER;</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>drop
table PURCHASE_ORDER;</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>--
-----------------------------------------------------------------------</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>--
PURCHASE_ORDER</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>--
-----------------------------------------------------------------------</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>create
table PURCHASE_ORDER</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>(</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>PK
INTEGER NOT NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>FK_CUSTOMER
INTEGER NOT NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>DATE
TIMESTAMP NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>FK_SHIPPER
INTEGER NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>PRIMARY
KEY (PK)</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>);</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>ALTER
TABLE PURCHASE_ORDER</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>ADD
CONSTRAINT PURCHASE_ORDER_FK_1 FOREIGN KEY (FK_CUSTOMER)</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>REFERENCES
CUSTOMER (PK)</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>;</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>ALTER
TABLE PURCHASE_ORDER</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>ADD
CONSTRAINT PURCHASE_ORDER_FK_2 FOREIGN KEY (FK_SHIPPER)</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>REFERENCES
SHIPPER (PK)</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>;</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>--
-----------------------------------------------------------------------</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>--
CUSTOMER</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>--
-----------------------------------------------------------------------</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>create
table CUSTOMER</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>(</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>PK
INTEGER NOT NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>NAME
VARCHAR NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>ADDRESS
VARCHAR NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>AGE
TINYINT NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>MOO
TINYINT NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>POO
TINYINT NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>PRIMARY
KEY (PK)</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>);</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>--
-----------------------------------------------------------------------</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>--
LINE_ITEM</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>--
-----------------------------------------------------------------------</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>create
table LINE_ITEM</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>(</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>PK
INTEGER NOT NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>DESCRIPTION
VARCHAR NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>PER_UNIT_OUNCES
DECIMAL NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>PRICE
DECIMAL NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>QUANTITY
INTEGER NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>FK_PURCHASE_ORDER
INTEGER NOT NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>PRIMARY
KEY (PK)</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>);</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>ALTER
TABLE LINE_ITEM</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>ADD
CONSTRAINT LINE_ITEM_FK_1 FOREIGN KEY (FK_PURCHASE_ORDER)</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>REFERENCES
PURCHASE_ORDER (PK)</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>;</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>--
-----------------------------------------------------------------------</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>--
SHIPPER</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>--
-----------------------------------------------------------------------</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>create
table SHIPPER</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>(</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>PK
INTEGER NOT NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>NAME
VARCHAR NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>PER_OUNCE_RATE
DECIMAL NULL ,</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>PRIMARY
KEY (PK)</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><FONT FACE="Monospace"><FONT SIZE=2>);</FONT></FONT></P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P><BR><BR>
</P>
</BODY>
</HTML>
|
