1 / 19

Data Definition Language (DDL)

Data Definition Language (DDL). Basisdata 2 T.Informatika UTM Ganjil 2012/2013. 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

isra
Download Presentation

Data Definition Language (DDL)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

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

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

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

  5. Basisdata Tennis Fika Hastarita Rachman

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

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

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

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

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

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

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

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

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

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

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

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

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

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

More Related