Skip to content

Assignment two #2

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
Ffffanglin wants to merge 2 commits into
base: main
Choose a base branch
from
Open

Assignment two #2

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
27cbfc0
assignment 2
Ffffanglin Nov 16, 2025
ec1005a
add the last part-essay
Ffffanglin Nov 16, 2025
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
Binary file added 02_activities/assignments/DC_Cohort/Assigment2-section1.db
View file
Open in desktop
Binary file not shown.
70 changes: 70 additions & 0 deletions 02_activities/assignments/DC_Cohort/Assigment2-section1.sqbpro
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,70 @@
<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="Assigment2-section1.db" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="2089"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><table title="Order_Line" custom_title="0" dock_id="1" table="4,10:mainOrder_Line"/><dock_state state="000000ff00000000fd000000010000000200000226000002f1fc0100000001fb000000160064006f0063006b00420072006f00770073006500310100000000000002260000010100ffffff000002260000000000000004000000040000000800000008fc00000000"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="Book" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="61"/><column index="2" value="34"/><column index="3" value="51"/><column index="4" value="69"/><column index="5" value="117"/><column index="6" value="70"/><column index="7" value="80"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Customer" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="89"/><column index="2" value="79"/><column index="3" value="76"/><column index="4" value="43"/><column index="5" value="49"/><column index="6" value="116"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Date" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="57"/><column index="2" value="101"/><column index="3" value="37"/><column index="4" value="57"/><column index="5" value="50"/><column index="6" value="32"/><column index="7" value="84"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Employee" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="91"/><column index="2" value="79"/><column index="3" value="76"/><column index="4" value="43"/><column index="5" value="49"/><column index="6" value="62"/><column index="7" value="91"/><column index="8" value="80"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Order" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="63"/><column index="2" value="91"/><column index="3" value="92"/><column index="4" value="101"/><column index="5" value="91"/><column index="6" value="81"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Order_Line" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="64"/><column index="2" value="56"/><column index="3" value="62"/><column index="4" value="62"/><column index="5" value="75"/><column index="6" value="69"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table></browse_table_settings></tab_browse><tab_sql><sql name="SQL 1*">CREATE TABLE &quot;Date&quot; (
date_id INT PRIMARY KEY,
calendar_date DATE NOT NULL,
year INT NOT NULL,
quarter INT NOT NULL CHECK (quarter BETWEEN 1 AND 4),
month INT NOT NULL CHECK (month BETWEEN 1 AND 12),
day INT NOT NULL CHECK (day BETWEEN 1 AND 31),
is_weekend BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE Employee (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE,
phone VARCHAR(50),
job_title VARCHAR(50),
hire_date_id INT REFERENCES &quot;Date&quot;(date_id),
active_flag BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE Customer (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE,
phone VARCHAR(50),
created_date_id INT REFERENCES &quot;Date&quot;(date_id)
);

CREATE TABLE Book (
book_id INT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author VARCHAR(255),
publisher VARCHAR(255),
publication_year INT,
list_price NUMERIC(10,2),
active_flag BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE &quot;Order&quot; (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL REFERENCES Customer(customer_id),
employee_id INT NOT NULL REFERENCES Employee(employee_id),
order_date_id INT NOT NULL REFERENCES &quot;Date&quot;(date_id),
order_status VARCHAR(30) NOT NULL DEFAULT 'OPEN',
order_total NUMERIC(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE Order_Line (
order_id INT NOT NULL REFERENCES &quot;Order&quot;(order_id) ON DELETE CASCADE,
line_no INT NOT NULL,
book_id INT NOT NULL REFERENCES Book(book_id),
quantity INT NOT NULL CHECK (quantity &gt; 0),
unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price &gt;= 0),
line_total NUMERIC(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
PRIMARY KEY (order_id, line_no)
);

CREATE TABLE Sales (
sale_id INT PRIMARY KEY,
order_id INT UNIQUE REFERENCES &quot;Order&quot;(order_id) ON DELETE CASCADE,
sale_date_id INT NOT NULL REFERENCES &quot;Date&quot;(date_id),
subtotal NUMERIC(12,2) NOT NULL DEFAULT 0,
tax_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
discount_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
total_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
payment_method VARCHAR(20)
);
</sql><current_tab id="0"/></tab_sql></sqlb_project>
14 changes: 9 additions & 5 deletions 02_activities/assignments/DC_Cohort/Assignment2.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -47,16 +47,14 @@ There are several tools online you can use, I'd recommend [Draw.io](https://www.

#### Prompt 2
We want to create employee shifts, splitting up the day into morning and evening. Add this to the ERD.

![ERD_Assignment2_FG](images/ERD_Assignment2_FG.png)
#### Prompt 3
The store wants to keep customer addresses. Propose two architectures for the CUSTOMER_ADDRESS table, one that will retain changes, and another that will overwrite. Which is type 1, which is type 2?

**HINT:** search type 1 vs type 2 slowly changing dimensions.

```
Your answer...
```

Type 1 overwrites old address data; Type 2 retains each change as a new historical record.
***

## Section 2:
Expand Down Expand Up @@ -183,5 +181,11 @@ Consider, for example, concepts of labour, bias, LLM proliferation, moderating c


```
Your thoughts...

Vicky Boikis's thesis reveals the human foundations of artificial intelligence, pointing out that “machine learning” is neither autonomous nor purely mechanical, it is built upon layers upon layers of human labor, judgment, and bias. Beneath every neural network lies a vast, invisible workforce: those who tag images, clean data, and define linguistic categories. From the low-wage workers on Amazon Mechanical Turk who annotated millions of photos for ImageNet to the graduate students who compiled the Brown Corpus decades ago, the birth of so-called “intelligent” systems relies on people performing repetitive and under-recognized labor. This invisible workforce constitutes a new form of digital piecework—globalized, precarious, and indispensable—raising ethical dilemmas about fair compensation, labor recognition, and working conditions in the age of automation.

Boykis also reveals how bias is deeply embedded within the data itself. Every decision to classify an image or define a word carries human worldviews, which often reflect cultural and societal prejudices. When such biases are amplified through massive datasets, the resulting systems, whether facial recognition, content moderation, or recommendation algorithms—replicate stereotypes and inflict real-world harm. For instance, the offensive labels in the ImageNet roulette scandal exposed how classification politics infiltrate supposedly objective technologies. The subsequent attempts by ImageNet's creators to remove “unsafe” or “offensive” categories underscore that AI's ethical accountability is ongoing and cannot be solely entrusted to code.

More broadly, Boikis' story connects industrial sewing labor with the cognitive labor of data annotation: both represent human dexterity that machines struggle to replicate yet depend upon. This narrative dismantles the automation myth by revealing that technology does not eliminate human labor—it merely conceals it. Ethically, this recognition demands transparency about AI system contributors, safeguards against exploitative digital labor, and strengthened accountability for the societal impacts of biased data. Ultimately, Boikis reminds us that neural networks are not self-sufficient machines but products of collective human creation. Respecting those who build, maintain, and are affected by them lies at the heart of constructing a vision for responsible artificial intelligence.

```
148 changes: 136 additions & 12 deletions 02_activities/assignments/DC_Cohort/assignment2.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -31,19 +31,72 @@ You can either display all rows in the customer_purchases table, with the counte
each new market date for each customer, or select only the unique market dates per customer
(without purchase details) and number those visits.
HINT: One of these approaches uses ROW_NUMBER() and one uses DENSE_RANK(). */


SELECT
cp.*,
DENSE_RANK() OVER (
PARTITION BY customer_id
ORDER BY market_date
) AS visit_number
FROM customer_purchases AS cp
ORDER BY customer_id, market_date;

SELECT
customer_id,
market_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY market_date
) AS visit_number
FROM (
SELECT DISTINCT customer_id, market_date
FROM customer_purchases
)
ORDER BY customer_id, market_date;

/* 2. Reverse the numbering of the query from a part so each customer’s most recent visit is labeled 1,
then write another query that uses this one as a subquery (or temp table) and filters the results to
only the customer’s most recent visit. */


SELECT *
FROM (
SELECT
cp.*,
DENSE_RANK() OVER (
PARTITION BY customer_id
ORDER BY market_date DESC
) AS visit_number
FROM customer_purchases AS cp
) x
WHERE x.visit_number = 1
ORDER BY customer_id, market_date;

SELECT *
FROM (
SELECT
customer_id,
market_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY market_date DESC
) AS visit_number
FROM (
SELECT DISTINCT customer_id, market_date
FROM customer_purchases
)
) x
WHERE x.visit_number = 1
ORDER BY customer_id, market_date;

/* 3. Using a COUNT() window function, include a value along with each row of the
customer_purchases table that indicates how many different times that customer has purchased that product_id. */


SELECT
cp.*,
COUNT(*) OVER (
PARTITION BY customer_id, product_id
) AS purchase_count
FROM customer_purchases AS cp
ORDER BY customer_id, product_id, market_date;

-- String manipulations
/* 1. Some product names in the product table have descriptions like "Jar" or "Organic".
Expand All @@ -57,11 +110,21 @@ Remove any trailing or leading whitespaces. Don't just use a case statement for

Hint: you might need to use INSTR(product_name,'-') to find the hyphens. INSTR will help split the column. */

SELECT
p.*,
CASE
WHEN INSTR(product_name, '-') > 0
THEN TRIM(SUBSTR(product_name, INSTR(product_name, '-') + 1))
ELSE NULL
END AS description
FROM product AS p;


/* 2. Filter the query to show any product_size value that contain a number with REGEXP. */


SELECT *
FROM product
WHERE product_size GLOB '*[0-9]*';

-- UNION
/* 1. Using a UNION, write a query that displays the market dates with the highest and lowest total sales.
Expand All @@ -73,7 +136,30 @@ HINT: There are a possibly a few ways to do this query, but if you're struggling
3) Query the second temp table twice, once for the best day, once for the worst day,
with a UNION binding them. */


WITH sales_per_market AS (
SELECT
market_date,
ROUND(SUM(quantity * cost_to_customer_per_qty), 2) AS sales
FROM customer_purchases
GROUP BY market_date
),
ranked AS (
SELECT
market_date,
sales,
RANK() OVER (ORDER BY sales ASC) AS r_asc,
RANK() OVER (ORDER BY sales DESC) AS r_desc
FROM sales_per_market
)
SELECT market_date, sales, 'worst_day' AS label
FROM ranked
WHERE r_asc = 1

UNION

SELECT market_date, sales, 'best_day' AS label
FROM ranked
WHERE r_desc = 1;


/* SECTION 3 */
Expand All @@ -89,27 +175,56 @@ Think a bit about the row counts: how many distinct vendors, product names are t
How many customers are there (y).
Before your final group by you should have the product of those two queries (x*y). */


SELECT
v.vendor_name,
p.product_name,
SUM(5 * vi.original_price) * (SELECT COUNT(*) FROM customer) AS revenue_if_every_customer_buys_5
FROM vendor_inventory vi
JOIN vendor v ON v.vendor_id = vi.vendor_id
JOIN product p ON p.product_id = vi.product_id
GROUP BY v.vendor_name, p.product_name;

-- INSERT
/*1. Create a new table "product_units".
This table will contain only products where the `product_qty_type = 'unit'`.
It should use all of the columns from the product table, as well as a new column for the `CURRENT_TIMESTAMP`.
Name the timestamp column `snapshot_timestamp`. */


CREATE TABLE product_units AS
SELECT
p.*,
CURRENT_TIMESTAMP AS snapshot_timestamp
FROM product p
WHERE product_qty_type = 'unit';

/*2. Using `INSERT`, add a new row to the product_units table (with an updated timestamp).
This can be any product you desire (e.g. add another record for Apple Pie). */


INSERT INTO product_units (
product_id, product_name, product_size, product_category_id,
product_qty_type, snapshot_timestamp
)
SELECT
product_id, product_name, product_size, product_category_id,
product_qty_type, CURRENT_TIMESTAMP
FROM product
WHERE product_id = 7;

-- DELETE
/* 1. Delete the older record for the whatever product you added.

HINT: If you don't specify a WHERE clause, you are going to have a bad time.*/


WITH older AS (
SELECT
product_id,
MIN(snapshot_timestamp) AS ts
FROM product_units
WHERE product_id = 7
)
DELETE FROM product_units
WHERE product_id = (SELECT product_id FROM older)
AND snapshot_timestamp = (SELECT ts FROM older);

-- UPDATE
/* 1.We want to add the current_quantity to the product_units table.
Expand All @@ -129,5 +244,14 @@ Finally, make sure you have a WHERE statement to update the right row,
When you have all of these components, you can run the update statement. */




ALTER TABLE product_units
ADD COLUMN current_quantity INT;

UPDATE product_units AS pu
SET current_quantity = COALESCE((
SELECT vi.quantity
FROM vendor_inventory AS vi
WHERE vi.product_id = pu.product_id
ORDER BY vi.market_date DESC, vi.vendor_id DESC
LIMIT 1
), 0);
Binary file added 02_activities/assignments/DC_Cohort/images/ERD_Assignment2_FG.png
View file
Open in desktop
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
Binary file modified 05_src/sql/farmersmarket.db
View file
Open in desktop
Binary file not shown.