- Geoscience Australia
Contents
1. and may therefore update Reference Details Block Reference ID pakit Entered By Biliags Year User s ReFID EST ee Entry Date fEEaUmerr R Title Phylogenetic analysis and classification of the Brachiopoda reply and comments Vol Part Pages EEREN Save these reference details for duplicating to subsequent entries J Count 1 lt Replace gt R E F E R E N C E Figure 5 The Reference Insert Update form This form is described first as it is the simplest in AGSOREFS that includes all the fields in the AUTHORS and REFERENCES tables and it is the only form you can use to enter a new reference into the database The form is accessible from item 4 in the AGSOREFS menu only by the owner and a few specially privileged users Most users see item 4 in a colour different to the rest of the menu meaning they cannot access it directly They must first query the database with another form before they can enter references with this form see Sections 9 amp 11 This procedure is aimed at reducing the incidence of redundant references in the database The insert update form also warns you before saving a reference if it finds other existing references with the same title and year However the comparison must be letter and punctuation perfect for duplicate detection to work The form can be used to query the AUTHORS and REFERENCES tables but not the full database but users see only the references that they themselves2. with the full references listed at the end of the document However the details of layout punctuation and text enhancements vary widely and there are a number of international standards in different disciplines The new AGSO standard is similar to the style used by the United States Geological Survey Geological Society of America Elsevier Economic Geology and several other prominent earth science journals Jt is designed to reduce the work required to prepare a reference list Gone are small capitals bold text and italics except for taxonomic names in titles The style is best illustrated with the help of a few examples Burst J F 1965 Subaqueously formed shrinkage cracks in clay Journal of Sedimentary Petrology 35 348 353 Davies G R 1970 Algal laminated sediments Gladstone Embayment Shark Bay Western Australia In Logan B W Davies G R Read J F amp Cebulski D E editors Carbonate sedimentation and environments Shark Bay Western Australia American Association of Petroleum Geologists Memoir 13 169 205 Friedman G M amp Sanders J E 1974 Principles of sedimentology Wiley New York Jago J B Dyson LA amp Gatehouse C G 1994 The nature of the sequence boundary between the Normanville and Kanmantoo Groups on Fleurieu Peninsula South Australia Australian Journal of Earth Sciences 41 445 453 Lenz S L Ryburn R J amp Kucka M 1993 Users guide to AGSO s Oracle databa
3. Check this one character field with an X to save the current reference details for duplication When entering a series of articles from the same source you may then copy the saved details with the DUPLICATE RECORD function key with the cursor in the Authors block Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 15 9 SINGLE AUTHOR QUERY FORM YT220 Terminal to AVIION File Edit Iransmit VT FuncKeys YT ShiftFuncKeys Setup Help AGSOREFS AGSO S SHARED BIBLIOGRAPHIC DATABASE SINGLE AUTHOR QUERY FORM ii Author Seq Ref ID A1t ID Year Title aurie J R 11689 foozren Hives ine canorin analysis and class Laurie J R 1 47H MLG2788 H1985 The Cambrian System in Austral Laurie J R 18 LOG654 H1086 PBLithostratigraphy and biostrati Segr Reference Details Bassett M G Year Entered B Ref Id Holmer L E SLAURLE Title Phylogenetic analysis and classification of the Brachio poda reply and comments You MUST query the database before adding Ejairik a new reference To add a new reference move to the Ref Detail block and then press Insert Record Vol Parteg Pagesgi ieii Pick list available Enter a search clue e g Gerono then press LIST Count 5 vu lt List gt lt Replace gt Figure 6 The Single Author Query form This form was designed for fast single author queries of the entire AGSOREFS database and for adding references already in the database to you
4. REM THE MAIN REFERENCES TABLE CREATE TABLE REFERENCES REFID VARCHAR2 9 NOT NULL PRIMARY KEY OTHERID VARCHAR2 16 YEAR NUMBER 4 0 TITLE VARCHAR2 1024 SOURCE VARCHAR2 1024 NOT NULL VOLPART VARCHAR2 36 PAGES VARCHAR2 36 ENTEREDBY VARCHAR2 8 ENTRYDATE DATE GRANT SELECT ON REFERENCES TO PUBLIC CREATE UNIQUE INDEX REFNOS ON REFERENCES REFID CREATE INDEX REFOTHER ON REFERENCES OTHERID CREATE INDEX REFUSER ON REFERENCES ENTEREDBY CREATE VIEW UREFERENCES AS SELECT FROM REFERENCES WHERE ENTEREDBY USER GRANT SELECT INSERT UPDATE ON UREFERENCES TO PUBLIC REM VIEW LINKING GEODX REFERENCES TO THE GEOREF REFERENCES TABLE CREATE VIEW AGSOREFS AS SELECT REFID OTHERID ENTEREDBY ENTRYDATE YEAR TITLE SOURCE VOLPART PAGES FROM GEOREF REFERENCES UNION SELECT CD ARTICLE GEODX Reference GEODX DT PROCESS DT PUBLICATION TX_TITLE NAME PUBLICATION NO VOLPART SUBSTR NAME PAGE 1 36 FROM GEODX ARTICLE GEODX PUBLICATION GEODX SOURCE WHERE PUBLICATION ID PUBLICATION SOURCE ID PUBLICATION AND SOURCE ID SOURCE ARTICLE ID SOURCE GRANT SELECT ON AGSOREFS TO PUBLIC Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 28 REM THE MAIN AUTHORS TABLE CREATE TABLE AUTHORS REFID VARCHAR2 9 NOT NULL AUTHOR VARCHAR2 60 NOT NULL SEQUENCE NUMBER 2 0 NOT NULL ENTEREDBY VARCHAR2 8 NOT NULL ENTRYDATE DATE NOT
5. Authors and Reference Details blocks With the cursor in the top block press INSERT RECORD and optionally enter a user supplied ID for the displayed reference Commit the record with ACCEPT The other fields are automatically added to the REFUSERS table A new reference can also be inserted into AGSOREFS after querying the database by placing the cursor in the Refs block use PREVIOUS BLOCK if in the Refusers top block and pressing INSERT RECORD This engages the Reference Insert Update form in the same way as the Single Author Query form see Section 9 Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 19 REFUSERS BLOCK This block which corresponds to to the UREFUSERS view of the REFUSERS table contains your personal list of Reference IDs with their Alternate IDs You are free to insert update and delete all ownership records displayed in this block as they belong to you and nobody else The Entered By and Entry Date are automatically inserted by a pre insert trigger in the form Reference ID REFID A mandatory field of up to 9 characters for the system reference ID Those from the AUTHORS table are an asterisk plus integer e g 11510 while those from GEODX have a variety of formats but never preceded by an asterisk and typically a year followed by a slash and a number e g 89 26413 The Ref ID must point to an existing reference in the AGSOREFS
6. FROM GEOREF REFERENCES UNION SELECT CD_ARTICLE GEODX DT_PROCESS TO_CHAR DT_PUBLICATION TX_TITLE NAME PUBLICATION NO_VOLPART SUBSTR NAME_PAGE 1 36 FROM GEODX ARTICLE GEODX PUBLICATION GHEODX SOURCE WHERE PUBLICATION ID_ PUBLICATION SOURCE ID PUBLICATION AND SOURCE ID_SOURCE ARTICLE ID_SOURCE Note the use of the union operator in each view and the need to join several GEODX tables to match the equivalent AGSOREFS tables The names of the columns in these views are the same as those in the first half of each union i e those belonging to the REFERENCES and AUTHORS tables in AGSOREFS These views may only be used to select references from the database not to insert delete or update them Three additional tables control the ownership of references and keywords REFUSERS establishes what references belong in the private list of each Oracle user and allows the user to attach their own IDs to any references including GEODX references KEYWORDS contains a list of all keywords and their owners The same keyword can occur only once for any given owner but more than once between different owners Keywords also have a parent pointer allowing a hierarchy of keywords KEYREFS contains a list of what keywords belong to what references and who owns the linkage All tables views and indexes in AGSOREFS are owned by GEOREF The complete list of tables and views is TABLE OR VIEW CONTENTS REFERENCES enterable table for ID t
7. Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 36 In this way the AGSOREFS database can be used as a sort of insertable pick list This procedure will eventually be applied to all AGSO Oracle databases needing links to AGSOREFS references AGSOREFS AGSO S SHARED BIBLIOGRAPHIC DATABASE SINGLE AUTHOR QUERY FORM thor Seq Ref ID AIt ID Year Title Page R U 1811117 gt Williams I S 2811117 X 1988 Age of the Barramundi Orogeny i gt Authors Segr Reference Details Page R W a Year Entered B Ref Id villians T s f 61_AUG_99ROCKCHEN Title Age of the Barramundi Orogeny in northern Australia by means of ion microprobe and convential U Pb zircon stud ies You MUST query the database before adding iit ee ere a new reference To add a new reference move to the Ref Detail block and then press Insert Record Vol Partiiiray Page sitis Pick list available Enter a search clue e g Gerono then press LIST Count 2 lt List gt lt Replace gt Figure 15 The AGSOREFS Single Author Query form displaying the reference corresponding to the OZCHRON Reference ID in Figure 14 In the OZROX database the same techniques now apply to the Biblio Ref field of the Sites form The ROCKREFS table has been eliminated from OZROX and its place is now taken by REF type records in the LITHDATA table For attaching references to rock samples the Rocks form now caters for bibliographic reference
8. M 303 1 Black L P 1 1986 Zircon xenocrysts from the Kambalda volcanics age constraints and dir Earth and Planetary Science Letters 76 299 311 2 1981 Excess 40Ar in metamorphic rocks from Broken Hill New South Wales Earth and Planetary Science Letters 55 123 149 The number appearing before both authors and reference remainders is an interim reference ID number that starts at 1 and goes as far as there are references in the file 303 in this file The second number before each author is the sequence number of each author 1 for senior authors The title and source fields can be as long as 255 characters Upload this file to the AViiON with Rapid Filer or some other ASCII file transfer program Use vi to split the file into two with all the authors in one file and the rest of the references in the other In the file containing the rest of the references it is necessary to append a to the Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 34 end of each physical record in order to get Oracle Loader to recognise the end of each field This can be done in vi with the command 1 s Now create control files to load the two files into TAUTHORS and TREFERENCES with Oracle Loader The control file used to load OZCHRON authors was as follows LOAD DATA INFILE AUTH1 OUT BADFILE REF1 BAD REPLACE INTO TABLE GEOREF TAUTHORS ENTEREDBY CON
9. command line environment reporting programs and nearly all screen forms Most ad hoc queries data inserts and updates are done via the screen forms although you should also know that batch retrievals and updates are often done in SQL Plus see Lenz et al 1993 Also Oracle Data Browser a Microsoft Windows client program is increasingly used to extract data from Oracle databases To run the Geological Databases menu first log into UNIX on the AViiON server and specify your terminal type see Lenz et al 1993 Then type geol lt ENTER gt This automatically puts you into the Oracle production environment and brings up the SQL Menu login screen After entering your Oracle username and password the first screen of the main menu is displayed This currently looks like this T220 Terminal to AViiON Bas Spey PRES File Edit Transmit YVT FuncKeys VT ShiftFuncKeys Setup Help AESO S GEOLOGICAL DATABASES MAIN MENU Field and Laboratory Databases 41 OZROX Field Database 2 Stratigraphic futhority Database 3 ROCKCHEM Database A OZCHROH Database 5 PETROG Database 6 OZMIN Database 7 STREANCHEM Database 9 Main Menu Screen 2 18 Exit Main Menu Enter your choice Fx Oracle Environment Production Application NEHA Menu N amp MA Figure 3 Screen 1 of the main menu to AGSO s Geological Databases This menu gives access to nearly all areas of AGSO s Geological Field and Laboratory Database system Sel
10. have actually entered not all the references in these tables This is because the form covers the UAUTHORS and UREFERENCES views of these tables which are defined as follows CREATE VIEW UAUTHORS AS SELECT FROM AUTHORS WHERE ENTEREDBY USER CREATE VIEW UREFERENCES AS SELECT FROM REFERENCES WHERE ENTEREDBY USER Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 12 This allows users to change a reference they have previously entered but not to the extent that the indentity of the reference is changed In other words you can correct spelling errors or improve the punctuation or format but you should not change Smith 1994 to Jones 1994 Other users may have pointers to this reference and you are not free to delete the reference or change it completely In fact you will find that you cannot to delete a reference using any form nor from SQL Plus You can only remove a reference from your personal list of references using the Personal Keyword Reference form AUTHORS BLOCK The Authors block corresponds to the AUTHORS table which has a record for each author in a reference The maximum number of authors per reference is 99 which is set by the 2 digit precision of the author sequence number The order in which the authors are displayed or printed is determined by this sequence number Use the TAB to move down the author list and into the Reference Details block when the bottom o
11. they have been entered but all other objects can be updated or deleted by their owners There are no secrets in AGSOREFS all objects can be seen but not necessarily changed Select Access All internal AGSO users have select access to all tables in AGSOREFS including all relevant GEODX tables When using the the SQL Plus language all users may select data from any of these tables provided they prefix the name of the table or view with the owner s name and a full stop e g SELECT REFID FROM GEOREF AUTHORS WHERE AUTHOR LIKE Jones SELECT CD_ARTICLE FROM GEODX ARTICLE_100K WHERE NO_100K_SHEET 7532 GEOREF being the owner of all AGSOREFS tables and GEODX of all GEODX tables Insert Update and Delete Access In AGSOREFS all internal Oracle users in the production environment can add or change their own data via specially named views Those relevant to AGSOREFS and their corresponding base tables are as follows BASE TABLE INSERT UPDATE VIEW AUTHORS UAUTHORS REFERENCES UREFERENCES REFUSERS UREFUSERS KEYREFS UKEYREFS KEYWORDS UKEYWORDS Note that records in the UREFERENCES view the essential part of each reference cannot be deleted However authors keywords keyword pointers and reference pointers can all be deleted by their owners Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 8 The restrictions applying to the above views are the same in each case For e
12. union view otherwise an error message will result Alternative ID ALTID Up to 16 characters for an optional alternative user supplied ID for the reference Users may have an existing reprint number or card numbering system they wish to use for this purpose Any combination of letters and numbers may be entered but the ID must be unique to the user Note that other users may attach their own Alternative IDs to the same references but you only get to see them in the General Keyword Reference Query form Oracle User USERID A mandatory 8 character field for the Oracle user ID of the person entering the ownership record into the REFUSERS table A value for this field is automatically supplied by a pre insert trigger This field can only be navigated in query mode Date Entered ENTRYDATE A mandatory date field in the standard Oracle format of DD MON YY for the date the record is entered into the REFUSERS table The correct date is automatically inserted into this field by a pre insert trigger This field can only be navigated in query mode KEYWORDS BLOCK The Keywords block which corresponds to the UKEYREFS view of the KEYREFS table displays only those keywords that belong to you If you want to use other people s keywords for querying purposes then you must use the General Keywords Reference Query form The records displayed in this block are yours to use as you like Keyword Number WORDNO A mandatory positive i
13. 14th February 1990 185 195 Keywords Papua New Guinea Sedimentation Carbonate Platforms Miocence Tectonics Oligocene Palaeoclimates Paleogeography Coral reefs Oil plays Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 25 15 KEYWORDS REPORT PROGRAM T220 Terminal to AViiON Edit Iransmit VT FuncKeys T ShiftFuncKeys Setup Help AGSOREFS DATABASE KEYWORDS REPORT PROGRAM SELECTION MENU Name of output file Select by Alphabetic Range Select by KEYWORD Other eg KEYWORDS ENTRYDATE gt 28 NOU 94 kwdrep 1lis between S and T2 Sorting clause ORDER BY UPPER REPLACE WORD Output in Rich Text File format Report on personal references only Yes R Co x gt Exit CHOOSE BY NUMBER OR LETTER Figure 12 The AGSOREFS Keywords Report menu Item 7 of the AGSOREFS menu is a keyword reporting program that lists the selected keywords to the screen or specified AViiON file As with output of references this can be in plain ASCT or rich text format Here is a sample of the keyword output from this program with the above selection criteria 12189 12190 12191 12192 12193 12194 12195 12196 12197 4378 4379 4380 4381 14368 4382 Australian Geological Survey Organisation 1995 saamica saaminica saba sabrinae sabulon sabulosa sabulosus sabus saccatum Sacciconus Sacciella Saccogonum Sacha sachaica S
14. AUSTRALIAN GEOLOGICAL SURVEY ORGANISATION GUIDE TO AGSOREFS AGSO S SHARED BIBLIOGRAPHIC REFERENCE DATABASE Record 1995 6 R J Ryburn amp L D Bond AUSTRALIAN GEOLOGICAL SURVEY ORGANISATION CANBERRA mu DEPARTMENT OF PRIMARY INDUSTRIES AND ENERGY Minister for Resources Hon David Beddall MP Secretary Greg Taylor AUSTRALIAN GEOLOGICAL SURVEY ORGANISATION Executive Director Harvey Jacka Commonwealth of Australia 1994 ISSN 1039 0073 ISBN 0 642 22322 X This work is copyright Apart from any fair dealings for the purposes of study research criticism or review as permitted under the Copyright Act 1968 no part may be reproduced by any process without written permission Copyright 1s the responsibility of the Executive Director Australian Geological Survey Organisation Requests and inquiries concerning reproduction and rights should be directed to the Principal Information Officer Australian Geological Survey Organisation GPO Box 378 Canberra City ACT 2601 CONTENTS ABSTRACT 1 10 11 12 13 14 15 16 17 INTRODUCTION AGSO s REFERENCE STANDARD REDUNDANT REFERENCES STRUCTURE OF AGSOREFS STRUCTURE OF GEODX SECURITY AND ACCESS MENU SYSTEM REFERENCE INSERT UPDATE FORM SINGLE AUTHOR QUERY FORM COMPLEX QUERY FORM PERSONAL KEYWORD REFERENCE FORM GENERAL KEYWORD REFERENCE QUERY FORM KEYWORDS ONLY FORM REFERENCES REPORT PROGRAM KEYWORDS REPORT PROGRAM AC
15. CLE MM L od_article cd_article no_im_sheet id_keyword no_250k_sheet SEQNOS tablename id_keyword name_keyword maxseqno nat pg Ma tate Me tate ARTICLE_GEOLPROV ARTICLE_100K cd_article A E o oublia i ublica cd_article nm_province Ee no_100k_sheet ARTICLE_PLACE cd_article nm_place id_locality Figure 2 Structure of the bibliographic part of GEODX The tables used by AGSOREFS are in grey The many end of many to one joins are indicated by crows feet The asterisks indicate primary key fields Users wishing to take advantage of these special GEODX features for querying purposes should call up the GEODX menu from the AViiON s UNIX prompt by typing geodx lt ENTER gt For a more complete description of GEODX see Lenz amp Modrak 1990 Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 7 6 SECURITY AND ACCESS Security and control of access are critical to the success of AGSOREFS as many users will contribute references to the database and all users select references from it Underpinning this is the principle of ownership nearly all objects in AGSOREFS are owned by someone Not only are all references owned many are owned by GEODX but so are the private lists of reference pointers alternative IDs keywords and the linkages between keywords and references References cannot be deleted once
16. E SAA heh ih LAE thE Adib ONA ENEE bi PPPS EDE dtd itd NOPNE D EEC ADINE ERT ANEAN POPS LN ANS ES ANTAS Ae Figure 13 Main menu of the RefForm reference reformatting program showing the settings for generating a file for loading into AGSOREFS Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 33 Note that the reference template shown in the lower part of the menu is AGSO TPL with the authors set to lower case this does the same as the SQL INITCAP function the commas removed from authors surnames and all text enhancements removed The input file is an ASCH file called REFS1 INP The output file is set to REFS1 OUT and the output file type is set to Relational Although not shown in the main menu the punctuation character marking the end of the title in input references has been set to in menu 2 the Input Options menu This ensures that the title and source will be correctly parsed in output If is not present in the input file set the title ending parameter to a full stop Press 8 to generate a RefForm output file The output file is in two parts with all authors in the first part and the rest of all references in the latter part The transition in the middle of the file looks like this 299 1 Kinny P D 299 2 Black L P 299 3 Sheraton J W 300 1 Jagodzinski E A 301 1 Kinny P D 301 2 Black L P 301 3 Sheraton J W 302 1 Lanyon R 302 2 Black L P 302 3 Seitz H
17. KNOWLEDGMENTS REFERENCES APPENDIX A AGSOREFS DATABASE DEFINITIONS APPENDIX B LOADING REFERENCES INTO AGSOREFS APPENDIX C REFERENCES IN OTHER AGSO DATABASES Australian Geological Survey Organisation 1995 iii 10 12 16 18 19 22 23 25 26 27 27 28 32 36 Users Guide to AGSO s Petrography Database ii ABSTRACT AGSOREFS is a shared bibliographic database designed to serve two main purposes First it is intended to serve as a single reference pool for all AGSO relational geoscience databases replacing a number of earlier reference systems Its standardised references can be displayed from any Oracle database via a unique reference ID Second it is designed to function as a pooled reference system for all AGSO staff members Users can select from it the references they want in their private lists and can attach their own IDs and keywords The menus screen forms and reports used to input and view references keywords etc are described in detail Definitions of most fields in the database are given in some depth under descriptions of the screen forms providing in effect a comprehensive data dictionary of the database The database schema with all definitions of tables views and indexes is listed in an appendix to the guide Instructions are given in a second appendix on how to parse and bulk load references from documents and other databases Australian Geological Survey Or
18. NULL GRANT SELECT ON AUTHORS TO PUBLIC CREATE INDEX AUTHREFIDS ON AUTHORS REFID CREATE INDEX AUTHAUTHORS ON AUTHORS AUTHOR CREATE INDEX AUTHUSERS ON AUTHORS ENTEREDBY CREATE VIEW UAUTHORS AS SELECT FROM AUTHORS WHERE ENTEREDBY USER GRANT SELECT INSERT UPDATE DELETE ON UAUTHORS TO PUBLIC REM VIEW LINKING GEODX AUTHORS TO THE GEOREF AUTHORS TABLE CREATE VIEW AGSOAUTHS AS SELECT REFID AUTHOR SEQUENCE ENTEREDBY FROM GEOREF AUTHORS UNION SELECT CD_ARTICLE NAME_AUTHOR ORDER_SEQUENCE GEODX FROM GEODX AUTHOR GEODX ARTICLE_AUTHOR WHERE AUTHOR ID_ AUTHOR ARTICLE_AUTHOR ID_AUTHOR GRANT SELECT ON AGSOAUTHS TO PUBLIC REM VIEW COMBINING ALL REFERENCE DATA INTO A SINGLE RECORD REM ONE RECORD TO ONE AUTHOR CREATE VIEW WHOLEREF AS SELECT AGSOREFS REFID AUTHOR YEAR FROM AGSOREFS AGSOAUTHS WHERE AGSOAUTHS REFID AGSOREFS REFID GRANT SELECT ON WHOLEREF TO PUBLIC Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 29 REM TABLE POINTING TO THE REFERENCES IN USERS PERSONAL DATABASES CREATE TABLE REFUSERS REFID VARCHAR2 9 NOT NULL ALTID VARCHAR2 16 USERID VARCHAR2 8 NOT NULL ENTRYDATE DATE NOT NULL GRANT SELECT ON REFUSERS TO PUBLIC CREATE INDEX REFUSERSREFID ON REFUSERS REFID CREATE INDEX REFUSERSUSERIDS ON REFUSERS USERID CREATE VIEW UREFUSERS AS SELECT FROM REFUSERS WHERE USERID USER GRANT SELECT IN
19. OZCHRON DATABASE U Pb SHRIMP FORM press NEXT BLOCK for Analytical Results ye Ha 23D LI age 2SD press PREVIOQUS BLOCK for Pooled Results Rec No Anal No EE Std Deuy Order No Ref ID 11117 Pb268 Pb286 Lab No Pb297 Pb206 Grain Noa Pb206 U238 Spot No Pb287 U235 U ppm Pb208 Th232 Th ppm allocated Th U Min 207 266 age Pb264 ppb Pb266 U238 age Pb266 Pb294 meas Pb267 U235 age F2 Pbh268 Th232 age Comments Press LIST for Bibliographic Reference Form Count 1 V Figure 14 The U Pb SHRIMP form in the OZCHRON database Note the message displayed when the cursor is in the Ref ID field You can now invoke the Single Author Query form by pressing the LIST function key whenever the cursor is in an AGSOREFS Reference ID field If a valid Reference ID is present it is passed to that form and the full reference is displayed as shown in Figure 15 Otherwise AGSOREFS may be queried and the correct Reference ID selected for returning to the calling form If the required reference cannot be found in AGSOREFS it may be entered via the Reference Insert Update form see Section 8 If you select or enter a new reference while in the AGSOREFS forms the following alert box appears on pressing EXIT and before the new Ref ID is automatically inserted into the calling form References Alert Do you wish te insert AGSOREFS Reference ID 11537 in the calling form Yes or No kgs Australian
20. SERT UPDATE DELETE ON UREFUSERS TO PUBLIC REM THE PERSONAL KEYWORDS TABLE CREATE TABLE KEYWORDS WORDNO NUMBER 5 NOT NULL PRIMARY KEY WORD VARCHAR2 40 NOT NULL PARENT NUMBER 5 OWNER VARCHAR2 8 NOT NULL ENTRYDATE DATE NOT NULL GRANT SELECT ON KEYWORDS TO PUBLIC CREATE UNIQUE INDEX KEYWORDNOS ON KEYWORDS WORDNO CREATE INDEX KEYWORDSWORDS ON KEYWORDS WORD CREATE INDEX KEYWORDOWNERS ON KEYWORDS OWNER CREATE VIEW UKEYWORDS AS SELECT FROM KEYWORDS WHERE OWNER USER GRANT SELECT INSERT UPDATE DELETE ON UKEYWORDS TO PUBLIC Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 30 REM THE TABLE LINKING KEYWORDS TO REFERENCES CREATE TABLE KEYREFS REFID VARCHAR2 9 NOT NULL WORDNO NUMBER 6 NOT NULL OWNER VARCHAR2 8 NOT NULL ENTRYDATE DATE NOT NULL GRANT SELECT ON KEYREFS TO PUBLIC CREATE INDEX KEYREFSREFIDS ON KEYREFS REFID CREATE INDEX KEYREFSWORDNOS ON KEYREFS WORDNO CREATE INDEX KEYREFSOWNERS ON KEYREFS OWNER CREATE VIEW UKEYREFS AS SELECT FROM KEYREFS WHERE OWNER USER GRANT SELECT INSERT UPDATE DELETE ON UKEYREFS TO PUBLIC REM THE TABLE THAT KEEPS TRACK OF SYSTEM NUMBERS CREATE TABLE MAXNOS IDMAXNO VARCHAR2 10 NOT NULL MAXNO NUMBER 6 NOT NULL GRANT SELECT UPDATE ON MAXNOS TO PUBLIC REM COPY OF THE REFERENCES TABLE FOR TEMPORARY LOADING USE ONLY CREATE TABLE TREFERENCES REFID
21. STANT ROCKCHEM REFID POSITION 1 4 INTEGER EXTERNAL SEQ POSITION 5 9 INTEGER EXTERNAL AUTHOR POSITION 10 80 CHAR And that used to load the rest of the OZCHRON references LOAD DATA INFILE REF1 OUT BADFILE REF1 BAD REPLACE RECLEN 500 CONCATENATE 8 INTO TABLE GEOREF TREFERENCES FIELDS TERMINATED BY TRAILING NULLCOLS ENTRYDATE CONSTANT 01 AUG 99 REFID INTEGER EXTERNAL YEAR CHAR TITLE CHAR SOURCE CHAR VOLPART CHAR ENTEREDBY CHAR PAGES CHAR OTHERID CHAR The reference IDs must now be adjusted via SQL update statements UPDATE TAUTHORS SET REFID SUBSTR TO_CHAR REFID 11054 1 9 UPDATE TREFERENCES SET REFID SUBSTR TO_CHAR REFID 11054 1 9 The references may now be copied into their final destinations as in the PALREFS example In this way references from almost any source can be added to AGSOREFS Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 35 APPENDIX C REFERENCES IN OTHER AGSO DATABASES A standard method has been introduced for handling AGSOREEFS references in the OZROX Ryburn et al 1993a PETROG Ryburn et al 1994 and OZCHRON Ryburn et al 1993b databases The following OZCHRON example shows the the U Pb SHRIMP form with the cursor positioned in the Ref ID field of the Analytical Results block T220 Terminal to AVON File Edit Transmit VT FuncKeys VT ShiftFuncKeys Setup Help
22. VARCHAR2 9 NOT NULL PRIMARY KEY OTHERID VARCHAR2 16 YEAR NUMBER 4 0 TITLE VARCHAR2 1024 SOURCE VARCHAR2 1024 NOT NULL VOLPART VARCHAR2 36 PAGES VARCHAR2 36 ENTEREDBY VARCHAR2 8 ENTRYDATE DATE GRANT SELECT INSERT UPDATE DELETE ON TREFERENCES TO PUBLIC REM COPY OF THE AUTHORS TABLE FOR TEMPORARY LOADING USE ONLY CREATE TABLE TAUTHORS REFID VARCHAR2 9 NOT NULL AUTHOR VARCHAR2 60 NOT NULL SEQUENCE NUMBER 2 0 NOT NULL ENTEREDBY VARCHAR2 8 NOT NULL ENTRYDATE DATE NOT NULL GRANT SELECT INSERT UPDATE DELETE ON TAUTHORS TO PUBLIC Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 31 APPENDIX B LOADING REFERENCES INTO AGSOREFS If AGSOREFS is to fulfill its purpose as a unified reference database serving all AGSO databases then all isolated reference tables currently attached to Oracle databases must be consolidated into AGSOREFS Already processed are the former PALREFS database and all ROCKCHEM and OZCHRON references but a number of other reference tables have yet to be transferred This appendix which is intended for developers rather than users outlines the methods used References can be transferred from almost any source For Oracle tables in which the authors of multi author articles occupy separate fields or records and the remainder of the references are properly parsed split into components then the problem is simply one of usi
23. achaspis 12223 12224 4393 12225 4394 4395 15379 16365 12226 227 2228 14323 12229 18070 12230 salairica 12253 salairicus 4407 Salanacus 16516 salancaniensis 12254 Salankanaspis 4408 Salanyella 4409 Salanygolina 12255 Salanytheca 14542 salax 4410 salebra 12256 salebrosa 15157 salebrosus 17568 salemensis 12257 salemi 12258 salinity toler sandoverensis Sandoveria sanduense sanduensis Sanduhedinaspis Sanduspis sanfangensis sanhuangshanensis Sanhuangshania sanhwaichangensis Sanjuanella sanjuanensis sanlucasensis santaquinensis Users Guide to the AGSOREFS Database 26 16 ACKNOWLEDGMENTS We are indebted to Mirek Kucka Database Administrator for the idea of creating a union between GEODX and the rest of AGSOREFS Thanks are also due to Sonja Lenz Cathy Brown George Chaproniere and John Laurie for their suggestions and help during development and testing of AGSOREFS Figure 2 was adapted from a diagram by Sonja Lenz This Record has benefitted greatly from reviews by Ian Hodgson John Laurie and Sonja Lenz 17 REFERENCES AGPS 1994 Style manual for authors editors and printers Fifth Edition Australian Government Publishing Service Canberra Ewers G R amp Ryburn R J 1993 User s guide to the OZMIN mineral deposits database Australian Geological Survey Organisation Record 1993 94 Kucka M 1994 AGSO s Oracle developers guide Australian Geological Survey Organisation Rec
24. are handy for finding the right issue in unbound sets which are increasingly encountered as a result of reduced library budgets Include them if you wish Page numbers are only given for serial articles not for monographs such as books or AGSO Records i e do not append 48p or 48pp The references in AGSOREFS are primarily intended as signposts and most users are not interested in how many pages a monograph has Similarly do not include the number of plates in a reference The new AGSO standard is not binding If you feel the need to deviate and you can convince the editors then go ahead The over riding philosophy should be that references are listed in bibliographies solely so that readers can obtain the corresponding source material from a library Therefore they need to contain only the minumum information needed to efficiently acheive this aim Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 3 3 REDUNDANT REFERENCES One of the issues that must be addressed in all large bibliographic databases is the problem of redundant references Even where the database is under the contro of a single custodian as in GEODX it is almost impossible to ensure that some references will not be repeated somewhere in the database In a multi custodian database like AGSOREFS the problem is much worse and we are forced to concede that redundant references will inevitably occur in the database as a wh
25. commit the reference to the database Be aware though that the slightest difference in punctuation or spelling in the titles will prevent this check performing as it should On the other hand if you find that the required reference is already in the database but you are dissatisfied with the content or way it has been entered you are then entitled to enter your preferred version This should not happen very often as AGSOREFS controls the style of references you can input via the database s screen forms Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 4 4 STRUCTURE OF AGSOREFS GEODX AUTHOR amp ARTICLE_AUTHOR GEODX ARTICLE SOURCE amp PUBLICATION Figure 1 The structure of AGSOREFS showing relationships between tables and views The crows feet mark the many end of many to one links GEODX tables not shown in detail see Fig 2 AGSOREFS has a simple structure with user entered references accommodated in just two tables REFERENCES and AUTHORS see Fig 1 above The bulk of the reference namely the reference ID title source volume plus part and page numbers belongs in the REFERENCES table The AUTHORS table accommodates one author per record along with the same reference ID and a sequence number for the author in multi author references This arrangement implies a many to one relationship between authors and references whereas the true relationshi
26. d with the help of another program from AutoRef Systems called DialogAR Database references must first be extracted from their source tables just as they would be for making up a published list of references of the author s year title source sort This can be done with an existing reporting program or just with a SQL select statement Punctuation is not critical but it helps to have a at the end of all titles and references must be separated by blank lines After extraction reference lists are transferred from the DG AViiON to a PC using LAN Workplace for DOS Rapid Filer or some such file transfer software and RefForm is then run to parse the references into their components RefForm s main menu should look similar to this when ready to process a reference list EEA I ANTES te ee oe ee REFFORM MAIN MENU EEA ATL ATE NAA AAA Ne le AA TEN ERE le NL EWE VE ENN WO LY fly eT a ae ia te Ee 1 Configuration Menu 4 Read Template File 7 Preview Output 2 Input Options Menu 5 Template Editor 8 Got Restyle amp Output 3 Output Options Menu 6 Uieu Input File Terninate Program INGS Input REFS1 INP File Type Output File Type Tpl File Delimiter Suapf ile Enhancnts Page Hun ae Printmarg 28 Page Len 3 STYLE TEMPLATE Gh b ber top or letter Chottam gt or ss ESE to exit PENNA AOE A tN SAAT LN TOE EAE PDPN NENA AND API EAEN NEDENES EL NS EI PIE PLS PIE NL NOEL DI PLS EG LEE EES PIM ahah Sh ADL EAEAEL EE DEAE ARER EEE
27. e are sometimes quite slow minutes to execute Author AUTHOR As in the Reference Insert Update form but for query only Sequence Number SEQUENCE As in the Reference Insert Update form but for query only Reference ID REFID A 9 character query only field for a system reference ID Those coming from the AUTHORS table consist of an asterisk plus an integer e g 11510 while those from GEODX have a variety of formats but are never preceded by an asterisk and are typically composed of a year followed by a slash and a number e g 89 26413 Alternative ID ALTID in the REFUSERS table This optional field is for a user supplied reference ID of up to 16 characters You may enter and commit your own reference IDs using this field and the appropriate record will automatically be inserted into your private list in the REFUSERS table Different users can have different Alternative Ds attached to the same reference Year YEAR in the AGSOREFS view As described in the Reference Insert Update form but for query only Title TITLE in the AGSOREFS view As described in the Reference Insert Update form but for query only Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 17 10 COMPLEX QUERY FORM AGSOREFS Database Complex Query Form Authors Chaproniere Pigram vear Ref Id Other Id Uolume part Pages Press EXECUTE QUERY to retrieve reference
28. ecting item ZO in the menu or pressing the EXIT function key takes you back to the AVION server s UNIX prompt Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 10 To engage the AGSOREFS database menu just enter 8 or move the highlight bar down to item 8 then press ENTER The following screen appears a T220 Terminal to AVION File Edit Transmit YVTJ FuncKeys VT ShiftFuncKeys Setup AGSOREFS AGSO s Shared Bibliographic Database 2 Personal Keyword Reference Fora 3 General Keyword Reference Query Forr 4 Reference Insert Update Form 5 Keywords Only Fora 6 Reference Report f Keywords Report 8 SQL2Plus 9 Exit Submenu Enter your choice Oracle Enviranment Production Application NGMA Menu AGSOREFS Figure 4 The AGSOREFS menu This menu gives you access to all forms and report programs belonging to the AGSOREFS database However if item 4 is displayed in a different colour from the rest of the menu as shown in Figure 4 then you cannot access the Reference Insert Update form directly from the menu You must first query the database via the forms at items Z or 2 of the menu before you can add a new reference see Sections 8 9 amp 11 Item Z is the Single Author Query form for rapid querying of AGSOREFS by single authors and also for adding existing references to your private list Use the Personal Keyword Reference form at item 2 for adding references and k
29. eywords to your private view Item 8 takes you to the SQL Plus command line environment from where you return by entering EXIT Selecting item 9 or using the PREVIOUS MENU function returns you to the main menu The AGSOREFS screen forms and reports accessible from the above menu are described below Formal definitions of all AGSOREFS tables views and indexes are listed in Appendix A while procedures for loading references into AGSOREFS from pre existing lists and databases are detailed in Appendix B The new bibliographic reference arrange ments for the OZROX field geology database Formerly the NGMA field database Ryburn et al 1993a and the PETROG Petrography Database Ryburn et al 1994 are covered in Appendix C If users find the current AGSOREFS screen forms unfriendly then please bear with us The current character based interface will soon be replaced with a graphical windows based interface Users should then find the database more intuitive and easier to use Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 11 8 REFERENCE INSERT UPDATE FORM T220 Terminal to AVIION File Edit Iransmit VT FuncKeys YVT ShiftFuncKeys Setup Ir AGSOREFS AGSO S REFERENCE DATABASE REFERENCE INSERTZUPDATE FORM Authors Block Sequence No Bassett M G Note that this form Holmer can retrieve only the Popov L E references that you Laurie J R yourself have previ ousiy entered
30. f the list has been reached Authors AUTHOR A mandatory field of up to 60 characters for a single author with the surname first in lower case except for the first letter followed by a space a comma and the initials with full stops for example Chowmondlier K L Most surnames have only the first letter in upper case but there are exceptions e g de Luca MacDonald and Smythe Higgins Also some surnames consist of more than one word e g Van Der Wall Do not attempt to insert more than one author in a single record In a few AGSOREFS references this may occur in the last author record of references that have originated from pre existing databases This situation should be corrected when found Sequence Number SEQUENCE A mandatory positive integer of up to two digits indicating the order of the author in the reference s author list This field is automatically inserted by a form trigger during the entry of authors For each reference the sequence number starts at one and it advances by one for each additional author in the reference The field can be updated so that the order of the authors in a reference s author list can be changed Note that the author sequence number is needed because relational databases do not generally preserve the order in which the records were originally entered into a table REFERENCE DETAILS BLOCK The Reference Details block relates mainly to the REFERENCES table
31. ganisation 1995 Users Guide to AGSO s Petrography Database ili 1 INTRODUCTION Bibliographic references are an important adjunct to most geoscientific databases They are a signpost to further information on the quality and provenance of the data Owing to difficulties in maintaining a unified reference database notably the absence of a suitable custodian most AGSO Oracle databases were previously set up with their own reference tables This resulted in a number of systems with various designs and differing standards The largest of these is the bibliographic component of GEODX Lenz amp Modrak 1990 the National Stratigraphic Index database which has about 25 000 references on Australian Stratigraphy PALREFS was another substantial bibliographic database used by some AGSO palaeontologists Our experience in shared relational databases is now such that we can develop a single multi custodian database that satisfies most of our clients needs The result is AGSOREFS which draws together a number of previously isolated reference databases We hope to consolidate all such reference databases into AGSOREFS Initial reactions to the problem of references in AGSO s geoscience databases have been Why not use GEODX or Why not use AESIS the large reference database run by the Australian Mineral Foundation AMF The principal objection to these seemingly reasonable suggestions is that these databases have specific topics and their c
32. he name of the parent keyword into this field and a validation trigger ensures that the name already exists in the table A list of keywords can be obtained with the LIST function preferably after entering a search clue into the field ENTRYDATE A mandatory date field cast in the standard Oracle format of DD MON YY for the date the keyword is entered into the KEYWORDS table The correct date is automatically inserted into this field by a pre insert trigger This field can only be navigated in query mode OWNER A mandatory 8 character field for the Oracle user ID of the person entering the keyword into the KEYWORDS table This field defaults to the current user s Oracle name It is automatically inserted by a pre insert trigger when a record is committed The field can be navigated only in query mode Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 24 14 REFERENCES REPORT PROGRAM T220 Terminal to AYIION Edit Transmit YT FuncKeys YVT ShiftFuncKeys Setup Help AGSOREFS DATABASE REPORT PROGRAM SELECTION MENU Name of output file refrep 1is Select Reference ID Select Select REFUSERS pointer owner JLAURIE Select SOURCE Select Select Volume Part Select Both Chaproniere Pigrams Select Author SEQUENCE No Select Other eg AGSOAUVTHS SEQUENCE 1 Sorting clause ORDER BY AGSOREFS REFID Output keywords Output in RTF WinWord format Yes Output personal
33. is not cancelled by a second enhancement character it will affect the rest of the title The most common requirement for text enhancement in geoscience titles is the italicisation of taxonomic fossil names Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 14 Source SOURCE A mandatory field of up to 1024 characters for the source of the reference e g Australian Geological Survey Organisation Record which is often a journal name For monograph book references this field is for the publisher e g Elsevier Amsterdam In symposium style references the name s of the editor s often preceeds the symposium name e g In Smith S S Editor Conference on Note that italics or underlining bold etc can only be represented in the title field Volume or Part VOLPART An optional field of up to 36 characters for the volume Pages issue or part of a serial reference If both the volume and issue numbers are included then the issue number should be placed in brackets after the volume number e g 35 3 Special volumes may have an aphanumeric volume ID e g Sam Carey Special Volume hence the length of the field PAGES An optional field of up to 36 characters for the page range of articles in serials e g 47 54 For monographs the total number of pages e g 39pp is not required by the AGSO standard Save Details non database field
34. is usually to enter N and re query with more specific selection criteria Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 18 11 PERSONAL KEYWORD REFERENCE FORM T220 Terminal to AViiON File Edit Transmit YT FuncKeys T ShiftFuncKeys Setup Help AGSOREFS AGSO S SHARED REFERENCE DATABASE PERSONAL KEYWORD REFERENCE FORM 4 O71 L92544 sa Alt racle Ay User Supplied Ref ID Kurd No Ke vori cee Y To query by 0 multiple key R words move D to Keyword Lower Cambrian S field press Middle Cambrian EnterQuery The Count 1 lt Replace gt Figure 8 The Personal Keyword Reference form This form allows you to manage your own references and keywords without having to worry about other people s data You must use this form if you wish to attach keywords to references You can query the entire database by using either of the single character boxes at the top left of the form just enter X followed by ENTER The Simple box engages the single author query form described in Section 9 and the Complex box gets you the complex query form described in Section 10 On completion of a simple or complex query the above form is reinstated with the selected reference in view If you wish to add an existing reference to your private list you must first query the database by either of the two methods described above so that the required reference is displayed in the
35. itle source volume etc AUTHORS enterable table for ID authors and their sequence AGSOREFS union view of REFERENCES with GEODX tables AGSOAUTHS union view of AUTHORS with GEODX tables REFUSERS Oracle users versus reference IDs and private IDs KEYWORDS keywords parent pointers and owners KEYREFS reference IDs versus keywords versus owners MAXNOS table for tracking current max reference ID TREFERENCES temporary loading table identical to REFERENCES TAUTHORS temporary loading table identical to AUTHORS The full database shema with all database definitions is listed in Appendix A Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 6 5 STRUCTURE OF GEODX The bibliographic part of GEODX consists of a number of tables Fig 2 GEODX tables used by the AGSOAUTHS and AGSOREFS union views in the AGSOREFS database include AUTHOR ARTICLE_AUTHOR ARTICLE SOURCE and PUBLICATION GEODX also contains tables that allow references to be linked to GEODX s own keywords KEYWORD KEYWORD_ARTICLE geological provinces ARTICLE_GEOLPROV place names ARTICLE_ PLACE states of Australia ARTICLE_STATE STATE 1 250 000 map sheet ARTICLE_250K and 1 100 000 map sheet ARTICLE_100K Not shown here are the parts of GEODX that link references to stratigraphic names STATE ri nmstate R ARTICLE_STATE or oda ettel iq cd article Te Ae ee source j nm_state proces J ARTICLE_250K KEYWORD_ARTI
36. n addition all users can set up their own private keywords It would be nice to be able to include AMF s AESIS in the AGSOREFS union but the technology has not yet advanced to the stage where this can be done easily not to mention commercial and administrative problems The performance alone of such a union would be unacceptable at present Nevertheless this is something that should be kept in mind for the future as it may become feasible in a few years This guide describes the infrastructure of the AGSOREFS database including all screen forms and definitions of all fields and tables For users information on the AViiON database server AGSO s Oracle environments and the use of SQL Plus SQL Forms ete see the Users Guide to AGSO s Oracle Database System Lenz et al 1993 Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database l 2 AGSO s REFERENCE STANDARD A reference bibliographic reference or end reference is the full author year title source quotation normally found in a reference list or bibliography at the end of a document AGSO referencing has long followed the Harvard system which was first promoted in the University of Chicago Style Manual and is now widely followed in the earth and life sciences This system is also advocated in the Australian Government Publishing Service Style Manual AGPS 1994 It uses author year citations embedded in the body of the text
37. ng SQL insert statements to transfer the references via temporary tables The Production Environment tables TAUTHORS and TREFERENCES are provided for this purpose but first make sure they are empty before using them In this way you can ensure that all authors have been given their correct sequence numbers and reference IDs and that the same Reference Ds are inserted in TREFERENCES The reference IDs linking AUTHORS and REFERENCES are monotonically increasing integers prefixed by an asterisk e g 11372 The following example of an insert statement was used to copy all first authors AUTHOR1 from PALREFS into TAUTHORS INSERT INTO TAUTHORS REFID AUTHOR SEQUENCE ENTEREDBY ENTRYDATE SELECT SUBSTR TO_CHAR REFNUM 11250 1 9 INITCAP AUTHOR1 1 SUBSTR OWNER 1 8 SYSDATE FROM PALREFS PALREFS WHERE NOT AUTHOR1 IS NULL Similar statements were used to transfer the 2nd 3rd 4th and 5th authors from PALREFS Note the use of INITCAP function to reduce the upper case PALREFS authors to lower case with leading capitals When allocating reference IDs starting at 11251 in the examples given be sure to leave a gap of 10 between the current highest ID in the REFERENCES table and the start of the reference IDs in TREFERENCES This allows for other AGSO users who may be adding references between the time you check the highest reference ID and the time you actually load the authors and references The following SQL sta
38. nteger field of up to 5 digits for the number of the keyword in the KEYWORDS table The keyword itself is automatically displayed in the next field by a post change trigger Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 20 Keyword WORD in the KEYWORDS table This 64 character derived field is for the name of the keyword identified by number in the previous field You may enter one of your existing keywords into this field and the number is automatically inserted into the previous field Alternatively you can enter a search clue like strat without the wild card and press the LIST function key You may then select the required word from the resulting pop up list of keywords starting with strat If you enter a word not in the list of your words in the KEYWORDS table e g project management you will be prompted by the following pop up alert box References lert project management not found in your personal GEOREF KEY list Do you want to add it to the table now Yes or No ME Answer YES if you wish to add the word to the KEYWORDS table Be careful though that you are not just entering a spelling variant of an existing keyword It is better to back out and check rather than say yes blindly Entered ENTRYDATE A mandatory date field in the standard Oracle format of DD MON YY for the date the record is entered into the KEYREFS table The correc
39. ole Since AGSOREFS has been built by bringing together a number of previously separate databases the initial incidence of redundant references is quite high and we do not have the time or resources to cull all redundant references In AGSOREFS the redundancy problem is sidestepped by throwing it back to individual clients to police their own reference lists Personal users access the database via a list of pointers that determine what references belong in their private list and it is entirely in the hands of the user whether duplicates occur in their private view Similarly geoscience databases like OZMIN Ewers amp Ryburn 1993 have pointers to references in AGSOREFS and it is up to the custodians of these databases to maintain their own lists Provided the redundancy problem does not get out of hand it does not matter if several versions of the same reference appear when the entire database is queried The user merely selects one preferably the GEODX one to add to their private list Therefore redundant references do not normally appear in printouts that use these private lists of reference pointers Nevertheless AGSOREFS has mechanisms to reduce the incidence of redundant references The first line of defence is that in general you must query the database before a new reference can be added In addition when you try to insert a new reference if AGSOREFS detects other instances of the same title and year it will alert you before you can
40. ord 1994 4 Lenz S L Ryburn R J amp Kucka M 1993 Users Guide to AGSO s Oracle database system Australian Geological Survey Organisation Record 1993 81 Lenz S amp Modrak K 1990 The Stratigraphic Index database GEODX user manual Bureau of Mineral Resources Australia Record 1990 16 Peters T J amp Waterman R H Jr 1984 In search of excellence lessons from America s best run companies Harper amp Rowe Sydney Ryburn R J 1990 Users guide to the PetChem database Bureau of Mineral Resources Australia Record 1990 19 Ryburn R J Blewett R S Stuart Smith P G amp Williams P R 1993a Users guide to the NGMA field database Australian Geological Survey Organisation Record 1993 49 Ryburn R J Page R W amp Richards J R 1993b Users guide to the OZCHRON database of Australian geochronology Australian Geological Survey Organisation Record 1993 11 Ryburn R J Knutson J Duggan M B Bond L D amp Richards J R 1994 Users guide to PETROG AGSO s petrography database Australian Geological Survey Organisation Record 1994 36 Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 21 APPENDIX A AGSOREFS DATABASE DEFINITIONS kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk SCHEMA FOR THE AGSO CORPORATE REFERENCE DATABASE SYSTEM kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkikkkkkkkkkkkkkkkkkkkkkkkkk
41. p is many to many as modelled by GEODX In AGSOREFS though we are interested in authors only as an aid to reference identification and the many to one compromise offers the advantages of simplicity and superior performance In the many to many model the users must select each author from a validation table of authors If the required author is not present the user must detour to enter the author into the validation table and most users find this procedure awkward In the many to one situation the user just enters the author s name and initials The same author can occur many times in the authors table associated with different reference IDs The downside of this system is that it is more prone to spelling mistakes On the other hand the same author is often quoted differently in different references eg Ryburn R and Rybur R J Overlaying these two tables are two views that join AGSOREFS to GEODX The state ments used to create these views are good illustrations of the power of the SQL language Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 5 CREATE VIEW AGSOAUTHS AS SELECT REFID AUTHOR SEQUENCE ENTEREDBY FROM GEOREF AUTHORS UNION SELECT CD_ARTICLE NAME_AUTHOR ORDER_SEQUENCE GEODX FROM GEHODX AUTHOR GEODX ARTICLE_AUTHOR WHERE AUTHOR ID_AUTHOR ARTICLE AUTHOR ID_AUTHOR CREATE VIEW AGSOREFS AS SELECT REFID ENTEREDBY ENTRYDATE YEAR TITLE SOURCE VOLPART PAGES
42. r personal list With this form you can query the database by Author Sequence Reference ID Alternative user supplied ID Year and Title as well as via the Complex Query form see Section 10 After querying the database you may then insert a new reference via the Reference Insert Update form see Section 8 which you invoke by moving the cursor into the Reference Details block and pressing INSERT RECORD You may obtain the Complex Query form for multi author queries by pressing ENTER QUERY with the cursor in the same block The form has three blocks of which the Authors and Reference Details blocks are similar to those in the Reference Insert Update form and need not be re described The top block in which most queries are performed has one line per reference and displays up to three references A query in the top block say by author will often bring up a number of references As the cursor is moved from record to record in the top block the full author list and other details of each reference are displayed in the bottom two blocks Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 16 TOP BLOCK The top block corresponds to the AGSOAUTHS view but includes fields derived from the REFUSERS table and the AGSOREFS view The derived fields are indicated by the gt symbol in front of them You may navigate to any top block field to compose a query but queries involving derived fields e g Titl
43. rdoredlichia Sarytcheviella Satpakella Saucrorthis 02 DEC 93 82 DEC 93 82 DEC 93 82 DEC 93 02 DEC 93 Sayramaspis Scabriscutellum Scalites lt lt lt lt lt lt lt lt lt lt lt lt lt lt lt lt lt lt lt Count 79 Figure 10 The Keywords Only form The Keywords Only form allows users to see all keywords and to insert update and delete their own keywords The form accesses both the KEYWORDS base table and the UKEYWORDS updateable view The inclusion of a parent pointer in the KEYWORDS table allows the user to set up a hierarchy of terms e g phylum class order family genus and species Although none of the other AGSOREFS forms yet take advantage of this feature SQL Plus has the syntax for hierarchical retrievals and the potential exists for future enhancements to the functionality of the keyword system When using this form to query the KEYWORDS table you will normally see just your own keywords This is because your Oracle username is automatically inserted into the By column whenever ENTER QUERY is pressed If you delete this field before pressing EXECUTE QUERY all the keywords in the KEYWORDS table can then be accessed not just yours You can delete a keyword only when all the reference links to it have been removed from the KEYREFS table using the Personal Keyword Reference form Attempting to do otherwise displays a message telling you how many reference
44. re by a pre insert trigger in this form A number of private user supplied IDs can be associated with the one reference The field is included in this form to simplify the entry procedure all relevant information can be entered from the one form Entered By ENTEREDBY A mandatory 8 character field for the Oracle user ID of the person entering the reference The value for this field is automatically supplied by a pre insert trigger This field can only be navigated in query mode Entry Date C ENTRYDATE A mandatory date field in the standard Oracle format of DD MON YY for the date the reference is entered into the database The correct date is automatically inserted into this field by a pre insert trigger This field can only be navigated in query mode Title TITLE An optional field of up to 1024 characters for the title of a reference In the instance of the symposium type of reference where a paper is one of many in a named and or edited collection of articles the name of the symposium or conference special volume etc should be placed in the Source field not the Title Text enhancements such as italics can be included in the titles of references printed by the reference report program see section 14 by bracketing the text to be enhanced with the following characters Enhancement Character Example Result in Reports Italic oe _Daonella_ Daonella Superscript S Fe 3 Fe Subscript HI210 H2O If an enhancement
45. references only No G Go xX Exit CHOOSE BY NUMBER OR LETTER G Figure 11 The AGSOREFS Reference Report menu Item 6 in the AGSOREFS menu is a reference reporting program that formats and retrieves references using a variety of retrieval criteria Output is directed to the specified UNIX file on the AViiON server in plain ASCII or rich text format Microsoft s RTF document interchange format The latter can be imported complete with fonts and text enhancements e g italics directly into a variety of word processors e g Microsoft s Word for Windows Use a PC file transfer program like Novell s Rapid Filer to transfer output files from your AViiON directory to your PC hard disk A sample of RTF output follows 10083 003401 Chaproniere G C H amp Pigram C J 1993 Miocene to Pleistocene foraminiferal biostratigraphy of dredge samples from the Marion Plateau offshore Queensland Australia AGSO Journal of Australian Geology amp Geophysics 14 1 19 Keywords Foraminiferida Planktic Foraminiferida Larger Foraminiferida Miocene Pliocene Pleistocene Marion Plateau Queensland Coral Sea Biostratigraphy 8120 003095 Pigram C J Davies P J Feary D A Symonds P A amp Chaproniere G C H 1990 Controls on the Tertiary carbonate platform evolution in the Papuan Basin new play concepts In Carmen G J amp Carmen Z Editors Proceedings of the First PNG Petroleum Convention Port Moresby 12
46. s WARNING about 22 references may be retrieved QUICKLY based on author queries make author queries as specific as possible Figure 7 The Complex Query form This pop up complex query form typically used for multi author queries can be called from all reference query forms by first placing the cursor in the Reference Details block and pressing ENTER QUERY After entering the query specifications into the above form and pressing EXECUTE QUERY the results are then displayed back in the calling form In the two keyword reference forms there is a special one character field into which an X can be entered to engage this form With this form multiple author queries are usually much faster but still slow than single author queries which can take up to two minutes to yield results In general the fewer the references retrieved the faster the results are obtained To perform a query enter the selection criteria into the form then press EXECUTE QUERY Check the senior author box with an X if you wish to specify the first author as the senior author The form employs two methods of executing the query fast and slow If more than about 22 references are retrieved via the fast method then the slow method is engaged after you answer Y to the following prompt References Alert The query criteria are too general Retrieval may take some time Do you want to preceed with this query Yes or No PE However the better course of action
47. s as shown in Fig 16 To attach a reference to a rock sample or lithology the Attribute REF and the Descriptor AR are entered into the Lithdata block of the Rocks form with an AGSOREFS Ref ID in the Description column A validation trigger prevents the entry of Ref IDs not already in AGSOREFS To view the corresponding reference just press the LEST function key with the cursor in the description field The reference is displayed in the AGSOREFS Single Author Query form see Figure 6 Press EXIT to return to the Rocks form Multiple references may be attached by inserting several REF records in the Lithdata block If you press LIST without a Ref ID in the description field the empty Single Author Query form is displayed Use it to query AGSOREFS before attaching a reference to a sample The same methods now apply to the Petrography form in the PETROG database the only difference being that REF records are placed in the third block of that form as shown in Figure 17 Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 37 T220 Terminal to AVIION File Edit Jransmit VTI FuncKeys VI ShiftFuncKeys Setup OZROX DATABASE ROCKS amp STRUCTURES READ ONLY FORM Entered PASA NLE Rockno FEDER Orig MEDRET Site ID AELE By ACRL Sample ID eeERERT Infrmi Name Ager ele Strat Ht m Drill Depth Upper m Depth Lowerim Qualifier Lithol jAss ae Rock Type airet gnei
48. s currently access the keyword Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 23 Keyword Number WORDNO A mandatory positive integer field of up to 5 digits for Keyword the number of the keyword the primary key to the KEYWORDS table When entering new keywords the field is automatically supplied by a pre insert trigger in the form WORD A mandatory field of up to 40 characters for the name of the keyword identified by number in the previous field The word or words a keyword can be composed of more than one word entered here must be unique to the user this 1s enforced by a validation trigger in the form The conventions you apply to your keywords are entirely up to you Parent Pointer PARENT An optional field for a positive integer of up to 5 digits pointing to the parent keyword number in the same table recursive pointer For example the species keyword sapiens would point to the number of the parent genus keyword Homo The Parent Pointer may only be set to a keyword already in the KEYWORDS table This pointer overlays the table with a hierarchical or tree structure Name Parent non database field An optional field of up to 32 characters for the name Entered By of the parent keyword This field is automatically filled from the parent pointer by a post change trigger It is not part of the base table or view A pointer can be inserted by entering t
49. se system Australian Geological Survey Organisation Record 1993 81 Ryburn R J 1973 Pomio Papua New Guinea 1 250 000 geological series map Sheet SB 56 6 Ist edition Bureau of Mineral Resources Australia Scott G H 1980 Globorotalia inflata lineage and G crassaformis from Blind River New Zealand recognition relations and use in uppermost Miocene Lower Pliocene biostratigraphy New Zealand Journal of Geology and Geophysics 23 665 677 Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 2 Note that italics are not used except for taxonomic names occurring within a title This removes a major source of confusion about italicisation of the title in monographs and the source in periodicals a convention which is difficult to automate particularly in the instance of monograph series Does one italicise the monograph title or the series title In general the volume number is not preceded by v or vol nor the page range by p or pp etc In straightforward journal references the numbers are clear enough on their own This has been the case for some time in AGSO reference lists Although the issue or number is not usually needed in addition to the volume there are some cases where page numbering starts at one in each new issue In this case the issue must be included in brackets after the volume e g Journal of Obfuscation 23 3 23 41 However issue numbers
50. ss Ding iaa T Rs ey Lith Desc UA a ame oe peo os Other Data Attribute Name Descriptor iption 64 chars REF Reference AR MAGSOREFS RefID ST Sample Type RC Wuhole rock chemi snr Name Az Ine Def Srf Rank gt Bi gt gt gt systen supplied aoe key Field can only be entered in quer no aaa LIST for Bibliographic Reference Form Count 2 lt Replace gt as cin re ae eas Figure 16 The Rocks form in the OZROX database illustrating the method used to attach a reference to a rock sample or lithology 1T220 Terminal to AVION File Edit Transmit VTi FuncKeys VTI ShiftFuncKeys Setup R Originator 0 Sample ID C Qualifier Rock Name Comments Attribute to cores of strongly zoned plagioclase average grainsize 1 2 mm across E bait hs Conents _ _ GR granular mostly about 2 4 mm but up to 6 GR granular mostly about 1 2 mm across GR Mgranular brown to blue green up to 6 mm lo GR granular euhedral to subhedral strongly zomy after feldspar L Press NEXT KEY to copy TS Block Rock Name to Rocks Block Description I Press LIST for Bibliographic Reference Forn Count 5 u lt Replace gt Figure 17 The Petrography form in the PETROG database with an AGSOREFS Ref ID shown in the Extendable Attributes 3rd block Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 38
51. t date is automatically inserted into this field by a pre insert trigger This field can be navigated only in query mode By OWNER A mandatory 8 character field for the Oracle user ID of the person entering the record into the KEYREFS table A value for this field is automatically supplied by a pre insert trigger This field can be navigated only in query mode Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 21 12 GENERAL KEYWORD REFERENCE QUERY FORM V T220 Terminal to AViIiON l File Edit Transmit YT FuncKeys YT ShiftFuncKeys Setup AGSOREFS AGSO S REFERENCE DATABASE GENERAL KEYWORD REFERENCE QUERY T Query x10828 L86386 JLAURIE 18 JUL 9 R Simple Ref Alt ID Oracle Date E Ci ID User User Entered F Complex Supplied it Ref ID Kurd No Keyword MGloborilus W To query by Globesochonetes G nultiple key Giobosoproductus 9 R words move Globoecassidulina canalisuturat D to Keyword Globocassidulina nipponica S field press Globocassidulina nipponica EnterQuery eae tseerakhiopody Otryada Chonetida Source Aaii aaao Eadie Ca ee AATA Uol amp Part fhe database must be queried before a ney reference can be added U lt List gt lt Replace gt Figure 9 The General Keyword Reference Query form Although similar in appearance to the previous form this form is designed to let you query the entire database with anyone s keywords not j
52. tement was used to insert the remainder of the references into TREFERENCES INSERT INTO TREFERENCES REFID YEAR TITLE SOURCE VOLPART ENTEREDBY ENTRYDATE SELECT SUBSTR TO_CHAR REFNUM 11250 1 9 YEAR TITLEL TITLE2 TITLE3 SUBSTR JOURNAL1 JOURNAL2 JOURNAL3 PUBLISHR 1 255 VOLUME SUBSTR OWNER 1 8 SYSDATE FROM PALREFS PALREFS When you are satisfied all references have been successfully inserted in the temporary tables with their correct reference IDs you may then transfer them to their final destinations in the Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 32 AUTHORS and REFERENCES tables using similar but simpler SQL insert statements The final task is to ensure the MAXNOS table is updated as follows UPDATE GEOREF MAXNOS SET MAXNO SELECT MAX SUBSTR REFID 2 8 FROM GEOREF REFERENCES WHERE IDMAXNO REFID References that are insufficiently broken down into their components are best processed by a PC reference reformatting program called RefForm from AutoRef Systems PO Box 4772 Kingston ACT 2604 The parsed output files from RefForm can then be loaded into AGSOREFS using Oracle Loader References from ROCKCHEM and OZCHRON were handled this way RefForm accepts as input files of references including ASCH files and files from most popular word processing programs Files downloaded from the DIALOG Information Retrieval Service can be also handle
53. ust your own You also get to see everyone s user supplied alternative reference IDs The form cannot be used to insert update or delete keywords or reference ownership records Instead of relating to the updateable views of the tables the top block of this form corresponds to the REFUSERS base table and the Keywords block to the KEYREFS base table In the above example Fig 9 you see the results of querying the database with a single keyword stub ENTER QUERY was pressed with the cursor in the Keyword Number field the query clue Globo was placed in the Keyword field and EXECUTE QUERY was pressed Keywords and reference ownership records belonging to both JLAURIE and GCHAPRON were retrieved As the cursor is moved down the Keywords block the corresponding reference ownership record is displayed in the top block The same keyword may occur many times under different ownership but once only in the private list of a given user Thus both GCHAPRON and JLAURIE have their own copies of the keyword Siberia In the above example GCHAPRON has at least two references attached to the keyword Globocassidulina nipponica Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 22 13 KEYWORDS ONLY FORM T220 Terminal to AViiON File Edit Transmit VI FuncKeys VI ShifiFuncKeys Setup AGSOREFS AGSO S SHARED REFERENCE DATABASE KEYWORDS FORM Keyword 32 Char Parent Entered Sardinia Sa
54. ustodians do not want references from outside their normal field even so GEODX is polluted by gold deposit references from the now defunct MINDEP database Furthermore these databases are controlled by custodians who abhor the idea of individual users entering and updating their own references whereas AGSO scientists regard a single custodian as an intolerable bottleneck Fortunately relational databases have a structure called a union that allows several databases or tables or views to be joined end to end to form a single seamless database To this we add views that restrict users to just the references they wish to see Thus AGSOREFS has been formed as a union between the existing tightly controlled and highly normalised GEODX database and several new tables of simpler structure that are accessible to AGSO users at large This gives the overall database simultaneous loose tight properties so favoured in organisational models Pieters amp Waterman 1984 p15 Any user is free to insert a new reference into the loose part of the database after first ascertaining that the reference is not already in AGSOREFS Alternatively if the desired reference is on Australian geology a user can request the GEODX custodian to add it to the tight part No reference can be deleted once it has been entered but individual users or geoscience databases have control over which references appear in their view of the database I
55. which uses the system supplied Reference ID as its primary key and link with the AUTHORS table The block also has a field for a User Supplied Reference ID from the REFUSERS table and a non database field for saving a reference for copying with DUPLICATE RECORD Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 13 Year YEAR An optional 4 digit integer for the year the reference was published normally just a 4 digit figure such as 1994 You cannot add letters to the end of the year if there is more than one reference by the same author s in the one year Although used in published lists of references this convention is not appropriate to a shared corporate reference system Reference ID REFID A 9 character mandatory field for the system supplied unique ID for each reference Primary key to the REFERENCES table This ID which is stored as a foreign key in the AUTHORS table consists of an asterisk followed by a monotonically increasing integer At the time of writing this ID has reached 11537 This field can only be accessed in query mode Alternative ID ALTID in the REFUSERS table An optional field of up to 16 characters for any ID the user may wish to attach to the reference a reprint number for example Note that this field is not part of the REFERENCES table it belongs in the user s private list of reference IDs contained in REFUSERS table and is automatically placed the
56. xample the view UREFUSERS of the REFUSERS table is defined as CREATE VIEW UREFUSERS AS SELECT FROM REFUSERS WHERE USERID USER The word USER in the above statement is an Oracle function that returns the current Oracle username Each of the above tables has a mandatory field called OWNER USERID or ENTEREDBY for the Oracle owner of the record or the Oracle username of the person entering the data usually one and the same thing This scheme guarantees that the users see only their own records in the insert update versions of the forms and only they or the data custodians can alter or delete them Users wishing to use SQL Plus to insert update or delete records in the above main tables or SQL Loader to load records from ASCII files must use the above views Database Owners Access Rights The user GEOREF has complete privileges on all tables and views that it owns in the AGSOREFS database as does the owner GEODX in the Stratigraphic Index database However once a database has been locked into the production environment all changes affecting the structure of tables and views must first be submitted by the owner to the database administrator via the change control directory see Kucka 1994 Australian Geological Survey Organisation 1995 Users Guide to the AGSOREFS Database 9 7 MENU SYSTEM Access to AGSOREFS is via a tree structured menu system covering the AGSO Geological Database System This includes SQL Plus
Download Pdf Manuals
Related Search