Answer the following questions. Make sure you answer all parts of each question.

1. What is the candidate key (or keys) of the relation, as it currently exists? What normal form is the relation in? Explain your reasoning.
2. Explain the problems with the existing design, in terms of the potential modification anomalies that it might exhibit.
3. Convert the relation to a set of relations in at least Third Normal Form (3NF). You only need to show the schema, not the data. Do not make any new attributes: work with the ones in the table. Give each of your new relations an appropriate name. Show all primary keys and foreign keys.
4. Explain how your new design addresses the problems you identified in (b). Also demonstrate that your set of relations has the dependency-preserving and lossless join properties.
5.  

Transcribed Image Text:

Habitat Deserts Forests Lakes and ponds Lakes and ponds Lakes and ponds Lakes and ponds Lakes and ponds Lakes and ponds Marshes Marshes Oceans Oceans Oceans Scrub Shorelines Genus Tundra Tundra Melanerpes uropygialis Buteo Pandion Aix Aix Gavia Pelecanus Pelecanus Anser Eudocimus Larus Species Larus platypterus Asio haliaetus Bubo sponsa sponsal Phoebastria nigripes Open woodlands Archilochus colubris Open woodlands Selasphorus rufus pacifica canagicus Pelecanus occidentalis albus occidentalis Gymnogyps californianus erythrorhynchos Pelecaniformes erythrorhynchos Pelecaniformes argentatus otus Order scandiacus Piciformes Anseriformes Accipitriformes Accipitridae Accipitriformes Pandionidae Anseriformes Gaviiformes Anseriformes Pelecaniformes Charadriiformes Pelecaniformes Cathartiformes Family Charadriiformes Picidae Strigiformes Strigiformes Anatidae Anatidae Gaviidae Pelecanidae Pelecanidae Anatidae Threskiornithidae Procellariiformes Diomedeidae Caprimulgiformes Trochilidae Caprimulgiformes Trochilidae Laridae Pelecanidae Cathartidae Laridae Strigidae Strigidae Family name Woodpeckers Hawks, Eagles and Kites Osprey Ducks, Geese and Waterfowl Ducks, Geese and Waterfowl Loons Pelicans Pelicans Ducks, Geese and Waterfowl Ibises and Spoonbills Gulls, Terns, and Skimmers. Pelicans. Albatrosses Hummingbirds. Hummingbirds New World Vultures Gulls, Terns, and Skimmers Owls Owls Common name Gila Woodpecker Broad-winged Hawk Osprey Wood Duck Wood Duck Pacific Loon American White Pelican American White Pelican Emperor Goose White Ibis Western Gull Brown Pelican Black-footed Albatross Ruby-throated Hummingbird Rufous Hummingbird California Condor Herring Gull Long-eared Owl Snowy Owl Omnivore Small animals Fish Insects Insects Fish Fish Food Fish Plants Aquatic invertebrates Fish Fish Fish Nectar Nectar Carrion Omnivore Mammals Mammals Distribution Southwestern US Eastern US Northern US Eastern US Northwest US Pacific Coast Central US Southern Coast Northwest Coast Southeast US Pacific Coast Southern Coast Pacific Ocean Eastern US Pacific Coast California Eastern Coast Central US Northern US Conservation Low concern Low concern Low concern Low concern Low concern Low concern Low concern Low concern Restricted range Low concern Low concern Low concern Restricted range Low concern Declining Red Watch List Steep decline Declining Declining Habitat Deserts Forests Lakes and ponds Lakes and ponds Lakes and ponds Lakes and ponds Lakes and ponds Lakes and ponds Marshes Marshes Oceans Oceans Oceans Scrub Shorelines Genus Tundra Tundra Melanerpes uropygialis Buteo Pandion Aix Aix Gavia Pelecanus Pelecanus Anser Eudocimus Larus Species Larus platypterus Asio haliaetus Bubo sponsa sponsal Phoebastria nigripes Open woodlands Archilochus colubris Open woodlands Selasphorus rufus pacifica canagicus Pelecanus occidentalis albus occidentalis Gymnogyps californianus erythrorhynchos Pelecaniformes erythrorhynchos Pelecaniformes argentatus otus Order scandiacus Piciformes Anseriformes Accipitriformes Accipitridae Accipitriformes Pandionidae Anseriformes Gaviiformes Anseriformes Pelecaniformes Charadriiformes Pelecaniformes Cathartiformes Family Charadriiformes Picidae Strigiformes Strigiformes Anatidae Anatidae Gaviidae Pelecanidae Pelecanidae Anatidae Threskiornithidae Procellariiformes Diomedeidae Caprimulgiformes Trochilidae Caprimulgiformes Trochilidae Laridae Pelecanidae Cathartidae Laridae Strigidae Strigidae Family name Woodpeckers Hawks, Eagles and Kites Osprey Ducks, Geese and Waterfowl Ducks, Geese and Waterfowl Loons Pelicans Pelicans Ducks, Geese and Waterfowl Ibises and Spoonbills Gulls, Terns, and Skimmers. Pelicans. Albatrosses Hummingbirds. Hummingbirds New World Vultures Gulls, Terns, and Skimmers Owls Owls Common name Gila Woodpecker Broad-winged Hawk Osprey Wood Duck Wood Duck Pacific Loon American White Pelican American White Pelican Emperor Goose White Ibis Western Gull Brown Pelican Black-footed Albatross Ruby-throated Hummingbird Rufous Hummingbird California Condor Herring Gull Long-eared Owl Snowy Owl Omnivore Small animals Fish Insects Insects Fish Fish Food Fish Plants Aquatic invertebrates Fish Fish Fish Nectar Nectar Carrion Omnivore Mammals Mammals Distribution Southwestern US Eastern US Northern US Eastern US Northwest US Pacific Coast Central US Southern Coast Northwest Coast Southeast US Pacific Coast Southern Coast Pacific Ocean Eastern US Pacific Coast California Eastern Coast Central US Northern US Conservation Low concern Low concern Low concern Low concern Low concern Low concern Low concern Low concern Restricted range Low concern Low concern Low concern Restricted range Low concern Declining Red Watch List Steep decline Declining Declining