University DB

University DB

University DB is an innovative and modern MariaDB 11.7+ sample database designed to offer a comprehensive learning experience in SQL, created as an alternative to the outdated Sakila database. This repository provides a rich dataset that encompasses various MariaDB data types—including VECTOR and JSON—and features a structure that is fully normalized to 3NF, making it suitable for both beginners and advanced users.

Key Features

• Robust Structure: Comprises 16 tables that facilitate lookup, domain, transactional data, and two extensive analytic tables.
• Comprehensive Data Types: Supports all major MariaDB types: INT, BIGINT, TINYINT, CHAR, VARCHAR, TEXT, MEDIUMTEXT, DECIMAL, DATE, DATETIME, TIMESTAMP, TIME, YEAR, ENUM, SET, BOOLEAN, JSON, and VECTOR(1536).
• Full-Text Search Capabilities: Enable robust text searches on courses and publications, enhancing querying capabilities.
• Advanced Query Support: Offers a 3-level department hierarchy (Faculty → Department → Sub-department) as well as complex analytical queries demonstrating the practical use of recursive Common Table Expressions (CTEs).
• Dynamic Data Generation: The database setup includes 4 levels of example queries guiding users from basic SELECT statements to advanced EXPLAIN ANALYZE commands and window functions.
• Docker Integration: Supports a Docker-first setup workflow providing an efficient way to initialize the database with a single command.

Comparison with Sakila

Feature	Sakila	University DB
Designed for	MySQL 5.x	MariaDB 11.7+
JSON support	No	Yes (5 columns)
VECTOR support	No	Yes VECTOR(1536)
FULLTEXT indexes	No	Yes (2 indexes)
Analytical big table	No	Yes (120,000 rows)
Recursive CTEs demo	No	Yes
Stored procedures	3	6
Triggers	6	7
Views	7	7

Schema Overview

The database includes essential tables such as departments, faculty, students, and grade_events, with notable features including:

• Department Structure: Self-referential relationships enable complex hierarchical data manipulation.
• Diverse Data Models: Incorporates JSON columns to manage complex data structures easily, enhancing the functionality and flexibility of data entries.

Database Objects

The University DB supports various database objects like views, stored procedures, and triggers that streamline data management and querying processes:

• Views: Enable easy access to complex queries, such as weighted GPAs or student faculty workloads.
• Stored Procedures: Validate and manage enrollments, compute GPAs, and maintain integrity across data entries.
• Triggers: Automatically enforce business rules during data modifications, ensuring data accuracy and integrity.

Learning Path

Users can progressively enhance their SQL skills by working through a structured learning path provided in the repository, from basic selections to more complex database operations:

• Level 1: Basics – Fundamental SQL commands.
• Level 2: Intermediate – Multi-table queries and advanced filtering techniques.
• Level 3: Advanced – Window functions and recursive queries.
• Level 4: DBA/Developer – Performance analysis and optimization.

In summary, University DB serves as an essential resource for anyone looking to master SQL with a focus on modern MariaDB features. The comprehensive structure, combined with extensive learning materials, enables users to engage with and understand complex database concepts effectively.