Relational Database: The Next Level Up

This article is the Next Step following Relational Database and SQL Fundamentals, exploring topics that developers and Database Administrators (DBAs) should understand, including database design with ER Diagrams, Primary/Foreign Keys and Referential Integrity, Normalization (1NF–3NF), Views, Indexes, Access Control, Backup & Recovery, and system administration best practices.

Every topic includes SQL code ready to copy and run in the SQL tab of phpMyAdmin or Adminer on CAMPP. The content uses the testdb database and patient/province tables from Part 1 as a foundation, extending with additional examples in each section. If you haven’t installed CAMPP yet, start from the “Before You Begin” section below.

Before You Begin: Install CAMPP and Open phpMyAdmin#

CAMPP is an environment for developing and testing web applications and databases locally (on your machine), free and easy to install. It bundles a web server, database system (MySQL/MariaDB), and database management tools in a single package.

Download and install CAMPP from https://campp.melivecode.com/ ↗

After installation, open CAMPP and click Start to begin the various services.

Then open phpMyAdmin through CAMPP’s web interface to manage databases via GUI, or click the SQL tab to type and run the SQL commands used in this article directly.

Tip: If you completed Part 1, the testdb database with province and patient tables will still exist, and you can continue following this article.

1. Database Design and ER Diagrams#

Good database design is the foundation of a fast, flexible system without redundant data. Popular tools for this phase include ER Diagrams (Entity-Relationship Diagrams), which visualize data structure before creating tables.

Entity, Attribute, and Relationship#

• Entity — Something we want to store data for, like Student, Course, Patient
• Attribute — Properties of an entity, like Student having student_id, fname, lname
• Relationship — Connections between entities, like Student “enrolls in” Course

One-to-One, One-to-Many, and Many-to-Many Relationships#

Important: Many-to-Many relationships cannot be stored directly in a relational database. You must create a junction table in between, like the enrollment table between student and course.

Creating ER Diagrams#

ER Diagrams use standard symbols: rectangles for entities, ovals for attributes, and diamonds for relationships. Modern design tools include draw.io ↗, dbdiagram.io ↗, and MySQL Workbench ↗.

The image above shows standard ER Diagram symbols with a legend explaining each mark. The image below is an example of an actual ER Diagram created with dbdiagram.io:

The ER Diagram above was created with dbdiagram.io ↗ using DBML language to define tables and relationships. Copy the code below and paste it into dbdiagram.io:

Table student {
  student_id int [pk, increment]
  fname varchar(100)
  lname varchar(100)
}

Table course {
  course_id int [pk, increment]
  title varchar(100)
  credits int
}

Table enrollment {
  enrollment_id int [pk, increment]
  student_id int
  course_id int
  enroll_date date
}

Ref: enrollment.student_id > student.student_id
Ref: enrollment.course_id > course.course_id

• [pk, increment] — Sets as Primary Key with auto-increment (equivalent to AUTO_INCREMENT in SQL)
• Lines Ref: ... > ... — Define Foreign Keys from enrollment to student and course (the > symbol means a many-to-one relationship)

Converting ER Diagrams to Database Tables#

When the ER Diagram is ready, convert it to tables immediately:

• Each entity → One table
• Each attribute → One column
• 1:N relationships → Store Foreign Key of the “1” side in the “N” side table
• M:N relationships → Create a junction table with FKs to both tables

Example: For a course enrollment system, we create a new database called schooldb and create three tables, converted to SQL as follows:

-- 1. Create schooldb database and select it
CREATE DATABASE IF NOT EXISTS schooldb;

-- Select schooldb database
-- (In phpMyAdmin, click schooldb from left panel instead of running USE)
USE schooldb;

-- 2. Entity: Student
CREATE TABLE student (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  fname VARCHAR(100),
  lname VARCHAR(100)
);

-- 3. Entity: Course
CREATE TABLE course (
  course_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  credits INT
);

-- 4. Junction table for M:N relationship (Student ↔ Course)
CREATE TABLE enrollment (
  enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT,
  course_id INT,
  enroll_date DATE,
  FOREIGN KEY (student_id) REFERENCES student(student_id),
  FOREIGN KEY (course_id) REFERENCES course(course_id)
);

After creating all tables, phpMyAdmin can automatically draw an ER Diagram from the tables and Foreign Keys we defined. Steps:

1. Select the schooldb database from the left panel
2. Click the Designer tab at the top
3. phpMyAdmin will display all tables with relationship lines (Foreign Keys) connecting them immediately

Do We Still Need to Draw ER Diagrams?#

In an era where we can use AI (like ChatGPT, Claude) to help design database structures, or even generate SQL CREATE TABLE statements in seconds, the question becomes: “Do we still need to draw ER Diagrams first?”

The answer is “Not always”, but ER Diagrams still have great value, depending on system size and complexity:

• Small systems or prototypes — Often don’t need formal ER Diagrams. Can describe requirements to AI and adjust structure gradually. Fewer tables and less complex relationships.
• Medium to large systems or team work — ER Diagrams still hold great value as a “common language” where everyone on the team and stakeholders can see the same structure and relationships. Also serves as a good map for AI to work in our intended direction.

In other words, AI helps us create structures faster, but doesn’t replace understanding of good design. ER Diagrams are like house blueprints — the larger and more complex the house, the more essential a plan is before construction.

Summary: ER Diagrams help visualize data before creating tables, enabling correct design from the start. Entities become tables, attributes become columns, and relationships become Foreign Keys or junction tables.

2. Primary Keys and Foreign Keys#

Primary Keys and Foreign Keys are critical mechanisms that connect tables correctly and reliably, especially in relational databases where data is often separated into multiple tables.

In this section, we’ll use the schooldb database created in section 1, which consists of 3 main tables:

• student stores student information
• course stores course information
• enrollment stores student enrollment information for each course

The relationship between student and course is Many-to-Many because one student can enroll in multiple courses, and one course can have multiple students. Therefore, a junction table named enrollment is required.

Primary Key Concept#

Primary Key (PK) is a column or set of columns that uniquely identifies each record and cannot be NULL. A table can have only one Primary Key.

For example, the student table uses student_id as Primary Key to identify each student:

CREATE TABLE student (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  fname VARCHAR(100),
  lname VARCHAR(100)
);

The course table uses course_id as Primary Key to identify each course:

CREATE TABLE course (
  course_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  credits INT
);

The enrollment table uses enrollment_id as Primary Key (full structure with Foreign Keys shown in next subsection).

Foreign Key Concept#

Foreign Key (FK) is a column that references the Primary Key of another table to create relationships between tables.

In this example, the enrollment table has two Foreign Keys:

• student_id references student(student_id)
• course_id references course(course_id)

CREATE TABLE enrollment (
  enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT,
  course_id INT,
  enroll_date DATE,
  FOREIGN KEY (student_id) REFERENCES student(student_id),
  FOREIGN KEY (course_id) REFERENCES course(course_id)
);

This means each enrollment record must reference an actual student in the student table and an actual course in the course table.

Referential Integrity#

Referential Integrity is the rule guaranteeing that every Foreign Key value must reference existing data in the parent table.

For example, if trying to add data to the enrollment table specifying student_id = 999, but there’s no student with student_id = 999 in the student table, the system will reject that command.

Similarly, if specifying course_id = 999, but that course doesn’t exist in the course table, the data cannot be added.

Preparing the Database for Experiments#

Before starting examples in this section, it’s recommended to delete the existing schooldb database first, so everyone starts from the same database state.

Dropping the Old Database in phpMyAdmin#

1. Click the schooldb database from the left bar
2. Select the Operations tab
3. Click Drop the database (DROP)
4. Press OK to confirm deletion

⚠️ Warning: Dropping a database permanently deletes all tables and data within that database.

Creating New Database and Tables#

Run the following SQL in the SQL tab of phpMyAdmin or your preferred database management tool:

-- 1. Create schooldb database and select it
CREATE DATABASE IF NOT EXISTS schooldb;

-- Select schooldb database
USE schooldb;

-- 2. Entity: Student
CREATE TABLE student (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  fname VARCHAR(100),
  lname VARCHAR(100)
);

-- 3. Entity: Course
CREATE TABLE course (
  course_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  credits INT
);

-- 4. Junction table for M:N relationship between Student and Course
CREATE TABLE enrollment (
  enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT,
  course_id INT,
  enroll_date DATE,
  FOREIGN KEY (student_id) REFERENCES student(student_id),
  FOREIGN KEY (course_id) REFERENCES course(course_id)
);

Adding Sample Data#

After creating tables, add sample data to student and course tables first, because enrollment needs to reference data from these two tables:

-- Add student data
INSERT INTO student (fname, lname) VALUES
('Somchai', 'Jaidee'),
('Suda', 'Sukjai'),
('Anan', 'Meesuk'),
('Malee', 'Thongdee'),
('Krit', 'Wongsa');

-- Add course data
INSERT INTO course (title, credits) VALUES
('Database Systems', 3),
('Web Programming', 3),
('Artificial Intelligence', 3),
('Cybersecurity Basics', 2);

-- Add enrollment data
INSERT INTO enrollment (student_id, course_id, enroll_date) VALUES
(1, 1, '2026-06-01'),
(1, 2, '2026-06-01'),
(2, 1, '2026-06-02'),
(2, 3, '2026-06-02'),
(3, 2, '2026-06-03'),
(4, 3, '2026-06-03'),
(5, 4, '2026-06-04');

Defining Relationships Between Tables#

The student and course tables have a Many-to-Many relationship:

• One student can enroll in multiple courses
• One course can have multiple students enrolled

Therefore, the enrollment table serves as a Junction Table to store the relationship between students and courses.

The relationship structure can be described as follows:

Specifically:

• student.student_id connects to enrollment.student_id
• course.course_id connects to enrollment.course_id

SQL Example to View Connected Data#

After adding data, use JOIN to see which courses each student enrolled in:

SELECT
  student.student_id,
  student.fname,
  student.lname,
  course.title,
  course.credits,
  enrollment.enroll_date
FROM enrollment
JOIN student ON enrollment.student_id = student.student_id
JOIN course ON enrollment.course_id = course.course_id;

The result will be combined data from all 3 tables:

Key Constraint Example#

Try running the following commands in the SQL tab of phpMyAdmin to see how Primary Keys and Foreign Keys work:

-- Correct: Add enrollment for existing student_id and course_id
INSERT INTO enrollment (student_id, course_id, enroll_date)
VALUES (1, 3, '2026-06-05');

-- Wrong: Add enrollment for non-existent student_id
-- Will be rejected because there's no student_id = 999 in student table
INSERT INTO enrollment (student_id, course_id, enroll_date)
VALUES (999, 1, '2026-06-05');

-- Wrong: Add enrollment for non-existent course_id
-- Will be rejected because there's no course_id = 999 in course table
INSERT INTO enrollment (student_id, course_id, enroll_date)
VALUES (1, 999, '2026-06-05');

Example of Deleting Referenced Data#

If a student is being referenced in the enrollment table and you try to delete that student, the system will reject it because it could make enrollment data inconsistent:

-- Wrong: Delete a student who has enrollment data
-- Will be rejected because student_id = 1 is being referenced
DELETE FROM student
WHERE student_id = 1;

If you really want to delete, you must delete the related enrollment data first, then delete the student:

-- Delete enrollment data for student_id = 1 first
DELETE FROM enrollment
WHERE student_id = 1;

-- Then delete the student
DELETE FROM student
WHERE student_id = 1;

ON DELETE and ON UPDATE#

We can control behavior when referenced data is deleted or modified using ON DELETE and ON UPDATE:

FOREIGN KEY (student_id) REFERENCES student(student_id)
  ON DELETE RESTRICT
  ON UPDATE CASCADE

Generally, for enrollment data, you might choose RESTRICT to prevent accidental deletion of students or courses while enrollment relationships exist.

Summary: Primary Key uniquely identifies records (like student_id), Foreign Key connects tables together. The enrollment table makes Many-to-Many relationships possible, and Referential Integrity prevents referencing non-existent data.

3. Normalization (1NF–3NF)#

Normalization is the process of organizing tables to reduce data redundancy and eliminate data anomalies. In this section, we’ll use the school system example (schooldb) from section 2 — starting from a single table “not yet normalized” and gradually separating it into the familiar student, course, and enrollment tables.

Starting Point: Single Table Storing Everything#

Suppose we initially designed a single table named student_course that stores students, courses, and enrollments together, putting multiple courses for one student in a single column:

Storing multiple values in one cell makes searching, updating, or joining difficult — this is what Normalization fixes.

Redundancy and Anomalies#

Poorly designed tables create redundancy and 3 types of anomalies:

• Update Anomaly — Changing a course name requires modifying multiple rows. If not all rows are updated, data becomes inconsistent.
• Insert Anomaly — Cannot add a new course that nobody has enrolled in yet (no students yet → no rows to insert into).
• Delete Anomaly — Deleting the last enrollment for a course removes the course data as well.

First Normal Form (1NF)#

1NF requires every column to store atomic values — not multiple values in one cell. The solution is to separate each course into its own row and extract course details as columns:

Now it passes 1NF, but still has obvious redundancy — “Somchai/Jaidee” repeats in 2 rows, and “Database Systems/3cr” also repeats in 2 rows.

Second Normal Form (2NF)#

2NF requires passing 1NF and no partial dependencies — every column must depend on the entire PK, not just part of it.

In this table, PK is (student_id, course_id), but:

• fname, lname depend on only student_id (part of PK)
• title, credits depend on only course_id (part of PK)
• enroll_date depends on both (it’s specific to that enrollment)

The solution is to separate into 3 tables:

• student — student_id → fname, lname
• course — course_id → title, credits
• enrollment — student_id, course_id → enroll_date

Third Normal Form (3NF)#

3NF requires passing 2NF and no transitive dependencies — non-key columns must not depend on other non-key columns.

Example of transitive dependency: To illustrate, suppose our student table also stores advisor (faculty mentor) information in the same table:

PK is student_id, but advisor_name and advisor_dept don’t depend directly on student_id — they depend on advisor_id:

student_id → advisor_id → advisor_name, advisor_dept

This is a transitive dependency, which causes the same problems: if “Dr. Smith” transfers departments, you must modify multiple rows. If an advisor changes names, you must update it in every row of their assigned students.

Solution (3NF): Separate advisor data into its own advisor table, and have student reference it with advisor_id:

CREATE TABLE advisor (
  advisor_id INT PRIMARY KEY,
  advisor_name VARCHAR(100),
  advisor_dept VARCHAR(100)
);

CREATE TABLE student (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  fname VARCHAR(100),
  lname VARCHAR(100),
  advisor_id INT,
  FOREIGN KEY (advisor_id) REFERENCES advisor(advisor_id)
);

Now every column in each table depends directly on PK with no transitive dependency — passes 3NF.

Back to our example: after separating in 2NF, all three tables student, course, enrollment also pass 3NF (no transitive dependencies), and the result is the same structure we created in section 2:

-- student: Student data, all depends on student_id
CREATE TABLE student (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  fname VARCHAR(100),
  lname VARCHAR(100)
);

-- course: Course data, all depends on course_id
CREATE TABLE course (
  course_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  credits INT
);

-- enrollment: Stores M:N relationship + enrollment details
CREATE TABLE enrollment (
  enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT,
  course_id INT,
  enroll_date DATE,
  FOREIGN KEY (student_id) REFERENCES student(student_id),
  FOREIGN KEY (course_id) REFERENCES course(course_id)
);

Note: Theoretically, PK of enrollment is (student_id, course_id), but in practice we often use a surrogate key (enrollment_id with AUTO_INCREMENT) to make referencing rows easier, as done in section 2.

Benefits of Normalized Databases#

• Reduces redundancy → Saves space
• Eliminates anomalies → Data is consistent and correct
• Single point of change → Reflects everywhere (e.g., change course name in one place in course)
• Easier maintenance and system expansion

Summary: Normalization transforms a single table containing everything into multiple tables where “each table stores only one thing” through 1NF → 2NF → 3NF — resulting in the student/course/enrollment structure we use in section 2.

What’s Denormalization? Sometimes in production, we might intentionally store some redundant data (denormalize), like storing course names in enrollment too, to reduce multiple table JOINs when reading. This makes queries faster but trades off with maintaining consistency of duplicated data.

4. Views#

Introduction to Views#

View is a virtual table created from query results. It doesn’t store actual data (except materialized views) but only stores the query definition. In this section, we’ll use the student/course/enrollment tables from section 2.

Creating and Managing Views#

Remember that long JOIN query connecting 3 tables in section 2? We can wrap it in a view for reuse:

-- Create a view combining students with their enrolled courses
CREATE VIEW student_courses AS
SELECT
  s.student_id,
  s.fname,
  s.lname,
  c.title AS course_title,
  c.credits,
  e.enroll_date
FROM enrollment e
JOIN student s ON e.student_id = s.student_id
JOIN course c ON e.course_id = c.course_id;

-- Use like a normal table — shorter than writing JOIN every time
SELECT * FROM student_courses;

-- Filter only students enrolled in Database Systems
SELECT * FROM student_courses WHERE course_title = 'Database Systems';

-- Drop view
DROP VIEW student_courses;

The result of SELECT * FROM student_courses; will be the same as the JOIN query in section 2, because a view is essentially “saving a query” and calling it through a short name. In phpMyAdmin, you’ll see the student_courses view in the left panel (with a virtual table icon) and can click to view data like any regular table.

Reducing Query Complexity#

Instead of writing long JOIN queries repeatedly, we wrap them in a view and call SELECT * FROM student_courses — whether in applications, reports, or data analysis tools.

Data Abstraction#

Views hide the complexity of actual table structure. Users don’t need to know how many tables data is distributed across. Suitable for creating “interfaces” for users or applications.

Using Views for Security#

You can grant users access to only specific views instead of actual tables, hiding sensitive columns. For example, create a view summarizing student counts per course without revealing student identities, then let external users access only that view instead of the student table.

Summary: Views reduce query complexity, provide abstraction, and increase security by controlling what data users can see.

5. Indexes#

Why Indexes Are Important#

Index is like a book’s index, helping databases find records much faster without reading the entire table. In this section, we’ll use the student/course/enrollment tables from section 2.

Full Table Scan vs Indexed Search#

• Full Table Scan — Reads every row, slow with lots of data
• Indexed Search — Uses index to jump directly to target rows, much faster

Use the EXPLAIN command to see whether a query uses an index or does a full table scan:

EXPLAIN SELECT * FROM student WHERE lname = 'Jaidee';

Result (before creating index):

The key points are type = ALL and key = NULL — meaning MySQL doesn’t have an index to use yet, so it must read every row (Full Table Scan) and filter with WHERE (seen in Extra: Using where). With only 5 rows, it doesn’t feel slow, but if the table has 100,000 rows, reading every row every time would be very slow.

After creating an index (in the next subsection) and running EXPLAIN again, you’ll see type change from ALL to ref and key become the index name — meaning it switched to Indexed Search.

Single-Column Index#

-- Create index on lname column of student table (search students by last name)
CREATE INDEX idx_student_lname ON student(lname);

After creating the index, run EXPLAIN again for the new result:

Comparing before/after creating index:

Now MySQL uses the index to search (key: idx_student_lname) and jumps directly to the matching row (rows: 1) instead of reading all rows — this is Indexed Search.

Composite Index#

Index on multiple columns together, suitable for queries using those columns together:

-- Index on student_id and enroll_date of enrollment table
CREATE INDEX idx_enrollment_student_date ON enrollment(student_id, enroll_date);

Order matters: Composite index (student_id, enroll_date) works well with WHERE student_id = ... and WHERE student_id = ... AND enroll_date > ..., but doesn’t help with WHERE enroll_date = ... alone.

Index Benefits and Trade-offs#

• Benefits — WHERE, JOIN, ORDER BY queries become much faster
• Drawbacks — Slightly slows down INSERT/UPDATE/DELETE (must update index too) and uses additional storage space

Summary: Create indexes on columns used frequently for search/join/sort, but don’t create too many because they impact write performance.

6. Stored Procedures#

Introduction to Stored Procedures#

Stored Procedure is a set of SQL commands collected together and stored in the database. It can be called repeatedly without rewriting the same SQL, making application code more compact, reducing data transfer between application and database, and increasing security by hiding business logic complexity.

Benefits of Stored Procedures#

• Reduces data transfer between app and database — Instead of sending multiple lines of SQL, the app just calls the Stored Procedure name
• Better security — Users can call Stored Procedures without direct table access permissions, and can hide complex business logic
• Easier maintenance — Modify business logic directly in the database without changing application code and redeploying
• Better performance — Database compiles and caches execution plan, making it faster than sending SQL one command at a time
• Reduces application complexity — Application just calls CALL procedure_name(...) without writing long queries

Creating Stored Procedures#

In MySQL/MariaDB, creating Stored Procedures requires temporarily changing the delimiter so the database understands where the CREATE PROCEDURE command ends, because Stored Procedures contain semicolons (;).

Example of creating a Stored Procedure named AddStudent that accepts fname and lname parameters and adds a new student:

DELIMITER $$

CREATE PROCEDURE AddStudent(
    IN p_fname VARCHAR(100),
    IN p_lname VARCHAR(100)
)
BEGIN
    INSERT INTO student (fname, lname)
    VALUES (p_fname, p_lname);
END $$

DELIMITER ;

Explaining each part:

• DELIMITER $$ — Temporarily change command delimiter from ; to $$ to allow writing multi-line SQL inside Stored Procedure
• CREATE PROCEDURE AddStudent(...) — Create Stored Procedure named AddStudent accepting 2 parameters
• IN p_fname VARCHAR(100) — Input parameter named p_fname of type VARCHAR(100)
• BEGIN ... END — Block containing all SQL commands of the Stored Procedure
• INSERT INTO student ... — SQL command that adds new student data
• END $$ — End Stored Procedure block (using $$ instead of ;)
• DELIMITER ; — Change delimiter back to ; as before

After creating the Stored Procedure, you can view all Stored Procedures in phpMyAdmin by:

1. Select the schooldb database from the left panel
2. Click the Procedures menu at the top
3. phpMyAdmin will display all Stored Procedures created in the schooldb database

Calling Stored Procedures#

After creation, call the Stored Procedure with the CALL command:

-- Call Stored Procedure to add a new student
CALL AddStudent('Nopphadol', 'Sriwilai');

-- Check results
SELECT * FROM student;

Result will show the newly added student:

More Complex Stored Procedures#

Stored Procedures can have multiple commands, use variables, and include conditions or loops. For example, a Stored Procedure that adds enrollment while verifying that the student and course exist:

DELIMITER $$

CREATE PROCEDURE EnrollStudent(
    IN p_student_id INT,
    IN p_course_id INT
)
BEGIN
    -- Variables to store counts
    DECLARE student_count INT;
    DECLARE course_count INT;

    -- Check if student exists
    SELECT COUNT(*) INTO student_count
    FROM student
    WHERE student_id = p_student_id;

    -- Check if course exists
    SELECT COUNT(*) INTO course_count
    FROM course
    WHERE course_id = p_course_id;

    -- If both student and course exist, add enrollment
    IF student_count > 0 AND course_count > 0 THEN
        INSERT INTO enrollment (student_id, course_id, enroll_date)
        VALUES (p_student_id, p_course_id, CURDATE());
        SELECT 'Enrollment successful' AS result;
    ELSE
        SELECT 'Student or course not found' AS result;
    END IF;

END $$

DELIMITER ;

Calling this Stored Procedure:

-- Enroll student ID 1 in course ID 3 (Artificial Intelligence)
CALL EnrollStudent(1, 3);

-- Check results
SELECT * FROM enrollment;

Managing and Deleting Stored Procedures#

-- View all Stored Procedures in database
SHOW PROCEDURE STATUS WHERE Db = 'schooldb';

-- View code of Stored Procedure named AddStudent
SHOW CREATE PROCEDURE AddStudent;

-- Delete Stored Procedure
DROP PROCEDURE IF EXISTS AddStudent;

Stored Procedures with Multiple Parameter Types#

Stored Procedures can have input (IN), output (OUT), and input/output (INOUT) parameters. Example of a Stored Procedure that calculates a count and returns it via an output parameter:

DELIMITER $$

CREATE PROCEDURE GetStudentCount(
    OUT p_total INT
)
BEGIN
    SELECT COUNT(*) INTO p_total
    FROM student;
END $$

DELIMITER ;

-- Call with output parameter
CALL GetStudentCount(@total);
SELECT @total AS total_students;

Stored Procedure for UPDATE#

Example of a Stored Procedure that modifies data:

DELIMITER $$

CREATE PROCEDURE UpdateStudentName(
    IN p_student_id INT,
    IN p_new_fname VARCHAR(100),
    IN p_new_lname VARCHAR(100)
)
BEGIN
    UPDATE student
    SET fname = p_new_fname,
        lname = p_new_lname
    WHERE student_id = p_student_id;

    SELECT ROW_COUNT() AS affected_rows;
END $$

DELIMITER ;

-- Call it
CALL UpdateStudentName(6, 'Nopphadol', 'Update');

Summary: Stored Procedures are sets of SQL commands stored in the database, reducing application complexity, improving performance, and increasing security by hiding business logic and controlling data access. They can be called repeatedly and maintained more easily than embedding SQL in application code.

7. Access Control and Security#

Users and Roles in Databases#

DBMS separates each user with their own account and often groups them into roles to grant permissions by group rather than individually. In our schooldb system, we might have users like teacher and staff with different roles.

-- Create user
CREATE USER 'teacher'@'localhost' IDENTIFIED BY 'secure_password';

Authentication and Authorization#

• Authentication — Confirming who the user is (e.g., username + password)
• Authorization — Determining what that user can do

Privileges and Permissions#

Grant permissions with GRANT and revoke with REVOKE:

-- Grant staff read-only access to student and course tables
GRANT SELECT ON schooldb.student TO 'staff'@'localhost';
GRANT SELECT ON schooldb.course TO 'staff'@'localhost';

-- Grant teacher read and modify access to enrollment table
GRANT SELECT, UPDATE ON schooldb.enrollment TO 'teacher'@'localhost';

-- Revoke permission
REVOKE UPDATE ON schooldb.enrollment FROM 'teacher'@'localhost';

-- View user's permissions
SHOW GRANTS FOR 'teacher'@'localhost';

Available permissions include SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, and grant levels (*.* for all databases, schooldb.enrollment for specific tables).

Principle of Least Privilege#

Principle of Least Privilege — Give users only the permissions necessary for their work, no more. For example, staff who can only view student data shouldn’t have DELETE or UPDATE permissions.

Managing Sensitive Data#

Manage sensitive data (like student personal information) with multiple layers: table/column-level permissions, views that filter data (see section 4), encryption, and access logging (auditing).

Access Control#

DBMS manages permissions and access levels for each user, allowing different users different access levels to the same schooldb database. For example, teacher can modify enrollment data, while staff can only “view” student and course data, helping protect data from unauthorized changes.

Summary: Access Control determines who can access what, using user/role + GRANT/REVOKE + Principle of Least Privilege to keep data secure and users working within their roles.

8. Backup & Recovery#

Importance of Data Backup#

Data is valuable and fragile — hardware failure, software bugs, accidental deletion, or attacks can all cause data loss. Data backup is the last line of defense.

Backup Strategy#

Plan backup strategy appropriate to data importance: how often to back up, how many copies to keep, where to store (on-site + off-site/cloud), and periodically test recovery.

Full Backup and Incremental Backup#

• Full Backup — Back up everything at once. Simple but slow and uses more space.
• Incremental Backup — Back up only “changes” since last backup. Faster and space-efficient, but recovery is more complex.

Database Recovery Techniques#

Export/Import via phpMyAdmin is easy: Click Export tab to save database as .sql file (backup), and click Import tab to bring .sql file back in (recovery).

Or use mysqldump command in command line. mysql/mysqldump are in the runtime folder of CAMPP. Steps:

1. Open Command Prompt (Windows) or Terminal (macOS/Linux)
2. cd into the bin folder containing mysql/mysqldump (under runtime by installed version), e.g., on Windows:

cd \
cd CAMPP\runtime\mysql-8.4.0-winx64\bin

Version folder name (like mysql-8.4.0-winx64) may differ based on installed version — open C:\CAMPP\runtime and enter the bin folder of MySQL. On macOS/Linux it’s similar: cd /Applications/CAMPP/runtime/mysql-<version>/bin

3. Run backup/recovery commands from this folder (backing up schooldb database we created in section 2):

# Backup data (full backup) to .sql file (store at C:\CAMPP)
mysqldump -u root -p -P 3307 schooldb > C:\CAMPP\schooldb_backup.sql

# Delete old database first, then create new one
mysql -u root -p -P 3307 -e "DROP DATABASE IF EXISTS schooldb;"
mysql -u root -p -P 3307 -e "CREATE DATABASE schooldb;"

# Recovery — import data back
mysql -u root -p -P 3307 schooldb < C:\CAMPP\schooldb_backup.sql

Meaning of each flag:

• -u root — Login as user root
• -p — System will prompt for password (typed without display on screen, more secure than putting password in command)
• -P 3307 — MySQL port (P is uppercase)
• > / < — File redirection: > writes to file (backup), < reads file for input (recovery)
• -e "..." — Run SQL command directly from command line (e.g., when dropping/creating database)

Disaster Recovery Planning#

Disaster Recovery Plan covers how fast to recover (RTO) and how much data loss is acceptable (RPO), keeping multiple backup copies in multiple locations, replication, and regular recovery drills.

Backup and Recovery#

DBMS has backup and recovery mechanisms. Regular data backup prevents permanent loss from hardware failure, accidental deletion, or system errors. Recovery processes restore databases from backup data, ensuring high availability and business continuity.

Summary: Backup & Recovery protects data from all situations. Plan your strategy (full/incremental), test recovery, and always have a disaster recovery plan.

9. Database Administration Best Practices#

Monitoring Database Health#

Monitor status with commands like SHOW STATUS, SHOW PROCESSLIST, and monitoring tools to check resource usage, slow queries, and locks.

Security Best Practices#

• Use Principle of Least Privilege (see section 8)
• Change passwords periodically and keep them secret
• Enable encryption for connections (TLS) and sensitive data
• Update DBMS to fix security vulnerabilities

Backup Scheduling#

Schedule automatic backups (e.g., cron job running mysqldump every night) and keep multiple copies in multiple locations per your strategy (see section 8).

Performance Optimization#

• Create indexes on columns used frequently for search/join/sort (see section 7)
• Analyze slow queries with EXPLAIN and adjust to use indexes
• Limit rows with LIMIT and select only necessary columns (avoid SELECT * when not needed)
• Use views or denormalize slightly when it helps read performance

Maintenance and Troubleshooting#

• Run OPTIMIZE TABLE / organize data periodically
• Update index statistics so query optimizer works well
• Keep logs and test recovery to stay prepared for problems

Summary: Good database administration involves monitoring system health, maintaining security, regular backups, performance optimization, and consistent maintenance to keep systems fast, secure, and reliable.

Summary#

This article is the Next Step exploring advanced database topics:

• Database Design and ER Diagrams — Design data structure before creating tables
• Primary Key & Foreign Key — Identity and relationships with referential integrity
• Normalization (1NF–3NF) — Reduce redundancy and eliminate anomalies
• Views — Virtual tables reducing complexity and increasing security
• Indexes — Accelerate searches with trade-offs
• Access Control & Security — Control permissions by Principle of Least Privilege
• Backup & Recovery — Protect data and recover when incidents occur
• Database Administration — Best practices for fast, secure, reliable systems

Understanding both fundamentals and these advanced topics enables you to design and maintain robust databases supporting real work from small web applications to enterprise systems with confidence.