İlişkisel model, anahtarlar ve ER'den tabloya

ER diyagramı kağıt üzerindeki tasarımdı. İlişkisel model (relational model) onun bilgisayardaki karşılığı: her şey tablo. Bu hafta iki şey öğreniyorsun. Birincisi terminoloji ve anahtarlar; ilişkisel cebir, SQL ve normalizasyon bu kavramların üzerine kurulu, “candidate key nedir” bilmeden 9. haftayı geçemezsin. İkincisi ER’den tabloya çevirme kuralları; vizede neredeyse her yıl “bu diyagramı ilişki şemalarına dönüştür” sorusu çıkar.

Altın kurallar:

1. Şema sabit, örnek değişken. Kısıtlar şemaya aittir.
2. Anahtar minimal olmalı. Fazladan öznitelik varsa süper anahtardır, aday değil.
3. FK başvurduğu satırın var olmasını şart koşar. Ya değer gerçek bir PK’ya eşittir ya NULL’dır.
4. M:N her zaman ayrı tablo. 1:N, N tarafına kolon olur.

Terminoloji

İlişkisel modelde veri tablolarda durur. Matematiksel adıyla tablo bir ilişkidir (relation), satır bir tuple, kolon bir özniteliktir (attribute). Kursta kullandığımız student ilişkisi:

• Domain: bir özniteliğin alabileceği değerler kümesi. tot_cred için negatif olmayan tam sayılar.
• Atomiklik (atomicity): her hücrede tek, bölünmez bir değer. Bir hücreye “555-0101, 555-0102” yazmak ilişkisel modeli bozar; bu, 1NF’nin (9. hafta) temelidir.
• NULL: değer bilinmiyor ya da yok. S7’nin bölümü henüz belli değil.
• Derece (degree): öznitelik sayısı, burada 4. Kardinalite: tuple sayısı, burada 7.

İlişki bir kümedir: satırların sırası yoktur ve iki aynı satır olamaz. Kolon sırası da teoride önemsizdir, çünkü öznitelikler isimle çağrılır.

Şema ve örnek

İlişki şeması (relation schema), ilişkinin yapısıdır: student(stu_id, name, dept_name, tot_cred). İlişki örneği (relation instance), belli bir andaki satırlardır. Programlamadaki karşılığı: şema değişken tipi, örnek değişkenin o anki değeri. Ada mezun olunca örnek değişir, şema aynı kalır.

Tüm kurs boyunca kullanacağımız şemalar:

department (dept_name, building, budget)
instructor (inst_id, name, dept_name -> department, salary)
student    (stu_id, name, dept_name -> department, tot_cred)
course     (course_id, title, dept_name -> department, credits)
teaches    (inst_id -> instructor, course_id -> course)
takes      (stu_id -> student, course_id -> course, grade)

Altı çizili yazamadığımız için birincil anahtar ilk kolon(lar); teaches ve takes için ilk iki kolon birlikte. Ok -> foreign key’in nereye baktığını gösterir. Şema diyagramında (schema diagram) her tablo bir kutu, PK altı çizili, FK’lar başvurdukları tablonun PK’sına okla bağlanır.

Anahtarlar

Süper anahtar (superkey): değerleri her satırı tekil belirleyen öznitelik kümesi. student için {stu_id}, {stu_id, name}, {stu_id, name, dept_name, tot_cred} hepsi süper anahtar.

Aday anahtar (candidate key): minimal süper anahtar, yani hiçbir özniteliği çıkarılamayan. {stu_id} aday; {stu_id, name} değil, çünkü name’i atınca hâlâ anahtar.

Birincil anahtar (primary key): tasarımcının aday anahtarlar arasından seçtiği. Bir ilişkinin birden fazla aday anahtarı olabilir (öğrenci numarası ve TC kimlik no); birini PK seçersin, diğerine UNIQUE koyarsın.

Yabancı anahtar (foreign key): bir ilişkideki öznitelik(ler)in, başka (ya da aynı) ilişkinin PK’sına başvurması. takes.stu_id → student.stu_id. Başvuran ilişki referencing, başvurulan referenced relation.

Örnekten anahtar okumak

Bir kümenin örnekte tekrar içerip içermediğine SQL ile bakabilirsin:

SELECT stu_id, course_id FROM takes
GROUP BY stu_id, course_id HAVING COUNT(*) > 1;   -- boş: tekrar yok
SELECT stu_id, grade FROM takes
GROUP BY stu_id, grade HAVING COUNT(*) > 1;       -- 3 grup tekrarlı

(ilk sorgu: satır yok)
S1 | A
S1 | B
S4 | A

TUZAK · örnek veri anahtar ispatlamaz. name şu anki 7 öğrencide tekrarsız, ama anahtar değil: ikinci bir “Ada” kaydolabilir. Örnek ancak olumsuz kanıt verir: dept_name tekrarlı (CS üç kez), o halde kesinlikle anahtar değil. Sınavda “this instance” ile “the schema” ayrımına dikkat.

Süper anahtar sayma

Bir öznitelik kümesi, en az bir aday anahtarı içeriyorsa süper anahtardır. R(A, B, C, D) için tek aday anahtar {A} ise geri kalan üç öznitelik istediğin gibi eklenir: $2^3 = 8$ süper anahtar. İki aday anahtar {A} ve {B, C} varsa içerme-dışarma uygula:

$|S_A \cup S_{BC}| = 2^3 + 2^2 - 2^1 = 8 + 4 - 2 = 10$

Son terim hem A’yı hem B,C’yi içerenler: {A,B,C} ve {A,B,C,D}.

Bütünlük kısıtları ve referans bütünlüğü

• Varlık bütünlüğü (entity integrity): PK değeri tekrar edemez ve NULL olamaz.
• Referans bütünlüğü (referential integrity): FK değeri ya NULL’dır ya da başvurulan tabloda gerçekten var olan bir PK değeridir.

Örnek veritabanında PRAGMA foreign_keys = ON ile denediklerimiz:

Başvurulan satır silinirken ne olacağını FK tanımında seçersin: varsayılan NO ACTION/RESTRICT reddeder, ON DELETE CASCADE başvuranları da siler, ON DELETE SET NULL FK’yı NULL yapar.

TUZAK · SQLite ve NULL birincil anahtar. Standart SQL’de PK asla NULL olamaz. SQLite tarihsel bir hata yüzünden TEXT PRIMARY KEY kolonuna NULL kabul ediyor (doğrulamada denendi); NOT NULL eklersen reddeder. Ayrıca SQLite’ta FK kontrolü varsayılan kapalıdır, PRAGMA foreign_keys = ON gerekir. PostgreSQL ve MySQL (InnoDB) bunları kendiliğinden uygular.

ER’den ilişkisel şemaya çevirme kuralları

Çözümlü örnek

Senaryo: department; instructor (bileşik name, çok değerli phone, bölüme N:1 total); student (bir danışman hoca, 1:N); course; section (course’un zayıf varlığı); takes (student ile section arasında M:N, grade); dean (department ile instructor arasında 1:1, ayrı tabloda tutulacak).

department (dept_name, building)
instructor (inst_id, first_name, last_name, dept_name -> department NOT NULL)
inst_phone (inst_id -> instructor, phone)                         PK: ikisi
student    (stu_id, name, advisor_id -> instructor)
course     (course_id, title)
section    (course_id -> course, sec_id, semester, year)          PK: dördü
takes      (stu_id -> student, course_id, sec_id, semester, year, grade)
           PK: ilk beşi ; FK (course_id, sec_id, semester, year) -> section
dean       (dept_name -> department, inst_id -> instructor UNIQUE) PK: dept_name

Toplam 8 tablo. Şemayı SQLite’ta kurup denedik: olmayan şubeye kayıt bileşik FK yüzünden reddedildi, aynı hocayı iki bölüme dekan yapmak UNIQUE yüzünden reddedildi, bölümsüz hoca NOT NULL yüzünden reddedildi, ders silinince şubeleri CASCADE ile gitti.

Dikkat: takes bir section’a bağlandığı için FK dört kolonlu bir bileşik anahtardır. FK, başvurduğu PK’nın tüm kolonlarını içermek zorunda; sadece course_id’yi section’a bağlayamazsın.

Specialization için üç yol

person(pid, name) → student(tot_cred), employee(salary):

Yöntem 2 overlapping’de aynı kişinin name’ini iki kez saklar, partial’da hiçbir alt kümede olmayan kişiyi saklayamaz.

Sık yapılan hatalar