Question

The following question is based upon the BIRD relation below, which lists information about the birds of the USA . You can assume the data is representative. Note that scientific names are generally used worldwide for plants and animals to eliminate ambiguity, as common names can vary between countries. You have been asked to design a relational database based on this design. You know that there are problems with the current design and that it will need to be modified in order to work effectively. Answer the following questions.

a. What is the candidate key (or keys) of the relation, as it currently exists? What normal form is the relation in? Explain your reasoning.

b. Explain the problems with the existing design, in terms of the potential modification anomalies that it might exhibit.

c. 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 create 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.

d. 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.

HABITAT	GENUS	SPECIES	ORDER	FAMILY	FAMILY NAME	COMMON NAME	FOOD	DISTRIBUTION	CONSERVATION
desert	melanerpes	uropygialis	Piciformes	Picidae	Woodpeckers	Gila Woodpecker	Omnivore	Southwestern US	Low concern
Forests	Buteo	platypterus	Accipitriformes	Accipitridae	Hawks, Eagles and Kites	Broad-winged Hawk	Small animals	Eastern US	Low concern
Lakes and ponds	Pandion	haliaetus	Accipitriformes	Pandionidae	Osprey	Osprey	Fish	Northern US	Low concern
Lakes and ponds	Aix	sponsa	Anseriformes	Anatidae	Ducks, Geese and Waterfowl	Wood Duck	Insects	Eastern US	low concern
Lakes and ponds	Aix	sponsa	Anseriformes	Anatidae	Ducks, Geese and Waterfowl	Wood Duck	Insects	Northwest US	Low concern
Lakes and ponds	Gavia	pacifica	Gaviiformes	Gaviidae	Loons	Pacific Loon	Fish	Pacific Coast	Low concern
Lakes and ponds	Pelecanus	erythrorhynchos	Pelecaniformes	Pelecanidae	Pelicans	American White Pelican	fish	Central US	low concern
Lakes and ponds	Pelecanus	erythrorhynchos	Pelecaniformes	Pelecanidae	Pelicans	American White Pelican	Fish	Southern Coast	Low concern
Marshes	Anser	canagicus	Anseriformes	Anatidae	Ducks, Geese and Waterfowl	Emperor Goose	Plants	Northwest Coast	Restricted range
Marshes	Eudocimus	albus	Pelecaniformes	Threskiornithidae	Ibises and Spoonbills	White Ibis	Aquatic invertebrates	Southeast US	Low concern
Oceans	Larus	occidentalis	Charadriiformes	Laridae	Gulls, Terns, and Skimmers	Western Gull	Fish	Pacific Coast	Low concern

Please help ^_^. My brain is fried, any help is appreciated!

Answer 1

PRIMARY KEY :

Habitat, Food, DIstribution, Genus

1NF SInce it Does not satisfies 2NF according to the Keys that we have like there should not be any partial dependencies Like

Habitat,Species=> Order

Check the Five attribute Genus, species, Order, Family, Family Name These Five attributes are repeating We can create a new relation for these two attributes and keep an id in the main table and by using that we can retrieve data

Problems With This Design :

For Example If we want to Change the Species for the Genus  Aix we need to update the two columns since there two Aix Genus, In The Same thing if we to change the Species of the Genus Pelecanus Again we need to repeat the same thing So It Creates a problem If we Update one and Forget to update Another Of the same Species This is an update Anamoly

Convert To 3NF

Birds(Habitat, Food, Distribution, Genus, Common Name, Conservation)

Genus =>Is a Derived Attribute of Table Family

Habitat, Food, Distribution =>Primary Key

Family(Genus, Species, Order, Family, Family Name)

Removing Species, Order, Family, Family Name To Remove Redundancy

Here Genus IS An Primary key By which we can Derive other Attributes

D Explanation

By Converting the given relation to the 3NF we solve the problem of redundancy of the Same  Genus, species, Order, Family, Family Name  

LossLess Decomposition :

Previously the dependency of the Habitat, Genus =>Order Now holds in the same Way like Habitat, Genus Are in the same Relation So we can Access the Order By accessing the Genus