QPZv8jFM

1DROP DATABASE IF EXISTS BD_projekt;
2CREATE DATABASE BD_Projekt;
3USE BD_Projekt;
4
5CREATE TABLE Genres(
6    ID int NOT NULL AUTO_INCREMENT,
7    Name varchar(4000),
8    PRIMARY KEY (ID)
9);
10
11CREATE TABLE Branches(
12    ID int NOT NULL AUTO_INCREMENT,
13    Name varchar(4000),
14    Address varchar(4000),
15    PhoneNumber int,
16    OpenTime time,
17    CloseTime time,
18    PRIMARY KEY (ID)
19);
20
21CREATE TABLE Publishers(
22    ID int NOT NULL AUTO_INCREMENT,
23    Name varchar(4000),
24    PRIMARY KEY (ID)
25);
26
27CREATE TABLE Books(
28    ID int NOT NULL AUTO_INCREMENT,
29    Title varchar(4000),
30    YearOfRelease int,
31    Publishers_ID int,
32    Branches_ID int,
33    PRIMARY KEY (ID),
34    FOREIGN KEY(branches_id)
35        REFERENCES Branches(ID),
36    FOREIGN KEY(publishers_id) 
37        REFERENCES Publishers(ID)
38);
39
40CREATE TABLE genres_books(
41    Genres_ID int,
42    Books_ID int,
43    PRIMARY KEY(Genres_ID, Books_ID),
44    FOREIGN KEY(genres_id) 
45        REFERENCES Genres(ID),
46    FOREIGN KEY(books_id) 
47        REFERENCES Books(ID)
48);
49
50CREATE TABLE Positions(
51    ID int NOT NULL AUTO_INCREMENT,
52    PositionName varchar(4000),
53    Salary float,
54    PRIMARY KEY (ID)
55);
56
57CREATE TABLE Employees(
58    ID int NOT NULL AUTO_INCREMENT,
59    FirstName varchar(4000),
60    LastName varchar(4000),
61    Branches_ID int,
62    Positions_ID int,
63    PRIMARY KEY (ID),
64    FOREIGN KEY(branches_id) 
65        REFERENCES Branches(ID),
66    FOREIGN KEY(positions_id) 
67        REFERENCES Positions(ID)
68);