1 / 9

Relational Algebra (Continued)

Relational Algebra (Continued). Here is a running example concerning World War II capital ships. It involved the following relations: classes ( class , type, country, numGuns, bore, displacement) ships ( name , class , launched) battles( name , date) outcomes ( ship , battle , result)

kieve
Download Presentation

Relational Algebra (Continued)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Relational Algebra(Continued)

  2. Here is a running example concerning World War II capital ships. It involved the following relations: classes (class, type, country, numGuns, bore, displacement) ships (name, class, launched) battles(name, date) outcomes (ship, battle, result) Ships are built in “classes” from the same design, and the class is usually named for the first ship of that class, the type (bb for battleship or bc for battlecruiser), the country that built the ship, the number of main guns, the bore (diameter of the gun barrel, in inches) of the main guns, and the displacement (weight in tons). Relation Ships records the name of the ship, the name of its class, and the year in which the ship was launched. Relation Battles gives the name and date of battles involving these ships, and relation Outcome gives the result (sunk, damaged, or OK) for each ship in each battle. Query Requests: a) Give the class names and countries of the classes that carried guns of at least 16-inch bore. b) Find the ships launched prior to 1921 c) Find the ships sunk in the battle of the North Atlantic d) The treaty of Washington in 1921 prohibited capital ships heavier than 35,000 tons. List the ships that violated the treaty of Washington

  3. Here is a running example concerning World War II capital ships. It involved the following relations: classes (class, type, country, numGuns, bore, displacement) ships (name, class, launched) battles(name, date) outcomes (ship, battle, result) Ships are built in “classes” from the same design, and the class is usually named for the first ship of that class, the type (bb for battleship or bc for battlecruiser), the country that built the ship, the number of main guns, the bore (diameter of the gun barrel, in inches) of the main guns, and the displacement (weight in tons). Relation Ships records the name of the ship, the name of its class, and the year in which the ship was launched. Relation Battles gives the name and date of battles involving these ships, and relation Outcome gives the result (sunk, damaged, or OK) for each ship in each battle. Query Requests: e) List the name, displacement, and number of guns of the ships engaged in the battle of Guadalcanal f) List all the capital ships mentioned in the database.

  4. Here is a running example concerning World War II capital ships. It involved the following relations: classes (class, type, country, numGuns, bore, displacement) ships (name, class, launched) battles(name, date) outcomes (ship, battle, result) Ships are built in “classes” from the same design, and the class is usually named for the first ship of that class, the type (bb for battleship or bc for battlecruiser), the country that built the ship, the number of main guns, the bore (diameter of the gun barrel, in inches) of the main guns, and the displacement (weight in tons). Relation Ships records the name of the ship, the name of its class, and the year in which the ship was launched. Relation Battles gives the name and date of battles involving these ships, and relation Outcome gives the result (sunk, damaged, or OK) for each ship in each battle. Query Requests: g) Find the classes that had only one ship as a member of that class h) Find those countries that had both battleships and battlecruisers i) find those ships that “lived to fight another day”; they were damaged in one battle, but later fought in another.

  5. Answers a) Πclass, country (bore >= 16(classes)) b) launched < 1921(ships)

  6. Answers c) Πship (result = ‘sunk’ .and. battle = ‘North Atlantic’ (outcomes)) Πship (result = ‘sunk’ (outcomes)   battle = ‘North Atlantic’ (outcomes)) Πship (result = ‘sunk’ ( battle = ‘North Atlantic’ (outcomes))) Πship (battle = ‘North Atlantic ( result = ‘sunk’ (outcomes)))

  7. Answers d) Πname (launched > 1921(ships) displacement > 35,000 (classes)) Πname(Πname, class (launched > 1921(ships)) Πclass(displacement > 35,000 (classes)) e) Πname, displacement, numGuns( classes (ships name = ship (battle = ‘Guadalcanal’ (outcomes))))

  8. Answers f) Πname (ships) g) Πclass (classes) - Πclass (ships ships.class = ship2.class .and. ships.name <> ship2.name ρShip2 (ships))

  9. Answers h) Πcountry (type = ‘bb’ (classes))  Πcountry ( type = ‘bc’ (classes)) i) ΠR1.ship (( ρR1(result = ‘damaged’ (outcomes) battle = name battles)) R1.ship = R2.ship .and. R1.date < R2.dateρR2(outcomes battle = name battles) )

More Related