The schema is design to support ADL buckets level search and report. It's not a native data set maintenance schema. The schema is intend to provide a general support for current ADL buckets search and data/metadata access. This database schema is designed for Informix Universal Database Server 9.x SpatialWare with Mapinfo and Verity Text Search DataBlades. It will not work on other database system.
Full metadata report and data access information page format, generating method, and storage are external to the schema. This schema only keep the urls which lead to full metadata report and data access information page.
CREATE ROW TYPE alltext_t
(title varchar(255) not null,
assigned_terms lvarchar,
topical_text lvarchar
);
CREATE TABLE j_contact (
-- Ingest person contact information.
-- Ingest NOTE:
-- Column j_contact.name must be unique
-- (i.e. one person has one entry only).
contact_id integer NOT NULL,
name varchar(255) NOT NULL unique,
email_address varchar(255),
phone_number varchar(255),
PRIMARY KEY (contact_id)
);
CREATE TABLE j_collection (
-- Ingest data set (collection) information.
-- INGEST NOTE:
-- The collection name j_collection.name must be unique.
collection_id integer NOT NULL,
contact_id integer NOT NULL,
name varchar(255) NOT NULL unique,
description varchar(255),
PRIMARY KEY (collection_id),
FOREIGN KEY (contact_id)
REFERENCES j_contact
);
CREATE TABLE j_holding (
-- The major table for each holding record information.
-- INGEST NOTE:
-- (1) j_holding.footprint is ST_Spatial type. There are standard formats
-- for data representation given in Mapinfo Spatail DataBlade documnets.
-- Please consult with Qi and
-- http://www.alexandria.ucsb.edu/~zheng/informix/SW30UG/
-- (2) Four bounding coordinates, n_bounding_coord, s_bounding_coord,
-- e_bounding_coord, w_bounding_coord, are in unit of degree in the
-- range of -180 to 180 for longituide and -90 to 90 in latituide.
-- (3) j_holding.all_text.assigned_terms, j_holding.all_text.topical_text,
-- and j_holding.originators all have values (terms) for the bucket in
-- one line. All terms are sparated by a symbol
-- ' ; ' (e.g. scaned map ; large print ; transportation maps ; GIS
-- Tom Smith ; USGS ; University of California ; ADL-UCSB
-- ).
-- (4) resolution_meter is for the diameter of the smallest identifiable
-- object in the data item.
-- (5) modification_date is either the ingest date or last modification date.
holding_id integer NOT NULL,
collection_id integer NOT NULL,
footprint ST_Spatial NOT NULL,
n_bounding_coord real NOT NULL
CHECK (n_bounding_coord BETWEEN -90.0 AND 90.
0),
s_bounding_coord real NOT NULL
CHECK (s_bounding_coord BETWEEN -90.0 AND 90.
0),
e_bounding_coord real NOT NULL
CHECK (e_bounding_coord BETWEEN -180.0 AND 18
0.0),
w_bounding_coord real NOT NULL
CHECK (w_bounding_coord BETWEEN -180.0 AND 18
0.0),
all_text alltext_t NOT NULL,
originators lvarchar,
resolution_meters real,
thumb_image_url varchar(255),
full_report_url varchar(255) NOT NULL,
access_report_url varchar(255) NOT NULL,
modification_date date NOT NULL,
PRIMARY KEY (holding_id),
FOREIGN KEY (collection_id)
REFERENCES j_collection
);
REATE TABLE j_identifier (
-- INGEST NOTE:
-- All identifier must be ALPHANUMERICAL. All non-alphanumerical
-- characters are deleted.
-- e.g. "a-145 6" must be converted to "a1456".
-- "/rr/Kg_6.gif" to "rrkg6gif".
holding_id integer NOT NULL,
identifier varchar(255) NOT NULL
FOREIGN KEY (holding_id)
REFERENCES j_holding
);
CREATE TABLE j_date_range (
-- INGEST NOTE:
-- date format: '1998-07-13'
-- Use date_range_textual to present date information in
-- text formate for those partial/fuzzy date "1998", "May, 1998", "Spring, 97".
holding_id integer NOT NULL,
begin date NOT NULL,
end date NOT NULL,
date_range_textual varchar(255) NOT NULL,
FOREIGN KEY (holding_id)
REFERENCES j_holding
);
CREATE TABLE j_browse_image (
holding_id integer NOT NULL,
url varchar(255) NOT NULL,
byte_size integer,
pixel_width integer,
pixel_height integer,
type varchar(255),
FOREIGN KEY (holding_id)
REFERENCES j_holding
);
CREATE TABLE j_type (
type_id integer NOT NULL,
name varchar(255) NOT NULL unique,
description varchar(255),
PRIMARY KEY (type_id)
);
CREATE TABLE j_holding_type (
holding_id integer NOT NULL,
type_id integer NOT NULL,
PRIMARY KEY (holding_id, type_id),
FOREIGN KEY (type_id)
REFERENCES j_type,
FOREIGN KEY (holding_id)
REFERENCES j_holding
);
CREATE TABLE j_format (
format_id integer NOT NULL,
name varchar(255) NOT NULL unique,
description varchar(255),
PRIMARY KEY (format_id)
);
CREATE TABLE j_holding_format (
holding_id integer NOT NULL,
format_id integer NOT NULL,
PRIMARY KEY (holding_id, format_id),
FOREIGN KEY (format_id)
REFERENCES j_format,
FOREIGN KEY (holding_id)
REFERENCES j_holding
);
1) Update j_contact table:
Check if your contact information is in the j_contact table and is correct.
Add or modify the your contact infomation if it's missing or incorrect.
Use your contact_id for next step.
2) Update j_collection table:
Check if the data collection you are going to ingest or modify is in the
j_collection table. If the collection information is missing, add the new
collection infomation into the j_collection table and set the contact_id to
be your contact_id from Step 1. The collection_id will be used in the
j_holding table.
3) Update j_format, and j_type:
Verify that j_format, and j_type tables have all the
format, and type which is needed.
Add new format, and type into the j_format and j_type
tables if it's neccessary.
4) Populate the j_holding table:
-- Use correct collection_id
5) Populate j_holding_formats and j_holding_types tables:
-- Use correct format_id and type_id.
-- It's REQUIRED that every holding must have at least one type and
one format. For gazetteer type record which only has metadata
and has no "associated data" the format can be "metadata only".
6) Populate j_date_range table and j_identifier.
7) Populate j_browse_image
Send your comment or question to Qi Zheng.