مشروع عملي متكامل: نظام إدارة مكتبة

CREATE TABLE Books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    published_year YEAR,
    genre VARCHAR(50),
    available_copies INT DEFAULT 0
);

CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(15),
    registration_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE BorrowedBooks (
    borrow_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    book_id INT,
    borrow_date DATE DEFAULT CURRENT_DATE,
    return_date DATE,
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (book_id) REFERENCES Books(book_id)
);

INSERT INTO Books (title, author, published_year, genre, available_copies) 
VALUES 
('The Great Gatsby', 'F. Scott Fitzgerald', 1925, 'Fiction', 5),
('1984', 'George Orwell', 1949, 'Dystopian', 3);

INSERT INTO Users (name, email, phone) 
VALUES 
('Ahmed Ali', '[email protected]', '0123456789'),
('Sara Mohamed', '[email protected]', '0987654321');

SELECT * FROM Books WHERE available_copies > 0;

SELECT 
    Users.name AS User_Name, 
    Books.title AS Book_Title, 
    BorrowedBooks.borrow_date, 
    BorrowedBooks.return_date
FROM BorrowedBooks
INNER JOIN Users ON BorrowedBooks.user_id = Users.user_id
INNER JOIN Books ON BorrowedBooks.book_id = Books.book_id;

UPDATE Books SET available_copies = available_copies - 1 WHERE book_id = 1;
INSERT INTO BorrowedBooks (user_id, book_id) VALUES (1, 1);

UPDATE Books SET available_copies = available_copies + 1 WHERE book_id = 1;
UPDATE BorrowedBooks SET return_date = CURRENT_DATE WHERE borrow_id = 1;

CREATE INDEX idx_title ON Books (title);

EXPLAIN SELECT Users.name, Books.title FROM BorrowedBooks
INNER JOIN Users ON BorrowedBooks.user_id = Users.user_id
INNER JOIN Books ON BorrowedBooks.book_id = Books.book_id;

mysqldump -u root -p library > library_backup.sql