Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE master;
- GO
- DECLARE @dbname sysname
- SET @dbname = N'Bus_Station'
- -- procedure to close any existing connections
- DECLARE @kill varchar(8000) = '';
- SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
- FROM sys.dm_exec_sessions
- WHERE database_id = db_id(@dbname)
- IF DB_ID(@dbname) IS NOT NULL
- BEGIN
- EXEC(@kill)
- DROP DATABASE Bus_Station
- END
- CREATE DATABASE Bus_Station;
- GO
- USE Bus_Station;
- CREATE TABLE City
- (
- ID int PRIMARY KEY IDENTITY(1,1) not NULL,
- Name nvarchar(25) not NULL
- )
- CREATE TABLE Driver
- (
- ID int PRIMARY KEY IDENTITY(1,1) not NULL,
- First_Name nvarchar(25) not NULL,
- Second_Name nvarchar(25) not NULL,
- FK_City_ID int FOREIGN KEY REFERENCES City(ID)
- )
- CREATE TABLE Bus_office
- (
- ID int PRIMARY KEY IDENTITY(1,1) not NULL,
- FK_City_ID int FOREIGN KEY REFERENCES City(ID) not NULL,
- Addr nvarchar(50) not NULL
- )
- CREATE TABLE Bus
- (
- Num int PRIMARY KEY not NULL,
- Count_place int not NULL,
- Model nvarchar(20) not NULL
- )
- CREATE TABLE Passenger
- (
- ID int PRIMARY KEY IDENTITY(1,1) not NULL,
- First_Name nvarchar(25) not NULL,
- Second_Name nvarchar(25) not NULL
- )
- CREATE TABLE Bus_route
- (
- ID int PRIMARY KEY IDENTITY(1,1) not NULL,
- Destination_City_ID int FOREIGN KEY REFERENCES City(ID) not NULL,
- Arrival_City_ID int FOREIGN KEY REFERENCES City(ID) not NULL,
- Direction nvarchar(7) not NULL,
- Time_Destination time not NULL,
- Time_Arrival_Point time not NULL,
- MIN_QuantityPlace int not NULL
- )
- CREATE TABLE Bus_service
- (
- ID int PRIMARY KEY IDENTITY(1,1) not NULL,
- FK_Bus_office_ID int FOREIGN KEY REFERENCES Bus_office(ID) not NULL,
- FK_Bus_Num int FOREIGN KEY REFERENCES Bus(Num) not NULL,
- FK_Bus_route_ID int FOREIGN KEY REFERENCES Bus_route(ID) not NULL,
- Departure_date date not NULL,
- Price int not NULL,
- FK_Driver_ID int FOREIGN KEY REFERENCES Driver(ID) not NULL
- )
- CREATE TABLE Buy_ticket
- (
- ID int PRIMARY KEY IDENTITY(1,1) not NULL,
- FK_Bus_office_id int FOREIGN KEY REFERENCES Bus_office(ID) not NULL,
- Passenger int FOREIGN KEY REFERENCES Passenger(ID) not NULL,
- -- Trip
- FK_Bus_service_ID int FOREIGN KEY REFERENCES Bus_service(ID),
- Purchase_date date not NULL,
- Place int not NULL
- )
- INSERT City
- VALUES
- ('Томск'),
- ('Новосибирск'),
- ('Москва'),
- ('Красноярск');
- INSERT Driver
- VALUES
- ('Петренко', 'Игорь', 1),
- ('Федоров', 'Матвей', 4),
- ('Иваненко', 'Владислав', 3),
- ('Васильев', 'Иван', 2),
- ('Турченко', 'Петр', 4);
- INSERT Bus_office
- VALUES
- (1, 'Советская, 76е'),
- (2, 'Советская, 1а'),
- (3, 'Ленина, 36'),
- (4, 'Красномосковская, 12г'),
- (2, 'Матросова, 8');
- INSERT Bus
- VALUES
- (2, 20, 'ПАЗ'),
- (15, 25, 'ИКАРУС'),
- (90, 10, 'Газель'),
- (196, 20, 'ПАЗ'),
- (519, 15, 'Газель'),
- (602, 20, 'ИКАРУС'),
- (610, 15, 'HUNDAY');
- INSERT Passenger
- VALUES
- ('Бородин', 'Александр'),
- ('Глинка', 'Михаил'),
- ('Мусоргский', 'Модест'),
- ('Прокофьев', 'Сергей'),
- ('Рахманинов', 'Сергей'),
- ('Римский-Корсаков', 'Николай'),
- ('Скрябин', 'Александр'),
- ('Стравинский', 'Игорь'),
- ('Чайковский', 'Пётр'),
- ('Шостакович', 'Дмитрий'),
- ('Свиридов', 'Георгий'),
- ('Гаврилин', 'Валерий'),
- ('Таривердиев', 'Микаэл'),
- ('Александров', 'Александр'),
- ('Хачатурян', 'Арам'),
- ('Петров', 'Андрей');
- INSERT Bus_route
- VALUES
- (1, 2, 'Туда', '13:00', '17:50', 10),
- (2, 3, 'Обратно', '16:30', '20:30', 10),
- (3, 4, 'Туда', '20:00', '23:30', 10),
- (4, 1, 'Обратно', '21:30', '1:50', 10),
- (3, 2, 'Туда', '8:00', '13:50', 10),
- (3, 1, 'Обратно', '13:30', '15:00', 10);
- INSERT Bus_service
- VALUES
- -- had to replace dates with regex
- -- to work across different system locales
- (1, 2, 1, '20230315', 200, 1),
- (2, 15, 2, '20220303', 300, 5),
- (3, 90, 3, '20220321', 120, 3),
- (4, 196, 4, '20220320', 250, 2),
- (3, 519, 5, '20220309', 150, 3),
- (2, 602, 2, '20220330', 300, 4),
- (1, 610, 1, '20220313', 200, 1);
- INSERT Buy_ticket
- VALUES
- (1, 1, 1, '20230315', 1),
- (2, 2, 2, '20220303', 2),
- (3, 3, 3, '20220321', 3),
- (4, 4, 4, '20220320', 4),
- (1, 5, 3, '20220309', 5),
- (2, 6, 2, '20220330', 6),
- (3, 7, 1, '20220313', 7);
- GO
- CREATE TRIGGER preserveTickets
- ON Buy_ticket INSTEAD OF DELETE
- AS
- IF (
- SELECT Departure_date
- FROM Buy_ticket ticket JOIN Bus_service serv
- ON ticket.FK_Bus_service_ID = serv.ID
- WHERE ticket.ID =
- (SELECT id
- FROM deleted)
- ) > CAST(CURRENT_TIMESTAMP AS Date )
- BEGIN
- ROLLBACK TRAN
- PRINT N'Can not delete this ticket'
- END
- GO
Advertisement
RAW Paste Data
Copied
Advertisement