CMSC 461: Database Management Systems Spring 07Homework 2Instructor: Parag Namjoshi Due: March 8, 2007Homework must be solved and written individually using only the textbook.I. (60 points)1. Solve exercise 2.1 (page 71)2. Solve exercise 2.5 (page 72)3. Solve exercise 2.8 (page 73)4. Solve exercise 2.11 (page 73)II. (40 points) (Source: Widom, Ullman) Consider a database containing informationabout World War II capital ships. It involves the following relations:Class(class, type, country, numGuns, bore, displacement)Ship(name, class, launched)Battle(name, date)Outcome(ship, battle, result)Ships are built in classes from the same design. The relation Class records the name ofthe class, the type (bb for battleship and bc for battle cruiser), the country that built theship, the number of main guns , the bore (d iameter of the gun barrel, in inches) of the mainguns, and the displacement (weight, in tons). Relation Ship records the name of the ship,the name of its class, and the year in which the ship was launched. Relation Battle givesthe name and date of battles involving these ships, and relation Outcome gives the result(sunk, damaged, or ok) for each ship in each battle. Write relational algebra expressions toanswer the following queries. Sample data is included at the end. Be sure to examine thedata before w riting the queries.1. Find the ships sunk in the battle of the North Atlantic.2. The treaty of Washington in 1921 prohibited capital ships heavier than 35,000 tons.List the ships that violated the treaty of Washington.3. List the name, displacement, and number of guns for each ship engaged in the battleof Guadalcanal.4. Find those countries that had both battleships and battle cruisers.Homework 2-I5. Find those ships that lived to fight another day; they were damaged in one battle,but later fought in another.6. Find the classes th at had exactly two ships as members of that class.7. Find those s hips that fought only in th e battles that California fought in .8. Find those s hips that fought in every battle that California fought in.Figure 1: Sample Data (Source: Widom, Ullman)Figure 2: Sample Data (Source: Widom, Ullman)Homework 2-IIFigure 3: Sample Data (Source: Widom, Ullman)Homework 2-IIIFigure 4: Sample Data (Source: Widom, Ullman)Homework
View Full Document
Unlocking...