/* SQL DDL: Create an implementation of the model using the SQL Data Definition Language */ /* Note: Comments such as this may not be supported by your system and may have to be removed */ /* You may want to change some field types to improve the implementation on your DBMS. */ /* Data type 'National Character' etc. indicates Unicode text. /* Note: Tested with MS SQL Server 2000 and PostgreSQL. Originally this file attempted to use */ /* Standard SQL for both. However, a common denominator could not be found. A special PostgreSQL file */ /* named '...SQL.Postgres' instead of '...SQL.txt' is now created in addition to the current file. */ /* The current file aims at SQL-Server dialects (but uses SQL92 whereever possible). The following changes may have to be made: */ /* Standard SQL 92: replace the data type BIT with SQL92 BOOLEAN */ /* MS SQL Server 2000: replace the SQL92 constant 'current_timestamp' with 'GETDATE()' (= date and time) */ /* MS SQL Server 2000: remove comments around the block of 'exec' statements at end of file to enable creation of column descriptions */ /* Note: CREATE INDEX statements (other than primary, foreign, or unique key constraints) are missing at the moment; to be added later */ /* Generated on 28.Sep.2003 13:25 by Diversity Information Model Documenter 2.5 (G. Hagedorn) */ /*=== Table: ReferenceType ===*/ /* Literature reference subsystem: Reference type definitions. Which reference attributes are enabled and how they should be labeled? Currently only the usage of a type is defined here; the attribute labels are defined directly in the vba code. */ /* RefType: The ReferenceTitle type, as used by Reference Manager (tm) 9.0 in their default databases. Additional ref. types may be added if they occur in the import. */ /* Description: A description, explaining the scope and use of the reference type to the user. */ /* LabelSet: This number is used to retrieve the correct customization set in the forms. Do not change the numbering without updating the methods in the code! */ /* ShowInPicklist: Only reference types selected here are available in a pick list; other types may be entered if the user knows their type code. */ /* Notes: Internal notes regarding the use of a ReferenceTitle type. */ CREATE TABLE ReferenceType ( RefType NATIONAL CHARACTER VARYING(10) NOT NULL PRIMARY KEY, Description NATIONAL CHARACTER VARYING(255) NULL, LabelSet SMALLINT NOT NULL, ShowInPicklist BIT NOT NULL, Notes NATIONAL CHARACTER VARYING(255) NULL ); /*=== Table: ReferenceTitle ===*/ /* Literature reference subsystem: Main entity; compatible with Reference Manager™ v.9. */ /* RefType: Type of literature reference, determines which fields are available for data entry. The value must come from the pick list provided. //[RefMan 01: TY] */ /* RefID: Unique reference ID code for the reference record. The only characters allowed are '0'-'9' and 'A'-'Z' //[RefMan 02: ID] */ /* RefDescription: * A short text identifying the reference, usually authors, year, title. Example: 'Smith, J. & Nao, S. (1999). New Taxa.' System generated, but users may update it. //[RefMan: not applicable] */ /* Title: The main (primary) title. Use normal capitalization, omit a period ('.') at the end, and do not type a paragraph return (Enter) at the end of each line! //[RefMan 03: TI, T1, CT, BT only for BOOK & UNPB] */ /* DateYear: Year of the publication date (primary date). Only numbers are allowed and the year must be entered with 4 digits ('1998', not '98'). //[RefMan 05: Y1,PY pro parte] */ /* DateMonth: Optional: The month of the publication date. [Note: in DateYear/Month/Day the information printed on the book or journal are entered, even if this is not the true date!] //[RefMan 05: Y1,PY pro parte] */ /* DateDay: Optional: The day of the publication date. [Note continued: if the true date is relevant, e.g. for the purpose of nomenclatural priority, it can be entered under DateSecondary.] //[RefMan 05: Y1,PY pro parte] */ /* DateSuppl: Optional: A date supplement, like 'approx.', a season ('Summer'), a quarter ('1st Quarter'), or any other information regarding the publication date. //[RefMan 05: Y1,PY pro parte] */ /* SourceTitle: The book (secondary) title in cases where the reference is an article or chapter from a book. Use normal capitalization.//[RefMan 13: T2; BT for all types except BOOK & UNPB] */ /* SeriesTitle: The series title. Use normal capitalization, omit a period ('.') at the end, and do not type a paragraph return (Enter) at the end of each line! //[RefMan 23: T3] */ /* Periodical: Journal/periodical in which the article appeared. Linked to the Abbreviation attribute of ReferencePeriodical. //[RefMan 11: JF, JO, JA] */ /* Volume: The volume (for periodicals or journals, excluding the issue number), report number, etc. //[RefMan 12: VL pro parte, comp. Edition!] */ /* Issue: The issue, if any. Useful also to enter a special designation for a supplement, for example for 'xxx 45 (Suppl.)' enter volume = 45 and issue = 'Suppl.'. Do not put '()' around the issue number. //[RefMan 15: IS] */ /* Pages: The page, table, or figure numbers for the reference, e.g. '23-41', '341 pp.', or '20, 22-24, 32' (for non-consecutive pages). //[RefMan 09: SP + 10: EP] */ /* Publisher: The name of the publisher (publishing company or institution, including universities or scientific societies). //[RefMan 17: PB] */ /* PublPlace: The location where the item being referenced was published, such as a city and state. //[RefMan 16: CY, CP] */ /* Edition: Number of the edition of a book. Use only positive integer numbers. //[RefMan 12: VL pro parte, compare Volume!] */ /* DateYear2: Year of a secondary date, esp. the true publ. date where relevant for nomenclatural priority. Only numbers are allowed and the year must be entered with 4 digits ('1998', not '98'). //[RefMan 28: Y2 pro parte] */ /* DateMonth2: Optional: The month of a secondary date. //[RefMan 28: Y2 pro parte] */ /* DateDay2: Optional: The day of a secondary date. //[RefMan 28: Y2 pro parte] */ /* DateSuppl2: Optional: A date supplement a secondary date, like 'approx.', a season ('Summer'), a quarter ('1st Quarter'), or any other information regarding the secondary date. //[RefMan 28: Y2 pro parte] */ /* ISSN_ISBN: The 'International Standard Serial Number' or 'International Standard Book Number'. Optional information; use is recommended only for publications that are otherwise difficult to order. //[RefMan 26: SN] */ /* Miscellaneous1: Various reference type dependent information; e.g. the total number of volumes for books //[RefMan 29: M1] */ /* Miscellaneous2: Various reference type dependent information //[RefMan 30: M2] */ /* Miscellaneous3: Various reference type dependent information //[RefMan 31: M3] */ /* URL: URL address (http://www...) or local file (C:\graphic.gif; \\servername\Data\x.doc) //[RefMan 33: UR] */ /* SourceRefID: * Independent publication (e.g. an edited book) in which a dependent publication was published. Refers to the ID code of a reference already entered in this system. Provided as an alternative to ref. manager's denormalized storage! */ /* Language: * Language of the article/book, as ISO 2 letter codes. //[RefMan: not supported!] */ /* DuplicateCheck: * A system generated string (typically Au. 1-4/Yr./Jour./Vol./first page) that is assumed to be unique. Use 'DuplicateOverride' to override if two reference titles are falsely identified as duplicates. //[RefMan: not applicable] */ /* DuplicateOverride: * A number to manually override automatic duplicate check, enter a number 1-255 if the system claims that non-duplicate entries are duplicates. //[RefMan: not applicable] */ /* ReplaceWithRefID: * Instead of direct deletes (currently not available for security reasons), users here select which reference should be the valid one, into which all related information (keywords, markers) is merged. References are later deleted after a review process. */ /* Problem: * A problem that occurred during data editing within the application. Typically the entries here should later be deleted after help has been obtained. Do not enter scientific or bibliographic problems here; use Notes for such permanent problems! */ /* ProblemUpdatedBy: * Operator who entered the problem text. */ /* ProblemUpdatedWhen: * Date and time when problem was recorded. */ /* CitationText: * Full text of a citation that describes the current reference. Use if ref. is only known as a citation in the bibliography of another publication, or if imported from unstructure data source. Empty if reference is transcribed from original publication! */ /* CitationFrom: * Description of publication, Only known as citation from bibliography of another publication given here (as ID code or author/year description); Empty if transcribed from original publication! */ /* ImportedFrom: * If imported from a reference database (esp. a commercial one): The name of the database system or provider; otherwise empty. This information is important to prevent copyright violations! */ /* ImportedID: * If imported from a reference database (esp. a commercial one): The ID identifying the record in that database; otherwise empty. Requires ImportedFrom //[RefMan: not supported!; Internal management attribute] */ /* PlausibilityCheckedBy: * Person responsible for a first plausibility or consistency check. User and Date are automatically filled if 'Original check' performed directly. //[RefMan: not applicable. Internal mgmt.attribute] */ /* PlausibilityCheckedWhen: * Date and time when plausibility/consistency was checked (i.e. data entry rules and spelling errors checked, no comparison with original publication) //[RefMan: not applicable. Internal mgmt.attribute] */ /* OriginalComparedBy: * Name of user responsible for comparison of ReferenceTitle record with original publication //[RefMan: not applicable. Internal mgmt.attribute] */ /* OriginalComparedWhen: * Date and time when entry was compared with the original publication (important esp. when ReferenceTitle was entered from secondary ReferenceTitle list) //[RefMan: not applicable. Internal mgmt.attribute] */ /* Responsible: * Person responsible for entering this reference into the data collection. */ /* LogCreatedBy: * Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute] */ /* LogCreatedWhen: * Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute] */ CREATE TABLE ReferenceTitle ( RefType NATIONAL CHARACTER VARYING(10) NOT NULL DEFAULT 'JOUR', RefID NATIONAL CHARACTER VARYING(20) NOT NULL PRIMARY KEY, RefDescription NATIONAL CHARACTER VARYING(120) NULL, Title NATIONAL TEXT NULL, DateYear SMALLINT NULL, DateMonth TINYINT NULL, DateDay TINYINT NULL, DateSuppl NATIONAL CHARACTER VARYING(255) NULL, SourceTitle NATIONAL TEXT NULL, SeriesTitle NATIONAL CHARACTER VARYING(255) NULL, Periodical NATIONAL CHARACTER VARYING(255) NULL, Volume NATIONAL CHARACTER VARYING(255) NULL, Issue NATIONAL CHARACTER VARYING(255) NULL, Pages NATIONAL CHARACTER VARYING(255) NULL, Publisher NATIONAL CHARACTER VARYING(255) NULL, PublPlace NATIONAL CHARACTER VARYING(255) NULL, Edition SMALLINT NULL, DateYear2 SMALLINT NULL, DateMonth2 TINYINT NULL, DateDay2 TINYINT NULL, DateSuppl2 NATIONAL CHARACTER VARYING(255) NULL, ISSN_ISBN NATIONAL CHARACTER VARYING(18) NULL, Miscellaneous1 NATIONAL CHARACTER VARYING(255) NULL, Miscellaneous2 NATIONAL CHARACTER VARYING(255) NULL, Miscellaneous3 NATIONAL CHARACTER VARYING(255) NULL, URL NATIONAL TEXT NULL, SourceRefID NATIONAL CHARACTER VARYING(20) NULL, Language NATIONAL CHARACTER VARYING(20) NOT NULL DEFAULT '?', DuplicateCheck NATIONAL CHARACTER VARYING(255) NULL UNIQUE, DuplicateOverride TINYINT NOT NULL DEFAULT 0, ReplaceWithRefID NATIONAL CHARACTER VARYING(20) NULL, Problem NATIONAL TEXT NULL, ProblemUpdatedBy INTEGER NULL, ProblemUpdatedWhen DATETIME NULL, CitationText NATIONAL TEXT NULL, CitationFrom NATIONAL CHARACTER VARYING(255) NULL, ImportedFrom NATIONAL CHARACTER VARYING(50) NULL, ImportedID NATIONAL CHARACTER VARYING(50) NULL, PlausibilityCheckedBy INTEGER NULL, PlausibilityCheckedWhen DATETIME NULL, OriginalComparedBy INTEGER NULL, OriginalComparedWhen DATETIME NULL, Responsible INTEGER NOT NULL, LogCreatedBy INTEGER NOT NULL, LogCreatedWhen DATETIME NOT NULL DEFAULT current_timestamp ); /*=== Table: ReferenceAuthor ===*/ /* Literature reference subsystem: Reference authors, book editors, or series editors. */ /* Author: Author. Example: 'Miller, W. I., Jr.'. Format: Last name, a comma, the first initial with period and blank, an optional middle initial, and an optional comma plus suffix (Jr./Sr./III./MD etc.) //[RefMan 04: A1/AU, 14: A2/ED, 24: A3; no '*' allowed!] */ /* AuthorAddress: The address of the author, if available. Entered only in cases where it is of special relevance to one of the users of the database (or if imported from a database). //[RefMan 32: AD] */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* AuthorType: Three different author categories are supported: 1 = primary author, 2 = secondary author/editor, 3 = series editor. //[RefMan implicit] */ /* AuthorSequence: The sequence of authors of the article. (Default based on system date/time; counter attrib. wouldn't work with replication -> random sequence! Note: Au+RefID+Type is not necessarily unique. Two authors may have identical abbreviated names, e.g. spouses!). */ CREATE TABLE ReferenceAuthor ( Author NATIONAL CHARACTER VARYING(255) NOT NULL, AuthorAddress NATIONAL CHARACTER VARYING(255) NULL, RefID NATIONAL CHARACTER VARYING(20) NOT NULL, AuthorType TINYINT NOT NULL DEFAULT 1, AuthorSequence INTEGER NOT NULL DEFAULT CLng(99999*(Now()-37200)), PRIMARY KEY (RefID,AuthorType,AuthorSequence) ); /*=== Table: ReferenceAvailability ===*/ /* Literature reference subsystem: Availability and location of reference items in private or official filing system; e.g., book signatures or reprint article availability. Each responsible user may enter multiple filing codes. */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* AvailabilityID: Unique ID to identify the availability record. (Technical note: to improve reliability of database replication, the primary key is formed in combination with the RefID. Note that FilingCode may be Null.). */ /* FilingCode: Information about availability or location of a copy of the referenced publication: Filing code of reprint or book in private filing system, institutional catalogue code, signature, official call number, or shelf code in a library. //[RefMan 27: AV] */ /* ReprintStatus: Refers to filing system of responsible person. Reprint may be 'Not in file' (0), 'On request (card to author)' (1), 'On request (internal order/copy marker)' (2), 'On request (interlibrary loan)' (3), or 'In file' (4) //[RefMan 08: RP pro parte] */ /* RequestDate: Only if ReprintStatus = 'On request' (1/2): The date on which the reprint was requested. //[RefMan 08: RP pro parte] */ /* Responsible: The person responsible for the availability/filing code information, and to which the reprint status/request date refers. //[RefMan: not supported] */ /* LogCreatedBy: Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute] */ /* LogCreatedWhen: Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute] */ CREATE TABLE ReferenceAvailability ( RefID NATIONAL CHARACTER VARYING(20) NOT NULL, AvailabilityID INTEGER IDENTITY NOT NULL, FilingCode NATIONAL CHARACTER VARYING(255) NULL, ReprintStatus TINYINT NOT NULL DEFAULT 0, RequestDate DATETIME NULL, Responsible INTEGER NOT NULL, LogCreatedBy INTEGER NOT NULL, LogCreatedWhen DATETIME NOT NULL DEFAULT current_timestamp, PRIMARY KEY (RefID,AvailabilityID) ); /*=== Table: ReferenceCollection ===*/ /* Literature reference subsystem: Declares that a reference belongs to a virtual reference database called a 'reference collection'. For example, all references from a collaborative project may form a reference collection. */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* ReferenceCollection: Code to identify a reference as belonging to a specific reference collection (= a virtual reference database, e.g. for a collaborative project). */ /* Responsible: The person responsible for assigning the reference to a reference collection. //[RefMan: not supported] */ CREATE TABLE ReferenceCollection ( RefID NATIONAL CHARACTER VARYING(20) NOT NULL, ReferenceCollection NATIONAL CHARACTER VARYING(25) NOT NULL, Responsible INTEGER NOT NULL, PRIMARY KEY (ReferenceCollection,RefID) ); /*=== Table: ReferenceKeyword ===*/ /* Literature reference subsystem: Keywords (may include 'subject heading'-keywords and organism names). */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* Keyword: A keyword, esp. subject headings or private markers. A keyword may consist of multiple words (phrases, e.g. 'red herring'), which can then only be searched as a whole. //[RefMan 07: KW] */ /* Responsible: The person responsible for the assignment of this keyword to a reference. //[RefMan: not supported] */ /* KeywordType: (temporarily introduced to preserve information when importing data that distinguish between (k)eyword, (g)eography, and organism (n)ames). */ CREATE TABLE ReferenceKeyword ( RefID NATIONAL CHARACTER VARYING(20) NOT NULL, Keyword NATIONAL CHARACTER VARYING(255) NOT NULL, Responsible INTEGER NOT NULL, KeywordType NATIONAL CHARACTER VARYING(1) NULL, PRIMARY KEY (Keyword,RefID) ); /*=== Table: ReferenceAbstract ===*/ /* Literature reference subsystem: Public Abstracts. */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* Responsible: * The person responsible for the abstract information. Abstracts are copyright protected! Leave empty if the abstract was not written by you, but copied from the publication itself or a bibliographic database. //[RefMan: not supported] */ /* Language: * Language of the abstract, as ISO 2 letter codes. //[RefMan : not supported!] */ /* Abstract: An abstract containing a short summary of the content of the article or book //[RefMan 25: N2] */ /* Shareable: * Do you want to share your abstract with other users, i.e. can it be published on the web? //[RefMan : not supported!] */ /* LogCreatedBy: * Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute] */ /* LogCreatedWhen: * Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute] */ CREATE TABLE ReferenceAbstract ( RefID NATIONAL CHARACTER VARYING(20) NOT NULL, Responsible INTEGER NOT NULL, Language NATIONAL CHARACTER VARYING(2) NOT NULL DEFAULT '?', Abstract NATIONAL TEXT NOT NULL, Shareable BIT NOT NULL DEFAULT 0, LogCreatedBy INTEGER NOT NULL, LogCreatedWhen DATETIME NOT NULL DEFAULT current_timestamp, PRIMARY KEY (RefID,Responsible,Language) ); /*=== Table: ReferenceUserMarker ===*/ /* Literature reference subsystem: User specific markers, e.g. to mark up a reference for a publication. */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* Marker: * User specific marker, e.g. to mark up a reference for a specific project or for a publication. These markers will normally be visible only under the same Responsible login name. */ /* Responsible: * The person responsible for the assignment of this user marker to a reference. Normally each person sees only his or her own markers! */ CREATE TABLE ReferenceUserMarker ( RefID NATIONAL CHARACTER VARYING(20) NOT NULL, Marker NATIONAL CHARACTER VARYING(255) NOT NULL, Responsible INTEGER NOT NULL, PRIMARY KEY (RefID,Marker,Responsible) ); /*=== Table: ReferenceUserNote ===*/ /* Literature reference subsystem: User specific notes. Each user will only see the notes entered under the same responsible user name. */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* Responsible: * The person responsible for the Notes. //[RefMan: not supported] */ /* Notes: Internal notes regarding the reference title. Notes will normally be visible only under the same Responsible login name. //[RefMan 06: N1, AB] */ /* LogCreatedBy: * Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute] */ /* LogCreatedWhen: * Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute] */ CREATE TABLE ReferenceUserNote ( RefID NATIONAL CHARACTER VARYING(20) NOT NULL, Responsible INTEGER NOT NULL, Notes NATIONAL TEXT NOT NULL, LogCreatedBy INTEGER NOT NULL, LogCreatedWhen DATETIME NOT NULL DEFAULT current_timestamp, PRIMARY KEY (RefID,Responsible) ); /*=== Table: ReferenceUserDefined ===*/ /* Literature reference subsystem: User-defined fields originating form imported Reference Manager RIS files. These user-defined fields are currently not well supported in Diversity References. */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* Responsible: The person responsible for the user defined fields (usually who imported the RIS file). */ /* UserDef1: User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 18: U1] */ /* UserDef2: User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 19: U2] */ /* UserDef3: User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 20: U3] */ /* UserDef4: User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 21: U4] */ /* UserDef5: User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 22: U5] */ CREATE TABLE ReferenceUserDefined ( RefID NATIONAL CHARACTER VARYING(20) NOT NULL, Responsible INTEGER NOT NULL, UserDef1 NATIONAL TEXT NULL, UserDef2 NATIONAL TEXT NULL, UserDef3 NATIONAL TEXT NULL, UserDef4 NATIONAL TEXT NULL, UserDef5 NATIONAL TEXT NULL, PRIMARY KEY (RefID,Responsible) ); /*=== Table: ReferenceWordIdx1 ===*/ /* Literature reference subsystem: Implementation entity. The words of the primary Title and the Source title, excluding stop words ('the', 'and', etc.), are written to this index table to allow fast queries. Words > 50 char. are truncated. */ /* Word: Words > 50 character are truncated! */ CREATE TABLE ReferenceWordIdx1 ( Word NATIONAL CHARACTER VARYING(50) NOT NULL, RefID NATIONAL CHARACTER VARYING(20) NOT NULL, PRIMARY KEY (Word,RefID) ); /*=== Table: ReferenceWordIdx2 ===*/ /* Literature reference subsystem: Implementation entity. The words of the Abstracts, excluding stop words ('the', 'and', etc.), are written to this index table to allow fast queries. Words > 50 char. are truncated. */ /* Word: Words > 50 character are truncated! */ CREATE TABLE ReferenceWordIdx2 ( Word NATIONAL CHARACTER VARYING(50) NOT NULL, RefID NATIONAL CHARACTER VARYING(20) NOT NULL, PRIMARY KEY (Word,RefID) ); /*=== Table: ReferenceAnonymousNote ===*/ /* Literature reference subsystem: Anonymous notes added through the internet public interface. */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* Notes: Notes entered by an anonymous user over the internet. */ /* InternetAccessCode: A system generated code that can be used to enable the anonymous user to update the Note over the internet. */ /* LogCreatedBy: Name of person who entered the anyonmous notes (free text, not referring to the User management component like most other LogCreatedBy attributes!). */ /* LogCreatedWhen: Date and time when record was entered into this system. */ /* LogUpdatedWhen: Date and time when record was last updated. */ CREATE TABLE ReferenceAnonymousNote ( RefID NATIONAL CHARACTER VARYING(20) NOT NULL, Notes NATIONAL TEXT NOT NULL, InternetAccessCode NATIONAL CHARACTER VARYING(255) NULL, LogCreatedBy NATIONAL CHARACTER VARYING(50) NOT NULL, LogCreatedWhen DATETIME NOT NULL DEFAULT current_timestamp, LogUpdatedWhen DATETIME NULL, PRIMARY KEY (RefID,LogCreatedWhen) ); /*=== Table: ReferenceChanges ===*/ /* Literature reference subsystem: Changes of reference records are logged here (user, date, attributes affected). */ /* RefID: Refers to the ID code of the main ReferenceTitle table (= foreign key). */ /* Changes: Optionally a record of the attributes and data that were affected by the update //[RefMan: not applicable. Internal mgmt.attribute] */ /* LogUpdatedBy: * User who last updated the reference identified by RefID. This is the operator (or typist) name, which may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute] */ /* LogUpdatedWhen: * Date and time of last update of the reference. //[RefMan: not applicable. Internal mgmt.attribute] */ CREATE TABLE ReferenceChanges ( RefID NATIONAL CHARACTER VARYING(20) NOT NULL, Changes NATIONAL TEXT NOT NULL, LogUpdatedBy INTEGER NOT NULL, LogUpdatedWhen DATETIME NOT NULL, PRIMARY KEY (LogUpdatedWhen,RefID) ); /*=== Table: ReferencePeriodical ===*/ /* Literature reference subsystem: Journal/periodical titles. */ /* Abbreviation: Standardized abbreviation of periodical or journal. Use periods after the abbreviations //[RefMan 11: JA, JO] */ /* FullName: Full, non-abbreviated name of periodical or journal //[RefMan 11: JF, JO] */ /* Notes: Notes, remarks, or comments regarding the journal/periodical as a whole, incl. "continued as (new title)" or notes about local availability. */ /* ImportedFrom: If imported from another database: The name of the database system or provider; otherwise empty. */ /* Responsible: The person responsible for entering the information. */ /* LogCreatedBy: Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute] */ /* LogCreatedWhen: Date and time when record was first entered (typed or imported) into this system. */ CREATE TABLE ReferencePeriodical ( Abbreviation NATIONAL CHARACTER VARYING(255) NOT NULL PRIMARY KEY, FullName NATIONAL CHARACTER VARYING(255) NULL, Notes NATIONAL TEXT NULL, ImportedFrom NATIONAL CHARACTER VARYING(50) NULL, Responsible INTEGER NOT NULL, LogCreatedBy INTEGER NOT NULL, LogCreatedWhen DATETIME NOT NULL DEFAULT current_timestamp ); /*=== Table: ReferencePeriodicalSyn ===*/ /* Literature reference subsystem, Journal/periodical titles, thesaurus with synonyms -> valid name. Any entry in Periodical.Abbreviation and Periodical.FullName must also be added to the synonym table. */ /* Synonym: Alternative, synonymous names for the main record. Automatically translated into StdAbbrev if a thesaurus pick list is used in forms. //[RefMan 11: J1, J2] */ /* Abbreviation: Standardized abbreviation of periodical or journal, as defined in RefPeriodical entity (= foreign key). */ /* Source: Source of the synonym/thesaurus name: 'ABBR' for standard abbreviation, 'FULL' for standard full name, else name or abbrev. of user who added a non-standard synonym (like PNAS for Proc. Nat. Acad. Sci.). */ CREATE TABLE ReferencePeriodicalSyn ( Synonym NATIONAL CHARACTER VARYING(255) NOT NULL PRIMARY KEY, Abbreviation NATIONAL CHARACTER VARYING(255) NOT NULL, Source NATIONAL CHARACTER VARYING(50) NOT NULL ); ALTER TABLE ReferenceTitle ADD FOREIGN KEY (SourceRefID) REFERENCES ReferenceTitle (RefID) ON UPDATE CASCADE, FOREIGN KEY (RefType) REFERENCES ReferenceType (RefType) ON UPDATE CASCADE, FOREIGN KEY (ReplaceWithRefID) REFERENCES ReferenceTitle (RefID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE ReferenceAuthor ADD FOREIGN KEY (RefID) REFERENCES ReferenceTitle (RefID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE ReferenceAvailability ADD FOREIGN KEY (RefID) REFERENCES ReferenceTitle (RefID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE ReferenceCollection ADD FOREIGN KEY (RefID) REFERENCES ReferenceTitle (RefID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE ReferenceKeyword ADD FOREIGN KEY (RefID) REFERENCES ReferenceTitle (RefID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE ReferenceAbstract ADD FOREIGN KEY (RefID) REFERENCES ReferenceTitle (RefID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE ReferenceUserMarker ADD FOREIGN KEY (RefID) REFERENCES ReferenceTitle (RefID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE ReferenceUserNote ADD FOREIGN KEY (RefID) REFERENCES ReferenceTitle (RefID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE ReferenceUserDefined ADD FOREIGN KEY (RefID) REFERENCES ReferenceTitle (RefID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE ReferenceAnonymousNote ADD FOREIGN KEY (RefID) REFERENCES ReferenceTitle (RefID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE ReferenceChanges ADD FOREIGN KEY (RefID) REFERENCES ReferenceTitle (RefID) ON UPDATE CASCADE ON DELETE CASCADE ALTER TABLE ReferencePeriodicalSyn ADD FOREIGN KEY (Abbreviation) REFERENCES ReferencePeriodical (Abbreviation) ON UPDATE CASCADE ON DELETE CASCADE /*** Remove this comment line to enable creation of column description for MS SQL Server 2000 *** exec sp_addextendedproperty N'MS_Description',N'The ReferenceTitle type, as used by Reference Manager (tm) 9.0 in their default databases. Additional ref. types may be added if they occur in the import.',N'user',N'dbo',N'table',N'ReferenceType',N'column',N'RefType' exec sp_addextendedproperty N'MS_Description',N'A description, explaining the scope and use of the reference type to the user.',N'user',N'dbo',N'table',N'ReferenceType',N'column',N'Description' exec sp_addextendedproperty N'MS_Description',N'This number is used to retrieve the correct customization set in the forms. Do not change the numbering without updating the methods in the code!',N'user',N'dbo',N'table',N'ReferenceType',N'column',N'LabelSet' exec sp_addextendedproperty N'MS_Description',N'Only reference types selected here are available in a pick list; other types may be entered if the user knows their type code.',N'user',N'dbo',N'table',N'ReferenceType',N'column',N'ShowInPicklist' exec sp_addextendedproperty N'MS_Description',N'Internal notes regarding the use of a ReferenceTitle type.',N'user',N'dbo',N'table',N'ReferenceType',N'column',N'Notes' exec sp_addextendedproperty N'MS_Description',N'Type of literature reference, determines which fields are available for data entry. The value must come from the pick list provided. //[RefMan 01: TY]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'RefType' exec sp_addextendedproperty N'MS_Description',N'Unique reference ID code for the reference record. The only characters allowed are ''0''-''9'' and ''A''-''Z'' //[RefMan 02: ID]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'RefID' exec sp_addextendedproperty N'MS_Description',N'* A short text identifying the reference, usually authors, year, title. Example: ''Smith, J. & Nao, S. (1999). New Taxa.'' System generated, but users may update it. //[RefMan: not applicable]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'RefDescription' exec sp_addextendedproperty N'MS_Description',N'The main (primary) title. Use normal capitalization, omit a period (''.'') at the end, and do not type a paragraph return (Enter) at the end of each line! //[RefMan 03: TI, T1, CT, BT only for BOOK & UNPB]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'Title' exec sp_addextendedproperty N'MS_Description',N'Year of the publication date (primary date). Only numbers are allowed and the year must be entered with 4 digits (''1998'', not ''98''). //[RefMan 05: Y1,PY pro parte]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'DateYear' exec sp_addextendedproperty N'MS_Description',N'Optional: The month of the publication date. [Note: in DateYear/Month/Day the information printed on the book or journal are entered, even if this is not the true date!] //[RefMan 05: Y1,PY pro parte]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'DateMonth' exec sp_addextendedproperty N'MS_Description',N'Optional: The day of the publication date. [Note continued: if the true date is relevant, e.g. for the purpose of nomenclatural priority, it can be entered under DateSecondary.] //[RefMan 05: Y1,PY pro parte]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'DateDay' exec sp_addextendedproperty N'MS_Description',N'Optional: A date supplement, like ''approx.'', a season (''Summer''), a quarter (''1st Quarter''), or any other information regarding the publication date. //[RefMan 05: Y1,PY pro parte]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'DateSuppl' exec sp_addextendedproperty N'MS_Description',N'The book (secondary) title in cases where the reference is an article or chapter from a book. Use normal capitalization.//[RefMan 13: T2; BT for all types except BOOK & UNPB]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'SourceTitle' exec sp_addextendedproperty N'MS_Description',N'The series title. Use normal capitalization, omit a period (''.'') at the end, and do not type a paragraph return (Enter) at the end of each line! //[RefMan 23: T3]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'SeriesTitle' exec sp_addextendedproperty N'MS_Description',N'Journal/periodical in which the article appeared. Linked to the Abbreviation attribute of ReferencePeriodical. //[RefMan 11: JF, JO, JA]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'Periodical' exec sp_addextendedproperty N'MS_Description',N'The volume (for periodicals or journals, excluding the issue number), report number, etc. //[RefMan 12: VL pro parte, comp. Edition!]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'Volume' exec sp_addextendedproperty N'MS_Description',N'The issue, if any. Useful also to enter a special designation for a supplement, for example for ''xxx 45 (Suppl.)'' enter volume = 45 and issue = ''Suppl.''. Do not put ''()'' around the issue number. //[RefMan 15: IS]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'Issue' exec sp_addextendedproperty N'MS_Description',N'The page, table, or figure numbers for the reference, e.g. ''23-41'', ''341 pp.'', or ''20, 22-24, 32'' (for non-consecutive pages). //[RefMan 09: SP + 10: EP]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'Pages' exec sp_addextendedproperty N'MS_Description',N'The name of the publisher (publishing company or institution, including universities or scientific societies). //[RefMan 17: PB]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'Publisher' exec sp_addextendedproperty N'MS_Description',N'The location where the item being referenced was published, such as a city and state. //[RefMan 16: CY, CP]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'PublPlace' exec sp_addextendedproperty N'MS_Description',N'Number of the edition of a book. Use only positive integer numbers. //[RefMan 12: VL pro parte, compare Volume!]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'Edition' exec sp_addextendedproperty N'MS_Description',N'Year of a secondary date, esp. the true publ. date where relevant for nomenclatural priority. Only numbers are allowed and the year must be entered with 4 digits (''1998'', not ''98''). //[RefMan 28: Y2 pro parte]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'DateYear2' exec sp_addextendedproperty N'MS_Description',N'Optional: The month of a secondary date. //[RefMan 28: Y2 pro parte]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'DateMonth2' exec sp_addextendedproperty N'MS_Description',N'Optional: The day of a secondary date. //[RefMan 28: Y2 pro parte]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'DateDay2' exec sp_addextendedproperty N'MS_Description',N'Optional: A date supplement a secondary date, like ''approx.'', a season (''Summer''), a quarter (''1st Quarter''), or any other information regarding the secondary date. //[RefMan 28: Y2 pro parte]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'DateSuppl2' exec sp_addextendedproperty N'MS_Description',N'The ''International Standard Serial Number'' or ''International Standard Book Number''. Optional information; use is recommended only for publications that are otherwise difficult to order. //[RefMan 26: SN]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'ISSN_ISBN' exec sp_addextendedproperty N'MS_Description',N'Various reference type dependent information; e.g. the total number of volumes for books //[RefMan 29: M1]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'Miscellaneous1' exec sp_addextendedproperty N'MS_Description',N'Various reference type dependent information //[RefMan 30: M2]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'Miscellaneous2' exec sp_addextendedproperty N'MS_Description',N'Various reference type dependent information //[RefMan 31: M3]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'Miscellaneous3' exec sp_addextendedproperty N'MS_Description',N'URL address (http://www...) or local file (C:\graphic.gif; \\servername\Data\x.doc) //[RefMan 33: UR]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'URL' exec sp_addextendedproperty N'MS_Description',N'* Independent publication (e.g. an edited book) in which a dependent publication was published. Refers to the ID code of a reference already entered in this system. Provided as an alternative to ref. manager''s denormalized storage!',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'SourceRefID' exec sp_addextendedproperty N'MS_Description',N'* Language of the article/book, as ISO 2 letter codes. //[RefMan: not supported!]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'Language' exec sp_addextendedproperty N'MS_Description',N'* A system generated string (typically Au. 1-4/Yr./Jour./Vol./first page) that is assumed to be unique. Use ''DuplicateOverride'' to override if two reference titles are falsely identified as duplicates. //[RefMan: not applicable]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'DuplicateCheck' exec sp_addextendedproperty N'MS_Description',N'* A number to manually override automatic duplicate check, enter a number 1-255 if the system claims that non-duplicate entries are duplicates. //[RefMan: not applicable]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'DuplicateOverride' exec sp_addextendedproperty N'MS_Description',N'* Instead of direct deletes (currently not available for security reasons), users here select which reference should be the valid one, into which all related information (keywords, markers) is merged. References are later deleted after a review process.',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'ReplaceWithRefID' exec sp_addextendedproperty N'MS_Description',N'* A problem that occurred during data editing within the application. Typically the entries here should later be deleted after help has been obtained. Do not enter scientific or bibliographic problems here; use Notes for such permanent problems!',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'Problem' exec sp_addextendedproperty N'MS_Description',N'* Operator who entered the problem text.',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'ProblemUpdatedBy' exec sp_addextendedproperty N'MS_Description',N'* Date and time when problem was recorded.',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'ProblemUpdatedWhen' exec sp_addextendedproperty N'MS_Description',N'* Full text of a citation that describes the current reference. Use if ref. is only known as a citation in the bibliography of another publication, or if imported from unstructure data source. Empty if reference is transcribed from original publication!',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'CitationText' exec sp_addextendedproperty N'MS_Description',N'* Description of publication, Only known as citation from bibliography of another publication given here (as ID code or author/year description); Empty if transcribed from original publication!',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'CitationFrom' exec sp_addextendedproperty N'MS_Description',N'* If imported from a reference database (esp. a commercial one): The name of the database system or provider; otherwise empty. This information is important to prevent copyright violations!',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'ImportedFrom' exec sp_addextendedproperty N'MS_Description',N'* If imported from a reference database (esp. a commercial one): The ID identifying the record in that database; otherwise empty. Requires ImportedFrom //[RefMan: not supported!; Internal management attribute]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'ImportedID' exec sp_addextendedproperty N'MS_Description',N'* Person responsible for a first plausibility or consistency check. User and Date are automatically filled if ''Original check'' performed directly. //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'PlausibilityCheckedBy' exec sp_addextendedproperty N'MS_Description',N'* Date and time when plausibility/consistency was checked (i.e. data entry rules and spelling errors checked, no comparison with original publication) //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'PlausibilityCheckedWhen' exec sp_addextendedproperty N'MS_Description',N'* Name of user responsible for comparison of ReferenceTitle record with original publication //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'OriginalComparedBy' exec sp_addextendedproperty N'MS_Description',N'* Date and time when entry was compared with the original publication (important esp. when ReferenceTitle was entered from secondary ReferenceTitle list) //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'OriginalComparedWhen' exec sp_addextendedproperty N'MS_Description',N'* Person responsible for entering this reference into the data collection.',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'Responsible' exec sp_addextendedproperty N'MS_Description',N'* Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'LogCreatedBy' exec sp_addextendedproperty N'MS_Description',N'* Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceTitle',N'column',N'LogCreatedWhen' exec sp_addextendedproperty N'MS_Description',N'Author. Example: ''Miller, W. I., Jr.''. Format: Last name, a comma, the first initial with period and blank, an optional middle initial, and an optional comma plus suffix (Jr./Sr./III./MD etc.) //[RefMan 04: A1/AU, 14: A2/ED, 24: A3; no ''*'' allowed!]',N'user',N'dbo',N'table',N'ReferenceAuthor',N'column',N'Author' exec sp_addextendedproperty N'MS_Description',N'The address of the author, if available. Entered only in cases where it is of special relevance to one of the users of the database (or if imported from a database). //[RefMan 32: AD]',N'user',N'dbo',N'table',N'ReferenceAuthor',N'column',N'AuthorAddress' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID code of the main ReferenceTitle table (= foreign key).',N'user',N'dbo',N'table',N'ReferenceAuthor',N'column',N'RefID' exec sp_addextendedproperty N'MS_Description',N'Three different author categories are supported: 1 = primary author, 2 = secondary author/editor, 3 = series editor. //[RefMan implicit]',N'user',N'dbo',N'table',N'ReferenceAuthor',N'column',N'AuthorType' exec sp_addextendedproperty N'MS_Description',N'The sequence of authors of the article. (Default based on system date/time; counter attrib. wouldn''t work with replication -> random sequence! Note: Au+RefID+Type is not necessarily unique. Two authors may have identical abbreviated names, e.g. spouses!).',N'user',N'dbo',N'table',N'ReferenceAuthor',N'column',N'AuthorSequence' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID code of the main ReferenceTitle table (= foreign key).',N'user',N'dbo',N'table',N'ReferenceAvailability',N'column',N'RefID' exec sp_addextendedproperty N'MS_Description',N'Unique ID to identify the availability record. (Technical note: to improve reliability of database replication, the primary key is formed in combination with the RefID. Note that FilingCode may be Null.).',N'user',N'dbo',N'table',N'ReferenceAvailability',N'column',N'AvailabilityID' exec sp_addextendedproperty N'MS_Description',N'Information about availability or location of a copy of the referenced publication: Filing code of reprint or book in private filing system, institutional catalogue code, signature, official call number, or shelf code in a library. //[RefMan 27: AV]',N'user',N'dbo',N'table',N'ReferenceAvailability',N'column',N'FilingCode' exec sp_addextendedproperty N'MS_Description',N'Refers to filing system of responsible person. Reprint may be ''Not in file'' (0), ''On request (card to author)'' (1), ''On request (internal order/copy marker)'' (2), ''On request (interlibrary loan)'' (3), or ''In file'' (4) //[RefMan 08: RP pro parte]',N'user',N'dbo',N'table',N'ReferenceAvailability',N'column',N'ReprintStatus' exec sp_addextendedproperty N'MS_Description',N'Only if ReprintStatus = ''On request'' (1/2): The date on which the reprint was requested. //[RefMan 08: RP pro parte]',N'user',N'dbo',N'table',N'ReferenceAvailability',N'column',N'RequestDate' exec sp_addextendedproperty N'MS_Description',N'The person responsible for the availability/filing code information, and to which the reprint status/request date refers. //[RefMan: not supported]',N'user',N'dbo',N'table',N'ReferenceAvailability',N'column',N'Responsible' exec sp_addextendedproperty N'MS_Description',N'Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceAvailability',N'column',N'LogCreatedBy' exec sp_addextendedproperty N'MS_Description',N'Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceAvailability',N'column',N'LogCreatedWhen' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID code of the main ReferenceTitle table (= foreign key).',N'user',N'dbo',N'table',N'ReferenceCollection',N'column',N'RefID' exec sp_addextendedproperty N'MS_Description',N'Code to identify a reference as belonging to a specific reference collection (= a virtual reference database, e.g. for a collaborative project).',N'user',N'dbo',N'table',N'ReferenceCollection',N'column',N'ReferenceCollection' exec sp_addextendedproperty N'MS_Description',N'The person responsible for assigning the reference to a reference collection. //[RefMan: not supported]',N'user',N'dbo',N'table',N'ReferenceCollection',N'column',N'Responsible' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID code of the main ReferenceTitle table (= foreign key).',N'user',N'dbo',N'table',N'ReferenceKeyword',N'column',N'RefID' exec sp_addextendedproperty N'MS_Description',N'A keyword, esp. subject headings or private markers. A keyword may consist of multiple words (phrases, e.g. ''red herring''), which can then only be searched as a whole. //[RefMan 07: KW]',N'user',N'dbo',N'table',N'ReferenceKeyword',N'column',N'Keyword' exec sp_addextendedproperty N'MS_Description',N'The person responsible for the assignment of this keyword to a reference. //[RefMan: not supported]',N'user',N'dbo',N'table',N'ReferenceKeyword',N'column',N'Responsible' exec sp_addextendedproperty N'MS_Description',N'(temporarily introduced to preserve information when importing data that distinguish between (k)eyword, (g)eography, and organism (n)ames).',N'user',N'dbo',N'table',N'ReferenceKeyword',N'column',N'KeywordType' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID code of the main ReferenceTitle table (= foreign key).',N'user',N'dbo',N'table',N'ReferenceAbstract',N'column',N'RefID' exec sp_addextendedproperty N'MS_Description',N'* The person responsible for the abstract information. Abstracts are copyright protected! Leave empty if the abstract was not written by you, but copied from the publication itself or a bibliographic database. //[RefMan: not supported]',N'user',N'dbo',N'table',N'ReferenceAbstract',N'column',N'Responsible' exec sp_addextendedproperty N'MS_Description',N'* Language of the abstract, as ISO 2 letter codes. //[RefMan : not supported!]',N'user',N'dbo',N'table',N'ReferenceAbstract',N'column',N'Language' exec sp_addextendedproperty N'MS_Description',N'An abstract containing a short summary of the content of the article or book //[RefMan 25: N2]',N'user',N'dbo',N'table',N'ReferenceAbstract',N'column',N'Abstract' exec sp_addextendedproperty N'MS_Description',N'* Do you want to share your abstract with other users, i.e. can it be published on the web? //[RefMan : not supported!]',N'user',N'dbo',N'table',N'ReferenceAbstract',N'column',N'Shareable' exec sp_addextendedproperty N'MS_Description',N'* Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceAbstract',N'column',N'LogCreatedBy' exec sp_addextendedproperty N'MS_Description',N'* Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceAbstract',N'column',N'LogCreatedWhen' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID code of the main ReferenceTitle table (= foreign key).',N'user',N'dbo',N'table',N'ReferenceUserMarker',N'column',N'RefID' exec sp_addextendedproperty N'MS_Description',N'* User specific marker, e.g. to mark up a reference for a specific project or for a publication. These markers will normally be visible only under the same Responsible login name.',N'user',N'dbo',N'table',N'ReferenceUserMarker',N'column',N'Marker' exec sp_addextendedproperty N'MS_Description',N'* The person responsible for the assignment of this user marker to a reference. Normally each person sees only his or her own markers!',N'user',N'dbo',N'table',N'ReferenceUserMarker',N'column',N'Responsible' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID code of the main ReferenceTitle table (= foreign key).',N'user',N'dbo',N'table',N'ReferenceUserNote',N'column',N'RefID' exec sp_addextendedproperty N'MS_Description',N'* The person responsible for the Notes. //[RefMan: not supported]',N'user',N'dbo',N'table',N'ReferenceUserNote',N'column',N'Responsible' exec sp_addextendedproperty N'MS_Description',N'Internal notes regarding the reference title. Notes will normally be visible only under the same Responsible login name. //[RefMan 06: N1, AB]',N'user',N'dbo',N'table',N'ReferenceUserNote',N'column',N'Notes' exec sp_addextendedproperty N'MS_Description',N'* Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceUserNote',N'column',N'LogCreatedBy' exec sp_addextendedproperty N'MS_Description',N'* Date and time when record was first entered (= typed or imported) into this system. Note: Updates are recorded in ReferenceChanges. //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceUserNote',N'column',N'LogCreatedWhen' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID code of the main ReferenceTitle table (= foreign key).',N'user',N'dbo',N'table',N'ReferenceUserDefined',N'column',N'RefID' exec sp_addextendedproperty N'MS_Description',N'The person responsible for the user defined fields (usually who imported the RIS file).',N'user',N'dbo',N'table',N'ReferenceUserDefined',N'column',N'Responsible' exec sp_addextendedproperty N'MS_Description',N'User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 18: U1]',N'user',N'dbo',N'table',N'ReferenceUserDefined',N'column',N'UserDef1' exec sp_addextendedproperty N'MS_Description',N'User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 19: U2]',N'user',N'dbo',N'table',N'ReferenceUserDefined',N'column',N'UserDef2' exec sp_addextendedproperty N'MS_Description',N'User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 20: U3]',N'user',N'dbo',N'table',N'ReferenceUserDefined',N'column',N'UserDef3' exec sp_addextendedproperty N'MS_Description',N'User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 21: U4]',N'user',N'dbo',N'table',N'ReferenceUserDefined',N'column',N'UserDef4' exec sp_addextendedproperty N'MS_Description',N'User defined fields as entered in Reference Manager, only provided for import/export compatibility and not supported beyond that. //[RefMan 22: U5]',N'user',N'dbo',N'table',N'ReferenceUserDefined',N'column',N'UserDef5' exec sp_addextendedproperty N'MS_Description',N'Words > 50 character are truncated!',N'user',N'dbo',N'table',N'ReferenceWordIdx1',N'column',N'Word' exec sp_addextendedproperty N'MS_Description',N'Words > 50 character are truncated!',N'user',N'dbo',N'table',N'ReferenceWordIdx2',N'column',N'Word' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID code of the main ReferenceTitle table (= foreign key).',N'user',N'dbo',N'table',N'ReferenceAnonymousNote',N'column',N'RefID' exec sp_addextendedproperty N'MS_Description',N'Notes entered by an anonymous user over the internet.',N'user',N'dbo',N'table',N'ReferenceAnonymousNote',N'column',N'Notes' exec sp_addextendedproperty N'MS_Description',N'A system generated code that can be used to enable the anonymous user to update the Note over the internet.',N'user',N'dbo',N'table',N'ReferenceAnonymousNote',N'column',N'InternetAccessCode' exec sp_addextendedproperty N'MS_Description',N'Name of person who entered the anyonmous notes (free text, not referring to the User management component like most other LogCreatedBy attributes!).',N'user',N'dbo',N'table',N'ReferenceAnonymousNote',N'column',N'LogCreatedBy' exec sp_addextendedproperty N'MS_Description',N'Date and time when record was entered into this system.',N'user',N'dbo',N'table',N'ReferenceAnonymousNote',N'column',N'LogCreatedWhen' exec sp_addextendedproperty N'MS_Description',N'Date and time when record was last updated.',N'user',N'dbo',N'table',N'ReferenceAnonymousNote',N'column',N'LogUpdatedWhen' exec sp_addextendedproperty N'MS_Description',N'Refers to the ID code of the main ReferenceTitle table (= foreign key).',N'user',N'dbo',N'table',N'ReferenceChanges',N'column',N'RefID' exec sp_addextendedproperty N'MS_Description',N'Optionally a record of the attributes and data that were affected by the update //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceChanges',N'column',N'Changes' exec sp_addextendedproperty N'MS_Description',N'* User who last updated the reference identified by RefID. This is the operator (or typist) name, which may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceChanges',N'column',N'LogUpdatedBy' exec sp_addextendedproperty N'MS_Description',N'* Date and time of last update of the reference. //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferenceChanges',N'column',N'LogUpdatedWhen' exec sp_addextendedproperty N'MS_Description',N'Standardized abbreviation of periodical or journal. Use periods after the abbreviations //[RefMan 11: JA, JO]',N'user',N'dbo',N'table',N'ReferencePeriodical',N'column',N'Abbreviation' exec sp_addextendedproperty N'MS_Description',N'Full, non-abbreviated name of periodical or journal //[RefMan 11: JF, JO]',N'user',N'dbo',N'table',N'ReferencePeriodical',N'column',N'FullName' exec sp_addextendedproperty N'MS_Description',N'Notes, remarks, or comments regarding the journal/periodical as a whole, incl. "continued as (new title)" or notes about local availability.',N'user',N'dbo',N'table',N'ReferencePeriodical',N'column',N'Notes' exec sp_addextendedproperty N'MS_Description',N'If imported from another database: The name of the database system or provider; otherwise empty.',N'user',N'dbo',N'table',N'ReferencePeriodical',N'column',N'ImportedFrom' exec sp_addextendedproperty N'MS_Description',N'The person responsible for entering the information.',N'user',N'dbo',N'table',N'ReferencePeriodical',N'column',N'Responsible' exec sp_addextendedproperty N'MS_Description',N'Operator who first entered (typed or imported) the data. The operator (or typist) may be different from the person responsible. //[RefMan: not applicable. Internal mgmt.attribute]',N'user',N'dbo',N'table',N'ReferencePeriodical',N'column',N'LogCreatedBy' exec sp_addextendedproperty N'MS_Description',N'Date and time when record was first entered (typed or imported) into this system.',N'user',N'dbo',N'table',N'ReferencePeriodical',N'column',N'LogCreatedWhen' exec sp_addextendedproperty N'MS_Description',N'Alternative, synonymous names for the main record. Automatically translated into StdAbbrev if a thesaurus pick list is used in forms. //[RefMan 11: J1, J2]',N'user',N'dbo',N'table',N'ReferencePeriodicalSyn',N'column',N'Synonym' exec sp_addextendedproperty N'MS_Description',N'Standardized abbreviation of periodical or journal, as defined in RefPeriodical entity (= foreign key).',N'user',N'dbo',N'table',N'ReferencePeriodicalSyn',N'column',N'Abbreviation' exec sp_addextendedproperty N'MS_Description',N'Source of the synonym/thesaurus name: ''ABBR'' for standard abbreviation, ''FULL'' for standard full name, else name or abbrev. of user who added a non-standard synonym (like PNAS for Proc. Nat. Acad. Sci.).',N'user',N'dbo',N'table',N'ReferencePeriodicalSyn',N'column',N'Source' GO *** Remove this comment line to enable creation of column description for MS SQL Server 2000 ***/