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
lsteine49 wants to merge 4 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
4 commits
Select commit Hold shift + click to select a range
c92a133
edited markdown
lsteine49 Nov 3, 2025
4d5ec7d
uploading assignment 1
lsteine49 Nov 5, 2025
a3ad08f
assignment submission
lsteine49 Nov 13, 2025
debc57f
Merge branch 'UofT-DSI:main' into assignment-two
lsteine49 Nov 13, 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
7 changes: 7 additions & 0 deletions 02_activities/assignments/DC_Cohort/Assignment1.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -207,3 +207,10 @@ Consider, for example, concepts of fariness, inequality, social structures, marg
```
Your thoughts...
```
One database that comes to mind is the healthcare system, specifically the Ontario Health Insurance Plan (OHIP). Being included in this data system, ie having an OHIP card with a unique identifying number, grants access to publicly covered health services that would otherwise be prohibitively expensive. Like the example discussed in the article, this data system embeds value judgments through its rules for inclusion, which not everyone can meet.

To be eligible for OHIP, individuals must have legal status in Canada (eg: be a citizen or hold a valid work permit). This requirement excludes several groups, such as temporary foreign workers between contracts, some international students, and non-status individuals, who are left to pay out-of-pocket for essential health services.

Accessing OHIP also requires extensive documentation, which can serve as an additional barrier. Proof of eligibility requires documents, such as a passport, birth certificate, permanent resident card, letter from the Immigration and Refugee Board, or work permit; all of which can be difficult or costly to obtain. Proof of residency in Ontario often relies on documents that assume employment or housing, which is not the reality for all residents. Finally, proof of identity is also required, which may be inaccessible for some individuals due to financial or administrative challenges.

This system, like others managed by government institutions (eg: the passport office or Ministry of Transportation), is interconnected. Gaining access to one database often facilitates access to others, as having “proof of status” becomes a key that unlocks multiple systems. However, for those unable to meet these requirements, these interconnected databases reinforce exclusion and inequity through administrative and financial barriers.
Binary file added 02_activities/assignments/DC_Cohort/Assignment1_conceptual_diagram.pdf
View file
Open in desktop
Binary file not shown.
30 changes: 29 additions & 1 deletion 02_activities/assignments/DC_Cohort/Assignment2.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -56,7 +56,34 @@ The store wants to keep customer addresses. Propose two architectures for the CU
```
Your answer...
```

Type 1 does not keep history and type 2 does

Type 1:
UPDATE CUSTOMER_ADDRESS
SET street_address = '1 university ave',
city = 'Toronto',
province = 'ON',
postal_code = 'M1Y 2Y3',
last_updated_date = CURRENT_DATE
WHERE customer_id = 1;

Type 2:
-- add column for historical text
ALTER TABLE CUSTOMER_ADDRESS
ADD COLUMN effective_start_date DATE,
ADD COLUMN effective_end_date DATE,
ADD COLUMN is_current CHAR(1) CHECK (is_current IN ('Y', 'N'));

-- mark old as inactive
UPDATE CUSTOMER_ADDRESS
SET effective_end_date = CURRENT_DATE,
is_current = 'N'
WHERE customer_id = 1 AND is_current = 'Y';

-- add new info
INSERT INTO CUSTOMER_ADDRESS (
customer_id, street_address, city, province, postal_code, effective_start_date, is_current)
VALUES ( 1, '1 sql ave', 'Toronto', 'ON', 'M4E 2G6', CURRENT_DATE, 'Y');
***

## Section 2:
Expand Down Expand Up @@ -185,3 +212,4 @@ Consider, for example, concepts of labour, bias, LLM proliferation, moderating c
```
Your thoughts...
```
The main problem is that AI is taught by humans. AI is trained to recognize patterns and reproduce existing relationships, however it learns on test datasets that have been coded by humans, and algorithms are validated through testing how well it replicates what it is taught. However, humans are inherently biased and in this instance, have tagged images with offensive descriptors and AI is replicating those biases and reproducing harmful associations. Therefore, this example shows how AI can unintentionally replicate and potentially amplify human prejudice. The example went on to mention how this may be used by police departments or other government agencies, for example, face recognition. If we know that these systems are reproducing human bias, it follows that similar biases likely exist within facial recognition programs, raising serious concerns about racial profiling and discrimination.
Binary file added 02_activities/assignments/DC_Cohort/DSI Assignment 2 diagram.pdf
View file
Open in desktop
Binary file not shown.
123 changes: 113 additions & 10 deletions 02_activities/assignments/DC_Cohort/assignment1.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -4,17 +4,24 @@

--SELECT
/* 1. Write a query that returns everything in the customer table. */


SELECT*
FROM customer;

/* 2. Write a query that displays all of the columns and 10 rows from the cus- tomer table,
sorted by customer_last_name, then customer_first_ name. */

Select*
from customer
order by customer_last_name, customer_first_name
limit 10


--WHERE
/* 1. Write a query that returns all customer purchases of product IDs 4 and 9. */

/* 1. Write a query that returns all customer purchases of product IDs 4 and 9. NOTE: no product ID =9*/
Select*
from customer_purchases
where product_id = 4
or product_id = 9;


/*2. Write a query that returns all customer purchases and a new calculated column 'price' (quantity * cost_to_customer_per_qty),
Expand All @@ -23,48 +30,108 @@ filtered by customer IDs between 8 and 10 (inclusive) using either:
2. one condition using BETWEEN
*/
-- option 1

SELECT
customer_id,
product_id,
(quantity*cost_to_customer_per_qty) as price
from customer_purchases
where customer_id >= 8
AND customer_id <= 10;

-- option 2

SELECT
customer_id,
product_id,
(quantity*cost_to_customer_per_qty) as price
from customer_purchases
where customer_id between 8 and 10;


--CASE
/* 1. Products can be sold by the individual unit or by bulk measures like lbs. or oz.
Using the product table, write a query that outputs the product_id and product_name
columns and add a column called prod_qty_type_condensed that displays the word “unit”
if the product_qty_type is “unit,” and otherwise displays the word “bulk.” */

SELECT
product_name,
product_id,
case when product_qty_type = 'unit' then 'unit'
when product_qty_type = 'lbs' then 'bulk'
end as prod_qty_type_condensed
from product


/* 2. We want to flag all of the different types of pepper products that are sold at the market.
add a column to the previous query called pepper_flag that outputs a 1 if the product_name
contains the word “pepper” (regardless of capitalization), and otherwise outputs 0. */


SELECT
product_name,
product_id,
case
when product_qty_type = 'unit' then 'unit'
when product_qty_type = 'lbs' then 'bulk'
end as prod_qty_type_condensed,
case
when product_name like '%pepper%' then 1
else 0
end as pepper_flag
from product

--JOIN
/* 1. Write a query that INNER JOINs the vendor table to the vendor_booth_assignments table on the
vendor_id field they both have in common, and sorts the result by vendor_name, then market_date. */

SELECT
vendor.vendor_name,
vendor_booth_assignments.market_date


FROM vendor
INNER JOIN vendor_booth_assignments
on vendor.vendor_id = vendor_booth_assignments.vendor_id
ORDER BY
vendor.vendor_name, vendor_booth_assignments.market_date;

/* SECTION 3 */

-- AGGREGATE
/* 1. Write a query that determines how many times each vendor has rented a booth
at the farmer’s market by counting the vendor booth assignments per vendor_id. */
SELECT
vendor.vendor_name,
vendor.vendor_id,
count (vendor_booth_assignments.booth_number)as booth_rental_count

FROM vendor
INNER JOIN vendor_booth_assignments
on vendor.vendor_id = vendor_booth_assignments.vendor_id

group by vendor.vendor_name
ORDER BY
vendor.vendor_name


/* 2. The Farmer’s Market Customer Appreciation Committee wants to give a bumper
sticker to everyone who has ever spent more than $2000 at the market. Write a query that generates a list
of customers for them to give stickers to, sorted by last name, then first name.

HINT: This query requires you to join two tables, use an aggregate function, and use the HAVING keyword. */

Select
customer.customer_first_name,
customer.customer_last_name,
sum(customer_purchases.quantity*customer_purchases.cost_to_customer_per_qty) as total_spend

FROM customer
INNER JOIN customer_purchases
on customer.customer_id = customer_purchases.customer_id

group by customer.customer_id

having total_spend >2000

ORDER BY
customer_last_name, customer_first_name;

--Temp Table
/* 1. Insert the original vendor table into a temp.new_vendor and then add a 10th vendor:
Expand All @@ -78,19 +145,55 @@ When inserting the new vendor, you need to appropriately align the columns to be
VALUES(col1,col2,col3,col4,col5)
*/

DROP TABLE IF EXISTS temp.new_vendor;

--make the table
CREATE TABLE temp.new_vendor as

-- definition of the table
SELECT*
FROM vendor;

INSERT INTO temp.new_vendor (vendor_id, vendor_name, vendor_type, vendor_owner_first_name, vendor_owner_last_name)
VALUES (10, 'Thomass Superfood Store', 'Fresh Focused', 'Thomas', 'Rosenthal');

--check to make sure the new table is there
SELECT*
FROM new_vendor;

-- Date
/*1. Get the customer_id, month, and year (in separate columns) of every purchase in the customer_purchases table.

HINT: you might need to search for strfrtime modifers sqlite on the web to know what the modifers for month
and year are! */


SELECT
customer_id,
STRFTIME('%m', market_date) AS purchase_month,
STRFTIME('%Y', market_date) AS purchase_year
FROM customer_purchases;

/* 2. Using the previous query as a base, determine how much money each customer spent in April 2022.
Remember that money spent is quantity*cost_to_customer_per_qty.

HINTS: you will need to AGGREGATE, GROUP BY, and filter...
but remember, STRFTIME returns a STRING for your WHERE statement!! */

Select
customer.customer_first_name,
customer.customer_last_name,
sum(customer_purchases.quantity*customer_purchases.cost_to_customer_per_qty) as total_spend

FROM customer
INNER JOIN customer_purchases
on customer.customer_id = customer_purchases.customer_id

WHERE
STRFTIME('%m', customer_purchases.market_date) = '04'
AND STRFTIME('%Y', customer_purchases.market_date) = '2022'

GROUP BY
customer.customer_id
ORDER BY
customer.customer_last_name,
customer.customer_first_name;
Loading