CREATE DATABASE LibraryManagement
GO

USE LibraryManagement
GO

CREATE TABLE Author
(
	Id INT NOT NULL IDENTITY PRIMARY KEY,
	AuthorName NVARCHAR(100) UNIQUE NOT NULL,
)
GO

CREATE TABLE Publisher
(
	Id INT NOT NULL IDENTITY PRIMARY KEY,
	PublisherName NVARCHAR(100) NOT NULL UNIQUE,
)
GO

CREATE TABLE Category
(
	Id INT NOT NULL IDENTITY PRIMARY KEY,
	CategoryName NVARCHAR(100) NOT NULL UNIQUE,
)
GO

CREATE TABLE Book
(
	Id INT NOT NULL IDENTITY PRIMARY KEY,
	ISBN VARCHAR(30),
	Title NTEXT NOT NULL,
	PublishedYear INT,
	BookDecription NTEXT,
	AuthorId INT,
	PublisherId INT,
	CategoryId INT,
	Quantity INT,

	FOREIGN KEY (PublisherId) REFERENCES dbo.Publisher(Id),
	FOREIGN KEY (AuthorId) REFERENCES dbo.Author(Id),
	FOREIGN KEY (CategoryId) REFERENCES dbo.Category(Id),
)
GO

CREATE TABLE Student
(
	Id INT NOT NULL IDENTITY PRIMARY KEY,
	StudentId VARCHAR(10) NOT NULL,
	StudentName NTEXT NOT NULL,
	Birthday DATE,
	PersonalId VARCHAR(12),
	PasswordStr VARCHAR(MAX),
)
GO

CREATE TABLE LibManager
(
	Id INT NOT NULL IDENTITY PRIMARY KEY,
	Username VARCHAR(20),
	ManagerName NTEXT,
	PersonalId VARCHAR(12),
	Birthday DATE,
	PasswordStr VARCHAR(MAX),
)
GO

CREATE TABLE BookLending
(
	Id INT NOT NULL IDENTITY PRIMARY KEY,
	BooIdOne INT NOT NULL,
	BookIdTwo INT,
	StudentId INT NOT NULL,
	LendingDay DATE,
	IsReturn BIT DEFAULT 0,
	LendingManagerId INT NOT NULL,
	ReturnManagerId INT,

	FOREIGN KEY (BooIdOne) REFERENCES dbo.Book(Id),
	FOREIGN KEY (BookIdTwo) REFERENCES dbo.Book(Id),
	FOREIGN KEY (StudentId) REFERENCES dbo.Student(Id),
	FOREIGN KEY (LendingManagerId) REFERENCES dbo.LibManager(Id),
	FOREIGN KEY (ReturnManagerId) REFERENCES dbo.LibManager(Id)
)