INFO210_HW5_Solution

Key constraint on animals is implemented by making

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: on on Animals), and a Habitat may be inhabited by at most one Animal (key constraint but no participation on Habitats). There are two equivalent ways of implementing these business rules, both are given below. In the first option, we use one table to represent Animals, and one table to represent Hbitats and inhabit. Key constraint on Animals is implemented by making (genus, species) a primary key on Animals_Inhabit. Key constraint on Habitats is implemented by making (name) a primary key on Habitats, and by designating habitat_name unique in Animals_Inhabit. Habitats with no animals in them will have a tuple in Habitats but their habitat name will not appear in Animals_Inhabit. Animals without a habitat will correspond to tuples in Animals_Inhabit in which habitat_name is null. Option 2 is explained similarly. Option 1: create table Habitats ( name varchar(128) p rimary k ey ); create table Animals_Inhabit ( genus varchar(64), species varchar(64), habitat_name varchar(128) u nique , population number, p rimary k ey (genus, species), f oreign k ey (habitat_name) r eferences Habitats(name) ); Option 2: create table Animals ( genus varchar(64), species varchar(64), p rimary k ey (genus, species), ); create table Habitats_Inhabit ( habitat_name varchar(128) p rimary key , genus varchar(64), species varchar(64), population number, u nique (genus, species), f oreign k ey (genus, species) r eferences Animals(genus, species)); Authors (first, last, country, born) Books (title, author, year) Nobel_Winners (author, year) Authors first last country born Haruki Murakami Japan 1949 Kindzaburo Oe Japan 1935 Yasunari Kawabata Japan 1899 Yukio Mishima Japan 1925 Ivo Andric Yugoslavia 1892 Mesa Selimovic Yugoslavia 1910 Danilo Kis Yugoslavia 1935 Julio Cortazar Argentina 1914 Mario Vargas Llosa Peru 1936 Jorge Borges Argentina 1899 Gabriel Garcia Marquez Colombia 1927 Books !tle author year Captain Pantoja and the Special Service Vargas Llosa 1978 The City and the Dogs Vargas Llosa 1963 Confessions of a Mask Mishima 1949 Patriotism Mishima 1961 Acts of Worship Mishima 1965 The Bridge on the Drina Andric 1945 The Damned Yard Andric Death and the Dervish Selimovic Axolotl Cortazar The Library of Babel Borges Labyrinths Borges Nobel_Winn...
View Full Document

Ask a homework question - tutors are online