SQL-DDL
TUJUAN
1. Tipe data yang didukung SQL
2. Tujuan Integrity enhancement pada SQL
3. Penerapan integrity constraints dengan SQL
4. Menggunakan Integrity enhancement dalam
pernyataan CREATE dan ALTER TABLE
5. Mengenal View
2
TUJUAN
6. Membuat dan menghapus view dengan SQL
7. Operasi DBMS terhadap view
8. Keuntungan dan kerugian view
9. Security dengan GRANT dan REVOKE
3
Tipe data SQL
• Identifier
digunakan untuk identifikasi objek BD,
seperti nama tabel, nama view, dan kolom
• Karakter yang digunakan :
A….Z a….z 0….9 _
• Syarat :
– <= 128 karakter
– Dimulai dengan huruf
– Tidak ada spasi
4
TIPE DATA SQL - ISO
Lebih hemat space
TRUE/FALSE
Character Varying
Nilai 0/1
Year, Month, Day gabungan
Periode waktu
Hour, Minute, Second
Precision dan scale
5
Integrity enhancement Feature (IEF)
• IEF :
– hal-hal yang perlu diperhatikan dalam membuat
basis data
– Berfungsi sebagai constraint/batasan
• Lima tipe IEF :
1. Data yang harus ada
2. Domain constraint
3. Entity Integrity
4. Referential integrity
5. Enterprise constraint
6
Integrity enhancement Feature (IEF)
1. Data yang harus ada
Position VARCHAR(10) NOT NULL
2. Domain constraint
(a) CHECK (search condition)
Sex CHAR NOT NULL CHECK ( sex IN (‘M’,’F’))
* Setiap field/kolom harus mempunyai domain
7
Integrity enhancement Feature (IEF)
– Cara membuat Create Domain
CREATE DOMAIN NamaDomain [AS] TipeData
[DEFAULT default/option]
[CHECK (searchCondition]
Contoh :
CREATE DOMAIN SexType AS CHAR
CHECK (VALUE in (‘M’,’F’))
– Mendefinisikan kolom/field
sex SexType NOT NULL
8
Integrity enhancement
Contoh :CREATE DOMAIN BranchNo AS CHAR(4)
CHECK (VALUE in (SELECT branchNo
FROM Branch))
• Domain dapat dihapus dengan DROP DOMAIN :
DROP DOMAIN NamaDomain [RESTRICT | CASCADE]
– Restrict : drop domain akan gagal jika domain tsb
masih digunakan oleh tabel/ view yang ada
– Cascade :tabel/view yang domainnya dihapus,
maka nilainya akan disesuaikan dengan nilai
default/tipe data dasar
Contoh : DROP DOMAIN BranchNo CASCADE
9
IEF – Entity Integrity
3. Entity Integrity
• Primary key pada suatu tabel harus
bernilai unique dan tidak boleh bernilai
NULL
• Contoh :
PRIMARY KEY (PropertyNo)
PRIMARY KEY (clientNo, PropertyNo)
• Untuk menjamin unique alternate key :
UNIQUE (telNo)
10
IEF – Referential Integrity
4. Referential Integrity
• Foreign Key adalah sebuah atau beberapa
field yang menghubungkan setiap baris
pada child table yang berisi FK ke parent
table yang mempunyai CK “match”
• Referential Integrity Nilai FK pada child
table harus sama/bersesuaian dengan CK
parent table
• Contoh :
FOREIGN KEY (BranchNo) REFERENCES Branch
• Terlihat pada saat INSERT/UPDATE
11
IEF – Referential Integrity
• Perintah SQL yang digunakan untuk
mencegah update/delete CK pada parent
table yang “match “ dengan baris pada
child table referential action
• Jenisnya:
– CASCADE
– SET DEFAULT
– SET NULL
– NO ACTION
12
IEF – Referential Integrity
CASCADE :
Menghapus baris dari parent table
otomatis menghapus baris yang
“match” pada child table
SET NULL:
Menghapus baris dari parent table dan
memberi nilai NULL untuk FK pada
child table
13
IEF – Referential Integrity
SET DEFAULT:
Menghapus baris dari parent table dan
memberi nilai default untuk FK pada
child table
NO ACtION
Proses DELETE parent table ditolak
default
14
IEF – Referential Integrity
• Contoh :
FOREIGN KEY (staffNo) REFERENCES Staff
ON DELETE SET NULL
FOREIGN KEY (ownerNo) REFERENCES
PrivateOwner ON UPDATE CASCADE
15
IEF – Interprise Constraint
5. Interprise Constraint
• Batasan dari organisasi/pengguna DB
• Didefinisikan dengan CHECK dan UNIQUE
• Digunakan pada CREATE dan ALTER
TABLE
• Misal pada basis data DreamHome setiap
staf tidak boleh menangani lebih dari 100
property pada saat bersamaan
16
Data Definition
• SQL DDL membuat dan menghapus obyek-
obyek basis data (schema, domain, tabel, view
dan indeks)
• Sintaks utama SQL DDL :
CREATE SCHEMA DROP SCHEMA
CREATE/ALTER DOMAIN DROP DOMAIN
CREATE/ALTER TABLE DROP TABLE
CREATE VIEW DROP VIEW
• Beberapa DBMS mendukung :
CREATE INDEX DROP INDEX
17
Membuat Tabel
• Langkah –langkah dalam membuat tabel:
1. Identify data types for attributes
2. Identify columns that can and cannot be null
3. Identify columns that must be unique (candidate
keys)
4. Identify primary key-foreign key mates
5. Determine default values
6. Identify constraints on columns (domain
specifications)
18
Membuat Tabel
• Digunakan untuk membuat sebuah tabel dengan satu
atau banyak field termasuk menentukan tipe datanya
CREATE TABLE NamaTabel
{(NamaKolom TipeData [NOT NULL] [UNIQUE]
[DEFAULT defaultOption]
[CHECK searchCondition [,…]}
[PRIMARY KEY (daftarkolom),]
{[UNIQUE (daftarkolom),] […,]}
{[FOREIGN KEY (daftarkolomFK)
REFENCES NamaTabelPArent [(daftarkolomCK)],
[ON UPDATE referentialAction]
[ON DELETE referentialAction]] [,…]}
19
Membuat Tabel
CREATE DOMAIN OwnerNumber AS VARCHAR(5)
CHECK (VALUE in (SELECT ownerNo FROM
Owner));
CREATE DOMAIN StaffNumber AS VARCHAR(5)
CHECK (VALUE in (SELECT staffNo FROM
Staff));
CREATE DOMAIN PNumber AS VARCHAR(5);
CREATE DOMAIN PRoom AS SMALLINT
CHECK (VALUE BETWEEN 1 AND 15);
CREATE DOMAIN PRent AS DECIMAL(6,2)
CHECK (VALUE BETWEEN 0 AND 9999.99);
20
Membuat Tabel
CREATE TABLE PropertyForRent (
PropertyNo Pnumber NOT NULL
rooms Proom NOT NULL, DEFAULT 4,
rent PRent NOT NULL, DEFAULT 600,
Owner OwnerNumber NOT NULL,
StaffNo StaffNumber Constraint StaffNotHandlingTooMuch …
branchNo BranchNumber NOT NULL,
PRIMARY KEY (PropertyNo),
FOREIGN KEY (StaffNo) REFERENCES Staff
ON DELETE SET NULL
UPDATE CASCADE …);
21
Mengubah definisi tabel
(ALTER TABLE)
• Menambahkan 1 field baru ke tabel
• Menghapus 1 field pada tabel
• Menambahkan table constraint yang baru
• Menghapus table constraint
• Menentukan nilai default untuk suatu kolom
• Menghapus nilai default pada suatu kolom
22
ALTER TABLE
• Contoh : Ubah tabel Staff dengan menghapus
nilai default “Assistant” untuk kolom position
dan set nilai default baru untuk kolom sex
adalah female (‘F’)
ALTER TABLE Staff
ALTER position DROP DEFAULT;
ALTER TABLE Staff
ALTER sex SET DEFAULT ‘F’;
23
ALTER TABLE
• Contoh : Hapus Constraint dari
PropertyForRent yang membatasi staff agar
tidak dapat menangani properti lebih dari 100
pada waktu yang bersamaan. Tambahkan field
baru ke tabel Client
ALTER TABLE PropertyForRent
DROP CONSTRAINT
StaffNotHandlingTooMuch …;
ALTER TABLE Client
ADD prefNoRoom Prooms;
24
Menghapus Tabel (DROP TABLE)
DROP TABLE TableName [RESTRICT | CASCADE]
Contoh : DROP TABLE PropertyForRent;
• Menghapus tabel dan semua baris yang ada
didalamnya
• Dengan RESTRICT, jika ada objek BD lainnya
yang terkait , misal keberadaan objek BD
tersebut tergantung pada tabel yang akan
dihapus, maka sintak SQL tersebut akan
ditolak
• Dengan CASCADE, SQL akan menghapus
semua obyek yang terkait tersebut
25
VIEW
View
Hasil dinamis dari satu atau lebih operasi
relasional yang bekerja pada suatu relasi
dasar untuk menghasilkan relasi lagi
• Virtual relasi sebenarnya tidak muncul secara
nyata dalam basis data tetapi relasi tersebut
akan dihasilkan pada saat ada permintaan
• Isi dari view ditentukan oleh query pada relasi
dasar
26
VIEW
• Dengan view resolution, suatu operasi pada
view diterjemahkan dalam bentuk operasi
yang bekerja pada relasi asal view tersebut
diturunkan
• Dengan view materialization, suatu view
disimpan sebagai tabel sementara, dengan
maintain ketika relasi asal view tersebut
diupdate
• Dengan view updatability, proses update pada
tabel dasar akan mempengaruhi semua view
yang termasuk, begitu juga sebaliknya
27
SQL - Membuat VIEW
CREATE VIEW NamaView [ (NamaKolomBaru [,...]) ]
AS subselect
[WITH [CASCADED | LOCAL] CHECK OPTION]
• Kolom-kolom pada view dapat diberi nama
tersendiri
• Dalam penentuan nama kolom view, jumlah
nama kolom pada subselect harus sama
dengan jumlah nama kolom pada view
• Jika hal tersebut diabaikan, maka nama kolom
pada view akan mengambil nama yang
berkesesuaian urutannya pada subselect
28
SQL - Membuat Horizontal VIEW
Buat view dimana manajer pda branch B003 dapat
melihat daftar staff yang bekerja hanya pada
kantor dia.
CREATE VIEW Manager3Staff
AS SELECT *
FROM Staff
WHERE branchNo = 'B003';
29
SQL - Membuat Vertical VIEW
Buat view daftar staff yang bekerja pda branch B003
kecuali gajinya
CREATE VIEW Staff3
AS SELECT staffNo, fName, lName, position, sex
FROM Staff
WHERE branchNo = 'B003';
30
Menghapus view-DROP VIEW
DROP VIEW ViewName [RESTRICT | CASCADE]
• Menghapus view
• Contoh :
DROP VIEW Manager3Staff;
31
Privileges
• Mendefinisikan action yang boleh dilakukan
user pada tabel dasar atau view :
SELECT Mendapatkan data dari suatu tabel
INSERT Menyisipkan baris baru ke tabel
UPDATE Mengubah baris pada tabel
DELETE Menghapus baris dari tabel
REFERENCES Me-refer kolom suatu tabel pada
integrity constraint
USAGE Menggunakan domain, collation,
himpunan karakter dan translations
32
GRANT
GRANT {PrivilegeList | ALL PRIVILEGES}
ON ObjectName
TO {AuthorizationIdList | PUBLIC}
[WITH GRANT OPTION]
• PrivilegeList berisi daftar jenis privilege ,
dipisahkan dengan koma
• ALL PRIVILEGES memperbolehkan user
melakukan semua jenis privilege
33
GRANT
• Manajer diberi privilege penuh pada tabel Staff
GRANT ALL PRIVILEGES
ON Staff
TO Manager WITH GRANT OPTION;
• Personnel dan Director dapat melakukan
SELECT dan UPDATE pada kolom salary di
tabel Staff
GRANT SELECT, UPDATE (salary)
ON Staff
TO Personnel, Director;
34
REVOKE
• REVOKE menghapus hak privilege yang telah
dilakukan GRANT
REVOKE [GRANT OPTION FOR]
{PrivilegeList | ALL PRIVILEGES}
ON ObjectName
FROM {AuthorizationIdList | PUBLIC}
[RESTRICT | CASCADE]
• Contoh
Revoke privilege SELECT tabel Branch
dari semua user
REVOKE SELECT
ON Branch
FROM PUBLIC;
35
SELESAI
Terima Kasih
PR
1. Buat tabel-tabel beserta nilainya pada
halaman terlampir dengan SQL
memperhatikan integrity enhancement berikut :
a. Type untuk ROOM, nilainya Single, Family, dan
Double
b. Price untuk ROOM nilainya 10,00 s/d 500,00
c. Date_From dan Date_To tidak boleh sama dengan
tanggal sekarang
d. Tidak boleh ada pesanan dobel untuk Room yang
sama
e. Tidak boleh ada pesanan dobel untuk tamu yang
sama
f. Perhatikan atribut yang dapat/tidak dapat bernilai
NULL
g. Perhatikan juga atribut yang menjadi PK dan FK
37
PR
3. Buat view yang berisi nama hotel dan nama
tamu yang menginap disana
(nama view :DataHotel)
3. Berikan hak akses penuh kepada Manager
terhadap view DataHotel
4. Hapus hak akses no 4.
38