$--$ nasa.sql

$--$

$--$

$--$ This database describes the United States space program, prior to the

$--$ space shuttle.

$--$

$--$ If you are concerned that NASA history is not relevant

$--$ to a Business$-$oriented student, you are wrong: Forom a business perspective

$--$ these are just projects, and the astronauts are "just" workers assigned to

$--$ one or more tasks $($ie$.$ missions$).$

$--$

$--$ Prior to the shuttle, NASA ran five different "manned" space projects:

$--$

$--$ Project Mercury $(1961-63)$: The six missions of this project carried a

$--$ single astronaut, the 'pilot'. The first two missions were sub$-$orbital

$--($missionType $=\text{'}$SO$\text{'})$ and four missions that took the astronauts into

$--$ Earth orbit $($missionType $=\text{'}$EO$\text{'}).$

$--$

$--$ Project Gemini $(1965-66)$: Each of the $10$ manned missions of this

$--$ program carried two astronauts, the 'Commander' and 'Pilot', into Earth

$--$ orbit $($missionType $=\text{'}$EO$\text{'}).($The first manned mission was number $3,$ the

$--$ first two were unmanned tests.$)$

$--$

$--$ Project Apollo $(1967-1972)$: Each of these missions carried three

$--$ astronauts, the 'Commander', 'Command Module Pilot', and 'Lunar Module

$--$ Pilot. Most missions used two spacecraft, the 'Command Service Module'

$--($craftType$=\text{'}$CSM$\text{'})$ and the 'Lunar Module' $($craftType$=\text{'}$LM$\text{'}).$ In most

$--$ missions $($missionType $=\text{'}$LL$\text{'}),$ the Command Module Pilot would stay in

$--$ lunar orbit in the CSM$,$ while the Commander and Lunar Module Pilot

$--$ descended in the LM$.$ Apollo $11$ was the first mission to land on the

$--$ moon. Earlier missions were test flights. Apollo $1$ did not fly

$--($hence the missionType of this mission has been left null$).$ The crew of

$--$ Apollo $1$ was killed while testing the spacecraft on the launch pad.

$--$ The first manned Apollo mission was Apollo $7.$ It tested a CSM in earth

$--$ orbit $($missionType $=\text{'}$EO$\text{'})$ and did not have a lunar module. Apollo $8$

$--$ tested a CSM in lunar orbit $($missionType $=\text{'}$LO$\text{'})$ and also did not have

$--$ a lunar module. Apollo $9$ tested a LM in Earth orbit, and Apollo $10$

$--$ tested a LM in lunar orbit. From Apollo $11$ onward, all missions landed

$--$ on the moon $($missionType $=\text{'}$LL$\text{'}),$ with one exception, Apollo $13,$ which

$--$ aborted its mission on the way to the moon, and looped round the moon

$--$ without entering orbit $($a "Fly By$",$ missionType$=\text{'}$LF$\text{'}).$

$--$

$--$ Project Skylab $(1973)$: Skylab $1$ was the launch of the first US space

$--$ station, in Earth orbit. Each of Skylab missions $2$ to $4$ took a

$--$ three$-$man crew to the Skylab station in an Apollo CSM$.$ The third member

$--$ of each crew was called the scientist $($not Lunar Module Pilot$).$

$--$

$--$ Project Apollo$-$Soyuz $(1975)$: A three$-$man crew in an Apollo CSM docked

$--$ with a Russian Soyuz spacecraft in Earth orbit. The third member of

$--$ that crew was called the Docking Module Pilot $($not Lunar Module Pilot$).$

DROP table NASA$\_$Assigned;

DROP table NASA$\_$SpaceCraft;

DROP table NASA$\_$Mission;

DROP table NASA$\_$Astronaut;

DROP table NASA$\_$Project;

Create table NASA$\_$Project

$($

projectName char$(15),$

CONSTRAINT NASA$\_$ProjectsPK PRIMARY KEY $($projectName$)$

$)$;

Create table NASA$\_$Astronaut

$($

astroNo integer,

astroName char$(25),$

birth integer,

death integer,

CONSTRAINT NASA$\_$AstronautPK PRIMARY KEY $($astroNo$)$

$)$;

Create table NASA$\_$Mission

$($

projectName char$(15),$

missionNo integer,

missionType char$(2),$

launchYear integer,

launchMonth integer,

launchDay integer,

days integer,

hours integer,

minutes integer,

description char$(80),$

CONSTRAINT NASA$\_$MissionPK PRIMARY KEY $($projectName$,$ missionNo$),$

CONSTRAINT NASA$\_$MissionFK FOREIGN KEY $($projectName$)$ REFERENCES NASA$\_$Project

$)$;

Create table NASA$\_$Assigned

$($

projectName char$(15),$

missionNo integer,

astroNo integer,

role char$(25),$

CONSTRAINT NASA$\_$AssignedPK PRIMARY KEY $($projectName$,$ missionNo, astroNo$),$

CONSTRAINT NASA$\_$AssignedFK$\_$Mission FOREIGN KEY

$($projectName$,$ missionNo$)$ REFERENCES NASA$\_$Mission,

CONSTRAINT NASA$\_$AssignedFK$\_$Astronaut FOREIGN KEY

$($astroNo$)$ REFERENCES NASA$\_$Astronaut

$)$;

Create table NASA$\_$SpaceCraft

$($

projectName char$(15),$

missionNo integer,

craftType char$(10),$

craftName char$(20),$

CONSTRAINT NASA$\_$SpaceCraftPK PRIMARY KEY $($projectName$,$ missionNo, craftType$),$

CONSTRAINT NASA$\_$SpaceCraftFK FOREIGN KEY

$($projectName$,$ missionNo$)$ REFERENCES NASA$\_$Mission

$)$;

INSERT INTO NASA$\_$Project VALUES$(\text{'}$Mercury$\text{'})$;

INSERT INTO NASA$\_$Project VALUES$(\text{'}$Gemini$\text{'})$;

INSERT INTO NASA$\_$Project VALUES$(\text{'}$Apollo$\text{'})$;

INSERT INTO NASA$\_$Project VALUES$(\text{'}$Skylab$\text{'})$;

INSERT INTO NASA$\_$Project VALUES$(\text{'}$Apollo$-$Soyuz'$)$;

Insert into NASA$\_$Astronaut VALUES$(1,$'Aldrin, Buzz',$1930,$NULL$)$;

Insert into NASA$\_$Astronaut VALUES$(2,$'Anders, William',