Assignment 2 Hints

1
Assignment 2 Hints
2
2.5

• List the name, displacement, and number of guns
  of the ships engaged in the battle of
  Guadalcanal.
• If there were no dangling tuples then we would
  easily do
• SELECT ship, displacement, numGuns
• FROM Outcomes o, Ships s, Classes c
• WHERE battle'Guadalcanal' AND o.ships.name AND
  s.classc.class
• However, what about ships mentioned in Outcomes
  but not in Ships or Classes?
• Solution we need to use outerjoins.

3
2.5 (Continued)

• SELECT ship, displacement, numGuns
• FROM (SELECT ship, class
• FROM Outcomes LEFT OUTER JOIN Ships
  ON shipname
• WHERE battle'Guadalcanal')
• NATURAL LEFT OUTER JOIN Classes

4
2.8

• Find those ships that "lived to fight another
  day" they were damaged in one battle, but later
  fought in another.
• To make things easier you can create first a
  virtual view
• CREATE VIEW OutcomesWithDate AS
• SELECT Outcomes.ship, Outcomes.battle,
  Outcomes.result, Battles.date_fought
• FROM Outcomes, Battles
• WHERE Outcomes.battleBattles.name
• Then write the query
• SELECT ...
• FROM OutcomesWithDate X, OutcomesWithDate Y
• WHERE ...

5
2.14

• Find for each class the year in which the first
  ship of that class was launched.
• We need to be careful here. There might be
  classes for which we don't know yet any ship. We
  shouldn't loose such classes from the output.
• CLASS MIN(LAUNCHED)
• --------------- -------------
• Yamato 1941
• North Carolina 1941
• Revenge 1916
• Renown 1916
• Kongo 1913
• Tennessee 1921
• Bismarck

6
2.15

• Find for each class with at least three ships the
  number of ships of that class sunk in battle.
• Result
• CLASS SUNK_SHIPS
• --------------- ----------
• Iowa 0
• Revenge 0
• Kongo 1

7
5.1

• Add the following constraint Every class
  mentioned in Ships must be mentioned in Classes.
• ALTER TABLE Classes ADD CONSTRAINT classes_pk
  PRIMARY KEY(class)
•  
• ALTER TABLE Ships ADD CONSTRAINT
  ship_to_classes_fk
• FOREIGN KEY(class) REFERENCES Classes(class)
• EXCEPTIONS INTO Exceptions
•  
• This doesnt go through. There are violating
  tuples in Ships. Lets see them.
•  
• SELECT Ships., constraint
• FROM Ships, Exceptions
• WHERE Ships.rowid Exceptions.row_id

8
5.1 (Continued)

• NAME CLASS LAUNCHED
  CONSTRAINT
• --------------- --------------- ----------
  ------------------------------
• Iowa Iowa 1943
  SHIP_TO_CLASSES_FK
• Missouri Iowa 1944
  SHIP_TO_CLASSES_FK
• New Jersey Iowa 1943
  SHIP_TO_CLASSES_FK
• Tennesse Tennesse 1920
  SHIP_TO_CLASSES_FK
• Wisconsin Iowa 1944
  SHIP_TO_CLASSES_FK
• Lets delete them.
•  
• DELETE FROM Ships
• WHERE class IN (
• SELECT class
• FROM Ships, Exceptions
• WHERE Ships.rowid Exceptions.row_id
• )
•  
• Now, we can set the constraint.
• ALTER TABLE Ships ADD CONSTRAINT
  ship_to_classes_fk
• FOREIGN KEY(class) REFERENCES Classes(class)

9
5.6

• No ship can be in battle before it is launched.
•  
• Use a view WITH CHECK OPTION for this.
• Also observe the TO_NUMBER function which
  converts a string into a number (e.g. '2008' into
  2008).