db_setup_library_stats.sql

-- SQL script to create tables for library statistics in library_db database

CREATE DATABASE IF NOT EXISTS library_db;
USE library_db;

-- Table to store total books count
CREATE TABLE IF NOT EXISTS total_books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    count INT NOT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table to store borrowed books count
CREATE TABLE IF NOT EXISTS borrowed_books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    count INT NOT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table to store active students count
CREATE TABLE IF NOT EXISTS active_students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    count INT NOT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table to store overdue returns count
CREATE TABLE IF NOT EXISTS overdue_returns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    count INT NOT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert initial values
INSERT INTO total_books (count) VALUES (1200);
INSERT INTO borrowed_books (count) VALUES (350);
INSERT INTO active_students (count) VALUES (500);
INSERT INTO overdue_returns (count) VALUES (25);