CREATE TABLE Category (
    CategoryId int IDENTITY(1,1) PRIMARY KEY,
    CategoryName varchar(255) NOT NULL
);

CREATE TABLE Book (
    BookId int IDENTITY(1,1) PRIMARY KEY,
    BookName varchar(255) NOT NULL,
    CategoryId int FOREIGN KEY REFERENCES Category(CategoryId),
	Available bit NOT NULL DEFAULT 1
);

CREATE TABLE Client (
    ClientId int IDENTITY(1,1) PRIMARY KEY,
    ClientFullName varchar(255) NOT NULL,
	IdentityCard varchar(255) NOT NULL
);

CREATE TABLE Rent (
    RentId int IDENTITY(1,1) PRIMARY KEY,
    BookId int FOREIGN KEY REFERENCES Book(BookId),
    ClientId int FOREIGN KEY REFERENCES Client(ClientId),
	RentalDate datetime NOT NULL DEFAULT getdate(),
	Returned bit NOT NULL DEFAULT 0
);


Insert into Category (CategoryName) values ('Cooking')
Insert into Category (CategoryName) values ('Mystery & Thriller')
Insert into Category (CategoryName) values ('Business')

Insert into Book (BookName, CategoryId) values ('Cooking Light', 1)
Insert into Book (BookName, CategoryId) values ('Prophecy', 2)
Insert into Book (BookName, CategoryId) values ('Shift', 3)
Insert into Book (BookName, CategoryId) values ('The Confession', 2)

Insert Client (ClientFullName, IdentityCard) values ('Freddy Krueger', '8776655M')
Insert Client (ClientFullName, IdentityCard) values ('Tony Montana', '3355665M')
Insert Client (ClientFullName, IdentityCard) values ('Jamie Oliver', '7775433A')