Osnove podatkovnih baz - vaje 18.3.2021

ER diagrami

Naloga 1

Nariši ER diagram za podatkovno bazo traktorjev! 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 INTEGER PRIMARY KEY,
  kapaciteta INTEGER,
  teza INTEGER
);

CREATE TABLE letalo (
  registrska TEXT PRIMARY KEY,
  model INTEGER 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 kontrolor (
  emso TEXT PRIMARY KEY REFERENCES zaposleni(emso),
  pregled DATE
);

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

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

CREATE TABLE kontrola (
  letalo TEXT,
  model TEXT,
  tehnik TEXT, -- ne potrebujemo reference na tabelo tehnik - glej spodaj!
  test INTEGER REFERENCES test(stevilka),
  datum DATE,
  cas INTERVAL,
  ocena INTEGER,
  PRIMARY KEY (letalo, tehnik, test, datum),
  FOREIGN KEY (letalo, model) REFERENCES letalo(registrska, model),
  FOREIGN KEY (model, tehnik) REFERENCES specialist(model, tehnik)
);

Naloga 4

Nariši ER diagram za sledečo farmacevtsko bazo podatkov:

• Pacienti so določeni z EMŠOm, hranimo še ime, naslov in starost.
• Zdravniki so določeni z EMŠOm, hranimo še ime, specializacijo in leta izkušenj.
• Farmacevtska podjetja so določena z imenom, hranimo še telefonsko številko.
• Vsako zdravilo ima tržno ime in formulo. Tržno ime je edinstveno za podjetje, ki proizvaja zdravilo.
• Vsako lekarno določa ime, hranimo še naslov in telefonsko številko.
• Vsak pacient ima osebnega zdravnika. Vsak zdravnik ima vsaj enega pacienta.
• Vsaka lekarna prodaja različna zdravila, vsako s svojo ceno. Cena zdravila se lahko razlikuje od lekarne do lekarne.
• Vsak zdravnik je lahko zaposlen pri največ eni lekarni. Posamezna lekarna lahko zaposluje več zdravnikov.
• Zdravniki predpisujejo zdravila za paciente. Vsak zdravnik lahko predpiše več zdravil več pacientom, prav tako lahko vsak pacient dobi zdravila od več zdravnikov. Za vsak tak izdan recept hranimo datum in količino. Predpostavi, da hranimo samo zadnji izdani recept za posamezno kombinacijo zdravnika, pacienta in zdravila.
• Farmacevtska podjetja imajo lahko pogodbe z več lekarnami, prav tako ima lahko vsaka lekarna pogodbe z več farmacevtskimi podjetji. Za vsako pogodbo hranimo datuma začetka in konca ter besedilo pogodbe.
• Za vsako pogodbo morajo lekarne določiti zdravnika, ki nadzoruje spoštovanje pogodbe. V času veljavnosti pogodbe se lahko zvrsti več nadzornikov, vsak z določenim začetkom in koncem nadzorovanja. Vsak zdravnik lahko nadzoruje le eno veljavno pogodbo.

Pretvori diagram še v SQL, pri čemer upoštevaj naslednje:

• Ko pacienta izbrišemo iz baze, naj se izbrišejo tudi podatki o zdravilih, ki so mu bila predpisana.
• Zdravnika ne moremo izbrisati iz baze, dokler ima kakšnega pacienta (bodisi kot osebni zdravnik ali predpisovalec zdravila) oziroma nadzoruje kako pogodbo.
• Zakoni na področju zdravstva prepovedujejo farmacevtskim družbam spremeniti ime, dokler so vezana s pogodbo s kako lekarno. Za lekarne take omejitve ni. Obojih pa vseeno ne moremo izbrisati, dokler je pogodba veljavna. Predpostavi, da se pretečene pogodbe sproti brišejo iz baze.
• Ko se izbriše pogodba med lekarno in farmacevtskim podjetjem, naj se izbrišejo tudi podatki o njenih nadzornikih. Obstoječe pogodbe pod nobenim pogojem ni mogoče prenesti na drugo podjetje ali lekarno.
• Ko izbrišemo farmacevtsko podjetje, naj se izbrišejo tudi podatki o njihovih zdravilih. Ko podjetje spremeni ime, se zdravila ohranijo pod novim imenom.
• Zdravila ni mogoče izbrisati, če je trenutno predpisano kateremu pacientu. Predpostavi, da se po določenem času recepti brišejo.
• Če se zdravilo izbriše, naj se izbriše tudi iz inventarja lekarn, ki ga prodajajo. Če se mu spremeni ime, naj se to pozna tako v inventarjih kot v receptih.
• Ko izbrišemo lekarno, se pri njej zaposleni zdravniki označijo kot nezaposleni v lekarnah, zapisi o prodajanih zdravilih za to lekarno pa se izbrišejo. Če lekarna spremeni ime, se to mora poznati pri zaposlenih zdravnikih in prodajanih zdravilih.