SQL Data Definition IK203 - PowerPoint Presentation

Slide1

SQL Data Definition

IK203

Sistem

Basis Data

Pertemuan

#14

Suryo

Widiantoro

, ST, MMSI,

M.Com

(IS)

Slide2

SQl untuk definisi data

Slide3

SQL

DDL

digunakan

untuk

membuat

dan

mengubah

struktur

tabel

database

,

serta

memasukkan

,

memodifikasi

, dan

menghapus

data

dalam

tabel

.

Sebelum

menggunakan

DBMS

,

ada

2

tugas

yang

harus

dikerjakan

:

Membuat

struktur

database

, dan

Membuat

tabel

yang

kan

menyimpan

data

end-user

.

Slide4

Membuat/menghapus

database

Slide5

Sebelum

membuat

tabel

,

kita

harus

membuat

database

.

Walaupun

ada

SQL

statement

untuk

membuat

database

,

kebanyakan

developer

menggunakan

graphic

tools

untuk

membuat

database

,

seperti

pada

Ms Access.

Slide6

Schema

adalah

nama

kumpulan

obyek

database

yang

saling

berkaitan

satu

dengan

lainnya

(

semua

obyek

dalam

database

dijelaskan

di

dalam

sebuah

schema

).

Obyek

di

dalam

schema

dapat

berupa

tabel

,

view

,

domain

, dan

kumpulan

karakter

.

Semua

obyek

di

dalam

schema

memiliki

pemilik

/

pembuat

yang

sama

.

Slide7

Pernyataan

SQL

statement

untuk

definisi

schema

secara

sederhana

dituliskan

sebagai

berikut

:

CREATE SCHEMA

nama

database

AUTHORIZATION

nama

pembuat

Contohnya

,

bila

pembuat

schema

SqlTest

adalah

Suryo, SQL

statement

nya

adalah

:

CREATE SCHEMA

SqlTest

AUTHORIZATION

Suryo;

Slide8

Sebuah

schema

dapat

dihancurkan

menggunakan

statement DROP SCHEMA,

dituliskan

sebagai

berikut

:

DROP SCHEMA

name database [

RESTRICT| CASCADE

]

RESTRICT

adalah

default,

di

mana

schema

harus

kosong

atau

perintah

akan

gagal

dijalankan

.

Bila

yang

dipilih

adalah

CASCADE

,

maka

perintah

akan

menghapus

seluruh

obyek

yang

berkaitan

dengan

schema

sesuai

urutan

.

Slide9

Membuat tabel

Slide10

SQL

statement

CREATE

TABLE

digunakan

untuk

membuat

struktur

tabel

. Format

dasar

statement

ini

adalah

:

CREATE TABLE

nama

tabel

(

namakolom

jenisdata

constraint

kolom

,

namakolom

jenisdata

constraint

kolom

,

table constraint

lainnya

. . .

);

Slide11

Constraint

kolom

yang

dapat

digunakan

adalah

:

PRIMARY KEY

,

NOT

NULL

,

NULL

,

dan

UNIQUE

.

Sebagai

tambahan

:

CHECK

, (

dibahas

pada

statement

ALTER), dan

DEFAULT

(

tidak

dianggap

constraint

kolom

)

digunakan

untuk

menetapkan

nilai

awal

.

Slide12

Contoh CREATE TABLE

Kolom

EmployeeNumber

memiliki

jenis

data Integer (

disingkat

Int

)

dan

constraint

kolom

sebagai

PRIMARY

KEY

.

Kolom FirstName menggunakan jenis data Character (disingkat Char) dan panjangnya 25 characters. Constraint kolomnya NOT NULL berarti harus ada nilai dan tidak boleh kosong. Kolom Phone, menggunakan jenis data Char(12) (termasuk separator antara area code, prefix, dan number) dengan constraint kolom NULL yang berarti boleh dikosongkan.

Slide13

Kolom

Department

menggunakan

jenis

data Char(35),

constraint

kolom

NOT NULL ,

dan

DEFAULT

untuk

menetapkan

nilai

awal

“human resources”

bila

tidak dimasukkan nilai saat membuat record baru.Kolom Email menggunakan jenis data VarChar(100) dengn constraint kolom NOT NULL dan UNIQUE. VarChar adalah variable-length yang berarti panjang character bisa bervariasi. Panjang maksimum email adalah 100 character. Namun bila email yang dimasukkan hanya 14 character, maka hanya 14 character yang akan disimpan.Constraint

kolom

UNIQUE

pada

Email

berarti

tidak

boleh

ada

duplikasi

nilai

pada

kolom

Email.

Ini

untuk

memastikan

bahwa

setiap

orang

harus

memiliki

email yang

berbeda

.

Slide14

Pada

tabel

PROJECT,

kolom

MaxHours

menggunakan

jenis

data Numeric(8,2) yang

berarti

nilai

MaxHours

terdiri

atas

8

angka

decimal, dengan 2 angka di belakang koma.Contohnya, DBMS akan menampilkan nilai 12345 yang disimpan sebagai 123.45, dan nilai 12345678 yang disimpan (8 angka) sebagai 123456.78.

Slide15

Keyword

DEFAULT

100

berarti

bahwa

saat

sebuah

record

dibuat

dan

tidak

ada

nilai

untuk

MaxHours, maka DBMS akan memberikan nilai default 100.00. Kolom StartDate menggunakan jenis data DateTime. Artinya nilai StartDate akan berisi date/tanggal sesuai dengan kebutuhan yang diinginkan.

Slide16

Jenis DataSQL Server 2012 yang umum

Slide17

Menentukan primary Key

dengan

table Constraint

Slide18

Table

constraint

diidentifikasi

dengan

keyword CONSTRAINT dan

dapat

digunakan

untuk

menerapkan

berbagai

constraint

.

Setiap

table

constraint memiliki nama constraint diikuti dengan definisi dari constraint tersebut. Primary key constraint ditulis dengan menggunakan nama tabel diikuti tanda underscore dan nama kolom yang menjadi primary key:CONSTRAINT nama tabel_PK PRIMARY KEY (nama kolom)

Slide19

Kolom

EmployeeNumber

pada

tabel

EMPLOYEE

diberi

tambahan

IDENTITY

(M,N

).

Keyword

IDENTITY

menunjukkan

bahwa

kolom

ini

adalah surrogate key yang dimulai dengan nilai M pada record pertama yang dibuat dan bertambah sebesar nilai N untuk setiap record yang dibuat berikutnya.Pada contoh di atas, EmployeeNumber akan dimulai dengan angka 1 dan bertambah dengan 1 (yaitu 1, 2, 3, 4, 5,…)

Slide20

Pada

contoh

di

atas

,

kolom

ProjectID

akan

dimulai

pada

nilai

1000 dan

bertambah

dengan 100 (yaitu 1000, 1100, 1200,…).

Slide21

Keuntungan Table Constraint

Diperlukan

untuk

mendefinisikan

composite key

,

karena

constraint

kolom

PRIMARY KEY

tidak

bisa

digunakan

untuk

lebih

dari satu kolom.Dapat memilih nama constraint yang digunakan untuk mendefinisikan primary key.Menggunakan table constraint untuk mendefinisikan primary key memudahkan untuk mendefinisikan surrogate key pada beberapa produk DBMS.

Slide22

Menentukan Foreign Key

dengan

table Constraint

Slide23

Slide24

Tabel

EMPLOYEE

memiliki

table

constraint

bernama

EMP_DEPART_FK yang

menetukan

hubungan

foreign

key

antara

kolom

Department

pada

EMPLOYEE dan

kolom

DepartmentName

pada

DEPARTMENT.Perhatikan statement ON UPDATE CASCADE yang menunjukkan perintah yang harus dijalankan bila nilai primary key DepartmentName pada DEPARTMENT berubah. Keyword CASCADE berarti bahwa perubahan yang sama harus dijalankan pada kolom Department yang berkaitan pada EMPLOYEE. Misalnya bila department Marketing diubah menjadi Sales and Marketing, maka nilai foreign key harus diupdate sesuai dengan perubahan.Karena DepartmentName

bukan

surrogate

key

,

maka

nilainya

dapat

diubah

,

sehingga

statement

ON

UPDATE CASCADE

bisa

dijalankan

.

Slide25

Pada

tabel

ASSIGNMENT,

ada

dua

foreign key constraint

:

satu

ke

EMPLOYEE dan

satunya

ke

PROJECT

.

Slide26

Yang

pertama

menentukan

constraint

ASSIGN_PROJ_FK

yang

membuat

ProjectID

pada

ASSIGNMENT

bereferensi

pada

kolom

ProjectID

pada

PROJECT.Statement ON UPDATE diatur menjadi NO ACTION. Ingat bahwa ProjectID adalah surrogate key, sehingga tidak akan pernah dapat berubah. Pada kasus ini, tidak perlu melakukan cascade update pada referensi primary key.Perhatikan bahwa ada juga statement ON DELETE, yang menunjukkan tindakan yang

harus

dijalankan

bila

sebuah

record

pada

PROJECT

dihapus

.

Statement

ON

DELETE CASCADE

berarti

bahwa

saat

sebuah

baris

pada

PROJECT

dihapus

,

maka

semua

baris

pada

ASSIGNMENT yang

terhubung

pada

baris

yang

dihapus

pada

PROJECT

juga

harus

terhapus

.

Slide27

Yang

kedua

adalah

table

constraint

foreign key

ASSIGN_EMP_FK

.

Constraint

ini

menunjukkan

bahwa

kolom

EmployeeNumber

bereferensi

ke kolom EmployeeNumber pada EMPLOYEE. Primary key yang bereferensi adalah surrogate key, sehingga ON UPDATE NO ACTION dapat diterapkan pada constraint ini.Statement ON DELETE NO ACTION menunjukkan bahwa penghapusan baris pada EMPLOYEE tidak diijinkan apabila baris tersebut terhubung dengan baris pada ASSIGNMENT.

Slide28

Menghapus tabel

Slide29

Statement

DROP

TABLE

adalah

salah

satu

SQL

statement

yang paling

berbahaya

karena

akan

menghapus

struktur

tabel

beserta seluruh isi data pada tabel.Untuk menghapus tabel dan seluruh data didalamnya, gunakan SQL statement berikut ini:DROP TABLE nama tabel;

Slide30

Statement

DROP

TABLE

tidak

berfungsi

bila

tabel

berisi

atau

dapat

berisi

nilai

yang

diperlukan

untuk memenuhi referential integrity constraint.Contohnya tabel EMPLOYEEberisi nilai EmployeeNumber yang diperlukan oleh foreign key constraint ASSIGN_EMP_FK. Dalam kasus ini, statement DROP TABLE EMPLOYEE akan gagal.Untuk menghapus tabel EMPLOYEE, tabel ASSIGNMENT harus dihapus terlebih dahulu, atau setidaknya hapus terlebih dahulu foreign key constraint ASSIGN_EMP_FK.

Slide31

Mengubah tabel

Slide32

ALTER

Statement

ALTER

TABLE

digunakan

untuk

menambah

,

memodifikasi

, dan

menghapus

kolom

serta

constraint

.

Slide33

Menghapus CONSTRAINT

Contohnya

,

untuk

menghapus

constraint

ASSIGN_EMP_FK

gunakan

statement

:

ALTER TABLE

ASSIGNMENT

DROP CONSTRAINT

ASSIGN_EMP_FK;

Slide34

Statement

ALTER

juga

dapat

digunakan

untuk

menambah

constraint

.

Constraint

CHECK

mirip

dengan

WHERE

pada

SQL

query

. Ini dapat berisi keyword IN, NOT IN, dan LIKE, serta dapat menggunakan tanda lebih kecil (<) dan lebih besar (>) untuk mengecek jangkauan nilai.

Slide35

Menambah CONSTRAINT

Contohnya

,

untuk

menambahkan

constraint

yang

diperlukan

pada

tabel

PROJECT,

gunakan

SQL

statement

berikut

ini:ALTER TABLE PROJECT ADD CONSTRAINT PROJECT_Check_Dates CHECK (StartDate < EndDate);

Slide36

Statement

ALTER

TABLE

juga

berguna

bila

diperlukan

untuk

menambah

atau

menghapus

kolom

.

Contohnya

,

ingin menambah sebuah kolom pada PROJECT untuk melacak berapa jam telah dijalankan pada sebuah project. Bila kolom ini diberi nama CurrentTotalHours, gunakan SQL statement berikut ini.

Slide37

Menambah Kolom

ALTER TABLE

PROJECT

ADD

CurrentTotalHours

Numeric(8,2)

NULL

;

Catatan

:

Karena

menambah

kolom

baru

maka

tidak

bisa NOT NULL; constraint akan langsung dilanggar karena datanya masih kosong. Bila ingin kolom ini NOT NULL, harus dibuat NULL terlebih dahulu, masukkan data yang diperlukan, lalu modifikasi kolom menjadi NOT NULL.

Slide38

Mengubah Kolom

Contohnya

,

setelah

data

dimasukkan

ke

dalam

CurrentTotalHours

pada

setiap

baris

yang

ada

,

baru

dapat diubah menjadi NOT NULL (lalu tambahkan nilai DEFAULT) dengan SQL statement sebagai berikut:ALTER TABLE PROJECT ALTER COLUMN CurrentTotalHours Numeric(8,2) NOT NULL DEFAULT 1;

Slide39

Menghapus Kolom

Bila

sebuah

kolom

sudah

tidak

diperlukan

lagi

,

maka

dapat

dihapus

dari

tabel PROJECT menggunakan SQL statement berikut ini:ALTER TABLE PROJECT DROP COLUMN CurrentTotalHours;Statement ALTER TABLE juga dapat digunakan untuk mengubah jenis data, namun harus berhati-hati karena dapat berakibat hilangnya data.

Slide40

TRUNCATE

Statement

TRUNCATE

TABLE

digunakan

untuk

menghapus

semua

data

dari

tabel

,

tetapi

membiarkan

struktur

tabel

tetap ada dalam database. Statement berikut ini digunakan untuk menghapus semua data dalam tabel PROJECT :TRUNCATE TABLE PROJECT;Statement TRUNCATE TABLE tidak dapat digunakan pada tabel yang direferensi oleh foreign key constraint karena dapat menciptakan nilai foreign key yang tidak memiliki pasangan nilai primary key. Jadi TRUNCATE TABLE dapat diterapkan pada

tabel

PROJECT,

tetapi

tidak

dapat

diterapkan

pada

tabel

DEPARTMENT.

Slide41

penutup