SALVATORE

CREATE TABLE books (
    book_id int PRIMARY KEY IDENTITY(1,1),
    title varchar(255) NOT NULL,
    publication_date date NOT NULL,
    editor varchar(255),
    status varchar(10) NOT NULL,
    lent_id int,
   -- FOREIGN KEY (book_id) REFERENCES book_authors(book_id),
   -- FOREIGN KEY (lent_id) REFERENCES lent(lent_id)
);

DROP TABLE BOOKS;
DROP TABLE authors;
DROP TABLE book_authors;
DROP TABLE lent;

CREATE TABLE authors (
    author_id int PRIMARY KEY IDENTITY(1,1),
    first_name varchar(255) NOT NULL,
    last_name varchar(255) NOT NULL,
    birth_date date NOT NULL
)


CREATE TABLE book_authors (
    book_id int PRIMARY KEY,
    author_id int,
  --  FOREIGN KEY (book_id) REFERENCES books(book_id),
    --FOREIGN KEY (author_id) REFERENCES authors(author_id)
);


CREATE TABLE lent (
    lent_id int PRIMARY KEY IDENTITY(1,1),
    book_id int NOT NULL,
    user_first_name varchar(255) NOT NULL,
    user_last_name varchar(255) NOT NULL,
    user_birth_date date NOT NULL,
    user_address varchar(255) NOT NULL,
    user_id_document varchar(255) NOT NULL,
    lent_date date NOT NULL,
    return_date date NOT NULL,
   -- FOREIGN KEY (book_id) REFERENCES books(book_id)
);

ALTER TABLE Books
ADD CONSTRAINT FK_CategoryID
FOREIGN KEY (book_id) REFERENCES book_authors(book_id) ;

ALTER TABLE Books
ADD CONSTRAINT FK_LentID
FOREIGN KEY(lent_id) REFERENCES lent(lent_id);

ALTER TABLE book_authors
ADD CONSTRAINT FK_Book_Authors_ID1
FOREIGN KEY (book_id) REFERENCES books(book_id);

ALTER TABLE book_authors
ADD CONSTRAINT FK_Book_Authors_ID2
FOREIGN KEY(author_id) REFERENCES authors(author_id);

ALTER TABLE lent
ADD CONSTRAINT FK_LentID2
FOREIGN KEY (book_id) REFERENCES books(book_id);