Waypoints: Reports
Similar to what is presented in our queries section, we have created sample reports to
demonstrate the functionality in our database.
1. Report to see who has donated and what they have donated. Ordered by donor. mysql> SELECT artifact.ArtifactID, ArtifactName, DonorGroupName, SubmissionDate FROM artifact, donor WHERE artifact.ArtifactID = donor.ArtifactID ORDER BY DonorGroupName; +------------+----------------------------------------------------+------------------------------+----------------+ | ArtifactID | ArtifactName | DonorGroupName | SubmissionDate | +------------+----------------------------------------------------+------------------------------+----------------+ | 10 | Sinbad, USCG (Ret.), K9C (Chief Petty Officer, Dog | Charles J. Williamson | 1975-12-12 | | 12 | Boatswains Mate First Class Maxie Berry, Sr., USCG | Charles J. Williamson | 1980-10-10 | | 13 | Alexander Palmer Haley, Chief Journalist, USCG (Re | Historic North Carolina | 1995-11-05 | | 16 | Crew of Station Quonocontaug (54) | Key West Archives | 1925-03-28 | | 21 | Curtiss F flying boat | Maryland Nautical Society | 1925-09-16 | | 22 | Loening OL-5 amphibian | Maryland Nautical Society | 1931-10-12 | | 31 | U.S. Coast Guard 75-ft Patrol | Maryland Nautical Society | 1992-06-26 | | 1 | Alcatraz Light | Museum Society of California | 0000-00-00 | | 2 | East Brother Island Light | Museum Society of California | 0000-00-00 | | 17 | USRC Gallatin | New Jersey Nautical Society | 1857-01-01 | | 18 | USRC Grant | New Jersey Nautical Society | 1901-05-25 | | 19 | USRC McCulloch | New Jersey Nautical Society | 1927-08-30 | | 20 | USRC Massachusetts | New Jersey Nautical Society | 1990-08-14 | | 6 | Idawalley Zorada Lewis (-Wilson), Keeper, USLHS | New York Historical Society | 0000-00-00 | | 7 | Commander Elmer Fowler Stone, USCG | New York Historical Society | 0000-00-00 | | 8 | Katherine Walker | New York Historical Society | 0000-00-00 | | 9 | Douglas A. Munro, Signalman First Class, U.S. Coas | New York Historical Society | 1950-06-05 | | 11 | Captain Hell Roaring, Michael A. Healy, U.S.R.C.S. | New York Historical Society | 1900-01-15 | | 14 | Commander (E) Quincy B. Newman | New York Historical Society | 1960-07-28 | | 15 | World War I Yeomanettes | New York Historical Society | 1925-03-28 | | 3 | Point Reyes Light | Point Reyes Natural Museum | 0000-00-00 | +------------+----------------------------------------------------+------------------------------+----------------+ 21 rows in set (0.00 sec) If this were MySQL v. 5.0 or higher, we would have created a stored procedure for this and would have additionally counted the donations by the New York Historical Society: mysql> CREATE PROCEDURE COUNT_MAJOR_DONORS AS BEGIN SELECT artifact.ArtifactID, ArtifactName, DonorGroupName FROM artifact, donor WHERE artifact.ArtifactID = donor.ArtifactID ORDER BY DonorGroupName; SELECT COUNT(*) FROM donor WHERE DonorGroupName = 'New York Historical Society'; END; Then we would have executed it: EXEC COUNT_MAJOR_DONORS; 2. mysql> SELECT ArtifactType, COUNT(ArtifactType) FROM artifact GROUP BY ArtifactType; +--------------+---------------------+ | ArtifactType | COUNT(ArtifactType) | +--------------+---------------------+ | Painting | 2 | | Photo | 35 | +--------------+---------------------+ 2 rows in set (0.00 sec) For this mid-term report, we only populated the artifact table with photos and a couple of paintings. If the database were fully populated, this report would provide a ount for more types of artifacts including historical documents, videos, and physical artifacts. 3. Users may wish to look up photos submitted in a certain time period. When the database is fully populated to include CreationDate, users may find it even more useful to look up artifacts by their creation date or within a certain time period. mysql> SELECT ArtifactName, StorageLocation, SubmissionDate FROM artifact WHERE SubmissionDate > '1700-01-01' AND SubmissionDate < '1935-01-01' ORDER BY SubmissionDate; +----------------------------------------------------+-------------------------------------------+----------------+ | ArtifactName | StorageLocation | SubmissionDate | +----------------------------------------------------+-------------------------------------------+----------------+ | USRC Gallatin | Newport, R.I. | 1857-01-01 | | Captain Hell Roaring, Michael A. Healy, U.S.R.C.S. | Washington DC | 1900-01-15 | | USRC Grant | Newport, R.I. | 1901-05-25 | | Bear | Alameda, CA | 1902-01-13 | | World War I Yeomanettes | Washington DC | 1925-03-28 | | Crew of Station Quonocontaug (54) | Washington DC | 1925-03-28 | | Curtiss F flying boat | Coast Guard Academy Museum Art Collection | 1925-09-16 | | USRC McCulloch | Coast Guard Academy Museum Art Collection | 1927-08-30 | | Unknown | Washington DC | 1929-06-17 | | Keeper and crew of Vermilion Life-Saving Station, | Washington DC | 1929-06-17 | | U.S. Coast Guard 75-foot Patrol | Alameda, CA | 1929-06-20 | | Loening OL-5 amphibian | North Carolina | 1931-10-12 | +----------------------------------------------------+-------------------------------------------+----------------+ 12 rows in set (0.00 sec) 4. Coast Guard Historians who review this material may also find it useful to see who is reviewing which artifacts. Perhaps this would be a report the head historian would run. mysql> SELECT ArtifactID, ArtifactName, LastName FROM artifact,historian WHERE HistorianReviewID = historian.HistorianID ORDER BY HistorianReviewID; +------------+----------------------------------------------------+-----------+ | ArtifactID | ArtifactName | LastName | +------------+----------------------------------------------------+-----------+ | 7 | Commander Elmer Fowler Stone, USCG | Cooper | | 4 | Yerba Buena (Goat Island) Light | Cooper | | 12 | Boatswains Mate First Class Maxie Berry, Sr., USCG | Cooper | | 6 | Idawalley Zorada Lewis (-Wilson), Keeper, USLHS | Cooper | | 1 | Alcatraz Light | Cooper | | 13 | Alexander Palmer Haley, Chief Journalist, USCG (Re | Cooper | | 23 | Sikorsky HOS-1 (R-6) Hoverfly II | Brannigan | | 19 | USRC McCulloch | Brannigan | | 2 | East Brother Island Light | Brannigan | | 24 | Piasecki HRP-1 Rescuer / Flying Banana | Brannigan | | 22 | Loening OL-5 amphibian | Brannigan | | 15 | World War I Yeomanettes | Brannigan | | 29 | Thetis | Ogden | | 27 | Bear | Ogden | | 25 | Sikorsky HO2S-1 / HO3S-1G Dragonfly | Ogden | | 30 | Storis | Ogden | | 28 | Blackthorn | Ogden | | 26 | Upshur (Abel P.) | Ogden | | 3 | Point Reyes Light | Ogden | +------------+----------------------------------------------------+-----------+ 19 rows in set (0.00 sec) 5. A retired Coast Guard officer may be a searching the system to see who might be in the content of photographs. Or a historian who has administrative privelages to add metadata to the system might want to see what people are contained in the Person facet table, so they can make sure the person they are adding is not already entered. (Note: Although we were not able to create it for this project due to time constraints, eventually there will be an interface from which those with the appropriate rights can add metadata to the system. Ideally, the logic will be built in to the interface or PHP to allow users to choose from a drop down list of options already in the system and then to add their metadata if it is not in the system.) mysql> select FacetName from person where LeafFlag = 1 ORDER BY FacetName; +-----------------------------+ | FacetName | +-----------------------------+ | Alex Haley | | Barney Lokken | | Charles Shepardson | | Christine Jones | | Elmer Stone | | Ida Lewis | | J. William Kime | | James M. Loy | | John P. Greathouse | | Joshua James | | Katherine Walker | | Ken Langford | | Maxie Berry, Sr. | | Mike Healey | | Oscar Lindbert | | Paul A. Yost, Jr. | | Rudolph R. Schlesinger, Jr. | | Thomas H. Collins | +-----------------------------+ 18 rows in set (0.00 sec)