1. PEMBUATAN DATABASE DB_SPP

Introduction

This tutorial guides you through the process of creating a database for the SPP payment application (DB_SPP). It is designed for developers and students interested in understanding database creation in the context of a school payment system. By following these steps, you will be able to set up the database necessary for managing student payments effectively.

Step 1: Setting Up Your Database Environment

Before creating the database, ensure you have the necessary environment set up.

• Install a Database Management System (DBMS): Use MySQL or any other preferred DBMS.
• Access your DBMS: You can use a GUI tool like phpMyAdmin for ease of use or command line for direct access.
• Create a new database: Use the following SQL command:

  CREATE DATABASE DB_SPP;
  

Step 2: Creating the Tables

In the DB_SPP database, you need to create several tables to manage different aspects of the payment system.

• Student Table: Stores information about students.

  CREATE TABLE students (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100),
      class_id INT,
      UNIQUE(name)
  );
  

• Class Table: Stores information about classes.

  CREATE TABLE classes (
      id INT AUTO_INCREMENT PRIMARY KEY,
      class_name VARCHAR(50) UNIQUE
  );
  

• Payment Table: Tracks payment transactions.

  CREATE TABLE payments (
      id INT AUTO_INCREMENT PRIMARY KEY,
      student_id INT,
      amount DECIMAL(10, 2),
      payment_date DATE,
      FOREIGN KEY (student_id) REFERENCES students(id)
  );
  

Step 3: Establishing Relationships Between Tables

To ensure data integrity and establish relationships, define foreign keys in your tables.

• Ensure that the class_id in the students table is linked to the id in the classes table.
• Ensure that student_id in the payments table is linked to the id in the students table.

Step 4: Inserting Sample Data

Add sample data to test your database functionality.

• Insert classes:

  INSERT INTO classes (class_name) VALUES ('Class 10'), ('Class 11'), ('Class 12');
  

• Insert students:

  INSERT INTO students (name, class_id) VALUES ('John Doe', 1), ('Jane Smith', 2);
  

• Insert payments:

  INSERT INTO payments (student_id, amount, payment_date) VALUES (1, 100.00, '2023-01-01'), (2, 200.00, '2023-01-02');
  

Step 5: Querying the Database

Learn how to retrieve information from your database.

• To get all students with their classes:

  SELECT students.name, classes.class_name
  FROM students
  JOIN classes ON students.class_id = classes.id;
  

• To view payment history:

  SELECT students.name, payments.amount, payments.payment_date
  FROM payments
  JOIN students ON payments.student_id = students.id;
  

Conclusion

You've successfully set up a database for the SPP payment application. This includes creating the necessary tables, establishing relationships, and inserting sample data. Next, you may want to explore creating a user interface for the application or expand functionalities such as CRUD operations and reporting features. This foundational knowledge of database management will help you in developing more complex applications in the future.