Увод у базе података. Модели података. Релациони модел (структурни део)

• Базе података, први пример: поједностављена евиденција студентске службе (резултати испита)
• Базе података, дефиниција.
  • Архитектура (физички или унутрашњи ниво, логички ниво или модел података, спољашњи ниво или подмодели).
  • Модел података (структурни део, манипулативни део, интегритетни део).
  • Историјски преглед модела података (хијерархијски, мрежни, релациони, објектно-оријентисани)
• Релациони модел података. Делови (релационог) модела:
  • Структурни део:

    Структурни део релационог модела

    концепти	математичка терминологија (релациони модел)	информатичка терминологија (ACCESS)
    ентитет (енгл. entity), објекат (енгл. object)	n-торка (енгл. tuple)	ред у табели (енгл. table row, datasheet row), слог (енгл. record)
    тип ентитета (енгл. entity type), тип објеката (енгл. object type)	релација (енгл. relation)	табела (енгл. table, datasheet)
    атрибут ентитета, својство ентитета (енгл. entity attribute)	атрибут релације (енгл. relation attribute)	колона у табели (енгл. table column, datasheet column), поље слога (енгл. field)
    однос између типова ентитета (енгл. relationship between entity types)	релација (енгл. relation)	табела (енгл. table, datasheet)

  • Пример реализације структурног дела у релационом моделу података: схема базе података Studenti2009

    STUDENTI(ime,prezime,indeks,smer,status)
    ISPITI(sifra_predmeta,indeks,put,datum,ocena,sifra_nastavnika)
    PREDMETI(sifra_predmeta,naziv,smer,statut)
    NASTAVNICI(sifra_nastavnika,ime,prezime,zvanje,katedra)
    

    

  • Манипулативни део: SQL искази (INSERT, SELECT, UPDATE, DELETE, тј. унос, претрага, ажурирање и брисање података)
  • Интегритетни део: општа и специфична правила интегритета.

Релациони модел (интегритетни део)

• Општа правила интегритета:
  • интегритет ентитета (примарни кључ)
  • референцијални интегритет (страни кључ).

  Примери нарушавања општих правила интегритета:

  • [Нарушен интегритет ентитета при уносу]: Унос слога са празном вредношћу примарног кључа (нпр. унос података о студенту без вредности за индекс).
  • [Нарушен интегритет ентитета при уносу]: Унос слога са већ постојећом вредношћу примарног кључа (нпр. унос података о студенту са вредношћу индекса која већ постоји у табели).
  • [Нарушен интегритет ентитета при ажурирању]: Слично, ажурирање слога при ком се дуплира или брише вредност примарног кључа (нпр. измена индекса студента тако да се вредност дуплира или брише).
  • [Нарушен референцијални интегритет при уносу]: У зависној табели је унет слог са са вредношћу страног кључа зависне табеле која не постоји као вредност примарног кључа односне табеле (нпр. у зависној табели Ispiti унети су подаци о полагању студента ког нема у односној табели Studenti).
  • [Нарушен референцијални интегритет при ажурирању]: У зависној табели је измењен слог тако да нова вредност страног кључа не постоји као вредност примарног кључа односне табеле (нпр. изменом се у зависној табели Ispiti налазе подаци о полагању студента ког нема у односној табели Studenti).
  • [Нарушен референцијални интегритет при брисању]: У односној табели је обрисан слог са вредношћу примарног кључа за коју постоји одговарајућа вредност страног кључа у зависној табели (нпр. брисање података о студенту из односне табеле Studenti при чему остаје евиденција о његовим полагањима испита у зависној табели Ispiti).
• Механизми за детекцију и спречавање нарушавања интегритета базе у ACCESS-у.

Релациони модел (манипулативни део). Увод у SQL (упити над једном табелом)

• SQL језик за рад са релационим базама података и његова примена у једном релационом систему.
• SELECT исказ. Упити над једном табелом.

  Неквалификовани упит	Квалификовани упит
  SELECT колоне FROM табеле	SELECT колоне FROM табеле WHERE услов рестрикције (ограничења);

• Примери:
  1. Имена и презимена свих студената у евиденцији, који су са смера Р (вредност се мора навести онако како је записана у бази, дакле, латиницом R).
  2. SQL као дигитрон и алиаси: Израчунати производ 11*24 и колону табеле са резултатом назвати proizvod.
  3. Имена и презимена свих студената у евиденцији.
  4. Сви подаци о студентима са смера Р (име, презиме, индекс, смер, статус):
     • са променом редоследа колона;
     • без промене редоследа и без навођења свих имена колона.
  5. Списак различитих смерова на којима су евидентирани студенти.
  6. За сва полагања испита исписати индекс студента који је полагао испит и одговарајућу шифру предмета.
• Куцање упита:
  1. Create / Query Design;
  2. клик на дугме Close новоотвореног прозора;
  3. десни клик на title bar новоотвореног прозора;
  4. клик на SQL view. (Ако имате стару верзију програма ACCESS у коме се величина фонта упита не може повећати у прозору SQL view, како бисте избегли могуће грешке (нпр, размаци у именима колона и табела), најбоље је упит откуцати у неком програму за куцање обичног текста какав је Notepad++, а онда га копирати у прозор SQL view ради тестирања.)
• Ток извршавања упита:
  1. FROM-део (избор табеле),
  2. WHERE-део (рестрикција = избор редова),
  3. SELECT-део (пројекција = избор колона)
• Резултат извршавања упита је поново табела!
• Пројекција
  • * (све колоне табеле)
  • DISTINCT (елиминација дупликата редова)
  • Именовани изрази и алиаси (AS)
• Основни типови у SQL-у употребљени у бази података Studenti2009:
  • текстуални тип (Text) и
  • нумерички тип (Number).

Изрази у SQL-у (бројеви и ниске)

• Аритметички изрази
  1. Целобројне и реалне константе (литерали). На пример:

     `2\text{ }-16\text{ }23.56\text{ }-47.39`

  2. Називи колона нумеричког типа. На пример: пут и оцена у бази података Studenti2009;
  3. Позиви функција (скаларних и агрегатних) чија је вредност број. На пример: Int(7 / 3);
  4. Ако су `A_1` и `A_2`аритметички изрази, онда су то и

     `A_1 + A_2` (збир целих и реалних бројева),
     `A_1 - A_2` (разлика целих и реалних бројева),
     `A_1 * A_2` (производ целих и реалних бројева),
     `A_1 \\ A_2` (количник при целобројном дељењу),
     `A_1 mod A_2` (остатак при целобројном дељењу),
     `A_1 // A_2` (количник при реалном дељењу),
     `A_1 \^ A_2` (степен),
     као и `(A_1)` и `(A_2)`.

  5. Ништа осим (A1)–(A4) није аритметички израз.
  Пример: 2*(ocena + LEN(indeks)).
• Ниске. Оператор дописивања (конкатенације) &
• Примери:
  1. Исписати имена и презимена студената у једној колони и то у формату:
     • име размак презиме (Хари Потер)
     • презиме размак име (Потер Хари)
     • презиме запета размак име (Потер, Хари)
• Скаларне функције за рад са нискама (LEFT, RIGHT, MID, LEN, TRIM).
• Примери:
  1. Исписати презимена студената и дужине презимена.
  2. Исписати прва три слова сваког имена студента.
  3. Исписати последња три слова сваког имена студента.
  4. Исписати имена и презимена студената који су уписали факултет 98. године.
  5. Исписати имена и презимена студената и њихове године уписа као четвороцифрене бројеве (1998).
• Оператор LIKE и џокер знаци (*, ?). Разлике у имплементацији стандардног SQL-а (%, _ )
• Примери:
  1. Студенти чије презиме почиње словом Т.
  2. Студенти који су уписали факултет 1998. године.
• Текстуални изрази
  1. Ниске над азбуком коју чине елементи карактерског скупа Unicode. На пример:

     brat Ђорђе Miško 125/95 R

  2. Називи колона текстуалног типа. На пример: ime, prezime, indeks, smer и status табеле Studenti у бази података Studenti2009;
  3. Позиви функција (скаларних и агрегатних) чија је вредност ниска. На пример: RIGHT(indeks, 2) и RIGHT(TRIM(indeks), 2) ;
  4. Ако су `T_1` и `T_2` текстуални изрази, онда су то и
     

     `T_1 & T_2`,
     као и `(T_1)` и `(T_2)`.

  5. Ништа осим (T1)–(T4) није текстуални израз.
  Пример: '19' & RIGHT(TRIM(indeks), 2).

Изрази у SQL-у (релацијски и логички). Спајање две табеле

• Релацијски оператори (=, <>, <, <=, >, >=). Поређења бројева и ниски. Релацијски изрази:
  1. Ако су `A_1` и `A_2` аритметички изрази, онда су

     `A_1 = A_2`,
     `A_1` <> `A_2`,
     `A_1` < `A_2`,
     `A_1`<= `A_2`,
     `A_1`> `A_2` и
     `A_1`>= `A_2`

     релацијски изрази.
  2. Ако су `T_1` и `T_2` текстуални изрази, онда су

     `T_1 = T_2`,
     `T_1`<> `T_2`,
     `T_1` < `T_2`,
     `T_1`<= `T_2`,
     `T_1`> `T_2` и
     `T_1`>= `T_2`

     релацијски изрази.
  3. Уопште, ако су `I_1` и `I_2` изрази истог типа, онда су

     `I_1 = I_2`,
     `I_1`<> `I_2`,
     `I_1` < `I_2`,
     `I_1`<= `I_2`,
     `I_1`> `I_2` и
     `I_1`>= `I_2`

     релацијски изрази.
• Примери:
  1. Индекси студената који су неки испит полагали бар два пута.
  2. Индекси студената који су неки испит полагали мање од 5 пута.
• Логички оператори (AND, OR, NOT)
• Логички оператор BETWEEN … AND
• Логички оператор IN и листа вредности (припадност скупу)
• Логички изрази:
  1. Логичке константе TRUE и FALSE су логички изрази (реализовани као цели бројеви -1 и 0).
  2. Имена колона које садрже логичке вредности (Yes/No у ACCESS-у) су логички изрази.
  3. Позиви функција чије су вредности логичке константе су логички изрази. На пример: IsNumeric(ocena) или IsDate(datum).
  4. Ако су x, y и z изрази истог типа, (аритметички или текстуални), онда је x BETWEEN y AND z логички израз. На пример:

     ocena BETWEEN 7 AND 9

  5. Ако су t и s текстуални изрази, онда је t LIKE s логички израз. На пример:

     indeks LIKE '*/98*'

  6. Ако је a израз и L листа вредности истог типа као a, онда је и a IN L логички израз. На пример:

     ocena IN (9, 10)

  7. Ако су `L_1` и `L_2` логички изрази, онда су то и

     `L_1` AND `L_2`,
     `L_1` OR `L_2`,
     NOT `L_1` и NOT `L_2`,
     као и `(L_1)` и `(L_2)`.

     На пример:

     smer = 'R' AND status = 'S'

  8. Ништа осим (L1)–(L8) није текстуални израз.
• Природно-језички запис негације оператора LIKE, BETWEEN … AND и IN:
  • t NOT LIKE s уместо NOT(t LIKE s). На пример:

    ocena NOT BETWEEN 7 AND 9

  • x NOT BETWEEN y AND z уместо NOT(x BETWEEN y AND z). На пример:

    indeks NOT LIKE '*/98*'

  • a NOT IN L уместо NOT(a IN L). На пример:

    ocena NOT IN (9, 10)

• Рестрикција је заправо конструкција WHERE логички_израз. Задаци:
  1. Индекси студената који су неки испит полагали бар два пута, а мање од 5 пута
     • коришћењем логичких оператора,
     • коришћењем оператора BETWEEN … AND,
     • коришћењем оператора IN.
  2. Шифре предмета из којих је бар један испит положен.
  3. Индекси студената и шифре предмета које су ти студенти полагали у фебруарском испитном року 1999. године;
• Спајање две табеле
  • Пример: Имена и презимена свих студената у евиденцији, који су неки испит положили са осмицом.
  • Алиаси за табеле (AS)
  • Ток извршавања упита:
    1. FROM-део (Декартов производ табела),
    2. WHERE-део (природно спајање и додатна ограничења, рестрикција = избор редова),
    3. SELECT-део (пројекција - избор колона)
  • Пример: Имена и презимена свих студената у евиденцији, који су неки испит положили код наставника са катедре Грифиндор (вредност се мора навести онако како је записана у бази, дакле, латиницом Grifindor).
• Упутство за I домаћи

  У оквиру самосталног рада код куће, студент треба да направи упите који исписују:

  1. имена и презимена наставника са катедре Грифиндор;
  2. Индекси студената који су полагали предмет са шифром 1A01 бар два пута;
  3. Шифре наставника код којих су студенти полагали предмет са шифром 1A01 или предмет са шифром 2B01.
  4. смерове на којима се слушају предмети чије име почиње са Историја.
  5. смерове на којима се слушају предмети чије име не почиње са Историја;
  6. индексе студената и шифре наставника код којих су студенти полагали испит у периоду фебруар-мај 1999. године;
  7. исписати називе предмета са смерова Р, Н i Г (односно R, N i G);

  Размотрити помоћу којих све оператора могу да се реализују решења. У случају да постоји више решења, исписати сва и упоредити резултате.

Спајање више табела. Подупити и оператор IN

• Спајање више табела
  • Пример: Имена и презимена свих студената у евиденцији, који су неки испит положили код наставника са катедре Грифиндор.
• Подупити (IN)
  • Пример: Имена и презимена свих студената у евиденцији, који су неки испит положили са осмицом.
  • Како бисмо проблем решавали 'ручно'?
  • Једна колона = листа вредности.
    Ако је резултат извршавања упита табела са једном колоном, то је заправо листа!
    Сваки оператор који може да се примени на листу (на пример, IN), може се применити и на упит чији је резултат само једна колона.
• Упутство за II домаћи

  У оквиру самосталног рада код куће, студент треба да направи упите који исписују:

  1. Исписати смер предмета који је полагао бар један ванредан студент (статус је В, тј. V).
  2. Имена и презимена свих могућих парова (различитих) студената са истог смера.
  3. Имена и презимена студената који су полагали бар један испит.
  4. Имена и презимена студената који су положили бар један испит.
• Задатак са осмицама (за размишљање)
  • Имена и презимена студената који имају бар једну осмицу.
  • Имена и презимена студената који имају бар једну оцену која није осмица.
  • Имена и презимена студената који су полагали бар један испит и немају ниједну осмицу.
  • Имена и презимена студената који су полагали бар један испит и на сваком испиту су добили осмицу.

Задатак са осмицама. Решење помоћу оператора IN

• Загревање:
  1. Имена и презимена студената који су полагали бар један испит
  2. Имена и презимена студената који су положили бар један испит
• Задатак са осмицама (случајеви)
  • Имена и презимена студената који имају бар једну осмицу.
  • Имена и презимена студената који имају бар једну оцену која није осмица.
  • Имена и презимена студената који су полагали бар један испит и немају ниједну осмицу.
  • Имена и презимена студената који су полагали бар један испит и на сваком испиту су добили осмицу.
• Упутство за III домаћи

  У оквиру самосталног рада код куће, студент треба да направи упите применом оператора IN који исписују:

  1. Називи предмета из кога је сваки студент који је полагао тај предмет добио оцену већу од 8.
  2. Имена и презимена наставника чији испит није успео да положи ниједан студент који је тај испит полагао.

Предикатски рачун I реда (са једнакошћу)

• Квантификатори `(\forall, \exists)`.
• Непразан скуп који је предмет посматрања називамо домен, а његове елементе објектима. Објекти се означавају променљивама ($x$, $y$, $z$, …). Ако променљиве означавају објекте одређеног домена, још се каже да променљиве узимају вредности из тог домена. Домен може бити цео космос или само неки неки одређени скуп (на пример, сви студенти који у овој школској години слушају предавања из предмета Базе података). Подскупови домена који окупљају објекте са заједничким особинама зваћемо категорије.
• Предикати (релацијски знаци) се користе:
  1. да се представи припадање неког објекта некој категорији (на пример, $Sx$, $x$ је студент) или
  2. да се представи некакав однос између више објеката (на пример, $Jxyz$, $x$ полаже $y$ код $z$; при томе је потребно додатно одређење којим категоријама припадају $x$, $y$ и $z$, тј. не подразумева се да је $x$ студент, $y$ предмет или испит, нити да је $z$ наставник, док се то експлицитно не наведе).
• Најважнији закони и правила извођења исказног и предикатског рачуна
• Златна правила:
  1. Квантификаторе не треба гурати напред (у смислу да су сви квантификатори наведени на почетку формуле), већ их уводити постепено, тек онда када су нам стварно потребни.
  2. Ако се квантификатор `\forall` односи на сложени исказ, онда је тај сложени исказ импликација (`\Rightarrow`). Ако се квантификатор `\exists` односи на сложени исказ, онда је тај сложени исказ конјункција (`\wedge`).
• Превођење формула са предикатског рачуна I реда на српски језик
  • Пример 1: Сваки човек је живо биће.
    • Предикати: $Cx$ ($x$ је човек), $Zx$ ($x$ је живо биће)
    • Формула: `(\forall x)(Cx \Rightarrow Zx)`
      (Сваки објекат (елемент домена), ако је човек, онда је и живо биће)
  • Пример 2: Неко живо биће је човек.
    • Предикати: $Cx$ ($x$ је човек), $Zx$ ($x$ је живо биће)
    • Формула: `(\exists x)(Zx \wedge Cx)`
      (Постоји објекат (елемент домена) који је живо биће и човек)
  • Пример 3: Свака библиотека поседује неку књигу.
    • Предикати: $Bx$ ($x$ је библиотека), $Kx$ ($x$ је књига), $Pxy$ ($x$ поседује $y$)
    • Формула: `(\forall x)(Bx \Rightarrow (\exists y)(Kx \wedge Pxy)`
      (За сваки објекат, ако је он библиотека, онда постоји објекат који је књига, тако да та библиотека поседује ту књигу)

Предикатски рачун I реда (са једнакошћу). Наставак

• Превођење формула са српског језика у формулу предикатског рачуна I реда
  • Пример 4: Имена и презимена студената са смера Р ('R').
    • Решење 1:
      • Предикат: $\mathit{Studenti}\left(x\text{,}y\text{,}z\text{,}u\text{,}v\right)$, ($x$ и $y$ су име и презиме студента са бројем индекса $z$, који студира на смеру $u$ са статусом $v$)
      • Тражени скуп може да се опише као скуп уређених парова облика $\left(\mathit{neko\_ime}\text{,}\mathit{neko\_prezime}\right)$ таквих да за сваки пар постоји $\mathit{neki\_indeks}$ и постоји $\mathit{neki\_status}$, при чему су $\mathit{neko\_ime}$, $\mathit{neko\_prezime}$, $\mathit{neki\_indeks}$, смер 'R' и $\mathit{neki\_status}$ у релацији $\mathit{Studenti}$:

        `{(ii "neko_ime", ii "neko_prezime") | (\exists ii "neki_indeks") (\exists ii "neki_status")`
        `ii Studenti(ii "neko_ime", ii "neko_prezime", ii "neki_indeks", 'R', ii "neki_status")}`

    • Претходна нотација је компликована. Стога уместо да се студент посматра као веза (релација $\mathit{Studenti}$) између више простих објеката (име, презиме, индекс, смер, статус), третирамо га као један сложени објекат са атрибутима, при чему уводимо нови предикат $Sx$ ($x$ је студент), а атрибуте студента $x$ означавамо са `x.ime`, `x.prezime`, `x.ii "indeks"`, `x.smer` и `x.status`.
    • Решење 2: скуп уређених парова облика `(neko_ime, neko_prezime)` таквих да за сваки пар постоји неки студент $x$ чије је име и презиме у ознаци (`x.ime`, `x.prezime`) једнако том пару, и $x$ је са смера R.

      `{(ii "neko_ime", ii "neko_prezime") | (\exists x) (Sx \wedge x.ime = ii "neko_ime" \wedge x.prezime = ii "neko_prezime" \wedge ii x.smer='R')}`

    • Решење 3: Ако уведемо договор да се претходно решење краће записује са

      `{(x.ime, x.prezime) | (\exists x)(Sx \wedge x.smer='R')}`,

      нови запис се непосредно преводи у SQL:
      

      SELECT x.ime, x.prezime FROM studenti AS x WHERE x.smer='R';

      односно, ако изоставимо алиас $x$ (подразумевајући да знамо да се имена атрибута односе на редове табеле Studenti):

      SELECT ime, prezime FROM studenti WHERE smer='R';

  • Пример 5: Имена и презимена студената који имају бар једну осмицу.
    • Предикати:
      1. $Sx$ ($x$ је студент). Атрибути: $x$.ime, $x$.prezime, $x$.indeks, $x$.smer, $x$.status
      2. $Ix$ ($x$ је испит). Атрибути: $x$.sifra_predmeta, $x$.indeks, $x$.datum, $x$.ocena, $x$.sifra_nastavnika, $x$.put
    • T1: Име и презиме студента $x$ таквог да постоји испит $y$ који је полагао студент $x$ и на коме је добио оцену 8.
    • T2: `{(x.ime, x.prezime) | (\exists x)(Sx \wedge (\exists y)(Iy \wedge y.ii "indeks" = x.ii "indeks" \wedge y.ocena = 8))}`
• Упутство за IV домаћи:

  Реченице претходног домаћег (II), превести у формуле предикатског рачуна I реда, користећи предикате:
  $Sx$ ($x$ је студент) и $Ix$ ($x$ је испит).

Задатак са осмицама. Решење помоћу оператора EXISTS

• Загревање:
  1. Имена и презимена студената који су полагали бар један испит
  2. Имена и презимена студената који су положили бар један испит
• Задатак са осмицама (случајеви)
  • Имена и презимена студената који имају бар једну осмицу.
  • Имена и презимена студената који имају бар једну оцену која није осмица.
  • Имена и презимена студената који су полагали бар један испит и немају ниједну осмицу.
  • Имена и презимена студената који су полагали бар један испит и на сваком испиту су добили осмицу.
• Корелисани подупити
• Упутство за V домаћи

  У оквиру самосталног рада код куће, студент треба да направи упите применом оператора EXISTS који исписују:

  1. Називи предмета из кога је сваки студент који је положио тај предмет добио оцену већу од 8.
  2. Имена и презимена наставника чији испит није успео да положи ниједан студент који је тај испит полагао.

Скаларне и агрегатне функције

• Скаларне функције (улаз је скаларна вредност: ниска, број или датум)
  • Скаларне функције за рад са нискама (LEFT, RIGHT, MID, LEN, TRIM).
  • Скаларне функције за рад са датумом (YEAR, MONTH, DAY, DATE, DATEDIFF)
  • Скаларна функција Switch анализира парове (логички израз, придружена вредност) и враћа као резултат ону придружену вредност која одговара првом логичком изразу који је тачан. Switch се заправо понаша као if-наредба у Python-у.
  • Пример: исписати имена и презимена студената и пун назив њиховог статуса (R - редован, V - ванредан, S - самофинансирајући).

    SELECT ime, prezime, Switch(
              status = 'R', 'redovan',
              status = 'V', 'vanredan',
              status = 'S', 'samofinansirajući'
    ) AS nazivStatusa
    FROM studenti;
    

    Позив функције Switch у овом примеру има исти ефекат као позив следеће функције у Python-у:

    def switch(status):
        if status == 'R':
            return 'redovan'
        elif status == 'V':
            return 'vanredan'
        elif status == 'S':
            return 'samofinansirajući'
    

    Стандард SQL-а а не познаје функцију Switch, уместо ње дефинише изразе CASE-WHEN-THEN-ELSE који имају исту сврху, али нису имплементирани у MS ACCESS-у.
• Агрегатне функције (улаз је подскуп скаларних вредности једне колоне): COUNT, SUM, AVG, MIN, MAX
• Примери:
  1. Одредити највећу и најмању оцену на свим полагањима испита, као и укупан број појединачних полагања.
  2. Одредити највећу и најмању оцену на свим полагањима испита, као и укупан број појединачних полагања, али само оних која су одржана 18.09.1999.
  3. Одредити просек студента са индексом 103/98:
     • користећи SUM и COUNT;
     • користећи AVG.
• Груписање (GROUP BY, HAVING).
• Сортирање редова упита: ORDER BY колона (ASC | DESC), колона (ASC | DESC), ...
• Примери:
  1. За студенте који су положили бар један испит исписати њихов индекс и просечну оцену.
  2. За студенте који су положили бар један испит исписати њихово име, презиме и просечну оцену.
  3. За студенте који су положили бар један испит и имају просечну оцену бар 8.50 исписати њихово име, презиме и просечну оцену.
  4. За студенте који су положили бар један испит исписати њихово име, презиме и просечну оцену и то сортирано опадајуће по просеку, а потом растуће по презимену и имену.
• Упутство за VI домаћи

  У оквиру самосталног рада код куће, студент треба да направи упите који исписују:

  1. Назив и шифру предмета, као и просечну оцену на положеним испитима за сваки предмет понаособ.
  2. Исписати смерове на којима студирају студенти заједно са бројем студената по смеру. Резултат сортирати опадајуће по броју студената, а потом по називу смера.
  3. Исписати статусе студената заједно са бројем студената по статусу. Резултат сортирати опадајуће по броју студената, а потом по називу статуса.
  4. Име, презиме и шифру наставника и просечну оцену на свим положеним испитима код тог наставника за сваког наставника понаособ.
  5. Исписати за сваки предмет његов назив као и највећи број полагања тог предмета од стране неког студента.

Пројектовање база података

• Модел ентитета и односа (енгл. Entity Relationship Model, скр. ERM). Нотација:
  
• Пример 1: истраживачи на пројекту
  
• Пример 2: истраживачи на пројекту (друга верзија)
  
• Пример 3: евиденција полагања испита (база Studenti2009)
  
• Пример 4: евиденција тв куповине (база TVSHOP-2020)
  

Креирање базе података и њених табела. Типови у SQL-у. Спецификација примарног и страног кључа

• Исказ CREATE TABLE
  • Типови у SQL-у:
    • INTEGER
    • REAL
    • CHAR
    • NUMBER
    • DATE
  • Преглед типова колона у Access-у (Table > Design View) указује да се набројани типови на крају своде на неки од типова Text, Number, Date/Time, чији се подтипови додатно спецификују у пољу Field Size (на пример, за тип Number подтипови су Integer, Long Integer, Single, Double, Decimal; за тип Date/Time подтипови су General Date, Long Date, Medium Date, Short Date, Long Time, Medium Time, Short Time).
• Креирање базе TVSHOP (упутство, TVSHOP-2020.accdb)

  RACUN(IDRacuna, Banka, Stanje)
  KUPAC(IDKupca, Ime, Prezima, DatRodj, Ulica, PBroj, Mesto, IDRacuna)
  DOBAVLJAC(IDFirme, Firma)
  PROIZVOD(IDProizvoda, Naziv,Cena, IDFirme)
  NARUCITI(IDKupca, IDProizvoda, Datum, Kolicina)

  

• Водити рачуна о:
  1. локалним подешавањима (Control Panel > Regional Settings), посебно о уносу датума (Regional Settings > Customize > Date). На пример, mm/dd/yyyy, dd.mm.yyyy, yyyy-mm-dd
  2. нарушавању правила референцијалног интегритета (редослед креирања и попуњавања табела је битан!)
• Задаци за самосталан рад и вежбу за предстојећи практикум

  У оквиру самосталног рада код куће, студент треба да направи упите који исписују:

  1. Имена и презимена купаца из места Mracni dol.
  2. Називе производа чија је цена мања од 100.
  3. Имена и презимена купаца чије је стање на рачуну између 500 и 1000.
  4. Називе производа које испоручује фирма Del-boj.
  5. Имена и презимена купца и назив нарученог производа за сваку наруџбину.
  6. Назив нарученог производа и вредност наруџбине за сваку наруџбину. (Вредност наруџбине је износ који купац мора да плати да би добио наручени производ).
  7. Имена и презимена купаца који су наручили бар један производ добављача Pekam co..
  8. Називе производа које није поручио ниједан купац са стањем рачуна већим од 1000.
  9. Називе добављача чији сваки производ има цену већу од 30.
  10. Називе добављача чији ниједан производ није поручен од стране купца из места Kraj sveta.
  11. Имена и презимена купаца који су наручили најскупљи производ.
  12. За сваки производ исписати његов назив и укупан број његових поруџбина.
  13. За сваког купца исписати његово име и презиме и укупну вредност његових поруџбина. (Укупна вредност поруџбина једног купца је укупан износ који тај купац мора да плати да би добио све производе које је наручио).
  14. Називи добављача таквих да је сваки производ који испоручују наручен у укупној вредности већој од 2000. (Укупна вредност у којој је наручен неки производ је збир вредности свих његових наруџбина.)

Рад са датумима типа DATE.

• Рад са подацима типа DATE (#mm/dd/yyyy#). Скаларне функције за рад са датумом:
  • YEAR, вредност године у задатом датуму;
  • MONTH, редни број месеца (1–12) у задатом датуму;
  • DAY, редни број дана (1–31) у задатом датуму;
  • DATE, данашњи датум;
  • DATEDIFF, разлика два задата датума (други и трећи параметар) изражена у некој временској јединици (први параметар). Ако је први параметар 'yyyy', временска јединица је година.
  Пример: исписати за сваког купца његово име, презиме и колико има година (број година је разлика данашњег датума и датума рођења купца, изражена у годинама).

  SELECT ime, prezime,
  DATEDIFF('yyyy', DatRodj, DATE()) AS godine_kupca
  FROM Kupac;

• Скаларна функција за форматирање излаза FORMAT (d, m, y, #, 0, %)
• Скаларна функција за заокруживање броја на одређен број децимала ROUND
• UNION
• Задаци за самосталан рад и вежбу за предстојећи практикум

  У оквиру самосталног рада код куће, студент треба да направи упите који исписују:

  1. Називе производа и њихове цене сортиране од најскупљих ка најјефтинијим производима.
  2. Називе добављача чији су производи наручени у мају 2001. године.
  3. Имена и презимена купаца који су све наруџбине наручили после 31. маја 2001. године, сортирана растуће најпре по презименима, а потом по именима.
  4. Имена и презимена купаца чије све наруџбине испоручује добављач Del-boj или чије све наруџбине имају вредност бар 1000.

Искази за унос, ажурирање и брисање редова у табели

• INSERT исказ (SQL исказ за унос реда у табелу базе података)

  Синтакса	INSERT INTO табела VALUES (листа_вредности_колона);	Ако је исказ успешно извршен, не може се поново извршити јер се нарушава интегритет ентитета (дупликат примарног кључа).
  Пример 1	INSERT INTO Studenti VALUES ('Pera', 'Mitić', '126/95', 'R', 'R');
  Пример 2	INSERT INTO Ispiti VALUES ('2B03', '126/95', '30.05.2001.', 9, 'PROF3', 1);	Овај исказ ће се успешно извршити само ако се претходно изврши исказ из Примера 1 (не може се унети испит студента са индексом 126/95 ако подаци о том студенту нису у евиденцији студената, тј. табели Studenti).
  Пример 3	INSERT INTO Racun VALUES (44444, 'Zdipi i brisi', 17000);
  Пример 4	INSERT INTO Kupac VALUES (6, 'Pera', 'Mitić', #01/23/1990#, 'Simina 2', 11000, 'Beograd', 44444);	Овај исказ ће се успешно извршити само ако се претходно изврши исказ из Примера 3 (не може се унети купац са рачуном 44444 ако подаци о том рачуну нису у евиденцији рачуна, тј. табели Racun).
  Пример 5	INSERT INTO Naruciti VALUES (6, 101, #11/29/2016#, 10);	Овај исказ ће се успешно извршити само ако се претходно изврши исказ из Примера 4 (не може се унети наруџбина купца чији је IDKupca 6 ако подаци о том купцу нису у евиденцији купаца, тј. табели Kupac).

• UPDATE исказ (SQL исказ за ажурирање ћелије у табели базе података)

  Синтакса	UPDATE табела SET листа_додела_вредности_колона WHERE услов рестрикције (ограничења);
  Пример 1	UPDATE Studenti SET smer = 'N', status = 'V' WHERE indeks = '126/95'; ('2B03', '126/95', '30.05.2001.', 9, 'PROF3', 1);
  Пример 2	UPDATE Ispiti SET ocena = 10 WHERE indeks = '126/95' AND put = 1;

• DELETE исказ (SQL исказ за брисање реда из табеле базе података)

  Синтакса	DELETE FROM табела WHERE услов рестрикције (ограничења);
  Пример 2	DELETE FROM Ispiti WHERE indeks = '126/95' AND ocena = 5;	Овај пример исказа за брисање може увек да се изврши, чак и када нема шта да обрише, јер ниједна табела не зависи од табеле Ispiti.
  Пример 1	DELETE FROM Studenti WHERE indeks = '126/95';	Овај пример може да се изврши тек када се претходно обришу сви испити студента са индексом 126/95 јер табела Ispiti зависи по свом страном кључу од табеле Studenti.

• Задаци за самосталан рад и вежбу за предстојећи практикум

  У оквиру самосталног рада код куће, студент треба да направи упите који исписују:

  1. Повећати за 10% цену свих производа које није поручио ниједан купац из места Kraj sveta.
  2. У наруџбини свих производа које је испоручио Del-boj смањити количину за 20.
  3. Повећати за 1500 стање на рачуну свим купцима који су поручили мање од 5 различитих производа.
  4. Обрисати све производе који немају више од 5 различитих купаца.
  5. Обрисати све производе чија је просечна вредност наруџбине мања од 1000.
  6. Обрисати све производе чија је укупна вредност свих наруџбина мања од 500.
  7. Написати INSERT-исказе којима се у базу TVSHOP уноси информација да Bojsi (добављач бр. 5) испоручује производ Pokvareni telefon са шифром 405 и ценом 105 (информације о производу нису у бази), при чему:
     • информације о добављачу (Bojsi) већ постоје у бази;
     • информације о добављачу (Bojsi) не постоје у бази.
     Ако је потребно више INSERT-исказа, образложити да ли је редослед њиховог извршавања битан и зашто.

Релациона алгебра

SELECT ime, prezime
FROM Studenti;

SELECT *
FROM Studenti
WHERE status='V' 
  AND smer='R';

SELECT *
FROM Studenti, Ispiti;

SELECT *
FROM Studenti AS s, 
     Ispiti AS i
WHERE s.indeks = i.indeks;

SELECT *
FROM Studenti
WHERE smer = 'R'
   OR smer = 'N';

SELECT *
FROM Studenti
WHERE status='V'
  AND smer='R';

SELECT *
FROM Studenti
WHERE status='V'
  AND NOT(smer = 'R');

SELECT *
FROM Studenti
WHERE status='V'
  AND smer <> 'R';

Приметимо да је природно спајање заправо композиција две операције релационе алгебре: Декартовог производа и рестрикције. С обзиром да нам је природно спајање неопходно у свим примерима који се односе на неку базу података, Декартов производ практично нећемо користити директно, већ искључиво као део природног спајања.

Већина SQL-исказа за претрагу може се описати неком композицијом наведених операција релационе алгебре.

• Пример 1: Имена и презимена студената са смера R. Кораци:
  1. Резултат првог корака је релација `R_1`, добијена операцијом рестрикције над релацијом Studenti, тј. скуп свих n-торки полазне релације Studenti таквих да атрибут `smer` има вредност R:

     `\sigma_{(smer='R')} Studenti`

  2. Резултат другог корака је релација `R_2`, добијена операцијом пројекције над релацијом `R_1` по атрибутима `ime` и `prezime`:

     `\prod_{(ime, prezime)} R_1`

  Према томе, коначно решење је композиција пројекције и рестрикције, `\Pi\circ\sigma(Studenti) = \Pi(\sigma(Studenti))`, коју записујемо на следећи начин:

  `\prod_{(ime, prezime)}(\sigma_{(smer='R')} Studenti)`

• Пример: Имена и презимена студената који немају ниједну осмицу (укључујући и бруцоше). Овај задатак решавамо слично решењу у SQL-у у којем смо користили оператор IN: најпре ћемо наћи комплемент траженог скупа, тј. студенте који имају бар једну осмицу, а онда ћемо њиховом елиминацијом из скупа свих студената добити студенте који немају ниједну осмицу.
  1. Резултат првог корака је релација `R_1`, добијена операцијом рестрикције над релацијом Ispiti, тј. скуп свих n-торки полазне релације Ispiti таквих да атрибут `ocena` има вредност 8:

     `\sigma_{(ocena=8)} ii "Ispiti";`

  2. Резултат другог корака је релација `R_2`, добијена операцијом пројекције над релацијом `R_1` по атрибуту `ii "indeks"`, тј. скуп свих бројева индекса студената који имају бар једну осмицу:

     `\prod_{(ii "indeks")} R_1 = \prod_{(ii "indeks")}(\sigma_{(ocena=8)} ii "Ispiti")`

  3. Резултат трећег корака је релација `R_3`, добијена операцијом пројекције над релацијом Studenti по атрибуту `ii "indeks"`, тј. скуп бројева индекса свих студенатa:

     `\prod_{(ii "indeks")} Studenti`

  4. Резултат четвртог корака је релација `R_4`, добијена као скуповна разлика релација `R_3` и `R_2`, тј. скуп бројева индекса свих студената који немају ниједну осмицу, укључујући и бруцоше:

     `R_3 - R_2=\prod_{(ii "indeks")} Studenti - \prod_{(ii "indeks")}(\sigma_{(ocena=8)} ii "Ispiti")`

  5. Резултат петог корака је релација `R_5`, добијена природним спајањем релација `R_4` и релације Studenti по заједничком атрибуту `ii "indeks"`, тј. скуп свих студената који немају ниједну осмицу, укључујући и бруцоше:

     `R_4\bowtie_{ii "indeks"=ii "indeks"}Studenti =(\prod_{(ii "indeks")} Studenti - \prod_{(ii "indeks")}(\sigma_{(ocena=8)} ii "Ispiti"))\bowtie_{ii "indeks"=ii "indeks"}Studenti`

  6. Резултат шестог корака је релација `R_6`, добијена пројекцијом релације `R_5` по атрубутима `ime` и `prezime`, тј. скуп свих имена и презимена студената који немају ниједну осмицу, укључујући и бруцоше:

     `\prod_{(ime, prezime)}R_5 =` `=\prod_{(ime, prezime)}((\prod_{(ii "indeks")} Studenti - \prod_{(ii "indeks")}(\sigma_{(ocena=8)} ii "Ispiti"))\bowtie_{ii "indeks"=ii "indeks"}Studenti)`