Moving RDF to a Relational Database

CREATE TABLE Discipline (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  label VARCHAR(50) UNIQUE,
  labelword VARCHAR(30) UNIQUE
)

# Create a Discipline table
#   with a primary key,
#   and the label text.
#   Why throw away a URL-sanitized label form?

mysql -u spaaaceman --local-infile SPAAACE
mysql> LOAD DATA LOCAL INFILE 'Discipline.tsv'
 INTO TABLE Discipline FIELDS
 TERMINATED BY '\t'
 OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES
 (labelword, label);
Query OK, 16 rows affected, 1 warning (0.00 sec)
Records: 16  Deleted: 0  Skipped: 0  Warnings: 1

# Start MySQL client in a mood to honor LOAD DATA LOCAL.
# Load from our freshly-created Discipline.tsv.
#  Populate the new Discipline table.
#  values separated by TAB.
#  Lines end with newline.
#  Strings enclosed in ""s.
#  Skip the header with the variable names.
#  Populate the labelword and name attributes;
#    id's AUTO_INCREMENT will handle the primary key.
# Get a thumbs-up back from MySQL.

CREATE TABLE proto_Launch (
  launchword VARCHAR(10),
  date DATE,
  siteword VARCHAR(40),
  vehicDesc VARCHAR(80),
  UNIQUE KEY (launchword, date, siteword, vehicDesc)
);
LOAD DATA LOCAL INFILE 'proto_Launch.tsv'
 INTO TABLE proto_Launch FIELDS
 TERMINATED BY '\t'
 OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES
 (launchword, date, siteword, vehicDesc);
Query OK, 5064 rows affected, 18 warnings (0.08 sec)
Records: 5064  Deleted: 0  Skipped: 0  Warnings: 18
CREATE TABLE Launch (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  launchword VARCHAR(10),
  date DATE,
  site INT UNSIGNED NOT NULL REFERENCES LaunchSite(id),
  vehicDesc VARCHAR(80),
  UNIQUE KEY (launchword, date, site, vehicDesc)
);
INSERT INTO Launch (launchword, date, site, vehicDesc)
  SELECT proto_Launch.launchword, proto_Launch.date,
         LaunchSite.id, proto_Launch.vehicDesc
    FROM proto_Launch
   INNER JOIN LaunchSite
     ON (LaunchSite.labelword=proto_Launch.siteword);
Query OK, 5064 rows affected (0.10 sec)
Records: 5064  Duplicates: 0  Warnings: 0

#
# Some launchwords are repeated, e.g. 1963-030
# These are very likely errors.
#
# 
# This is the tightest unique supported by the data.
#
#
#
# 
# 
# 
# 
#
# There are entries in the SPAAAAACE database with
#   empty date strings.

ALTER TABLE Launch RENAME Launch_dupes;
CREATE TABLE Launch (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  launchword VARCHAR(10) UNIQUE,
  date DATE,
  site INT UNSIGNED NOT NULL REFERENCES LaunchSite(id),
  vehicDesc VARCHAR(80),
  UNIQUE KEY (launchword, date, site, vehicDesc)
);
INSERT INTO Launch (SELECT * FROM Launch_dupes GROUP BY launchword);Query OK, 4937 rows affected (0.08 sec)
Records: 4937  Duplicates: 0  Warnings: 0

CREATE TABLE Mission (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(50) UNIQUE,
  titleword VARCHAR(30) UNIQUE
);
LOAD DATA LOCAL INFILE 'Mission.tsv'
 INTO TABLE Mission FIELDS
 TERMINATED BY '\t'
 OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES
 (titleword, title);
Query OK, 11 rows affected (0.01 sec)
Records: 11  Deleted: 0  Skipped: 0  Warnings: 0

# 
# 
# 
# 
# 
# 
# 
# 
# 
# 
# 
# 
# 11 records!? That's right.
# This class includes only the fancy Apollo missions.

CREATE TABLE proto_Spacecraft (
  craftword   VARCHAR(10) UNIQUE,
  launchword  VARCHAR(10),
  missionword VARCHAR(10),
  designator  VARCHAR(10) UNIQUE,
  name        VARCHAR(60),
  agency      VARCHAR(30),
  mass        FLOAT,
  homepage    VARCHAR(80) UNIQUE,
  craftDesc   BLOB
);
LOAD DATA LOCAL INFILE 'proto_Spacecraft.tsv'
 INTO TABLE proto_Spacecraft FIELDS
 TERMINATED BY '\t'
 OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES
 (craftword, launchword, missionword, designator,
         name, agency, mass, craftDesc, homepage);
Query OK, 4964 rows affected (0.22 sec)
Records: 4964  Deleted: 0  Skipped: 0  Warnings: 0
CREATE TABLE Spacecraft (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  craftword  VARCHAR(10) UNIQUE,
  launch     INT UNSIGNED NOT NULL REFERENCES Launch(id),
  mission    INT UNSIGNED REFERENCES Mission(id),
  designator VARCHAR(10) UNIQUE,
  name       VARCHAR(60),
  agency     VARCHAR(30),
  mass       FLOAT,
  homepage   VARCHAR(80) UNIQUE,
  craftDesc  BLOB
);
INSERT INTO Spacecraft (craftword, launch, mission, designator,
                        name, agency, mass, homepage, craftDesc)
  SELECT proto_Spacecraft.craftword, Launch.id, Mission.id,
         proto_Spacecraft.designator, proto_Spacecraft.name,
         proto_Spacecraft.agency, proto_Spacecraft.mass,
         proto_Spacecraft.homepage, proto_Spacecraft.craftDesc
    FROM proto_Spacecraft
   INNER JOIN Launch
     ON (Launch.launchword=proto_Spacecraft.launchword)
   LEFT OUTER JOIN Mission
     ON (Mission.titleword=proto_Spacecraft.missionword);
Query OK, 4954 rows affected (0.19 sec)
Records: 4954  Duplicates: 0  Warnings: 0

#
# Some craftwords are repeated, e.g. 1963-030
# These are very likely errors.
#
# 
# This is the tightest unique supported by the data.
#
#
#
# 
# 
# 
# 
#
# There are entries in the SPAAAAACE database with
#   empty date strings.

CREATE TABLE proto_SpacecraftDiscipline (
  craftword   VARCHAR(10),
  discword  VARCHAR(30),
  UNIQUE KEY (craftword, discword)
);
LOAD DATA LOCAL INFILE 'proto_SpacecraftDiscipline.tsv'
 INTO TABLE proto_SpacecraftDiscipline FIELDS
 TERMINATED BY '\t'
 OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES
 (craftword, discword);
Query OK, 7828 rows affected (0.11 sec)
Records: 7828  Deleted: 0  Skipped: 0  Warnings: 0
CREATE TABLE SpacecraftDiscipline (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  spacecraft INT UNSIGNED NOT NULL REFERENCES Spacecraft(id),
  discipline INT UNSIGNED NOT NULL REFERENCES Discipline(id),
  UNIQUE KEY (spacecraft, discipline)
);
INSERT INTO SpacecraftDiscipline (spacecraft, discipline)
  SELECT Spacecraft.id, Discipline.id
    FROM proto_SpacecraftDiscipline
   INNER JOIN Spacecraft
     ON (Spacecraft.craftword=proto_SpacecraftDiscipline.craftword)
   INNER JOIN Discipline
     ON (Discipline.labelword=proto_SpacecraftDiscipline.discword);
Query OK, 5804 rows affected (0.24 sec)
Records: 5804  Duplicates: 0  Warnings: 0

CREATE TABLE Crew (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  crewword VARCHAR(30),
  name VARCHAR(50)
);

# Create a Crew table
#   with a primary key,
#   the labelword from the Person URL,
#   and the full name.

LOAD DATA LOCAL INFILE 'Crew.tsv'
 INTO TABLE Crew FIELDS
 TERMINATED BY '\t'
 OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES
 (crewword, name);
Query OK, 51 rows affected (0.01 sec)
Records: 51  Deleted: 0  Skipped: 0  Warnings: 0

# Start MySQL client in a mood to honor LOAD DATA LOCAL.
# Load from our freshly-created Crew.tsv.
#  Populate the new Crew table.
#  values separated by TAB.
#  Lines end with newline.
#  Strings enclosed in ""s.
#  Skip the header with the variable names.
#  Populate the labelword and name attributes;
#    id's AUTO_INCREMENT will handle the primary key.
# Get a thumbs-up back from MySQL.

CREATE TABLE MissionRole (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  crew INT UNSIGNED NOT NULL,
  mission INT UNSIGNED NOT NULL,
  task INT UNSIGNED NOT NULL,
  mysteriousInt INT UNSIGNED
)

# Capture the roles the crew performed.
# Always add a primary key to make life easier.
#   the labelword from the Person URL,
#   a string form (pathetic, I know) of the mission,
#   the task name,
#   and some number associated with capsule-communicators.

CREATE TABLE proto_MissionRole (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  crew VARCHAR(30),
  mission VARCHAR(10),
  task VARCHAR(30),
  mysteriousInt INT,
  UNIQUE KEY (crew, mission, task, mysteriousInt)
);
LOAD DATA LOCAL INFILE 'proto_MissionRole.tsv'
 INTO TABLE proto_MissionRole FIELDS
 TERMINATED BY '\t'
 OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES
 (crew, mission, task, mysteriousInt);
Query OK, 142 rows affected (0.03 sec)
Records: 142  Deleted: 0  Skipped: 0  Warnings: 0
CREATE TABLE MissionRole (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  crew INT UNSIGNED NOT NULL REFERENCES Crew(id),
  mission INT UNSIGNED NOT NULL REFERENCES Mission(id),
  role INT UNSIGNED NOT NULL REFERENCES Role(id),
  mysteriousInt INT,
  UNIQUE KEY (crew, mission, role, mysteriousInt)
);

# Capture the roles the crew performed.
# Always add a primary key to make life easier.
#   the labelword from the Person URL,
#   a string form (pathetic, I know) of the mission,
#   the task name,
#   and some number associated with capsule-communicators.
# Load from our freshly-created Crew.tsv.
#  Populate the new Crew table.
#  values separated by TAB.
#  Lines end with newline.
#  Strings enclosed in ""s.
#  Skip the header with the variable names.
#  Populate the labelword and name attributes;
#    id's AUTO_INCREMENT will handle the primary key.
# Get a thumbs-up back from MySQL.