-- ############################################################################## -- -- File: triplerock.CS.Berkeley.EDU:/private/S2K/Meta/FGDC/Src/FGDC_Meta1.msql -- -- Auth: Paul Brown -- -- Date: Late May - June 1994 -- -- This script defines the Illustra (ex - Montage) SQL schema -- for the FGDC Spacial Meta Data standards. Although this -- standard deals with spacial data, it is hoped that it will -- work for the Sequoia style data as well. -- -- Note that some of the information required in the schema -- can be derived from the more basic schema which the Meta -- Data piggy-backs onto. Where this is possible, and the -- other components aren't prepared, a gap with appropriate -- explaination is included. -- -- This implementation needs the Spatial, Image and Document -- Blades for Illustra. The document blade, while desirable, isn't -- available. I'll 'fudge it' with text structures for most things. -- -- This is the logical schema. No effort is to be made in -- advance of some application to apply tuning efforts -- to this document except that; -- -- Each Relation has a primary key on which an index is -- constructed. This Key is identified in the definition. -- -- Any Relation which is the target of a 'ref' will have an -- index on the Oid's constructed for it. If an oid is used on -- a join, I'm maintaining an index on the oid. -- -- Every effort is made to preserve normal form in the layout -- of the tables. Any setof() attributes are represented as -- functions. -- -- I keep to the naming conventions as supplied as much as -- possible, to avoid confusion. -- -- Conventions; -- -- o each Illustra (ex - Montage) reserved word is in lower case. -- -- eg. 'create table', 'create index' etc -- -- o every class has a type. Not every type has a class, however. -- -- eg. Each 'create table' which does not specify some other -- type has the 'of new ty[e' clause. -- -- o Relations have the first letter of their name capitalized and -- are the plural of the root form. -- -- eg. 'create table Foos . . .' -- -- o Types have the suffix _t appended by convention to the singular -- of the root form. -- -- eg. 'create table Foos with new type Foo_t . . .' -- -- o Attributes are singular, Capitalised, and of the root form. -- -- create table Descriptions -- of new type Description_t -- ( -- Description text -- ); -- -- o Each type will have at least the following functions -- defined for it; (except for *very* simple types like -- text) -- -- EQ(X,Y) - bool if X == Y -- GR(X,Y) - bool if (X > Y) -- LT(X,Y) - bool if (X < Y) -- GE(X,Y) - bool if ((Eq(X,Y)) || (Gt(X,Y))) -- LE(X,Y) - bool if ((Eq(X,Y)) || (Lt(X,Y))) -- -- Other functions are defined where sensible. -- -- o Attributes are as tightly domain bound as possible. -- -- o An inherent difficulty in using the Object approach here is -- making the thing work both up and down. That is, we want -- users to be able to display all the information associated -- with a 'discovered' resource, and to use each of the pieces -- of information to 'discover' the governing resource. The -- Object approach to this requires navigation. I think I'll -- use the oids for each of the lower resources as a surrograte -- key. Changes to any of the associated meta-data stuff will -- be achieved by an edit/addition to the text. A notes -- principle. -- -- o I have declared as much as I can of the referential integrity -- into the schema. -- -- o Although the schema doesn't explicitly set this out, I've included -- the standard Codes tables to allow for sets of codes expressed in -- normal form. This may barf performance wise, but we'll deal with -- than as we have to. -- -- o There are extensive comments throughout the schema explaining the -- whys and assumptions. -- -- o Everything changes . .. -- -- --##################################################################### -- -- MetaData = -- Identification_Information + -- Status_Information + -- Metadate_Reference_Information; -- -- Note: The complete FDGC spec includes; -- -- 0{Data_Quality_Information}1 + -- 0{Spatial_Data_Organization_Information}1 + -- 0{Spatial_Reference_Information}1 + -- 0{Lineage}1 + -- 0{Entity_and_Attribute_Information}1+ -- 0{Distribution_Information1}; -- -- Some of this can be derived from system catalog information. Some -- would be replicated from the underlying SAIF schema (or whatever). -- The emphasis for the first run is on Resource Discovery which is -- unlikely to be driven by queries on the orgnisational structure, -- but rather on the content, which is reflected in the three -- sub-types done first. -- -- Subsequent components will be incorporated in seperate scripts, -- once per module, and the final script which "drags it all together" -- will allow the user to 'plug and play' their requirements. -- -- In order of importance, the next components will be Data_Quality, -- Distribution_Information. Then the rest of the associated -- information in no particular order. -- --##################################################################### -- -- Identification_Information = -- Data_Set_Identity + -- 0{Identification_Code}1 + -- Data_Set_Description + -- Theme + -- Bounding_Coordinates + -- ( 1{Data_Set_G-Polygon}n + -- 1{Geographic_Keyword}n + -- 1{Browse_Graphic}n + -- Data_Set_Citation + -- Data_Set_Credit + -- Use_Restrictions + -- Access_Restrictions + -- Security_Information + -- Native_Data_Set_Environment ); -- -- Data_Set_Identity = text; -- -- Limited to 8K, but this shouldn't be a problem. This is the -- title field. -- -- Identification_Code = text; -- -- Limited, again, but better than nothing. -- -- Data_Set_Description = text; ** OR doc!! ** -- -- Theme = -- 1{Theme_Key_Word_Thesaurus + -- 1{Theme_Keywords}n}n}; -- -- Theme_Key_Word_Thesaurus = ref(); -- Theme_Keywords = text; -- -- The general idea is that the Theme_Keywords would be populated by -- scanning the Data_Set_Description for elements in the Theme_Key_Word_Thesaurus. -- -- Bounding_Coordinates = -- West_Bounding_Coordinate + -- East_Bounding_Coordinate + -- North_Bounding_Coordinate + -- South_Bounding_Coordinate ; -- -- Data_Set_G-Polygon = -- Data_Set_G-Polygon_Outer_G-Ring + -- 0{Data_Set_G-Polygon_Exclusion_G-Ring}n; -- -- Data_Set_G-Polygon_Outer_G-Ring = -- Polygon; -- -- Data_Set_G-Polygon_Inner_G-Ring = -- Polygon; -- -- Rule 1 : Poly_Contains(Data_Set_G-Polygon.Data_Set_G-Polygon_Outer_G-Ring, -- 0{Data_Set_G-Polygon.Data_Set_G-Polygon_Inner_G-Ring}); -- -- Browse_Graphic = -- Browse_Graphic_File_Name (large_object)+ -- Browse_Graphic_File_Description(GR_Desc)+ -- Security_Handling_Description (SEC_Desc); -- -- Data_Set_Citation (Cit_Code); -- -- Data_Set_Credit (Text); -- -- Use_Restrictions (Text); -- -- Access_Restrictions (Text); -- -- Security_Information = -- Security_Classification_System (date)+ -- Security_Classification (SEC_Code), -- Security_Handling_Description (Comment); -- -- Native_Data_Set_Environment = -- NDS_Code; -- -- -- From the Bottom Up! begin transaction; -- -- Need two types. One is the straight description of stuff, -- which may constitute a tag, or evolving comment, but is unindexed. -- The second is an indexed variety of thingy that will be included -- in any index on which resource queries are composed. This can -- be dereffed to get the bits involved! -- -- As a first 'normalization' step I'm pulling the un-indexed stuff -- back into the table. (De-Normalization!) I've called each of these -- de-normalised text attributes 'Blurb', to distinguish them from -- Description, which could confuse some possums. -- create table Descriptions of new type Description_t ( Description text -- This will go to being a doc ); -- -- Graphic -- -- Need the ability to store graphic with practically -- everything. This is for a GUI interface, point and click -- solution to the data entry stuff. Also for other graphic -- things - sound, etc. -- -- create table Graphic_Type_Codes of new type Graphic_Type_Code_t ( Code text not null unique, -- The only code not in code's table! Blurb text ); -- -- create table Graphics of new type Graphic_t ( Graphic image, -- This is the Image Blurb text, -- Some description of the Image GraphicType text references Graphic_Type_Codes(Code) ); -- -- For standards; -- -- -- Code -- -- This is a type/table which will need to be pretty widely -- disseminated through the schema.The principle of keeping -- codes unique, while allowing the code's meanings to change, -- drives this. -- -- While hardly elegant, this allows the user a) a codes list which -- speeds up data entry, b) some text for written reports -- c) the capacity to associate a graphic with a code, making -- GUI easier d) the flexibility of storing each Code Type in -- the schema, and rule driven referential integrity. -- create table Basic_Codes of new type Basic_Code_t ( Code_Type text not null unique, Blurb text --, Graphic ref(Graphic_t) There may not be a graphic, so -- all non-graphics get some default. ); create function EQ(Basic_Code_t, Basic_Code_t) returns boolean as return ($1.Code_Type = $2.Code_Type); create function NE(Basic_Code_t, Basic_Code_t) returns boolean as return ($1.Code_Type <> $2.Code_Type); create function GT(Basic_Code_t, Basic_Code_t) returns boolean as return ($1.Code_Type > $2.Code_Type); create function LT(Basic_Code_t, Basic_Code_t) returns boolean as return ($1.Code_Type < $2.Code_Type); create function LE(Basic_Code_t, Basic_Code_t) returns boolean as return ($1.Code_Type <= $2.Code_Type); create function GE(Basic_Code_t, Basic_Code_t) returns boolean as return ($1.Code_Type >= $2.Code_Type); -- -- These are some examples of the Basic_Codes, which are schema -- driven, and can be changed. Because the codes are only in -- a couple of places, an update can trigger a cascade to -- make the Codes consistent throughout the system. -- -- In practice, this will be a highly restricted transaction. -- -- Codes table (Code, and a Code which tells you what type of -- Code this is!!) -- create table Codes of new type Code_t ( Code text not null references Basic_Codes(Code_Type), Code_Type text not null references Basic_Codes(Code_Type) ); -- -- I can't let there be duplicates in this table, and I can't seem -- to declare multi-column uniqueness; Bummer! -- -- Note: I'd like to be able to jointly specify this! -- -- I won't turn the rules stuff at this time. -- -- I can't turn the rule on. Darn!! -- -- create rule R1 on insert to Codes -- where exists ( select 1 -- from Codes -- where Codes.Code = new.Code -- and Codes.Code_Type = new.Code_Type) -- do instead nothing; -- create rule R2 on update to Codes -- where exists ( select 1 -- from Codes -- where Codes.Code = new.Code -- and Codes.Code_Type = new.Code_Type) -- do instead nothing; -- -- There is no mapping function between members of this -- set. There is no need to compare them. Their existance -- precludes equality, they will all be non-equal, and -- being GT or LT isn't relevent. -- -- Enough of this codes rubbish already! AAAGH! -- -- Native_Data_Set_Environment -- -- Description of the data set in the producer's processing -- environment, including items like software, HW, etc. This -- is the external origin of the data, or where else - apart -- from this dataset - it can be found. Source information. -- -- create table Native_Data_Set_Environments of new type Native_Data_Set_Environment_t under Descriptions; -- -- Security -- -- All done with Codes, Resolution and virtual tables. (Magic!) -- -- Access Restrictions -- -- The idea is for another Codes Table driven security system. Each Object -- will have an access description type which identifies the User Restrictions. -- Note that this may - or may not - be used to drive a rules oriented -- security system. -- -- User Restrictions -- -- The idea - again - is to use the conventional Raltional Codes table. -- -- Data Set Credit -- -- Not needed for a codes table. Ideally, this would be some set of users, -- but that will be in future, when every user of the net gets an -- id. -- create table Data_Credits of new type Data_Credit_t under Descriptions; -- -- Data Set Citation -- -- Ditto create table Data_Citations of new type Data_Citation_t under Descriptions; -- -- Browse Graphic -- -- Refer to the resolution table after the main -- MetaData definition. The Browse Graphic stuff -- makes use of types already defined. -- -- Graphic Keyword -- -- This is also a resolution table between the -- MetaData and whatever the thesaurus ends up -- being, as this is an n:m relationship. -- -- -- Data Set Polygon -- -- This will be stored redundantly, and calculated -- from the data in the associated schema. The type -- of the bounding box will be determined by the -- 'real' data schema associated with this meta-data. -- -- For now, it's just a polygon, for brevity. -- create table Bounding_Coords of new type Bounding_Coord_t ( Bounding_GPolygon polygon not null unique ); -- -- Theme -- This is the list of subjects the data proper handles. -- This is to be handled as a resolution table. -- However. we need a Themes table and the appropriate -- type. -- -- Something more sophisticated will need to be enforced here -- with the doc blade, but for now this works!! -- create table Themes of new type Theme_t ( ThemeWord varchar(20) not null unique, Thesauri oid not null -- Who knows!! ); -- -- Data Set Description -- This is the text associated with the Meta Data. This will be -- Anoted, etc, and will be a large_text type, in case people -- want to add stuff to it. This makes the tuple small -- and doesn't limit the utlity of the attribute. -- create table Data_Set_Descriptions of new type Data_Set_Description_t under Descriptions; -- -- Identification Code -- This is the order by code. Just varchar(20) at this time, -- but I might need some kind of odd structure here to -- maintain a strict ordering. (Need to insert between etc). -- -- Data Set Identity -- Title. Has to be text. Bleagh!! Will ref() it for fun. -- -- Data Set Id -- This is an Id I'll generate. A number to keep track of -- the data. -- -- Identification_Information -- At last! Tie it all together -- create table Identification_Informations of new type Identification_Information_t ( DataSetId integer not null unique, IdentificationCode varchar(20), DataSetDescription ref(Data_Set_Description_t), Theme setof(Theme_t) virtual,-- Function of resolution table BoundingCoordinates box, -- Function of Bounding polygon BoundingPolygon ref(Bounding_Coord_t), -- Polygon stored redundantly BrowseGraphic setof(Graphic_t) virtual,--Function of resolution table DataSetCitation ref(Data_Citation_t), -- In case they're big. DataSetCredit ref(Data_Credit_t), UseRestrictions setof(Basic_Code_t) virtual,--Note the resolution tables AccessRestrictions setof(Basic_Code_t) virtual,--and functions for these. SecurityInformation setof(Basic_Code_t) virtual, NativeDataSetEnvironment ref(Native_Data_Set_Environment_t) ); -- -- Whew! At 60 bytes per tuple overhead, and 8 bytes for a ref(), -- this gives me a tuple width of 4 + 20 + (8*8) + 60 = 148bytes, -- with an overhead to data ratio of 60/88. Whether this is a -- good thing or not will depend on the use of the text fields!! -- -- And the degree of redundancy in the codes tables, for example. -- -- These are the resolution tables to handle the n:m entity -- relationships. -- create table Browse_Graphic_Resols of new type Browse_Graphic_Resol_t ( IdentificationInformation oid not null, BrowseGraphic oid not null ); -- -- These are necessary rules for ensuring that no duplicate rows -- exist; -- create rule R3 -- on insert to Browse_Graphic_Resols -- where exists ( select 1 -- from Browse_Graphic_Resols B -- where B.IdentificationInformation = new.IdentificationInformation -- and B.BrowseGraphic = new.BrowseGraphic ) -- do instead nothing; -- -- create rule R4 -- on insert to Browse_Graphic_Resols -- where exists ( select 1 -- from Browse_Graphic_Resols B -- where B.IdentificationInformation = new.IdentificationInformation -- and B.BrowseGraphic = new.BrowseGraphic ) -- do instead nothing; -- create function BrowseGraphic(Identification_Information_t) returns setof(Graphic_t) as begin -- Give me the Graphics which select G.* -- are associated with this from Graphics G, Browse_Graphic_Resols R -- Identification Info. where $1.oid = R.IdentificationInformation and R.BrowseGraphic = G.oid; end; -- -- Variation on theme. Someone may want only the Pictures, or -- whatever. -- create function GetBrowseGraphic(Identification_Information_t,text) returns setof(Graphic_t) as begin select G.* -- Give me the Graphics which from Graphics G, Browse_Graphic_Resols R -- are associated with this where $1.oid = R.IdentificationInformation-- Identfication Info and R.BrowseGraphic = G.oid -- are of a specific type. and G.GraphicType = $2; end; -- -- Theme Resolution Table -- create table Theme_Resols of new type Theme_Resol_t ( IdentificationInformation oid not null, Theme oid not null ); -- -- Rules to ensure no duplicates; -- --create rule R5 --on insert to Theme_Resols --where exists ( select 1 -- from Theme_Resols T -- where T.IdentificationInformation = new.IdentificationInformation -- and T.Theme = new.Theme ) --do instead nothing; ---- --create rule R6 --on update to Theme_Resols --where exists ( select 1 -- from Theme_Resols T -- where T.IdentificationInformation = new.IdentificationInformation -- and T.Theme = new.Theme ) --do instead nothing; -- create function Theme(Identification_Information_t) returns setof(Theme_t) as begin select T.* from Themes T, Theme_Resols R where R.IdentificationInformation = $1.oid and T.oid = R.Theme; end; -- -- Codes Resolution table; -- -- For UserRestrictions Codes. -- create table Codes_Resols of new type Codes_Resol_t ( IdentificationInformation oid not null, Code oid not null -- Basic_Codes oid ); -- -- Rules to ensure no duplicates; -- -- create rule R7 --on insert to Codes_Resols --where exists ( select 1 -- from Codes_Resols C -- where C.IdentificationInformation = new.IdentificationInformation -- and C.Code = new.Code ) --do instead nothing; -- --create rule R8 --on update to Codes_Resols --where exists ( select 1 -- from Codes_Resols C -- where C.IdentificationInformation = new.IdentificationInformation -- and C.Code = new.Code ) --do instead nothing; -- create function UserRestrictions(Identification_Information_t) returns setof(Code_t) as begin select B.* from Basic_Codes B, Codes_Resols R where R.IdentificationInformation = $1.oid and R.Code = B.oid; end; -- -- For Access Restriction Codes. -- create function AccessRestrictions(Identification_Information_t) returns setof(Basic_Code_t) as begin select B.* from Basic_Codes B, Codes_Resols R where R.IdentificationInformation = $1.oid and R.Code = B.oid; end; -- -- For Security Restriction Codes -- create function SecurityInformation(Identification_Information_t) returns setof(Basic_Code_t) as begin select B.* from Basic_Codes B, Codes_Resols R where R.IdentificationInformation = $1.oid and R.Code = B.oid; end; -- -- Double whew! -- end transaction; -- -- ##############################################################################