CSI 710: Scientific Databases

1
CSI 710 Scientific Databases Group Assignment
1 Project Rocket Science Database
Project   Group Project 10 (Britzolakis
Georgios Donato Davide)
2

• Specifications as required by the Assignment
  (and using the URL
• link
  http//science.ksc.nasa.gov/shuttle/missions/missi
  ons.html)
• Implementation of the project using MS SQL
  Server 2000
• Implementation of the queries using T-SQL
  (Transact Structure
• Query Language) as precompiled Stored
  procedures because
• 1. better performance (no time spent to compile
  the queries)
• 2. easy code development (running only the
  script exec procedure Name)

3
Database Diagram Table Specification 1
Mission
Vehicle
Rocket
Shuttle
Manufacturer
4
Database Diagram Table Specification 2
Commander
Co-pilot
Scientific Member
International Scientist
Astronomer
5
Database Diagram Table Specification 3
Satellite
Data from satellite
Experiment
6
Database Diagram Table Specification 4
Co-PI
PI
Graduate Research Assistant
Research Associate
Post-Doc
7
Final Schema
8
The Queries

• 1. List the names and the affiliations of
  principal Investigators for all experiments
• --The selection of the required columns.
• select Exp_Name, PI_ID, PI_FNAME, PI_LNAME,
  PI_HOME_INST, PI_PHONE, PI_FAX, PI_EMAIL, PI_SSN,
  
• PI_INST_ID, PI_TITLE, PI_LOC_PHONE
• from PI
• --The join between the PI table and the
  experiment table
• inner join Experiment on Experiment.Exp_PI_IDPI.P
  I_ID
• Which experiments were launched aboard the
  Atlantis during 1997?
• --The selection of the required columns
• select Exp_Name
• from Experiment
• --The join between the Mission table and the
  experiment table
• inner join Mission on Experiment.Exp_missionMissi
  on.Mission_ID

9

• List the crew members for the shuttle that
  launched the Hubble Space Telescope
• --The selection of the required columns
• select Commander_FNAME, Commander_LNAME,
  CoPilot_FNAME, CoPilot_LNAME,
• ScMemb_FNAME, ScMemb_LNAME, IntSc_FNAME,
  IntSc_LNAME, Astr_FNAME, Astr_LNAME
• from Shuttle
•  
• --The join between the shuttle table and the
  commander table
• inner join Commander on Shuttle.Shuttle_Commander_
  IDCommander.Commander_ID
• --The join between the Shuttle table and the
  Co-Pilot table
• inner join CoPilot on Shuttle.Shuttle_CoPilot_IDC
  oPilot.CoPilot_ID
• --The join between the shuttle table and the
  scientific member table
• inner join ScMemb on Shuttle.Shuttle_ScMemb_IDScM
  emb.ScMemb_ID
• --The join between the shuttle table and the
  International Scientist table
• inner join IntSc on Shuttle.Shuttle_IntSc_IDIntSc
  .IntSc_ID
• --The join between the shuttle table and the
  Astronaut table
• inner join Astr on Shuttle.Shuttle_Astr_IDAstr.As
  tr_ID
• --The join between the shuttle table and the
  vehicle table
• inner join vehicle on Shuttle.Shuttle_IDVehicle_I
  D

10
4. List the missions, crew, and dates for repairs
on the Hubble Space Telescope --The selection
of the required columns select Mission_Name,
Commander_FNAME, Commander_LNAME, CoPilot_FNAME,
CoPilot_LNAME, ScMemb_FNAME, ScMemb_LNAME,
IntSc_FNAME, IntSc_LNAME, Astr_FNAME, Astr_LNAME,
Sat_Rep_Start, Sat_Rep_Finish from
Mission   --The join between the satellite table
and the mission table inner join Satellite on
Mission.Mission_IDSatellite.Sat_Miss_ID --The
join between the mission table and the vehicle
table inner join Vehicle on Mission.Mission_IDVe
hicle.Vehicle_Mission_ID --The join between the
vehicle table and the shuttle table inner join
Shuttle on Vehicle.Vehicle_IDShuttle.Shuttle_ID -
-The join between the shuttle table and the
commander table inner join Commander on
Shuttle.Shuttle_Commander_IDCommander.Commander_I
D  --The join between the shuttle table and the
Co-Pilot table inner join CoPilot on
Shuttle.Shuttle_CoPilot_IDCoPilot.CoPilot_ID --Th
e join between the shuttle table and the
Scientific member table inner join ScMemb on
Shuttle.Shuttle_ScMemb_IDScMemb.ScMemb_ID --The
join between the shuttle table and the
international Scientist table inner join IntSc on
Shuttle.Shuttle_IntSc_IDIntSc.IntSc_ID --The
join between the Shuttle table and the Astronaut
table inner join Astr on Shuttle.Shuttle_Astr_ID
Astr.Astr_ID   --The selection criterion where
Sat_Name'Hubble Space Telescope'
11

• List the shuttle launches, and mission names for
  which Terry Willcutt was a crew
• member
• --The selection of the required columns
• select Shuttle_Name, Mission_Name
• from Mission
•  
• --The join between the mission table and the
  vehicle table
• inner join Vehicle on Mission.Mission_IDVehicle.V
  ehicle_Mission_ID 
• --The join between the shuttle table and the
  vehicle table
• inner join Shuttle on Vehicle.Vehicle_IDShuttle.S
  huttle_ID  
• --The join between the Shuttle table and the
  commander table
• inner join Commander on Shuttle.Shuttle_Commander_
  IDCommander.Commander_ID  
• --The join between the Shuttle table and the
  Co-Pilot table
• inner join CoPilot on Shuttle.Shuttle_CoPilot_IDC
  oPilot.CoPilot_ID 
• --The join between the Shuttle table and the
  Scientific member table
• inner join ScMemb on Shuttle.Shuttle_ScMemb_IDScM
  emb.ScMemb_ID
• --The join between the Shuttle table and the
  International Scientist table
• inner join IntSc on Shuttle.Shuttle_IntSc_IDIntSc
  .IntSc_ID  

12
6. List the missions for which the shuttle
commander was female --The selection of the
required columns select Mission_Name from
Mission   --The join between the mission table
and the Vehicle table inner join Vehicle on
Mission.Mission_IDVehicle.Vehicle_Mission_ID --Th
e join between the Shuttle table and the vehicle
table inner join Shuttle on Vehicle.Vehicle_IDShu
ttle.Shuttle_ID  --The join between the Shuttle
table and the Commander table inner join
Commander on Shuttle.Shuttle_Commander_IDCommande
r.Commander_ID   --The selection Criterion where
Commander_SEX'FEMALE'
13
7. List shuttle missions where crew members
include Russian, Japanese or French citizens
--The selection of the required columns select
Mission_Name from Mission   --The join between
the mission table and the Vehicle table inner
join Vehicle on Mission.Mission_IDVehicle.Vehicle
_Mission_ID  --The join between the Vehicle table
and the Shuttle table inner join Shuttle on
Vehicle.Vehicle_IDShuttle.Shuttle_ID  --The join
between the Shuttle table and the Commander
table inner join Commander on Shuttle.Shuttle_Comm
ander_IDCommander.Commander_ID --The join
between the Shuttle table and the Co-Pilot
table inner join CoPilot on Shuttle.Shuttle_CoPilo
t_IDCoPilot.CoPilot_ID --The join between the
Shuttle table and the Scientific Member
table inner join ScMemb on Shuttle.Shuttle_ScMemb_
IDScMemb.ScMemb_ID --The join between the
Shuttle table and the International Scientist
table inner join IntSc on Shuttle.Shuttle_IntSc_ID
IntSc.IntSc_ID --The join between the Shuttle
table and the Astronaut table inner join Astr on
Shuttle.Shuttle_Astr_IDAstr.Astr_ID   --The
Selection criteria where Commander_Nat'Russian'
or Commander_Nat'Japanese' or Commander_Nat'Fren
ch or (CoPilot_Nat'Russian' or
CoPilot_Nat'Japanese' or CoPilot_Nat'French')
or ScMemb_Nat'Russian' or ScMemb_Nat'Japanese'
or ScMemb_Nat'French or IntSc_Nat'Russian' or
IntSc_Nat'Japanese' or IntSc_Nat'French
or Astr_Nat'Russian' or Astr_Nat'Japanese' or
Astr_Nat'French'
14
8. List shuttle missions involved in the
international Space Station --The selection of
the reqired columns select Mission_Name from
Mission   --The join between the Mission table
and the Vehicle table inner join Vehicle on
Mission.Mission_IDVehicle.Vehicle_Mission_ID --Th
e join between the Shuttle table and the Vehicle
table inner join Shuttle on Vehicle.Vehicle_IDShu
ttle.Shuttle_ID --The selection criteria where
Shuttle.Shuttle_Launch_Place'International Space
Station'