SQL: DDL

1. SQL: DDL Basis Data 09

2. 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.

3. Data Type (ISO SQL )

4. Integrity Enhancement Feature • Data yang dibutuhkan (Required data) • Batasan domain (Domain constraints) • Integritas entitas (Entity integrity) • Integritas referensial (Referential integrity) • Batasan enterprise (Enterprise constraints)

5. 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

6. 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’));

7. Lanjut.. • atau dieklarasikan secara eksplisit : CREATE DOMAIN DomainName [AS] dataType [DEFAULT defaultOption] [CHECK (searchCondition)] • Sehingga : CREATE DOMAIN SexType AS CHAR CHECK (VALUE IN (‘M’, ‘F’)); sex SexType NOT NULL

8. 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));

9. 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]

10. 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)

11. 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)

12. 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.

13. 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

14. 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

15. 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

16. 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)

17. 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))

18. 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

19. 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.

20. 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.

21. 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)] [,…] })

22. 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.

23. 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);

24. 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);

25. 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);

26. 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

27. 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’;

28. 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;

29. 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.

30. Contoh: • Hapus sebuah tabel dan seluruh baris didalamnya DROP TABLE PropertyForRent;