Data Definition Language (DDL)

Data Definition Language (DDL) Basisdata 2 T.Informatika UTM Ganjil 2012/2013 Fika Hastarita Rachman

Pengantar DDL • Data Definition Language (DDL) menggambarkan struktur basisdata secara keseluruhan • Meliputi: • Membuat basisdata baru • Menghapus basisdata • Membuat tabel baru • Menghapus tabel • Mengubah nama tabel • Mengubah struktur tabel Fika Hastarita Rachman

Membuat basisdata baru • Sintaks SQL: CREATE DATABASE db_name; • Contoh: Buat basisdata baru ‘TENNIS’ CREATE DATABASE TENNIS; Fika Hastarita Rachman

Menghapus Basisdata • Sintaks SQL: DROP DATABASE db_name; • Contoh: Hapus basisdata ‘TENNIS’ DROP DATABASE TENNIS; Fika Hastarita Rachman

Basisdata Tennis Fika Hastarita Rachman

Membuat Tabel • Komponen minimal yang harus ada: • Nama tabel • Nama kolom • Tipe data • Nullity masing-masing kolom • PK • Sintaks SQL: CREATE TABLE tbl_name ( column_namedata_type [DEFAULTexpr] [column _constraint] , … [table_constraint] ); Fika Hastarita Rachman

Contoh create table • Buat tabel PLAYERS CREATE TABLE PLAYERS (PLAYERNO SMALLINT NOT NULL, NAME CHAR(15) NOT NULL, INITIALS CHAR(3) NOT NULL, BIRTH_DATE DATE , SEX CHAR(1) NOT NULL, JOINED SMALLINT NOT NULL, STREET CHAR(15) NOT NULL, HOUSENO CHAR(4) , POSTCODE CHAR(6) , TOWN CHAR(10) NOT NULL, PHONENO CHAR(10) , LEAGUENO CHAR(4) , PRIMARY KEY (PLAYERNO) ); • Buat tabel COMMITTEE_MEMBERS ? Fika Hastarita Rachman

Penerapan batasan dalam create table • Batasan Tipe Data PLAYERNO = NUMERIC (4,0) values >= 1 INITIALS = CHARACTER (3) DATE = DATE YEARNO = NUMERIC (4,0) SEXCODE = CHARACTER (1) set of values={'M','F'} MATCHENO = NUMERIC (4,0) values >= 1 NR_OF_SETS = NUMERIC (1,0) set of values ={0,1,2,3} PAYMENTNO = NUMERIC (8,0) values >= 1 AMOUNT = NUMERIC (7,2) POSITIONNAME = CHARACTER (20) set of values = {'Chairman','Secretary','Treasurer', 'General member'} Fika Hastarita Rachman

Batasan nilai kolom PLAYERS (JOINED) values >= 1970 PENALTIES (PAYMENT_DATE) values >='1970-01-01' PENALTIES (AMOUNT) values > 0.00 COMMITTEE_MEMBERS (BEGIN_DATE) values > '1990-01-01‘ • Batasan nilai baris PLAYERS: YEAR (BIRTH_DATE) <= JOINED COMMITTEE_MEMBERS: END_DATE >= BEGIN_DATE • Batasan referensi integritas FK TEAMS (PLAYERNO) ---> PLAYERS (PLAYERNO) MATCHES (TEAMNO) ---> TEAMS (TEAMNO) MATCHES (PLAYERNO) ---> PLAYERS (PLAYERNO) Fika Hastarita Rachman

Latihan • Buatlah sintaks tabel PLAYERS dengan rincian batasan tipe adta, nilai kolom, nilai baris, dan referensi FK (bila ada) • CREATE TABLE PLAYERS ( • PLAYERSNO SMALLINT PRIMARY KEY NOT NULL, • NAME CHAR(15) NOT NULL, • INITIALS CHAR(3) NOT NULL, • BIRTH_DAY DATE NULL, • SEX CHAR(1) NOT NULL); • ALTER TABLE PLAYERS ADD COLUMN POSTCODE CHAR(6) Fika Hastarita Rachman

Menghapus tabel • Sintaks SQL DROP TABLE tbl_name ; • Contoh: DROP TABLE COMMITTEE_MEMBERS; DROP TABLE PLAYERS; Fika Hastarita Rachman

Mengubah nama tabel • Sintaks SQL: RENAME TABLE tbl_name TO new_tbl_name; • Contoh: RENAME TABLE TEAMS TO GROUPS Fika Hastarita Rachman

Mengubah struktur tabel • Sintaks SQL ALTER TABLE tbl_namealter_specification; • 3 macam spesifikasi perubahan (alter_specification) • Menambah (add) kolom ADD COLUMN col_name column_definition; • Mengubah (change or modify) kolom CHANGE COLUMN old_col_name new_col_name column_definition; MODIFY COLUMN col_name column_definition; • Menghapus (drop) DROP COLUMN col_name; DROP PRIMARY KEY; DROP FOREIGN KEY fk_symbol; Fika Hastarita Rachman

Latihan 1. Tambahkan satu kolom dalam tabel TEAMS, bernama TYPE yang memberikan identifikasi tim pria dan wanita ALTER TABLE TEAMS ADD COLUMN TYPE CHAR (1); Fika Hastarita Rachman

2. Perbarui data dengan TEAMNO=2 sebagai tim pria (M) UPDATE TEAMS SET TYPE = 'M' WHERE TEAMNO = 2; 3. Tambahkan panjang karakter TOWN dari 10 menjadi 20 • Perintah SQL 1: ALTER TABLE PLAYERS CHANGE COLUMN TOWN TOWN CHAR (20) NOTNULL; • Perintah SQL 2: ALTER TABLE PLAYERS MODIFY COLUMN TOWN CHAR (20) NOT NULL; Fika Hastarita Rachman

4. Ubahtipe data kolom PLAYERNO dalamtabel PLAYERS dari INTEGER menjadi SMALLINT • Perintah SQL 1: ALTER TABLE PLAYERS CHANGE PLAYERNO PLAYERNO SMALLINT; • Perintah SQL 2: ALTER TABLE PLAYERS MODIFY PLAYERNO SMALLINT; 5. Pindahkanposisikolom TOWN dalamtabel PLAYERS keposisikolomkedua • Perintah SQL 1: ALTER TABLE PLAYERS CHANGE TOWN TOWN CHAR(20) NOT NULL AFTERPLAYERNO; • Perintah SQL 2: ALTER TABLE PLAYERS MODIFY TOWN CHAR(20) NOT NULL AFTER PLAYERNO; Fika Hastarita Rachman

6. Hapuskankolom TYPE daritabel TEAMS ALTER TABLE TEAMS DROP COLUMN TYPE; Fika Hastarita Rachman

Tugas Minggu Depan 12 Maret 2013 • Buatlahlahtabel TEAMS denganrincianbatasantipe data, nilaikolom, nilaibaris, danreferensi FK (bilaada)! • Hapuslahkelimatabel yang adadidalambasisdata TENNIS! • Ubahlahnamatabel PLAYERS menjadi TENNIS_PLAYERS dan COMMITTEE_MEMBERS menjadi MEMBERS! • Ubahlahnamakolom BIRTH_DATE yang adadidalamtabel TEAMS menjadi DATE_OF_BIRTH! Fika Hastarita Rachman

5. Ubahlahnamakolom POSITION yang adadidalamtabel COMMITTEE_MEMBERS menjadi COMMITTEE_POSITION! • UbahlahpanjangkarakterdarikolomCOMMITTEE_POSITION dari 20 menjadi 30 karakter! Dikumpulkan maks 12 maret 2013 jam 12.00 WIB via email : hastarita.tugas@gmail.com Subjek : Basdat2_kelas_namaNRP Contoh : Basdat2_A_fika040111100567 Fika Hastarita Rachman

Data Definition Language (DDL)