diff --git a/tiddlers/content/labs/lab01/Entropy.svg b/tiddlers/content/labs/lab01/Entropy.svg new file mode 100644 index 0000000..7335855 --- /dev/null +++ b/tiddlers/content/labs/lab01/Entropy.svg @@ -0,0 +1,134 @@ + + + + + + + + image/svg+xml + + + + + + + + + diff --git a/tiddlers/content/labs/lab01/Images/Entropy.svg b/tiddlers/content/labs/lab01/Images/Entropy.svg new file mode 100644 index 0000000..7335855 --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/Entropy.svg @@ -0,0 +1,134 @@ + + + + + + + + image/svg+xml + + + + + + + + + diff --git a/tiddlers/content/labs/lab01/Images/HuffmanBySymbol.png b/tiddlers/content/labs/lab01/Images/HuffmanBySymbol.png new file mode 100644 index 0000000..1ed254d --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/HuffmanBySymbol.png Binary files differ diff --git a/tiddlers/content/labs/lab01/Images/HuffmanScrabble.png b/tiddlers/content/labs/lab01/Images/HuffmanScrabble.png new file mode 100644 index 0000000..c17c169 --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/HuffmanScrabble.png Binary files differ diff --git a/tiddlers/content/labs/lab01/Images/WikipediaHuffman.png b/tiddlers/content/labs/lab01/Images/WikipediaHuffman.png new file mode 100644 index 0000000..8606442 --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/WikipediaHuffman.png Binary files differ diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_HuffmanBySymbol.png b/tiddlers/content/labs/lab01/Images/_Labs_01_HuffmanBySymbol.png new file mode 100644 index 0000000..1ed254d --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_HuffmanBySymbol.png Binary files differ diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_HuffmanBySymbol.png.meta b/tiddlers/content/labs/lab01/Images/_Labs_01_HuffmanBySymbol.png.meta new file mode 100644 index 0000000..b32f1fa --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_HuffmanBySymbol.png.meta @@ -0,0 +1,3 @@ +tags: image lab01 +title: /Labs/01/HuffmanBySymbol.png +type: image/png \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanBySymbol.png b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanBySymbol.png new file mode 100644 index 0000000..7aa5f22 --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanBySymbol.png Binary files differ diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanBySymbol.png.meta b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanBySymbol.png.meta new file mode 100644 index 0000000..bc497b0 --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanBySymbol.png.meta @@ -0,0 +1,2 @@ +title: HuffmanBySymbol.png +type: image/png \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanBySymbol_1.png b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanBySymbol_1.png new file mode 100644 index 0000000..1ed254d --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanBySymbol_1.png Binary files differ diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanBySymbol_1.png.meta b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanBySymbol_1.png.meta new file mode 100644 index 0000000..8aaae8a --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanBySymbol_1.png.meta @@ -0,0 +1,3 @@ +tags: image lab01 +title: /Labs/01/Images/HuffmanBySymbol.png +type: image/png \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanScrabble.png b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanScrabble.png new file mode 100644 index 0000000..c17c169 --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanScrabble.png Binary files differ diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanScrabble.png.meta b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanScrabble.png.meta new file mode 100644 index 0000000..dd9df71 --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanScrabble.png.meta @@ -0,0 +1,3 @@ +tags: image +title: /Labs/01/Images/HuffmanScrabble.png +type: image/png \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanTreeCode.png b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanTreeCode.png new file mode 100644 index 0000000..8aac78d --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanTreeCode.png Binary files differ diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanTreeCode.png.meta b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanTreeCode.png.meta new file mode 100644 index 0000000..b55badf --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_HuffmanTreeCode.png.meta @@ -0,0 +1,3 @@ +tags: image +title: /Labs/01/Images/HuffmanTreeCode +type: image/png \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig1.svg b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig1.svg new file mode 100644 index 0000000..c9f4013 --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig1.svg @@ -0,0 +1,124 @@ + + +G + + + +n00 +C + + + +n01 +D + + + +n00->n01 + + + +n100 +B + + + +n01->n100 + + + +n101 +E + + + +n100->n101 + + + +n11 +A + + + +n101->n11 + + + +n + + + + +n0 + + + + +n->n0 + + +0 + + + +n1 + + + + +n->n1 + + + 1 + + + +n0->n00 + + +0 + + + +n0->n01 + + + 1 + + + +n1->n11 + + + 1 + + + +n10 + + + + +n1->n10 + + +0 + + + +n10->n100 + + +0 + + + +n10->n101 + + + 1 + + + \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig1.svg.meta b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig1.svg.meta new file mode 100644 index 0000000..1520567 --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig1.svg.meta @@ -0,0 +1,3 @@ +tags: image +title: /Labs/01/Images/fig1.svg +type: image/svg+xml \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig2.svg b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig2.svg new file mode 100644 index 0000000..74138da --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig2.svg @@ -0,0 +1,124 @@ + + +G + + + +n0000 +A + + + +n0001 +D + + + +n0000->n0001 + + + +n001 +E + + + +n0001->n001 + + + +n01 +B + + + +n001->n01 + + + +n1 +C + + + +n01->n1 + + + +n + + + + +n->n1 + + + 1 + + + +n0 + + + + +n->n0 + + +0 + + + +n0->n01 + + + 1 + + + +n00 + + + + +n0->n00 + + +0 + + + +n00->n001 + + + 1 + + + +n000 + + + + +n00->n000 + + +0 + + + +n000->n0000 + + +0 + + + +n000->n0001 + + + 1 + + + \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig2.svg.meta b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig2.svg.meta new file mode 100644 index 0000000..39054f5 --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig2.svg.meta @@ -0,0 +1,3 @@ +tags: image +title: /Labs/01/Images/fig2.svg +type: image/svg+xml \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig3.svg.tid b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig3.svg.tid new file mode 100644 index 0000000..20da134 --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/_Labs_01_Images_fig3.svg.tid @@ -0,0 +1,1333 @@ +status: complete +tags: image +title: /Labs/01/Images/fig3.svg +type: text/vnd.tiddlywiki + + + +G + + + +n000 +⎵ + + + +n0010000 +Y + + + +n000->n0010000 + + + +n0010001000 +( + + + +n0010000->n0010001000 + + + +n0010001001 +7 + + + +n0010001000->n0010001001 + + + +n001000101 +9 + + + +n0010001001->n001000101 + + + +n00100011 +K + + + +n001000101->n00100011 + + + +n001001 +U + + + +n00100011->n001001 + + + +n001010 +M + + + +n001001->n001010 + + + +n0010110 +, + + + +n001010->n0010110 + + + +n0010111 +. + + + +n0010110->n0010111 + + + +n00110 +H + + + +n0010111->n00110 + + + +n001110 +F + + + +n00110->n001110 + + + +n001111000 +- + + + +n001110->n001111000 + + + +n0011110010 +' + + + +n001111000->n0011110010 + + + +n0011110011 +Z + + + +n0011110010->n0011110011 + + + +n00111101 +0 + + + +n0011110011->n00111101 + + + +n0011111 +V + + + +n00111101->n0011111 + + + +n0100 +A + + + +n0011111->n0100 + + + +n0101 +T + + + +n0100->n0101 + + + +n01100 +L + + + +n0101->n01100 + + + +n011010 +P + + + +n01100->n011010 + + + +n011011 +G + + + +n011010->n011011 + + + +n0111 +I + + + +n011011->n0111 + + + +n10000 +D + + + +n0111->n10000 + + + +n10001000000 +: + + + +n10000->n10001000000 + + + +n100010000010 +; + + + +n10001000000->n100010000010 + + + +n1000100000110 +! + + + +n100010000010->n1000100000110 + + + +n1000100000111 +? + + + +n1000100000110->n1000100000111 + + + +n1000100001 +Q + + + +n1000100000111->n1000100001 + + + +n100010001 +J + + + +n1000100001->n100010001 + + + +n10001001 +2 + + + +n100010001->n10001001 + + + +n100010100 +3 + + + +n10001001->n100010100 + + + +n100010101 +8 + + + +n100010100->n100010101 + + + +n100010110 +X + + + +n100010101->n100010110 + + + +n100010111 +5 + + + +n100010110->n100010111 + + + +n100011 +W + + + +n100010111->n100011 + + + +n1001 +N + + + +n100011->n1001 + + + +n1010 +O + + + +n1001->n1010 + + + +n1011 +R + + + +n1010->n1011 + + + +n11000 +C + + + +n1011->n11000 + + + +n110010 +B + + + +n11000->n110010 + + + +n110011000 +4 + + + +n110010->n110011000 + + + +n110011001 +" + + + +n110011000->n110011001 + + + +n110011010 +6 + + + +n110011001->n110011010 + + + +n110011011 +) + + + +n110011010->n110011011 + + + +n1100111 +1 + + + +n110011011->n1100111 + + + +n1101 +S + + + +n1100111->n1101 + + + +n111 +E + + + +n1101->n111 + + + +n + + + + +n0 + + + + +n->n0 + + +0 + + + +n1 + + + + +n->n1 + + + 1 + + + +n00 + + + + +n0->n00 + + +0 + + + +n01 + + + + +n0->n01 + + + 1 + + + +n00->n000 + + +0 + + + +n001 + + + + +n00->n001 + + + 1 + + + +n0010 + + + + +n001->n0010 + + +0 + + + +n0011 + + + + +n001->n0011 + + + 1 + + + +n00100 + + + + +n0010->n00100 + + +0 + + + +n00101 + + + + +n0010->n00101 + + + 1 + + + +n00100->n001001 + + + 1 + + + +n001000 + + + + +n00100->n001000 + + +0 + + + +n001000->n0010000 + + +0 + + + +n0010001 + + + + +n001000->n0010001 + + + 1 + + + +n0010001->n00100011 + + + 1 + + + +n00100010 + + + + +n0010001->n00100010 + + +0 + + + +n00100010->n001000101 + + + 1 + + + +n001000100 + + + + +n00100010->n001000100 + + +0 + + + +n001000100->n0010001000 + + +0 + + + +n001000100->n0010001001 + + + 1 + + + +n00101->n001010 + + +0 + + + +n001011 + + + + +n00101->n001011 + + + 1 + + + +n001011->n0010110 + + +0 + + + +n001011->n0010111 + + + 1 + + + +n0011->n00110 + + +0 + + + +n00111 + + + + +n0011->n00111 + + + 1 + + + +n00111->n001110 + + +0 + + + +n001111 + + + + +n00111->n001111 + + + 1 + + + +n001111->n0011111 + + + 1 + + + +n0011110 + + + + +n001111->n0011110 + + +0 + + + +n0011110->n00111101 + + + 1 + + + +n00111100 + + + + +n0011110->n00111100 + + +0 + + + +n00111100->n001111000 + + +0 + + + +n001111001 + + + + +n00111100->n001111001 + + + 1 + + + +n001111001->n0011110010 + + +0 + + + +n001111001->n0011110011 + + + 1 + + + +n010 + + + + +n01->n010 + + +0 + + + +n011 + + + + +n01->n011 + + + 1 + + + +n010->n0100 + + +0 + + + +n010->n0101 + + + 1 + + + +n011->n0111 + + + 1 + + + +n0110 + + + + +n011->n0110 + + +0 + + + +n0110->n01100 + + +0 + + + +n01101 + + + + +n0110->n01101 + + + 1 + + + +n01101->n011010 + + +0 + + + +n01101->n011011 + + + 1 + + + +n10 + + + + +n1->n10 + + +0 + + + +n11 + + + + +n1->n11 + + + 1 + + + +n100 + + + + +n10->n100 + + +0 + + + +n101 + + + + +n10->n101 + + + 1 + + + +n100->n1001 + + + 1 + + + +n1000 + + + + +n100->n1000 + + +0 + + + +n1000->n10000 + + +0 + + + +n10001 + + + + +n1000->n10001 + + + 1 + + + +n10001->n100011 + + + 1 + + + +n100010 + + + + +n10001->n100010 + + +0 + + + +n1000100 + + + + +n100010->n1000100 + + +0 + + + +n1000101 + + + + +n100010->n1000101 + + + 1 + + + +n1000100->n10001001 + + + 1 + + + +n10001000 + + + + +n1000100->n10001000 + + +0 + + + +n10001000->n100010001 + + + 1 + + + +n100010000 + + + + +n10001000->n100010000 + + +0 + + + +n100010000->n1000100001 + + + 1 + + + +n1000100000 + + + + +n100010000->n1000100000 + + +0 + + + +n1000100000->n10001000000 + + +0 + + + +n10001000001 + + + + +n1000100000->n10001000001 + + + 1 + + + +n10001000001->n100010000010 + + +0 + + + +n100010000011 + + + + +n10001000001->n100010000011 + + + 1 + + + +n100010000011->n1000100000110 + + +0 + + + +n100010000011->n1000100000111 + + + 1 + + + +n10001010 + + + + +n1000101->n10001010 + + +0 + + + +n10001011 + + + + +n1000101->n10001011 + + + 1 + + + +n10001010->n100010100 + + +0 + + + +n10001010->n100010101 + + + 1 + + + +n10001011->n100010110 + + +0 + + + +n10001011->n100010111 + + + 1 + + + +n101->n1010 + + +0 + + + +n101->n1011 + + + 1 + + + +n11->n111 + + + 1 + + + +n110 + + + + +n11->n110 + + +0 + + + +n110->n1101 + + + 1 + + + +n1100 + + + + +n110->n1100 + + +0 + + + +n1100->n11000 + + +0 + + + +n11001 + + + + +n1100->n11001 + + + 1 + + + +n11001->n110010 + + +0 + + + +n110011 + + + + +n11001->n110011 + + + 1 + + + +n110011->n1100111 + + + 1 + + + +n1100110 + + + + +n110011->n1100110 + + +0 + + + +n11001100 + + + + +n1100110->n11001100 + + +0 + + + +n11001101 + + + + +n1100110->n11001101 + + + 1 + + + +n11001100->n110011000 + + +0 + + + +n11001100->n110011001 + + + 1 + + + +n11001101->n110011010 + + +0 + + + +n11001101->n110011011 + + + 1 + + + \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/Images/drawing3.svg b/tiddlers/content/labs/lab01/Images/drawing3.svg new file mode 100644 index 0000000..d75183e --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/drawing3.svg @@ -0,0 +1,2846 @@ + + + + + + + + image/svg+xml + + + + + + + + + + ⎵ + + + Y + + + + ( + + + + 7 + + + + 9 + + + + K + + + + U + + + + M + + + + , + + + + . + + + + H + + + + F + + + + - + + + + ' + + + + Z + + + + 0 + + + + V + + + + A + + + + T + + + + L + + + + P + + + + G + + + + I + + + + D + + + + : + + + + ; + + + + ! + + + + ? + + + + Q + + + + J + + + + 2 + + + + 3 + + + + 8 + + + + X + + + + 5 + + + + W + + + + N + + + + O + + + + R + + + + C + + + + B + + + + 4 + + + + " + + + + 6 + + + + ) + + + + 1 + + + + S + + + + E + + + + + + + + + + + + 0 + + + + + + + + 1 + + + + + + + + 0 + + + + + + + + 1 + + + + + 0 + + + + + + + + 1 + + + + + + + + 0 + + + + + + + + 1 + + + + + + + + 0 + + + + + + + + 1 + + + + + 1 + + + + + + + + 0 + + + + + 0 + + + + + + + + 1 + + + + + 1 + + + + + + + + 0 + + + + + 1 + + + + + + + + 0 + + + + + 0 + + + + + 1 + + + + + 0 + + + + + + + + 1 + + + + + 0 + + + + + 1 + + + + + 0 + + + + + + + + 1 + + + + + 0 + + + + + + + + 1 + + + + + 1 + + + + + + + + 0 + + + + + 1 + + + + + + + + 0 + + + + + 0 + + + + + + + + 1 + + + + + 0 + + + + + 1 + + + + + + + + 0 + + + + + + + + 1 + + + + + 0 + + + + + 1 + + + + + 1 + + + + + + + + 0 + + + + + 0 + + + + + + + + 1 + + + + + 0 + + + + + 1 + + + + + + + + 0 + + + + + + + + 1 + + + + + + + + 0 + + + + + + + + 1 + + + + + 1 + + + + + + + + 0 + + + + + 0 + + + + + + + + 1 + + + + + 1 + + + + + + + + 0 + + + + + + + + 0 + + + + + + + + 1 + + + + + 1 + + + + + + + + 0 + + + + + 1 + + + + + + + + 0 + + + + + 1 + + + + + + + + 0 + + + + + 0 + + + + + + + + 1 + + + + + 0 + + + + + + + + 1 + + + + + 0 + + + + + 1 + + + + + + + + 0 + + + + + + + + 1 + + + + + 0 + + + + + 1 + + + + + 0 + + + + + 1 + + + + + 0 + + + + + 1 + + + + + 1 + + + + + + + + 0 + + + + + 1 + + + + + + + + 0 + + + + + 0 + + + + + + + + 1 + + + + + 0 + + + + + + + + 1 + + + + + 1 + + + + + + + + 0 + + + + + + + + 0 + + + + + + + + 1 + + + + + 0 + + + + + 1 + + + + + 0 + + + + + 1 + + + + diff --git a/tiddlers/content/labs/lab01/Images/fig1.svg b/tiddlers/content/labs/lab01/Images/fig1.svg new file mode 100644 index 0000000..c9f4013 --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/fig1.svg @@ -0,0 +1,124 @@ + + +G + + + +n00 +C + + + +n01 +D + + + +n00->n01 + + + +n100 +B + + + +n01->n100 + + + +n101 +E + + + +n100->n101 + + + +n11 +A + + + +n101->n11 + + + +n + + + + +n0 + + + + +n->n0 + + +0 + + + +n1 + + + + +n->n1 + + + 1 + + + +n0->n00 + + +0 + + + +n0->n01 + + + 1 + + + +n1->n11 + + + 1 + + + +n10 + + + + +n1->n10 + + +0 + + + +n10->n100 + + +0 + + + +n10->n101 + + + 1 + + + \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/Images/fig2.svg b/tiddlers/content/labs/lab01/Images/fig2.svg new file mode 100644 index 0000000..74138da --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/fig2.svg @@ -0,0 +1,124 @@ + + +G + + + +n0000 +A + + + +n0001 +D + + + +n0000->n0001 + + + +n001 +E + + + +n0001->n001 + + + +n01 +B + + + +n001->n01 + + + +n1 +C + + + +n01->n1 + + + +n + + + + +n->n1 + + + 1 + + + +n0 + + + + +n->n0 + + +0 + + + +n0->n01 + + + 1 + + + +n00 + + + + +n0->n00 + + +0 + + + +n00->n001 + + + 1 + + + +n000 + + + + +n00->n000 + + +0 + + + +n000->n0000 + + +0 + + + +n000->n0001 + + + 1 + + + \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/Images/fig3.svg b/tiddlers/content/labs/lab01/Images/fig3.svg new file mode 100644 index 0000000..193ad1f --- /dev/null +++ b/tiddlers/content/labs/lab01/Images/fig3.svg @@ -0,0 +1,1328 @@ + + +G + + + +n000 +⎵ + + + +n0010000 +Y + + + +n000->n0010000 + + + +n0010001000 +( + + + +n0010000->n0010001000 + + + +n0010001001 +7 + + + +n0010001000->n0010001001 + + + +n001000101 +9 + + + +n0010001001->n001000101 + + + +n00100011 +K + + + +n001000101->n00100011 + + + +n001001 +U + + + +n00100011->n001001 + + + +n001010 +M + + + +n001001->n001010 + + + +n0010110 +, + + + +n001010->n0010110 + + + +n0010111 +. + + + +n0010110->n0010111 + + + +n00110 +H + + + +n0010111->n00110 + + + +n001110 +F + + + +n00110->n001110 + + + +n001111000 +- + + + +n001110->n001111000 + + + +n0011110010 +' + + + +n001111000->n0011110010 + + + +n0011110011 +Z + + + +n0011110010->n0011110011 + + + +n00111101 +0 + + + +n0011110011->n00111101 + + + +n0011111 +V + + + +n00111101->n0011111 + + + +n0100 +A + + + +n0011111->n0100 + + + +n0101 +T + + + +n0100->n0101 + + + +n01100 +L + + + +n0101->n01100 + + + +n011010 +P + + + +n01100->n011010 + + + +n011011 +G + + + +n011010->n011011 + + + +n0111 +I + + + +n011011->n0111 + + + +n10000 +D + + + +n0111->n10000 + + + +n10001000000 +: + + + +n10000->n10001000000 + + + +n100010000010 +; + + + +n10001000000->n100010000010 + + + +n1000100000110 +! + + + +n100010000010->n1000100000110 + + + +n1000100000111 +? + + + +n1000100000110->n1000100000111 + + + +n1000100001 +Q + + + +n1000100000111->n1000100001 + + + +n100010001 +J + + + +n1000100001->n100010001 + + + +n10001001 +2 + + + +n100010001->n10001001 + + + +n100010100 +3 + + + +n10001001->n100010100 + + + +n100010101 +8 + + + +n100010100->n100010101 + + + +n100010110 +X + + + +n100010101->n100010110 + + + +n100010111 +5 + + + +n100010110->n100010111 + + + +n100011 +W + + + +n100010111->n100011 + + + +n1001 +N + + + +n100011->n1001 + + + +n1010 +O + + + +n1001->n1010 + + + +n1011 +R + + + +n1010->n1011 + + + +n11000 +C + + + +n1011->n11000 + + + +n110010 +B + + + +n11000->n110010 + + + +n110011000 +4 + + + +n110010->n110011000 + + + +n110011001 +" + + + +n110011000->n110011001 + + + +n110011010 +6 + + + +n110011001->n110011010 + + + +n110011011 +) + + + +n110011010->n110011011 + + + +n1100111 +1 + + + +n110011011->n1100111 + + + +n1101 +S + + + +n1100111->n1101 + + + +n111 +E + + + +n1101->n111 + + + +n + + + + +n0 + + + + +n->n0 + + +0 + + + +n1 + + + + +n->n1 + + + 1 + + + +n00 + + + + +n0->n00 + + +0 + + + +n01 + + + + +n0->n01 + + + 1 + + + +n00->n000 + + +0 + + + +n001 + + + + +n00->n001 + + + 1 + + + +n0010 + + + + +n001->n0010 + + +0 + + + +n0011 + + + + +n001->n0011 + + + 1 + + + +n00100 + + + + +n0010->n00100 + + +0 + + + +n00101 + + + + +n0010->n00101 + + + 1 + + + +n00100->n001001 + + + 1 + + + +n001000 + + + + +n00100->n001000 + + +0 + + + +n001000->n0010000 + + +0 + + + +n0010001 + + + + +n001000->n0010001 + + + 1 + + + +n0010001->n00100011 + + + 1 + + + +n00100010 + + + + +n0010001->n00100010 + + +0 + + + +n00100010->n001000101 + + + 1 + + + +n001000100 + + + + +n00100010->n001000100 + + +0 + + + +n001000100->n0010001000 + + +0 + + + +n001000100->n0010001001 + + + 1 + + + +n00101->n001010 + + +0 + + + +n001011 + + + + +n00101->n001011 + + + 1 + + + +n001011->n0010110 + + +0 + + + +n001011->n0010111 + + + 1 + + + +n0011->n00110 + + +0 + + + +n00111 + + + + +n0011->n00111 + + + 1 + + + +n00111->n001110 + + +0 + + + +n001111 + + + + +n00111->n001111 + + + 1 + + + +n001111->n0011111 + + + 1 + + + +n0011110 + + + + +n001111->n0011110 + + +0 + + + +n0011110->n00111101 + + + 1 + + + +n00111100 + + + + +n0011110->n00111100 + + +0 + + + +n00111100->n001111000 + + +0 + + + +n001111001 + + + + +n00111100->n001111001 + + + 1 + + + +n001111001->n0011110010 + + +0 + + + +n001111001->n0011110011 + + + 1 + + + +n010 + + + + +n01->n010 + + +0 + + + +n011 + + + + +n01->n011 + + + 1 + + + +n010->n0100 + + +0 + + + +n010->n0101 + + + 1 + + + +n011->n0111 + + + 1 + + + +n0110 + + + + +n011->n0110 + + +0 + + + +n0110->n01100 + + +0 + + + +n01101 + + + + +n0110->n01101 + + + 1 + + + +n01101->n011010 + + +0 + + + +n01101->n011011 + + + 1 + + + +n10 + + + + +n1->n10 + + +0 + + + +n11 + + + + +n1->n11 + + + 1 + + + +n100 + + + + +n10->n100 + + +0 + + + +n101 + + + + +n10->n101 + + + 1 + + + +n100->n1001 + + + 1 + + + +n1000 + + + + +n100->n1000 + + +0 + + + +n1000->n10000 + + +0 + + + +n10001 + + + + +n1000->n10001 + + + 1 + + + +n10001->n100011 + + + 1 + + + +n100010 + + + + +n10001->n100010 + + +0 + + + +n1000100 + + + + +n100010->n1000100 + + +0 + + + +n1000101 + + + + +n100010->n1000101 + + + 1 + + + +n1000100->n10001001 + + + 1 + + + +n10001000 + + + + +n1000100->n10001000 + + +0 + + + +n10001000->n100010001 + + + 1 + + + +n100010000 + + + + +n10001000->n100010000 + + +0 + + + +n100010000->n1000100001 + + + 1 + + + +n1000100000 + + + + +n100010000->n1000100000 + + +0 + + + +n1000100000->n10001000000 + + +0 + + + +n10001000001 + + + + +n1000100000->n10001000001 + + + 1 + + + +n10001000001->n100010000010 + + +0 + + + +n100010000011 + + + + +n10001000001->n100010000011 + + + 1 + + + +n100010000011->n1000100000110 + + +0 + + + +n100010000011->n1000100000111 + + + 1 + + + +n10001010 + + + + +n1000101->n10001010 + + +0 + + + +n10001011 + + + + +n1000101->n10001011 + + + 1 + + + +n10001010->n100010100 + + +0 + + + +n10001010->n100010101 + + + 1 + + + +n10001011->n100010110 + + +0 + + + +n10001011->n100010111 + + + 1 + + + +n101->n1010 + + +0 + + + +n101->n1011 + + + 1 + + + +n11->n111 + + + 1 + + + +n110 + + + + +n11->n110 + + +0 + + + +n110->n1101 + + + 1 + + + +n1100 + + + + +n110->n1100 + + +0 + + + +n1100->n11000 + + +0 + + + +n11001 + + + + +n1100->n11001 + + + 1 + + + +n11001->n110010 + + +0 + + + +n110011 + + + + +n11001->n110011 + + + 1 + + + +n110011->n1100111 + + + 1 + + + +n1100110 + + + + +n110011->n1100110 + + +0 + + + +n11001100 + + + + +n1100110->n11001100 + + +0 + + + +n11001101 + + + + +n1100110->n11001101 + + + 1 + + + +n11001100->n110011000 + + +0 + + + +n11001100->n110011001 + + + 1 + + + +n11001101->n110011010 + + +0 + + + +n11001101->n110011011 + + + 1 + + + \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/_Labs_01_Computing Entropy.md b/tiddlers/content/labs/lab01/_Labs_01_Computing Entropy.md new file mode 100644 index 0000000..3d31082 --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Computing Entropy.md @@ -0,0 +1,61 @@ +As mentioned in lectures, entropy is measure of how much information content (“surprise”) is present in a system. + +Given a set of N symbols, and the probability of each symbol occurring, we can compute the entropy (in bits) as: + +{{/Labs/01/Images/entropy.svg}} + +where pi is the probability of encountering a given symbol. A worked example of computing entropy was given in lectures. For another example, consider a system with five symbols: A; B; C; D; and E, each occurring with probabilities: 0.0625; 0.25; 0.5; 0.0625; and 0.125. The entropy of this system is 1.875 bits, the computation of which is outlined in the following table: + + + + + + + + + + + + + + + + + + + + + + + + + + + +
Symbolipilog2pi-pi x log2pi
A10.2-2.3220.464
B20.1-3.3220.332
C30.3-1.7370.521
D40.3-1.7370.521
E50.1-3.3220.332
s=2.171
+### Exercise +Use the table below to help you compute the entropy of a system with five symbols (A, B, C, D, E) with the probabilities 0.0625, 0.25, 0.5, 0.0625 and 0.125 (respectively): +Note:There is an editable worksheet doc on Blackboard you can use for your workings in this lab. + + + + + + + + + + + + + + + + + + + + + + +
Symbol i pi log2pi -pi x log2pi
A10.0625
B20.25
C30.5
D40.0625
E50.125
s= ?
\ No newline at end of file diff --git a/tiddlers/content/labs/lab01/_Labs_01_Computing Entropy.md.meta b/tiddlers/content/labs/lab01/_Labs_01_Computing Entropy.md.meta new file mode 100644 index 0000000..a4ca35b --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Computing Entropy.md.meta @@ -0,0 +1,4 @@ +section: 2 +tags: lab lab01 +title: /Labs/01/Computing Entropy +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/_Labs_01_Decoding and Encoding Data Sequences.md b/tiddlers/content/labs/lab01/_Labs_01_Decoding and Encoding Data Sequences.md new file mode 100644 index 0000000..6b9afd0 --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Decoding and Encoding Data Sequences.md @@ -0,0 +1,44 @@ +Recall in lectures that we presented a Huffman tree that was constructed by examining the frequency of characters on Wikipedia: +{{/Labs/01/Images/fig3.svg}} +The corresponding Huffman coding for this tree is: +{{/Labs/01/Images/HuffmanTreeCode}} + + + +Using this coding, we can decode the sequence + +1100010100010100110100001100111001111011100111 + +as: +11000 = C (remaining bits: 10100010100110100001100111001111011100111) + +1010 = O (remaining bits: 0010100110100001100111001111011100111) + +001010 = M (remaining bits: 0110100001100111001111011100111) + +011010 = P (remaining bits: 0001100111001111011100111) + +000 = [space] (remaining bits: 1100111001111011100111) + +1100111 = 1 (remaining bits: 001111011100111) + +00111101 = 0 (remaining bits: 1100111) + +1100111 = 1 (no remaining bits, end of sequence) + +Resulting in the string 'COMP 101'. +### Exercise +Use this Huffman coding to decode the following bits: 11010011001001001100110101001 + +Answer? + +--- + + + +Encoding information using a Huffman coding follows the opposite direction (look up the symbol and emit the corresponding code). This is straightforward when the table describing the Huffman coding is sorted by symbol: + +{{/Labs/01/Images/HuffmanBySymbol.png}} +Now, we can quickly confirm that the sequence ‘QWERTY’ would be encoded as 1000100001(Q) 100011(W) 111(E) 1011(R​) 0101(T) 0010000(Y) 1000100001100011111101101010010000 (34 bits). + + diff --git a/tiddlers/content/labs/lab01/_Labs_01_Decoding and Encoding Data Sequences.md.meta b/tiddlers/content/labs/lab01/_Labs_01_Decoding and Encoding Data Sequences.md.meta new file mode 100644 index 0000000..717303b --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Decoding and Encoding Data Sequences.md.meta @@ -0,0 +1,4 @@ +section: 4 +tags: lab lab01 +title: /Labs/01/Decoding and Encoding Data Sequences +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/_Labs_01_Exercises.md b/tiddlers/content/labs/lab01/_Labs_01_Exercises.md new file mode 100644 index 0000000..b8081c0 --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Exercises.md @@ -0,0 +1,21 @@ +1. Using the Wikipedia-derived Huffman coding: + a. encode your full name and count the number of bits required for the encoding (remember to encode the spaces between your names). + b. encode your student user code and count the number of bits required for the encoding. + c. Encode your student ID and count the number of bits required for the encoding. + d. Encode the University of Otago contact number (+64 3 479 7000) and count the number of bits required for the encoding. + e. Encode the University of Otago contact email (university@otago.ac.nz) and count the number of bits required for the encoding. +2. The Wikipedia-derived Huffman coding represents 48 characters – if these were encoded using a naïve coding, how many bits would be required per character? +3. Based on the naïve coding, how many bits would be required to encode: + a. your full name (including spaces between names)? + b. your student user code? + c. your student ID? + d. the University of Otago contact phone number? + e. the University of Otago contact email address? +4. Based on the results in steps 1 and 3, comment on the efficiency of the Wikipedia-derived Hufman coding for encoding the various types of information you worked with (names, IDs, numbers, etc.). + + +### Final Exercises +1 . In the game Scrabble, each letter is given a score. Compare the letter scores in Scrabble to the code lengths for the corresponding letters in our Wikipedia-derived Huffman coding. Briefly comment on this comparison. We have extracted the letters for you in the table below (they are also available on Blackboard). + +{{/Labs/01/Images/HuffmanScrabble.png}} +2 . Perform a similar comparison between the code lengths for letters in Morse code and those in our Wikipedia-derived Huffman coding. Briefly comment on this comparison. \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/_Labs_01_Exercises.md.meta b/tiddlers/content/labs/lab01/_Labs_01_Exercises.md.meta new file mode 100644 index 0000000..b8587b8 --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Exercises.md.meta @@ -0,0 +1,4 @@ +section: 5 +tags: lab lab01 +title: /Labs/01/Exercises +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/_Labs_01_Huffman Coding.md b/tiddlers/content/labs/lab01/_Labs_01_Huffman Coding.md new file mode 100644 index 0000000..a1bd64d --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Huffman Coding.md @@ -0,0 +1,89 @@ +Also mentioned in lectures was the concept of a Huffman code, which is a method of lossless data compression (reducing the amount of data needed to store or transmit information). Recall that a Huffman code can be extracted from a Huffman tree – one basic algorithm for building a Huffman tree is: +1. Start with an empty set T +2. Add all the symbols to set T +3. While there are multiple "trees" in the set: + a. Remove the lowest probability tree from set T (break ties in terms of smaller tree size), and call this tree A + b. Remove the lowest probability tree from set T (break ties in terms of smaller tree size), and call this tree B + c. Make a new tree C by joining A and B, and set the probability of C to p(A) + p(B) + d. Add the new tree C to set T +4. Return the only tree in set T as the Huffman tree + +(note: we will cover algorithm concepts is more detail in later classes, for now you only need to know the algorithm itself) + +As with entropy, a worked example of building a Huffman tree is discussed in lectures. For example, one possible Huffman tree corresponding to the system shown earlier (with entropy of 2.171 bits) would be: + +{{/Labs/01/Images/fig1.svg}} + +The Huffman coding can then be extract from this tree by walking the path along each symbol: + + + + + + + + + + + + + + + + + + + +
Symbol P(Symbol) Code |Code|
A 0.2 11 2
B 0.1 100 3
C 0.3 00 2
D 0.3 01 2
E 0.1 101 3
+On average, the code length in this system is 0.2 * 2 + 0.1 * 3 + 0.3 * 2 + 0.3 * 2 + 0.1 * 3 = 2.2 bits. Recall that the entropy of this system is 2.171 bits, so the efficiency of this Huffman coding is 2.171/2.200 = 98.7%. Contrast this with a naïve encoding of our symbols, with three bits per symbol: + + + + + + + + + + + + + + + + + + + +
Symbol P(Symbol) Code |Code|
A 0.2 000 2
B 0.1 001 3
C 0.3 010 2
D 0.3 011 2
E 0.1 100 3
+The code length here is 3 bits, so the efficiency of this system is 2.171/3.000 = 72.4%. In other words, the Huffman coding is over 25% more efficient than a naïve encoding. + +**Exercise** +Compute the Huffman tree of the second example from the previous section (the one with entropy of 1.875 bits). Use this tree to extract the Huffman coding, and then compute its efficiency. In doing so, what do you note about the lengths of the codes, both overall and relative to the probabilities of the symbols that they represent? + + +Draw your tree now to fill in the table below. + + + + + + + + + + + + + + + + + + + + + + + +
Symbol P(Symbol) Code |Code|
A 0.625
B 0.25
C 0.5
D 0.0625
E 0.125
\ No newline at end of file diff --git a/tiddlers/content/labs/lab01/_Labs_01_Huffman Coding.md.meta b/tiddlers/content/labs/lab01/_Labs_01_Huffman Coding.md.meta new file mode 100644 index 0000000..766d6ad --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Huffman Coding.md.meta @@ -0,0 +1,4 @@ +section: 3 +tags: lab lab01 +title: /Labs/01/Huffman Coding +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/_Labs_01_Images_.tid b/tiddlers/content/labs/lab01/_Labs_01_Images_.tid new file mode 100644 index 0000000..9908d7b --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Images_.tid @@ -0,0 +1,8 @@ +status: complete +tags: image +title: /Labs/01/Images/ +type: text/vnd.tiddlywiki + +The following tiddlers were imported: + +# [[HuffmanBySymbol.png]] \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/_Labs_01_Images_entropy.svg.tid b/tiddlers/content/labs/lab01/_Labs_01_Images_entropy.svg.tid new file mode 100644 index 0000000..1a9ad1e --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Images_entropy.svg.tid @@ -0,0 +1,139 @@ +status: complete +tags: image +title: /Labs/01/Images/entropy.svg +type: text/vnd.tiddlywiki + + + + + + + + + image/svg+xml + + + + + + + + + diff --git a/tiddlers/content/labs/lab01/_Labs_01_Information Theory.md b/tiddlers/content/labs/lab01/_Labs_01_Information Theory.md new file mode 100644 index 0000000..f3f9cd4 --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Information Theory.md @@ -0,0 +1,6 @@ +Recall from lectures that information theory underpins a vast number of concepts in computer and information systems. Concepts such as data transmission, compression, estimating effort, and machine learning, heavily lean on information theory concepts. + +In this lab, you will be working on the following exercises: +* computing the entropy of some small systems, +* creating a Huffman code for these systems (and examining their efficiency), +* and finally using a Huffman coding to encode and decode data. \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/_Labs_01_Information Theory.md.meta b/tiddlers/content/labs/lab01/_Labs_01_Information Theory.md.meta new file mode 100644 index 0000000..77adc53 --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Information Theory.md.meta @@ -0,0 +1,4 @@ +section: 1.1 +tags: lab lab01 +title: /Labs/01/Information Theory +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab01/_Labs_01_Lab 1_ Information Theory.tid b/tiddlers/content/labs/lab01/_Labs_01_Lab 1_ Information Theory.tid new file mode 100644 index 0000000..c88c3b6 --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Lab 1_ Information Theory.tid @@ -0,0 +1,14 @@ +tags: lab lab01 toc +title: /Labs/01/Lab 1: Information Theory +type: text/vnd.tiddlywiki + +
Click the <> button below to open all of the sections for this lab.
+ +!! Contents + +<$set name="path" value="/Labs/01/"> + +<$macrocall $name="contents-tree" path=<> /> +
<$macrocall $name="openByPath" path=<> />
+ + diff --git a/tiddlers/content/labs/lab01/_Labs_01_Task 0_ Basic Lab Environment.md b/tiddlers/content/labs/lab01/_Labs_01_Task 0_ Basic Lab Environment.md new file mode 100644 index 0000000..8478a1f --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Task 0_ Basic Lab Environment.md @@ -0,0 +1,47 @@ +### Familiarise yourself with the Student Desktop. +When you log into the machine you are sitting at it appears the work that you are doing, and all your files are stored locally. They are not, they are on a virtual computer running on a server somewhere else. All the machine you are on is doing is handling the connection to the virtual machine, capturing your keystrokes and displaying the screen. This is why you can log into the student desktop from a Mac in Singapore and you are actually working on a Windows machine in Dunedin. Make sure that you can access your student desktop remotely from your own computer, assuming you have an internet connection. +### Open Windows Explorer and look around. +In the left pane click on the root of your H:\Drive signified by your usercode, this is the Root of your H:\Drive. While you are there create a folder for COMP101 and any of your other courses. This is your Home directory where you keep all your work! + +If you open your COMP101 folder in Explorer and click inside the address bar above it you will see the address change, and can see its actual address is - H:\COMP101. +If you use the My Documents folder on your desktop to create your folders and click into the address bar you will see that it shows something like…. +```plaintext +https://student.otago.ac.nz\mds\Profiles-V2\b\burga052\RedirectFolders\Documents\COMP101 +``` +(Not an address you want to type into the command line compared to **H:\COMP101**) + +Check out the other drives you have available. + +You will probably not see the C:\Drive. That would either be the main hard drive on the local machine (usually) or the C:\Drive on the virtual machine at University. If you can see the C:\Drive be aware that you can’t work or store files there on the student desktop. + +There should also be a T:\Drive with an InfoSci folder inside it. That is where we will place files or applications specific to our coursework and not available to other students. + +O:\Drive. How to access OneDrive from your Student Desktop +Although your OneDrive files are stored online in the cloud, you can access them from the Student Desktop just like another folder. Open any folder and click on the OneDrive icon in the sidepane. +Note: It could be a useful place to keep your back ups. It’s not a good place to work on files there however as it’s not even in New Zealand (at this time). + + +Your student H:\Drive will have 3Gb of storage available although some of it will be taken up with your system profile. You also have access to 1TB of cloud storage in your Student One Drive. You should not run out of space for legitimate student work and backups. + +There will also be a CD/DVD drive, that is physically on the machine in front of you. +If you plug a flash drive or phone into the USB port of the computer it will also show up as a Drive. +### Backup +Backing up your files is your responsibility. Losing or having a file corrupt is never an excuse to get a time extension for an assessment deadline. Usually it is considered good practice to have back ups “off site” however the university systems run by ITS have a mirrored drive system. If the server your virtual computer is working on now fails, another will take over and nothing saved is lost. If however your file corrupts (as they just do sometimes) having multiple copies of a corrupt file will not help. Sometimes you may get lucky and by right click on the file in windows explorer you may have the option to restore a previous version, but don’t rely on this as there is no guarantee that previous versions will be available! + +Work out a backup system that is easy and fast. The easier it is the more likely you will keep regular backups. Try out one possible method described below. + +#### Task: Backup archive. +Find a file using the File Explorer you wish to backup + +Right click on it and select Send To > Compressed zip file. +Right click on the .zip file it created and select Rename. Rename it to BackUp.zip. + +Left click on any other file you want to back up and drag it over the BackUp.zip file. This will keep the original file and a backup. + +Now both are backed up. As you work on your files every half hour or so save the file (Ctrl-S) go back to file explorer and drag the file over BackUp.zip and overwrite your old backup of the same name. + +If anything goes wrong you can extract your file from the BackUp.zip and save it back to your H:\Drive. + +**It does not matter what system you choose to back up your files, but you must have a system that you will adhere to.** + + diff --git a/tiddlers/content/labs/lab01/_Labs_01_Task 0_ Basic Lab Environment.md.meta b/tiddlers/content/labs/lab01/_Labs_01_Task 0_ Basic Lab Environment.md.meta new file mode 100644 index 0000000..3586f33 --- /dev/null +++ b/tiddlers/content/labs/lab01/_Labs_01_Task 0_ Basic Lab Environment.md.meta @@ -0,0 +1,4 @@ +section: 1.0 +tags: lab lab01 +title: /Labs/01/Task 0: Basic Lab Environment +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab02/_Labs_02_Lab 2_ Data Modelling.tid b/tiddlers/content/labs/lab02/_Labs_02_Lab 2_ Data Modelling.tid new file mode 100644 index 0000000..6a43434 --- /dev/null +++ b/tiddlers/content/labs/lab02/_Labs_02_Lab 2_ Data Modelling.tid @@ -0,0 +1,14 @@ +tags: lab toc lab02 +title: /Labs/02/Lab 2: Data Modelling +type: text/vnd.tiddlywiki + +
Click the <> button below to open all of the sections for this lab.
+ +!! Contents + +<$set name="path" value="/Labs/02/"> + +<$macrocall $name="contents-tree" path=<> /> +
<$macrocall $name="openByPath" path=<> />
+ + diff --git a/tiddlers/content/labs/lab03/ERD-1.jpg b/tiddlers/content/labs/lab03/ERD-1.jpg new file mode 100644 index 0000000..ea355b7 --- /dev/null +++ b/tiddlers/content/labs/lab03/ERD-1.jpg Binary files differ diff --git a/tiddlers/content/labs/lab03/Images/Tables.jpg b/tiddlers/content/labs/lab03/Images/Tables.jpg new file mode 100644 index 0000000..0cfb2e1 --- /dev/null +++ b/tiddlers/content/labs/lab03/Images/Tables.jpg Binary files differ diff --git a/tiddlers/content/labs/lab03/Images/Untitled.pdn b/tiddlers/content/labs/lab03/Images/Untitled.pdn new file mode 100644 index 0000000..74b3d64 --- /dev/null +++ b/tiddlers/content/labs/lab03/Images/Untitled.pdn Binary files differ diff --git a/tiddlers/content/labs/lab03/Tables.jpg b/tiddlers/content/labs/lab03/Tables.jpg new file mode 100644 index 0000000..0cfb2e1 --- /dev/null +++ b/tiddlers/content/labs/lab03/Tables.jpg Binary files differ diff --git a/tiddlers/content/labs/lab03/Tables.svg b/tiddlers/content/labs/lab03/Tables.svg new file mode 100644 index 0000000..534d926 --- /dev/null +++ b/tiddlers/content/labs/lab03/Tables.svg @@ -0,0 +1,1661 @@ + + + + + + image/svg+xml + + + + + + + + + + + diff --git a/tiddlers/content/labs/lab03/_Labs_03_CHECK Constraints.md b/tiddlers/content/labs/lab03/_Labs_03_CHECK Constraints.md new file mode 100644 index 0000000..89a7a80 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_CHECK Constraints.md @@ -0,0 +1,39 @@ +Any additional validation rules for the database can be enforced using the general-purpose **CHECK** constraint type. The body of a **CHECK** constraint is a condition (true/false expression), which will usually centre around the following: + +• Equality (=) +• Inequality (>, <, >=, <=, **BETWEEN**) +• Set membership (**IN**) +• The string pattern matching operator, **LIKE** (and **NOT LIKE**) +• The logical operators **AND, OR, NOT** + +Here is a typical **CHECK** constraint (but see the lectures for further examples): + + +```plaintext +constraint Sale_Shipped check (Shipped in ('T', 'F')), nt, +``` +### SQL’s Expression Language +Most SQL dialects have an extensive library of elements that you can use to build database-specific formulae. These can be used in most places a value is expected, including when defining integrity constraints. These language elements are documented in the Oracle SQL Language Reference, under the chapters labelled Pseudocolumns, Operators, Functions, Expressions, and Conditions: +https://www.h2database.com/html/grammar.html +Another very good resource for all things SQL + +https://www.w3schools.com/sql/sql_intro.asp +To test a particular formula, write it in the **SELECT** clause of a **SELECT** statement, e.g. + +```plaintext +select 13 * 49, length('abracadabra'), add_months(Current_Date, 6) from Dual; +``` +When implementing any complex **CHECK** constraint, test your formula thoroughly first using a **SELECT** statement as shown, especially if you are using unfamiliar operators or functions. You will find this much quicker and easier than having to implement the entire table before testing. + +#### Implement and test the following integrity constraints: +• Site region should be Dunedin, Alexandra, or Tekapo. + +• Site altitude should be constrained according to New Zealand’s geography (from -2 m on the Taieri Plains to 3724 m for Aoraki/Mount Cook, but call it -10 m to 4000 m). + +• The institute was founded in 2015, so there should be no samples before the start of that year. + +• E-mail addresses should contain an ‘@’ symbol. (Extra challenge: ensure that there is only one ‘@’, and that it is not at the start or end.) + +• Extra challenge: the first two characters of the site code should match the region (e.g. ‘DN’ for Dunedin). + +• Extra challenge: refactor the region constraint to use a separate lookup table. What advantages would this bring? diff --git a/tiddlers/content/labs/lab03/_Labs_03_CHECK Constraints.md.meta b/tiddlers/content/labs/lab03/_Labs_03_CHECK Constraints.md.meta new file mode 100644 index 0000000..32be081 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_CHECK Constraints.md.meta @@ -0,0 +1,4 @@ +section: 3.4 +tags: Contents +title: /Labs/03/CHECK Constraints +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Candidate, Primary, and Alternate Keys.md b/tiddlers/content/labs/lab03/_Labs_03_Candidate, Primary, and Alternate Keys.md new file mode 100644 index 0000000..3f92530 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Candidate, Primary, and Alternate Keys.md @@ -0,0 +1,22 @@ +In the Relational Model, the most important kind of key is the candidate key. A candidate key is a set of columns whose values must be unique for all rows. No element of a candidate key may be null, since nulls cannot be compared for equality (which is the basis of uniqueness). Every table must have at least one candidate key, in order to avoid duplicate rows, which should be avoided – as Edgar Codd observed, “Saying something more than once doesn’t make it any more true.” + +In SQL, there is no **CANDIDATE KEY** constraint – only **PRIMARY KEY** for the candidate key chosen as the most useful identifier, and UNIQUE for others. Note that **PRIMARY KEY** implies **NOT NULL** for all attributes, but **UNIQUE** does not, so you should add **NOT NULL** constraints for those. + +SQL allows at most one **PRIMARY KEY** constraint per table, and while it will permit a table with no primary key, you should never do this in practice (other than for temporary tables for importing data that need to be cleaned up first). + +Note that in general, a key is a set of columns, and it is not unusual for a key to have more than one (these are known as composite keys). When declaring a composite key, use the usual SQL list syntax, like so: + + +```plaintext +primary key (Student_ID, Paper_Code, Year, Semester) +``` +Note that adding more columns to a key makes it “less unique”, i.e. it undermines its strictness. Therefore, use only the minimal set of attributes required to assure uniqueness. Using existing columns to form a composite primary key is generally preferable to creating a new surrogate key, since surrogate key values have no intrinsic meaning. + +Using the ERD as a guide, identify the primary key for each of the following tables: + +• Scientist: +• Sample: +• Site: + +Now modify your CREATE TABLE statements to implement these, and test that they are correctly enforced. + diff --git a/tiddlers/content/labs/lab03/_Labs_03_Candidate, Primary, and Alternate Keys.md.meta b/tiddlers/content/labs/lab03/_Labs_03_Candidate, Primary, and Alternate Keys.md.meta new file mode 100644 index 0000000..cebb64c --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Candidate, Primary, and Alternate Keys.md.meta @@ -0,0 +1,4 @@ +section: 3.2 +tags: Contents +title: /Labs/03/Candidate, Primary, and Alternate Keys +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Column Values_ Mandatory and Default.md b/tiddlers/content/labs/lab03/_Labs_03_Column Values_ Mandatory and Default.md new file mode 100644 index 0000000..c25e33e --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Column Values_ Mandatory and Default.md @@ -0,0 +1,16 @@ +### Mandatory Column Values +Columns that are required to have a value (indicated by • in the ERD) should have a NOT NULL constraint applied, following the data type, like so: + + +```plaintext + Sale_Date timestamp not null, +``` +### Default Column Values +Sometimes a column has a sensible default value, which the system can insert automatically if the user does not specify one. These can be a fixed value such as a default bank balance of 0, or a calculation such as using the current date and time for new sales. + +To define a default column value, use the DEFAULT keyword followed by the desired expression. This must appear after the data type for the column, but before any constraints such as NOT NULL. + + +```plaintext +Sale_Date timestamp default current_timestamp not null, +``` diff --git a/tiddlers/content/labs/lab03/_Labs_03_Column Values_ Mandatory and Default.md.meta b/tiddlers/content/labs/lab03/_Labs_03_Column Values_ Mandatory and Default.md.meta new file mode 100644 index 0000000..e27e2d0 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Column Values_ Mandatory and Default.md.meta @@ -0,0 +1,4 @@ +section: 2.1 +tags: Contents +title: /Labs/03/Column Values: Mandatory and Default +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Database Integrity Constraints.md b/tiddlers/content/labs/lab03/_Labs_03_Database Integrity Constraints.md new file mode 100644 index 0000000..4e14a90 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Database Integrity Constraints.md @@ -0,0 +1,21 @@ +### Main Concepts: + Primary key constraints + Foreign key constraints + CHECK constraints + Column vs table constraints + Table creation order + Constraint naming + Testing constraints + Importing data + +### Introduction +A database with a well-designed structure that is centrally stored and managed is a good start, but there are still some potential problems with your database as it currently stands. For example, what happens if you try to add the same scientist more than once? What happens if you try to record a sample for a site that does not exist? The system should disallow these sorts of invalid data, and the key to enforcing this is *integrity constraints*. + +SQL DBMSs support a number of kinds of constraint, which can be built into your database via extra code in your **CREATE TABLE** statements: + +• **NOT NULL** for mandatory columns (which you looked at last week) +• **PRIMARY KEY** and **UNIQUE** for identity columns +• **FOREIGN KEY** for referential integrity (orphan prevention) +• **CHECK** for miscellaneous “business rules” + +You will be extending the SQL schema definition script you created earlier, so start by making a copy of that file in a new folder for this week’s lab and opening the new copy in DBeaver. diff --git a/tiddlers/content/labs/lab03/_Labs_03_Database Integrity Constraints.md.meta b/tiddlers/content/labs/lab03/_Labs_03_Database Integrity Constraints.md.meta new file mode 100644 index 0000000..604a972 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Database Integrity Constraints.md.meta @@ -0,0 +1,4 @@ +section: 3.0 +tags: Contents +title: /Labs/03/Database Integrity Constraints +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Developing and Testing Your Code.md b/tiddlers/content/labs/lab03/_Labs_03_Developing and Testing Your Code.md new file mode 100644 index 0000000..c1bafcf --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Developing and Testing Your Code.md @@ -0,0 +1,46 @@ +When developing your code, use an incremental approach: test early and often! Run each CREATE TABLE statement by itself (using Ctrl+Enter) and verify that there are no syntax errors. + +If errors are reported, try to read and understand them. If the error isn’t obvious, check for things like missing or extraneous commas, blank lines, semicolons, parentheses, or misspelled keywords. + +Enable line numbers in the gutter/left margin of the editor (right-click), as error messages will often refer to the line number. If you can’t see an error there, look at the end of the previous line. You will often see a red underlining as well. + +If you need to make changes to a table, the easiest way is to drop the table, amend your code, and run it again. Use the DROP TABLE statement like so: + +```plaintext +drop table Scientist; +``` +You may wish to keep a DROP TABLE statement for each table at the start of your script for easy use. Enclose them all in a block comment so that they normally have no effect but can be run when required. + +To check that a table is working properly, try inserting data, e.g. + +```plaintext +insert into Scientist (Scientist_Num, Surname, Other_Names, Email, Mobile_Phone) +values (123, 'Hankins', 'Stephanie', 'steph@mail.com', '021 123 456'); +``` +For testing, use the data from the Excel spreadsheets you already have. When entering dates, convert from the character representation to a true date using the TO_DATE function with a suitable format, e.g. +```plaintext +to_date('31/JUL/2017', 'DD/MON/YYYY') +``` +You can also convert from an internal date to a formatted string using the TO_CHAR function: +```plaintext +to_char(current_date, 'YYYY-MM-DD HH24:MI:SS') +``` +See the documentation of format models for more information: +https://www.h2database.com/html/functions.html?highlight=date&search=date#firstFound + +To check that the data has gone in, use the SELECT statement to query the table +```plaintext +select * from Scientist; +``` +To insert a default value, either omit the column name and value from your INSERT statement, or use the DEFAULT keyword in place of the value. + +To insert a null (missing value), either omit the column name and value from your INSERT, or use the NULL keyword instead of the value. + +As an alternative to dropping and re-creating the table, you can use the ALTER TABLE statement. This is a little more complicated to use, but has the advantage of leaving the table and its data in place. This would be an essential tool for a database administrator wanting to modify a live database! That said, modifying your CREATE TABLE statements has the advantage of keeping the entire table definition in one place. As you will see next time, the order of your CREATE TABLE statements will matter when you have foreign keys. + +Make sure you have saved your SQL source file, and know where to find it again! +### Further Reading -- need updating links... +• H2 database home +https://www.h2database.com/html/main.html +https://www.h2database.com/html/commands.html#alter_table_add + diff --git a/tiddlers/content/labs/lab03/_Labs_03_Developing and Testing Your Code.md.meta b/tiddlers/content/labs/lab03/_Labs_03_Developing and Testing Your Code.md.meta new file mode 100644 index 0000000..8864857 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Developing and Testing Your Code.md.meta @@ -0,0 +1,4 @@ +section: 2.2 +tags: Contents +title: /Labs/03/Developing and Testing Your Code +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Foreign Keys.md b/tiddlers/content/labs/lab03/_Labs_03_Foreign Keys.md new file mode 100644 index 0000000..65c5a25 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Foreign Keys.md @@ -0,0 +1,32 @@ +Foreign keys enforce referential integrity: they ensure that each row has a valid parent row (in this sense, they prevent “orphan” rows). A foreign key is required for each relationship identified on the ERD, and the foreign key will appear on the “many” side, and reference a candidate key (usually the primary key) on the “one” side. + +A foreign key is a set of attributes (columns in SQL), and must match the parent key in both number of elements and data types. The column names can differ, but should normally match. You can satisfy both of these by copying the parent column definitions and pasting them into the child table (but check carefully whether the foreign key attributes should have **NOT NULL** constraints applied). We recommend that you declare foreign keys in SQL using named table-level constraints, like so: + + +```plaintext +constraint Enrolment_Student_FK foreign key (Student_ID) + references Student, +``` +If the column names differ, identify the corresponding columns in the parent table like so: +```plaintext +references Student (Student_ID), +``` +The foreign keys in your schema will dictate the order of your **CREATE TABLE** statements. Since a foreign key always references a candidate key from the parent table, that parent table must exist first. For example, in a student records system, you would have to create the Paper and Student tables before you could create the Enrolment table. If you imagine moving the crow’s foot signifying the “many” side along the relationship, it will resemble an arrow head pointing at the table that must exist first. You might even like to number the tables on your ERD as a guide to their order, for example: +IMAGE GOES HERE Tables.jpg +{{/Labs/03/Images/Tables}} + +Similarly, when entering data into your tables, you must enter the parent rows first, e.g. you would have to record a paper and a student before you could enter an enrolment. **DELETE** and **DROP TABLE** statements would use the reverse order. + +If a foreign key references a composite candidate key, the foreign key will also be composite. + +Which of Scientist, Sample, and Site will have foreign keys? + +What columns would make up each foreign key? + + +Which foreign keys would be mandatory (NOT NULL)? + +In what order would the tables need to be created? + +Modify your **CREATE TABLE** statements to implement these, and test that they are correctly enforced. + diff --git a/tiddlers/content/labs/lab03/_Labs_03_Foreign Keys.md.meta b/tiddlers/content/labs/lab03/_Labs_03_Foreign Keys.md.meta new file mode 100644 index 0000000..d485de3 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Foreign Keys.md.meta @@ -0,0 +1,4 @@ +section: 3.3 +tags: Contents +title: /Labs/03/Foreign Keys +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Getting Set Up.md b/tiddlers/content/labs/lab03/_Labs_03_Getting Set Up.md new file mode 100644 index 0000000..26dd51b --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Getting Set Up.md @@ -0,0 +1,2 @@ +The first task will be to connect to a PostgreSQL database using <> DBMS + diff --git a/tiddlers/content/labs/lab03/_Labs_03_Getting Set Up.md.meta b/tiddlers/content/labs/lab03/_Labs_03_Getting Set Up.md.meta new file mode 100644 index 0000000..65b501a --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Getting Set Up.md.meta @@ -0,0 +1,4 @@ +section: 1.2 +tags: Contents +title: /Labs/03/Getting Set Up +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Images_ERD.jpg b/tiddlers/content/labs/lab03/_Labs_03_Images_ERD.jpg new file mode 100644 index 0000000..ea355b7 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Images_ERD.jpg Binary files differ diff --git a/tiddlers/content/labs/lab03/_Labs_03_Images_ERD.jpg.meta b/tiddlers/content/labs/lab03/_Labs_03_Images_ERD.jpg.meta new file mode 100644 index 0000000..ac8f077 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Images_ERD.jpg.meta @@ -0,0 +1,3 @@ +tags: image +title: /Labs/03/Images/ERD +type: image/jpeg \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Images_Tables.jpg b/tiddlers/content/labs/lab03/_Labs_03_Images_Tables.jpg new file mode 100644 index 0000000..0cfb2e1 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Images_Tables.jpg Binary files differ diff --git a/tiddlers/content/labs/lab03/_Labs_03_Importing Data.md b/tiddlers/content/labs/lab03/_Labs_03_Importing Data.md new file mode 100644 index 0000000..36f6846 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Importing Data.md @@ -0,0 +1,7 @@ +You should now have a complete implementation of the first three tables. A useful next step is to import data from the existing spreadsheet into your database tables to populate them. Refer to the instructional video on Blackboard on how to do this, and pay particular attention to the treatment of dates and date formats, and missing values and nulls. + +Further Reading + +• **CONSTRAINT** and other Commands: + +https://www.h2database.com/html/commands.html diff --git a/tiddlers/content/labs/lab03/_Labs_03_Importing Data.md.meta b/tiddlers/content/labs/lab03/_Labs_03_Importing Data.md.meta new file mode 100644 index 0000000..d150a7c --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Importing Data.md.meta @@ -0,0 +1,4 @@ +section: 3.5 +tags: Contents +title: /Labs/03/Importing Data +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Lab 3_ Building a Relational Database.tid b/tiddlers/content/labs/lab03/_Labs_03_Lab 3_ Building a Relational Database.tid new file mode 100644 index 0000000..13b9309 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Lab 3_ Building a Relational Database.tid @@ -0,0 +1,14 @@ +tags: lab toc +title: /Labs/03/Lab 3: Building a Relational Database +type: text/vnd.tiddlywiki + +
Click the <> button below to open all of the sections for this lab.
+ +!! Contents + +<$set name="path" value="/Labs/03/"> + +<$macrocall $name="contents-tree" path=<> /> +
<$macrocall $name="openByPath" path=<> />
+ + \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Part 1_ Building a Relational Database.md b/tiddlers/content/labs/lab03/_Labs_03_Part 1_ Building a Relational Database.md new file mode 100644 index 0000000..0089d9e --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Part 1_ Building a Relational Database.md @@ -0,0 +1,14 @@ +# Introduction +Over the next few labs, you will learn how to create and use a database using the SQL language a PostgreSQL database and the DBeaver database management system. Databases form the foundation of most information systems, and they provide a powerful and consistent way to organise and manage large volumes of shared data. + +### Today’s Learning Outcomes +* Understanding ERDs +* Connecting to PosgreSQL database using DBeaver DBMS +* Create a blank database +* SQL CREATE TABLE statements +* SQL data types +* Default Column Values +* Mandatory columns +* Populate and query tables + + diff --git a/tiddlers/content/labs/lab03/_Labs_03_Part 1_ Building a Relational Database.md.meta b/tiddlers/content/labs/lab03/_Labs_03_Part 1_ Building a Relational Database.md.meta new file mode 100644 index 0000000..e1dbc7a --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Part 1_ Building a Relational Database.md.meta @@ -0,0 +1,3 @@ +tags: Contents +title: /Labs/03/Part 1: Building a Relational Database +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Running Code in a DBMS.md b/tiddlers/content/labs/lab03/_Labs_03_Running Code in a DBMS.md new file mode 100644 index 0000000..7ff1fee --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Running Code in a DBMS.md @@ -0,0 +1,18 @@ +SQL is a text-based command language for database work. Each SQL statement instructs the database server to perform some task, such as creating a new table or deleting a row. SQL statements begin with one or two keywords that identify the action to be performed, and end with a semicolon (“;”). +Other clauses and expressions can appear in between. +Statements can span lines, and all whitespace is equivalent. SQL is case-insensitive, except for character strings, and identifiers written inside double-quotation marks. +You can annotate your code with comments, which will be ignored by the system. Both line and block comments are supported: + + +```plaintext +-- This is a single-line comment. + +/* This is + a multi-line + comment. */ +``` +The code you write has no effect until you run it. For normal development and testing, you should run only the current statement (the one nearest the text cursor), by typing Ctrl+Enter. You can also execute a group of statements by selecting them and pressing the F5 key. To run the entire file as a script, press F5 with nothing selected. + +NOTE: The code you see in the editor in front of you does not necessarily match what is in the database. Use the Database Navigator panel at the left to see the current state of the database (remember to right click and refresh to get an updated view)). You should also save a copy of your SQL source code for future reference (and make sure to save often!). + +NOTE: when asked to write an SQL script, you must test it thoroughly to ensure that it runs without errors as a batch. Pay particular attention to the order of the statements, and don’t assume that the script will run just because you have tested the statements individually! diff --git a/tiddlers/content/labs/lab03/_Labs_03_Running Code in a DBMS.md.meta b/tiddlers/content/labs/lab03/_Labs_03_Running Code in a DBMS.md.meta new file mode 100644 index 0000000..bc395b9 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Running Code in a DBMS.md.meta @@ -0,0 +1,4 @@ +section: 1.4 +tags: Contents +title: /Labs/03/Running Code in a DBMS +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Scenario and Recap.md b/tiddlers/content/labs/lab03/_Labs_03_Scenario and Recap.md new file mode 100644 index 0000000..8ea1206 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Scenario and Recap.md @@ -0,0 +1,6 @@ +Today’s lab continues from last time, taking the ERD for the water quality scenario and beginning to implement it as a real database. The database approach should avoid many of the problems you encountered earlier with ad-hoc management of data, such as duplicated or missing data, inconsistencies in how values are recorded, and fragmented “islands of information”. +Using sample data (Excel spreadsheets and a sample form), information from members of the organisation, and your wits, you analysed the business requirements and designed the ERD shown in the figure. +The next step is to map the ERD to a set of SQL CREATE TABLE statements. +**Note** that details of the Measurement and Measurement Type entities are missing, as these will be completed in your assignment work. +This lab focuses on Scientist, Sample, and Site, and getting the structure, naming, and data types right. We will leave integrity constraints (primary and foreign keys, and additional business rules) for next time, apart from the basic NOT NULL. +{{/Labs/03/Images/ERD}} \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Scenario and Recap.md.meta b/tiddlers/content/labs/lab03/_Labs_03_Scenario and Recap.md.meta new file mode 100644 index 0000000..f9b501e --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Scenario and Recap.md.meta @@ -0,0 +1,4 @@ +section: 1.1 +tags: Contents +title: /Labs/03/Scenario and Recap +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Table and Column Constraints.md b/tiddlers/content/labs/lab03/_Labs_03_Table and Column Constraints.md new file mode 100644 index 0000000..00b8589 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Table and Column Constraints.md @@ -0,0 +1,31 @@ +SQL provides two syntactic forms for defining constraints: column-level (or “inline”) and table-level (or “out-of-line”). The column-level syntax bundles the constraint as part of the column definition, but has the limitation that it can only refer to that one column. The **NOT NULL** constraints you used last time were unnamed, column-level constraints. Here is a column-level primary key constraint: + +```plaintext +Scientist_Num varchar2(6) primary key, +``` +The table-level syntax puts the constraint definition at the end of the **CREATE TABLE** statement, at the same level of nesting as the other columns. Table constraints can apply to multiple columns (within the same table). We recommend that you define all primary and foreign keys as table-level constraints, for clarity and consistency. + +Some SQL dialects also support schema-level constraints, which can involve columns from multiple tables. + +### Constraint Names +Every constraint has a name, which must be unique within the schema. Constraint names are helpful to developers and administrators, and also to end-users, who may see them in error messages. The system does not try to interpret constraint names, however. + +If you do not specify a name for a constraint, the system will generate one automatically. Such names will probably not be very meaningful (e.g. “SYS_C0023877”), so this is not considered good practice. Instead, use the **CONSTRAINT** keyword, followed by a concise but descriptive name, and then the constraint definition, like so: + + +```plaintext +constraint Scientist_PK primary key (Scientist_Num) +``` +A useful convention is to begin the constraint name with the table name, so that the table can be easily identified, and constraints for the same table will sort together. Include the column name(s) if appropriate (usually not for primary and foreign keys), and a brief indication of the purpose of the constraint (e.g. PK for primary key, FK for foreign key, or Range, Valid, or similar for other types). + +If a constraint name is too long, you will see an error. Sensibly abbreviate the name and try again. + +The system only cares about the constraint definition (the last part), and does not try to interpret the name. + +You may choose not to name your NOT NULL constraints, however, as they are very common and error reports a relatively helpful and specific error message for these. +### Testing Constraints + +Just because your code runs without errors does not mean it is correct! When developing any database integrity constraint, you should design some test cases and make sure that they are treated correctly. You should check that invalid data are rejected, but also that valid data are not unintentionally rejected. Test especially around “edge cases”, such as values near the boundary between valid and invalid. For primary key constraints, you should test both the unique and not null properties. + +Normally your test cases will be **INSERT** statements, though you can test **UPDATE** and **DELETE** operations as well. When an operation fails, check the error message carefully to make sure that it failed in the way you expected, and not for some other reason (e.g. missing punctuation). + diff --git a/tiddlers/content/labs/lab03/_Labs_03_Table and Column Constraints.md.meta b/tiddlers/content/labs/lab03/_Labs_03_Table and Column Constraints.md.meta new file mode 100644 index 0000000..792c2d0 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Table and Column Constraints.md.meta @@ -0,0 +1,4 @@ +section: 3.1 +tags: Contents +title: /Labs/03/Table and Column Constraints +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Task _ Implementing the ERD.md b/tiddlers/content/labs/lab03/_Labs_03_Task _ Implementing the ERD.md new file mode 100644 index 0000000..0bb1a2e --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Task _ Implementing the ERD.md @@ -0,0 +1,52 @@ +Now that you have details for the main entities, you can begin writing SQL CREATE TABLE statements, but first, fill out the relevant parts of the worksheet supplied in the Lab. +### Creating a Table +Each entity type in the ERD will become a table in the database. Attributes will become columns. Each column definition within the CREATE TABLE statement begins with the column name and data type. In SQL, lists (such as the columns within a table) are written in parentheses, with commas separating the items, e.g. “(a, b, c)”, so don’t forget the comma after each column (except for the last one). +Here’s an example of a (very minimal!) CREATE TABLE statement that you can use as a guide for writing your own: + + +```plaintext +create table Employee +( + Employee_ID varchar2(6), + First_Name varchar2(20) not null, + Last_Name varchar2(25) not null, + Hire_Date date default sysdate, + Salary number(8,2), + Commission_Percent number(2,2) default 0 +); + +``` +This example has deliberately left out any primary and foreign key constraints, as well as additional constraints implementing business rules such as the salary being greater than 0. We will look at these shortly. +### Table and Column Names +Generally, you should base your SQL names (identifiers) on the specification provided by the ERD. However, SQL identifiers cannot normally contain spaces, which are usually replaced by the underscore character, “_”. There are a few other prohibited characters, and they cannot start with a digit. Identifiers should be descriptive but not too long. + +Identifiers must also be unique, of course (within the scope of the table, for column names, or across the entire database for table and constraint names). + +SQL Developer will apply syntax-highlighting to keywords, but note that not all keywords are reserved words; that is, just because an identifier is highlighted does not necessarily mean you can’t use it as an identifier. SAMPLE is an example of this. + +### Column Data Types +When defining a column, you must choose a suitable data type. Your main choices are: + +• CHAR for fixed-length character strings (default length = 1), e.g. CHAR(3). + +• VARCHAR for variable length strings (size limit required), e.g. VARCHAR(50). H2 will only store the characters needed, so be generous with the size limit. + +• NUMERIC (or equivalently DECIMAL, or NUMBER in H2) for integers and decimal numbers. Specify a precision and scale to control the number of significant figures and decimal places respectively, for example NUMERIC(2) for integers up to 99, or NUMERIC(5,2) for decimal numbers up to 999.99. + +• DATE or TIMESTAMP for date/time data (with optional time zone). These do not require a size. + +• INTERVAL for relative time (durations), such as for lap times in a race, or the playing time of music tracks. + +• BLOB or CLOB for binary or character large objects (such as PDFs, video, and Web pages). + +For comprehensive information on the data types provided by SQL, as well as further useful information about expressing and comparing values, consult the documentation: + +Link required here - maybe w3 ?? + +### Questions +What criteria would you use to choose a data type for a column? + +Why might it be unwise to use a numeric type for storing phone numbers? + + + diff --git a/tiddlers/content/labs/lab03/_Labs_03_Task _ Implementing the ERD.md.meta b/tiddlers/content/labs/lab03/_Labs_03_Task _ Implementing the ERD.md.meta new file mode 100644 index 0000000..10a227a --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Task _ Implementing the ERD.md.meta @@ -0,0 +1,4 @@ +section: 2.0 +tags: Contents +title: /Labs/03/Task : Implementing the ERD +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab03/_Labs_03_Test your connection.md b/tiddlers/content/labs/lab03/_Labs_03_Test your connection.md new file mode 100644 index 0000000..8e177a8 --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Test your connection.md @@ -0,0 +1,28 @@ +Before we start the task for today we will test that we have our set up correct. +Write a simple create table statement. +```plaintext +CREATE TABLE test( +UserName varchar(10) +); +``` +Control Enter will run the statement or use the buttons on the left. +Right click on the gutter between the statements and controls and select line numbers, this can be helpful to find errors. +Check the Navigator window to see if it created ok. No? +Right click on the WaterSample database in the navigator and go refresh. +Can you see table test now? +Try insert some data. +```plaintext +INSERT INTO test (UserName) values ('Dr Dick'); +``` +Control Enter to run the insert statament. +Now we test that it worked by interrogating the database with a SELECT statement. +```plaintext +SELECT * FROM test; +``` +to check it is there. +```plaintext +DROP TABLE test; +``` +Test completed, clear out the the test table and we are ready to start work. + + diff --git a/tiddlers/content/labs/lab03/_Labs_03_Test your connection.md.meta b/tiddlers/content/labs/lab03/_Labs_03_Test your connection.md.meta new file mode 100644 index 0000000..7b55d7b --- /dev/null +++ b/tiddlers/content/labs/lab03/_Labs_03_Test your connection.md.meta @@ -0,0 +1,4 @@ +section: 1.3 +tags: Contents +title: /Labs/03/Test your connection +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab04/_Labs_04_Continue work on second assignment and prepare for next lab.md b/tiddlers/content/labs/lab04/_Labs_04_Continue work on second assignment and prepare for next lab.md new file mode 100644 index 0000000..582ecc7 --- /dev/null +++ b/tiddlers/content/labs/lab04/_Labs_04_Continue work on second assignment and prepare for next lab.md @@ -0,0 +1,13 @@ +You should now have data in three of the five tables, and you should be getting close to completing the CREATE TABLE statements for the two remaining tables (Measurement and Measurement Type). Once you’ve created these two tables, use the skills you learned in this lab to populate them with data as well. Data for the Measurement table are in the CSV file, but you’ll need to think carefully about how to handle outlier values. You’ll need to generate appropriate data for the Measurement Type table based on the types of measurement shown on the sample data form from Lab 03. + +In the next two labs, you’ll learn how to retrieve data from a database. If you want to get a head start, you can experiment with SQL’s SELECT statement. + + Here are some useful links: + +• Oracle’s SQL Language Reference +https://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm +• Documentation for Oracle’s SELECT statement (caution: extremely detailed!) +https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#SQLRF01702 +• An online SQL tutorial that’s a bit more lightweight and interactive +https://www.w3schools.com/sql/ + diff --git a/tiddlers/content/labs/lab04/_Labs_04_Continue work on second assignment and prepare for next lab.md.meta b/tiddlers/content/labs/lab04/_Labs_04_Continue work on second assignment and prepare for next lab.md.meta new file mode 100644 index 0000000..b13cef3 --- /dev/null +++ b/tiddlers/content/labs/lab04/_Labs_04_Continue work on second assignment and prepare for next lab.md.meta @@ -0,0 +1,4 @@ +section: 1.4 +tags: Contents +title: /Labs/04/Continue work on second assignment and prepare for next lab +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab04/_Labs_04_Delete Data.md b/tiddlers/content/labs/lab04/_Labs_04_Delete Data.md new file mode 100644 index 0000000..e9c14f2 --- /dev/null +++ b/tiddlers/content/labs/lab04/_Labs_04_Delete Data.md @@ -0,0 +1,51 @@ +**CAUTION! It’s very easy to delete more data than you intended if you’re not careful. Make sure you save your INSERT statements from Task 1.2 so that you can re-create any data that you delete during this task.** + +Sometimes you also need to remove data from a table. You can do this using SQL’s DELETE statement, which has the following general form: + +```plaintext +delete from +[where ]; +``` +For example, the following will delete the Scientist with the ID 12345: +```plaintext +delete from Scientist +where Scientist_Num = '12345'; +``` + +Check the contents of your Scientist table, run the above DELETE statement, then check the table contents again. (Remember that you may need to modify column names and values to be compatible with your table.) Have the contents of the table changed, and does this match what you expected to happen? If not, why not? + +--- +The WHERE clause is absolutely critical for most delete operations. Only rows that match the specified criteria will be deleted. What will happen if you run the statement delete from Scientist? + +--- + +Deleting data isn’t always this straightforward, though. Run the three INSERT statements below, modifying column names and values as necessary to match your tables. (**Hint**: To run multiple statements, select all of them in the SQL worksheet, then click the Run Script button, which is second from the left in the SQL Worksheet toolbar, and looks like a document with a green triangle on top of it. The selected statements will run in sequence.) + + + +```plaintext +insert into Scientist (Scientist_Num, Surname, Other_Names, Email, Mobile_Phone) +values ('54321', 'Jones', 'Heather', 'hjones@wet.co.nz', '+64 27 297 1619'); + +insert into Site (Site_ID, Region, Description, Latitude, Longitude, + Catchment_Area, Catchment_Height, Altitude) +values ('DN20', 'Dunedin', 'Leith at Clyde St. Br.', -45.867093, 170.516081, + null, null, null); + +insert into Sample (Scientist_Num, Site_ID, Recorded_On, Comments) +values ('54321', 'DN20', to_date('25/11/2016', 'DD/MM/YYYY'), null); + +``` +Now run the following statement: +```plaintext +delete from Site +where Site_ID = 'DN20'; +``` +What happens, and why? + + +--- +How can you work around this? + +--- +After all that inserting and deleting, your data may have become a little messy. It wouldn’t hurt at this point to delete everything from all three tables and rebuild the data from scratch using the INSERT statements that you saved earlier. (You did save them, didn’t you? ) Just delete the data, don’t drop the tables. \ No newline at end of file diff --git a/tiddlers/content/labs/lab04/_Labs_04_Delete Data.md.meta b/tiddlers/content/labs/lab04/_Labs_04_Delete Data.md.meta new file mode 100644 index 0000000..bbcf518 --- /dev/null +++ b/tiddlers/content/labs/lab04/_Labs_04_Delete Data.md.meta @@ -0,0 +1,4 @@ +section: 1.3 +tags: Contents +title: /Labs/04/Delete Data +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab04/_Labs_04_Insert Data.md b/tiddlers/content/labs/lab04/_Labs_04_Insert Data.md new file mode 100644 index 0000000..48295ae --- /dev/null +++ b/tiddlers/content/labs/lab04/_Labs_04_Insert Data.md @@ -0,0 +1,80 @@ +Before you can do anything else, you need to get some data into your tables. This is done using SQL’s INSERT statement, which has the following general form: +```plaintext +insert into
[()] +values (); +``` +The list of column names after the table name is optional, but it’s generally good practice to include it. Why is this good practice? (Hint: Think about what could go wrong if you didn’t include the column names.) + +--- +Can you think of other reasons why you might want to explicitly list the column names? (Hint: Look at the columns in the Sample and Site tables.) + +--- + +Why does it say “list of value expressions” rather than just “list of values” above? What are the advantages of this? + +--- +### Scientist table +First, you’ll load some data into the Scientist table. For example, the following will insert a new Scientist row: + +```plaintext +insert into Scientist (Scientist_Num, Surname, Other_Names, Email, Mobile_Phone) +values ('12345', 'Smith', 'Jane', 'jsmith@wet.co.nz', '+64 22 0191 1468'); +``` +Open DBeaver, connect to your PostgreSQL schema, and run the above INSERT statement on your Scientist table. + +**You may need to modify the column names in the INSERT statement to match your table. You may also need to modify the values to be compatible with the data types in your table, e.g., the values above may be longer than what you’ve allowed for, or you may need to remove the quotes from values that you stored as numbers rather than text (e.g., Scientist_Num). Ask for advice if you’re unsure.** + +If all goes well, you’ll see “1 row inserted.” appear in the Script Output panel at the bottom of the window. +What happens if you try to insert the same row again, and why? (**Hint**: Just run the INSERT statement again. Make sure you fully read the error message that appears in the Script Output panel.) + +--- +Change the data in the INSERT statement to correct the reported issue and try running it again. Did it work this time? If not, why not? + +--- +Continue to correct the data in the INSERT statement until you’re able to insert a second row into the Scientist table. Check that the data are actually there by running the statement +```plaintext +select * from Scientist;. +``` + +You should see two rows appear in the Query Result panel at the bottom of the window. + +Invent some more Scientist data and insert them into the table. Five to ten rows will be plenty. Try to make the data at least somewhat realistic, e.g., don’t just use the same name or mobile number for all rows. Make sure you save a copy of your INSERT statements for future reference (either copy and paste them into a text document, or save the SQL Worksheet contents to a file). +### Site table +You had to invent your own data for the Scientist table, but you already have data for Site table, in the first eight columns of the water quality CSV file from Lab 06. Open it in Excel and scroll down until you find some data in the CatchmentArea, CatchmentHeight, and Altitude columns. Create a few INSERT statements by copying and pasting the corresponding values from the CSV file. Include some rows that have values for CatchmentArea, CatchmentHeight, and Altitude, and some that don’t. For those that don’t, use NULL (without quotes) instead of a value. Make sure that each row is for a different region. Here are a couple of examples to get you started: +```plaintext +insert into Site (Site_ID, Region, Description, Latitude, Longitude, + Catchment_Area, Catchment_Height, Altitude) +values ('TK3', 'Tekapo', 'Opuha at Skipton Br.', -44.07888926, 170.9797435, + 458, 1020, 238); + +insert into Site (Site_ID, Region, Description, Latitude, Longitude, + Catchment_Area, Catchment_Height, Altitude) +values ('AX1', 'Alexandra', 'Clutha at Luggate Br.', -44.72911152, 169.2805516, + null, null, null); +``` + +Writing individual INSERT statements will clearly get quite tedious if there are a lot of rows to insert. For large amounts of data, you could use something to generate INSERT statements for you, but the best option is to use a bulk data loading tool that can directly import formats like CSV. + +Is there a way to get Excel to generate INSERT statements for you, which you can then paste into SQL Developer? + +--- +Does DBeaver provide any way to import data in bulk from a disk file? + +--- +There are about 20 sites listed in the CSV data. Load the data for all of these into the Site table using one of the three methods mentioned above (manually-written INSERTs, generated INSERTs, or bulk import). +### Sample table +Finally, you need to load the relevant CSV data into the Sample table. Here are some tips: + +• The relevant columns in the CSV data are SiteID and RecordedOn. The latter contains a date/time value. To get this into Oracle, you’ll need to convert the value into an Oracle DATE using the TO_DATE function, e.g.,: to_date('6/01/2015 8:45', 'DD/MM/YYYY HH:MI'). + +• The CSV data specifies the site ID for each sample, but not the scientist. You’ll need to assign this yourself, drawing on the Scientist data you created. + +• You’ll need to generate a sample ID value. You could just make this up yourself, but better options include using Excel to generate the IDs, or using a sequence (if the sample ID is stored as a NUMBER). + +• There are no comments for any of the samples in the CSV data. For now, just leave the Comments column null. (Hint: If a column is allowed to be null, you can omit it from the list of column names in the INSERT statement, and the database will automatically insert a null into that column if it doesn’t have a default value.) + +Why did we ask you to load data into the Scientist and Site tables before loading data into the Sample table? What would have happened if you tried to insert into Sample first? + +--- + + diff --git a/tiddlers/content/labs/lab04/_Labs_04_Insert Data.md.meta b/tiddlers/content/labs/lab04/_Labs_04_Insert Data.md.meta new file mode 100644 index 0000000..2e567d1 --- /dev/null +++ b/tiddlers/content/labs/lab04/_Labs_04_Insert Data.md.meta @@ -0,0 +1,4 @@ +section: 1.2 +tags: Contents +title: /Labs/04/Insert Data +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab04/_Labs_04_Lab 4_ Inserting and Deleting Data.tid b/tiddlers/content/labs/lab04/_Labs_04_Lab 4_ Inserting and Deleting Data.tid new file mode 100644 index 0000000..8f9406a --- /dev/null +++ b/tiddlers/content/labs/lab04/_Labs_04_Lab 4_ Inserting and Deleting Data.tid @@ -0,0 +1,14 @@ +tags: lab toc +title: /Labs/04/Lab 4: Inserting and Deleting Data +type: text/vnd.tiddlywiki + +
Click the <> button below to open all of the sections for this lab.
+ +!! Contents + +<$set name="path" value="/Labs/04/"> + +<$macrocall $name="contents-tree" path=<> /> +
<$macrocall $name="openByPath" path=<> />
+ + \ No newline at end of file diff --git a/tiddlers/content/labs/lab04/_Labs_04_Scenario and Setup.md b/tiddlers/content/labs/lab04/_Labs_04_Scenario and Setup.md new file mode 100644 index 0000000..ac2b87a --- /dev/null +++ b/tiddlers/content/labs/lab04/_Labs_04_Scenario and Setup.md @@ -0,0 +1,13 @@ +Over the next few weeks, you’ll explore ways to create databases to store and manipulate data in a consistent manner. This week, we’ll look at how to manipulate data in a database, focusing on the Create (SQL INSERT) and Delete (SQL DELETE) data manipulation operations. + +All the work that you do today contributes towards completing the second assignment. + +It’s in your interest to make notes in this lab, as the notes will help you with the required components of the assignment. +### Task 0: Scenario and setup +Today’s lab continues the water quality scenario from where you left off last time. You’ll need to complete Lab 03 before you can complete the tasks in this lab. You created tables corresponding to the Scientist, Sample, and Site entities in the following ERD: + +{{/Labs/03/Images/ERD}} + +If you haven’t already done so, you’ll complete the details for the Measurement and Measurement Type tables as part of your work for the second assignment. This lab focuses on the Scientist, Sample, and Site tables. + +You’ll also need the water quality CSV data file and the sample data form from Lab 04 on Blackboard. Download copies of these if you don’t have them already. diff --git a/tiddlers/content/labs/lab04/_Labs_04_Scenario and Setup.md.meta b/tiddlers/content/labs/lab04/_Labs_04_Scenario and Setup.md.meta new file mode 100644 index 0000000..b72e586 --- /dev/null +++ b/tiddlers/content/labs/lab04/_Labs_04_Scenario and Setup.md.meta @@ -0,0 +1,4 @@ +section: 1.1 +tags: Contents +title: /Labs/04/Scenario and Setup +type: text/x-markdown \ No newline at end of file diff --git "a/tiddlers/content/labs/lab05/_Labs_05_Lab 5_ Querying Data \0501\051.tid" "b/tiddlers/content/labs/lab05/_Labs_05_Lab 5_ Querying Data \0501\051.tid" new file mode 100644 index 0000000..46ce90f --- /dev/null +++ "b/tiddlers/content/labs/lab05/_Labs_05_Lab 5_ Querying Data \0501\051.tid" @@ -0,0 +1,14 @@ +tags: lab toc +title: /Labs/05/Lab 5: Querying Data (1) +type: text/vnd.tiddlywiki + +
Click the <> button below to open all of the sections for this lab.
+ +!! Contents + +<$set name="path" value="/Labs/05/"> + +<$macrocall $name="contents-tree" path=<> /> +
<$macrocall $name="openByPath" path=<> />
+ + \ No newline at end of file diff --git a/tiddlers/content/labs/lab05/_Labs_05_Scenario and Setup.md b/tiddlers/content/labs/lab05/_Labs_05_Scenario and Setup.md new file mode 100644 index 0000000..1ea10b3 --- /dev/null +++ b/tiddlers/content/labs/lab05/_Labs_05_Scenario and Setup.md @@ -0,0 +1,12 @@ +This week, you’ll look at how to manipulate data in an Oracle database, focusing on the Read (SQL SELECT) operation. Of the data manipulation sublanguage statements, SELECT is by far the most complicated and interesting (INSERT, UPDATE and DELETE are much more straightforward). + +All the work that you do today contributes towards completing the second assignment. It’s in your interest to make notes in this lab, as the notes will help you with the required components of the assignment. + +### Task 0: Scenario and setup +Today’s lab continues the water quality scenario from where you left off last time. You’ll need to complete Lab 04 before you can complete the tasks in this lab (a solution is available on Blackboard if you need to catch up). You created database tables corresponding to the Scientist, Sample, and Site entities in the ERD below, and filled them with data. + +{{/Labs/03/Images/ERD}} + +If you haven’t already done so, you’ll complete the details for the Measurement and Measurement Type tables as part of your work for the second assignment. This lab focuses on the Scientist, Sample, and Site tables. + +You’ll also need the water quality CSV data file and the sample data form from Lab 04 on Blackboard. Download copies of these if you don’t have them already. diff --git a/tiddlers/content/labs/lab05/_Labs_05_Scenario and Setup.md.meta b/tiddlers/content/labs/lab05/_Labs_05_Scenario and Setup.md.meta new file mode 100644 index 0000000..9404b08 --- /dev/null +++ b/tiddlers/content/labs/lab05/_Labs_05_Scenario and Setup.md.meta @@ -0,0 +1,4 @@ +section: 1.1 +tags: Contents +title: /Labs/05/Scenario and Setup +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab05/_Labs_05_Task 1_ Select Exercises.md b/tiddlers/content/labs/lab05/_Labs_05_Task 1_ Select Exercises.md new file mode 100644 index 0000000..2f96348 --- /dev/null +++ b/tiddlers/content/labs/lab05/_Labs_05_Task 1_ Select Exercises.md @@ -0,0 +1,7 @@ +Write SQL SELECT statements to answer the following questions. **Make sure you save your code somewhere for future reference.** Note that an empty query result may not necessarily indicate an error. If you find there are no data in your schema that satisfy a query, insert some that do and try again. +1. List all sites in order of region and description. +2. List all samples that have comments, in reverse date order. (Hint: Look at the IS NULL operator.) +3. List the site ID, region, description, latitude and longitude of sites whose altitude is between 0 and 100 metres. Save this query as a view called Low_Altitude_Sites. +What happens to sites that have no (i.e., null) altitude, and why? + +4. Find the mobile phone numbers of scientists Terry Towel and Jane Smith (using their name, not ID!). diff --git a/tiddlers/content/labs/lab05/_Labs_05_Task 1_ Select Exercises.md.meta b/tiddlers/content/labs/lab05/_Labs_05_Task 1_ Select Exercises.md.meta new file mode 100644 index 0000000..72c092f --- /dev/null +++ b/tiddlers/content/labs/lab05/_Labs_05_Task 1_ Select Exercises.md.meta @@ -0,0 +1,4 @@ +section: 1.3 +tags: Contents +title: /Labs/05/Task 1: Select Exercises +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab05/_Labs_05_Task 1_ The basic SELECT statement.md b/tiddlers/content/labs/lab05/_Labs_05_Task 1_ The basic SELECT statement.md new file mode 100644 index 0000000..deb8838 --- /dev/null +++ b/tiddlers/content/labs/lab05/_Labs_05_Task 1_ The basic SELECT statement.md @@ -0,0 +1,65 @@ +### Select +You can think of the SELECT statement as roughly equivalent to a “print” statement in other languages: it’s simply a way to output some information from the database. What you output could be as simple as a single character literal value, or complex collection of column values, calculations and summarised statistical data. + +The basic form of the SELECT statement looks like this: +```plaintext +select []] +from
+[where ] +[order by ]; +``` +The SELECT clause is simply a comma-separated list of expressions, so you can write queries like the following: +```plaintext +select 'Hello, World!', 18 * 365, sysdate from Dual; +``` +What does this query output? + +--- +Describe the structure and contents of the Dual table? Note:The Wikipedia page for the Dual table (https://en.wikipedia.org/wiki/DUAL_table) may be useful in answering these questions. + +Why is this table needed? (Hint: It has to do with the syntax of the SELECT statement.) + +--- +### From +The FROM clause specifies a table-valued expression, which tells the query where to get its data. In the simplest case, the FROM clause identifies a single table, but as you’ll see, it supports much more complex expressions. +```plaintext +select Site_ID, Latitude, Longitude +from Site; +``` +select * will return all the columns that exist in the FROM expression. + +Can you think of any reasons why it might not be a good idea to use **select** * in production code (i.e., deployed and running for real)? + +--- +### Where +The WHERE clause is used to restrict data based on some condition. Only those rows returned by the FROM clause for which the condition evaluates to true will be output. +```plaintext +select * +from Site +where Altitude >= 100; +``` +Sensible sorting of the output rows can mean the difference between a useful query result and an unintelligible one. Use the ORDER BY clause to specify a sort key. If you need to sort on multiple columns, specify them as a comma-separated list, with the most significant column(s) first. For descending order, specify DESC. For example: +```plaintext +select * +from Scientist +order by Surname, Other_Names; +``` +```plaintext +select * +from Site +order by Altitude desc; +``` +Almost all queries have a sensible row ordering, so you should get into the habit of supplying an ORDER BY clause for any queries that you are likely to use more than once. +Queries that might be commonly re-used can be stored as views, for example: +```plaintext +create view High_Altitude_Sites as + select * + from Site + where Altitude >= 100 + order by Description; +``` +Then all you have to do is: +```plaintext +select * from High_Altitude_Sites; +``` + diff --git a/tiddlers/content/labs/lab05/_Labs_05_Task 1_ The basic SELECT statement.md.meta b/tiddlers/content/labs/lab05/_Labs_05_Task 1_ The basic SELECT statement.md.meta new file mode 100644 index 0000000..d38bbac --- /dev/null +++ b/tiddlers/content/labs/lab05/_Labs_05_Task 1_ The basic SELECT statement.md.meta @@ -0,0 +1,4 @@ +section: 1.2 +tags: Contents +title: /Labs/05/Task 1: The basic SELECT statement +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab05/_Labs_05_Task 2_ Join Exercises.md b/tiddlers/content/labs/lab05/_Labs_05_Task 2_ Join Exercises.md new file mode 100644 index 0000000..72e38ef --- /dev/null +++ b/tiddlers/content/labs/lab05/_Labs_05_Task 2_ Join Exercises.md @@ -0,0 +1,8 @@ +Write SQL SELECT statements to answer the following questions. **Make sure you save your code somewhere for future reference.** + +Note that an empty query result may not necessarily indicate an error. +1. List the site ID, date, region, description, and altitude of all samples, in date order. +2. List all samples that were gathered at an altitude of between 0 and 100 metres. (Hint: This should look familiar. Can you re-use something you’ve already done?) +3. List the names (in alphabetical order) of scientists who have gathered samples in the Dunedin region. +4. Output full details of all samples, including the scientist and the site. +5. List the names (in alphabetical order) of scientists who haven’t yet gathered a sample. (Hint: You’ll need to use an outer join. Remember that unmatched columns are filled with nulls.) diff --git a/tiddlers/content/labs/lab05/_Labs_05_Task 2_ Join Exercises.md.meta b/tiddlers/content/labs/lab05/_Labs_05_Task 2_ Join Exercises.md.meta new file mode 100644 index 0000000..2efe494 --- /dev/null +++ b/tiddlers/content/labs/lab05/_Labs_05_Task 2_ Join Exercises.md.meta @@ -0,0 +1,4 @@ +section: 2.2 +tags: Contents +title: /Labs/05/Task 2: Join Exercises +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab05/_Labs_05_Task 2_ Joins.md b/tiddlers/content/labs/lab05/_Labs_05_Task 2_ Joins.md new file mode 100644 index 0000000..e1a5a72 --- /dev/null +++ b/tiddlers/content/labs/lab05/_Labs_05_Task 2_ Joins.md @@ -0,0 +1,40 @@ +Very often, you need information from two or more tables. The relational *join* operation combines related data from multiple tables, returning a single table. Specifically, join combines pairs of rows from the two tables that have the same value in some common set of columns—most often the foreign key in one and the referenced primary key in the other. For example, to see full details of samples and the scientists that gathered them: +```plaintext +select * +from Sample inner join Scientist using (Scientist_Num); +``` +Run the above query. Why doesn’t the Scientist_Num column appear twice, given that it exists in both tables? + +--- +What happens if you swap the order of Sample and Scientist in the FROM clause? Is the difference important? + +--- +The most common type of join is the inner join, which retrieves only matching pairs. Unmatched rows don’t appear in the result. If you want unmatched rows to be included, there are several kinds of outer join (syntax: [LEFT|RIGHT|FULL] OUTER JOIN) that can be used instead. These usually introduce nulls into the result. + +If the join columns have different names, you’ll need to specify join conditions with ON instead of USING. For example, if the Scientist_Num foreign key in Sample was instead called Scientist_No: +```plaintext +select * +from Sample inner join Scientist on (Scientist_Num = Scientist_No); +``` +Rewrite the first join above with ON instead of USING. What additional changes do you need to make to get this to work, and why? + +--- +Joins involving more than two tables can be done simply by adding more join operations in the FROM clause. For example: +```plaintext +select * +from Sample inner join Scientist using (Scientist_Num) + inner join Site using (Site_ID); +``` +You should always have an ERD handy when writing queries. The ERD is like a map of the database, giving you an overview of all tables and their columns, and also the relationships among them. Each relationship you traverse will add a join to your query. If you need data from two distant tables, you may have to join through a number of intervening tables as well. To be useful, the ERD should explicitly include all foreign key attributes, etc. + +**Here are some hints for writing joins:** + +• Work incrementally. Plan the path using the ERD, and implement one join at a time, testing at each step. If there are multiple paths, make sure you choose the right one! +• Don’t do more joins than necessary. Use the foreign key attributes! +• Check the number of rows returned by the query. If you see more rows than the largest table being joined, you have a problem. +• Fill in the SELECT clause last (just use select * while testing). + + + + + diff --git a/tiddlers/content/labs/lab05/_Labs_05_Task 2_ Joins.md.meta b/tiddlers/content/labs/lab05/_Labs_05_Task 2_ Joins.md.meta new file mode 100644 index 0000000..19ad038 --- /dev/null +++ b/tiddlers/content/labs/lab05/_Labs_05_Task 2_ Joins.md.meta @@ -0,0 +1,4 @@ +section: 2.1 +tags: Contents +title: /Labs/05/Task 2: Joins +type: text/x-markdown \ No newline at end of file diff --git a/tiddlers/content/labs/lab05/_Labs_05_Task 3_ Continue work on second assignment and prepare for next lab.md b/tiddlers/content/labs/lab05/_Labs_05_Task 3_ Continue work on second assignment and prepare for next lab.md new file mode 100644 index 0000000..c066d52 --- /dev/null +++ b/tiddlers/content/labs/lab05/_Labs_05_Task 3_ Continue work on second assignment and prepare for next lab.md @@ -0,0 +1,3 @@ +You should now have a reasonable idea of how basic querying works in SQL. If you found this easy and would like an extra challenge, learn about subqueries and how they can be used (for example, subqueries can provide a different, and perhaps slightly more intuitive, way to answer the query above about scientists who have no samples). Otherwise, work on the queries and views for Assignment 2. + +In the next lab, you’ll learn how to use aggregate functions and grouping (GROUP BY) to summarise data diff --git a/tiddlers/content/labs/lab05/_Labs_05_Task 3_ Continue work on second assignment and prepare for next lab.md.meta b/tiddlers/content/labs/lab05/_Labs_05_Task 3_ Continue work on second assignment and prepare for next lab.md.meta new file mode 100644 index 0000000..0547ee1 --- /dev/null +++ b/tiddlers/content/labs/lab05/_Labs_05_Task 3_ Continue work on second assignment and prepare for next lab.md.meta @@ -0,0 +1,4 @@ +section: 3.0 +tags: Contents +title: /Labs/05/Task 3: Continue work on second assignment and prepare for next lab +type: text/x-markdown \ No newline at end of file