Transcript Basisdata_09

SQL: DDL
Basis Data 09
Sasaran :
• Tipe data yang didukung oleh SQL
standard.
• Kegunaan dari integrity enhancement
feature pada SQL.
• Bagaimana menetapkanbatasan integritas
menggunakan SQL.
• Bagaimana menggunakan integrity
enhancement feature dalam perintah
CREATE dan ALTER TABLE.
Data Type (ISO SQL )
Integrity Enhancement Feature
•
•
•
•
•
Data yang dibutuhkan (Required data)
Batasan domain (Domain constraints)
Integritas entitas (Entity integrity)
Integritas referensial (Referential integrity)
Batasan enterprise (Enterprise
constraints)
IEF-Required Data
• Beberapa kolom field harus memiliki nilai yang
pasti (tidak diperkenankan bernilai NULL).
• NULL digunakan untuk merepresentasikan data
yang tidak ada/ tidak tersedia, hilang atau tidak
disertakan.
• Standar ISO menetapkan keyword NOT NULL
untuk mengatasi hal tersebut.
• Contoh format deklarasi :
position VARCHAR(10) NOT
NULL
IEF-Domain Constraints
• Setiap kolom mempunyai domain, atau dengan
kata lain himpunan dari nilai-nilai yang benar.
• Misalkan dalam tabel Staff terdapat kolom Sex
yang berisi nilai karakter tunggal ‘M’ atau ‘F’,
maka dapat dideklarasikan CHECK (Search
Condition);
• sehingga :
sex CHAR
NOT NULL
CHECK (sex IN (‘M’, ‘F’));
Lanjut..
• atau dieklarasikan secara eksplisit :
CREATE DOMAIN DomainName
dataType
[DEFAULT defaultOption]
[CHECK (searchCondition)]
[AS]
• Sehingga :
CREATE DOMAIN SexType AS CHAR
CHECK (VALUE IN (‘M’, ‘F’));
sex
SexType NOT NULL
Lanjut..
• searchCondition dapat mengandung table
lookup, misalkan membuat domain
Branchnumber untuk memastikan nilai yang
akan dimasukan sesuai dengan branch number
yang sudah ada pada tabel Branch :
CREATE DOMAIN BranchNo AS
CHAR(4)
CHECK (VALUE IN (SELECT branchNo
FROM Branch));
Lanjut..
• Domain dapat dihapuskan dengan menggunakan
perintah DROP DOMAIN, jika ditetapkan RESTRICT
kemudian domain digunakan dalam tabel, view dan
definisi penegasan (assertion definition), maka
penghapusan domain akan ditolak. Jika ditetapkan
CASCADE, maka kolom tabel yang menggunakan
domain tersebut secara otomatis diganti dengan nilai
default yang ada.
DROP DOMAIN DomainName
[RESTRICT | CASCADE]
IEF - Entity Integrity
• Primary key dari suatu tabel harus berisi nilai
yang unik, dan non-null untuk setiap barisnya.
• Standard ISO menyediakan clause FOREIGN
KEY pada perintah CREATE dan ALTER TABLE
:
PRIMARY KEY(staffNo)
PRIMARY KEY(clientNo, propertyNo)
->(Jika primary Key terdiri dari beberapa kolom)
Lanjut..
• Hanya dapat mempunyai 1 clause
PRIMARY KEY untuk setiap table, tetapi
masih dapat memastikan pemasukkan
nilai yang unik untuk beberapa alternate
key dengan menggunakan keyword
UNIQUE:
UNIQUE(telNo)
IEF - Referential Integrity
• Foreign Key adalah kolom atau himpunan kolom yang
menghubungkan setiap baris dalam child table yang
berisi Foreign Key dengan baris dari parent table yang
berisi Primary Key yang sesuai/match.
• Integritas referential berarti, jika FK berisi suatu nilai,
maka nilai tersebut harus mengacu kesuatu baris dalam
parent table.
• Standard ISO menyediakan pendefinisian untuk FK
dengan clause FOREIGN KEY dalam CREATE dan
ALTER TABLE:
FOREIGN KEY(branchNo) REFERENCES Branch
• Operasi INSERT/UPDATE yang berusaha untuk
membuat nilai FK dalam child table tanpa nilai candidate
key yang sesuai dalam parent table.
Lanjut..
Aksi yang dilakukan yang berusaha untuk merubah / menghapus
(update/delete) nilai candidate key dalam parent table yang memiliki
baris yang sesuai dalam child table tergantung pada referential action
yang ditetapkan dengan subclause ON UPDATE dan ON DELETE.
Terdapat 4 pilihan aksi, yaitu :
– CASCADE, menghapus baris dari parent table dan secara otomatis
menghapus baris yang sesuai dalam child table, jika baris yang dihapus
tadi merupakan candidate key yang digunakan sebagai foreign key pada
tabel lainnya, maka aturan foreign key untuk tabel ini dihilangkan.
– SET NULL, menghapus baris pada parent table dan menetapkan nilai
foreign key dalam child table menjadi NULL. Berlaku jika kolom foreign
key mempunyai qualifier NOT NULL.
– SET DEFAULT, menghapus baris dari parent table dan menetapkan setiap
komponen foreign key dari child table menjadi defaultyang telah
ditetapkan. Berlaku jika kolom foreign key memliki nilai DEFAULT.
– NO ACTION, menolak operasi penghapusan dari parent table. Merupakan
default jika aturan ON DELETE dihilangkan
Contoh 1
• Pada tabel PropertyForRent, StaffNo merupakan
foreign key yang mengacu ke tabel Staff. Untuk
menetapkan aturan penghapusan, jika record
staff dihapus dari tabel Staff, maka nilai StaffNo
yang ada pada PropertyForRent akan diganti
menjadi NULL. Sehingga dapat dituliskan :
• FOREIGN KEY (staffNo) REFERENCES Staff
ON DELETE SET NULL
Contoh 2:
• OwnerNo dalam tabel PropertyForRent
merupakan foreign key yang mengacu ke tabel
PrivateOwner. Untuk mendefinisikan aturan
peng-update-an, jika OwnerNo dalam
PrivateOwner di-update, maka kolom yang
terkait dalam tabel PropertyForRent akan diganti
dengan nilai baru, sehingga dapat dituliskan :
FOREIGN KEY (ownerNo) REFERENCES
Owner ON UPDATE CASCADE
IEF - Enterprise Constraints
• Standard ISO memungkinkan untuk
menetapkan pendefinisian enterprise
constraint dengan menggunakan clause
CHECK/UNIQUE dalam CREATE dan
ALTER TABLE juga CREATE
ASSERTION.
• Format pendeklarasian :
CREATE ASSERTION AssertionName
CHECK (searchCondition)
Contoh
• Untuk mendefinisikan enterprise constraint yang
menegaskan agar anggota staff tidak mengatur
lebih dari 100 property pada waktu yang sama :
CREATE ASSERTION StaffNotHandlingTooMuch
CHECK (NOTEXISTS (SELECT
staffNo
FROM PropertyForRent
GROUP BY staffNo
HAVING COUNT(*) > 100))
Data Definition
• SQL DDL memungkinkan objek database seperti
schema, domain, table, view, dan index untuk
dibuat dan dihapuskan.
• Perintah-perintah SQL DDL yang utama adalah :
–
–
–
–
–
–
–
–
CREATE SCHEMA
DROP SCHEMA
CREATE/ALTER DOMAIN
DROP DOMAIN
CREATE/ALTER TABLE
DROP TABLE
CREATE VIEW
DROP VIEW
Lanjut..
• Beberapa DBMS juga menyediakan :
– CREATE INDEX
– DROP INDEX
• Relasi-relasi dan objek lain dari suatu database berada
dalam sebuah environment.
• Setiap environment mengandung saru atau lebih
catalog, dan setiap catalog terdiri dari
sekumpulan/himpunan skema.
• Skema adalah suatu himpunan bernama yang terdiri dari
oobjek-objek database yang saling berhubungan.
• Objek dalam sebuah skema dapat berupa table, view,
domain, assertion, collation, translation, dan himpunan
karakter. Seluruhnya memiliki owner yang sama.
Membuat Skema (CREATE SCHEMA)
• Perintah untuk mendefinisikan skema :
CREATE SCHEMA [Name |
AUTHORIZATION CreatorId ]
• Perintah untuk menghapus skema :
DROP SCHEMA Name [RESTRICT | CASCADE ]
• Jika ditetapkan RESTRICT (default), maka skema harus
kosong atau operasi akan digagalkan. Jika ditetapkan
CASCADE, maka operasi berjalan berurut menghapus
seluruh objek yang terkait dengan skema yang telah
didefinisikan sebelumnya. Jika terjadi kegagalan operasi,
maka DROP SCHEMA akan gagal juga.
Membuat table (CREATE TABLE)
• Membuat tabel dasar digunakan format sbb :
CREATE TABLE TableName
{(colName dataType [NOT NULL] [UNIQUE]
[DEFAULT defaultOption]
[CHECK searchCondition] [,...]}
[PRIMARY KEY (listOfColumns),]
{[UNIQUE (listOfColumns),] […,]}
{[FOREIGN KEY (listOfFKColumns)
REFERENCES ParentTableName [(listOfCKColumns)],
[MATCH {PARTIAL|FULL}]
[ON UPDATE referentialAction]
[ON DELETE referentialAction ]] [,…]}
{[CHECK (searchCondition)] [,…] })
Lanjut..
• Membuat tabel dengan satu atau lebih kolom
dengan tipe data tertentu.
• Dengan NOT NULL, sistem akan menolak setia[
usaha untuk memasukan nilai NULL kedalam
kolom.
• Dapat menspesifikasikan nilai DEFAULT untuk
kolom.
• Primary key harus selalu ditetapkan NOT NULL.
• Clause FOREIGN KEY menetapkan FK
bersama dengan aksi referensial.
Contoh:
CREATE DOMAIN OwnerNumber AS VARCHAR(5)
CHECK (VALUE IN (SELECT ownerNo FROM
PrivateOwner));
CREATE DOMAIN StaffNumber AS VARCHAR(5)
CHECK (VALUE IN (SELECT staffNo FROM Staff));
CREATE DOMAIN BranchNumber AS VARCHAR(5)
CHECK (VALUE IN (SELECT branchNo FROM
Branch));
CREATE DOMAIN PropertyNumber AS VARCHAR(5);
CREATE DOMAIN Street AS VARCHAR(25);
CREATE DOMAIN City AS VARCHAR(15);
CREATE DOMAIN PostCode AS VARCHAR(8);
CREATE DOMAIN PropertyType AS
VARCHAR(1);
CHECK (VALUE IN (‘B’, ‘C’, ‘D’, ‘E’, ‘F’, ’M’, ‘S’));
CREATE DOMAIN PropertyRooms AS SMALLINT;
CHECK(VALUE BETWEEN 1 AND 15);
CREATE DOMAIN PRent AS DECIMAL(6,2)
CHECK(VALUE BETWEEN 0 AND 9999.99);
CREATE TABLE PropertyForRent (
propertyNo
PropertyNumber
NOT NULL,
street
Street
NOT NULL,
city
City
NOT NULL,
postcode
PostCode
type
PropertyType
NOT NULL
DEFAULT 4,
rooms
PropertyRooms
NOT NULL
DEFAULT 4,
rent
PropertyRent
NOT NULL,
DEFAULT 600,
ownerNo
OwnerNumber
NOT NULL,
staffNo
StaffNumber
Constraint StaffNotHandlingTooMuch
CHECK (NOT EXIST( SELECT staffNo FROM PropertyForRent
GROUP BY staffNo
HAVING COUNT(*) > 100)),
branchNo
BranchNumber
NOT NULL,
PRIMARY KEY (propertyNo),
FOREIGN KEY (staffNo) REFERENCES Staff
ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY (ownerNo) REFERENCES PrivateOwner
ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (branchNo) REFERENCES Branch
ON DELETE NO ACTION ON UPDATE CASCADE);
Merubah Table (ALTER TABLE)
•
•
•
•
•
•
Menambahkan kolom pada tabel
Menghapus kolom dari tabel
Menambahkan batasan kolom
Menghapus batasan tabel
Menetapkan default untuk kolom
Menghapus default dari kolom
Contoh:
• Ubah tabel Staff dengan menghapus
default ‘Assistant’ untuk kolom position
dan tetapkan default untuk kolom sex
menjadi (‘F’).
ALTER TABLE Staff
ALTER position DROP DEFAULT;
ALTER TABLE Staff
ALTER sex SET DEFAULT ‘F’;
Contoh:
• Hapus batasan/constraint dari tabel
PropertyForRent yang menetapkan bahwa tidak
diperbolehkan menangani lebih dari 100 unit
properti pada saat yang sama. Tambahkan
kolom baru untuk tabel Client.
ALTER TABLE PropertyForRent
DROP CONSTRAINT StaffNotHandlingTooMuch;
ALTER TABLE Client
ADD prefNoRooms PRooms;
Menghapus Tabel (DROP TABLE)
• Format penulisan :
DROP TABLE
DROP TABLE TableName [RESTRICT | CASCADE]
• Jika ditetapkan RESTRICT, maka operasi
penghapusan akan ditolak jika terdapat objek
lain yang terkait dengan objek yang akan
dihapus. Sedangkan CASCADE, operasi
penghapusan akan dilaksanakan dan seluruh
objek terkait akan dihapus juga.
Contoh:
• Hapus sebuah tabel dan seluruh baris
didalamnya
DROP TABLE PropertyForRent;