5. Tablolar arası ilişkiler

5.1. Yabancı anahtarlar

İlişkisel bir veritabanı, ilişkilerle birbirine bağlanmış tabloların bir kümesidir. Yapısı aşağıdaki gibi olan önceki [BIBLIO] tablosundan esinlenerek bir örnek ele alalım:

İçerik örneği şu şekildeydi:

Örneğin, nom ve prénom gibi bu eserlerin farklı yazarları hakkında bilgi almak isteyebiliriz; doğum tarihleri, nationalité gibi. Böyle bir tablo oluşturalım. [DBBIBLIO / Tables] üzerine sağ tıklayıp [New Table] seçeneğini seçelim:

Şimdi şu [AUTEURS] tablosunu oluşturalım:

`id`	tablonun birincil anahtarı - bir satırı benzersiz bir şekilde tanımlamak için kullanılır
`nom`	yazarın adı
`prénom`	yazarın adı (varsa)
`date_naissance`	doğum tarihi
`nationalite`	menşe ülkesi

[AUTEURS] tablosunun içeriği şu şekilde olabilir:

[BIBLIO] tablosuna ve içeriğine geri dönelim:

Tablonun [AUTEUR] satırında, yazarın adını yazmaya gerek kalmaz. Bunun yerine, [AUTEURS] tablosundaki kimlik numarasını (id) yazmak daha uygundur. Öyleyse, [LIVRES] adlı yeni bir tablo oluşturalım. Bu tabloyu oluşturmak için, 3.14. paragrafta oluşturulan [biblio.sql] komut dosyasını kullanacağız. Bu komut dosyasını [Script Executive, Ctrl-F12] aracıyla yükleyelim:

BIBLIO tablo oluşturma komut dosyasını, LIVRES tablosunkine uyacak şekilde değiştiriyoruz:

CREATE TABLE LIVRES (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      INTEGER,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 8, 'POèME', '1978-01-01', 120, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 6, 'BD', '1990-11-10', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 3, 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 4, 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 1, 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 7, 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 6, 'BD', '1991-05-15', 70, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 5, 'Roman', '1991-09-01', 210, 'o');
INSERT INTO LIVRES (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 2, 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                                                     Benzersizlik Kısıtlamaları ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT UNQ1_LIVRES UNIQUE (TITRE);


/******************************************************************************/
/****                                                           Birincil Anahtarlar ****/
/******************************************************************************/

ALTER TABLE LIVRES ADD CONSTRAINT PK_LIVRES PRIMARY KEY (ID);

Sadece yapılan değişiklikleri açıklıyoruz:

4. satır: Tablodaki [AUTEUR] alanı bir tamsayı haline geliyor. Bu numara, daha önce oluşturulan [AUTEURS] tablosundaki yazarlardan birine atıfta bulunuyor.
11-19. satırlar: Yazarların isimleri, yazar numaralarıyla değiştirilmiştir.
29. satır: kısıtlamanın adı değiştirilmiştir. Daha önce [ UNQ1_BIBLIO ] olarak adlandırılmıştı. Artık [ UNQ1_LIVRES ] olarak adlandırılmaktadır. Bu isim herhangi bir şey olabilir. Ancak anlamlı olması tercih edilir. Burada bu çaba gösterilmemiştir. Bir veritabanındaki farklı alanlar ve farklı tablolar üzerindeki kısıtlamalar, farklı isimlerle birbirinden ayırt edilmelidir. Hatırlatmak gerekirse, 29. satırdaki kısıtlama, tablodaki bir başlığın benzersiz olmasını gerektirir.
36. satır: ID birincil anahtar üzerindeki kısıtlamanın adının değiştirilmesi.

Bu komut dosyasını çalıştıralım. Başarılı olursa, aşağıdaki gibi yeni [LIVRES] tablosunu elde ederiz:

Sonuçta bu değişiklikten bir kazanç elde edip etmediğimizi sorgulayabiliriz. Nitekim, [LIVRES] tablosunda yazarların adları yerine numaraları yer almaktadır. Binlerce yazar olduğu için, bir kitap ile yazarı arasındaki bağlantıyı kurmak zor görünmektedir. Neyse ki SQL dili bize yardım etmek için var. Bu dil, birden fazla tabloyu aynı anda sorgulamamıza olanak tanır. Örnek olarak, kütüphanedeki kitapların başlıklarını yazar bilgilerine bağlı olarak almamızı sağlayan SQL sorgusunu sunuyoruz. SQL (F12) düzenleyicisini kullanarak aşağıdaki SQL komutunu verelim:

SQL> select LIVRES.titre, AUTEURS.nom, AUTEURS.prenom,AUTEURS.date_naissance
FROM LIVRES inner join AUTEURS on LIVRES.AUTEUR=AUTEURS.ID
ORDER BY AUTEURS.nom asc

SQL emrini açıklamak için henüz çok erken. Bu konuya yakında tekrar değineceğiz. Bu sorgunun sonucu şöyledir:

Her kitap, yazarı ve ilgili bilgilerle doğru bir şekilde eşleştirilmiştir.

Şimdi yaptıklarımızı özetleyelim:

Farklı türde bilgileri içeren iki tablomuz var:
- AUTEURS tablosu yazarlar hakkındaki bilgileri içeriyor
- LIVRES tablosu ise kütüphane tarafından satın alınan kitaplarla ilgili bilgileri barındırıyor
bu tablolar birbiriyle bağlantılıdır. Bir kitabın mutlaka bir yazarı vardır. Hatta birden fazla yazarı da olabilir. Bu durum burada dikkate alınmamıştır. [LIVRES] tablosundaki [AUTEUR] satırı, [AUTEURS] tablosundaki bir satıra referans vermektedir. Buna ilişki denir.

[LIVRES] tablosunu [AUTEURS] tablosuna bağlayan ilişki, aslında bir tür kısıtlamadır: [LIVRES] tablosundaki bir satırın, her zaman [AUTEURS] tablosunda mevcut olan bir yazar numarasına sahip olması gerekir. Eğer [LIVRES] tablosundaki bir satırın yazar numarası [AUTEURS] tablosunda mevcut değilse, bir kitabın yazarını bulamayacağımız anormal bir durumla karşı karşıya kalırız.

SGBD, bu kısıtlamanın her zaman geçerli olduğunu doğrulayabilir. Bunun için [LIVRES] tablosuna bir kısıtlama ekleyeceğiz:

[LIVRES] tablosundaki [AUTEUR] satırını [AUTEURS] tablosundaki [ID] alanına bağlayan bağlantı, yabancı anahtar bağlantısı olarak adlandırılır. [LIVRES] tablosundaki [AUTEUR] sütunu, yukarıdaki sihirbazda "yabancı anahtar" veya "foreign key" olarak adlandırılır. Yabancı anahtar tanımlamak, [T1] tablosundaki [c1] sütunundaki değerin, [T2] tablosundaki [c2] sütununda mevcut olması gerektiği anlamına gelir. [c1] sütunu, [T2] tablosundaki [c2] sütununa göre T1 tablosunun "yabancı anahtarı" olarak adlandırılır. [c2] sütunu genellikle [T2] tablosunun birincil anahtarıdır, ancak bu zorunlu değildir.

[LIVRES] tablosunun [AUTEUR] yabancı anahtarını, [AUTEURS] tablosunun [ID] alanına şu şekilde tanımlıyoruz:

kısıtlama adı: serbest
"yabancı anahtar" sütunu, burada [LIVRES] tablosundaki [AUTEUR] sütunu
Yabancı anahtar tarafından referans verilen tablo. Burada, [LIVRES] tablosundaki [AUTEUR] sütununda, [AUTEURS] tablosundaki [ID] sütununda bir değer bulunmalıdır. Dolayısıyla, referans verilen tablo [AUTEURS]'tir.
Yabancı anahtar tarafından referans verilen sütun. Burada, [AUTEURS] tablosundaki [ID] sütunu.

Bu kısıtlamayı onaylıyoruz:

Her şey yolunda giderse, kısıtlama kabul edilir:

Bu yeni yabancı anahtar kısıtlamasının sonucu nedir? SQL (F12) düzenleyicisiyle, LIVRES tablosuna mevcut olmayan bir yazar numarasıyla bir satır eklemeyi deneyelim:

Yukarıdaki [INSERT] işlemi, mevcut olmayan bir yazar numarası (100) ile bir kitap eklemeye çalıştı. Sorgunun yürütülmesi başarısız oldu. İlgili hata mesajı, "FK_LIVRES_AUTEURS" yabancı anahtar kısıtlamasının ihlal edildiğini gösteriyor. Bu, az önce tanımladığımız kısıtlamadır.

5.2. İki tablo arasında birleştirme işlemleri

Yine [DBBIBLIO] veritabanında (ya da başka bir veritabanında fark etmez), TA ve TB adlı ve aşağıdaki gibi tanımlanmış iki test tablosu oluşturalım:

TA tablosu

- ID: TA tablosunun birincil anahtarı

- DATA: herhangi bir veri

TB tablosu

- ID: TB tablosunun birincil anahtarı

- IDTA: TB tablosunun yabancı anahtarı; bu anahtar, TA tablosundaki ID sütununa referans verir. Dolayısıyla, TA tablosundaki IDTA sütunundaki bir değer, TA tablosundaki ID sütununda da bulunmalıdır

- VALEUR: herhangi bir veri

SQL (F12) düzenleyicisinde, TA ve TB tablolarını aynı anda kullanan SQL komutlarını vereceğiz.

SQL>select * from TA,  TB

SQL komutu, FROM anahtar sözcüğünün ardından TA ve TB tablolarını kullanır. FROM ve TA işlemleri, TB işlemleri, TA tablosundaki her satırın TB tablosundaki her bir satırla eşleştirileceği yeni bir geçici tablonun oluşturulmasına neden olacaktır. Dolayısıyla, TA tablosunda NA satır ve TB tablosunda NB satır varsa, ortaya çıkan tablo NA x NB satır içerecektir. Yukarıdaki ekran görüntüsü bunu göstermektedir. Ayrıca, her satır her iki tablonun sütunlarını da içerir. [SELECT col1, col2, ... FROM ...] sıralamasında belirtilen coli sütunları, alınacak sütunları gösterir. Burada * anahtar sözcüğü, sonuç tablosundaki tüm sütunların istenildiğini belirtir. Bazen, önceki SQL emrinin sonuç tablosunun, TA ve TB tablolarının kartezyen çarpımı olduğu söylenir.

Yukarıda, TA tablosunun her satırı, TB tablosunun her satırıyla eşleştirilmiştir. Genel olarak, TA tablosundaki bir satıra, onunla ilişkisi olan TB tablosundaki satırları eşleştirmek istenir. Bu ilişki genellikle yabancı anahtar kısıtlaması şeklinde olur. Burada da durum böyledir. TA tablosundaki bir satıra, TB.IDTA=TA.ID ilişkisini karşılayan TB tablosundaki satırlar eşleştirilebilir. Bunu sorgulamanın birkaç yolu vardır:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA, TB where TA.ID=TB.IDTA

Önceki SQL emri, bir önceki emirle benzerdir ancak iki fark vardır:

TA x TB kartezyen çarpımının sonuç satırları, WHERE koşulu ile filtrelenir; bu koşul, TA tablosundaki bir satırı, TB tablosundaki, TB.IDTA=TA.ID ilişkisini karşılayan satırları
[T.col] sözdizimiyle yalnızca belirli sütunlar istenir; burada T bir tablonun adı, col ise bu tablodaki bir sütunun adıdır. Bu sözdizimi, iki tablonun aynı adlı sütunlara sahip olması durumunda ortaya çıkabilecek belirsizliği ortadan kaldırır. Böyle bir belirsizlik söz konusu olmadığında, sütunun ait olduğu tabloyu belirtmeden [col] sözdizimini kullanabiliriz.

Elde edilen sonuç şöyledir:

Aynı sonuç, aşağıdaki SQL komutuyla da elde edilebilir:

SQL>select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA inner join TB on TA.ID=TB.idta

[inner join] teriminden, iki tablo arasındaki bu tür işlemlere verilen "iç birleştirme" adı türemiştir. "Dış birleştirme"nin de var olduğunu göreceğiz. İç birleştirmede, sorgudaki tabloların sırası sonuç üzerinde herhangi bir etkiye sahip değildir: FROM TA inner join TB ifadesi, FROM TB inner join TA ile eşdeğerdir.

Önceki SQL sorgusu, TB tablosundaki en az bir satır tarafından referans verilen TA tablosundaki satırları sonuç tablosuna ekler. Bu nedenle, TA tablosundaki [3, data3] satırı, TB tablosundaki herhangi bir satır tarafından referanslanmadığı için sonuçta görünmez. TA tablosundaki tüm satırları, TB tablosundaki bir satır tarafından referans gösterilip gösterilmediğine bakılmaksızın almak isteyebiliriz. Bu durumda, iki tablo arasında bir dış birleştirme kullanılır:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TA left outer join TB on TA.ID= TB.IDTA

Burada bir “sol dış birleştirme” (left outer join) söz konusudur. “FROM TA left outer join TB” ifadesini anlamak için, sol tarafta TA tablosu, sağ tarafta ise TB tablosu bulunan bir birleştirme düşünmeliyiz. Sol taraftaki tablonun tüm satırları, birleşim ilişkisi doğrulanmamış olanlar da dahil olmak üzere, sol dış birleşim sonucuna dahil edilir. Bu birleşim ilişkisi, her ne kadar en yaygın durum bu olsa da, mutlaka bir yabancı anahtar kısıtlaması olmak zorunda değildir.

Aşağıdaki sırayla:

SQL> select TA.ID, TA.data, TB.valeur, TB.IDTA FROM TB left outer join TA on TA.ID= TB.IDTA

dış birleşmede "sol"da yer alan tablo TB'tir. Dolayısıyla, TB tablosundaki tüm satırlar sonuçta yer alacaktır:

İç birleşimden farklı olarak, tabloların sırası burada önemlidir. Ayrıca sağ dış birleşimler de mevcuttur:

FROM TA left outer join TB ifadesi, FROM TB right outer join TA ile eşdeğerdir: TA tablosu soldadır
FROM TB sol dış birleştirme TA, FROM TA sağ dış birleştirme TB ile eşdeğerdir: TB tablosu soldadır

Artık birden fazla tablonun eşzamanlı olarak işlenmesinin temellerini öğrendiğimize göre, veritabanlarında daha karmaşık sorgu işlemlerini ele alabiliriz.