From e09aa90e0e388d4c42e38381961bc3fa1771cff3 Mon Sep 17 00:00:00 2001
From: Nguyễn Gia Phong <mcsinyx@disroot.org>
Date: Wed, 8 Jul 2020 14:11:07 +0700
Subject: [usth/ICT2.{5,12}] Process images and base data

---
 usth/ICT2.12/labwork/1             |  55 ++++++++++++++++++++
 usth/ICT2.12/labwork/1.pdf         | Bin 0 -> 1376916 bytes
 usth/ICT2.12/labwork/2             |  54 +++++++++++++++++++
 usth/ICT2.12/labwork/2.pdf         | Bin 0 -> 1348500 bytes
 usth/ICT2.12/labwork/dino-gang.jpg | Bin 0 -> 53971 bytes
 usth/ICT2.12/recipe                |   1 +
 usth/ICT2.5/cheese-shop            |   1 +
 usth/ICT2.5/practical/1.pdf        | Bin 0 -> 833453 bytes
 usth/ICT2.5/practical/1.sql        |  58 +++++++++++++++++++++
 usth/ICT2.5/practical/2.pdf        | Bin 0 -> 191759 bytes
 usth/ICT2.5/practical/2.sql        |  69 ++++++++++++++++++++++++
 usth/ICT2.5/practical/3.pdf        | Bin 0 -> 91823 bytes
 usth/ICT2.5/practical/3.sql        | 104 +++++++++++++++++++++++++++++++++++++
 usth/ICT2.5/practical/mywind       |   1 +
 14 files changed, 343 insertions(+)
 create mode 100755 usth/ICT2.12/labwork/1
 create mode 100644 usth/ICT2.12/labwork/1.pdf
 create mode 100755 usth/ICT2.12/labwork/2
 create mode 100644 usth/ICT2.12/labwork/2.pdf
 create mode 100644 usth/ICT2.12/labwork/dino-gang.jpg
 create mode 160000 usth/ICT2.12/recipe
 create mode 160000 usth/ICT2.5/cheese-shop
 create mode 100644 usth/ICT2.5/practical/1.pdf
 create mode 100644 usth/ICT2.5/practical/1.sql
 create mode 100644 usth/ICT2.5/practical/2.pdf
 create mode 100644 usth/ICT2.5/practical/2.sql
 create mode 100644 usth/ICT2.5/practical/3.pdf
 create mode 100644 usth/ICT2.5/practical/3.sql
 create mode 160000 usth/ICT2.5/practical/mywind

diff --git a/usth/ICT2.12/labwork/1 b/usth/ICT2.12/labwork/1
new file mode 100755
index 0000000..2590192
--- /dev/null
+++ b/usth/ICT2.12/labwork/1
@@ -0,0 +1,55 @@
+#!/usr/bin/env python3
+from cv2 import (
+    THRESH_BINARY, ADAPTIVE_THRESH_MEAN_C,
+    adaptiveThreshold as adaptive_threshold, calcHist as calc_hist, blur,
+    equalizeHist as equalize_hist, GaussianBlur as gaussian_blur,
+    imread, imshow, Laplacian as laplacian, medianBlur as median_blur,
+    resize, Sobel as sobel, threshold, waitKey as wait_key)
+from numpy import uint8
+
+FILENAME = 'dino-gang.jpg'
+GREYSCALE_COEFF = 0.2126, 0.7152, 0.0722
+THRESHOLD, WHITE = 128, 255
+BLUR_KSIZE = 6, 9
+
+
+def disp(image, name):
+    """Display the given image."""
+    imshow(name, image.astype(uint8))
+    wait_key()
+
+
+# Exercise 1
+image = imread(FILENAME)
+disp(image, 'original')
+
+# Exercise 2
+disp(resize(image, (512, 512)), 'square')
+
+# Exercise 3
+# I'm going to ignore about cv2.IMREAD_GRAYSCALE
+grey_image = uint8(image.dot(GREYSCALE_COEFF))
+disp(grey_image, 'grey')
+
+# Exercise 4
+a, b = 1/2, 1/3
+disp(image*a+b, 'brightness adjusted')
+
+# Exercise 5
+disp(threshold(grey_image, THRESHOLD, WHITE, THRESH_BINARY)[-1],
+     'binary threshold')
+disp(adaptive_threshold(grey_image, WHITE, ADAPTIVE_THRESH_MEAN_C,
+                        THRESH_BINARY, 25, 12), 'adaptive threshold')
+
+# Exercise 6
+hist = calc_hist([grey_image], [0], None, [256], [0, 256])
+disp(equalize_hist(grey_image), 'equalized')
+
+# Exercise 7
+disp(blur(image, BLUR_KSIZE), 'blur')
+disp(gaussian_blur(image, (0, 0), 5), 'Gaussian blur')
+disp(median_blur(image, 7), 'median blur')
+
+# Exercise 8
+disp(laplacian(image, 2), 'Laplacian')
+disp(sobel(image, 2, 1, 1), 'Sobel')
diff --git a/usth/ICT2.12/labwork/1.pdf b/usth/ICT2.12/labwork/1.pdf
new file mode 100644
index 0000000..b7286d0
Binary files /dev/null and b/usth/ICT2.12/labwork/1.pdf differ
diff --git a/usth/ICT2.12/labwork/2 b/usth/ICT2.12/labwork/2
new file mode 100755
index 0000000..af253ed
--- /dev/null
+++ b/usth/ICT2.12/labwork/2
@@ -0,0 +1,54 @@
+#!/usr/bin/env python3
+from cv2 import (
+    COLOR_BGR2GRAY, KMEANS_RANDOM_CENTERS, THRESH_BINARY, TERM_CRITERIA_EPS,
+    TERM_CRITERIA_MAX_ITER, Canny as canny, cvtColor as cvt_color,
+    HoughLines as hough_lines, imread, imshow, inRange as in_range, kmeans,
+    Laplacian as laplacian, line, Sobel as sobel, threshold,
+    waitKey as wait_key)
+from numpy import cos, float32, pi, sin, uint8
+
+FILENAME = 'dino-gang.jpg'
+THRESHOLD, WHITE = 128, 255
+CANNY_THRESH = 69
+
+
+def disp(image, name):
+    """Display the given image."""
+    imshow(name, image.astype(uint8))
+    wait_key()
+
+
+image = imread(FILENAME)
+disp(image, 'original')
+
+# Exercise 1
+# Requiring all three channels to be greater than THRESHOLD
+# using in_range produces a blacker result (fewer white points).
+# The information inferred by human (me) is less clear.
+grey = cvt_color(image, COLOR_BGR2GRAY)
+disp(threshold(grey, THRESHOLD, WHITE, THRESH_BINARY)[-1], 'threshold')
+disp(in_range(image, (THRESHOLD,)*3, (WHITE,)*3), 'in range')
+
+# Exercise 2
+disp(laplacian(image, 2), 'Laplacian')
+disp(sobel(image, 2, 1, 1), 'Sobel')
+# Canny produces a lot visible edge comparing to Laplacian and Sobel.
+edges = canny(image, CANNY_THRESH, CANNY_THRESH*2)
+disp(edges, 'canny')
+
+# Exercise 3
+pixels = float32(image.reshape((-1, 3)))
+criteria = TERM_CRITERIA_EPS|TERM_CRITERIA_MAX_ITER, 10, 1.0
+ret, labels, centers = kmeans(pixels, 3, None, criteria,
+                              10, KMEANS_RANDOM_CENTERS)
+# Compared to global threshold, this has colors.
+# I am unsure how this relate to adaptive threshold though.
+disp(centers[labels.flatten()].reshape(image.shape), 'seg')
+
+# Exercise 4
+for ((rho, theta),) in hough_lines(edges, 1, pi/180, THRESHOLD):
+    a, b = cos(theta), sin(theta)
+    x, y = a*rho, b*rho
+    line(image, (int(x-b*1000), int(y+a*1000)), (int(x+b*1000), int(y-a*1000)),
+         (0, 0, 255), 2)
+disp(image, 'hough')
diff --git a/usth/ICT2.12/labwork/2.pdf b/usth/ICT2.12/labwork/2.pdf
new file mode 100644
index 0000000..2471ca1
Binary files /dev/null and b/usth/ICT2.12/labwork/2.pdf differ
diff --git a/usth/ICT2.12/labwork/dino-gang.jpg b/usth/ICT2.12/labwork/dino-gang.jpg
new file mode 100644
index 0000000..1d8001a
Binary files /dev/null and b/usth/ICT2.12/labwork/dino-gang.jpg differ
diff --git a/usth/ICT2.12/recipe b/usth/ICT2.12/recipe
new file mode 160000
index 0000000..d1eff3a
--- /dev/null
+++ b/usth/ICT2.12/recipe
@@ -0,0 +1 @@
+Subproject commit d1eff3a50da29e8364162dabdd622789e90cf29a
diff --git a/usth/ICT2.5/cheese-shop b/usth/ICT2.5/cheese-shop
new file mode 160000
index 0000000..63ad0c3
--- /dev/null
+++ b/usth/ICT2.5/cheese-shop
@@ -0,0 +1 @@
+Subproject commit 63ad0c373de0f57de4cd8032e0d6703cb2d2d41e
diff --git a/usth/ICT2.5/practical/1.pdf b/usth/ICT2.5/practical/1.pdf
new file mode 100644
index 0000000..510c83e
Binary files /dev/null and b/usth/ICT2.5/practical/1.pdf differ
diff --git a/usth/ICT2.5/practical/1.sql b/usth/ICT2.5/practical/1.sql
new file mode 100644
index 0000000..7fb1394
--- /dev/null
+++ b/usth/ICT2.5/practical/1.sql
@@ -0,0 +1,58 @@
+CREATE DATABASE Company;
+USE Company;
+
+CREATE TABLE employee (
+  fname char(255),
+  minit char(255),
+  lname char(255),
+  ssn int(9) PRIMARY KEY,
+  bdate date,
+  address char(255),
+  sex bigint(255),
+  salary int(9),
+  super_ssn int(9),
+  dno int(9),
+  FOREIGN KEY (super_ssn) REFERENCES employee(ssn));
+
+CREATE TABLE department (
+  dname char(255),
+  dnumber int(9) PRIMARY KEY,
+  mgr_ssn int(9),
+  mgr_start_date date,
+  FOREIGN KEY (mgr_ssn) REFERENCES employee(ssn));
+
+ALTER TABLE employee ADD FOREIGN KEY (dno) REFERENCES department(dnumber);
+
+CREATE TABLE dept_locations (
+  dnumber int(9),
+  dlocation char(255),
+  PRIMARY KEY (dnumber, dlocation),
+  FOREIGN KEY (dnumber) REFERENCES department(dnumber));
+
+CREATE TABLE project (
+  pname char(255),
+  pnumber int(9) PRIMARY KEY,
+  plocation char(255),
+  dnum int(9),
+  FOREIGN KEY (dnum) REFERENCES department(dnumber));
+
+CREATE TABLE works_on (
+  essn int(9),
+  pno int(9),
+  hours int(9),
+  PRIMARY KEY (essn, pno),
+  FOREIGN KEY (essn) REFERENCES employee(ssn),
+  FOREIGN KEY (pno) REFERENCES project(pnumber));
+
+CREATE TABLE dependant (
+  essn int(9),
+  dependant_name char(255),
+  sex bigint(255),
+  bdate date,
+  relationship char(255),
+  PRIMARY KEY (essn, dependant_name),
+  FOREIGN KEY (essn) REFERENCES employee(ssn));
+
+ALTER TABLE employee ADD partner_ssn int(9);
+
+DROP DATABASE Company;
diff --git a/usth/ICT2.5/practical/2.pdf b/usth/ICT2.5/practical/2.pdf
new file mode 100644
index 0000000..924fffc
Binary files /dev/null and b/usth/ICT2.5/practical/2.pdf differ
diff --git a/usth/ICT2.5/practical/2.sql b/usth/ICT2.5/practical/2.sql
new file mode 100644
index 0000000..23cbf4b
--- /dev/null
+++ b/usth/ICT2.5/practical/2.sql
@@ -0,0 +1,69 @@
+SOURCE mywind/northwind-default-current-timestamp.sql;
+SOURCE mywind/northwind-data.sql;
+
+SELECT *
+FROM products
+WHERE discontinued=1;
+
+SELECT *
+FROM products
+ORDER BY list_price
+LIMIT 4;
+
+SELECT id, product_name, list_price
+FROM products
+WHERE list_price
+BETWEEN 15 AND 25;
+
+SELECT id, CONCAT(first_name, ' ', last_name) AS name
+FROM employees;
+
+SELECT id, first_name, last_name
+FROM employees
+WHERE first_name LIKE 'A%';
+
+SELECT COUNT(DISTINCT city) AS 'number of different cities'
+FROM employees;
+
+SELECT DISTINCT ship_name
+FROM orders;
+
+SELECT MIN(list_price), MAX(list_price)
+from products;
+
+SELECT COUNT(id) AS 'number of current products'
+FROM products
+WHERE discontinued=0;
+
+SELECT AVG(list_price), STD(list_price)
+FROM products;
+
+SELECT product_name, list_price
+FROM products
+WHERE list_price > (SELECT AVG(list_price) FROM products);
+
+INSERT INTO suppliers (company, last_name, first_name, city, country_region)
+VALUES ('Habeco', 'Nguyễn', 'Hồng Linh', 'Hanoi', 'Vietnam');
+
+INSERT INTO products (
+  supplier_ids,
+  product_code,
+  product_name,
+  list_price,
+  discontinued,
+  category)
+VALUES (
+  (SELECT id FROM suppliers WHERE company='Habeco'),
+  'TB',
+  'Truc Bach',
+  22,
+  0,
+  'Beverages');
+
+UPDATE products
+SET standard_cost=18
+WHERE product_name='Truc Bach';
+
+DELETE
+FROM suppliers
+WHERE company='Habeco';
diff --git a/usth/ICT2.5/practical/3.pdf b/usth/ICT2.5/practical/3.pdf
new file mode 100644
index 0000000..4adcc28
Binary files /dev/null and b/usth/ICT2.5/practical/3.pdf differ
diff --git a/usth/ICT2.5/practical/3.sql b/usth/ICT2.5/practical/3.sql
new file mode 100644
index 0000000..9e264e8
--- /dev/null
+++ b/usth/ICT2.5/practical/3.sql
@@ -0,0 +1,104 @@
+SOURCE mywind/northwind-default-current-timestamp.sql;
+SOURCE mywind/northwind-data.sql;
+
+CREATE VIEW recent_orders
+AS SELECT *
+FROM orders
+WHERE order_date>'2006-03-24';
+SELECT *
+FROM recent_orders;
+
+SELECT
+  product_code,
+  unit_price,
+  quantity,
+  (unit_price*quantity*(1-discount)) AS value
+FROM order_details, products
+WHERE order_id=31 AND product_id=products.id;
+
+CREATE VIEW order_values
+AS SELECT
+  recent_orders.id AS id,
+  order_date,
+  company,
+  (unit_price*quantity*(1-discount)) AS value
+FROM recent_orders, customers, order_details
+WHERE recent_orders.id=order_id AND customer_id=customers.id;
+SELECT *
+FROM order_values
+ORDER BY id;
+
+CREATE VIEW order_subtotal
+AS SELECT id, order_date, company, SUM(value) as sub_total
+FROM order_values
+GROUP BY id;
+SELECT *
+FROM order_subtotal;
+
+SELECT *
+FROM order_subtotal
+WHERE sub_total>=800;
+
+SELECT
+  CONCAT(first_name, ' ', last_name) AS full_name,
+  SUM(unit_price*quantity*(1-discount)) AS sale
+FROM orders, order_details, employees
+WHERE orders.id=order_id AND employee_id=employees.id
+GROUP BY employee_id
+ORDER BY sale DESC;
+
+SELECT
+  company, 
+  CONCAT(first_name, ' ', last_name) AS full_name,
+  email_address,
+  'C' as 'type'
+FROM employees
+UNION
+SELECT
+  company, 
+  CONCAT(first_name, ' ', last_name) AS full_name,
+  email_address,
+  'S' as 'type'
+FROM suppliers;
+
+SELECT DISTINCT category
+FROM products
+ORDER BY category;
+
+SELECT
+  MIN(standard_cost),
+  MAX(standard_cost),
+  AVG(standard_cost),
+  STD(standard_cost),
+  VARIANCE(standard_cost)
+FROM products;
+
+CREATE VIEW price_by_category
+AS SELECT category, AVG(list_price) as average_price
+FROM products
+GROUP BY category;
+SELECT *
+FROM price_by_category;
+
+DELIMITER //
+CREATE PROCEDURE top_categories(n int)
+BEGIN
+  SELECT *
+  FROM price_by_category
+  ORDER BY average_price DESC
+  LIMIT n;
+END//
+DELIMITER ;
+CALL top_categories(3);
+
+SELECT category, MIN(average_price)
+FROM price_by_category;
+
+CREATE VIEW employee_names
+AS SELECT id, CONCAT(first_name, ' ', last_name) as full_name
+FROM employees;
+SELECT
+  id,
+  (SELECT full_name FROM employee_names WHERE id=created_by) as creator,
+  (SELECT full_name FROM employee_names WHERE id=approved_by) as approver
+FROM purchase_orders;
diff --git a/usth/ICT2.5/practical/mywind b/usth/ICT2.5/practical/mywind
new file mode 160000
index 0000000..936a3f2
--- /dev/null
+++ b/usth/ICT2.5/practical/mywind
@@ -0,0 +1 @@
+Subproject commit 936a3f26806ac4c6060e450529f7a2f60eb6859d
-- 
cgit 1.4.1