The Motor Insurance Management System is a relational database project developed using MySQL. It models the core operations of a motor insurance company, including customer management, vehicle registration, quotation generation, premium calculation, policy issuance, payment processing, claims, and business reporting.
- Design a normalized relational database.
- Implement business relationships using primary and foreign keys.
- Store and manage insurance data efficiently.
- Demonstrate SQL concepts from basic to advanced.
- Generate business reports using SQL queries.
- MySQL 8.0
- MySQL Workbench
- Region Management
- State Management
- City Management
- User & Role Management
- Login Management
- Broker Management
- Sales Agent Management
- Customer Management
- Vehicle Management
- Insurance Quote Management
- Coverage Management
- Premium Rate Management
- Payment Management
- Policy Management
- Claim Management
- Audit Logging
- 1 Database
- mi_regions
- mi_states
- mi_cities
- mi_vehicle_make
- mi_vehicle_model
- mi_vehicle_body
- mi_vehicle_color
- mi_vehicle_category
- mi_lov_master
- mi_coverages
- mi_user_roles
- mi_users
- mi_login_users
- mi_brokers
- mi_sales_agents
- mi_customers
- mi_vehicles
- mi_quotes
- mi_quote_coverages
- mi_premium_rates
- mi_payments
- mi_policies
- mi_claims
- mi_audit_log
- 3 Views
- 3 Indexes
- 4 Stored Functions
- 4 Stored Procedures
- 4 Triggers
- 2 Transactions
- 30 Business Scenarios
- Database Creation
- Table Creation
- Constraints
- Primary Keys
- Foreign Keys
- Data Insertion
- INNER JOIN
- LEFT JOIN
- Aggregate Functions
- GROUP BY
- ORDER BY
- WHERE
- HAVING
- Window Functions (RANK)
- Views
- Indexes
- Stored Functions
- Stored Procedures
- Triggers
- Transactions
- Top Premium Paying Customer
- Customer Owning Maximum Vehicles
- Customers with Highest Premium Spending
- Customers Who Used Online Payment
- Customer Policy History
- Broker Generating Highest Premium
- Sales Agent Generating Highest Business
- Broker-wise Business Report
- Agent-wise Business Report
- Broker Ranking by Business
- Vehicle Category with Highest Policies
- Most Selected Coverage
- Coverage Generating Highest Premium
- Average Premium by Vehicle Category
- Vehicles with Quotes Generated
- Successful Payment Details
- Paid Quotes with Generated Policies
- Lowest Premium Policy
- Top 10 Premium Policies
- Active Policy Report
- Policies with Registered Claims
- Highest Claim Amount
- Claim Summary Report
- City with Highest Customers
- State with Highest Policies
- Region with Highest Business
- Monthly Premium Collection Report
- Active vs Inactive Customers
- Customer Ranking by Premium
- Complete Insurance Dashboard Report
- Normalized database design
- Business-oriented table relationships
- Secure and structured data storage
- Automated business operations using procedures and triggers
- Performance optimization using indexes
- Reusable business logic using functions
- Business analytics through SQL reporting scenarios
This project demonstrates the implementation of a complete Motor Insurance Management System using MySQL. It combines database design, SQL programming, and business reporting into a practical application that showcases both fundamental and advanced database concepts.