You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 
 
 

548 lines
34 KiB

  1. <?xml version="1.0" encoding="utf-8"?>
  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  3. "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
  4. <html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
  5. <head>
  6. <!-- 2023-02-28 Tue 23:48 -->
  7. <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
  8. <meta name="viewport" content="width=device-width, initial-scale=1" />
  9. <title>Lab 6 Amirlan Sharipov (BS-CS21-01)</title>
  10. <meta name="author" content="Amirlan Sharipov (BS-CS21-01)" />
  11. <meta name="generator" content="Org Mode" />
  12. <style>
  13. #content { max-width: 60em; margin: auto; }
  14. .title { text-align: center;
  15. margin-bottom: .2em; }
  16. .subtitle { text-align: center;
  17. font-size: medium;
  18. font-weight: bold;
  19. margin-top:0; }
  20. .todo { font-family: monospace; color: red; }
  21. .done { font-family: monospace; color: green; }
  22. .priority { font-family: monospace; color: orange; }
  23. .tag { background-color: #eee; font-family: monospace;
  24. padding: 2px; font-size: 80%; font-weight: normal; }
  25. .timestamp { color: #bebebe; }
  26. .timestamp-kwd { color: #5f9ea0; }
  27. .org-right { margin-left: auto; margin-right: 0px; text-align: right; }
  28. .org-left { margin-left: 0px; margin-right: auto; text-align: left; }
  29. .org-center { margin-left: auto; margin-right: auto; text-align: center; }
  30. .underline { text-decoration: underline; }
  31. #postamble p, #preamble p { font-size: 90%; margin: .2em; }
  32. p.verse { margin-left: 3%; }
  33. pre {
  34. border: 1px solid #e6e6e6;
  35. border-radius: 3px;
  36. background-color: #f2f2f2;
  37. padding: 8pt;
  38. font-family: monospace;
  39. overflow: auto;
  40. margin: 1.2em;
  41. }
  42. pre.src {
  43. position: relative;
  44. overflow: auto;
  45. }
  46. pre.src:before {
  47. display: none;
  48. position: absolute;
  49. top: -8px;
  50. right: 12px;
  51. padding: 3px;
  52. color: #555;
  53. background-color: #f2f2f299;
  54. }
  55. pre.src:hover:before { display: inline; margin-top: 14px;}
  56. /* Languages per Org manual */
  57. pre.src-asymptote:before { content: 'Asymptote'; }
  58. pre.src-awk:before { content: 'Awk'; }
  59. pre.src-authinfo::before { content: 'Authinfo'; }
  60. pre.src-C:before { content: 'C'; }
  61. /* pre.src-C++ doesn't work in CSS */
  62. pre.src-clojure:before { content: 'Clojure'; }
  63. pre.src-css:before { content: 'CSS'; }
  64. pre.src-D:before { content: 'D'; }
  65. pre.src-ditaa:before { content: 'ditaa'; }
  66. pre.src-dot:before { content: 'Graphviz'; }
  67. pre.src-calc:before { content: 'Emacs Calc'; }
  68. pre.src-emacs-lisp:before { content: 'Emacs Lisp'; }
  69. pre.src-fortran:before { content: 'Fortran'; }
  70. pre.src-gnuplot:before { content: 'gnuplot'; }
  71. pre.src-haskell:before { content: 'Haskell'; }
  72. pre.src-hledger:before { content: 'hledger'; }
  73. pre.src-java:before { content: 'Java'; }
  74. pre.src-js:before { content: 'Javascript'; }
  75. pre.src-latex:before { content: 'LaTeX'; }
  76. pre.src-ledger:before { content: 'Ledger'; }
  77. pre.src-lisp:before { content: 'Lisp'; }
  78. pre.src-lilypond:before { content: 'Lilypond'; }
  79. pre.src-lua:before { content: 'Lua'; }
  80. pre.src-matlab:before { content: 'MATLAB'; }
  81. pre.src-mscgen:before { content: 'Mscgen'; }
  82. pre.src-ocaml:before { content: 'Objective Caml'; }
  83. pre.src-octave:before { content: 'Octave'; }
  84. pre.src-org:before { content: 'Org mode'; }
  85. pre.src-oz:before { content: 'OZ'; }
  86. pre.src-plantuml:before { content: 'Plantuml'; }
  87. pre.src-processing:before { content: 'Processing.js'; }
  88. pre.src-python:before { content: 'Python'; }
  89. pre.src-R:before { content: 'R'; }
  90. pre.src-ruby:before { content: 'Ruby'; }
  91. pre.src-sass:before { content: 'Sass'; }
  92. pre.src-scheme:before { content: 'Scheme'; }
  93. pre.src-screen:before { content: 'Gnu Screen'; }
  94. pre.src-sed:before { content: 'Sed'; }
  95. pre.src-sh:before { content: 'shell'; }
  96. pre.src-sql:before { content: 'SQL'; }
  97. pre.src-sqlite:before { content: 'SQLite'; }
  98. /* additional languages in org.el's org-babel-load-languages alist */
  99. pre.src-forth:before { content: 'Forth'; }
  100. pre.src-io:before { content: 'IO'; }
  101. pre.src-J:before { content: 'J'; }
  102. pre.src-makefile:before { content: 'Makefile'; }
  103. pre.src-maxima:before { content: 'Maxima'; }
  104. pre.src-perl:before { content: 'Perl'; }
  105. pre.src-picolisp:before { content: 'Pico Lisp'; }
  106. pre.src-scala:before { content: 'Scala'; }
  107. pre.src-shell:before { content: 'Shell Script'; }
  108. pre.src-ebnf2ps:before { content: 'ebfn2ps'; }
  109. /* additional language identifiers per "defun org-babel-execute"
  110. in ob-*.el */
  111. pre.src-cpp:before { content: 'C++'; }
  112. pre.src-abc:before { content: 'ABC'; }
  113. pre.src-coq:before { content: 'Coq'; }
  114. pre.src-groovy:before { content: 'Groovy'; }
  115. /* additional language identifiers from org-babel-shell-names in
  116. ob-shell.el: ob-shell is the only babel language using a lambda to put
  117. the execution function name together. */
  118. pre.src-bash:before { content: 'bash'; }
  119. pre.src-csh:before { content: 'csh'; }
  120. pre.src-ash:before { content: 'ash'; }
  121. pre.src-dash:before { content: 'dash'; }
  122. pre.src-ksh:before { content: 'ksh'; }
  123. pre.src-mksh:before { content: 'mksh'; }
  124. pre.src-posh:before { content: 'posh'; }
  125. /* Additional Emacs modes also supported by the LaTeX listings package */
  126. pre.src-ada:before { content: 'Ada'; }
  127. pre.src-asm:before { content: 'Assembler'; }
  128. pre.src-caml:before { content: 'Caml'; }
  129. pre.src-delphi:before { content: 'Delphi'; }
  130. pre.src-html:before { content: 'HTML'; }
  131. pre.src-idl:before { content: 'IDL'; }
  132. pre.src-mercury:before { content: 'Mercury'; }
  133. pre.src-metapost:before { content: 'MetaPost'; }
  134. pre.src-modula-2:before { content: 'Modula-2'; }
  135. pre.src-pascal:before { content: 'Pascal'; }
  136. pre.src-ps:before { content: 'PostScript'; }
  137. pre.src-prolog:before { content: 'Prolog'; }
  138. pre.src-simula:before { content: 'Simula'; }
  139. pre.src-tcl:before { content: 'tcl'; }
  140. pre.src-tex:before { content: 'TeX'; }
  141. pre.src-plain-tex:before { content: 'Plain TeX'; }
  142. pre.src-verilog:before { content: 'Verilog'; }
  143. pre.src-vhdl:before { content: 'VHDL'; }
  144. pre.src-xml:before { content: 'XML'; }
  145. pre.src-nxml:before { content: 'XML'; }
  146. /* add a generic configuration mode; LaTeX export needs an additional
  147. (add-to-list 'org-latex-listings-langs '(conf " ")) in .emacs */
  148. pre.src-conf:before { content: 'Configuration File'; }
  149. table { border-collapse:collapse; }
  150. caption.t-above { caption-side: top; }
  151. caption.t-bottom { caption-side: bottom; }
  152. td, th { vertical-align:top; }
  153. th.org-right { text-align: center; }
  154. th.org-left { text-align: center; }
  155. th.org-center { text-align: center; }
  156. td.org-right { text-align: right; }
  157. td.org-left { text-align: left; }
  158. td.org-center { text-align: center; }
  159. dt { font-weight: bold; }
  160. .footpara { display: inline; }
  161. .footdef { margin-bottom: 1em; }
  162. .figure { padding: 1em; }
  163. .figure p { text-align: center; }
  164. .equation-container {
  165. display: table;
  166. text-align: center;
  167. width: 100%;
  168. }
  169. .equation {
  170. vertical-align: middle;
  171. }
  172. .equation-label {
  173. display: table-cell;
  174. text-align: right;
  175. vertical-align: middle;
  176. }
  177. .inlinetask {
  178. padding: 10px;
  179. border: 2px solid gray;
  180. margin: 10px;
  181. background: #ffffcc;
  182. }
  183. #org-div-home-and-up
  184. { text-align: right; font-size: 70%; white-space: nowrap; }
  185. textarea { overflow-x: auto; }
  186. .linenr { font-size: smaller }
  187. .code-highlighted { background-color: #ffff00; }
  188. .org-info-js_info-navigation { border-style: none; }
  189. #org-info-js_console-label
  190. { font-size: 10px; font-weight: bold; white-space: nowrap; }
  191. .org-info-js_search-highlight
  192. { background-color: #ffff00; color: #000000; font-weight: bold; }
  193. .org-svg { }
  194. </style>
  195. </head>
  196. <body>
  197. <div id="content" class="content">
  198. <h1 class="title">Lab 6 Amirlan Sharipov (BS-CS21-01)</h1>
  199. <div id="table-of-contents" role="doc-toc">
  200. <h2>Table of Contents</h2>
  201. <div id="text-table-of-contents" role="doc-toc">
  202. <ul>
  203. <li><a href="#org66e9ff0">1. Disclaimer</a></li>
  204. <li><a href="#orge252c5e">2. Exercise 1</a>
  205. <ul>
  206. <li><a href="#org7e66bfe">2.1. Table creation and insertion</a></li>
  207. <li><a href="#org4bc389b">2.2. Queries</a></li>
  208. </ul>
  209. </li>
  210. <li><a href="#org613a69d">3. Exercise 2</a>
  211. <ul>
  212. <li><a href="#orgd2fe10b">3.1. Normalization</a>
  213. <ul>
  214. <li><a href="#org3192edb">3.1.1. 1NF</a></li>
  215. <li><a href="#org7985b8c">3.1.2. 2NF</a></li>
  216. <li><a href="#orgcc1c4fe">3.1.3. 3NF</a></li>
  217. <li><a href="#org5a0e8c4">3.1.4. BCNF and 4NF</a></li>
  218. <li><a href="#org19e9a14">3.1.5. Code</a></li>
  219. </ul>
  220. </li>
  221. <li><a href="#org7829592">3.2. Queries</a></li>
  222. </ul>
  223. </li>
  224. </ul>
  225. </div>
  226. </div>
  227. <div id="outline-container-org66e9ff0" class="outline-2">
  228. <h2 id="org66e9ff0"><span class="section-number-2">1.</span> Disclaimer</h2>
  229. <div class="outline-text-2" id="text-1">
  230. <p>
  231. Please, use the lab6.sql file to read/copy the source code. Also, the html version of this document looks better than the pdf one.
  232. </p>
  233. </div>
  234. </div>
  235. <div id="outline-container-orge252c5e" class="outline-2">
  236. <h2 id="orge252c5e"><span class="section-number-2">2.</span> Exercise 1</h2>
  237. <div class="outline-text-2" id="text-2">
  238. </div>
  239. <div id="outline-container-org7e66bfe" class="outline-3">
  240. <h3 id="org7e66bfe"><span class="section-number-3">2.1.</span> Table creation and insertion</h3>
  241. <div class="outline-text-3" id="text-2-1">
  242. <p>
  243. I used the schema provided in the slides. And then manually inserted data into the tables.
  244. </p>
  245. <div class="org-src-container">
  246. <pre class="src src-sql"><span style="color: #89DDFF;">CREATE</span> <span style="color: #89DDFF;">TABLE</span> <span style="color: #82aaff;">customers</span> (
  247. customerId <span style="color: #c792ea;">INT</span>,
  248. customerName <span style="color: #c792ea;">VARCHAR</span>(<span style="color: #f78c6c; font-weight: bold;">50</span>),
  249. city <span style="color: #c792ea;">VARCHAR</span>(<span style="color: #f78c6c; font-weight: bold;">50</span>),
  250. <span style="color: #89DDFF;">PRIMARY</span> <span style="color: #89DDFF;">KEY</span> (customerId)
  251. );
  252. <span style="color: #89DDFF;">CREATE</span> <span style="color: #89DDFF;">TABLE</span> <span style="color: #82aaff;">items</span> (
  253. itemId <span style="color: #c792ea;">INT</span>,
  254. itemName <span style="color: #c792ea;">VARCHAR</span>(<span style="color: #f78c6c; font-weight: bold;">50</span>),
  255. price <span style="color: #c792ea;">FLOAT</span>,
  256. <span style="color: #89DDFF;">PRIMARY</span> <span style="color: #89DDFF;">KEY</span> (itemId)
  257. );
  258. <span style="color: #89DDFF;">CREATE</span> <span style="color: #89DDFF;">TABLE</span> <span style="color: #82aaff;">orders</span> (
  259. orderId <span style="color: #c792ea;">INT</span>,
  260. customerId <span style="color: #c792ea;">INT</span>,
  261. <span style="color: #c792ea;">date</span> <span style="color: #c792ea;">DATE</span>,
  262. <span style="color: #89DDFF;">FOREIGN</span> <span style="color: #89DDFF;">KEY</span> (customerId) <span style="color: #89DDFF;">REFERENCES</span> customers(customerId),
  263. <span style="color: #89DDFF;">PRIMARY</span> <span style="color: #89DDFF;">KEY</span> (orderId)
  264. );
  265. <span style="color: #89DDFF;">CREATE</span> <span style="color: #89DDFF;">TABLE</span> <span style="color: #82aaff;">order_details</span> (
  266. orderId <span style="color: #c792ea;">INT</span>,
  267. itemId <span style="color: #c792ea;">INT</span>,
  268. quantity <span style="color: #c792ea;">INT</span>,
  269. <span style="color: #89DDFF;">FOREIGN</span> <span style="color: #89DDFF;">KEY</span> (itemId) <span style="color: #89DDFF;">REFERENCES</span> items(itemId),
  270. <span style="color: #89DDFF;">PRIMARY</span> <span style="color: #89DDFF;">KEY</span> (orderId, itemId)
  271. );
  272. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> customers <span style="color: #89DDFF;">VALUES</span>(<span style="color: #c3e88d;">'101'</span>, <span style="color: #c3e88d;">'Martin'</span>, <span style="color: #c3e88d;">'Prague'</span>);
  273. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> customers <span style="color: #89DDFF;">VALUES</span>(<span style="color: #c3e88d;">'107'</span>, <span style="color: #c3e88d;">'Herman'</span>, <span style="color: #c3e88d;">'Madrid'</span>);
  274. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> customers <span style="color: #89DDFF;">VALUES</span>(<span style="color: #c3e88d;">'110'</span>, <span style="color: #c3e88d;">'Pedro'</span>, <span style="color: #c3e88d;">'Moscow'</span>);
  275. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> items <span style="color: #89DDFF;">VALUES</span>(<span style="color: #c3e88d;">'3786'</span>, <span style="color: #c3e88d;">'Net'</span>, <span style="color: #f78c6c; font-weight: bold;">35.0</span>);
  276. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> items <span style="color: #89DDFF;">VALUES</span>(<span style="color: #c3e88d;">'4011'</span>, <span style="color: #c3e88d;">'Racket'</span>, <span style="color: #f78c6c; font-weight: bold;">65.0</span>);
  277. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> items <span style="color: #89DDFF;">VALUES</span>(<span style="color: #c3e88d;">'9132'</span>, <span style="color: #c3e88d;">'Pack-3'</span>, <span style="color: #f78c6c; font-weight: bold;">4.75</span>);
  278. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> items <span style="color: #89DDFF;">VALUES</span>(<span style="color: #c3e88d;">'5794'</span>, <span style="color: #c3e88d;">'Pack-6'</span>, <span style="color: #f78c6c; font-weight: bold;">5.0</span>);
  279. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> items <span style="color: #89DDFF;">VALUES</span>(<span style="color: #c3e88d;">'3141'</span>, <span style="color: #c3e88d;">'Cover'</span>, <span style="color: #f78c6c; font-weight: bold;">10.0</span>);
  280. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> orders <span style="color: #89DDFF;">VALUES</span>(<span style="color: #c3e88d;">'2301'</span>, <span style="color: #c3e88d;">'101'</span>, <span style="color: #c3e88d;">'2011-02-23'</span>);
  281. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> orders <span style="color: #89DDFF;">VALUES</span>(<span style="color: #c3e88d;">'2302'</span>, <span style="color: #c3e88d;">'107'</span>, <span style="color: #c3e88d;">'2011-02-25'</span>);
  282. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> orders <span style="color: #89DDFF;">VALUES</span>(<span style="color: #c3e88d;">'2303'</span>, <span style="color: #c3e88d;">'110'</span>, <span style="color: #c3e88d;">'2011-02-27'</span>);
  283. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> order_details <span style="color: #89DDFF;">VALUES</span> (<span style="color: #c3e88d;">'2301'</span>, <span style="color: #c3e88d;">'3786'</span>, <span style="color: #f78c6c; font-weight: bold;">3</span>);
  284. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> order_details <span style="color: #89DDFF;">VALUES</span> (<span style="color: #c3e88d;">'2301'</span>, <span style="color: #c3e88d;">'4011'</span>, <span style="color: #f78c6c; font-weight: bold;">6</span>);
  285. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> order_details <span style="color: #89DDFF;">VALUES</span> (<span style="color: #c3e88d;">'2301'</span>, <span style="color: #c3e88d;">'9132'</span>, <span style="color: #f78c6c; font-weight: bold;">8</span>);
  286. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> order_details <span style="color: #89DDFF;">VALUES</span> (<span style="color: #c3e88d;">'2302'</span>, <span style="color: #c3e88d;">'5794'</span>, <span style="color: #f78c6c; font-weight: bold;">4</span>);
  287. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> order_details <span style="color: #89DDFF;">VALUES</span> (<span style="color: #c3e88d;">'2303'</span>, <span style="color: #c3e88d;">'4011'</span>, <span style="color: #f78c6c; font-weight: bold;">2</span>);
  288. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> order_details <span style="color: #89DDFF;">VALUES</span> (<span style="color: #c3e88d;">'2303'</span>, <span style="color: #c3e88d;">'3141'</span>, <span style="color: #f78c6c; font-weight: bold;">2</span>);
  289. </pre>
  290. </div>
  291. </div>
  292. </div>
  293. <div id="outline-container-org4bc389b" class="outline-3">
  294. <h3 id="org4bc389b"><span class="section-number-3">2.2.</span> Queries</h3>
  295. <div class="outline-text-3" id="text-2-2">
  296. <p>
  297. First query takes the sum of all prices * quantities groupped by the orders and sorts them.
  298. The second query does the same thing, groups by customers, sorts by sum of quantities (descending order) and takes the first result.
  299. </p>
  300. <div class="org-src-container">
  301. <pre class="src src-sql"><span style="color: #89DDFF;">SELECT</span> order_details.orderId, <span style="color: #82aaff;">SUM</span>(items.price * order_details.quantity)
  302. <span style="color: #89DDFF;">FROM</span> order_details
  303. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> items <span style="color: #89DDFF;">on</span> items.itemId=order_details.itemId
  304. <span style="color: #89DDFF;">group</span> <span style="color: #89DDFF;">by</span> order_details.orderId <span style="color: #89DDFF;">ORDER</span> <span style="color: #89DDFF;">BY</span> <span style="color: #82aaff;">sum</span> <span style="color: #89DDFF;">ASC</span>;
  305. <span style="color: #89DDFF;">SELECT</span> customers.customerName, customers.city <span style="color: #89DDFF;">FROM</span> customers
  306. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> orders <span style="color: #89DDFF;">ON</span> customers.customerId=orders.customerId
  307. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> order_details <span style="color: #89DDFF;">ON</span> order_details.orderId=orders.orderId
  308. <span style="color: #89DDFF;">GROUP</span> <span style="color: #89DDFF;">BY</span> customers.customerId
  309. <span style="color: #89DDFF;">ORDER</span> <span style="color: #89DDFF;">BY</span> <span style="color: #82aaff;">SUM</span>(order_details.quantity) <span style="color: #89DDFF;">DESC</span>
  310. <span style="color: #89DDFF;">LIMIT</span> <span style="color: #f78c6c; font-weight: bold;">1</span>;
  311. </pre>
  312. </div>
  313. </div>
  314. </div>
  315. </div>
  316. <div id="outline-container-org613a69d" class="outline-2">
  317. <h2 id="org613a69d"><span class="section-number-2">3.</span> Exercise 2</h2>
  318. <div class="outline-text-2" id="text-3">
  319. </div>
  320. <div id="outline-container-orgd2fe10b" class="outline-3">
  321. <h3 id="orgd2fe10b"><span class="section-number-3">3.1.</span> Normalization</h3>
  322. <div class="outline-text-3" id="text-3-1">
  323. <p>
  324. I have several assumptions:
  325. </p>
  326. <ul class="org-ul">
  327. <li>Any teacher can work at several schools at once (or change the school)</li>
  328. <li>Room numbers don&rsquo;t depend on schools: the first number of the room name is not enough to assume the opposite</li>
  329. <li>Teachers may teach several courses: it&rsquo;s an elementary school, usually teachers can teach anything in elementary schools</li>
  330. </ul>
  331. </div>
  332. <div id="outline-container-org3192edb" class="outline-4">
  333. <h4 id="org3192edb"><span class="section-number-4">3.1.1.</span> 1NF</h4>
  334. <div class="outline-text-4" id="text-3-1-1">
  335. <p>
  336. It&rsquo;s almost in 1NF state. Each cell is already atomic, values of the same domain, etc. The only thing that&rsquo;s not there is the primary key. Let&rsquo;s say for now that the primary key is a tuple of school, teacher, course, room, grade, and book. This way there are no conflicts. It still looks like a mess, so I will normalize it further.
  337. </p>
  338. </div>
  339. </div>
  340. <div id="outline-container-org7985b8c" class="outline-4">
  341. <h4 id="org7985b8c"><span class="section-number-4">3.1.2.</span> 2NF</h4>
  342. <div class="outline-text-4" id="text-3-1-2">
  343. <p>
  344. Make new tables with relations for partial functional dependencies of non-prime attributes on candidate keys:
  345. </p>
  346. <ul class="org-ul">
  347. <li>lessons (lessonId, schoolName, teacherName, courseName, roomName, gradeName)</li>
  348. <li>books (bookId, bookName, publisherName)</li>
  349. <li>loans (loanId, lessonId, bookId, loanDate)</li>
  350. </ul>
  351. </div>
  352. </div>
  353. <div id="outline-container-orgcc1c4fe" class="outline-4">
  354. <h4 id="orgcc1c4fe"><span class="section-number-4">3.1.3.</span> 3NF</h4>
  355. <div class="outline-text-4" id="text-3-1-3">
  356. <p>
  357. Make new tables (with appropriate IDs) with relations for transitive functional dependencies of non-prime attribute on candidate key:
  358. </p>
  359. <ul class="org-ul">
  360. <li>schools (schoolId, schoolName)</li>
  361. <li>teachers (teacherId, teacherName)</li>
  362. <li>courses (courseId, courseName)</li>
  363. <li>rooms (roomId, roomName)</li>
  364. <li>grades (gradeId, gradeName)</li>
  365. <li>publishers (publisherId, publisherName)</li>
  366. <li>lessons (lessonId, schoolId, teacherId, courseId, roomId, gradeId)</li>
  367. <li>books (bookId, bookName, publisherId)</li>
  368. <li>loans (loanId, lessonId, bookId, loanDate)</li>
  369. </ul>
  370. </div>
  371. </div>
  372. <div id="outline-container-org5a0e8c4" class="outline-4">
  373. <h4 id="org5a0e8c4"><span class="section-number-4">3.1.4.</span> BCNF and 4NF</h4>
  374. <div class="outline-text-4" id="text-3-1-4">
  375. <p>
  376. Already satisfies.
  377. </p>
  378. </div>
  379. </div>
  380. <div id="outline-container-org19e9a14" class="outline-4">
  381. <h4 id="org19e9a14"><span class="section-number-4">3.1.5.</span> Code</h4>
  382. <div class="outline-text-4" id="text-3-1-5">
  383. <div class="org-src-container">
  384. <pre class="src src-sql"><span style="color: #89DDFF;">CREATE</span> <span style="color: #89DDFF;">TABLE</span> <span style="color: #82aaff;">schools</span> (
  385. schoolId SERIAL,
  386. schoolName <span style="color: #c792ea;">VARCHAR</span>(<span style="color: #f78c6c; font-weight: bold;">50</span>),
  387. <span style="color: #89DDFF;">PRIMARY</span> <span style="color: #89DDFF;">KEY</span> (schoolId)
  388. );
  389. <span style="color: #89DDFF;">CREATE</span> <span style="color: #89DDFF;">TABLE</span> <span style="color: #82aaff;">teachers</span> (
  390. teacherId SERIAL,
  391. teacherName <span style="color: #c792ea;">VARCHAR</span>(<span style="color: #f78c6c; font-weight: bold;">30</span>),
  392. <span style="color: #89DDFF;">PRIMARY</span> <span style="color: #89DDFF;">KEY</span> (teacherId)
  393. );
  394. <span style="color: #89DDFF;">CREATE</span> <span style="color: #89DDFF;">TABLE</span> <span style="color: #82aaff;">courses</span> (
  395. courseId SERIAL,
  396. courseName <span style="color: #c792ea;">VARCHAR</span>(<span style="color: #f78c6c; font-weight: bold;">40</span>),
  397. <span style="color: #89DDFF;">PRIMARY</span> <span style="color: #89DDFF;">KEY</span> (courseId)
  398. );
  399. <span style="color: #89DDFF;">CREATE</span> <span style="color: #89DDFF;">TABLE</span> <span style="color: #82aaff;">rooms</span> (
  400. roomId SERIAL,
  401. roomName <span style="color: #c792ea;">VARCHAR</span>(<span style="color: #f78c6c; font-weight: bold;">40</span>),
  402. <span style="color: #89DDFF;">PRIMARY</span> <span style="color: #89DDFF;">KEY</span> (roomId)
  403. );
  404. <span style="color: #89DDFF;">CREATE</span> <span style="color: #89DDFF;">TABLE</span> <span style="color: #82aaff;">grades</span> (
  405. gradeId SERIAL,
  406. gradeName <span style="color: #c792ea;">VARCHAR</span>(<span style="color: #f78c6c; font-weight: bold;">15</span>),
  407. <span style="color: #89DDFF;">PRIMARY</span> <span style="color: #89DDFF;">KEY</span> (gradeId)
  408. );
  409. <span style="color: #89DDFF;">CREATE</span> <span style="color: #89DDFF;">TABLE</span> <span style="color: #82aaff;">publishers</span> (
  410. publisherId SERIAL,
  411. publisherName <span style="color: #c792ea;">VARCHAR</span>(<span style="color: #f78c6c; font-weight: bold;">30</span>),
  412. <span style="color: #89DDFF;">PRIMARY</span> <span style="color: #89DDFF;">KEY</span> (publisherId)
  413. );
  414. <span style="color: #89DDFF;">CREATE</span> <span style="color: #89DDFF;">TABLE</span> <span style="color: #82aaff;">books</span> (
  415. bookId SERIAL,
  416. bookName <span style="color: #c792ea;">VARCHAR</span>(<span style="color: #f78c6c; font-weight: bold;">60</span>),
  417. publisherId <span style="color: #c792ea;">INT</span>,
  418. <span style="color: #89DDFF;">FOREIGN</span> <span style="color: #89DDFF;">KEY</span> (publisherId) <span style="color: #89DDFF;">REFERENCES</span> publishers(publisherId),
  419. <span style="color: #89DDFF;">PRIMARY</span> <span style="color: #89DDFF;">KEY</span> (bookId)
  420. );
  421. <span style="color: #89DDFF;">CREATE</span> <span style="color: #89DDFF;">TABLE</span> <span style="color: #82aaff;">lessons</span> (
  422. lessonId SERIAL,
  423. schoolId <span style="color: #c792ea;">INT</span>,
  424. teacherId <span style="color: #c792ea;">INT</span>,
  425. courseId <span style="color: #c792ea;">INT</span>,
  426. roomId <span style="color: #c792ea;">INT</span>,
  427. gradeId <span style="color: #c792ea;">INT</span>,
  428. <span style="color: #89DDFF;">FOREIGN</span> <span style="color: #89DDFF;">KEY</span> (teacherId) <span style="color: #89DDFF;">REFERENCES</span> teachers(teacherId),
  429. <span style="color: #89DDFF;">FOREIGN</span> <span style="color: #89DDFF;">KEY</span> (courseId) <span style="color: #89DDFF;">REFERENCES</span> courses(courseId),
  430. <span style="color: #89DDFF;">FOREIGN</span> <span style="color: #89DDFF;">KEY</span> (roomId) <span style="color: #89DDFF;">REFERENCES</span> rooms(roomId),
  431. <span style="color: #89DDFF;">FOREIGN</span> <span style="color: #89DDFF;">KEY</span> (gradeId) <span style="color: #89DDFF;">REFERENCES</span> grades(gradeId),
  432. <span style="color: #89DDFF;">PRIMARY</span> <span style="color: #89DDFF;">KEY</span> (lessonId)
  433. );
  434. <span style="color: #89DDFF;">CREATE</span> <span style="color: #89DDFF;">TABLE</span> <span style="color: #82aaff;">loans</span> (
  435. loanId SERIAL,
  436. lessonId <span style="color: #c792ea;">INT</span>,
  437. bookId <span style="color: #c792ea;">INT</span>,
  438. loanDate <span style="color: #c792ea;">DATE</span>,
  439. <span style="color: #89DDFF;">FOREIGN</span> <span style="color: #89DDFF;">KEY</span> (lessonId) <span style="color: #89DDFF;">REFERENCES</span> lessons(lessonId),
  440. <span style="color: #89DDFF;">FOREIGN</span> <span style="color: #89DDFF;">KEY</span> (bookId) <span style="color: #89DDFF;">REFERENCES</span> books(bookId),
  441. <span style="color: #89DDFF;">PRIMARY</span> <span style="color: #89DDFF;">KEY</span> (loanId)
  442. );
  443. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> schools (schoolName)
  444. <span style="color: #89DDFF;">SELECT</span> <span style="color: #89DDFF;">DISTINCT</span> school <span style="color: #89DDFF;">FROM</span> loan_books;
  445. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> teachers (teacherName)
  446. <span style="color: #89DDFF;">SELECT</span> <span style="color: #89DDFF;">DISTINCT</span> teacher <span style="color: #89DDFF;">FROM</span> loan_books;
  447. <span style="color: #676E95;">-- </span><span style="color: #676E95;">Inserted Numerical thinking 2 times because of case sensitivity. Not gonna change anything.</span>
  448. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> courses (courseName)
  449. <span style="color: #89DDFF;">SELECT</span> <span style="color: #89DDFF;">DISTINCT</span> course <span style="color: #89DDFF;">FROM</span> loan_books;
  450. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> rooms (roomName)
  451. <span style="color: #89DDFF;">SELECT</span> <span style="color: #89DDFF;">DISTINCT</span> room <span style="color: #89DDFF;">FROM</span> loan_books;
  452. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> grades (gradeName)
  453. <span style="color: #89DDFF;">SELECT</span> <span style="color: #89DDFF;">DISTINCT</span> grade <span style="color: #89DDFF;">FROM</span> loan_books;
  454. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> publishers (publisherName)
  455. <span style="color: #89DDFF;">SELECT</span> <span style="color: #89DDFF;">DISTINCT</span> publisher <span style="color: #89DDFF;">FROM</span> loan_books;
  456. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> books (bookName, publisherId)
  457. <span style="color: #89DDFF;">SELECT</span> <span style="color: #89DDFF;">DISTINCT</span> loan_books.book,
  458. publishers.publisherId <span style="color: #89DDFF;">FROM</span> loan_books
  459. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> publishers <span style="color: #89DDFF;">ON</span>
  460. publishers.publisherName=loan_books.publisher;
  461. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> lessons (schoolId, teacherId, courseId, roomId, gradeId)
  462. <span style="color: #89DDFF;">SELECT</span> <span style="color: #89DDFF;">DISTINCT</span> schools.schoolId, teachers.teacherId,
  463. courses.courseId, rooms.roomId, grades.gradeId <span style="color: #89DDFF;">FROM</span> loan_books
  464. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> schools <span style="color: #89DDFF;">ON</span> schools.schoolName=loan_books.school
  465. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> teachers <span style="color: #89DDFF;">ON</span> teachers.teacherName=loan_books.teacher
  466. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> courses <span style="color: #89DDFF;">ON</span> courses.courseName=loan_books.course
  467. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> rooms <span style="color: #89DDFF;">ON</span> rooms.roomName=loan_books.room
  468. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> grades <span style="color: #89DDFF;">ON</span> grades.gradeName=loan_books.grade;
  469. <span style="color: #89DDFF;">INSERT</span> <span style="color: #89DDFF;">INTO</span> loans (lessonId, bookId, loanDate)
  470. <span style="color: #89DDFF;">SELECT</span> <span style="color: #89DDFF;">DISTINCT</span> lessons.lessonId, books.bookId,
  471. loan_books.loanDate <span style="color: #89DDFF;">FROM</span> loan_books
  472. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> schools <span style="color: #89DDFF;">ON</span> schools.schoolName=loan_books.school
  473. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> teachers <span style="color: #89DDFF;">ON</span> teachers.teacherName=loan_books.teacher
  474. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> courses <span style="color: #89DDFF;">ON</span> courses.courseName=loan_books.course
  475. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> rooms <span style="color: #89DDFF;">ON</span> rooms.roomName=loan_books.room
  476. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> grades <span style="color: #89DDFF;">ON</span> grades.gradeName=loan_books.grade
  477. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> books <span style="color: #89DDFF;">ON</span> books.bookName=loan_books.book
  478. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> lessons <span style="color: #89DDFF;">ON</span> lessons.gradeId=grades.gradeId
  479. <span style="color: #89DDFF;">and</span> lessons.roomId=rooms.roomId
  480. <span style="color: #89DDFF;">and</span> lessons.courseId=courses.courseId
  481. <span style="color: #89DDFF;">and</span> lessons.teacherId=teachers.teacherId;
  482. </pre>
  483. </div>
  484. </div>
  485. </div>
  486. </div>
  487. <div id="outline-container-org7829592" class="outline-3">
  488. <h3 id="org7829592"><span class="section-number-3">3.2.</span> Queries</h3>
  489. <div class="outline-text-3" id="text-3-2">
  490. <p>
  491. The first query list all the schools that borrowed the books of every publisher using DISTINCT keyword.
  492. The second query orders the results from each school, and takes only 1 loan that has the highest loanDate for each school.
  493. </p>
  494. <div class="org-src-container">
  495. <pre class="src src-sql"><span style="color: #89DDFF;">SELECT</span> <span style="color: #89DDFF;">DISTINCT</span> publishers.publisherName, books.bookName,
  496. schools.schoolName <span style="color: #89DDFF;">FROM</span> books
  497. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> loans <span style="color: #89DDFF;">ON</span> loans.bookId=books.bookId
  498. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> lessons <span style="color: #89DDFF;">ON</span> lessons.lessonId=loans.lessonId
  499. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> schools <span style="color: #89DDFF;">ON</span> schools.schoolId=lessons.schoolId
  500. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> publishers <span style="color: #89DDFF;">ON</span> publishers.publisherId=books.publisherId
  501. <span style="color: #89DDFF;">ORDER</span> <span style="color: #89DDFF;">BY</span> publishers.publisherName;
  502. <span style="color: #89DDFF;">SELECT</span> <span style="color: #89DDFF;">DISTINCT</span> <span style="color: #89DDFF;">ON</span> (schools.schoolName) schools.schoolName,
  503. publishers.publisherName, books.bookName <span style="color: #89DDFF;">FROM</span> loans
  504. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> lessons <span style="color: #89DDFF;">ON</span> lessons.lessonId=loans.lessonId
  505. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> schools <span style="color: #89DDFF;">ON</span> schools.schoolId=lessons.schoolId
  506. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> books <span style="color: #89DDFF;">ON</span> books.bookId=loans.bookId
  507. <span style="color: #89DDFF;">INNER</span> <span style="color: #89DDFF;">JOIN</span> publishers <span style="color: #89DDFF;">ON</span> publishers.publisherId=books.publisherId
  508. <span style="color: #89DDFF;">ORDER</span> <span style="color: #89DDFF;">BY</span> schools.schoolName, loans.loanDate <span style="color: #89DDFF;">DESC</span>, <span style="color: #f78c6c; font-weight: bold;">1</span>;
  509. </pre>
  510. </div>
  511. </div>
  512. </div>
  513. </div>
  514. </div>
  515. <div id="postamble" class="status">
  516. <p class="author">Author: Amirlan Sharipov (BS-CS21-01)</p>
  517. <p class="date">Created: 2023-02-28 Tue 23:48</p>
  518. </div>
  519. </body>
  520. </html>