librarydb

DROP DATABASE IF EXISTS librarydb;
CREATE DATABASE librarydb;
USE librarydb;

CREATE TABLE userRole (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    roleName ENUM ("Admin", "Librarian", "Student", "Teacher") NOT NULL
);

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR (100) NOT NULL,
    egn VARCHAR(10) NOT NULL UNIQUE,
    pass VARCHAR(100) NOT NULL,
    phone VARCHAR (10) NOT NULL UNIQUE,
    email VARCHAR (50) NOT NULL UNIQUE,
    role_id INT NOT NULL,

    CONSTRAINT FOREIGN KEY (role_id) REFERENCES userRole(id)
);

CREATE TABLE loanBooks (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,

    user_id INT NOT NULL,
    CONSTRAINT FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE publishers (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    description TEXT NOT NULL,

    user_id INT NOT NULL,
    loan_id INT NOT NULL,
    publisher_id INT NOT NULL,
    CONSTRAINT FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT FOREIGN KEY (loan_id) REFERENCES loanBooks(id),
    CONSTRAINT FOREIGN KEY (publisher_id) REFERENCES publishers(id)
);

CREATE TABLE authors (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    info TEXT NOT NULL
);

CREATE TABLE genres (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE books_authors(
    book_id INT NOT NULL,
    author_id INT NOT NULL,

    CONSTRAINT FOREIGN KEY (book_id) REFERENCES books(id),
    CONSTRAINT FOREIGN KEY (author_id) REFERENCES authors(id),
    PRIMARY KEY(book_id, author_id)
);

CREATE TABLE books_genres (
    book_id INT NOT NULL,
    genre_id INT NOT NULL,

    CONSTRAINT FOREIGN KEY (book_id) REFERENCES books(id),
    CONSTRAINT FOREIGN KEY (genre_id) REFERENCES genres(id),
    PRIMARY KEY(book_id, genre_id)
);

CREATE VIEW info AS
SELECT books.title, books.description, authors.name, genres.name, publishers.name
FROM books
JOIN books_authors ON books.id = books_authors.book_id
JOIN authors ON books_authors.author_id = authors.id
JOIN books_genres ON books.id = books_genres.book_id
JOIN genres ON books_genres.genre_id = genres.id
JOIN publishers ON books.publisher_id = publishers.id;

(SELECT books.title, publishers.name
FROM books
LEFT JOIN publishers ON books.publisher_id = publishers.id)
UNION
(SELECT books.title, publishers.name
FROM books
RIGHT JOIN publishers ON books.publisher_id = publishers.id);

SELECT a1.name AS author1_name, a2.name AS author2_name, b.title AS book_title
FROM books_authors ba1
JOIN books_authors ba2 ON ba1.book_id = ba2.book_id
JOIN authors a1 ON ba1.author_id = a1.id
JOIN authors a2 ON ba2.author_id = a2.id
JOIN books b ON ba1.book_id = b.id
ORDER BY b.title;