Untitled

CREATE DATABASE ColonialJourney
USE ColonialJourney

CREATE TABLE Planets (
Id INT PRIMARY KEY IDENTITY NOT NULL,
[Name] VARCHAR(30) NOT NULL)

CREATE TABLE Spaceports (
Id INT PRIMARY KEY IDENTITY NOT NULL,
[Name] VARCHAR(50) NOT NULL,
PlanetId INT FOREIGN KEY REFERENCES Planets(Id))

CREATE TABLE Spaceships (
Id INT PRIMARY KEY IDENTITY NOT NULL,
[Name] VARCHAR(50) NOT NULL,
Manufacturer VARCHAR(30) NOT NULL,
LightSpeedRate INT DEFAULT(0))

CREATE TABLE Colonists (
Id INT PRIMARY KEY IDENTITY NOT NULL,
Firstname VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Ucn VARCHAR(10) UNIQUE NOT NULL,
BirthDate DATE NOT NULL)

CREATE TABLE Journeys (
Id INT PRIMARY KEY IDENTITY NOT NULL,
JourneyStart DATETIME NOT NULL,
JourneyEnd DATETIME NOT NULL,
Purpose VARCHAR(11)  CONSTRAINT chk_Purpose CHECK (Purpose IN ('Medical','Technical','Educational','Military')),
DestinationSpaceportId INT FOREIGN KEY REFERENCES Spaceports(Id) NOT NULL,
SpaceshipId INT FOREIGN KEY REFERENCES Spaceships(Id) NOT NULL )

CREATE TABLE TravelCards (
Id INT PRIMARY KEY IDENTITY NOT NULL,
CardNumber VARCHAR(10) UNIQUE NOT NULL,
JobDuringJourney VARCHAR(8) CONSTRAINT chk_Job CHECK(JobDuringJourney IN('Pilot', 'Engineer', 'Trooper', 'Cleaner', 'Cook')),
ColonistId INT FOREIGN KEY REFERENCES Colonists(Id) NOT NULL,
JourneyId INT FOREIGN KEY REFERENCES Journeys(Id) NOT NULL )

INSERT INTO Planets(Name) VALUES
('Mars'),
('Earth'),
('Jupiter'),
('Saturn')

INSERT INTO Spaceships(Name, Manufacturer, LightSpeedRate) VALUES
('Golf','VW',3),
('WakaWaka','Wakanda',4),
('Falcon9','SpaceX',1),
('Bed','Vidolov',6)