Create database Try1 use Try1 Create table Reader ReaderTicket_ID int

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
Create database Try1
use Try1
Create table Reader(
ReaderTicket_ID int PRIMARY KEY,
Name varchar(12) NOT NULL,
MobileNumber varchar(12) NOT NULL,
Address varchar(50) NOT NULL,
Constraint CK_Mobile_Address_UNIQUE UNIQUE(MobileNumber,Address))
Create table Employee(
EmployeeTicket_ID int PRIMARY KEY,
Name varchar(12) NOT NULL,
PhoneNumber varchar(12),
Post varchar(50),
Branch_ID_FK int,
Constraint CK_Post_Legit CHECK (Post IN ('бібліотекар','завідувач відділенням')))
ALTER TABLE Employee
ADD FOREIGN KEY (Branch_ID_FK) REFERENCES Branch(Branch_ID) ON DELETE SET NULL
Create table Book(
Book_ID int PRIMARY KEY,
Tittle varchar(50) UNIQUE NOT NULL,
Genre varchar(15) NOT NULL,
PublishDate Date NOT NULL,
Branch_ID_FK int ,
PublishingHouse_ID_FK int,
CONSTRAINT Genre_Legit CHECK (Genre IN('пригоди','детектив','фантастика','роман','драма')),
CONSTRAINT Publish_Legit CHECK ( PublishDate > '1950.01.01'),
CONSTRAINT FK_Branch_ID_Book FOREIGN KEY(Branch_ID_FK) REFERENCES Branch(Branch_ID) ON DELETE SET NULL,
CONSTRAINT FK_PublishingHouse_ID_Book FOREIGN KEY(PublishingHouse_ID_FK) REFERENCES PublishingHouse(PublishingHouse_ID),
CONSTRAINT CK_Book_UNIQUE UNIQUE(Tittle, PublishDate,PublishingHouse_ID_FK))
Create table [Transaction](
Transaction_ID int PRIMARY KEY,
Quantity int NOT NULL,
Type varchar(15) NOT NULL,
Book_ID_FK int,
ReaderTicket_ID_FK int,
EmployeeTicket_ID_FK int,
TimePeriod_ID_FK int,
CONSTRAINT Quantity_Legit CHECK ( Quantity <= 3 AND Quantity >= 1 ),
CONSTRAINT Type_Legit CHECK (Type IN('видача', 'прийом')),
CONSTRAINT FK_Book_ID_Transaction FOREIGN KEY(Book_ID_FK) REFERENCES Book(Book_ID),
CONSTRAINT ReaderTicket_ID_FK_Transaction FOREIGN KEY(ReaderTicket_ID_FK) REFERENCES Reader(ReaderTicket_ID),
CONSTRAINT EmployeeTicket_ID_FK_Transaction FOREIGN KEY(EmployeeTicket_ID_FK) REFERENCES Employee(EmployeeTicket_ID),
CONSTRAINT TimePeriod_ID_FK_Transaction FOREIGN KEY( TimePeriod_ID_FK) REFERENCES TimePeriod( TimePeriod_ID),
CONSTRAINT Transaction_Unique UNIQUE(TimePeriod_ID_FK, Book_ID_FK,EmployeeTicket_ID_FK,Type))
Create table BookAvailability(
Request_ID int PRIMARY KEY,
TotalAmount int NOT NULL,
InStock int NOT NULL,
Book_ID_FK int,
CONSTRAINT InStock_Legit CHECK(InStock < TotalAmount),
CONSTRAINT Book_ID_FK_BookAvailability FOREIGN KEY(Book_ID_FK) REFERENCES Book(Book_ID))
Create table PublishingHouse(
PublishingHouse_ID int PRIMARY KEY,
Name varchar(50) UNIQUE NOT NULL)
Create table Author(
Author_ID int PRIMARY KEY,
Name varchar(50) NOT NULL,
Surname varchar(5) NOT NULL)
Create table BooksAuthor(
Record_ID int PRIMARY KEY,
Book_ID_FK int,
Author_ID_FK int,
CONSTRAINT Book_ID_FK_BooksAuthor FOREIGN KEY(Book_ID_FK) REFERENCES Book(Book_ID),
CONSTRAINT Author_ID_FK_BookAuthor FOREIGN KEY(Author_ID_FK) REFERENCES Author(Author_ID))
Create table Branch(
Branch_ID int PRIMARY KEY,
EmployeeCount int NOT NULL,
TotalBookCount int NOT NULL,
Chief_FK int,
CONSTRAINT Chief_FK_Branch FOREIGN KEY(Chief_FK) REFERENCES Employee(EmployeeTicket_ID))
Create table TimePeriod(
TimePeriod_ID int PRIMARY KEY,
IssueDate Date NOT NULL,
ReturnDate Date NOT NULL
)
INSERT INTO TimePeriod
Values
(1, '2000-01-01', '2000-01-08'),
(2, '2000-01-02', '2000-01-09'),
(3, '2000-01-03', '2000-01-10'),
(4, '2000-01-04', '2000-01-11'),
(5, '2000-01-05', '2000-01-12'),
(6, '2000-01-06', '2000-01-13')
INSERT INTO Reader
VALUES
(1,'Микола', '063123123','Заводська 71'),
(2,'Oskar', '123123', 'Ніжинська 29A'),
(3,'Artem','222222', 'Ніжинська 29В'),
(4,'Bohdan', '333333', 'Кумача 29'),
(5,'Vlad' , '4444444', 'Академ 33')
INSERT INTO Employee
VALUES
(1, 'Вадим', '0673873643', 'бібліотекар',1),
(2, 'Ростислав', '07123123', 'завідувач відділенням',1),
(3, 'Бонислав', '222222', 'бібліотекар',1)
INSERT INTO Branch
VALUES
(1, 3, 6, NULL)
UPDATE Branch
SET Chief_FK = 1
WHERE Chief_FK = NULL;
INSERT INTO PublishingHouse
VALUES
(1, 'Видавництво1'),
(2, 'Видавництво2'),
(3, 'Видавництво3'),
(4, 'Видавництво4'),
(5, 'Видавництво5')
INSERT INTO Author
VALUES
(1, 'Джоан', 'Роулинг'),
(2, 'Редьярд', 'Кіплінг'),
(3, 'Лев','Тостой'),
(4, 'Тарас', 'Шевченко'),
(5, 'Ернест', 'Хемінгуей'),
(6, 'Дмитрий','Глуховский'),
(7, 'Джон','Толкиен')
INSERT INTO Book
VALUES
(1, 'Гарри Потер', 'фантастика', '2000-01-01',1, 1),
(2, 'Маугли', 'пригоди', '2000-02-02', 1, 2),
(3, 'Три мушкетера','пригоди','2000-03-03', 1,3),
(4, 'Кобзар', 'драма', '2000-04-04',1,4),
(5, 'Старий і море', 'фантастика', '2000-05-05', 1,5),
(6, 'Метро 2033', 'роман', '2013-01-11', 1, 1),
(7, 'Метро 2034', 'роман', '2014-01-11', 1, 1),
(8, 'Властелин колец', 'фантастика', '2000-06-06', 1,2)
INSERT INTO BooksAuthor
VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 4, 4),
(5, 5, 5),
(6, 6, 6),
(7, 7, 6),
(8, 8, 7)
INSERT INTO BookAvailability
VALUES
(1, 3, 2, 1),
(2, 2, 1, 2),
(3, 2, 1, 3),
(4, 1, 0, 4),
(5, 2, 1, 5),
(6, 3, 1, 6),
(7, 2, 0, 7),
(8, 2, 1, 8)
INSERT INTO [Transaction]
VALUES
(1, 1, 'видача', 1, 1 , 1, 1),
(2, 1, 'прийом', 1, 1 , 1, 2),
(3, 1, 'видача', 2, 2 , 1, 1),
(4, 1, 'видача', 3, 1 , 1, 2)