database_setup.sql

-- School Management Database Setup Script
-- Run this script in your MySQL database to create the database and sample data

-- Create database
CREATE DATABASE IF NOT EXISTS school_management;
USE school_management;

-- Create schools table
CREATE TABLE IF NOT EXISTS schools (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(500) NOT NULL,
  latitude FLOAT NOT NULL,
  longitude FLOAT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Add indexes for better performance
CREATE INDEX idx_schools_name ON schools(name);
CREATE INDEX idx_schools_location ON schools(latitude, longitude);

-- Insert sample data
INSERT INTO schools (name, address, latitude, longitude) VALUES
('Delhi Public School', '123 Main Street, New Delhi, India', 28.7041, 77.1025),
('Mumbai International School', '456 Marine Drive, Mumbai, India', 19.0760, 72.8777),
('Bangalore Public School', '789 MG Road, Bangalore, India', 12.9716, 77.5946),
('Chennai Central School', '321 Anna Salai, Chennai, India', 13.0827, 80.2707),
('Kolkata Heritage School', '654 Park Street, Kolkata, India', 22.5726, 88.3639),
('Hyderabad Tech School', '987 Banjara Hills, Hyderabad, India', 17.3850, 78.4867),
('Pune Modern School', '147 Koregaon Park, Pune, India', 18.5204, 73.8567),
('Ahmedabad City School', '258 Satellite, Ahmedabad, India', 23.0225, 72.5714),
('Jaipur Royal School', '369 C-Scheme, Jaipur, India', 26.9124, 75.7873),
('Lucknow Central School', '741 Hazratganj, Lucknow, India', 26.8467, 80.9462);

-- Verify the data
SELECT * FROM schools ORDER BY name;

-- Show table structure
DESCRIBE schools;

-- Show indexes
SHOW INDEX FROM schools;