How to Read the Bird-Watchers' Database ERD

The focus of the bird-watchers' entity-relationship diagram (ERD) is Bird, which has a bird identifier attribute (BirdID) and two attributes to describe the size range typical to each bird (MinSize and MaxSize). Bird relates one-to-one (1:1) to Names—each type of bird has only one name, and each name refers to one type of bird.

A many-to-many (M:N) relationship exists between Bird and ColorNames: a bird can be many colors, and a single color can occur on many birds. The M:N relationship always appears as a pair of one-to-many (1:M) relationships, as in the 1:M relationship between Bird and BirdColors and the counterpart 1:M relationship between ColorNames and BirdColors. Bird lists the birds, ColorNames lists the colors, and BirdColor (the gerund or associative entity) cross-references which birds have which colors.

The relationship between Bird and HabitatNames is also M:N. This relationship appears as the pair of 1:M relationships Bird to BirdHabitats and HabitatNames to BirdHabitats. HabitatNames is a list of habitats where you expect to find a bird: meadow, woodland, riparian, ocean, or mountain. BirdHabitats associates a bird with the many habitats where you might find it, and associates a habitat with the many birds that inhabit it.

The last group of entities forms a ternary, or three-way, relationship: Bird to RegionNames to SeasonNames. Here you can describe which birds are in which geographic regions (north, south, east, and west) during which seasons (e.g., summer, winter). This ternary relationship (many-to-many-to-many) is always broken into three 1:M relationships: Bird to BirdRegions, RegionNames to BirdRegions, and SeasonNames to BirdRegions. The gerund entity BirdRegions associates a bird with a region during a season.