12 – Database Design Case Study #2: East Coast Aquarium

Publisher Summary The organization described in this chapter actually needs two databases, the larger of which is replete with many-to-many relationships. In some cases it will be necessary to create additional entities for composite entities, to reference merely to ensure data integrity. The company has two very different areas in which it needs data management. The first is in the handling of its animals —where they are housed in the aquarium, where they came from, what they are to be fed, problems that occur in the tanks, and so on. The second area concerns the volunteers, including who they are, what they have been trained to do, and when they are scheduled to work. It needed an “inventory” of which species are living in which locations of the aquarium. Some species can be found in more than one tank, and several tanks, in addition to the central tank, contain more than one species. There also need for data about the foods each species eats, including how much should be fed at what interval. The head animal keeper would like to be able to print out a feeding instruction list every morning to give to the staff. Data about the sizes, locations, and habitats of the tanks on the aquarium grounds is also needed, along with the habitats in which a given species can live. When a new species arrives at the aquarium, the staff can use this information to determine which locations could possibly house that species. Given that the specifications of the database are rather general, the first step is to create a prototype of an application program interface. From the approved prototype of the application design, the database designers can gather enough information to create a basic ER diagram for the volunteers organization.