SEN2104 · 8. hafta · Midterm Exam

Vize tekrarı: DBMS, ER, ilişkisel model, cebir ve SQL

İlk yedi haftanın tek sayfalık özeti ve karışık vize soruları. Sınavdan önceki akşam buradan başla, eksik gördüğün konunun kendi haftasına dön.

schemainstancedata independenceACIDentity setweak entitymultivalued attributemapping cardinalitysuperkeycandidate keyforeign keyselectionprojectionnatural joindivisionGROUP BYHAVINGouter joinNOT INNOT EXISTS

Vizeye bir hafta kala ilk yedi haftanın tamamını bir kez daha görmek istersin. Bu sayfa her konunun sınavda soru olarak çıkan çekirdeğini tablolarla toplar, sonra karışık sorularla seni dener. Bir soruda takılırsan o konunun kendi haftasına dön. Vize genelde şu yapıdadır: birkaç tanım ve kavram sorusu, bir ER tasarımı ya da ER’den tablo çıkarma, birkaç ilişkisel cebir ifadesi ve ağırlıklı olarak SQL yazma.

Çalışma sırası önerisi:

Önce aşağıdaki özet tabloları oku, bilmediğin satırı işaretle.
Örnek veritabanının tablolarını önüne koy. Bütün sorular bunun üzerinde.
Soruları cevabına bakmadan çöz, sonra açıklamayı oku.
En çok puan kaybettiren dört tuzak: doğal birleşimin fazladan kolon eşitlemesi, NOT IN ve NULL, WHERE ile HAVING karışıklığı, COUNT(*) ile COUNT(kolon) farkı.

Örnek veritabanı

Dönem boyunca kullandığımız üniversite veritabanı. Soruların hepsi bu verilerle çözülüyor.

student

stu_id	name	dept_name	tot_cred
S1	Ada	CS	60
S2	Bora	CS	30
S3	Cem	EE	45
S4	Defne	MATH	90
S5	Ece	CS	15
S6	Firat	EE	0
S7	Gizem	NULL	10

instructor

inst_id	name	dept_name	salary
I1	Aksoy	CS	95000
I2	Demir	CS	80000
I3	Kaya	EE	85000
I4	Sahin	MATH	70000
I5	Celik	MATH	72000
I6	Yildiz	BIO	60000

course

course_id	title	dept_name	credits
CS101	Intro to Programming	CS	4
CS201	Data Structures	CS	4
CS305	Databases	CS	3
EE210	Circuits	EE	3
MA101	Calculus	MATH	4
MA202	Linear Algebra	MATH	3
BI100	Biology	BIO	3

takes (stu_id, course_id, grade): S1 CS101 A, S1 CS201 B, S1 CS305 A, S1 MA101 B, S2 CS101 B, S2 CS201 C, S3 EE210 A, S3 MA101 C, S3 CS101 NULL, S4 MA101 A, S4 MA202 A, S4 CS305 B, S5 CS101 F, S7 MA202 B. Toplam 14 satır.

department: CS (Galata, 500000), EE (Galata, 350000), MATH (Rumeli, 200000), BIO (Kadikoy, 150000). teaches: I1 CS101, I1 CS305, I2 CS201, I3 EE210, I4 MA101, I5 MA202.

Hafta 1: DBMS temelleri

Kavram	Bir cümlede
Şema (schema) / örnek (instance)	Şema tasarımdır, nadiren değişir. Örnek o anki satırlardır, sürekli değişir.
Üç seviye	external (view), logical (conceptual), physical (internal)
Fiziksel veri bağımsızlığı	Fiziksel düzen (index, dosya yapısı) değişir, mantıksal şema ve uygulama aynı kalır.
Mantıksal veri bağımsızlığı	Mantıksal şema değişir (tablo bölme), view’lar uygulamayı korur. Daha zordur.
DDL / DML	CREATE, ALTER, DROP yapıyı; SELECT, INSERT, UPDATE, DELETE veriyi değiştirir.
ACID	Atomicity, Consistency, Isolation, Durability

Hafta 2-3: ER modeli ve ilişkisel model

ER diyagramındaki her şeklin ilişkisel tablolarda bir karşılığı var. Vizede en sık çıkan soru “bu ER diyagramından kaç tablo çıkar, anahtarları ne?” sorusudur.

ER öğesi	Tabloya dönüşümü
Güçlü varlık (strong entity)	Kendi tablosu, kendi birincil anahtarı
Zayıf varlık (weak entity)	Kendi tablosu. PK = sahip varlığın PK’si + ayırt edici (discriminator). Sahibe FK.
M:N ilişki	Ayrı tablo. PK = iki tarafın PK’lerinin birleşimi. İlişki öznitelikleri buraya.
1:N ilişki	Ayrı tablo yok. “N” (çok) tarafına diğer tarafın PK’si FK olarak eklenir.
1:1 ilişki	Taraflardan birine (tercihen tam katılımlı olana) FK eklenir.
Bileşik öznitelik (composite)	Sadece yaprak parçalar kolon olur: first_name, last_name
Çok değerli öznitelik (multivalued)	Ayrı tablo: (sahip PK, değer). İkisi birlikte PK.
Türetilmiş öznitelik (derived)	Genelde saklanmaz, sorguyla hesaplanır (yaş, doğum tarihinden).

Anahtarlar:

Anahtar	Tanım
Süper anahtar (superkey)	Satırları tekil olarak ayırt eden herhangi bir kolon kümesi
Aday anahtar (candidate key)	Minimal süper anahtar: hiçbir kolonu atılamaz
Birincil anahtar (primary key)	Tasarımcının aday anahtarlardan seçtiği
Yabancı anahtar (foreign key)	Başka bir tablonun PK’sine referans veren kolon(lar). Referans bütünlüğü (referential integrity) sağlar.

TUZAK · örnek veri anahtarı ispatlamaz. Mevcut 6 hocanın isimleri farklı diye name anahtar olmaz. Anahtar, şemanın bütün olası örnekleri için geçerli bir kuraldır. Örnek veri sadece çürütebilir: instructor tablosunda CS iki kez geçtiği için dept_name kesinlikle anahtar değildir.

Hafta 4-5: İlişkisel cebir

Operatör	Sembol	Yaptığı	Satır sayısı
Seçim (selection)	$\sigma_p(R)$	koşulu sağlayan satırlar	en fazla R kadar
İzdüşüm (projection)	$\Pi_{A}(R)$	istenen kolonlar, tekrarlar silinir	en fazla R kadar
Birleşim, fark, kesişim	$\cup, -, \cap$	uyumlu (union-compatible) iki ilişki	işleme göre
Kartezyen çarpım	$R \times S$	her satır her satırla	R satır sayısı çarpı S satır sayısı
Yeniden adlandırma	$\rho_{x}(R)$	ilişkiye ya da kolonlara yeni ad	R ile aynı
Doğal birleşim (natural join)	$R \bowtie S$	aynı isimli tüm kolonları eşitler	0 ile çarpım arası
Sol dış birleşim (left outer join)	R ⟕ S	eşleşmeyen R satırları NULL ile kalır	en az R kadar
Bölme (division)	$R \div S$	S’nin tüm satırlarıyla eşleşenler	en fazla aday sayısı kadar
Gruplama (aggregation)	$_{G}\gamma_{F(A)}(R)$	gruplar ve toplu fonksiyonlar	grup sayısı

İki küçük örnek. Hiç ders almamış öğrenciler bir farkla bulunur:

$\Pi_{stu\_id}(student) - \Pi_{stu\_id}(takes) = \{S6\}$

CS101 alıp CS201 almamış öğrenciler de öyle:

$\Pi_{stu\_id}(\sigma_{course\_id='CS101'}(takes)) - \Pi_{stu\_id}(\sigma_{course\_id='CS201'}(takes)) = \{S3, S5\}$

CS101’i alanlar {S1, S2, S3, S5}, CS201’i alanlar {S1, S2}. Fark {S3, S5}. SQL’deki EXCEPT ile de aynı sonuç çıktı.

Doğal birleşim tuzağı

student ⋈ takes ⋈ course yazınca “her öğrenci, aldığı her ders” beklenir. Ama student ve course tablolarında dept_name kolonu ortak. Doğal birleşim onu da eşitler ve sadece öğrencinin kendi bölümünden aldığı dersler kalır:

stu_id	name	course_id	dept_name
S1	Ada	CS101	CS
S1	Ada	CS201	CS
S1	Ada	CS305	CS
S2	Bora	CS101	CS
S2	Bora	CS201	CS
S3	Cem	EE210	EE
S4	Defne	MA101	MATH
S4	Defne	MA202	MATH
S5	Ece	CS101	CS

14 kayıttan 9’u kaldı. Ada’nın MA101’i, Cem’in MA101 ve CS101’i, Defne’nin CS305’i ve Gizem’in kaydı düştü. SQLite’ta NATURAL JOIN 9, JOIN ... ON ile anahtar eşitleme 14 satır verdi.

Hafta 6-7: SQL

Bir SELECT sorgusunun mantıksal çalışma sırası, yazılış sırasından farklıdır. Bunu bilmek WHERE/HAVING ve alias hatalarını bitirir:

FROM / JOIN  →  WHERE  →  GROUP BY  →  HAVING  →  SELECT  →  DISTINCT  →  ORDER BY

Konu	Kural
`WHERE` vs `HAVING`	WHERE tek satıra, gruplamadan önce. HAVING gruba, aggregate üzerinde.
`COUNT(*)` vs `COUNT(kolon)`	`COUNT(*)` tüm satırları, `COUNT(kolon)` NULL olmayanları sayar. takes’te 14 ve 13 (S3’ün CS101 notu NULL).
NULL karşılaştırma	`x = NULL` hiçbir zaman TRUE değildir, UNKNOWN’dur. `IS NULL` kullan.
`NOT IN`	Alt sorgu bir NULL döndürürse sonuç boş olur.
`EXISTS`	Alt sorgu en az bir satır döndürüyorsa TRUE. NULL’dan etkilenmez.
Dış birleşim	Eşleşmeyen satırları kaybetmemek için `LEFT JOIN`, sayarken `COUNT(kolon)`
Küme işlemleri	`UNION`, `INTERSECT`, `EXCEPT` tekrarları siler. `UNION ALL` silmez.
“Her / hepsi”	Çift `NOT EXISTS` (SQL’de bölme operatörü yok)

Bölümlere göre ortalama maaş, örnek veriyle:

SELECT dept_name, AVG(salary) FROM instructor GROUP BY dept_name;

BIO  | 60000
CS   | 87500
EE   | 85000
MATH | 71000

HAVING AVG(salary) > 75000 eklenince sadece CS ve EE kalır.

TUZAK · NOT IN ve tek bir NULL. “Öğrencisi olmayan bölümler” için WHERE dept_name NOT IN (SELECT dept_name FROM student) yazarsan sonuç BIO değil, boş gelir. Gizem’in NULL bölümü alt sorguya girer ve her karşılaştırmayı UNKNOWN yapar. NOT EXISTS ile yaz ya da alt sorguya WHERE dept_name IS NOT NULL ekle.

İPUCU · soruyu cebire mi SQL’e mi çeviriyorsun? Önce Türkçe cümleyi parçala: “hangi tablo(lar)” → FROM ve birleşimler, “hangi koşul” → WHERE / σ, “ne gösterilecek” → SELECT / π, “her biri için” → GROUP BY / γ, “hepsini” → bölme ya da çift NOT EXISTS, “hiç … olmayan” → fark ya da NOT EXISTS.

Sık yapılan hatalar

Yanlış düşünce	Doğrusu
Örnekte tekrar yok, o hâlde anahtar	Örnek sadece “anahtar değil” diyebilir
1:N ilişki için ayrı tablo açılır	N tarafına FK eklenir, ayrı tablo gerekmez
Zayıf varlığın PK’si sadece discriminator	owner PK + discriminator
Çok değerli öznitelik tek kolonda virgülle tutulur	Ayrı tablo (owner PK, değer). 1NF bunu ister
`student ⋈ takes ⋈ course` tüm kayıtları verir	`dept_name` da eşitlenir: 14 değil 9 satır
$R \times S$ kolon sayısı çarpılır	Satır sayısı çarpılır, kolon sayısı toplanır
Aggregate koşulu WHERE’e yazılır	HAVING’e yazılır
`NOT IN` her zaman `NOT EXISTS` ile aynı	Alt sorguda NULL varsa NOT IN boş döner
`LEFT JOIN` + `COUNT(*)` ile 0 görünür	NULL’lu satır sayılır, 1 çıkar. `COUNT(kolon)` yaz

Kendini test et

Sınavdaki gibi İngilizce sorular. Önce kâğıt kalemle çöz, sonra cevabı aç.

Soru 1kolay

Which ACID property guarantees that concurrently running transactions do not see each other's intermediate (uncommitted) results?

Cevabı göster

Isolation

Yalıtım (isolation): her transaction sanki tek başına çalışıyormuş gibi davranır. Atomiklik yarım işi geri almak, tutarlılık kısıtların korunması, kalıcılık COMMIT edilen işin çökmeden sonra kalmasıdır.

Soru 2kolay

In Chen-style ER notation, what does a double ellipse represent?

Derived attribute
Multivalued attribute
Composite attribute
Key attribute

Cevabı göster

Multivalued attribute

Çift elips çok değerli (multivalued) öznitelik demektir, örneğin bir hocanın birden çok telefonu. Kesikli elips türetilmiş (derived), alt elipslere dallanan elips bileşik (composite), altı çizili isim anahtar özniteliktir.

Soru 3orta

section is a weak entity set identified by course through the identifying relationship sec_course. Its discriminator is sec_id. What is the primary key of the relation section?

Cevabı göster

(course_id, sec_id)

Zayıf varlığın kendi başına anahtarı yoktur. Birincil anahtarı = tanımlayan (owner) varlığın birincil anahtarı + ayırt edici (discriminator). Böylece CS101'in 1. şubesi ile MA101'in 1. şubesi ayrı satırlar olur, ama CS101 için ikinci bir 1. şube eklenemez. SQLite'ta ikinci ('CS101', 1) eklemesi UNIQUE hatası verdi.

Soru 4orta

An ER design has entity sets student, instructor, course; an M:N relationship takes between student and course with attribute grade; a 1:N relationship advisor (an instructor advises many students, a student has at most one advisor); and a multivalued attribute phone of instructor. Using the standard mapping with no redundant tables, how many relations do you get?

Cevabı göster

Üç güçlü varlık → 3 tablo (student, instructor, course). M:N takes → ayrı tablo takes(stu_id, course_id, grade). 1:N advisor ayrı tablo gerektirmez: N tarafına (student) advisor_id yabancı anahtarı eklenir. Çok değerli phone → inst_phone(inst_id, phone). Toplam 3 + 1 + 1 = 5. Şema SQLite'ta kurulup tablo sayısı sayıldı.

Soru 5orta

In the current instance of instructor, no two rows have the same name. Can we conclude that name is a candidate key of instructor?

Cevabı göster

Bir örnek (instance) anahtar olduğunu ispatlayamaz, sadece anahtar olmadığını gösterebilir. Anahtar, şemanın tüm geçerli örnekleri için geçerli bir kuraldır. Yarın ikinci bir 'Demir' işe girebilir. Tersine, dept_name için örnek yeterlidir: CS ve MATH ikişer kez geçtiği için kesinlikle anahtar değildir (SQLite GROUP BY ile doğrulandı).

Soru 6kolay

student has 7 tuples and 4 attributes; department has 4 tuples and 3 attributes. How many tuples does $student \times department$ have?

Cevabı göster

Kartezyen çarpımda her satır her satırla eşleşir: 7 · 4 = 28 satır. Kolon sayısı toplanır (4 + 3 = 7), satır sayısı çarpılır. dept_name iki tarafta da olduğu için sonuçta student.dept_name ve department.dept_name diye iki ayrı kolon olur. SQL CROSS JOIN ile de 28 çıktı.

Soru 7orta

Give the result of $\Pi_{name}(\sigma_{salary > 75000}(instructor))$ .

Cevabı göster

Aksoy, Demir, Kaya

Önce seçim: maaşı 75000'den büyük satırlar I1 Aksoy (95000), I2 Demir (80000), I3 Kaya (85000). Sahin 70000, Celik 72000, Yildiz 60000 elenir. Sonra sadece name kolonu alınır. SQL karşılığı SELECT name FROM instructor WHERE salary > 75000 aynı üç ismi verdi.

Soru 8zor

How many tuples does $student \bowtie takes \bowtie course$ (natural joins) return on the sample data?

Cevabı göster

Tuzak sorusu. student ile takes ortak kolon olarak sadece stu_id paylaşır: 14 satır. Ama bu sonuç ile course arasında ortak kolonlar course_id ve dept_name'dir. Doğal birleşim ikisini birden eşitler, yani sadece öğrencinin kendi bölümünden aldığı dersler kalır: Ada 3 (CS101, CS201, CS305; MA101 elenir), Bora 2, Cem 1 (EE210), Defne 2 (MA101, MA202; CS305 elenir), Ece 1. Cem'in CS101'i ve Gizem'in (dept_name NULL) dersi de düşer. Toplam 9. Tüm kayıtları istiyorsan JOIN ... ON ile sadece anahtarları eşitle (14 satır).

Soru 9zor

Write a relational algebra expression for the names of students who have taken every course offered by the CS department, and give the result.

Cevabı göster

Π_name( (Π_{stu_id,course_id}(takes) ÷ Π_course_id(σ_{dept_name='CS'}(course))) ⋈ student ) → Ada

"Her" kelimesi bölme (division) demektir. Bölünen: hangi öğrenci hangi dersi almış, $\Pi_{stu\_id, course\_id}(takes)$ . Bölen: CS dersleri {CS101, CS201, CS305}. Bölme, bu üçünün hepsiyle eşleşen stu_id'leri verir: sadece S1. Bora CS305'i, Ece CS201 ve CS305'i almadı. Sonra student ile birleştirip isim alınır: Ada. SQL'de aynı sorgu çift NOT EXISTS ile yazılır.

Soru 10orta

Write an SQL query that lists each department name and its average instructor salary, but only for departments whose average salary exceeds 75000. Give the result.

Cevabı göster

SELECT dept_name, AVG(salary) FROM instructor GROUP BY dept_name HAVING AVG(salary) > 75000; → (CS, 87500), (EE, 85000)

Gruplar: CS {95000, 80000} ort. 87500; EE {85000} ort. 85000; MATH {70000, 72000} ort. 71000; BIO {60000} ort. 60000. Toplu fonksiyon (aggregate) üzerindeki koşul WHERE'e değil HAVING'e yazılır, çünkü WHERE gruplamadan önce tek tek satırlara uygulanır. 75000'i geçenler CS ve EE.

Soru 11zor

What does the following query return on the sample data?

SELECT dept_name
FROM department
WHERE dept_name NOT IN (SELECT dept_name FROM student);

Cevabı göster

Empty result (no rows)

Beklenen cevap BIO gibi görünür, çünkü BIO bölümünde öğrenci yok. Ama alt sorgu Gizem'in NULL değerini de döndürür. 'BIO' NOT IN ('CS','EE','MATH',NULL) ifadesi 'BIO' <> NULL karşılaştırmasını içerir, bu da UNKNOWN'dur. AND zincirinde bir UNKNOWN olunca sonuç asla TRUE olmaz, satır gelmez. Düzeltme: alt sorguya WHERE dept_name IS NOT NULL eklemek ya da NOT EXISTS kullanmak. İkisi de SQLite'ta BIO döndürdü.

Soru 12orta

Write an SQL query that lists every student's name with the number of courses they have taken, including students who have taken none. Give the result.

Cevabı göster

SELECT s.name, COUNT(t.course_id) FROM student s LEFT JOIN takes t ON s.stu_id = t.stu_id GROUP BY s.stu_id, s.name; → Ada 4, Bora 2, Cem 3, Defne 3, Ece 1, Firat 0, Gizem 1

İç birleşim (inner join) hiç ders almamış Firat'ı kaybeder. LEFT JOIN onu NULL'larla birlikte tutar. Sayarken COUNT(t.course_id) kullanmak şart: NULL'u saymaz, Firat 0 çıkar. COUNT(*) yazsaydın Firat'ın NULL'lu satırı da sayılırdı ve 1 görünürdü (SQLite'ta denendi). Gruplamaya s.stu_id eklemek aynı isimli iki öğrenciyi ayırmak için iyi bir alışkanlık.

Doğrulama: Bütün sonuç tabloları ve cevaplar verify/SEN2104/08.mjs ile hesaplandı: SQL sorguları node:sqlite (SQLite 3.53) üzerinde, ilişkisel cebir ifadeleri lib.mjs içindeki JS küme işlemleriyle (sigma, pi, njoin, divide, minus) çalıştırıldı ve SQL karşılıklarıyla karşılaştırıldı. ER eşlemesi SQLite'ta tablo olarak kurulup kısıtları denendi.

Deftere not kartı

schema = tasarım, instance = o anki veri. Fiziksel bağımsızlık: index; mantıksal: tablo bölme + view ACID: Atomicity (hep/hiç), Consistency, Isolation (ara hâl görünmez), Durability (commit kalır) ER: çift elips = multivalued, kesikli = derived, çift dikdörtgen = weak entity, çift elmas = identifying rel. Weak entity PK = owner PK + discriminator M:N → ayrı tablo 1:N → N tarafına FK Multivalued → ayrı tablo (owner PK, değer) Composite → alt parçalar ayrı kolon Instance anahtarı ispatlamaz, sadece çürütür R × S: satır çarpılır, kolon toplanır σ satır süzer, π kolon seçer (tekrar siler) Natural join TÜM ortak isimli kolonları eşitler (student ⋈ takes ⋈ course → dept_name tuzağı) "her / hepsi" → ÷ (division) ya da çift NOT EXISTS WHERE gruplamadan önce satıra, HAVING gruptan sonra aggregate'e NOT IN (... NULL ...) → boş sonuç. NOT EXISTS ya da IS NOT NULL kullan LEFT JOIN + COUNT(kolon) → 0'lar görünür; COUNT(*) NULL satırı da sayar