---

CREATE DATABASE LibraryDB;
GO

USE LibraryDB;
GO


-- 1. Читатели
CREATE TABLE Readers (
    id_reader INT PRIMARY KEY IDENTITY(1,1),

    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),

    home_address VARCHAR(150) NOT NULL,

    work_phone VARCHAR(20),
    home_phone VARCHAR(20),

    birth_date DATE NOT NULL,

    ticket_number INT NOT NULL UNIQUE,

    -- Должен быть указан хотя бы один телефон
    CHECK (work_phone IS NOT NULL OR home_phone IS NOT NULL)
);


-- 2. Области знаний
CREATE TABLE KnowledgeAreas (
    id_knowledge_area INT PRIMARY KEY IDENTITY(1,1),

    unique_number INT NOT NULL UNIQUE,

    name VARCHAR(100) NOT NULL
);


-- 3. Издательства
CREATE TABLE Publishers (
    id_publisher INT PRIMARY KEY IDENTITY(1,1),

    name VARCHAR(100) NOT NULL,

    city VARCHAR(100) NOT NULL
);


-- 4. Книги
CREATE TABLE Books (
    id_book INT PRIMARY KEY IDENTITY(1,1),

    unique_code VARCHAR(50) NOT NULL UNIQUE,

    title VARCHAR(150) NOT NULL,

    author VARCHAR(100),

    coauthors VARCHAR(255),

    id_publisher INT NOT NULL,

    publication_year INT NOT NULL,

    page_count INT NOT NULL,

    FOREIGN KEY (id_publisher)
        REFERENCES Publishers(id_publisher),

    -- В библиотеке книги только начиная с 1960 года
    CHECK (publication_year >= 1960),

    CHECK (page_count > 0),

    -- Если нет автора, не должно быть соавторов
    CHECK (
        author IS NOT NULL
        OR coauthors IS NULL
    )
);


-- 5. Связь книг и областей знаний
-- Одна книга может относиться к нескольким областям знаний,
-- и одна область знаний может содержать несколько книг.
CREATE TABLE BookKnowledgeAreas (
    id_book INT NOT NULL,

    id_knowledge_area INT NOT NULL,

    PRIMARY KEY (id_book, id_knowledge_area),

    FOREIGN KEY (id_book)
        REFERENCES Books(id_book),

    FOREIGN KEY (id_knowledge_area)
        REFERENCES KnowledgeAreas(id_knowledge_area)
);


-- 6. Экземпляры книг
CREATE TABLE BookCopies (
    id_copy INT PRIMARY KEY IDENTITY(1,1),

    inventory_number INT NOT NULL UNIQUE,

    id_book INT NOT NULL,

    is_available BIT NOT NULL DEFAULT 1,

    FOREIGN KEY (id_book)
        REFERENCES Books(id_book)
);


-- 7. Выдача книг
CREATE TABLE BookIssues (
    id_issue INT PRIMARY KEY IDENTITY(1,1),

    id_reader INT NOT NULL,

    id_copy INT NOT NULL,

    issue_date DATE NOT NULL,

    return_date DATE,

    FOREIGN KEY (id_reader)
        REFERENCES Readers(id_reader),

    FOREIGN KEY (id_copy)
        REFERENCES BookCopies(id_copy),

    CHECK (
        return_date IS NULL
        OR return_date >= issue_date
    )
);