DATABASE MAINTENANCE

?? Table of Contents

1. Introduction
2. Database Maintenance – Overview
3. Listing
4. Database Dashboard
5. Columns List – Dynamic
6. Columns List – Master
7. Columns List – Import
8. Stored Procedures List
9. Views List
10. Tables List
11. Primary Keys List
12. Tables Without Primary Key
13. Indexes List
14. Missing Objects – Overview
15. Missing Tables
16. Missing Views
17. Missing Procedures
18. Missing Columns
19. Add Objects – Overview
20. Add Table
21. Add View
22. Add Procedure
23. Add Column
24. Run Query – Overview
25. Select Query
26. Update Query
27. Create Table
28. SQL Reports
29. SQL Report
30. Business Benefits
31. Best Practices & Security Notes

1?? Introduction

The Database Maintenance module in EasyLib is a powerful administrative and technical tool designed to ensure the stability, integrity, performance, and long-term reliability of the EasyLib application database.

As EasyLib manages critical institutional data—including library records, academic information, transactions, user activity, and system configurations—maintaining a healthy and consistent database structure is essential. The Database Maintenance module provides administrators with controlled visibility and management capabilities over the underlying database of objects that support these operations.

This module enables system administrators, database administrators, and authorized technical users to:

1. Monitor the overall database structure and health
2. Validate the presence of required database objects after installations, upgrades, or migrations
3. Identify missing, inconsistent, or improperly configured database components
4. Maintain data integrity and referential consistency
5. Improve system performance and query efficiency
6. Reduce dependency on direct database access by providing a safe, application-level interface

The Database Maintenance module is organized into logical functional areas, each serving a specific purpose:

1. Listing and Dashboard screens provide a consolidated view of database metadata and health indicators
2. Schema inspection screens display detailed information about tables, columns, keys, indexes, views, and procedures
3. Missing object screens help identify gaps between the expected and actual database structure
4. Add and Run Query screens allow controlled creation and maintenance of database objects
5. SQL Reports enable advanced reporting directly from database queries

By centralizing these capabilities within EasyLib, the Database Maintenance module helps institutions:

1. Minimize system downtime
2. Prevent data corruption and duplication
3. Ensure smooth upgrades and feature rollouts
4. Support audits, compliance, and accreditation requirements

Overall, the Database Maintenance module acts as a foundation layer for system governance, providing the technical backbone required for EasyLib to function reliably and efficiently across departments and campuses.

The Database Dashboard provides a high-level summary of the EasyLib database structure.

It gives administrators an instant snapshot of key database objects and their total counts, helping them quickly understand the size, complexity, and health of the system database.

This screen acts as the entry point for database maintenance and monitoring activities.

Navigation Path

Database Maintenance ? Listing ? Database Dashboard

Screen Overview

The dashboard displays database schema components in a tabular summary format with two main columns:

Schema Types Explained

Tables

1. Represents the total number of database tables in EasyLib.
2. Tables store core data such as:
3. Student records
4. Book and circulation data
5. Academic configurations
6. Transactions and logs

?? A large table count indicates a feature-rich and modular system.

Columns

1. Shows the total number of columns across all tables.
2. Columns define individual data fields such as:
3. IDs
4. Names
5. Dates
6. Status flags

?? Helps administrators assess schema depth and data granularity.

Views

1. Displays the number of database views.
2. Views are pre-defined queries used to:
3. Simplify complex data retrieval
4. Improve reporting performance
5. Enforce data abstraction and security

?? High view usage indicates optimized reporting and data access.

Stored Procedures

1. Indicates the total number of stored procedures.
2. Stored procedures handle:
3. Business logic
4. Validation rules
5. Batch processing
6. Performance-critical operations

?? Stored procedures reduce application load and improve execution speed.

How This Dashboard Is Used

Administrators use the Database Dashboard to:

1. Verify database completeness after:
2. Fresh installation
3. Version upgrades
4. Data migration
5. Detect unexpected changes in database size
6. Cross-check missing objects using:
7. Missing Tables
8. Missing Views
9. Missing Procedures
10. Missing Columns
11. Plan maintenance and optimization activities

Columns List – Dynamic

The Columns List – Dynamic screen provides a live, real-time view of all database columns present in the EasyLib database.

This screen dynamically reads column metadata directly from the database schema, ensuring that the information displayed is always up to date with the actual database structure.

It is primarily used by system administrators and database administrators to inspect, verify, and analyze table columns across the entire EasyLib system.

Navigation Path

Database Maintenance ? Listing ? Columns List Dynamic

Screen Overview

The screen displays a tabular list of database columns, with built-in search, pagination, export, and insert options.

Column Descriptions

?? Columns with null length indicate data types where length is not applicable.

Functional Controls

Search

1. Allows quick filtering by:
2. Column name
3. Table name
4. Data type
5. Useful for locating specific fields instantly.

Pagination

1. Displays records in pages (e.g., 10 per page).
2. Helps manage very large schemas efficiently (10,000+ columns).

Export

1. Exports the full column list to Excel or CSV format.
2. Useful for:
3. Audits
4. Documentation
5. Impact analysis before upgrades

Insert

1. Allows administrators to add a new column entry.
2. Typically used in controlled schema updates.
3. ? Should only be used by authorized technical users.

How This Screen Is Used

Administrators commonly use this screen to:

1. Verify column existence after:
2. Feature deployment
3. Database migration
4. Patch installation
5. Cross-check with:
6. Columns List Master
7. Missing Columns
8. Analyze column data types for performance tuning
9. Support troubleshooting and development activities

Dynamic vs Master Columns List

Columns List – Master

The Columns List – Master screen represents the reference (master) definition of all database columns that are expected to exist in the EasyLib system.

Unlike the Dynamic Columns List, which reads live data from the database, the Master Columns List acts as a baseline schema repository. It defines how the database should look according to EasyLib’s design and version standards.

This screen is essential for:

1. Schema validation
2. Upgrade and patch verification
3. Identifying missing or inconsistent columns

Navigation Path

Database Maintenance ? Listing ? Columns List Master

Screen Overview

The screen displays a structured list of expected database columns, maintained centrally as a master reference.

It includes search, pagination, export, and insert functions for efficient management.

Column Descriptions

?? null in Maximum Length indicates data types where size is not applicable.

Functional Controls

Search

1. Quickly locate columns by name, table, or data type.
2. Useful when validating specific features or modules.

Pagination

1. Displays large master datasets in manageable pages.
2. Helps navigate tens of thousands of column definitions efficiently.

Export

1. Exports the master column list for:
2. Documentation
3. Version comparison
4. Audit and compliance review

Insert

1. Allows adding a new master column definition.
2. Typically used during:
3. New module implementation
4. Product enhancement releases
5. ? Changes here should align with official EasyLib schema updates.

How This Screen Is Used

Administrators and technical teams use this screen to:

1. Compare against Columns List – Dynamic
2. Identify discrepancies using Missing Columns
3. Prepare databases before:
4. Production rollout
5. Version upgrade
6. Maintain a consistent database structure across campuses

Master vs Dynamic Columns List

Columns List Import

The Columns List Import screen is used to bulk upload column definitions into the EasyLib master schema repository using an import file (typically Excel or CSV).

This screen helps administrators quickly populate or update the Columns List – Master without manually inserting each column, especially during:

1. New database setup
2. Version upgrades
3. Large schema changes
4. Multi-campus standardization

Navigation Path

Database Maintenance ? Listing ? Columns List Import

Why This Screen Is Important

Managing thousands of database columns manually is time-consuming and error-prone.

The Columns List Import feature ensures:

1. Faster schema onboarding
2. Consistent column definitions across environments
3. Reduced manual errors
4. Smooth validation with Dynamic Columns and Missing Columns

What Gets Imported

Each imported record usually contains:

These records populate the Columns List – Master.

Typical Workflow

Prepare Import File

1. Download or prepare the template in Excel/CSV format
2. Ensure:
3. Column names match EasyLib standards
4. Table names are correct
5. Data types are valid SQL types

Upload File

1. Select the prepared file using the import option
2. System reads all column definitions from the file

Validation

During import, the system validates:

1. Duplicate column definitions
2. Invalid data types
3. Missing mandatory fields
4. Structural mismatches

Any errors are highlighted before final submission.

Import Execution

1. Valid records are added to Columns List – Master
2. Existing records may be:
3. Skipped
4. Updated
5. (depending on system rules and configuration)

Post-Import Usage

After successful import, the data is used by:

1. Columns List – Master
2. Missing Columns
3. Schema comparison tools
4. Upgrade readiness checks

Views List

What is a View?

A database view is a virtual table created from a stored SQL query. It does not store data physically; instead, it presents data dynamically from one or more tables.

In EasyLib, views are heavily used for:

1. Reports (attendance, exams, fees, LMS, library)
2. Dashboards
3. Role-based data access
4. Simplifying complex joins across modules

What the Views List Screen Does

The Views List screen provides a complete inventory of all views available in the database.

It helps administrators:

1. Confirm that required views exist
2. Detect missing or broken views after upgrades
3. Validate reporting dependencies

Why This Screen is Critical

If a view is missing or invalid:

1. Reports may fail
2. Dashboards may show blank data
3. Performance may degrade if fallback queries run

Typical Use Cases

1. Post-upgrade verification
2. Troubleshooting report issues
3. Audit of reporting layer
4. Database health checks

Tables List

What is a Table?

A table is the core physical structure where EasyLib stores data such as:

1. Students
2. Staff
3. Exams
4. Fees
5. Library transactions

Purpose of Tables List

The Tables List screen provides a live snapshot of all physical tables in the database.

What This Screen Helps With

1. Verifying that all required module tables exist
2. Confirming table creation after installation
3. Monitoring schema size and growth
4. Supporting migrations and backups

Why It Matters

Missing or corrupted tables can:

1. Break entire modules
2. Cause data loss
3. Prevent system startup

This screen acts as the first checkpoint for database completeness.

Admin Insight

Large row counts may indicate:

1. High transaction volume
2. Missing archiving strategy
3. Performance tuning requirements

Primary Keys List

What is a Primary Key?

A primary key (PK) uniquely identifies each record in a table.

Without a primary key:

1. Duplicate records can exist
2. Relationships fail
3. Indexing becomes inefficient

Role of Primary Keys in EasyLib

EasyLib relies on primary keys for:

1. Student and staff identity
2. Exam result mapping
3. Fee transactions
4. Library circulation tracking

What the Primary Keys List Screen Shows

This screen lists:

1. Tables that have PKs
2. Columns used as PKs
3. Constraint definitions

Why This Screen is Important

It ensures:

1. Referential integrity
2. Reliable joins between tables
3. Consistent reporting results
4. Faster query execution

DBA Perspective

Tables without PKs are high-risk objects and should be reviewed immediately.

Tables Without Primary Key – Deep Explanation

What This Screen Identifies

This screen highlights tables that:

1. Exist physically
2. BUT do not have any primary key defined

Why Tables Without PKs Are Dangerous

Such tables can cause:

1. Duplicate data
2. Inconsistent updates
3. Failed foreign key relationships
4. Poor query performance

Why EasyLib Tracks This

EasyLib enforces best-practice database design, and this screen acts as:

1. A schema quality checker
2. A risk detection tool

When This Screen Is Used

1. After schema import
2. After manual database changes
3. During performance troubleshooting
4. Before audits or upgrades

Corrective Action

Admins usually:

1. Review table purpose
2. Add appropriate primary key
3. Align with EasyLib master schema

Indexes List

What is an Index?

An index improves database performance by allowing faster data retrieval, similar to an index in a book.

Why Indexes Matter in EasyLib

EasyLib databases handle:

1. Thousands of students
2. Large exam records
3. Daily attendance and fee transactions

Indexes ensure:

1. Faster reports
2. Smooth dashboards
3. Responsive UI

What the Indexes List Screen Shows

1. Index names
2. Associated tables
3. Indexed columns
4. Index types (unique, clustered, non-clustered)

Performance Impact

Missing or incorrect indexes can lead to:

1. Slow searches
2. Timeout errors
3. Heavy database load

Excessive indexes can:

1. Slow down inserts/updates
2. Increase storage usage

How Admins Use This Screen

1. Identify missing performance indexes
2. Detect duplicate or unused indexes
3. Optimize slow-running queries

1. Missing – Overview

What Does “Missing” Mean in EasyLib?

The Missing section identifies gaps between the expected EasyLib master schema and the actual database schema.

EasyLib maintains a reference definition of required:

1. Tables
2. Views
3. Stored Procedures
4. Columns

If any of these objects are not found in the live database, they appear under Missing.

Why the Missing Section Is Critical

Missing database objects can:

1. Break modules
2. Cause runtime errors
3. Prevent reports from loading
4. Block upgrades and patches

This section acts as a schema validation and compliance checker.

2. Missing Tables – Deep Explanation

What Are Missing Tables?

Tables that:

1. Are defined in the EasyLib master schema
2. But do NOT exist in the current database

Common Causes

1. Incomplete installation
2. Failed upgrade scripts
3. Manual deletion
4. Partial database restore
5. Environment mismatch (Prod vs Test)

Impact on EasyLib

If a required table is missing:

1. Module features stop working
2. Data cannot be stored
3. System may throw fatal errors

Example:

1. Missing StudentAttendance table ? attendance cannot be saved

How Admins Use This Screen

1. Validate schema after installation
2. Verify post-upgrade completeness
3. Prepare database before go-live

Recommended Action

1. Recreate table using Add Table
2. Restore from backup
3. Run official EasyLib schema scripts

3. Missing Views – Deep Explanation

What Are Missing Views?

Views that:

1. Are expected by EasyLib reports or dashboards
2. But are not present in the database

Why Views Go Missing

1. Upgrade scripts not executed
2. Manual DB cleanup
3. View creation failure due to dependency errors

Impact

1. Reports return empty results
2. Dashboards fail to load
3. Performance issues due to fallback queries

Admin Best Practice

Always validate Missing Views before:

1. Enabling reporting modules
2. Sharing analytics with management

4. Missing Procedures – Deep Explanation

What Are Missing Procedures?

Stored procedures that:

1. Perform business logic
2. Handle bulk processing
3. Support data validation

Why Stored Procedures Matter in EasyLib

EasyLib uses stored procedures for:

1. Exam result calculations
2. Fee posting
3. Attendance processing
4. Bulk imports

Impact of Missing Procedures

1. Actions fail silently or throw errors
2. Imports do not complete
3. Transactions remain incomplete

Resolution

1. Re-add procedure using Add Procedure
2. Re-run upgrade scripts
3. Validate procedure permissions

5. Missing Columns – Deep Explanation

What Are Missing Columns?

Columns that:

1. Are expected inside existing tables
2. But do not exist in the live schema

Common Scenarios

1. Partial upgrade
2. Manual schema alteration
3. Version mismatch

Impact

1. Data not saved correctly
2. Application errors
3. Inconsistent reports

Example:

1. Missing IsActive column ? filtering logic fails

Admin Action

1. Add column using Add Column
2. Verify datatype and default values
3. Reindex table if needed

ADD Section – Controlled Schema Creation

6. Add – Overview

The Add section allows controlled creation of missing database objects without direct SQL access.

This ensures:

1. Standardization
2. Reduced risk
3. Audit-friendly changes

Add Table

Creates a new table aligned with EasyLib schema standards.

What Happens Internally

1. Table is created with defined columns
2. Primary key constraints applied
3. Default indexes added
4. Audit fields included (CreatedDate, CreatedBy)

When to Use

1. Missing table identified
2. New module deployment
3. Schema recovery

Add View

Creates or recreates a database view using standard SQL definitions.

Usage Scenario

1. View missing after upgrade
2. Reporting layer not working

Add Procedure

Creates stored procedures required for EasyLib business logic.

Add Column

Purpose

Add missing columns to existing tables.

Important Considerations

1. Data type accuracy
2. Default values
3. Nullability
4. Impact on existing data

Admin Tip

After adding a column:

1. Update related views
2. Rebuild indexes if needed

11. Run Query

Allows administrators to execute SQL commands inside EasyLib UI with controlled access.

Select Query

Runs read-only queries.

Typical Use Cases

1. Data verification
2. Debugging
3. Report validation

Safety

1. No data modification allowed
2. Ideal for audits

Update Query

Executes data modification queries.

Usage Scenarios

1. Data correction
2. Status updates
3. Cleanup operations

Admin Warning ??

1. Always test with Select first
2. Backup before execution
3. Track changes carefully

Create Table

Allows creation of new tables via SQL.

When to Use

1. Custom extensions
2. Temporary tables
3. Special integrations

SQL REPORTS

SQL Reports – Overview

Enables creation of custom reports using SQL queries.

Why SQL Reports Are Powerful

1. No code changes
2. Real-time data
3. Flexible filtering

SQL Report – Deep Explanation

What an SQL Report Contains

1. SQL query
2. Parameters
3. Output columns
4. Sorting and filtering rules

Use Cases

1. Management reports
2. Audit reports
3. Custom analytics