INFO210_HW5_Solution

# Smooth air company operates multiple daily flights

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: lows: a mother and a father are part of exactly one small family, while a child is part of at most one small family. name% INCORRECT' name% MOTHER' are_family' FATHER' name% CHILD' name% MOTHER' CORRECT' are_family' name% FATHER' name% CHILD' (d) Draw an ER diagram that correctly represents the following business rules. Smooth Air Company operates multiple daily flights between certain pairs of cities. Each flight has an origin city, a destination city and a scheduled departure time. We want to keep track of all flights between a given pair of cities on a particular day. You may assume that each city is uniquely identified by its name. Solution: there are 2 ways to draw this ER diagram. name% name% name% ﬂight) ORIGIN) DESTINATION) CITY) origin% des&na&on% ﬂight) date% &me% date% &me% DEPARTURE) DEPARTURE) Part 3 (30 points): Translating ER models to relational schemas Consider ER diagrams below. Write SQL statements (create table) that implement the constraints specified by the ER diagram below. Create as many tables as required. Briefly explain which constraints are captured in your relational implementation, and in what way. If a constraint cannot be implemented, state that explicitly in your explanation. You will not receive full credit without an explanation. (Attribute population refers to the number of animas in a habitat.) (a) genus% ANIMALS' species% popula.on% name% inhabit' HABITATS' Solution: The ER diagram states that each Animal inhabits exactly one Habitat, and that each Habitat is inhabited by exactly one Animal. There are 2 correct solutions, each consisting of exactly one create table statement. In the first solution, the key constraint on Animals is encoded by making (genus, species) the primary key, and participation on Animals is encoded by making habitat_name not null. Key constraint on Habitats is encoded by making habitat_name not null unique (it’s the second candidate key on this table), while participation on Habitats is encoded in (genus, species) being the primary key of this table, which makes them automatically not null. The second solution is very similar, and has an analogous explanation. The only difference is that habitat_name is the primary key, and (genus, species) is the second candidate key on this table. Option 1: create table Animals_Inhabit_Habitats ( genus varchar(64),...
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