2. Sample Schema#
This appendix provides information about the schemas and data used in the examples in the Altibase Manuals.
Information about the Sample Schema#
Script Files#
A schema creation file is provided at $ALTIBASE_HOME/sample/APRE/schema/schema.sql.
Executing this file creates the tables referenced in the manuals and populates them with sample data.
Therefore, if you would like to work with the examples described in the manuals, first execute the schema creation file, after which it will be possible to follow the provided examples.
The Sample Schema#
Purpose: Managing Customers and Orders
Tables: employees, departments, customers, orders, goods
employees Table#
Primary Key: Employee Number (eno)
| Column Name | Data Type | Description | Other |
|---|---|---|---|
| eno | INTEGER | Employee Number | PRIMARY KEY |
| e_lastname | CHAR(20) | Employee Last Name | NOT NULL |
| e_firstname | CHAR(20) | Employee First Name | NOT NULL |
| emp_job | VARCHAR(15) | Title | NULL allowed |
| emp_tel | CHAR(15) | Telephone Number | NULL allowed |
| dno | SMALLINT | Department Number | NULL allowed, INDEX ASC |
| salary | NUMBER(10,2) | Monthly Salary | NULL allowed, DEFAULT 0 |
| sex | CHAR(1) | Sex (Gender) | NULL allowed |
| birth | CHAR(6) | Birthday | NULL allowed |
| join_date | DATE | Date of entry | NULL allowed |
| status | CHAR(1) | Status | NULL allowed, DEFAULT ‘H’ |
departments Table#
Primary Key: Department Number (dno)
| Column Name | Data Type | Description | Other |
|---|---|---|---|
| dno | SMALLINT | Department Number | PRIMARY KEY |
| dname | CHAR(30) | Department Name | NOT NULL |
| dep_location | CHAR(15) | Department Location | NULL allowed |
| mgr_no | INTEGER | Administrator Number | NULL allowed, INDEX ASC |
customers Table#
Primary Key: Resident Registration Number (cno)
| Column Name | Data Type | Description | Other |
|---|---|---|---|
| cno | CHAR(14) | Customer Number | PRIMARY KEY |
| c_lastname | CHAR(20) | Customer Last Name | NOT NULL |
| c_firstname | CHAR(20) | Customer First Name | NOT NULL |
| cus_job | VARCHAR(20) | Occupation | NULL allowed |
| cus_tel | NIBBLE(15) | Telephone Number | NOT NULL |
| sex | CHAR(1) | Sex (Gender) | NOT NULL |
| birth | CHAR(6) | Birthday | NULL allowed |
| postal_cd | VARCHAR(9) | Postal Code | NULL allowed |
| address | VARCHAR(60) | Address | NULL allowed |
orders Table#
Primary Keys: Order Number & Order Date (ono, order_date)
| Column Name | Data Type | Description | Other |
|---|---|---|---|
| ono | BIGINT | Order Number | PRIMARY KEY |
| order_date | DATE | Order Date | PRIMARY KEY |
| eno | INTEGER | Sales Clerk | NOT NULL, INDEX ASC |
| cno | BIGINT | Customer Number | NOT NULL, INDEX DESC |
| gno | CHAR(10) | Product No. | NOT NULL, INDEX ASC |
| qty | INTEGER | Order Quantity | NULL allowed, DEFAULT 1 |
| arrival_date | DATE | Expected Arrival Date | NULL allowed |
| processing | CHAR(1) | Order Status | NULL allowed, O: ORDER, R: PREPARE, D: DELIVERY, C: COMPLETE, DEFAULT 'O' |
goods Table#
Primary Key: Product No. (gno)
| Column Name | Data Type | Description | Other |
|---|---|---|---|
| gno | CHAR(10) | Product Number | PRIMARY KEY |
| gname | CHAR(20) | Product Name | NOT NULL, UNIQUE |
| goods_location | CHAR(9) | Storage Location | NULL allowed |
| stock | INTEGER | Stored Quantity | NULL allowed, DEFAULT 0 |
| price | NUMERIC(10,2) | Item Price | NULL allowed |
dual Table#
Record Size: 1
| Column Name | Data Type | Description | Other |
|---|---|---|---|
| DUMMY | CHAR(1) |
E-R Entity-Relationship (ER) Diagram and Sample Data#
E-R Diagram#
Sample Data#
Employees Table#
iSQL> select * from employees;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
----------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
----------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
2 Davenport Susan designer
0113654540 1500 F 721219 18-NOV-2009 H
3 Kobain Ken engineer
0162581369 1001 2000 M 650226 11-JAN-2010 H
4 Foster Aaron PL
0182563984 3001 1800 M 820730 H
5 Ghorbani Farhad PL
01145582310 3002 2500 M 20-DEC-2009 H
6 Momoi Ryu programmer
0197853222 1002 1700 M 790822 09-SEP-2010 H
7 Fleischer Gottlieb manager
0175221002 4002 500 M 840417 24-JAN-2004 H
8 Wang Xiong manager
0178829663 4001 M 810726 29-NOV-2009 H
9 Diaz Curtis planner
0165293668 4001 1200 M 660102 14-JUN-2010 H
10 Bae Elizabeth programmer
0167452000 1003 4000 F 710213 05-JAN-2010 H
11 Liu Zhen webmaster
0114553206 1003 2750 M 28-APR-2011 H
12 Hammond Sandra sales rep
0174562330 4002 1890 F 810211 14-DEC-2009 H
13 Jones Mitch PM
0187636550 1002 980 M 801102 H
14 Miura Yuu PM
0197664120 1003 2003 M H
15 Davenport Jason webmaster
0119556884 1003 1000 M 901212 H
16 Chen Wei-Wei manager
0195562100 1001 2300 F 780509 H
17 Fubuki Takahiro PM
0165293886 2001 1400 M 781026 07-MAY-2010 H
18 Huxley John planner
01755231044 4001 1900 M 30-OCT-2007 H
19 Marquez Alvar sales rep
0185698550 4002 1800 M 18-NOV-2010 H
20 Blake William sales rep
01154112366 4002 M 18-NOV-2006 H
20 rows selected.
departments Table#
iSQL> select * from departments;
DNO DNAME DEP_LOCATION MGR_NO
----------------------------------------------------------------------------
1001 RESEARCH DEVELOPMENT DEPT 1 New York 16
1002 RESEARCH DEVELOPMENT DEPT 2 Sydney 13
1003 SOLUTION DEVELOPMENT DEPT Osaka 14
2001 QUALITY ASSURANCE DEPT Seoul 17
3001 CUSTOMERS SUPPORT DEPT London 4
3002 PRESALES DEPT Peking 5
4001 MARKETING DEPT Brasilia 8
4002 BUSINESS DEPT Palo Alto 7
8 rows selected.
customers Table#
iSQL> select * from customers;
CNO C_LASTNAME C_FIRSTNAME
---------------------------------------------------------------------
CUS_JOB CUS_TEL SEX BIRTH POSTAL_CD
---------------------------------------------------------------------
ADDRESS
----------------------------------------------------------------
1 Sanchez Estevan
engineer 0514685282 M 720828 90021
2100 Exposition Boulevard Los Angeles USA
2 Martin Pierre
doctor 023242121 M 821215 V6T 1F2
4712 West 10th Avenue Vancouver BC Canada
3 Morris Gabriel
designer 023442542 M 811111 75010
D914 Puteaux Ile-de-France France
4 Park Soo-jung
engineer 022326393 F 840305 609-735
Geumjeong-Gu Busan South Korea
5 Stone James
webmaster 0233452141 M 821012 6060
142 Francis Street Western Australia AUS
6 Dureault Phil
WEBPD 025743215 M 810209 H1R-2W1
1000 Rue Rachel Est Montreal Canada
7 Lalani Yasmin
planner 023143366 F 821225 156772
176 Robinson Road Singapore
8 Kanazawa Tsubasa
PD 024721114 M 730801 141-0031
2-4-6 Nishi-Gotanda Shinagawa-ku Tokyo JP
9 Yuan Ai
designer 0512543734 F 690211 200020
10th Floor No. 334 Jiujiang Road Shanghai
10 Nguyen Anh Dung
0516232256 M 790815 70000
8A Ton Duc Thang Street District 1 HCMC Vietnam
11 Sato Naoki
manager 027664545 M 810101 455-8205
3-23 Oye-cho Minato-ku Nagoya Aichi Japan
12 Rodriguez Aida
banker 023343214 F 810905 76152
3484 Taylor Street Dallas TX USA
13 White Crystal
engineer 022320119 F 801230 WC2B 4BM
12th Floor Five Kemble Street London UK
14 Kim Cheol-soo
banker 024720112 M 660508 135-740
222-55 Samsung-dong Gangnam-gu Seoul Korea
15 Fedorov Fyodor
manager 0518064398 M 750625 50696
No 6 Leboh Ampang 50100 Kuala Lumpur Malaysia
16 Lefebvre Daniel
planner 027544147 M 761225 21004
Chaussee de Wavre 114a 1050 Brussels Belgium
17 Yoshida Daichi
023543541 M 811001 530-0100
2-7 3-Chome-Kita Tenjinbashi Kita-ku Osaka
18 Zhang Bao
engineer 024560207 F 840419 100008
2 Chaoyang Men Wai Street Chaoyang Beijing
19 Pahlavi Saeed
022371234 M 741231 20037
3300 L Street NW Washington DC USA
20 Dubois Alisee
webmaster 024560002 F 860405 1357
Chemin de Messidor 7-6 CH-1006 Lausanne Suisse
20 rows selected.
orders Table#
iSQL> select * from orders;
ONO ORDER_DATE ENO CNO
------------------------------------------------------------------------
GNO QTY ARRIVAL_DATE PROCESSING
------------------------------------------------------
11290007 29-NOV-2011 12 3
A111100002 70 02-DEC-2011 C
11290011 29-NOV-2011 12 17
E111100001 1000 05-DEC-2011 D
11290100 29-NOV-2011 19 11
E111100001 500 07-DEC-2011 D
12100277 10-DEC-2011 19 5
D111100008 2500 12-DEC-2011 C
12300001 01-DEC-2011 19 1
D111100004 1000 02-JAN-2012 P
12300002 29-DEC-2011 12 2
C111100001 300 02-JAN-2012 P
12300003 29-DEC-2011 20 14
E111100002 900 02-JAN-2012 P
12300004 30-DEC-2011 20 15
D111100002 1000 02-JAN-2012 P
12300005 30-DEC-2011 19 4
D111100008 4000 02-JAN-2012 P
12300006 30-DEC-2011 20 13
A111100002 20 02-JAN-2012 P
12300007 30-DEC-2011 12 7
D111100002 2500 02-JAN-2012 P
12300008 30-DEC-2011 20 11
D111100011 300 02-JAN-2012 P
12300009 30-DEC-2011 20 19
D111100003 500 02-JAN-2012 P
12300010 30-DEC-2011 19 16
D111100010 2000 02-JAN-2012 P
12300011 30-DEC-2011 20 15
C111100001 1000 02-JAN-2012 P
12300012 30-DEC-2011 12 3
E111100012 1300 02-JAN-2012 P
12300013 30-DEC-2011 20 6
C111100001 5000 02-JAN-2012 P
12300014 30-DEC-2011 12 12
F111100001 800 02-JAN-2012 P
12310001 31-DEC-2011 20 15
A111100002 50 09-DEC-2011 O
12310002 31-DEC-2011 12 10
D111100008 10000 03-JAN-2012 O
12310003 31-DEC-2011 20 18
E111100009 1500 03-JAN-2012 O
12310004 31-DEC-2011 19 5
E111100010 5000 08-JAN-2012 O
12310005 31-DEC-2011 20 14
E111100007 940 03-JAN-2012 O
12310006 31-DEC-2011 20 2
D111100004 500 03-JAN-2012 O
12310007 31-DEC-2011 12 19
E111100012 1400 03-JAN-2012 O
12310008 31-DEC-2011 19 1
D111100003 100 03-JAN-2012 O
12310009 31-DEC-2011 12 5
E111100013 500 03-JAN-2012 O
12310010 31-DEC-2011 20 6
D111100010 1500 03-JAN-2012 O
12310011 31-DEC-2011 19 15
E111100012 10000 03-JAN-2012 O
12310012 31-DEC-2011 19 1
C111100001 250 03-JAN-2012 O
30 rows selected.
goods Table#
iSQL> SELECT * FROM goods;
GOODS.GNO GOODS.GNAME GOODS.GOODS_LOCATION GOODS.STOCK
-------------------------------------------------------------------------
GOODS.PRICE
--------------
A111100001 IM-300 AC0001 1000
78000
A111100002 IM-310 DD0001 100
98000
B111100001 NT-H5000 AC0002 780
35800
C111100001 IT-U950 FA0001 35000
7820.55
C111100002 IT-U200 AC0003 1000
9455.21
D111100001 TM-H5000 AC0004 7800
12000
D111100002 TM-T88 BF0001 10000
72000
D111100003 TM-L60 BF0002 650
45100
D111100004 TM-U950 DD0002 8000
96200
D111100005 TM-U925 AC0005 9800
23000
D111100006 TM-U375 EB0001 1200
57400
D111100007 TM-U325 EB0002 20000
84500
D111100008 TM-U200 AC0006 61000
10000
D111100009 TM-U300 DD0003 9000
50000
D111100010 TM-U590 DD0004 7900
36800
D111100011 TM-U295 FA0002 1000
45600
E111100001 M-T245 AC0007 900
2290.54
E111100002 M-150 FD0001 4300
7527.35
E111100003 M-180 BF0003 1000
2300.55
E111100004 M-190G CE0001 88000
5638.76
E111100005 M-U310 CE0002 11200
1450.5
E111100006 M-T153 FD0002 900
2338.62
E111100007 M-T102 BF0004 7890
966.99
E111100008 M-T500 EB0003 5000
1000.54
E111100009 M-T300 FA0003 7000
3099.88
E111100010 M-T260 AC0008 4000
9200.5
E111100011 M-780 AC0009 9800
9832.98
E111100012 M-U420 CE0003 43200
3566.78
E111100013 M-U290 FD0003 12000
1295.44
F111100001 AU-100 AC0010 10000
100000
30 rows selected.
dual Table#
iSQL> SELECT * FROM dual;
DUAL.X
------------
X
1 row selected.