Lab 1 - Logical Spatial Database Design using ERDs

To kickstart the first module and lab assignment for this class, we were given a scenario of working as part of a GIS project team that has been tasked to develop a spatial database to support mapping of water quality data across Pensacola, Florida. The data already exists, and has been collected by multiple organizations for years from historical sample stations.

One of the main deliverables for our lab assignment was to create an Entity-Relationship Diagram (ERD) based on 8 entities that have already been identified for us as important to the Pensacola water quality database project. So, starting with a new, blank document in Lucidchart, I created the ERD shown below. I referenced Chen (1983) on setting up the entity-relationship structure, and referenced Chawla (2013) to account for the cardinality and ordinality of my ERD.

All my entities/relationships apply either a mandatory or optional ordinality – I may have gone overboard – especially because we know that if we allow a user to leave an attribute blank, it can haunt us later. That being said, I can justify how all my entities are possibly associated with each other:

-          A county boundary can have a minimum of one road or a maximum of many roads; also, a road can be fully contained in one county or intersect multiple counties.

-           All counties have at least one land use cover or more; and land use cover can bleed outside a county boundary onto other counties.

-          A county can have either no water quality organizations or many water quality organizations; also, a water quality organization must belong to at least one county, or they can also belong to many counties (for instance, if they have various satellite office locations).

-          A sample station can only be located in one county, but not all counties will have a sample station; or a county can have multiple sample stations.

-          Each sample station has water sample measurement data, which is associated to at least one water body, but perhaps not all water bodies have been sampled (which is why a water body can have 0 to many water sample measurements).

-          Water bodies must belong to at least one hydrological unit, and depending on this unit, the hydrological unit can overlay multiple land use covers (which can be the case for watershed areas).