Advertisement
jacket410

Untitled

May 24th, 2022
745
144 days
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE master;
  2. GO
  3.  
  4. DECLARE @dbname sysname
  5. SET @dbname = N'Bus_Station'
  6.  
  7. -- procedure to close any existing connections
  8. DECLARE @kill varchar(8000) = '';
  9. SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
  10. FROM sys.dm_exec_sessions
  11. WHERE database_id  = db_id(@dbname)
  12.  
  13. IF DB_ID(@dbname) IS NOT NULL
  14.   BEGIN
  15.     EXEC(@kill)
  16.     DROP DATABASE Bus_Station
  17. END
  18.  
  19. CREATE DATABASE Bus_Station;
  20. GO
  21.  
  22. USE Bus_Station;
  23.  
  24. CREATE TABLE City
  25. (
  26.     ID int PRIMARY KEY IDENTITY(1,1) not NULL,
  27.     Name nvarchar(25) not NULL
  28. )
  29.  
  30. CREATE TABLE Driver
  31. (
  32.     ID int PRIMARY KEY IDENTITY(1,1) not NULL,
  33.     First_Name nvarchar(25) not NULL,
  34.     Second_Name nvarchar(25) not NULL,
  35.     FK_City_ID int FOREIGN KEY REFERENCES City(ID)
  36. )
  37.  
  38. CREATE TABLE Bus_office
  39. (
  40.     ID int PRIMARY KEY IDENTITY(1,1) not NULL,
  41.     FK_City_ID int FOREIGN KEY REFERENCES City(ID) not NULL,
  42.     Addr nvarchar(50) not NULL
  43. )
  44.  
  45. CREATE TABLE Bus
  46. (
  47.     Num int PRIMARY KEY not NULL,
  48.     Count_place int not NULL,
  49.     Model nvarchar(20) not NULL
  50. )
  51.  
  52. CREATE TABLE Passenger
  53. (
  54.     ID int PRIMARY KEY IDENTITY(1,1) not NULL,
  55.     First_Name nvarchar(25) not NULL,
  56.     Second_Name nvarchar(25) not NULL
  57. )
  58.  
  59. CREATE TABLE Bus_route
  60. (
  61.     ID int PRIMARY KEY IDENTITY(1,1) not NULL,
  62.     Destination_City_ID int FOREIGN KEY REFERENCES City(ID) not NULL,
  63.     Arrival_City_ID int FOREIGN KEY REFERENCES City(ID) not NULL,
  64.     Direction nvarchar(7) not NULL,
  65.     Time_Destination time not NULL,
  66.     Time_Arrival_Point time not NULL,
  67.     MIN_QuantityPlace int not NULL
  68. )
  69.  
  70. CREATE TABLE Bus_service
  71. (
  72.     ID int PRIMARY KEY IDENTITY(1,1) not NULL,
  73.     FK_Bus_office_ID int FOREIGN KEY REFERENCES Bus_office(ID) not NULL,
  74.     FK_Bus_Num int FOREIGN KEY REFERENCES Bus(Num) not NULL,
  75.     FK_Bus_route_ID int FOREIGN KEY REFERENCES Bus_route(ID) not NULL,
  76.     Departure_date date not NULL,
  77.     Price int not NULL,
  78.     FK_Driver_ID int FOREIGN KEY REFERENCES Driver(ID) not NULL
  79. )
  80.  
  81. CREATE TABLE Buy_ticket
  82. (
  83.     ID int PRIMARY KEY IDENTITY(1,1) not NULL,
  84.     FK_Bus_office_id int FOREIGN KEY REFERENCES Bus_office(ID) not NULL,
  85.     Passenger int FOREIGN KEY REFERENCES Passenger(ID) not NULL,
  86.     -- Trip
  87.     FK_Bus_service_ID int FOREIGN KEY REFERENCES Bus_service(ID),
  88.     Purchase_date date not NULL,
  89.     Place int not NULL
  90. )
  91.  
  92. INSERT City
  93. VALUES
  94.     ('Томск'),
  95.     ('Новосибирск'),
  96.     ('Москва'),
  97.     ('Красноярск');
  98.  
  99. INSERT Driver
  100. VALUES
  101.     ('Петренко', 'Игорь', 1),
  102.     ('Федоров', 'Матвей', 4),
  103.     ('Иваненко', 'Владислав', 3),
  104.     ('Васильев', 'Иван', 2),
  105.     ('Турченко', 'Петр', 4);
  106.  
  107. INSERT Bus_office
  108. VALUES
  109.     (1, 'Советская, 76е'),
  110.     (2, 'Советская, 1а'),
  111.     (3, 'Ленина, 36'),
  112.     (4, 'Красномосковская, 12г'),
  113.     (2, 'Матросова, 8');
  114.  
  115. INSERT Bus
  116. VALUES
  117.     (2, 20, 'ПАЗ'),
  118.     (15, 25, 'ИКАРУС'),
  119.     (90, 10, 'Газель'),
  120.     (196, 20, 'ПАЗ'),
  121.     (519, 15, 'Газель'),
  122.     (602, 20, 'ИКАРУС'),
  123.     (610, 15, 'HUNDAY');
  124.  
  125. INSERT Passenger
  126. VALUES
  127.     ('Бородин', 'Александр'),
  128.     ('Глинка', 'Михаил'),
  129.     ('Мусоргский', 'Модест'),
  130.     ('Прокофьев', 'Сергей'),
  131.     ('Рахманинов', 'Сергей'),
  132.     ('Римский-Корсаков', 'Николай'),
  133.     ('Скрябин', 'Александр'),
  134.     ('Стравинский', 'Игорь'),
  135.     ('Чайковский', 'Пётр'),
  136.     ('Шостакович', 'Дмитрий'),
  137.     ('Свиридов', 'Георгий'),
  138.     ('Гаврилин', 'Валерий'),
  139.     ('Таривердиев', 'Микаэл'),
  140.     ('Александров', 'Александр'),
  141.     ('Хачатурян', 'Арам'),
  142.     ('Петров', 'Андрей');
  143.  
  144. INSERT Bus_route
  145. VALUES
  146.     (1, 2, 'Туда', '13:00', '17:50', 10),
  147.     (2, 3, 'Обратно', '16:30', '20:30', 10),
  148.     (3, 4, 'Туда', '20:00', '23:30', 10),
  149.     (4, 1, 'Обратно', '21:30', '1:50', 10),
  150.     (3, 2, 'Туда', '8:00', '13:50', 10),
  151.     (3, 1, 'Обратно', '13:30', '15:00', 10);
  152.  
  153. INSERT Bus_service
  154. VALUES
  155.     -- had to replace dates with regex
  156.     -- to work across different system locales
  157.     (1, 2, 1, '20230315', 200, 1),
  158.     (2, 15, 2, '20220303', 300, 5),
  159.     (3, 90, 3, '20220321', 120, 3),
  160.     (4, 196, 4, '20220320', 250, 2),
  161.     (3, 519, 5, '20220309', 150, 3),
  162.     (2, 602, 2, '20220330', 300, 4),
  163.     (1, 610, 1, '20220313', 200, 1);
  164.  
  165. INSERT Buy_ticket
  166. VALUES
  167.     (1, 1, 1, '20230315', 1),
  168.     (2, 2, 2, '20220303', 2),
  169.     (3, 3, 3, '20220321', 3),
  170.     (4, 4, 4, '20220320', 4),
  171.     (1, 5, 3, '20220309', 5),
  172.     (2, 6, 2, '20220330', 6),
  173.     (3, 7, 1, '20220313', 7);
  174. GO
  175.  
  176. CREATE TRIGGER preserveTickets
  177. ON Buy_ticket INSTEAD OF DELETE
  178. AS
  179. IF (
  180. SELECT Departure_date
  181. FROM Buy_ticket ticket JOIN Bus_service serv
  182.     ON ticket.FK_Bus_service_ID = serv.ID
  183. WHERE ticket.ID =
  184. (SELECT id
  185. FROM deleted)
  186. ) > CAST(CURRENT_TIMESTAMP AS Date )
  187. BEGIN
  188.     ROLLBACK TRAN
  189.     PRINT N'Can not delete this ticket'
  190. END
  191. GO
Advertisement
RAW Paste Data Copied
Advertisement