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
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)