🗄️ CBSE Class 12 Database Project Lab

Prerequisites: Basic SQL commands, Database concepts, MySQL/Access knowledge

🎯 Project Overview

Project Title: Library Management System
Database: MySQL / MS Access
Marks: 20 (Practical File)
Duration: 4-6 hours

---

📋 Project Requirements

CBSE Guidelines

• Minimum 5 tables with proper relationships
• Complete DDL and DML operations
• Forms and reports screenshots
• Viva questions preparation
• Project documentation

Evaluation Criteria

• Database Design (5 marks)
• Implementation (8 marks)
• Forms & Reports (4 marks)
• Documentation (3 marks)

---

🏗️ Database Design

Table Structure

1. Books Table

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Author VARCHAR(50) NOT NULL,
    Category VARCHAR(30),
    ISBN VARCHAR(20) UNIQUE,
    Price DECIMAL(8,2),
    StockQuantity INT DEFAULT 0,
    PublicationYear YEAR
);

2. Members Table

CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Address VARCHAR(100),
    Phone VARCHAR(15),
    Email VARCHAR(50),
    MembershipDate DATE,
    MemberType ENUM('Student', 'Faculty', 'Staff')
);

3. Issues Table

CREATE TABLE Issues (
    IssueID INT PRIMARY KEY,
    BookID INT,
    MemberID INT,
    IssueDate DATE NOT NULL,
    DueDate DATE,
    ReturnDate DATE,
    Status ENUM('Issued', 'Returned', 'Overdue'),
    FOREIGN KEY (BookID) REFERENCES Books(BookID),
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
);

4. Categories Table

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(30) UNIQUE,
    Description TEXT
);

5. Fine Table

CREATE TABLE Fine (
    FineID INT PRIMARY KEY,
    IssueID INT,
    Amount DECIMAL(5,2),
    Paid BOOLEAN DEFAULT FALSE,
    PaymentDate DATE,
    FOREIGN KEY (IssueID) REFERENCES Issues(IssueID)
);

---

💻 Implementation Steps

Step 1: Database Creation

-- Create database
CREATE DATABASE LibraryManagement;
USE LibraryManagement;

-- Create all tables (as shown above)
-- Insert sample data

Step 2: Sample Data Insertion

-- Insert categories
INSERT INTO Categories VALUES 
(1, 'Computer Science', 'Programming and IT books'),
(2, 'Mathematics', 'Math textbooks and reference'),
(3, 'Physics', 'Physics textbooks and guides');

-- Insert books
INSERT INTO Books VALUES 
(1, 'Python Programming', 'John Doe', 'Computer Science', '978-0134676270', 450.00, 15, 2020),
(2, 'Data Structures', 'Jane Smith', 'Computer Science', '978-0262033844', 380.00, 8, 2018);

Step 3: Essential Queries

Book Search

SELECT b.Title, b.Author, c.CategoryName
FROM Books b
JOIN Categories c ON b.Category = c.CategoryName
WHERE b.Title LIKE '%Python%';

Member Activity Report

SELECT m.Name, COUNT(i.IssueID) AS BooksIssued
FROM Members m
LEFT JOIN Issues i ON m.MemberID = i.MemberID
GROUP BY m.MemberID, m.Name;

Overdue Books

SELECT b.Title, m.Name, i.IssueDate, i.DueDate
FROM Issues i
JOIN Books b ON i.BookID = b.BookID
JOIN Members m ON i.MemberID = m.MemberID
WHERE i.Status = 'Overdue';

---

📊 Forms & Reports

Required Forms

1. Book Entry Form
2. Add new books to library
3. Auto-generate BookID

4. Category selection dropdown

5. Member Registration Form

6. New member registration
7. Auto-generate MemberID

8. Validation for required fields

9. Book Issue Form

10. Book and member selection
11. Automatic due date calculation
12. Stock quantity update

Required Reports

1. Book Availability Report
2. List all books with stock status

3. Category-wise summary

4. Member Activity Report

5. Books issued by each member

6. Monthly statistics

7. Fine Report

8. Overdue books and fines
9. Payment status

---

🎓 Viva Questions

Database Design

1. Why did you choose this database structure?
2. Explain the relationships between tables.
3. What are primary keys and foreign keys?
4. How did you ensure data integrity?

Implementation

1. Explain the normalization process.
2. What are the different types of joins used?
3. How do you handle concurrent book issues?
4. What backup strategies did you implement?

Practical Scenarios

1. How would you add a new book category?
2. How do you generate monthly reports?
3. What happens if a book is lost?
4. How do you handle member renewals?

---

📝 Documentation Template

Project Report Structure

1. Introduction
   - Project objectives
   - Scope and limitations

2. System Analysis
   - Requirements gathering
   - Existing system analysis

3. Database Design
   - ER diagram
   - Table structures
   - Relationships

4. Implementation
   - DDL commands
   - Sample queries
   - Forms and reports

5. Testing
   - Test cases
   - Results

6. Conclusion
   - Achievements
   - Future enhancements

---

⚡ Tips for High Marks

Design Excellence

• ✅ Follow normalization rules (at least 3NF)
• ✅ Proper primary and foreign key relationships
• ✅ Appropriate data types and constraints
• ✅ Clear naming conventions

Implementation Quality

• ✅ All CRUD operations working
• ✅ Error handling for edge cases
• ✅ Data validation in forms
• ✅ Meaningful reports with calculations

Documentation Standards

• ✅ Complete ER diagram
• ✅ All SQL commands documented
• ✅ Screenshots of forms and reports
• ✅ Clear explanation of design choices

---

🔍 Common Mistakes to Avoid

• ❌ Incomplete normalization
• ❌ Missing foreign key constraints
• ❌ No data validation
• ❌ Inadequate testing
• ❌ Poor documentation
• ❌ Missing viva preparation

---

📅 Project Timeline

Week	Task	Completion
1	Database design and ER diagram	✅
2	Table creation and relationships	✅
3	Form development and testing	✅
4	Report generation and documentation	✅

---

This lab follows CBSE Class 12 guidelines and helps score full 20 marks in practical examination.