BEN Metadata Repository Database specification

Written 11/21/01
by Cal Collins
Founder & Chief Technology Officer
e-guana.net, Inc.
617.868.9669
617.868.1250 (fax)
617.504.4664 (cell)
ccollins@e-guana.net

Developed on behalf of the American Association for the Advancement of Science
Copyright 2001 AAAS

--------------------------------------------------------------------------------

Note: recent changes have been made to the database schema that may not yet be reflected in this document. Please see the Changes and SQL documents

Contents

1. Introduction

• 1.1 Scope
• 1.2 Definitions
• 1.3 Typographic conventions

• 2.1 Metadata Tables
• 2.2 SQL Schema

1. Introduction

The BEN (Biological sciences Education Network) Collaborative is an effort to develop and launch a portal for biology educators. This portal will be "a catalyst for individual undergraduate biology educators to improve their teaching through resources, collaboration, and network building. However, it is also a catalyst for participating biology research and education-based organizations to build collaborations in terms of pedagogy, authentic assessment, multidisciplinary resources, and development of their individual digital library collections." [BEN Collaborative Cycle 2 Proposal] The centerpiece of this portal is a searchable database of metadata on peer-reviewed biology education resources taken from the collections of the BEN partner societies.

1.1 Scope

The database contains the metadata of all the materials in the BEN collection. This database is consistent with the IEEE Learning Object Metadata (LOM) specification (IEEE P1484.12/D6.1) and the BEN 1.0 implementation of the IEEE LOM. The database was constructed to power the three main components of the BEN portal: - A search engine to power location and retrieval of items from the BEN collection of biology education materials that is searchable on multiple conditions/characteristics of the materials stored in the collection. - A metadata collection and indexing tool that consists of a portable metadata harvester based on the LOM 1.0/BEN 1.0 metadata standards that transmit learning resource metadata from the collections of the BEN member societies to the central BEN site. - A contributor interface for the BEN site to accept submissions to the portal and route the submission to the collection of the appropriate BEN member society.

1.2 Definitions

All definitions will be taken from the IEEE LOM and/or BEN 1.0 implementation of the IEEE LOM. In the case of a discrepancy, the BEN 1.0 implementation will take precedence.

1.3 Typographic Conventions

Actual computer file names, field names, and field contents (character strings as entered and stored) are shown in typewriter type.

2. General Structure of the Database

The database has 52 separate tables. 35 of these tables store information about metadata records. These 35 tables begin with the prefix tbl_meta_ followed by the metadata path of the element being stored in the record. Of the other 17 tables, three, tbl_users, tbl_userclasses, and tbl_useractions, contain information about registered users and their permission levels for performing various actions on the site. A partners table, tbl_Partners, stores information on the BEN Partners, The remaining 13 tables have names that begin with the prefix tbl_ddlb_ and are used to generate drop-down lists for submissions and searches based on the BEN LOM 1.0 vocabulary.

2.1 Metadata Tables

Each unique metadata record will have a unique record in the table tbl_meta_records. The table tbl_meta_records has the following fields:

recordID - a unique integer that serves as the ID # for the record and the table's primary key
dateCreated - the date the record was created (YYYY-MM-DD)
timeCreated - the time the record was created (HH:MM:SS)
dateModified - the date the record was last modified (YYYY-MM-DD)
timeModified - the time the record was last modified (HH:MM:SS)

The other 34 tables hold metadata elements in a relational structure. The recordID field will serve as a foreign key in each of the other 30 tables. There are nine tables that represent the top level metadata categories:

tbl_meta_general
tbl_meta_lifeCycle
tbl_meta_metaMetadata
tbl_meta_technical
tbl_meta_educational
tbl_meta_rights
tbl_meta_relation
tbl_meta_annotation
tbl_meta_classification

Each of these tables has the following fields:

id - a unique integer that serves as the ID # for the record and the table's primary key
recordID - a foreign key from the records table; the ID # for the metadata record
datetime- the date the record was last modified (YYYY-MM-DD)

Each of these nine tables also have unique fields that correspond to some of the metadata elements for that metadata category. For instance, the table general has in addition to the 3 fields above, the following fields, all of data type text:

identifier
title
structure
aggregationLevel

Each of these fields corresponds to a metadata element that has (as described in the metadata specification) a maximum size of 1, meaning that for every instance of the metadata category of 1.0 - General, there can be no more than 1 value for each of the fields identifier, title, description, structure, and aggregationLevel.

However, the metadata also specifies that 1.0 - General should have value spaces for Catalog Entry, Language, Keywords, and Coverage. Each of these can have multiple values within 1.0 - General. Therefore, a child table exists for each:

tbl_meta_general_catalogEntry
tbl_meta_general_language
tbl_meta_general_keywords
tbl_meta_general_coverage

Each of these tables contains the following fields:

id - a unique integer that serves as the ID # for the record and the table's primary key
recordID - a foreign key from the records table; the ID # for the metadata record
parentID - the id number of the appropriate parent record from the tablename specified in the category field
datetime- the date the record was last modified (YYYY-MM-DD)

Each of these child tables also have unique fields that correspond to some of the metadata elements for that metadata category. For instance, the table tbl_meta_general_catalogEntry has in addition to the 5 fields above, the following fields, all of data type text:

catalog
entry

Each of these fields corresponds to a metadata element that has (as described in the metadata specification) a maximum size of 1, meaning that for every instance of the metadata category of 1.3 - General CatalogEntry, there can be no more than 1 value for each of the fields catalog and entry.

This general structure repeats itself throughout the database based on the metadata specification. There are three important points left to make:

• The parent-child relationship of the tables can continue to an infinite depth. Therefore, in the example above, if 1.3 - General CatalogEntry had a child element that had a size greater than 1 (as occurs in 9 - Classification), another table would exist that would hold those values. By referencing the recordID of the oveall metadata record and the parentID of where its parent metadata element is, all the necessary information exists to accurately identifiy the metadata, no matter how many levels deep the elements go.
• Some child tables (for example, the table language) only contain 1 metadata field. In this case the field name is value (rather than language).
• In some instances, child elements in different sections of the metadata have the same structure. For example, both 2 - LifeCycle and 3 - MetaMetadata have an element 'Contribute' which has the child properties 'Role', 'Entity', and 'Date'. However, though the child elements have the same structure, the LifeCycle.Contribute elements are stored in the table tbl_meta_lifeCycle_contribute, while the MetaMetadata.Contribute elements are stored in a separate table, tbl_meta_metaMetadata_contribute.

2.2 SQL Schema

Click here for the complete SQL schema