Data definition language ddl
This presentation is the property of its rightful owner.
Sponsored Links
1 / 19

Data Definition Language (DDL) PowerPoint PPT Presentation


  • 109 Views
  • Uploaded on
  • Presentation posted in: General

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

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


Data definition language ddl

  • 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


Data definition language ddl

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


Data definition language ddl

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


Data definition language ddl

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


Data definition language ddl

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


  • Login