INFO210_HW5_Solution

On name inhabit species habitats solution the

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

This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: species varchar(64), habitat_name varchar(128) n ot n ull u nique , population number, primary key (genus, species)); Option 2: create table Animals_Inhabit_Habitats ( genus varchar(64) n ot n ull , species varchar(64) n ot n ull , habitat_name varchar(128) primary key, population number, u nique (genus, species)); (b) genus% ANIMALS' popula.on% name% inhabit' species% HABITATS' Solution: The ER specifies a many-to-many relationship set between Animals and Habitats: an Animal may inhabit 0, 1 or several Habitats, and a Habitat may be inhabited by 0, 1 or several Animals. There is only one correct way to implement this relationship set: using 3 create table statements, one for each entity set, and one for the relationship set, with the appropriate primary and foreign keys. create table Animals ( genus varchar(64), species varchar(64), primary key (genus, species) ); create table Habitats ( name varchar(128) primary key ); create table Animals_Inhabit_Habitats ( genus varchar(64), species varchar(64), habitat_name varchar(128), population number, primary key (genus, species, habitat), foreign key (genus, species) references Animals(genus, species), foreign key (habitat_name) references Habitats(name) ); (c) 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 must inhabit exactly one Habitat (both key and participation on Animals), and a Habitat must be inhabited by at least 1 Animal (participation but no key constraint on Habitats). As we saw in class, it will not be possible to enforce the participation constraint over the relationship set Habitats. Therefore, our implementation only enforces the key and participation constraints over the entity set Animals. The best way to represent this relationship set with constraints is to create two tables: one for Habitats and one for Animals and the relationship set Inhabit. Key constraint on the entity set Animals is enforced in our implementation by making (genus, species) the primary key in Animals_Inhabit. Participation on the entity set Animals is enforced in our implementation by making hab...
View Full Document

Ask a homework question - tutors are online