Ana içeriğe geç
Kesit SQL
İleri · Kanat Havayolları

Veri değiştirme ve bütünlük: iptal, yeni rota, kısıtlar ve işlemler

Rezervasyon ve uçuş iptali, INSERT ... SELECT ile rota ekleme, CHECK kısıtı ve tek işlemde birden çok değişiklik.

Uygulamada aç, 8 alıştırmayı çözÜcretsiz, hesap gerekmez, kurulum yok.

Bu sayfa dersin okuma sürümüdür. Metin bir kutuyu çalıştırmanı ya da adıma geçmeni söylüyorsa dersi uygulamada aç.

Operasyonda veri değişir

Bir uçuş iptal olduğunda yalnızca uçuşun durumu değişmez; o uçuştaki tüm rezervasyonlar da iptal edilmelidir. İki değişiklikten biri yapılıp diğeri yapılamazsa sistem tutarsız kalır. Bu yüzden birlikte, tek bir işlem içinde yapılır.

BEGIN;
UPDATE hv_flights
SET status = 'cancelled', actual_dep = NULL, actual_arr = NULL, pax = 0
WHERE flight_id = 3000;
UPDATE hv_bookings
SET status = 'cancelled', seat = NULL
WHERE flight_id = 3000;
COMMIT;

Var olan veriden yeni satır üretmek

ADB → FRA rotası planlandı ama dönüşü yok. Dönüş rotasını elle yazmak yerine mevcut rotadan türetebilirsin:

INSERT INTO hv_routes (route_id, origin, dest, distance_km, block_min)
SELECT 46, dest, origin, distance_km, block_min
FROM hv_routes
WHERE origin = 'ADB' AND dest = 'FRA';
Önce SELECT, sonra UPDATEBir UPDATE veya DELETE'i çalıştırmadan önce aynı WHERE koşuluyla SELECT çalıştırıp kaç satırın etkileneceğini gör. Bu derste her alıştırma veritabanının temiz bir kopyasında çalışır.

Derinleş: Bütünlük denetimi: kısıtların yakalayamadıkları

Kısıtlar (PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE) hatalı verinin girişini engeller. Ama her iş kuralı bir kısıtla ifade edilemez, eski veriler kısıtlar eklenmeden önce girilmiş olabilir ve toplu yüklemeler bazen kısıtları geçici olarak kapatır. Bu yüzden veri ekipleri düzenli bir bütünlük karnesi çalıştırır. Her satır bir kuralı test eder ve 0 dışındaki her sayı bir alarmdır:

SELECT
  (SELECT COUNT(*) FROM hv_flights f JOIN hv_aircraft a ON a.tail_no = f.tail_no
    WHERE f.pax > a.seats) AS kapasite_asimi,
  (SELECT COUNT(*) FROM hv_bookings b JOIN hv_flights f ON f.flight_id = b.flight_id
    WHERE f.status = 'cancelled' AND b.status <> 'cancelled') AS iptal_tutarsiz,
  (SELECT COUNT(*) FROM hv_bookings b
    WHERE NOT EXISTS (SELECT 1 FROM hv_flights f WHERE f.flight_id = b.flight_id)) AS yetim_rezervasyon,
  (SELECT COUNT(*) FROM hv_flights WHERE actual_arr < actual_dep) AS ters_zaman,
  (SELECT COUNT(*) FROM (SELECT flight_id, seat FROM hv_bookings
                         WHERE seat IS NOT NULL
                         GROUP BY flight_id, seat HAVING COUNT(*) > 1) t) AS cift_koltuk;

Kanat'ın verisi dört kontrolden temiz geçiyor ama beşincide 20 uçuş–koltuk çifti çıkıyor. Örneğin 219 numaralı uçuşta 27B koltuğu iki ayrı yolcuya atanmış ve ikisi de “flown” görünüyor. UNIQUE (flight_id, seat) kısıtı olsaydı ikinci kayıt hiç girilemezdi.

UNIQUE ve NULL: SQL türleri ayrışır

İptal edilen rezervasyonların koltuğu NULL'dır. Standart SQL'de ve PostgreSQL, MySQL, Oracle, SQLite'ta birden çok NULL bir UNIQUE kısıtını ihlal etmez. SQL Server ise UNIQUE sütunda yalnızca bir NULL'a izin verir. Orada çözüm filtreli bir indekstir: CREATE UNIQUE INDEX ... WHERE seat IS NOT NULL.

Güvenli değişiklik akışı

  1. Say: Aynı WHERE koşuluyla SELECT COUNT(*) çalıştır, beklenen sayıyı yaz.
  2. Değiştir: İşlem (transaction) içinde UPDATE / DELETE çalıştır; etkilenen satır sayısı beklenenle aynı mı?
  3. Doğrula: Bütünlük karnesini yeniden çalıştır; sonra COMMIT, sorun varsa ROLLBACK.
  4. Tekrarlanabilirlik: Değişikliği ikinci kez çalıştırmak zarar vermemeli (idempotent). WHERE status <> 'cancelled' gibi bir koşul bunu sağlar.
Uçak değişimi gibi toplu güncellemelerBir uçağı bakıma alıp uçuşlarını başka bir uçağa aktarırken WHERE koşulunu hem uçağa hem tarih aralığına bağla. Yalnızca WHERE tail_no = ... yazmak, uçağın geçmişteki bütün uçuşlarını da değiştirir ve geçmiş raporlar bozulur.
Alıştırmalara geç (8)