Freaks City

Relational Schema Design When tracking academic performance, a marks table typically requires references to both learners and courses rather than storing redundant textual data. CREATE TABLE marks ( mark_id INT PRIMARY KEY AUTO_INCREMENT, pupil_ref INT, course_ref INT, mark_value DECIMAL(5,2) ); The pupil_ref and course_ref col ...

Multi-Table Relationships When designing relational schemas for applications, tables often relate due to business logic. Common relationship types include: One-to-Many (or Many-to-One) Many-to-Many One-to-One One-to-Many Example: Department–Employee association. A department can have multiple employees; each employee belongs to one departmen ...

Pagination and SortingTo retrieve a specific range of records, such as skipping the first 20 entries and fetching the subsequent 10, the LIMIT clause with an offset is utilized:SELECT * FROM articles LIMIT 20, 10;Alternatively, the explicit offset syntax can be used:SELECT * FROM articles LIMIT 10 OFFSET 20;Filtering and ordering results descen ...

Constraints are rules applied to table columns to restrict the data that can be stored. Their purpose is to ensure data correctness, validity, and integrity within the database. Categories of Constraints NOT NULL: Ensures a column cannot have a NULL value. UNIQUE: Guarantees that all values in a column are different. PRIMARY KEY: Uniquely iden ...

Database Concepts and Initialization A database is a repository for structured data storage and management. Service Management Commands net start mysql net stop mysql mysqladmin -u root password new_password mysql -u root -p SQL Statement Categories Type Full Name Purpose DDL Data Definition Language Defines database objects DML Data ...

Creating Database Tables and Relationshpis -- Create class table and insert data CREATE TABLE classes ( class_id INT AUTO_INCREMENT PRIMARY KEY, class_name VARCHAR(32) NOT NULL DEFAULT "" ) CHARSET utf8; INSERT INTO classes (class_name) VALUES ("Grade 3 Class 2"), ("Grade 1 Class 3"), ("Grade 3 Class ...

Creating Tables To build a table, use the standard CREATE TABLE syntax. Separate column definitions with commas, and designate a primary key to uniquely identify each row. CREATE TABLE table_name( column_name data_type, column_name data_type, column_name data_type, column_name data_type, column_name data_type ) ENGINE = stor ...

Foreign Keys Why Foreign Keys? Before foreign keys, merging every thing into one table caused issues: Unclear focus: Hard to separate employee vs. department data. Redundant storage: Same fields repeated across rows. Poor scalability: Changing one part affected the whole table. Solution: Split into multiple tables (e.g., emp and dep) and use ...

Data Types in MySQL MySQL supports various data types organized into several categories: Category Examples Integer Types TINYINT, SMALINT, MEDIUMINT, INT, BIGINT Floating Point FLOAT, DOUBLE Decimal Numbers DECIMAL Bit Types BIT Date/Time Types YEAR, TIME, DATE, DATETIME, TIMESTAMP String Types CHAR, VARCHAR, TINYTEXT, TEXT, ME ...

A self-join operates by treating a single table as two distinct entitise through table aliasing. This technique proves essential when modeling hierarchical relationships—such as organizational reporting structures—or when comparing records within the same dataset to identify duplicates or related pairs. The fundamental pattern requires assignin ...