Untitled
unknown
plain_text
4 years ago
1.6 kB
5
Indexable
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)
Editor is loading...