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)