MySQL + SQL · Lesson 117
Library Database Project
About this Project
A Library Management database tracks books, members and issued books — a classic Class 12 / BCA project.
Database Schema
CREATE TABLE members (member_id INT PRIMARY KEY, name VARCHAR(50), phone VARCHAR(15));
CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(100), author VARCHAR(50), available INT DEFAULT 1);
CREATE TABLE issues (issue_id INT PRIMARY KEY, member_id INT, book_id INT, issue_date DATE, return_date DATE,
FOREIGN KEY(member_id) REFERENCES members(member_id),
FOREIGN KEY(book_id) REFERENCES books(book_id));Useful Queries
-- books currently issued (not returned)
SELECT m.name, b.title, i.issue_date
FROM issues i JOIN members m ON i.member_id=m.member_id
JOIN books b ON i.book_id=b.book_id
WHERE i.return_date IS NULL;
-- count books issued per member
SELECT m.name, COUNT(*) AS total_issued
FROM issues i JOIN members m ON i.member_id=m.member_id
GROUP BY m.name;
-- available books
SELECT title FROM books WHERE available = 1;Summary
- This project shows a real, exam-ready database design with working queries.
- Create the tables, insert sample data, and run each query to learn by doing.