Mengenal Database Management System (DBMS)

Database Management System (DBMS) adalah merupakan suatu sistem software yang memungkinkan seorang user dapat mendefinisikan, membuat, dan memelihara serta menyediakan akses terkontrol terhadap data. Database sendiri adalah sekumpulan data yang berhubungan dengan secara logika dan memiliki beberapa arti yang saling berpautan

DBMS Environment

E-R Model Constructs

1. Entity instance - person, place, object, event, concept (often corresponds to a row in a table)
• Entity Type – collection of entities (often corresponds to a table)
2. Attribute - property or characteristic of an entity type (often corresponds to a field in a table)
3. Relationship instance – link between entities (corresponds to primary key-foreign key equivalencies in related tables)
4. Relationship type – category of relationship…link between entity types

E-R Diagram example

MySQL tables example

Database and Database Management System

• Database is simply a collection of data. In relational database, data is organized into tables.
• Database Management System (DBMS) is software to maintain and utilize the collections of data (Oracle, DB2, MySQL)

MySQL Introduction

• MySQL is a database management system
• SQL stands for the Structured Query Language. It defines how to insert, retrieve, modify and delete data
• Free from www.mysql.com
• Reference sites
• NASA, Yahoo!, Compaq, Motorola

Basic MySQL Operations

• Create table
• Insert records
• Load data
• Retrieve records
• Update records
• Delete records
• Modify table
• Join table
• Drop table
• Optimize table
• Count, Like, Order by, Group by
• More advanced ones (sub-queries, stored procedures, triggers, views …)

How MySQL stores data (by default)

1. A MySQL server can store several databases
2. Databases are stored as directories
• Default is at /usr/local/mysql/var/
3. Tables are stored as files inside each database (directory)
4. For each table, it has three files:
• table.FRM file containing information about the table structure
• table.MYD file containing the row data
• table.MYI containing any indexes belonging with this table, as well as some statistics about the table.

Login

• ssh –l adji 172.24.14.11
• mysql –h hostname –u username –p [password]
• Example
• % mysql -uroot -p Enter password: a**i
• Welcome to the MySQL monitor. Commands end with ; or
• \g. Your MySQL connection id is 23 to server version: 3.23.41.
• Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
• mysql>

Create User and Database

• mysql> show databases;
• mysql> use mysql;
• Use database mysql, used by the system
• mysql> create database testdb;
• mysql> use testdb;
• mysql> desc mysql.user;
• grant create, insert, delete, update, select (all priviliges) on testdb.* to test1@localhost identified by 'pass1';
• mysql>insert into user (Host, User, Password) values (‘localhost’, ‘test1’, password(‘pass1’));
• Create a new database user test1

Create User and Database (cont.)

• mysql>flush privileges
• Reloads the privileges from the grant tables in the database mysql
• Select user from mysql.user;
• GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON testdb.* TO ‘test1’@’localhost’ IDENTIFIED BY ‘pass1’;

Create Database

Create Table



Display Table Structure



Modify Table Structure



Insert Record

Retrieve Record

Update Record



Delete Record

Drop Table

Create Table

Baca Juga : Memahami Index Pada MongoDB

Display Table Structure

Modify Table Structure

Insert Record



Retrieve Record

Create Table

Display Table Structure

Modify Table Structure

Insert Record

Retrieve Record

Buck Load



More Table Retrieval

Group By

NULL

Table Join

Backup Database

MySQL Optimization

Denormalization

• Usually driven by the need to improve query spee
• Query speed is improved at the expense of more complex or problematic DML (Data manipulation language) for updates, deletions and insertions.

Downward Denormalization

Upward Denormalization