The Ultimate Full-Stack & Systems Reading Guide
Preparation for Exam & Lab Submission
This guide covers:
- Linux Installation & Dual Boot
- CLI & Permissions
- MySQL Server & InnoDB
- Full University Schema (Student, Course, Enrollment)
- Transactions (ACID)
- Indexing & EXPLAIN
- Backup & Recovery
- Secure Development (PDO & [Link])
- Advanced Features (JSON, Spatial, Fulltext)
- Submission Checklist
--------------------------------------------
PART 1: Linux Installation (Dual Boot)
Dual boot allows Windows and Linux on the same machine.
GRUB bootloader presents the OS selection menu.
Partitioning divides a disk into logical sections.
Installation Flow:
1. Shrink Windows partition (creates unallocated space).
2. Create bootable USB using Rufus and Linux ISO.
3. Boot via BIOS/UEFI.
4. Install Linux alongside Windows.
5. GRUB installed in EFI partition.
Manual Partitions:
/ (root) → ext4
swap → virtual RAM
/home → user files
After install:
sudo apt update
sudo apt upgrade
--------------------------------------------
PART 2: CLI Essentials
Navigation:
pwd, ls, cd
File Management:
touch, mkdir, rm -r, cp, mv
System Services:
systemctl status mysql
ss -tulnp | grep 3306
--------------------------------------------
PART 3: Permissions
Owner | Group | Others
r = 4, w = 2, x = 1
Examples:
chmod 760 [Link]
7 = rwx
6 = rw-
0 = ---
Ownership:
chown, chgrp (requires sudo)
--------------------------------------------
PART 4: MySQL & InnoDB
Verify installation:
SELECT VERSION();
InnoDB supports:
Transactions, Foreign Keys, Crash Recovery
Components:
Buffer Pool (memory cache)
Redo Log (durability)
Undo Log (rollback)
Doublewrite Buffer (corruption protection)
XAMPP vs Native Linux:
XAMPP → development
Linux → production-like
--------------------------------------------
PART 5: University Schema
CREATE DATABASE university;
USE university;
CREATE TABLE Student (
student_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Course (
course_id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
credits INT NOT NULL
);
CREATE TABLE Enrollment (
enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_id INT,
enrollment_date DATE,
FOREIGN KEY (student_id) REFERENCES Student(student_id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES Course(course_id) ON DELETE CASCADE
);
--------------------------------------------
PART 6: Transactions (ACID)
START TRANSACTION;
UPDATE Student SET first_name='John' WHERE student_id=1;
COMMIT;
ROLLBACK;
Atomicity, Consistency, Isolation, Durability
--------------------------------------------
PART 7: Indexing & EXPLAIN
CREATE INDEX idx_email ON Student(email);
EXPLAIN SELECT * FROM Student WHERE email='a@[Link]';
type = ALL → full scan
type = ref/const → index used
--------------------------------------------
PART 8: Backup & Recovery
mysqldump --single-transaction -u root -p university > [Link]
Check binary logs:
SHOW VARIABLES LIKE 'log_bin';
Binary logs enable point-in-time recovery.
--------------------------------------------
PART 9: Secure Development
Prepared statements prevent SQL injection.
PDO::ATTR_EMULATE_PREPARES = false
Connection pooling improves performance.
--------------------------------------------
PART 10: Advanced Features
JSON_EXTRACT()
Spatial: POINT(), ST_Distance_Sphere()
FULLTEXT index for search
--------------------------------------------
Submission Checklist:
- Proper ZIP naming
- Ethics note
- Provenance block
- SQL files
- Screenshots