CREATE DATABASE Library ON PRIMARY
    (NAME = 'Library',
    FILENAME = 'D:\Thai1904\Library.mdf',
    SIZE = 3072KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1024KB)
LOG ON
    (NAME = 'Library_log',
    FILENAME = 'D:\Thai1904\Library.Ldf',
    SIZE = 1024KB,
    MAXSIZE = 2048KB,
    FILEGROWTH = 10%)
GO

USE Library
GO

--Tao bang
CREATE TABLE Publisher
(
    Name VARCHAR(20) NOT NULL,
    Address VARCHAR(30),
    Phone VARCHAR(10)
)
GO

CREATE TABLE Book
(
    Book_ID SMALLINT NOT NULL,
    Ttile VARCHAR(30) NOT NULL,
    Publisher_Name VARCHAR(20)
)
GO

CREATE TABLE Library_Branch
(
    Branch_ID SMALLINT NOT NULL,
    Branch_Name VARCHAR(20) NOT NULL,
    Address VARCHAR(30)
)
GO

CREATE TABLE Book_Authors
(
    Book_ID SMALLINT NOT NULL,
    Author_Name VARCHAR(20) NOT NULL
)
GO

CREATE TABLE Borrower
(
    Card_NO SMALLINT NOT NULL,
    Name VARCHAR(20),
    Adress VARCHAR(30),
    Phone VARCHAR(10)
)
GO

CREATE TABLE Book_Copies
(
    Book_ID SMALLINT NOT NULL,
    Branch_ID SMALLINT NOT NULL,
    No_Of_Copies SMALLINT
)
GO

CREATE TABLE Book_Loans
(
    Book_ID SMALLINT NOT NULL,
    Branch_ID SMALLINT NOT NULL,
    Card_NO SMALLINT NOT NULL,
    Date_Out DATETIME,
    Due_Date DATETIME
)
GO

--Tao khoa chinh
ALTER TABLE Publisher
    ADD CONSTRAINT pk_pub PRIMARY KEY(Name);
GO

ALTER TABLE Book
    ADD CONSTRAINT pk_boo PRIMARY KEY(Book_ID);
GO

ALTER TABLE Library_Branch
    ADD CONSTRAINT pk_lib PRIMARY KEY(Branch_ID);
GO

ALTER TABLE Book_Authors
    ADD CONSTRAINT pk_boa PRIMARY KEY(Book_ID, Author_Name);
GO

ALTER TABLE Borrower
    ADD CONSTRAINT pk_bor PRIMARY KEY(Card_NO);
GO

ALTER TABLE Book_Copies
    ADD CONSTRAINT pk_boc PRIMARY KEY(Book_ID, Branch_ID);
GO

ALTER TABLE Book_Loans
    ADD CONSTRAINT pk_bol PRIMARY KEY(Book_ID, Branch_ID, Card_NO);
GO

--Tao khoa ngoai
ALTER TABLE Book
    ADD CONSTRAINT fk_booPub FOREIGN KEY (Publisher_Name) REFERENCES Publisher(Name);
GO

ALTER TABLE Book_Authors
    ADD CONSTRAINT fk_boaBoi FOREIGN KEY (Book_id) REFERENCES Book(Book_id);
GO

ALTER TABLE Book_Copies
    ADD CONSTRAINT fk_bocBoi FOREIGN KEY (Book_id) REFERENCES Book(Book_id);
GO

ALTER TABLE Book_Loans
    ADD CONSTRAINT fk_bolBoi FOREIGN KEY (Book_id) REFERENCES Book(Book_id);
GO

ALTER TABLE Book_Loans
    ADD CONSTRAINT fk_bolBri FOREIGN KEY (Branch_id) REFERENCES Library_Branch(Branch_id);
GO

ALTER TABLE Book_Loans
    ADD CONSTRAINT fk_bolcan FOREIGN KEY (Card_no) REFERENCES Borrower(Card_no);
GO

--Insert Data
--Publisher
INSERT INTO Publisher
    VALUES ('Amazing Books', '9012 Great Rd', '333-3333');
GO

INSERT INTO Publisher
    VALUES ('Plume', '1234 Cool Ave', '555-5555');
GO

INSERT INTO Publisher
    VALUES ('Random', 'House 5678 Neat St', '444-4444');
GO

--Book
--delete from Book;
INSERT INTO Book
    VALUES (1, 'The Lost Tribe', 'Random House');
GO

INSERT INTO Book
    VALUES (2, 'The Lost Tribe', 'Random House');
GO