V34UmHgJ

1DROP DATABASE IF EXISTS librarydb;
2CREATE DATABASE librarydb;
3USE librarydb;
4
5CREATE TABLE userRole (
6	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
7    roleName ENUM ("Admin", "Librarian", "Student", "Teacher") NOT NULL
8);
9
10CREATE TABLE users (
11	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
12    name VARCHAR (100) NOT NULL,
13    egn VARCHAR(10) NOT NULL UNIQUE,
14    pass VARCHAR(100) NOT NULL,
15    phone VARCHAR (10) NOT NULL UNIQUE,
16    email VARCHAR (50) NOT NULL UNIQUE,
17    role_id INT NOT NULL,
18    
19    CONSTRAINT FOREIGN KEY (role_id) REFERENCES userRole(id)
20);
21
22CREATE TABLE loanBooks (
23	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
24    date DATE NOT NULL,
25    
26    user_id INT NOT NULL,
27    CONSTRAINT FOREIGN KEY (user_id) REFERENCES users(id)
28);
29
30CREATE TABLE publishers (
31	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
32    name VARCHAR(100) NOT NULL,
33    address VARCHAR(100) NOT NULL
34);
35
36CREATE TABLE books (
37	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
38    title VARCHAR(50) NOT NULL,
39    description TEXT NOT NULL,
40    
41    user_id INT NOT NULL,
42    loan_id INT NOT NULL,
43    publisher_id INT NOT NULL,
44    CONSTRAINT FOREIGN KEY (user_id) REFERENCES users(id),
45    CONSTRAINT FOREIGN KEY (loan_id) REFERENCES loanBooks(id),
46    CONSTRAINT FOREIGN KEY (publisher_id) REFERENCES publishers(id)
47);
48
49CREATE TABLE authors (
50	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
51    name VARCHAR(100) NOT NULL,
52    info TEXT NOT NULL
53);
54
55CREATE TABLE genres (
56	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
57    name VARCHAR(100) NOT NULL
58);
59
60CREATE TABLE books_authors(
61	book_id INT NOT NULL,
62    author_id INT NOT NULL,
63    
64	CONSTRAINT FOREIGN KEY (book_id) REFERENCES books(id),
65    CONSTRAINT FOREIGN KEY (author_id) REFERENCES authors(id),
66    PRIMARY KEY(book_id, author_id)
67);
68
69CREATE TABLE books_genres (
70	book_id INT NOT NULL,
71	genre_id INT NOT NULL,
72    
73    CONSTRAINT FOREIGN KEY (book_id) REFERENCES books(id),
74    CONSTRAINT FOREIGN KEY (genre_id) REFERENCES genres(id),
75    PRIMARY KEY(book_id, genre_id)
76);
77
78CREATE VIEW info AS
79SELECT books.title, books.description, authors.name, genres.name, publishers.name
80FROM books
81JOIN books_authors ON books.id = books_authors.book_id
82JOIN authors ON books_authors.author_id = authors.id
83JOIN books_genres ON books.id = books_genres.book_id
84JOIN genres ON books_genres.genre_id = genres.id
85JOIN publishers ON books.publisher_id = publishers.id;
86
87(SELECT books.title, publishers.name
88FROM books
89LEFT JOIN publishers ON books.publisher_id = publishers.id)
90UNION
91(SELECT books.title, publishers.name
92FROM books
93RIGHT JOIN publishers ON books.publisher_id = publishers.id);
94
95SELECT a1.name AS author1_name, a2.name AS author2_name, b.title AS book_title
96FROM books_authors ba1
97JOIN books_authors ba2 ON ba1.book_id = ba2.book_id 
98JOIN authors a1 ON ba1.author_id = a1.id
99JOIN authors a2 ON ba2.author_id = a2.id
100JOIN books b ON ba1.book_id = b.id
101ORDER BY b.title;
102
103