INFO210_HW5_Solution

On name inhabit habitats solution the diagram

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: itat_name not null. create table Habitats ( name varchar(128) primary key ); create table Animals_Inhabit ( genus varchar(64), species varchar(64), habitat_name varchar(128) n ot n ull , population number, primary key (genus, species), foreign key (habitat_name) references Habitats(name) ); (d) genus% ANIMALS' species% popula.on% name% inhabit' HABITATS' Solution: The diagram specifies a one-to-many relationship set between Animals and Habitats: an Animal must inhabit exactly one Habitat (both key and participation on Animals), and a Habitat may be inhabited by 0, 1 or several Animals. To implement the business rules, we use exactly the same two create table statements as in (c) above, with the same explanation. (e) genus% ANIMALS' popula.on% name% inhabit' species% HABITATS' Solution: The ER diagram specifies a one-to-many relationship set between Animals and Habitats: an Animal can inhabit at most 1 Habitat (key constraint but no participation on Animals), and a Habitat may be inhabited by 0, 1 or several Animals (no key and no participation constraints on Habitats). The best way to implement these business rules is with two tables: one representing Habitats and the other representing Animals and inhabit. Key constraint on Animals is implemented by making (genus, species) a primary key on Animals_Inhabit. Habitats with no Animals in them will have a tuple in Habitats, but their habitat name will not appear in Animals_Inahbit. Animals with no Habitat will appear in Animals_Inhabit, with habitat_name set to null. create table Habitats ( name varchar(128) primary key ); create table Animals_Inhabit ( genus varchar(64), species varchar(64), habitat_name varchar(128), population number, primary key (genus, species), foreign key (habitat_name) references Habitats(name) ); (f) genus% ANIMALS' species% popula.on% name% inhabit' HABITATS' Solution: This ER diagram specifies a one-to-one relationship set between Animals and Habitats: an Animal can inhabit at most 1 Habitat (key constraint but no participati...
View Full Document

This note was uploaded on 01/21/2014 for the course INFO 210 taught by Professor Stoy during the Winter '13 term at Drexel.

Ask a homework question - tutors are online