Project Implementation Proposal

Data Model v0.4, by Zach Tomaszewski

for ICS 691-3, Fall 2001, taught by Dr. Joan Nordbotten

Table of Contents

• Introduction
• Context
• Users
• Sample Queries
• SSM Data Model
  • Description of Entities
  • Description of Relationships
• Implementation
  • Tables
  • Indices
  • Functions
• Conclusion
• Appendices
  • Known Design Issues and Future Ideas
  • Wishlist for Version 2.0

Introduction

The following in a data model and beginning implementation plan for an animal classification system. This system contains multimedia records for many types of animal. The system allows for the construction of a variety of classifications by using Node objects. By adding new classifications or exploring existing ones, users can examine a variety of relationships between animals. They can use alternate classifications, or even intersect different classifications, depending on their current information need.

Context

I imagine this system as a web-based, open-access system. It will be an animal encyclopedia with text and images of specific animal species. It can serve as a partial index to related works, especially since cited resources can be URLs as well as print materials. It will be a taxonomy modeling tool. Most importantly, the combination of multiple user-created classifications will make it a powerful searching and metadata system.

The system would probably be setup and maintained by either a volunteer group, non-profit organization, or university. There should be a graphical Web interface so that users can browse, see classification structures, and get the full benefit of the images and URL links to other sources. The system will be implemented using PostgreSQL.

Users

Possible users include:

• junior high students
• amateur enthusiasts (such as birdwatchers)
• biology researchers
• zoological taxonomists.

Users such as high school students and some amateur enthusiasts will likely use the system mainly as an encyclopedia to find more information on certain animals. Such uses do not involve building new classifications or adding animal records. These users will be Users in terms of the SSM below.

Other amatuer enthusiasts may compile information from various sources and create new animal records or edit current ones. Taxonomists may use the system to build models of their work. Biology researchers may add their findings to animal records. They may also use the system to examine possible relationships made visible through intersecting classficiations, such as effect of climate on gestation period. These users that create new records will be Creators in terms of the SSM below.

Sample Queries

Here are some example information needs that illustrate the system.

1. Are most of the animals that live in the arctic white in color?

User: a high school student
Required classifications: Climate (one zone of which is "arctic")
Comments: This search would simply involve examining the images of the animals contained by the Arctic node of the Climate classfication.

2. Give me a list of marine mammals.

User: biology author researching for an identification guide
Required classifications: Linnaean; Climate (one zone of which is "marine")
Comments: This search would involve intersecting the set of the animals contained by all nodes under the Mammalia node in the Linnaean classification with the set of the animals under the Marine node in the Climate classification.

3. Do state birds predominantly come from one certain family or order?

User: a bored amateur birdwatcher
Required classifications: Linnaean; State Birds (simply 50 nodes, each containing a bird)
Comments: This search would involve intersecting the set of the animals contained by all nodes under the Aves node in the Linnaean classification with the set of the animals under the State Birds root. (Ideally, when results are displayed graphically, the tree structure of Orders, Families, and Genuses can be maintained.)

4. What is the difference between the arctic and the tundra climate zones? And where can I find more information?

User: a generic user confused by the Climate classification
Required classifications: Climate classification.
Comments: This search would involve pulling up the description fields for the Arctic and Tundra nodes. The keywords fields may also provide some help. If this isn't enough information, hopefully the creator of these nodes cited some helpful, related resources that the user could examine.

5. Hey, who screwed up my Ursus maritimus animal record?!

User/Creator: an irrate Arctic biologist who granted access to her record to all other creators
Required classifications: None.
Comments: This search would need only pull up all creator records that share an edited relationship with this animal record.

SSM Data Model

-- SSM model --

Description of Entities

Animal
id	<integer> -- a TreeEntitiy foreign key
species_name	<char(60)> -- the Latin Genus-species name for this animal
common_name	<char(60)> -- the common English name for this animal.
keywords	<vchar(200)> -- keywords describing this animal to aid in searching
Description	<text> -- a description of the animal, perhaps including such things as physical characteristics, habitat, main diet, etc.
Image	filename: <char(30)> -- the name of the image file pixel-width: <integer> -- the width of the image in pixels pixel-height: <integer> -- the height of the image in pixels format: <char(4)> -- {JPEG | GIF | PNG | BMP} comments: <vchar(500)> -- any comments on the image image: <image> -- a picture of the animal type

Description of Relationships

For more information on the above entities and relationships, see v0.3. The examples there may also make the system easier to understand.

Implementation

Tables

I forsee the need to create a table for each SSM entity and relationship above, plus a table for Image objects. Thus, I will have the following tables:

• tree_entities
• roots
• nodes
• animals
• resources
• users
• creators
• cited
• edited
• opened
• r_contains_n
• n_contains_n
• n_contains_a
• image

Here are some example CREATE statements from this system.

CREATE SEQUENCE teid_counter;

CREATE TABLE tree_entities (
   teid INTEGER DEFAULT nextval('teid_counter') PRIMARY KEY,
   entity_oid OID
);

CREATE TABLE roots (
   id REFERENCES tree_entities PRIMARY KEY,
   classif_name char(60),
   keywords varchar(200),
   scope_all boolean,
   description varchar(1000)
);

CREATE TABLE r_contains_n (
   root_id REFERENCES roots,
   node_id REFERENCES nodes UNIQUE
);

Indices

I plan to index the following columns for faster retrieval (in order of imporance):

• TreeEntity.TEid (implicit)
• NcontainsN.parent
• NcontainsA.parent
• RcontainsN.parent
• NcontainsA.child
• Nodes.taxon_name
• Animals.species_name
• Animals.common_name
• Roots.root_name
• User.username

If possible, it would be nice to also form indices of keywords from the the content of all the keywords and descriptions fields, as well as from animal text objects.

Functions

Certain functions will also be required. They should do the following:

get_contained_animals(TEid)

recursively return all animals under this node (or root)

get_contained_nodes(TEid)

recursively return all nodes under this node (or root)

Conclusion

Comments or questions are appreciated!

Appendices

Thank you for reading this far! The following is a working list of notes, thoughts, and running commentary concerning current problems and future developments. Though not officially part of this report, the content of these appendices delimit the known problems . Thus comments on these points would be most helpful.

Known Design Issues and Future Ideas

New Issues

Implementation of Creator:

After reading more on SQL3, I have learned the GRANT, REVOKE NOTIFY, and LISTEN commands might be possible replacements to the opened and edited relationships. GRANT does require a sequence, but this could be (relatively) easily incorporated as the TreeEntity ID. However, I do not know if this access would be inherited from this Entity to Node, Root, and Animal, which is where it is actually needed.

Benefits of switching:

• It would make the model clearer by separating User records and actions from the data itself. As it currently stands, a number of relationships must be maintained with each creation and update.
• It would rely on an established system for allowing users to grant rights to other users. (Wouldn't have to reinvent the wheel.)

Drawbacks of switching:

• This means every User will need a username and password to the OS on which the database runs, rather than just accessing through a web interface.
• If this means that the User and Creator entities, as currently stated, are unnecessary, a different system or database would be needed to keep track of user information. Or we could just not collect user information.
• I still want edits to leave permanent records. (Implying that the Creator entity, at least, is still necessary.) This means catching and recording NOTIFY messages, which are apparently only 31 characters long. This could be a combination of TreeEnitity ID and editingCreator; this would certainly be a possible system. Yet it is warned that, because NOTIFY messages are sometimes dropped, one should not rely on the number recieved. Overall, I am not assured that the LISTEN/NOTIFY system can reliably do what I want it to do. [Probable conclusion: Creator and edits will still be needed regardless, and will be based on rules or triggers.]
• This still leaves the opens relationship. Perhaps I can find out if GRANT status is inherited. I think it unlikely, since this could leave to overlaps.

This needs more study.

Update: After further study, I realized that GRANT applies to the sequence relation (a counter basically), and not to the rows numbered using that sequence. Thus, after all that, I realized I can not use GRANT to control access to the entity objects of the system because they will correspond to table rows and not distinct tables.

Arrays rather than contains relationship tables?

At first this seemed like a good idea, but after further consideration I realized it would make determining parents impossible. Admittedly, working up a tree will be much more rare, but it still seems a necessary feature.

OIDs not the answer.

Just as this page was going to "press" (this project is ongoing and doesn't seem to stop for progress reports) I realized that, while OIDs are unique identifiers for rows, I can't seem to use them without also knowing the table they're in. This was the reason I started using them in the first place; I do not yet know how to determine which table (roots, nodes, or animals) a certain entity (row) is in. I suppose could just SELECT FROM all three tables; because the teid is unique, the id will occur in only one of the tables. The other alternative is to include a table-identifying column in tree_entities.

From v0.3's Known Design Issues:

Entities

Animal:

• Keywords could come from a thesaurus, if an appropriate one can be found. I know most Creators will not be skilled at determining their own descriptive keywords from thin air.
• I think the text object should be just plain text. Perhaps some basic HTML could be allowed, such as paragraph, bold, and list tags. Possibly links as well. More than that would likely endanger the web interface.
• The image complex object could be expanded to a multimedia (video, audio, multiple images, etc) in future editions. In this case, where each animal would have a variety of different multimedia objects, a weak entity would serve very well. At this time, I only want one image though.
• Resolution was not included because it's hard to determine; most users uploading the file probably won't know it. Hopefully the system will be able to calculate the image dimensions for the user.
• I see I forgot the imageCite again. I suppose this should be a full-blown relationship between Animal and Resource. Yet it seems more appropriate to be included in the Image complex object. Hmm...

Root:

• Again, some keyword help for users is going to be needed here.
• I think there should be a path attribute that holds where this root should fit in a navigational hierarchy. There are going to a lot of classifications, and so there will need to a classification of them. (scope will help by splitting the roots into two groups.) I'll include this attribute when I know how it will be determined and what it will contain.

Resource:

• There is no bibliographic authority control in this system. Thus, there can be multiple records for the same source. This was allowed so that Creators could include their own notes. (Perhaps a better solution would be into include a Comments field in the cites relationship. But then there would be the whole problem of who insures correctness across the system, and users would have to search for existing records. Though not as suave, I think the current system is easier.)
• These are not weak entities because Creators will likely want to reuse the same sources from different entities.
• I should perhaps include fields such as journal_title and volume_number here.

User:

• I also want a preferences field here to save users' interface personalizations. I don't know what that will look like yet, though.
• People will probably forget their usernames. The system can email it to them at their stated email address.
• There should probably still be a default Guest user for those who want don't want to register. (This depends on system resources; if they're low, administrators may want to force registration to discourage casual traffic.)

Relationships

contains:

• Still debating on that (2,n) cardinality for RcontainsN. Most classifications will have at least 2 nodes. But then, perhaps someone just wants to label a single attribute, such as "egg-layers". But shouldn't this then contain "not egg-layers"? Hmm...
• I have learned that NcontainsN implies a relationship between different nodes. Good; that way a Node won't contain itself.
• Still gotta remember that a Node can contain Nodes or Animals but not both. (I guess I've decided this issue.) Stick in a trigger on that or something.
• Also, every Node must have one and only one parent.
• There will have to be a function defined that traverses these relationships.

opened:

• Issues of scope brought up in v0.2 appendix seem solved. If a Creator is working on a whole Tree, she can pass on access from a certain Node. Once a creator with access creates their own Node under that one, they can in turn pass on access.
• I'm assuming that adding relationships requires access to the parent node.
• I have also included the edited relationship to track changes. This way, a Creator can open an Entity to all, yet still be able to track who did what when.
• I have not extended these relationships to Resources. Perhaps I should. As it stand, it looks like anyone (probably Creators) can add or change an existing Resource. I'll think on this more. Perhaps this can be cleanly controlled by the system outside of the SSM.

Wishlist for Version 2.0

See v0.3's Wishlist.