-- Create the Authors to store information about Authors of books.  
CREATE TABLE Authors (  
  AuthorID Char(5) PRIMARY KEY,  
  Name TEXT NOT NULL,  
  Nationality VARCHAR (20) NOT NULL,  
  BirthYear DATE NOT NULL  
);  
-- Create the Books table to store information about books.  
CREATE TABLE Books (   
  ISBN INTEGER PRIMARY KEY,  -- Primary key for uniquely identifying each book.  
  Title TEXT NOT NULL,       -- Title of the book.  
  Genre VARCHAR (50) NOT NULL,  -- Genre or category of the book.  
  Quantity INTEGER NOT NULL   -- Number of copies available for the book.  
);   

-- Create the Members table to store information about library members.
CREATE TABLE Members (   
  MemberID CHAR(5) PRIMARY KEY,  -- Primary key for uniquely identifying each member.  
  Name TEXT NOT NULL,             -- Name of the member.  
  Email VARCHAR (50) NOT NULL,    -- Email address of the member.  
  Phone VARCHAR (20) NOT NULL     -- Phone number of the member.  
);   

-- Create the Loans table to store information about books borrowed by members.  

CREATE TABLE Loans (   
  LoanID CHAR(5) PRIMARY KEY,    -- Primary key for uniquely identifying each loan.  
  MemberID CHAR(5) NOT NULL,     -- Foreign key referencing the MemberID in the Members table.  
  ISBN INTEGER NOT NULL,         -- Foreign key referencing the ISBN in the Books table.  
  LoanDate DATE NOT NULL,        -- Date when the book was borrowed.  
  ReturnDate DATE,               -- Date when the book was returned (nullable).  
  FOREIGN KEY (MemberID) REFERENCES Members(MemberID),  -- Define foreign key constraint.  
  FOREIGN KEY (ISBN) REFERENCES Books(ISBN)            -- Define foreign key constraint.  
);   
  -- Insert new records into the Books table.  

INSERT INTO Books VALUES (9781544506326, 'Gig mindset', 'Self-help publications', 100);   
INSERT INTO Books VALUES (9780316030908, 'Little women', 'novels', 120);   
INSERT INTO Books VALUES (9781600603679, 'Seeds of change', 'Picture books for children', 50);   
INSERT INTO Books VALUES (9780545165761, 'Endangered', 'Young adult fiction', 70);    
INSERT INTO Books VALUES (9781541619623, 'Right within', 'Self-help publications', 90);  --Minda Harts  
INSERT INTO Books VALUES (9780062278234, 'Nimona', 'Young adult fiction', 130);--ND Stevenson  
INSERT INTO Books VALUES (9780063374683, 'Poor things', 'Fiction', 80); --Alasdair Gray  
INSERT INTO Books VALUES (9780872867659, 'Troublemaker for justice ', 'Biography', 30); --Walter Naegle ,Jacqueline Houtman,Michael G. Long  
INSERT INTO Books VALUES (9780545165760, 'Nature and space in contemporary Scottish writing and art', 'Geocriticism and spatial literary studies', 55);   
INSERT INTO Books VALUES (9780545165767, 'Endangered', 'Young adult fiction', 70);   

-- Insert new records into the Members table.  
INSERT INTO Members VALUES ('O0001', 'Oanh Tran', 'oanhtran@gmail.com', '+84-0923-140-282');   
INSERT INTO Members VALUES ('H0001', 'Ha Phan', 'haphan@gmail.com', '+84-0913-032-112');   
INSERT INTO Members VALUES ('H0002', 'Hoa Dao', 'hoadao@yahoo.com', '+84-0953-211-367');   
INSERT INTO Members VALUES ('L0001', 'Lan Le', 'lanphan@gmail.com', '+84-0989-223-119');  
INSERT INTO Members VALUES ('Y0001', 'Yen Tran', 'yentran@gmail.com', '+84-0912-005-123');  
INSERT INTO Members VALUES ('H0003', 'Hong Tran', 'hongtran@gmail.com', '+84-0923-556-113');  
INSERT INTO Members VALUES ('H0004', 'Hoang Tran', 'hoangtran@gmail.com', '+84-0967-765-331');  
INSERT INTO Members VALUES ('C0001', 'Cuc Pham', 'cucpham@gmail.com', '+84-0936-332-838');  
INSERT INTO Members VALUES ('N0001', 'Nhan Phan', 'nhanphan@gmail.com', '+84-0911-432-658');  
INSERT INTO Members VALUES ('E0001', 'Evan Le', 'evanle@gmail.com', '+84-0367-441-225');  
INSERT INTO Members VALUES ('T0001', 'Tom Hanks', 'tomhanks@gmail.com', '+84-0123-955-347');  
INSERT INTO Members VALUES ('B0001', 'Brad Pitt', 'bradpitt@gmail.com', '+84-0975-223-423');  
INSERT INTO Members VALUES ('J0001', 'Jackie Chan', 'Jackiechan@gmail.com', '+84-0914-346-223');  
INSERT INTO Members VALUES ('J0002', 'Jennifer Garner', 'lanphan@gmail.com', '+84-0324-993-111');  
INSERT INTO Members VALUES ('H0005', 'Halle Berry', 'halle@gmail.com', '+84-0357-321-678');  
INSERT INTO Members VALUES ('J0003', 'Jodie Foster', 'jodie@gmail.com', '+84-0911-332-114');  
INSERT INTO Members VALUES ('A0001', 'Anne Hathaway', 'anne@gmail.com', '+84-0912-140-823');  
INSERT INTO Members VALUES ('J0005', 'Jennifer Aniston', 'jenifer@gmail.com', '+84-0915-356-777');  
INSERT INTO Members VALUES ('J0006', 'Julia Roberts', 'julia@gmail.com', '+84-0985-222-444');  
INSERT INTO Members VALUES ('C0002', 'Cameron Diaz', 'cameronn@gmail.com', '+84-0923-111-237');  

-- Insert new records into the Loans table.  

INSERT INTO Loans VALUES ('05121', 'O0001', 9780316030908, '2024-01-23', '2024-02-15');   
INSERT INTO Loans VALUES ('14537', 'H0034', 9780316030908, '2024-02-03', '2024-03-02');   
INSERT INTO Loans VALUES ('76581', 'H0034', 9781600603679, '2024-03-04', '2024-04-30');   
INSERT INTO Loans VALUES ('23874', 'H0012', 9781544506326, '2034-04-15', '2024-05-06');   
INSERT INTO Loans VALUES ('98234', 'L0001', 9780545165761, '2024-05-02', ''); 

  

Alasdair Gray