Osnove podatkovnih baz - vaje 19.3.2020

ER diagrami

Naloga 1

Nariši ER diagram za podatkovno bazo traktorji! Popravi diagram tako, da je lahko v tabeli deli le en vnos istega tipa za istega lastnika in znamko!

Naloga 2

Nariši ER diagram za sledečo univerzitetno podatkovno bazo:

• Vsak profesor ima EMŠO, ime, starost, naziv in raziskovalno področje.
• Vsak projekt ima svojo številko, pokrovitelja, začetni in končni datum ter sredstva.
• Vsak podiplomski študent ima EMŠO, ime, starost in univerzitetni program.
• Vsak oddelek ima svojo številko, ime in pisarno.
• Vsak projekt upravlja natanko en profesor
• Na vsakem projektu dela vsaj en profesor.
• Profesorji lahko upravljajo in delajo na več projektih.
• Na vsakem projektu dela vsaj en podiplomski študent.
• Za vsak projekt, na katerem dela študent, je določen en profesor, ki ga nadzoruje (istega študenta lahko pri več različnih projektih nadzorujejo različni profesorji).
• Vsak profesor dela na vsaj enem oddelku, pri čemer za vsak oddelek beležimo delež delovnega časa profesorja.
• Vsak oddelek vodi natanko en profesor.
• Vsak podiplomski študent opravlja raziskovalno dejavnost na natanko enem oddelku.
• Podiplomski študentje imajo lahko enega starejšega kolega za svetovalca.

Naloga 3

Nariši ER diagram za sledečo letališčno podatkovno bazo:

• Vsako letalo ima registrsko številko in je nekega modela.
• Za vsak model letala hranimo njegovo številko, kapaciteto in težo.
• Zaposleni na letališču so letalski tehniki in kontrolorji letenja. Za vsakega hranimo EMŠO, ime, naslov, telefon in plačo.
• Vsak tehnik je specialist za enega ali več modelov letal. Za vsak model imamo lahko več specialistov.
• Vsak kontrolor mora prestati letni zdravniški pregled. Hranimo datum zadnjega pregleda.
• Za preverjanje stanja letal so predpisani periodični testi. Za vsak test hranimo njegovo številko, ime in maksimalno doseženo oceno.
• Vsakič, ko tehnik opravi test na nekem letalu, to zabeležimo. Za vsak tak dogodek hranimo datum, porabljen čas in doseženo oceno.

Ali lahko ER diagram priredimo tako, da lahko test na letalu opravi le tak tehnik, ki je specialist za njegov model?

CREATE TABLE model (
  stevilka TEXT PRIMARY KEY,
  kapaciteta INTEGER,
  teza INTEGER
);

CREATE TABLE letalo (
  registrska TEXT PRIMARY KEY,
  model TEXT NOT NULL REFERENCES model(stevilka)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  UNIQUE (registrska, model)
);

CREATE TABLE test (
  stevilka INTEGER PRIMARY KEY,
  ime TEXT,
  max_ocena INTEGER
);

CREATE TABLE zaposleni (
  emso TEXT PRIMARY KEY,
  ime TEXT,
  naslov TEXT,
  telefon TEXT,
  placa INTEGER
);

CREATE TABLE tehnik (
  emso TEXT PRIMARY KEY REFERENCES zaposleni(emso)
);

CREATE TABLE kontrolor (
  emso TEXT PRIMARY KEY REFERENCES zaposleni(emso),
  pregled DATE
);

CREATE TABLE specialist (
  tehnik TEXT REFERENCES tehnik(emso),
  model TEXT REFERENCES model(stevilka),
  PRIMARY KEY (tehnik, model)
);

CREATE TABLE kontrola (
  tehnik TEXT,
  registrska TEXT,
  model TEXT,
  test INTEGER REFERENCES test(stevilka),
  datum DATE,
  cas INTERVAL,
  ocena INTEGER,
  FOREIGN KEY (tehnik, model)
    REFERENCES specialist(tehnik, model),
  FOREIGN KEY (registrska, model)
    REFERENCES letalo(registrska, model),
  PRIMARY KEY (tehnik, registrska, model, test, datum)
);