data definition language ddl
Download
Skip this Video
Download Presentation
Data Definition Language (DDL)

Loading in 2 Seconds...

play fullscreen
1 / 19

Data Definition Language (DDL) - PowerPoint PPT Presentation


  • 168 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Data Definition Language (DDL)' - isra


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
data definition language ddl

Data Definition Language (DDL)

Basisdata 2

T.Informatika UTM Ganjil 2012/2013

Fika Hastarita Rachman

pengantar ddl
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
Membuat basisdata baru
  • Sintaks SQL:

CREATE DATABASE db_name;

  • Contoh:

Buat basisdata baru ‘TENNIS’

CREATE DATABASE TENNIS;

Fika Hastarita Rachman

menghapus basisdata
Menghapus Basisdata
  • Sintaks SQL:

DROP DATABASE db_name;

  • Contoh:

Hapus basisdata ‘TENNIS’

DROP DATABASE TENNIS;

Fika Hastarita Rachman

basisdata tennis
Basisdata Tennis

Fika Hastarita Rachman

membuat tabel
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
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
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

slide9

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
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
Menghapus tabel
  • Sintaks SQL

DROP TABLE tbl_name ;

  • Contoh:

DROP TABLE COMMITTEE_MEMBERS;

DROP TABLE PLAYERS;

Fika Hastarita Rachman

mengubah nama tabel
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
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

latihan1
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

slide15

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

slide16

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

slide17

6. Hapuskankolom TYPE daritabel TEAMS

ALTER TABLE TEAMS

DROP COLUMN TYPE;

Fika Hastarita Rachman

tugas minggu depan 12 maret 2013
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

slide19

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 :

[email protected]

Subjek : Basdat2_kelas_namaNRP

Contoh : Basdat2_A_fika040111100567

Fika Hastarita Rachman

ad