Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SET DATEFORMAT dmy
- IF OBJECT_ID('id_of_city', N'FN') IS NOT NULL
- DROP FUNCTION id_of_city;
- IF OBJECT_ID('id_of_country', N'FN') IS NOT NULL
- DROP FUNCTION id_of_country;
- IF OBJECT_ID('id_of_airport', N'FN') IS NOT NULL
- DROP FUNCTION id_of_airport
- IF OBJECT_ID('Flight', 'U') IS NOT NULL
- DROP TABLE Flight;
- IF OBJECT_ID('[Status]', 'U') IS NOT NULL
- DROP TABLE [Status];
- IF OBJECT_ID('Airline', 'U') IS NOT NULL
- DROP TABLE Airline;
- IF OBJECT_ID('Airport', 'U') IS NOT NULL
- DROP TABLE Airport;
- IF OBJECT_ID('Airplane', 'U') IS NOT NULL
- DROP TABLE Airplane;
- IF OBJECT_ID('City', 'U') IS NOT NULL
- DROP TABLE City;
- IF OBJECT_ID('Country', 'U') IS NOT NULL
- DROP TABLE Country;
- IF OBJECT_ID('Aircraft', 'U') IS NOT NULL
- DROP TABLE Aircraft;
- IF OBJECT_ID('Configuration', 'U') IS NOT NULL
- DROP TABLE Configuration;
- IF OBJECT_ID('Types_of_classes', 'U') IS NOT NULL
- DROP TABLE Types_of_classes;
- CREATE TABLE Country (
- country_id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
- name VARCHAR(40) NOT NULL
- );
- GO
- CREATE FUNCTION dbo.id_of_country(@country_name VARCHAR(40))
- RETURNS INTEGER
- AS
- BEGIN
- RETURN
- (SELECT country_id FROM Country
- WHERE name LIKE @country_name)
- END;
- GO
- CREATE TABLE City (
- city_id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
- name VARCHAR(40) NOT NULL,
- difference_with_the_UTC SMALLINT NOT NULL,
- country_id INT NOT NULL
- FOREIGN KEY REFERENCES Country(country_id)
- ON UPDATE CASCADE
- );
- ALTER TABLE City
- ADD CHECK ( (difference_with_the_UTC BETWEEN -12 AND 12) );
- GO
- CREATE FUNCTION dbo.id_of_city(@city_name VARCHAR(40))
- RETURNS INTEGER
- AS
- BEGIN
- RETURN
- (SELECT city_id FROM City
- WHERE name LIKE @city_name)
- END;
- GO
- CREATE TABLE Airport (
- airport_id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
- name VARCHAR(50) NOT NULL,
- ICAO_code CHAR(4) NOT NULL,
- city_id INT NOT NULL
- FOREIGN KEY REFERENCES City(city_id)
- ON UPDATE CASCADE
- );
- GO
- CREATE FUNCTION dbo.id_of_airport(@airport_name VARCHAR(50))
- RETURNS INTEGER
- AS
- BEGIN
- RETURN
- (SELECT airport_id FROM Airport
- WHERE name LIKE @airport_name)
- END;
- GO
- CREATE TABLE Airline (
- airline_id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
- name VARCHAR(40) NOT NULL,
- base_airport_id INT NOT NULL
- FOREIGN KEY REFERENCES Airport(airport_id)
- ON UPDATE CASCADE
- );
- CREATE TABLE Types_of_classes (
- [type_id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
- name VARCHAR(40) NOT NULL
- );
- CREATE TABLE Configuration (
- configuration_id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
- [type_id] INT NOT NULL
- FOREIGN KEY REFERENCES Types_of_classes([type_id])
- ON UPDATE CASCADE,
- number_of_seats SMALLINT NOT NULL
- );
- ALTER TABLE Configuration
- ADD CHECK ( (number_of_seats BETWEEN 0 AND 1000) );
- CREATE TABLE Aircraft (
- aircarft_model VARCHAR(20) NOT NULL PRIMARY KEY,
- cruising_speed SMALLINT NOT NULL,
- range_of_flight SMALLINT NOT NULL,
- max_takeoff_weight INT NOT NULL
- );
- ALTER TABLE Aircraft
- ADD CHECK ( (range_of_flight BETWEEN 100 AND 22000) AND
- (cruising_speed BETWEEN 0 AND 3000) );
- CREATE TABLE Airplane (
- airplane_id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
- aircarft_model VARCHAR(20) NOT NULL
- FOREIGN KEY REFERENCES Aircraft(aircarft_model),
- configuration_id INT NOT NULL
- FOREIGN KEY REFERENCES Configuration(configuration_id)
- ON UPDATE CASCADE,
- );
- CREATE TABLE [Status] (
- status_id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
- [type_id] INT NOT NULL
- FOREIGN KEY REFERENCES Types_of_classes([type_id])
- ON UPDATE CASCADE,
- left_seats SMALLINT NOT NULL,
- price SMALLMONEY NOT NULL
- );
- ALTER TABLE [Status]
- ADD CHECK ( (left_seats BETWEEN 0 AND 1000) );
- CREATE TABLE Flight (
- flight_id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
- departure_airport INT NOT NULL
- FOREIGN KEY REFERENCES Airport(airport_id)
- ON UPDATE CASCADE,
- departure_date_and_time DATETIME NOT NULL,
- arrival_airport INT NOT NULL
- FOREIGN KEY REFERENCES Airport(airport_id)
- --ON UPDATE CASCADE,
- ON DELETE NO ACTION,
- arrival_date_and_time DATETIME NOT NULL,
- airline_id INT NOT NULL
- FOREIGN KEY REFERENCES Airline(airline_id)
- --ON UPDATE CASCADE,
- ON DELETE NO ACTION,
- airplane_id INT NOT NULL
- FOREIGN KEY REFERENCES Airplane(airplane_id)
- ON UPDATE CASCADE,
- distance SMALLINT NOT NULL,
- status_id INT NOT NULL
- FOREIGN KEY REFERENCES [Status](status_id)
- --ON UPDATE CASCADE
- ON DELETE NO ACTION
- );
- ALTER TABLE Flight
- ADD CHECK ( (distance BETWEEN 100 AND 100000) );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement