Next: SQL Features Up: Relational Databases Previous: Example Queries: Part C Contents

Data Dictionary [163]

Data Dictionary
catalog: information about tables
``data about data''
system tables: like user tables
self-describing: information about DD
DD defines schema (structure):
(S) S# SNAME STATUS CITY
(P) P# PNAME COLOR WEIGHT CITY
(J) J# JNAME CITY
(SPJ) S# P# J# QTY
primary key is unique identifier

Data Dictionary: Keys [164]

candidate

candidate key: unique identifier for each record
simple candidate key of S: S#
composite candidate key of SPJ: S# P# J#
index: direct access

primary

primary key is a particular candidate key

alternate

alternate key: non-primary candidate key

foreign

foreign key points to primary key of another table
foreign key of SPJ: S# points to S
foreign key of SPJ: P# points to P
foreign key of SPJ: J# points to J

Data Dictionary: Example [165]

$\fbox{\bf TAB.DD.tbl:}$ $\fbox{COL.DD.tbl:}$ $\fbox{DOM.DD.tbl}$

TABNAME COLS ROWS TABNAME COLNAME KEY DOMNAME TYPE

TAB.DD 3 8 TAB.DD TABNAME 1 TABNAME CHAR:15

COL.DD 3 27 TAB.DD COLS 0 COLS INT:15

DOM.DD 2 18 TAB.DD ROWS 0 ROWS INT:15

S 4 5 COL.DD TABNAME 1 COLNAME CHAR:15

P 5 6 COL.DD COLNAME 2 KEY INT:15

J 3 7 COL.DD KEY 0 DOMNAME CHAR:15

SP 3 12 DOM.DD COLNAME 1 TYPE CHAR:15

SPJ 4 24 DOM.DD DATATYPE 0 S# CHAR:15

S S# 1 SNAME CHAR:15

S SNAME 0 STATUS INT:15

S STATUS 0 CITY CHAR:15

S CITY 0 P# CHAR:15

P P# 1 PNAME CHAR:15

P PNAME 0 COLOR CHAR:15

P COLOR 0 WEIGHT INT:15

$\fbox{\bf J.tbl:}$ P WEIGHT 0 QTY INT:15

J# JNAME CITY P CITY 0 J# CHAR:15

J1 Sorter Paris J J# 1 JNAME CHAR:15

J2 Display Rome J JNAME 0

J3 OCR Athens J CITY 0

J4 Console Athens SP S# 1

J5 Raid London SP P# 2

J6 EDS Oslo SP QTY 0

J7 Tape London SPJ S# 1

SPJ P# 2

SPJ J# 3

SPJ QTY 0

Data Dictionary: TABLES [166]

TABLES

TAB.DD.tbl: information about all tables
fields: TABNAME, COLS, ROWS
OWNERSHIP, PERMISSIONS
key (unique identifier) is TABNAME
self-describing information about TAB.DD
TAB.DD is one of the entries

Data Dictionary: COLUMNS [167]

COLUMNS

COL.DD.tbl: info about all of the columns
fields: TABNAME, COLNAME, KEY
other: INDEX STRUCTURE
key is TABNAME COLNAME.
KEY : column is used to form key
KEY : left-most part of the key
self-describing information about COL.DD
COL.DD KEY is one of the entries

Data Dictionary: DOMAINS [168]

DOMAIN

DOM.DD.tbl: information about the data types
fields: DOMNAME (COLNAME), TYPE
key is DOMNAME (COLNAME)
TYPE may be CHAR, INT, FLOAT, DATE, BIT
type for a column defined once in DOM.DD
(CITY appears in several of the tables)
self-describing information about DOM.DD
TYPE is one of the entries

Data Definition Language [169]

DDL

Data Dictionary Language
user defines structure of the database
tables names
column names
column types
keys (unique identifiers)
initialization

DML

Data Manipulation Language
user modifies and queries database

SQL

Standard Query Language
contains DDL and DML

Data Dictionary: Shell DDL [170]

sql_dd

initialize Data Dictionary

sql_table

create new table, define key, add entries to DD

sql_domain

define data type for columns

sql_ddup

update DD to current number of rows

Data Dictionary: sql_dd (Help) [171]

SQL: DD
   Usage  : sql_dd all
   Output : TAB.DD.tbl, COL.DD.tbl, DOM.DD.tbl
   Purpose: To initialize Data Dictionary with self-describing information.
   Example: sql_dd all
   
   TAB.DD.tbl:   
   +-------+----+----+
   |TABNAME|COLS|ROWS| 
   +-------+----+----+
   | TAB.DD|   3|   3|
   | COL.DD|   3|   8|
   | DOM.DD|   2|   7|
   +-------+----+----+

Data Dictionary: sql_table (Help) [172]

SQL: TABLE
   Usage  : sql_table <tbl> [key] <fld1> [key] <fld2>...[key] <fldn> 
   Output : new file <tbl> and TAB.DD, COL.DD  
   Purpose: To do sql_create and to update the Data Dictionary
   Example: sql_table animals key animal sex color
  
   TAB.DD.tbl:          COL.DD.tbl:
   +-------+----+----+  +-------+-------+---+
   |TABNAME|COLS|ROWS|  |TABNAME|COLNAME|KEY|
   +-------+----+----+  +-------+-------+---+
   |animals|   3|   0|  |animals| animal|  1|
   +-------+----+----+  |animals|    sex|  0|
                        |animals|  color|  0|
                        +-------+-------+---+

Data Dictionary: sql_domain (Help) [173]

SQL: DOMAIN
   Usage  : sql_domain <col> <type>
   Output : DOM.DD
   Purpose: To define in the Data Dictionary the data type for a column.
   Example: sql_domain animal CHAR:15
   
   DOM.DD.tbl:   
   +-------+-------+
   |DOMNAME|   TYPE|
   +-------+-------+
   | animal|CHAR:15|
   +-------+-------+

Data Dictionary: sql_ddup (Help) [174]

SQL: DDUP
   Usage  : sql_ddup all
   Output : TAB.DD.tbl
   Purpose: To update the Data Dictionary to the current ROWS for each table.
   Example: sql_ddup all
   Comment: This is much faster than updates with each new append.
 
   TAB.DD.tbl:
   +-------+----+----+
   |TABNAME|COLS|ROWS|
   +-------+----+----+
   |animals|   3|   3|
   +-------+----+----+

Data Dictionary: Example DDL [175]

Init

sql_dd all

Domain

sql_domain S# CHAR:15
sql_domain SNAME CHAR:15
sql_domain STATUS INT:15
sql_domain CITY CHAR:15
sql_domain P# CHAR:15
sql_domain PNAME CHAR:15
sql_domain COLOR CHAR:15
...

Table

sql_table S key S# SNAME STATUS CITY
sql_table P key P# PNAME COLOR WEIGHT CITY
sql_table J key J# JNAME CITY
sql_table SP key S# key P# QTY
sql_table SPJ key S# key P# key J# QTY

Insert

sql_insert S S1 Smith 20 London
sql_insert ...

Update

sql_ddup all

Data Dictionary: Example Queries 1 [176]

Query

Get the number of columns for projects J.

Select

sql_select TAB.DD.COLS from TAB.DD where 'TAB.DD.TABNAME=="J"'

RESULT           TAB.DD.COLS 3

Query

Get the TABNAMEs where CITY is used.

Select

sql_select COL.DD.TABNAME from COL.DD
where 'COL.DD.COLNAME=="CITY"'

RESULT           COL.DD.TABNAME S P J

Query

Get the DOMNAMEs that are INTegers (any length).

Select

sql_select DOM.DD.DOMNAME from DOM.DD
where 'DOM.DD.TYPE ~ /INT/'

RESULT           DOM.DD.DOMNAME COLS ROWS KEY STATUS WEIGHT QTY

Data Dictionary: Example Queries 2 [177]

Query

Get the AVG number of ROWS in all of the tables.

Summarize

sql_summarize TAB.DD ROWS >temp.tbl
sql_project temp AVG >ROWAVG.tbl

RESULT           AVG 13.4

Query

Get the TABNAMEs which have more ROWS than the AVG number of ROWS.

Select

sql_select TAB.DD.TABNAME from TAB.DD ROWAVG
where 'TAB.DD.ROWS > ROWAVG.AVG'

RESULT           TAB.DD.TABNAME COL.DD DOM.DD SPJ

Data Dictionary: Example Queries 3 [178]

Query

Get the COLNAMEs that form the primary key and their data TYPE for SPJ.

Select

sql_select COL.DD.COLNAME DOM.DD.TYPE from COL.DD DOM.DD
where '(COL.DD.COLNAME==DOM.DD.DOMNAME)&&
(COL.DD.TABNAME=="SPJ")&&(COL.DD.KEY>0)'

RESULT           COL.DD.COLNAME    DOM.DD.TYPE
                             S#        CHAR:15
                             P#        CHAR:15
                             J#        CHAR:15

Next: SQL Features Up: Relational Databases Previous: Example Queries: Part C Contents

Ted Billard 2001-10-31

$\fbox{\bf TAB.DD.tbl:}$			$\fbox{COL.DD.tbl:}$			$\fbox{DOM.DD.tbl}$
TABNAME	COLS	ROWS	TABNAME	COLNAME	KEY	DOMNAME	TYPE
TAB.DD	3	8	TAB.DD	TABNAME	1	TABNAME	CHAR:15
COL.DD	3	27	TAB.DD	COLS	0	COLS	INT:15
DOM.DD	2	18	TAB.DD	ROWS	0	ROWS	INT:15
S	4	5	COL.DD	TABNAME	1	COLNAME	CHAR:15
P	5	6	COL.DD	COLNAME	2	KEY	INT:15
J	3	7	COL.DD	KEY	0	DOMNAME	CHAR:15
SP	3	12	DOM.DD	COLNAME	1	TYPE	CHAR:15
SPJ	4	24	DOM.DD	DATATYPE	0	S#	CHAR:15
			S	S#	1	SNAME	CHAR:15
			S	SNAME	0	STATUS	INT:15
			S	STATUS	0	CITY	CHAR:15
			S	CITY	0	P#	CHAR:15
			P	P#	1	PNAME	CHAR:15
			P	PNAME	0	COLOR	CHAR:15
			P	COLOR	0	WEIGHT	INT:15
	$\fbox{\bf J.tbl:}$		P	WEIGHT	0	QTY	INT:15
J#	JNAME	CITY	P	CITY	0	J#	CHAR:15
J1	Sorter	Paris	J	J#	1	JNAME	CHAR:15
J2	Display	Rome	J	JNAME	0
J3	OCR	Athens	J	CITY	0
J4	Console	Athens	SP	S#	1
J5	Raid	London	SP	P#	2
J6	EDS	Oslo	SP	QTY	0
J7	Tape	London	SPJ	S#	1
			SPJ	P#	2
			SPJ	J#	3
			SPJ	QTY	0