DPVfZmkW

1DROP DATABASE IF EXISTS projekt_jg_mk;
2CREATE DATABASE projekt_jg_mk;
3USE projekt_jg_mk;
4
5CREATE TABLE Reservation_Status ( 
6    ID INT AUTO_INCREMENT, 
7    Name VARCHAR(4000) NOT NULL, 
8    PRIMARY KEY (ID) 
9);
10
11CREATE TABLE Rent_Status ( 
12    ID INT AUTO_INCREMENT, 
13    Name VARCHAR(4000), 
14    PRIMARY KEY(ID) 
15);
16
17CREATE TABLE Library_Cards ( 
18    ID INT AUTO_INCREMENT, 
19    Card_Number VARCHAR(30), 
20    Owner_Last_Name VARCHAR(50), 
21    Owner_Phone_Number INT, 
22    PRIMARY KEY(ID) 
23);
24
25CREATE TABLE Authors ( 
26    ID INT AUTO_INCREMENT, 
27    First_Name VARCHAR(4000), 
28    Last_Name VARCHAR(4000), 
29    PRIMARY KEY(ID) 
30);
31
32CREATE TABLE Genres(
33    ID int NOT NULL AUTO_INCREMENT,
34    Name varchar(4000),
35    PRIMARY KEY (ID)
36);
37 
38CREATE TABLE Branches(
39    ID int NOT NULL AUTO_INCREMENT,
40    Name varchar(4000),
41    Address varchar(4000),
42    PhoneNumber int,
43    OpenTime time,
44    CloseTime time,
45    PRIMARY KEY (ID)
46);
47 
48CREATE TABLE Publishers(
49    ID int NOT NULL AUTO_INCREMENT,
50    Name varchar(4000),
51    PRIMARY KEY (ID)
52);
53 
54CREATE TABLE Books(
55    ID int NOT NULL AUTO_INCREMENT,
56    Title varchar(4000),
57    YearOfRelease int,
58    Publishers_ID int,
59    Branches_ID int,
60    PRIMARY KEY (ID),
61    FOREIGN KEY(branches_id)
62        REFERENCES Branches(ID),
63    FOREIGN KEY(publishers_id)
64        REFERENCES Publishers(ID)
65);
66 
67CREATE TABLE genres_books(
68    Genres_ID int,
69    Books_ID int,
70    PRIMARY KEY(Genres_ID, Books_ID),
71    FOREIGN KEY(genres_id)
72        REFERENCES Genres(ID),
73    FOREIGN KEY(books_id)
74        REFERENCES Books(ID)
75);
76 
77CREATE TABLE Positions(
78    ID int NOT NULL AUTO_INCREMENT,
79    PositionName varchar(4000),
80    Salary float,
81    PRIMARY KEY (ID)
82);
83 
84CREATE TABLE Employees(
85    ID int NOT NULL AUTO_INCREMENT,
86    FirstName varchar(4000),
87    LastName varchar(4000),
88    Branches_ID int,
89    Positions_ID int,
90    PRIMARY KEY (ID),
91    FOREIGN KEY(branches_id)
92        REFERENCES Branches(ID),
93    FOREIGN KEY(positions_id)
94        REFERENCES Positions(ID)
95);
96
97CREATE TABLE authors_books ( 
98    authors_id INT NOT NULL, 
99    books_id INT NOT NULL,
100    FOREIGN KEY(authors_id) REFERENCES Authors(ID), 
101    FOREIGN KEY(books_id) REFERENCES Books(ID) 
102);
103
104CREATE TABLE Reservations ( 
105    ID INT AUTO_INCREMENT, 
106    Start_Date DATE, 
107    End_Date DATE, 
108    reservation_status_id INT NOT NULL, 
109    library_card_id INT NOT NULL, 
110    books_id INT NOT NULL, 
111    PRIMARY KEY(ID), 
112    FOREIGN KEY(reservation_status_id) REFERENCES reservation_status(ID), 
113    FOREIGN KEY(library_card_id) REFERENCES Library_cards(ID), 
114    FOREIGN KEY(books_id) REFERENCES books(ID) 
115);
116
117CREATE TABLE Rents ( 
118    ID INT AUTO_INCREMENT, 
119    Start_Date DATE, End_Date DATE, 
120    rent_status_id INT NOT NULL, 
121    library_cards_id INT NOT NULL, 
122    books_id INT NOT NULL, 
123    PRIMARY KEY(ID), 
124    FOREIGN KEY(rent_status_id) REFERENCES Rent_Status(ID), 
125    FOREIGN KEY(library_cards_id) REFERENCES Library_Cards(ID), 
126    FOREIGN KEY(books_id) REFERENCES Books(ID) 
127);
128
129INSERT INTO Authors(ID,First_Name,Last_Name) VALUES (NULL,"Adam","Mickiewicz");
130INSERT INTO Authors(ID,First_Name,Last_Name) VALUES (NULL,"Jan","Kochanowski");
131INSERT INTO Authors(ID,First_Name,Last_Name) VALUES (NULL,"Juliusz","Slowacki");
132INSERT INTO Authors(ID,First_Name,Last_Name) VALUES (NULL,"Stephen","King");
133SELECT * FROM Authors;