Comparison of Stored Procedures on Relational Database Management System

Store procedure is a group of query stored in database catalogue which allows it to be recalled. Insert, update and delete data could performed using store procedure. Stored procedure is an alternative to optimize query performance and also to reduce security gap at interface level of a system. This research provides an overview implementation of stored procedure insert, update and deleting in two DBMS, MariaDB and PostgreSQL. Software developers prefer MariaDB and PostgreSQL because it is open-source, which means free. This research is an experimental research and descriptive analysis. This research data uses student data with amount of data 25000, 15000 and 5000 data. Research result show PostgreSQL require less processing time than MariaDB. Results of this research can used as a reference to design application programs which sometimes choose a DBMS is not a concern of developer.

RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS)

Buku berjudul "Relational Database Management System" ini mencoba membahas MySQL secara praktis, disajikan secara terstruktur dan disertai contoh‐contoh dan latihan untuk membantu pemahaman. Buku ini diharapkan dapat membantu Anda menguasai MySQL hingga mahir. Buku ini sangat cocok bagi Anda yang baru mempelajari MySQL maupun bagi Anda yang ingin lebih memperdalam MySQL sebagai salah satu software database terkemuka saat ini. Buku ini terbagi menjadi 4 (empat) bagian. Bagian pertama merupakan bagian pendahuluan yang membahas mengenai penjelasan singkat MySQL dan juga langkah instalasi MySQL serta software pendukung lainnya. Bagian kedua adalah Dasar‐dasar MySQL yang menjelaskan mengenai perintah‐perintah dasar dari MySQL termasuk fungsi‐fungsi di dalam MySQL. Pada bagian ketiga dipaparkan mengenai perintah‐perintah MySQL yang lebih kompleks seperti penggabungan antar tabel, trigger, views dan stored procedure. Selanjutnya pada bagian yang terakhir akan dijelaskan mengenai penyajian laporan dan proses backup, restore database MySQL.

Comparative Analysis of Database Query Storage Performance Between Stored Procedure and Function

This research was conducted to measure the data storage time carried out by the DBMS on data that has been prepared with an increasing number of data, the data provided is consistent student data which will be stored with Stored Procedure and Function. This study uses the action research method which has 4 stages, starting with planning, action, observation and reflection. From the results of the experiments that have been carried out, it appears that Stored Procedure is able to outperform Function in data storage time. The data provided are 2 different types of data, each of which consists of 500, 2500, 4500 and 6500 data stages. This study also compares data storage that is differentiated by the computer between the data provider computer and the data storage computer or server, the result of which is the Stored Procedure. able to outperform Function in data storage speed.

Three Layer Security Model for Distributed Relational Database

Today we are living in a digital world where we all are connected to an open access network to exchange information. This information is more valuable assets that need protection from unauthenticated access. There are many security models has been proposed to protect the data stored in database but still needs more protection from attacks. In this study we have a three layer security model for relational database. Proposed model builds a security architecture based on access control policy, cryptography techniques and stored procedure. These mechanisms and techniques are combined together to present an interactive three-layer security model for securing relational databases in distributed environment. This model provides a kind of security controlling both in rests of database and data in motion from malicious user and other attacks.

Optimasi Proses Klasterisasi di MySQL DBMS dengan Mengintegrasikan Algoritme MIC-Kmeans Menggunakan Bahasa SQL dalam Stored Procedure

<p>Proses klasterisasi data di <em>DBMS</em> akan lebih efisien jika dilakukan langsung di dalam <em>DBMS</em> itu sendiri karena <em>DBMS</em> mendukung untuk pengelolaan data yang baik. <em>SQL-Kmeans</em> merupakan salah satu metode yang sebelumnya telah digunakan untuk mengintegrasikan algoritme klasterisasi <em>K-means</em> ke dalam <em>DBMS</em> menggunakan <em>SQL</em>. Akan tetapi, metode ini juga membawa kelemahan dari algoritme <em>K-means</em> itu sendiri yaitu lamanya iterasi untuk mencapai konvergen dan keakuratan hasil klasterisasi yang belum optimal akibat dari proses inisialisasi <em>centroid</em> awal secara acak. Algoritme <em>Median Initial Centroid (MIC)-Kmeans</em> merupakan pengembangan dari algoritme <em>K-means</em> yang bisa memberikan solusi optimal dalam menentukan awal <em>centroid</em> yang berdampak pada keakuratan dan lamanya iterasi. Dengan keunggulan yang dimiliki algoritme <em>MIC-Kmeans</em>, maka dalam penelitian ini dipilih sebagai alternatif algoritme yang diintegrasikan dalam proses klasterisasi data secara langsung di <em>DBMS</em> menggunakan <em>SQL</em>. Proses integrasinya meliputi 4 tahap yaitu tahap inisialisasi tabel <em>dataset</em>, tahap pemetaan algoritme <em>MIC-Kmeans</em> pada <em>SQL</em> dan tabel <em>dataset</em>, tahap perancangan <em>SQL </em>untuk tiap hasil pemetaan dan tahap implementasi rancangan <em>SQL</em> dalam <em>MySQL</em> <em>stored procedure</em>. Hasil pengujian menunjukkan bahwa metode <em>SQL MIC-Kmeans</em> bisa mengurangi 43% jumlah iterasi dan mengurangi 39% waktu yang dibutuhkan dari metode <em>SQL-Kmeans</em> untuk mencapai konvergen. Selain itu, nilai rata-rata <em>silhouette coefficient </em>metode <em>SQL MIC-Kmeans</em> adalah 0,79 dan masuk dalam kategori <em>strong structure</em> (nilai rentang 0,7 sampai 1). Sedangkan nilai rata-rata <em>silhouette coefficient </em>metode <em>SQL-Kmeans </em>adalah<em> </em>0,68<em> </em>dan masuk dalam kategori <em>medium structure </em>(nilai rentang 0,5 sampai 0,7).</p><p class="Judul2"><strong><em>Abstract</em></strong></p><p class="Judul2"><em>The process of data clustering in the DBMS will be more efficient because the DBMS supports good data management. SQL-Kmeans is a method that has been used to integrate K-means clustering algorithms into DBMS using SQL. However, it carries the weakness of the K-means algorithm itself in the duration of iterations to reach convergence and the accuracy of clustering due to the centroid initialization process randomly. Median Initial Centroid (MIC)-Kmeans algorithm is a development of the K-means algorithm that can provide the optimal solution in determining the initial centroid which has an impact on the accuracy and duration of iterations. With the advantages of the MIC-Kmeans algorithm, the method was chosen as an alternative algorithm to be integrated in the DBMS using SQL for a clustering. The integration process includes 4 stages, there are dataset initialization, SQL algorithm mapping and dataset table, SQL design for each mapping result, and implementation SQL in the MySQL stored procedure. The test results show that the SQL MIC-Kmeans method can reduce 43% the number of iterations and reduce 39% of the time required from the SQL-Kmeans method to reach convergence. In addition, the average value of the coefficient SQL MIC-Kmeans method is 0.79 and categorized as strong structure (value ranges from 0.7 to 1). While, the average value of the coefficient SQL-Kmeans method is 0.68 and categorized as medium structure (value ranges from 0.5 to 0.7).</em></p>