Waypoints: Queries
In addition to our web interface browse pages, we have created sample queries and result sets to
demonstrate the functionality in our database.
1. A user can find which donor group donated a particular artifact. mysql> SELECT donor.DonorID, DonorGroupName, artifact.ArtifactID FROM donor, artifact WHERE artifact.ArtifactID = donor.ArtifactID; +---------+------------------------------+------------+ | DonorID | DonorGroupName | ArtifactID | +---------+------------------------------+------------+ | 1 | Maryland Nautical Society | 21 | | 2 | Maryland Nautical Society | 22 | | 3 | Maryland Nautical Society | 31 | | 4 | Museum Society of California | 1 | | 5 | Museum Society of California | 2 | | 6 | Point Reyes Natural Museum | 3 | | 7 | New York Historical Society | 6 | | 8 | New York Historical Society | 7 | | 9 | New York Historical Society | 8 | | 10 | New York Historical Society | 9 | | 11 | Charles J. Williamson | 10 | | 12 | New York Historical Society | 11 | | 13 | Charles J. Williamson | 12 | | 14 | Historic North Carolina | 13 | | 15 | New York Historical Society | 14 | | 16 | New York Historical Society | 15 | | 17 | Key West Archives | 16 | | 18 | New Jersey Nautical Society | 17 | | 19 | New Jersey Nautical Society | 18 | | 20 | New Jersey Nautical Society | 19 | | 21 | New Jersey Nautical Society | 20 | +---------+------------------------------+------------+ 21 rows in set (0.00 sec) 2. We use a binary flag to indicate whether artifacts can be released. This query will display those that are not for release (where '0' = no and '1' = yes). mysql> SELECT ArtifactName, StorageLocation FROM artifact WHERE ForRelease = '0'; +----------------------------------------------------+-------------------------------------------+ | ArtifactName | StorageLocation | +----------------------------------------------------+-------------------------------------------+ | Captain Joshua James, USLSS | Washington DC | | Idawalley Zorada Lewis (-Wilson), Keeper, USLHS | Washington DC | | Commander Elmer Fowler Stone, USCG | Washington DC | | Katherine Walker | Washington DC | | Douglas A. Munro, Signalman First Class, U.S. Coas | Washington DC | | Sinbad, USCG (Ret.), K9C (Chief Petty Officer, Dog | Washington DC | | Captain Hell Roaring, Michael A. Healy, U.S.R.C.S. | Washington DC | | Boatswains Mate First Class Maxie Berry, Sr., USCG | Washington DC | | Alexander Palmer Haley, Chief Journalist, USCG (Re | Washington DC | | Commander (E) Quincy B. Newman | Washington DC | | World War I Yeomanettes | Washington DC | | Crew of Station Quonocontaug (54) | Washington DC | | USRC Gallatin | Newport, R.I. | | USRC Grant | Newport, R.I. | | USRC McCulloch | Coast Guard Academy Museum Art Collection | | USRC Massachusetts | Coast Guard Academy Museum Art Collection | | Curtiss F flying boat | Coast Guard Academy Museum Art Collection | | Loening OL-5 amphibian | North Carolina | | Sikorsky HOS-1 (R-6) Hoverfly II | Alameda, CA | | Piasecki HRP-1 Rescuer / Flying Banana | Alameda, CA | | Sikorsky HO2S-1 / HO3S-1G Dragonfly | Alameda, CA | | Upshur (Abel P.) | Alameda, CA | | Bear | Alameda, CA | | Blackthorn | Alameda, CA | | Thetis | Key West, FL | | Storis | Kodiak, Alaska | | U.S. Coast Guard 75-ft Patrol | Kodiak, Alaska | | U.S. Coast Guard 75-foot Patrol | Alameda, CA | | Unknown | Washington DC | | Douglas A. Munro, Signalman First Class, U.S. Coas | Washington DC | | Keeper and crew of Vermilion Life-Saving Station, | Washington DC | | The club swinging member of the CGB-48s Shore Patr | Washington DC | | WWII. Loading Explosives | Washington DC | +----------------------------------------------------+-------------------------------------------+ 33 rows in set (0.00 sec) 3. One may be interested in photos between a certain time period. mysql> SELECT ArtifactName, SubmissionDate FROM artifact WHERE SubmissionDate > '1800-01-01' AND SubmissionDate < '1995-01-01' AND ArtifactType = 'Photo'; +----------------------------------------------------+----------------+ | ArtifactName | SubmissionDate | +----------------------------------------------------+----------------+ | Douglas A. Munro, Signalman First Class, U.S. Coas | 1950-06-05 | | Sinbad, USCG (Ret.), K9C (Chief Petty Officer, Dog | 1975-12-12 | | Captain Hell Roaring, Michael A. Healy, U.S.R.C.S. | 1900-01-15 | | Boatswains Mate First Class Maxie Berry, Sr., USCG | 1980-10-10 | | Commander (E) Quincy B. Newman | 1960-07-28 | | World War I Yeomanettes | 1925-03-28 | | Crew of Station Quonocontaug (54) | 1925-03-28 | | USRC Gallatin | 1857-01-01 | | USRC Grant | 1901-05-25 | | Curtiss F flying boat | 1925-09-16 | | Loening OL-5 amphibian | 1931-10-12 | | Sikorsky HOS-1 (R-6) Hoverfly II | 1952-07-18 | | Piasecki HRP-1 Rescuer / Flying Banana | 1962-02-24 | | Sikorsky HO2S-1 / HO3S-1G Dragonfly | 1962-02-24 | | Upshur (Abel P.) | 1942-01-13 | | Bear | 1902-01-13 | | Blackthorn | 1954-04-11 | | Thetis | 1986-05-26 | | Storis | 1992-06-26 | | U.S. Coast Guard 75-ft Patrol | 1992-06-26 | | U.S. Coast Guard 75-foot Patrol | 1929-06-20 | | Unknown | 1929-06-17 | | Keeper and crew of Vermilion Life-Saving Station, | 1929-06-17 | | The club swinging member of the CGB-48s Shore Patr | 1954-03-17 | | WWII. Loading Explosives | 1954-03-17 | +----------------------------------------------------+----------------+ 25 rows in set (0.00 sec) 4. A historian, DB administrator, or researcher may want to see what person metadata is associated with each artifact that is related to people. There is a many to many relationship between the person and artifact and each metadata table (e.g. person, location, event, thing, etc.), so we created an association table for each top level facet (personassociation, locationassociation, eventassociation, thingassociation, etc.). mysql> select FacetName, personassociation.ArtifactID, ArtifactName from artifact, person, personassociation where person.FacetID = personassociation.FacetID and artifact.ArtifactID = personassociation.ArtifactID; +-----------------------------+------------+-------------------------+ | FacetName | ArtifactID | ArtifactName | +-----------------------------+------------+-------------------------+ | Historic Figures | 125 | Maxie Berry | | Maxie Berry, Sr. | 125 | Maxie Berry | | Historic Figures | 126 | Maxie Berry | | Maxie Berry, Sr. | 126 | Maxie Berry | | Historic Figures | 128 | Legion of Merit | | Rudolph R. Schlesinger, Jr. | 128 | Legion of Merit | | Lightkeepers | 130 | Scotch Cap Lightkeepers | | Charles Shepardson | 130 | Scotch Cap Lightkeepers | | Barney Lokken | 130 | Scotch Cap Lightkeepers | | Oscar Lindbert | 130 | Scotch Cap Lightkeepers | | Historic Figures | 133 | John P. Greathouse | | John P. Greathouse | 133 | John P. Greathouse | | Commandants | 134 | Paul Yost | | Paul A. Yost, Jr. | 134 | Paul Yost | | Commandants | 135 | Paul Yost | | Paul A. Yost, Jr. | 135 | Paul Yost | | Commandants | 136 | J. William Kime | | J. William Kime | 136 | J. William Kime | | Historic Figures | 137 | Elmer Stone | | Elmer Stone | 137 | Elmer Stone | | Joshua James | 138 | Joshua James | | Historic Figures | 138 | Joshua James | +-----------------------------+------------+-------------------------+ 22 rows in set (0.02 sec) 5. A researcher or Coast Guard enthusiast may be interested in the most detailed description of artifacts related to events. This query gets the Facet name for leaf facets (most detailed metadata level) for events and returns the facet name of the leaf facet that is associated with the artifact, the artifact id, and the artifact name. mysql> select FacetName, eventassociation.ArtifactID, ArtifactName from artifact, event, eventassociation where event.FacetID = eventassociation.FacetID and artifact.ArtifactID = eventassociation.ArtifactID and LeafFlag = 1; +----------------------+------------+--------------------------------------+ | FacetName | ArtifactID | ArtifactName | +----------------------+------------+--------------------------------------+ | Mariel Boat Lift | 1 | Freedom Flotilla | | Mariel Boat Lift | 2 | Navy Mall | | Mariel Boat Lift | 3 | Freedom Flotilla | | Mariel Boat Lift | 4 | Mariel Briefing | | Mariel Boat Lift | 5 | CGC Cape Gull Crewman | | Mariel Boat Lift | 6 | CGC Dallas Crewmen | | Mariel Boat Lift | 7 | Refugee Receiving Area | | Mariel Boat Lift | 8 | CGC Cape Strait Medivac | | Mariel Boat Lift | 9 | Coast Guard Medical Assistance | | Mariel Boat Lift | 10 | Coast Guard Receving Area | | Mariel Boat Lift | 11 | Coast Guard Crewman | | Mariel Boat Lift | 12 | Coast Guard Aids Disabled Vessel | | Mariel Boat Lift | 13 | Freedom Flotilla | | Mariel Boat Lift | 14 | Cherokee Escort | | Mariel Boat Lift | 15 | Awaiting Medivac | | Mariel Boat Lift | 16 | Refugee Mooring | | Prinsendam | 18 | Prinsendam Survivors | | Prinsendam | 19 | Prinsendam Survivor | | Prinsendam | 20 | Prinsendam Lifeboat | | Marine Sulphur Queen | 21 | SS Marine Sulphur Queen Debris | | Marine Sulphur Queen | 22 | SS Marine Sulphur Queen Debris | | Ocean Eagle | 23 | Ocean Eagle pollution Response | | Ocean Eagle | 24 | Surveying Damage | | Ocean Eagle | 25 | Ocean Eagle Wreck | | Andrea Doria | 26 | Andrea Doria | | Andrea Doria | 27 | Andrea Doria | | Andrea Doria | 28 | Andrea Doria Escort | | Andrea Doria | 29 | Andrea Doria Debris | | WWII | 56 | Amphibious DUKW | | WWII | 57 | Amphibious DUKW | | WWII | 60 | CGC Pandora | | WWII | 83 | CGC Midgett and 41-foot Utility Boat | | Deep Freeze | 85 | USS Glacier | | OPSAIL | 96 | CGC Eagle Opsail | | OPSAIL | 97 | Operation Sail | | Vietnam | 99 | USCG Squadron One | | Vietnam | 100 | CGC Half Moon | | Vietnam | 102 | CGC Point Young Salvage | | Vietnam | 103 | CGC Point Young Salvage | | Vietnam | 104 | Boarding and Searching Junks | | Vietnam | 105 | Searching a Fishing Junk | | Vietnam | 106 | Ammunition Packing | | Vietnam | 107 | Ammunition Loading | | Vietnam | 108 | Boarding and Searching Junk | | Vietnam | 109 | Firing at Hideout | | Vietnam | 110 | Captured Weapons | | Vietnam | 111 | Market Time Inspection | | Vietnam | 112 | Probing Sand | | Vietnam | 113 | Sights In | | Vietnam | 115 | Fire Fighting | | Vietnam | 116 | Enemy Rifles | | Vietnam | 117 | 81mm Mortar | | Vietnam | 118 | CGC Point Young | | Vietnam | 119 | CGC Point Banks | | Vietnam | 120 | CGC Point Banks | | WWII | 123 | Uniforms | | WWII | 124 | Black Coastguardsman | | WWII | 128 | Legion of Merit | +----------------------+------------+--------------------------------------+ 58 rows in set (0.02 sec) 6. A historian, or DB administrator might be interested in viewing the hierarchy of metadata in the thing table, so would enter this query to view the parent facet id of those metadata items that are not leaf nodes. mysql> select parentID, FacetName, FacetID from thing where LeafFlag = 0; +----------+-----------------------------------+---------+ | parentID | FacetName | FacetID | +----------+-----------------------------------+---------+ | 0 | Thing | 1 | | 1 | Aircraft | 2 | | 2 | By type | 3 | | 3 | Helicopter | 4 | | 3 | Fixed Wing | 5 | | 2 | By manufacturer | 35 | | 1 | Boats | 44 | | 44 | By length | 45 | | 44 | By type | 46 | | 1 | Cutters | 60 | | 60 | By name | 61 | | 60 | By length | 99 | | 99 | 72 ft | 100 | | 99 | 74ft | 101 | | 99 | 75 ft | 102 | | 99 | 82 ft | 103 | | 99 | 83ft | 104 | | 99 | 95 ft | 105 | | 99 | 110 ft | 106 | | 99 | 133 ft | 107 | | 99 | 160 ft | 108 | | 99 | 180 ft | 109 | | 99 | 210 ft | 110 | | 99 | 225 ft | 111 | | 99 | 270 ft | 112 | | 99 | 295 ft | 113 | | 99 | 311 ft | 114 | | 99 | 378 ft | 115 | | 60 | By manufacturer | 139 | | 139 | Marine Iron and Shipbuilding, Co. | 140 | | 1 | Revenue Cutter | 141 | | 141 | By class | 142 | | 1 | Communications | 147 | | 1 | Rates | 149 | | 1 | Medals/Awards | 158 | +----------+-----------------------------------+---------+ 35 rows in set (0.02 sec) 7. If a researcher is trying to find artifacts associated with Pea Island, s/he may use the query below to search the artifact table for artifacts that have Pea Island defined in their metadata. mysql> select artifact.ArtifactID, ArtifactName, ArtifactType, FacetName from artifact, location, locationassociation where FacetName = 'Pea Island' and artifact.ArtifactID = locationassociation.ArtifactID and location.FacetID = locationassociation.FacetID; +------------+--------------+--------------+------------+ | ArtifactID | ArtifactName | ArtifactType | FacetName | +------------+--------------+--------------+------------+ | 125 | Maxie Berry | Photograph | Pea Island | | 126 | Maxie Berry | Photograph | Pea Island | +------------+--------------+--------------+------------+ 2 rows in set (0.00 sec) 8. A historian may want to search the system to find artifacts that need preservation or restoration of some sort so they may use the query below to perform a search of those artifacts that are listed in poor condition. They would also need to find out the storage location to arrange to obtain the artifact(s). mysql> select artifact.ArtifactID, ArtifactName, Condition, StorageLocation from artifact where Condition = 'poor'; +------------+--------------+-----------+-----------------+ | ArtifactID | ArtifactName | Condition | StorageLocation | +------------+--------------+-----------+-----------------+ | 26 | Andrea Doria | Poor | | | 117 | 81mm Mortar | Poor | | +------------+--------------+-----------+-----------------+ 2 rows in set (0.00 sec) Note: For the query above, we thought it would be useful to search the photograph table specifically for photos (35mm format) listed in 'poor' condition since a preservation/restoration specialist might only work with one medium. However, the query returned an empty set because the data for photographs included in the system does not contain information on original format. mysql> select artifact.ArtifactID, ArtifactName, Condition, OriginalFormat, StorageLocation from artifact, photograph where Condition = 'good' and artifact.ArtifactID = photograph.ArtifactID and OriginalFormat = '35mm'; Empty set (0.00 sec)