Chapter 9 – DBMS Development Using DBMS Tools

Learning Objectives

  • After studying this chapter, you should be able to:
    • Explain the requirements for DBMS tools.
    • Navigate and use phpMyAdmin for database administration.
    • Perform and understand CRUD (Create, Read, Update, Delete) operations through MySQLi + PHP.

DBMS Tools Requirement

  • A complete local‐development stack must include:
    • Web Server (implements HTTP protocol).
    • MySQL Database (open-source RDBMS).
    • PHP (server-side scripting language).
    • Apache (typical open-source HTTP server engine; other choices possible).
    • Web Browser (client-side interface for interaction/testing).
    • IDE / Code Editor (to write PHP & SQL).

Web Server

  • Defined as software that accepts HTTP requests, parses server-side scripts, and returns content (HTML, JSON, images, API payloads, etc.).
  • Must fully implement the HTTP/1.1 or HTTP/2 specification to be classed as a web server.
  • Popular engines:
    • Apache HTTP Server – open source, modular, cross-platform.
    • Microsoft IIS – proprietary, Windows-centric.
    • Apache Tomcat – focused on Java Servlets/JSP but can also serve static content.
  • Practical considerations:
    • Runs on port 8080 (HTTP) and/or 443443 (HTTPS).
    • Works together with PHP via mod_php, FastCGI, or PHP-FPM modules.

MySQL Database

  • MySQL is a relational DBMS whose source is dual-licensed (GNU/GPL or commercial).
  • Known as the world’s most popular open-source database.
  • Favoured for "LAMP" stacks (Linux-Apache-MySQL-PHP).
  • phpMyAdmin front-end makes administration intuitive and GUI-oriented.

phpMyAdmin Overview

  • Browser-based PHP application shipped with most AMP packages (e.g.
    XAMPP, WAMP, MAMP).
  • Default credentials on localhost:
    • Username: root
    • Password: (blank)
  • Main tabs / actions:
    • Databases – list / create / drop databases.
    • SQL – run ad-hoc queries.
    • Structure – manage tables, indexes, relations.
    • Insert / Browse – CRUD operations on rows.

Creating a New Database

  • Steps:
    1. Click the Databases tab.
    2. Enter a database name.
    3. Press Create → empty schema appears in sidebar.

Creating a Table

  • An empty DB cannot hold data; at least one table is required.
  • Dialog asks for table name and number of fields.
  • Design recommendations:
    • Reserve first column for a surrogate key (e.g. id).
    • Data type: BIGINT.
    • Enable AI (AUTOINCREMENT) so each row gets a unique value automatically.
    • Set INDEX to PRIMARY.

Configuring Data Types

  • MySQL groups data types into numeric, string, date/time, spatial, JSON.
  • Choosing correct types controls storage size, valid data domain, and retrieval speed.
  • Common string types:
    • CHAR(n) – fixed length; optimal for uniform values (e.g. country codes).
    • VARCHAR(n) – variable length up to nn; ideal when length varies (e.g. names).
  • Performance tips:
    • Avoid over-allocating VARCHAR (wastes memory in buffers).
    • Index only columns that will truly be searched or joined.

Numeric Types Cheat-Sheet

TypeSigned RangeUnsigned Range
TINYINT−128≤x≤127-128 \le x \le 1270≤x≤2550 \le x \le 255
SMALLINT−32768≤x≤32767-32768 \le x \le 327670≤x≤655350 \le x \le 65535
MEDIUMINT−8388608≤x≤8388607-8388608 \le x \le 83886070≤x≤167772150 \le x \le 16777215
INT−2147483648≤x≤2147483647-2147483648 \le x \le 21474836470≤x≤42949672950 \le x \le 4294967295
BIGINT−9.22×1018≤x≤9.22×1018-9.22\times10^{18} \le x \le 9.22\times10^{18}0≤x≤1.84×10190 \le x \le 1.84\times10^{19}
FLOATsmall floating-point
DOUBLElarger floating-point

Date/Time Types Cheat-Sheet

TypeFormat
DATEYYYY-MM-DDYYYY\text{-}MM\text{-}DD
DATETIMEYYYY-MM-DD HH:MM:SSYYYY\text{-}MM\text{-}DD\space HH:MM:SS
TIMESTAMPYYYYMMDDHHMMSSYYYYMMDDHHMMSS (numeric)
TIMEHH:MM:SSHH:MM:SS

Connecting to MySQL with MySQLi (Procedural Style)

  • Required connection details:
    • Server/Host name – usually localhost for local dev.
    • Database name – schema you created (e.g. phptest).
    • Username / Password – root / (blank) by default.
<?php
$host = "localhost";  // Server name
$user = "root";       // Username
$pwd  = "rahimi!!";   // Password (example)
$db   = "testing";    // Database name

// Open connection
$con = mysqli_connect($host, $user, $pwd, $db)
       or die(mysqli_connect_errno());
?>
  • $con is reused for all subsequent queries.

CRUD Operations via MySQLi

C – Create / Insert

  • Direct value embedding:
mysqli_query($con, "INSERT INTO tbl_pengguna
  (name, age, faculty, course)
  VALUES ('Mohamad Rahimi Mohamad Rosman','27','IM245','IMS607')");
  • Using local variables (clearer & safer when combined with prepared statements):
$name    = "Mohamad Rahimi Mohamad Rosman";
$age     = 27;
$faculty = "IM245";
$course  = "IMS607";
mysqli_query($con, "INSERT INTO tbl_pengguna (name, age, faculty, course)
               VALUES ('$name', '$age', '$faculty', '$course')");
  • On success: echo confirmation and always close the connection with mysqli_close($con);.

R – Read / Query

Display a Single Record
$search = mysqli_query($con, "SELECT * FROM tbl_pengguna")
          or die(mysqli_error($con));
$data   = mysqli_fetch_array($search);
echo "Name: $data[name]<br>";
echo "Age:  $data[age]<br>";
echo "Faculty/Course: $data[faculty] / $data[course]";
Display Multiple Records
$search = mysqli_query($con, "SELECT * FROM tbl_pengguna");
$total  = mysqli_num_rows($search);
echo "$total records found<br>";
while($row = mysqli_fetch_array($search)) {
  echo "Name: $row[name]<br>";
  echo "Age:  $row[age]<br>";
  echo "Faculty/Course: $row[faculty] / $row[course]<hr>";
}

U – Update

Update a Single Row (ID = 1)
mysqli_query($con, "UPDATE tbl_pengguna SET age = 25 WHERE id = 1");
Update Multiple Rows (all rows)
mysqli_query($con, "UPDATE tbl_pengguna SET faculty = 'IM221'");
  • Essential to use a WHERE clause to avoid unintentional mass updates.

D – Delete

  • Syntax parallels UPDATE but uses DELETE:
mysqli_query($con, "DELETE FROM tbl_pengguna WHERE id = 2");
  • Without a WHERE clause, whole table will be purged.

Best Practices & Performance Considerations

  • Use prepared statements (mysqli_prepare, PDO) to prevent SQL injection.
  • Normalise database schema (1NF/2NF/3NF) to reduce redundancy.
  • Index columns used in WHERE, JOIN, ORDER BY, but avoid over-indexing.
  • Use EXPLAIN to profile query plans; watch for full table scans.
  • Keep CHAR sizes minimal; prefer fixed CHAR for codes, VARCHAR for variable strings.

Security, Ethical & Practical Implications

  • Default root account with blank password is a major vulnerability; set a strong password immediately.
  • Validate & sanitise user input before building SQL queries.
  • Implement least privilege: web app account should have only required permissions (e.g. SELECT, INSERT, UPDATE on its specific database).
  • Back up databases routinely; consider binary logs for point-in-time recovery.
  • Follow organisational or legal compliance for data privacy (e.g. GDPR) when storing personal data such as age, name, faculty.

Connections to Prior Knowledge

  • CRUD parallels HTTP verbs:
    • CREATE → POST
    • READ → GET
    • UPDATE → PUT/PATCH
    • DELETE → DELETE
  • AUTO_INCREMENT primary key mirrors the concept of a sequence in Oracle or IDENTITY column in SQL Server.
  • Data typing echoes primitive types in programming (int, float, string), reinforcing the need for type discipline.

Real-World Relevance

  • The LAMP stack (Linux, Apache, MySQL, PHP) powers countless CMSs (WordPress, Joomla, Moodle), e-commerce sites (Magento, WooCommerce), and internal dashboards.
  • phpMyAdmin is often available on shared hosting, making these skills directly transferable to low-cost production environments.

Additional Resources

  • Book: PHP GURU – Tingkatkan Kung-Fu Pengaturcaraan Anda by Mohamad Rahimi Mohamad Rosman (Penerbit Universiti Utara Malaysia, 2023).
  • Official docs:
    • https://www.php.net/manual/en/book.mysqli.php
    • https://dev.mysql.com/doc/
    • https://www.phpmyadmin.net/