Create database Try1 use Try1 Create table Reader ReaderTicket_ID int

  • Anonymous
  • SQL
  • 29 Nov 2019 
  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)
← newer dump
older dump →