LibraryManagementSystem

-- Create the database
CREATE DATABASE LibraryManagementSystem;

-- Use the database
USE LibraryManagementSystem;

-- Create the Students table
CREATE TABLE Students (
    StudentID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    PasswordHash VARCHAR(255) NOT NULL,
    RecoveryQuestion VARCHAR(255),
    RecoveryAnswer VARCHAR(255),
    RegistrationDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    LastUpdated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create the Admins table
CREATE TABLE Admins (
    AdminID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    PasswordHash VARCHAR(255) NOT NULL,
    LastUpdated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create the Categories table
CREATE TABLE Categories (
    CategoryID INT AUTO_INCREMENT PRIMARY KEY,
    CategoryName VARCHAR(100) NOT NULL
);

-- Create the Authors table
CREATE TABLE Authors (
    AuthorID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);

-- Create the Books table
CREATE TABLE Books (
    BookID INT AUTO_INCREMENT PRIMARY KEY,
    Title VARCHAR(200) NOT NULL,
    CategoryID INT,
    AuthorID INT,
    AvailableCopies INT NOT NULL DEFAULT 0,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID),
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

-- Create the IssuedBooks table
CREATE TABLE IssuedBooks (
    IssueID INT AUTO_INCREMENT PRIMARY KEY,
    StudentID INT,
    BookID INT,
    IssueDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ReturnDate DATETIME,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

-- Insert sample data into Students table
INSERT INTO Students (Name, Email, PasswordHash, RecoveryQuestion, RecoveryAnswer)
VALUES
    ('Alice Johnson', 'alice.johnson@example.com', SHA2('password123', 256), 'Your first pet\'s name?', 'Buddy'),
    ('Bob Smith', 'bob.smith@example.com', SHA2('bobpassword', 256), 'Your mother\'s maiden name?', 'Smith'),
    ('Charlie Brown', 'charlie.brown@example.com', SHA2('charlie123', 256), 'Your favorite color?', 'Blue'),
    ('Diana Prince', 'diana.prince@example.com', SHA2('wonderwoman', 256), 'Your birthplace?', 'Themyscira'),
    ('Eve Adams', 'eve.adams@example.com', SHA2('evepassword', 256), 'Your favorite book?', '1984'),
    ('Frank White', 'frank.white@example.com', SHA2('frank123', 256), 'Your first school?', 'Springfield'),
    ('Grace Hopper', 'grace.hopper@example.com', SHA2('grace123', 256), 'Your favorite teacher?', 'Mr. Johnson'),
    ('Hannah Lee', 'hannah.lee@example.com', SHA2('hannah123', 256), 'Your childhood nickname?', 'Hanny');

-- Insert sample data into Admins table
INSERT INTO Admins (Name, Email, PasswordHash)
VALUES
    ('Admin One', 'admin1@library.com', SHA2('admin123', 256)),
    ('Admin Two', 'admin2@library.com', SHA2('adminpass', 256));

-- Insert sample data into Categories table
INSERT INTO Categories (CategoryName)
VALUES
    ('Fantasy'),
    ('Mystery'),
    ('Thriller'),
    ('Horror'),
    ('Science Fiction'),
    ('Non-Fiction'),
    ('Romance'),
    ('Adventure');

-- Insert sample data into Authors table
INSERT INTO Authors (Name)
VALUES
    ('J.K. Rowling'),
    ('George Orwell'),
    ('J.R.R. Tolkien'),
    ('Agatha Christie'),
    ('Dan Brown'),
    ('Stephen King'),
    ('Jane Austen'),
    ('Mark Twain');

-- Insert sample data into Books table
INSERT INTO Books (Title, CategoryID, AuthorID, AvailableCopies)
VALUES
    ('Harry Potter and the Sorcerer\'s Stone', 1, 1, 5),
    ('1984', 5, 2, 3),
    ('The Hobbit', 8, 3, 4),
    ('Murder on the Orient Express', 2, 4, 6),
    ('The Da Vinci Code', 3, 5, 2),
    ('The Shining', 4, 6, 3),
    ('Pride and Prejudice', 7, 7, 7),
    ('The Adventures of Tom Sawyer', 8, 8, 5);

-- Insert sample data into IssuedBooks table
INSERT INTO IssuedBooks (StudentID, BookID, IssueDate, ReturnDate)
VALUES
    (1, 1, '2023-12-01', '2023-12-09'),
    (2, 3, '2023-12-05', NULL),
    (3, 5, '2023-12-10', NULL),
    (4, 6, '2023-12-12', '2023-12-18'),
    (5, 2, '2023-11-25', '2023-12-01'),
    (6, 4, '2023-11-20', '2023-11-29'),
    (7, 7, '2023-12-15', NULL),
    (8, 8, '2023-12-18', NULL);

-- Queries to demonstrate functionalities

-- 1. View all books available in the library
SELECT 
    Books.BookID,
    Books.Title,
    Categories.CategoryName,
    Authors.Name AS AuthorName,
    Books.AvailableCopies
FROM Books
JOIN Categories ON Books.CategoryID = Categories.CategoryID
JOIN Authors ON Books.AuthorID = Authors.AuthorID;

-- 2. Update a student's profile (example: update email for StudentID 1)
UPDATE Students
SET Email = 'new.alice@example.com'
WHERE StudentID = 1;

-- 3. View issued books for a specific student
SELECT 
    IssuedBooks.IssueID,
    Books.Title,
    IssuedBooks.IssueDate,
    IssuedBooks.ReturnDate
FROM IssuedBooks
JOIN Books ON IssuedBooks.BookID = Books.BookID
WHERE IssuedBooks.StudentID = 1;

-- 4. Admin adding a new book
INSERT INTO Books (Title, CategoryID, AuthorID, AvailableCopies)
VALUES ('The Catcher in the Rye', 6, 8, 4);

-- 5. Admin updating book details
UPDATE Books
SET AvailableCopies = 10
WHERE BookID = 1;

-- 6. Admin deleting a book
DELETE FROM Books
WHERE BookID = 8;

-- 7. Admin searching a student by ID
SELECT * FROM Students WHERE StudentID = 1;

-- 8. Admin viewing student details
SELECT * FROM Students;

-- 9. Admin changing their password
UPDATE Admins
SET PasswordHash = SHA2('newadminpassword', 256)
WHERE AdminID = 1;