SQL

CIS  363     Spring  2019  

CIS  363  –  Enterprise  Database  Management    

Assignment  #1  –  The  Structured  Query  Language  (SQL)  

Due  Date:  Sunday  February  17,  2019  (by  midnight)  

Grading:  The  weight  of  this  assignment  is  10%  of  the  course  grade    

Consider  the  following  database  schema,  which  is  concerned  with  World  War  II  capital  ships.   It  involves  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   relation   Classes   records   the   name   of   the   class,   the   type   (’bb’   for   battleship  or  ’be’  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  Outcomes  gives  the  result  (sunk,  damaged,  or  ok)  for  each   ship  in  each  battle.  

Some  sample  data  for  these  relations  is  shown  on  the  last  two  pages  of  this  document.  

Furthermore,   assume   that   the   data   types   of   the   above   attributes   are   consistent   with   the   following  Standard  SQL  declarations:  

CREATE TABLE Classes (

class CHAR(20),

type CHAR(5),

country CHAR(20),

numGuns INTEGER,

bore DECIMAL(3,1),

displacement INTEGER

);

CREATE TABLE Ships (

name CHAR(30),

class CHAR(20),

launched INTEGER

);

CREATE TABLE Battles (

name CHAR(30),

date DATE

);

CREATE TABLE Outcomes (

ship CHAR(30),

battle CHAR(30),

result CHAR(10)

);

CIS  363     Spring  2019  

 

 

1) Using  MySQL,   create   the   above   four   relations   and   populate   them  with   the   sample   data   given  on  the  last  two  pages  of  this  document.  

2) Write  the  following  queries  and  apply  them  to  the  above  database:  

a) Find   the   names   of   all   ships   launched   prior   to   1918,   but   call   the   resulting   column   ShipName.  

b) Find  the  names  of  ships  sunk  in  battle  and  the  name  of  the  battle  in  which  they  were   sunk.  

c) Find  the  names  of  all  ships  that  begin  with  the  letter  “R”.  

d) Find   the  names  of  all   ships  whose  name  consists  of   three  or  more  words   (e.g.,  King   George  V).  

e) Find  the  ships  heavier  than  35,000  tons.  

f) List  the  name,  displacement,  and  number  of  guns  of  the  ships  engaged  in  the  battle  of   Guadalcanal.  

g) List  all  the  ships  mentioned  in  the  database.  (Remember  that  all  these  ships  may  not   appear  in  the  Ships  relation.)  

h) Find  the  names  of  the  ships  with  a  16-­‐inch  bore.  

i) Find  the  battles  in  which  ships  of  the  Kongo  class  participated.  

j) Find  the  countries  whose  ships  had  the  largest  number  of  guns.     Create  one  pdf  document  demonstrating  your  answers  to  the  above  ten  MySQL  queries  and   show  the  results  of  those  queries  when  applied  to  the  sample  data  given  below.  Submit  this   pdf  file  into  the  “Assignment  1  Drop  Box”  of  the  course’s  Canvas  website.    

CIS  363     Spring  2019  

CIS$132( ( Summer(2015(

(

( (    

CIS  363     Spring  2019   CIS$132( ( Summer(2015(

( (d)(Sample data for relation Ships

(