HR Şeması İle Uygulamalı SQL(DDL)

Merhaba arkadaşlar!

SQL'e ait son konularla devam ediyoruz. DML komutlarından sonra sıra DDL komutlarında. DDL'e geçmeden neden DDL komutlarına ihtiyaç duyuluyor kısaca bahsedelim:

Bildiğiniz gibi DDL-Data Definiton Language yani Veri Tanımlama Dili, veritabanı nesnelerini oluşturmak ve yönetmek maksadıyla kullanılır. Peki nedir bu veritabanı nesneleri:

• Tablolar: Verilerin saklandığı satır ve sütunlardan oluşan yapılar.
• Görüntüler: Tabloların kullanıcı ihtiyacına göre şekillendirilerek hayali tablolar oluşturmayla meydana gelen tablo benzeri yapılar. Kullanım kolaylığı için tercih edilirler.
• İndeksler: Verilere erişim hızınızı ve sorgu performansınızı artıran, verilerin bir bakıma adreslerini tutan yapılar.
• Eş Anlamlılar: Herhangi bir veritabanı nesnesine kullanım kolaylığı için farklı isimler verilebilir.
• Sekanslar: Özel durumlarda sayısal değerler üretmeniz gerekebilir. Bu durumda sekanslara ihtiyacınız olacak.

Şimdi bunlar veritabanı nesnelerimiz. Peki bu nesneleri nasıl tanımlayıp düzenleyeceğiz. Elbette DDL komutlarıyla. Bakalım temel DDL komutları neler:

• CREATE: İlgili nesneyi sıfırdan oluşturmak için kullanılır.
• ALTER: Var olan nesneyi yeniden şekillendirme ve düzenlemeye yarar.
• DROP: Nesneyi kaldırma amacıyla kullanılır.

Şimdi "Tablo" işlemleri üzerinden konuları anlamaya çalışalım:

Tablo Oluşturma

Tablo oluşturmadan önce elbette veritabanında tablonun amacını, kısıtlarını, ismini, boyutunu, diğer tablolarla ilişkisini v.s. durumları belirlemeniz gerekmektedir. Komutu kullanmak her ne kadar kolaysa da bundan sonraki işler; yani oluşturulan tablonun amaca uygun çalışması sizin için daha önemlidir. Örneğin NULL değer girilmemesi gereken bir sütuna bu kısıtı vermezseniz şirketi zarara uğratmanız hiç de zor olmaz. Yada müşterilerin telefon numaralarını sayısal girme kısıtını vermezseniz veri kirliliği ile karşı karşıya kalırsınız. Bunlar en basit ve en görünen durumlar. 

Şimdi isim, kısıt, boyut dedik ama bunlarında kendi içlerinde kuralları bulunuyor. Nasıl ki bir programlama dilinde değişkenlere isim verirken bazı durumları göz önünde bulunduruyorsak, veritabanı nesnelerine de isim verirken kafamıza göre davranamayız. Hem amacını açıklayan, hem okunur hem de aşağıdaki kurallara uyan bir isim seçeriz:

• İsimler harfler ile başlar, rakamlar ile başlayamaz.
• En az 1, en fazla 30 karakter ile nesne ismi girilebilir.
• Harfler ve rakamlar haricinde sadece _, $ ve # simgelerini kullanabilirsiniz. Diğerlerini kullanamazsınız.
• Ayrıcalıklı kelimeleri içermemelidir. Örneğin "alter" isminde bir tablo oluşturamazsınız.
• Bir kullanıcı farklı nesnelerine aynı isimler veremez.

Bu kurallara dikkat edilerek oluşturulan isimlerle tablonuzu oluşturursunuz. Tabi tablo oluşturma hakkınız varsa. Çünkü veritabanı yöneticileri; veritabanı kullanıcılarına farklı haklar tanırlar. Bunlardan birisi de tablo oluşturma iznidir. Bu izniniz yoksa tablo oluşturamazsınız.

Tablo oluştururken; sütunların ismini ve boyutunu da tahmini olarak biliyor olmalısınız. İlgili genel cümle aşağıdaki gibi olur:

CREATE TABLE [semaismi.]tabloismi

          (sütunismi sütunveritipi [DEFAULT ifadesi] [, diğer sütunlar...]);

Eğer oluşturacağınız tablo sizin şemanızda değilse, bir başka kullanıcının şemasında ise semaismi.tabloismi kullanımı gerekecektir. Ancak bu kullanıcının size kendi şemasında tablo oluşturmanıza izin vermesi de gerekmektedir. Örneğin SQL Developer'dan veritabanına SYSTEM kullanıcısı ile bağlanıyorum ve HR kullanıcısının tablolarına ulaşmam gerekiyor. Bunun için yazacağım sorgu şöyle olacaktır:

SELECT * FROM HR.countries;

Aksi halde "tablo veya görüntünün olmadığına" dair hata alırsınız. Burası tamam. Cümledeki diğer farklı kullanım olan DEFAULT ifadesi ne anlama geliyor? Bu da ilgili sütuna eğer değer girilmiyorsa, varsayılan olarak INSERT anında ne atanacağını bildireceğiniz kısımdır. Kullanmayabilirsiniz. Buraya vereceğiniz değerin sütunun veri tipi ile aynı olmasına özen göstermelisiniz. Şimdi öğrendiklerimizi görebilmek adına bir tablo oluşturalım:

CREATE TABLE yoneticiler

      (yoneticiID NUMBER(3) NOT NULL,

      yoneticiAdi CHAR(25),

      departmanID NUMBER(3),

      tarih DATE DEFAULT SYSDATE);

CREATE TABLE succeeded

İnceleyelim: Departmanları yöneten kişilerin ID'sinin, adının, yönettiği departmanın ID'sinin ve yönetmeye başladığı tarihin tutulduğu bir tablo tasarladık. Tabi şimdilik olması gereken pek çok kısıtı vermedik; çünkü bu kısıtların ne olduğuna henüz bakmadık. İlerledikçe şekillenecektir. Tablonun ismini "yoneticiler" olarak belirleyip sütunları parantezler arasında () tanımladık. Örneğin yoneticiID en fazla 3 dijit içerebilen bir NUMBER veri tipi değeridir. Bu kısma bir de basit bir kısıt olan NOT NULL kısıtını ekledik. Yani bu değer INSERT anında boş geçilemez! Bir de tarih sütununun tanımına bakalım: Burada da DATE veri tipinde bir sütun oluşturduk ve varsayılan olarak eğer INSERT anında değer girilmezse sistem tarihinin girilmesini sağladık. Şimdi bakalım tablomuz nasıl görünüyor:

DESCRIBE YONETICILER;

Name                           Null             Type

------------------------------ ---------------  -----------------

YONETICIID                     NOT NULL         NUMBER(3)

YONETICIADI                                     CHAR(25) 

DEPARTMANID                                     NUMBER(3)

TARIH                                           DATE             

4 rows selected

Tablonun oluştuğunu doğrulamış olduk. Bu işlemde 3 adet veri tipi tanımış olduk:

• NUMBER: Değişken uzunluklu sayısal veri. 
• CHAR: Sabit uzunluklu karakter verisi. Eğer sabit uzunluk vermek istemiyorsanız VARCHAR2(uzunluk) veri tipini kullanırsınız.
• DATE: Tarih-saat verileri. 

Bu veri tipleri şimdilik işimizi görecek. Bunların dışında veri tipleri de var ancak bu veri tipleri çok büyük boyutlarda veri saklanan tablolarda kullanılır. Bunlara değinmiyoruz.

Kısıtlar

Şimdi az önceki NOT NULL da dahil kısıtları inceleyelim. Tablo oluştururken veri girişleri, tablolar arasındaki bağlar, boş değer girme, birincil(primery) ve ikincil(foreign) anahtar oluşturma gibi kısıtlarla ilgilenmeniz gerekiyor. İlgileneceğimiz kısıtlar şöyle:

• NOT NULL: NULL değer içermeyecek sütunlar kısıtlanır.
• UNIQUE: Sütun boyunca bir değerin sadece bir kere kullanılacağı durumlarda kullanılır.
• PRIMARY KEY: Tabloya ait birincil anahtarı yani ID sütununu belirler.
• FOREIGN KEY: Eğer tablo bir başka tablo ile ilişkiliyse, diğer tabloya ait birincil anahtar bu tabloda ikincil anahtar olarak bu kısıtla belirlenir.
• CHECK: Tabloya kayıt girilirken dikkat edilecek kısıtlar burada bildirilir.

İlgili kısıtlar Oracle sunucusunda veri sözlüğü (data dictionary) kısmında görüntülenir. Veri sözlüğünün ne olduğunu Oracle Veritabanı Mimarisi yazılarından öğrenebilirsiniz. 

Kısıtlarınızı mutlaka tablo oluştururken vermek zorunda değilsiniz. Daha sonra da bu işlemleri sağlayabilirsiniz. Kısıtlarınıza isim vermek bu kısıtların amacını anlamanızda size daha çok yardımcı olabilir. Ancak isim vermek zorunda değilsiniz. Kısıtın nasıl tanımlanacağını az önceki SQL cümlesinde gördük. 

CREATE TABLE tablo ismi

      (sütunismi sütunveritipi [DEFAULT ifadesi] [CONSTRAINT kisitismi] [kisit]);

Şeklinde olur. Eğer kısıta isim verecekseniz CONTRAINT isim şeklinde kullanım gerekir ki bu opsiyoneldir.  Şimdi yukarıdaki tablo oluşturma komutunu bir de bu kısıtlarla deniyelim:

CREATE TABLE yonetici

      (yoneticiID NUMBER(3) NOT NULL CONSTRAINT yon_id_pk PRIMARY KEY,

      yoneticiAdi CHAR(25),

      departmanID NUMBER(3),

      tarih DATE DEFAULT SYSDATE,

      CONSTRAINT yon_dep_fk FOREIGN KEY (departmanID) REFERENCES departments(department_id),

      CONSTRAINT yon_depID_uk UNIQUE(departmanID));

CREATE TABLE succeeded

Bu SQL DDL cümlesini anlarsak kısıtları anladık sayılır. Kısıtları tanımlarken iki türlü tanımlama mevcuttur:

• Sütun seviyesinde tanımlama: yon_id_pk kısıtına bakarsanız sütunun tanımlandığı yerde tanımlanan bir kısıt olduğunu görürsünüz. Bu sütun seviyesinde bir tanımlamadır.
• Tablo seviyesinde tanımlama: Cümlenin sonuna yapılan kısıt tanımlarıdır: yon_depID_uk ve yon_dep_fk buna birer örnektir. 

Kısıtlara verilen isimlere dikkat ediniz: tabloismi_sütunismi_kisitismi şeklinde kullanırsanız ilerde baktığınızda kısıtın ne amaçla kullanıldığını daha rahat anlarsınız. Şimdi burada kullandığımız kısıtları tanıyalım:

• PRIMARY KEY: İlişkisel veritabanlarında her tablonun bir ID sütunu olmalıdır. Bu ID sütunu sayesinde diğer tablolarla bağlantı kurarsınız. ID sütunları tekil (unique) olmak zorundadır. Aksi olsaydı veri karmaşası yaşanırdı. ID değerleri bulundukları satırları temsil eden özel değerlerdir. Burada da yoneticiID'yi sütun seviyesinde bir kısıt tanımlayarak PRIMARY KEY olarak tanımladık. Şunu da belirtelim ki PRIMARY KEY kısıtına sahip olan bir sütun aynı zamanda UNIQUE ve NOT NULL kısıtına da sahiptir. Yani NULL değerler ve tekrar eden değerler girilemez. Burada ayrıca bu kısıtları belirttik ki; kullanım için örnek vermiş olalım.
• NOT NULL: Bu kısıtın ne olduğunu biliyoruz. İlgili sütuna ait değerlerin boş bırakılmasının önüne geçer. PRIMARY KEY sütunları NOT NULL olmaya zorlanırlar.
• UNIQUE: Bu kısıt ile bir sütunda bulunan satır değerleri sadece bir kere kullanılabilir demektir. Bu sayede tekrarlı kullanımın önüne geçilir. Örneğin kimlik numarası gibi benzersiz olmak zorunda olan bir sütunla ilgileniyorsanız bu sütunu UNIQUE olarak belirlemelisiniz.
• FOREIGN KEY: İki tablo arasındaki ilişki kurulurken anahtarlar kullanılır demiştik. İki anahtarımız var ve bunların birbirine bağlanmasıyla ilişkiler oluşturulur. Bu anahtarlardan PRIMARY KEY baz alınan tabloda bulunurken, FOREIGN KEY de ilişki kurulmak istenen diğer tabloda yer alır. FOREIGN KEY sütunu baz alınan tablodaki PRIMARY KEY sütununu takip etmek zorundadır. Örneğin PRIMARY KEY sütununda bulunmayan bir değeri tabloya FOREIGN KEY sütununa INSERT etmeye çalışırsanız hata alırsınız.Mesela oluşturduğumuz tablo ve departments tablosu arasındaki şu SQL cümlelerini bir inceleyelim:
  
  INSERT INTO yonetici (yoneticiID, yoneticiAdi, departmanID)
            VALUES (197, 'Rachel', 90);
  1 rows inserted
  
  INSERT INTO yonetici (yoneticiID, yoneticiAdi, departmanID)
            VALUES (198, 'Rachel', 9);
  Error report:
  SQL Error: ORA-02291: bütünlük kısıtlaması (HR.YON_DEP_FK) bozuldu - üst anahtar bulunamadı
  
  Açıklama: 1. sorguda problem yok. Çünkü departments tablosunda 90 nolu bir departmana ait ID var. Fakat 9 nolu departman yok. Bu da kısıtlamanın bozulmasına sebep oluyor. Yani 2. sorgunun eklemesi sağlanmıyor. Birinci sorguda tarih değeri INSERT edilmediği için DEFAULT kısmı devreye girerek o güne ait tarihi INSERT ediyor.

Şimdi FOREIGN KEY ile kullanılan diğer anahtar kelimelere bakalım:

• REFERENCES: Kullanımını yukarıda verdiğimiz bu anahtar kelime ile oluşturduğunuz tablo ile bir üst tablo arasındaki bağlantıyı sağlarsınız. Bu bağlantılar zayıf olabilir.
• Zayıf Bağlantı: Ana tablodaki bir değerin silinmesi ile çocuk tablodaki (foreign key olan tablo) bununla alakalı olan satırın silinmesi söz konusudur. Bu sayede veri tutarlılığı sağlanır. Örneğin departments tablosundaki 90 nolu departman silinir yada değiştirilirse, bizim INSERT ettiğimiz 'Rachel' kaydı ne olacak? Veri tutarlılığının sağlanması adına bu kayıt da aynı değişimlere uğramalıdır. Bu yüzden ON DELETE CASCADE ifadesi eklenerek sorgu aşağıdaki gibi yapılabilir.
  ...CONSTRAINT yon_dep_fk FOREIGN KEY (departmanID) REFERENCES departments(department_id) ON DELETE CASCADE,...
• Bire Bir Bağlantı: İlişkili olan iki tablo arasında eğer bu tür bir ilişki söz konusuysa, her kaydın diğer tabloda sadece ve sadece bir karşılığı olabilir. Bu da demek oluyor ki iki tablodaki kayıt sayısı aynıdır. Bu ilişki çok kullanılmaz.
• Bire Çok Bağlantı: İlişkili olan tablolar arasında bir kayda karşılık diğer tabloda birden çok kaydın bulunması durumudur. Örneğin bir departmanda birden çok işçinin çalışması durumu gibi... Bizim tablomuzdaki durum budur. ON DELETE CASCADE yoksa ve bir FOREIGN KEY kullanmışsanız bire çok bağlantıyı sağladınız demektir.
• Çoka Çok Bağlantı: Her iki tabloda da karşılıklı olarak bir kayda karşılık birden çok kayıt söz konusu ise bu bağlantı aktif demektir. Çok kullanılmazlar ve veritabanını karmaşıklaştırırlar. Tercih etmemek adına üçüncü bir bağlantı kullanılarak alternatif bir bağlantı oluşturulabilir.

Bir de ON DELETE CASCADE ile aynı amaca hizmet eden ve aynı şekilde kullanılan ON DELETE SET NULL ifadesi vardır ki bu da ana tabloda silinen değerin alt tablodaki foreign key değerini NULL olarak ayarlar.

CHECK Kısıtı: Oluşmasını istemediğiniz durumları belirtmek için bu kısıtı kullanırsınız. Örneğin bir çalışanın maaşının 0'dan küçük olamayacağını belirtmek isterseniz şu kısıtı eklersiniz:

..., salary  NUMBER(2)

     CONSTRAINT emp_salary_min 

            CHECK (salary > 0),...

Silinmeyen Kayıtlar?

Eğer böyle bir problemle karşılaşıyorsanız bu silmeye çalıştığınız kaydın diğer tabloda bir kayıtla ilişkili olduğunun göstergesidir. Silmeye çalıştığınız satır PRIMARY KEY olan yani ana tablodaki satırdır. Çünkü FOREIGN KEY olan yani çocuk tablodaki kaydı silmek isteseydiniz burada bir hata almazdınız. Deneyelim:

DELETE FROM departments

WHERE department_id=90;

Error report:

SQL Error: ORA-02292: bütünlük kısıtlaması (HR.EMP_DEPT_FK) bozuldu - oğul kayıdı bulundu

DELETE FROM yonetici

WHERE departmanID = 90;

1 rows deleted

Bir Tabloyu Silme:

DROP tabloismi; komutunu kullanmanız yeterlidir. Tabloya bağlı nesneler geçersiz kabul edilir. Nesnelere ait ilişkili ayrıcalıklar kaldırılır. 

DROP TABLE yonetici;

DROP TABLE yonetici succeeded.

Tabloyu Düzenleme:

ALTER TABLE komutu ile sağlanır. Sütun ekleme, var olan bir sütunun düzenini değiştirme, bir sütunu kaldırma, yeniden isimlendirme gibi temel işlemleri yapabilirsiniz. Hatta kendi tablonuzu diğer kullanıcıların değiştirmesini istemiyorsanız sadece okunabilir moda alabilirsiniz:

ALTER TABLE yonetici READ ONLY;

Eski moda döndürmek için:

ALTER TABLE yonetici READ WRITE;

Alt Sorgu İle Tablo Oluşturma:

Alt sorgudan dönen sütun değerleri ile tablo oluşturabilirsiniz. Bunun için şu söz dizimini kullanırsınız:

CREATE TABLE tabloismi

      [(sutunisimleri...)]

AS altsorgu;

Alt sorgudan dönen sütun sayısı ile sizin verdiğiniz sütun sayıları uyumlu olmalıdır. 

CREATE TABLE   bolum90

  AS
    SELECT  employee_id, last_name,

            hire_date
    FROM    employees
    WHERE   department_id = 90;

Sütun isimlerini bu sorguda olduğu gibi vermezseniz ana tablodaki sütun isimleri ile yeni tablonuz oluşturulur. 

Bu konu da böylelikle bitiyor. Herkese başarılar...