Sdong23 · Sdong23 · Jan 26, 2025 · Jan 26, 2025 · Jan 27, 2025 · Jan 27, 2025
diff --git a/02_activities/assignments/Assignment1.md b/02_activities/assignments/Assignment1.md
@@ -205,5 +205,19 @@ Consider, for example, concepts of fariness, inequality, social structures, marg
 
 
 ```
-Your thoughts...
+In the article When Databases Get to Define Family by Rafia Qadri (2021), the author explores the implications of a digital database system implemented in Pakistan, which aims to track and define familial relationships through a centralized system. This system, known as the National Database and Registration Authority (NADRA), was designed to create a comprehensive digital record of families, providing better access to public services. However, as the article reveals, the technology inadvertently embeds certain value systems that have profound social implications. Through the lens of this case, we can examine how databases and data systems in our day-to-day lives often reflect underlying societal values, such as fairness, inequality, and social structures.
+
+One of the most prominent value systems embedded in databases, like the one implemented by NADRA, is the idea of fairness. On the surface, the digitalization of family records aims to make government services more accessible and efficient. However, the concept of fairness is complicated when we consider who gets included in the system and who is marginalized.
+
+In Pakistan, the NADRA database has, unintentionally, made it difficult for some families to fully benefit from state services. For example, families with limited access to technology, particularly in rural or underdeveloped regions, struggle to register their family members correctly in the digital system. Without proper documentation or understanding of how the database functions, marginalized groups—especially women, the illiterate, and economically disadvantaged—often find themselves excluded from benefits like healthcare, education, and social security. In this case, the system that was designed to promote fairness ends up exacerbating existing inequalities.
+
+Databases like NADRA’s also reflect and reinforce certain social structures. In many societies, family structures are traditionally patriarchal, where men often hold the legal authority over family decisions. As the article illustrates, this digital system further solidifies these existing social hierarchies by using a male-centric model of family, where the father is often seen as the head of the household. This value system is embedded within the design of the database, influencing who gets recognized as a family’s primary representative.
+
+The interaction of technology and society is crucial here. The database was created with the intention of bringing modernity and convenience to state processes. However, it doesn’t fully account for how deeply ingrained societal norms and structures may influence the way technology is used and experienced. By automating and digitalizing the process of recognizing family units, the system inadvertently disregards the complexity of familial relationships, particularly those that don’t fit neatly into the traditional mold of a nuclear family. For example, women who may be the primary caregivers or breadwinners of their families might not be properly acknowledged in the database system because the database relies on conventional norms about family leadership.
+
+Another significant issue raised by Qadri is how databases can marginalize certain groups of people, especially in societies where access to technology and government services is unequal. Data systems, by their very nature, rely on the collection of information from individuals. Those without access to digital tools, like smartphones or reliable internet, are excluded from being able to register their information in the database. This digital divide creates a form of systemic marginalization. People from lower socioeconomic classes, women in conservative societies, or rural populations often find themselves excluded from digital platforms that are increasingly required for accessing basic services.
+
+Furthermore, data systems can reinforce existing biases in how different groups are perceived and treated. In the case of NADRA, data systems may perpetuate discriminatory practices that disproportionately affect women or minority groups. For example, the system’s structure may not fully recognize non-traditional family structures, such as those involving blended families, single-parent households, or intergenerational households, thereby undermining their ability to receive state benefits. This structural bias built into the design of databases highlights the intersection of technology and societal values.
+
+In conclusion, the values embedded in databases and data systems, such as fairness, inequality, and social structures, are not neutral. These systems are shaped by the societal norms and structures of the communities that create them. As illustrated by the NADRA system in Pakistan, what is intended to be a tool for inclusion can inadvertently perpetuate discrimination and marginalization, especially for those who are already disadvantaged. The article by Rafia Qadri emphasizes the need for greater awareness and sensitivity when developing digital systems to ensure that they do not inadvertently reinforce existing inequalities or exclude marginalized groups from their benefits. Ultimately, the intersection of technology and society requires a careful examination of how data systems shape our understanding of fairness, identity, and access to resources.
 ```
diff --git a/02_activities/assignments/assignment1.png b/02_activities/assignments/assignment1.png
diff --git a/02_activities/assignments/assignment1.sql b/02_activities/assignments/assignment1.sql
@@ -4,21 +4,28 @@
 
 --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. */
 -- option 1
-
+SELECT *
+FROM customer_purchases
+WHERE product_id IN (4,9);
 
 -- option 2
-
+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), 
@@ -27,30 +34,55 @@ filtered by vendor IDs between 8 and 10 (inclusive) using either:
 	2.  one condition using BETWEEN
 */
 -- option 1
-
+SELECT *,quantity * cost_to_customer_per_qty AS price
+FROM customer_purchases
+WHERE vendor_id >= 8 AND vendor_id <= 10;
 
 -- option 2
-
+SELECT *,quantity * cost_to_customer_per_qty AS price
+FROM customer_purchases
+WHERE vendor_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_id,
+       product_name,
+       CASE 
+           WHEN product_qty_type = 'unit' 
+		   THEN 'unit'
+           ELSE '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_id,
+       product_name,
+       CASE 
+           WHEN product_qty_type = 'unit' THEN 'unit'
+           ELSE 'bulk'
+       END AS prod_qty_type_condensed,
+       CASE
+           WHEN LOWER(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 *
+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;
 
 
 
@@ -59,15 +91,22 @@ vendor_id field they both have in common, and sorts the result by vendor_name, t
 -- 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_id, COUNT(*) AS booth_rentals
+FROM vendor_booth_assignments
+GROUP BY vendor_id;
 
 
 /* 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_first_name, customer_last_name
+FROM customer c
+JOIN customer_purchases p ON c.customer_id = p.customer_id
+GROUP BY c.customer_id, c.customer_first_name, c.customer_last_name
+HAVING SUM(p.quantity * p.cost_to_customer_per_qty) > 2000
+ORDER BY c.customer_last_name, c.customer_first_name;
 
 
 --Temp Table
@@ -81,20 +120,38 @@ When inserting the new vendor, you need to appropriately align the columns to be
 -> To insert the new row use VALUES, specifying the value you want for each column:
 VALUES(col1,col2,col3,col4,col5) 
 */
+DROP TABLE IF EXISTS temp.new_vendor;
+CREATE TABLE temp.new_vendor AS
+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');
 
 
 -- 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 month,
+    strftime('%Y', market_date) AS 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_id,
+    SUM(quantity * cost_to_customer_per_qty) AS total_spent
+FROM 
+    customer_purchases
+WHERE 
+    strftime('%Y-%m', market_date) = '2022-04'  -- Filter for April 2022
+GROUP BY 
+    customer_id;
diff --git a/Untitled Diagram.drawio b/Untitled Diagram.drawio
@@ -0,0 +1,58 @@
+<mxfile host="app.diagrams.net" agent="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36" version="26.0.8">
+  <diagram name="Page-1" id="yLB1_hxO5Nf7JVr4UumI">
+    <mxGraphModel dx="954" dy="558" grid="1" gridSize="10" guides="1" tooltips="1" connect="1" arrows="1" fold="1" page="1" pageScale="1" pageWidth="850" pageHeight="1100" math="0" shadow="0">
+      <root>
+        <mxCell id="0" />
+        <mxCell id="1" parent="0" />
+        <mxCell id="ocX54kWvVfms7DKp8SOT-1" value="&lt;b&gt;customer_purchases&lt;/b&gt;" style="swimlane;fontStyle=0;childLayout=stackLayout;horizontal=1;startSize=26;fillColor=none;horizontalStack=0;resizeParent=1;resizeParentMax=0;resizeLast=0;collapsible=1;marginBottom=0;html=1;" vertex="1" parent="1">
+          <mxGeometry x="110" y="160" width="180" height="208" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-2" value="product_id" style="text;strokeColor=none;fillColor=none;align=left;verticalAlign=top;spacingLeft=4;spacingRight=4;overflow=hidden;rotatable=0;points=[[0,0.5],[1,0.5]];portConstraint=eastwest;whiteSpace=wrap;html=1;" vertex="1" parent="ocX54kWvVfms7DKp8SOT-1">
+          <mxGeometry y="26" width="180" height="26" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-3" value="vendor_id" style="text;strokeColor=none;fillColor=none;align=left;verticalAlign=top;spacingLeft=4;spacingRight=4;overflow=hidden;rotatable=0;points=[[0,0.5],[1,0.5]];portConstraint=eastwest;whiteSpace=wrap;html=1;" vertex="1" parent="ocX54kWvVfms7DKp8SOT-1">
+          <mxGeometry y="52" width="180" height="26" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-4" value="market_date" style="text;strokeColor=none;fillColor=none;align=left;verticalAlign=top;spacingLeft=4;spacingRight=4;overflow=hidden;rotatable=0;points=[[0,0.5],[1,0.5]];portConstraint=eastwest;whiteSpace=wrap;html=1;" vertex="1" parent="ocX54kWvVfms7DKp8SOT-1">
+          <mxGeometry y="78" width="180" height="26" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-7" value="customer_id" style="text;strokeColor=none;fillColor=none;align=left;verticalAlign=top;spacingLeft=4;spacingRight=4;overflow=hidden;rotatable=0;points=[[0,0.5],[1,0.5]];portConstraint=eastwest;whiteSpace=wrap;html=1;" vertex="1" parent="ocX54kWvVfms7DKp8SOT-1">
+          <mxGeometry y="104" width="180" height="26" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-6" value="quantity&lt;table&gt;&lt;tbody&gt;&lt;tr&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;/tr&gt;&lt;tr&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;/tr&gt;&lt;tr&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;/tr&gt;&lt;tr&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;td&gt;&lt;br&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/tbody&gt;&lt;/table&gt;" style="text;strokeColor=none;fillColor=none;align=left;verticalAlign=top;spacingLeft=4;spacingRight=4;overflow=hidden;rotatable=0;points=[[0,0.5],[1,0.5]];portConstraint=eastwest;whiteSpace=wrap;html=1;" vertex="1" parent="ocX54kWvVfms7DKp8SOT-1">
+          <mxGeometry y="130" width="180" height="26" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-9" value="cost_to_customer_per_qty&lt;div&gt;&lt;br&gt;&lt;/div&gt;" style="text;strokeColor=none;fillColor=none;align=left;verticalAlign=top;spacingLeft=4;spacingRight=4;overflow=hidden;rotatable=0;points=[[0,0.5],[1,0.5]];portConstraint=eastwest;whiteSpace=wrap;html=1;" vertex="1" parent="ocX54kWvVfms7DKp8SOT-1">
+          <mxGeometry y="156" width="180" height="26" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-8" value="transaction_time" style="text;strokeColor=none;fillColor=none;align=left;verticalAlign=top;spacingLeft=4;spacingRight=4;overflow=hidden;rotatable=0;points=[[0,0.5],[1,0.5]];portConstraint=eastwest;whiteSpace=wrap;html=1;" vertex="1" parent="ocX54kWvVfms7DKp8SOT-1">
+          <mxGeometry y="182" width="180" height="26" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-11" value="&lt;b&gt;customer&lt;/b&gt;" style="swimlane;fontStyle=0;childLayout=stackLayout;horizontal=1;startSize=26;fillColor=none;horizontalStack=0;resizeParent=1;resizeParentMax=0;resizeLast=0;collapsible=1;marginBottom=0;html=1;" vertex="1" parent="1">
+          <mxGeometry x="425" y="180" width="140" height="130" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-12" value="customer_id" style="text;strokeColor=none;fillColor=none;align=left;verticalAlign=top;spacingLeft=4;spacingRight=4;overflow=hidden;rotatable=0;points=[[0,0.5],[1,0.5]];portConstraint=eastwest;whiteSpace=wrap;html=1;" vertex="1" parent="ocX54kWvVfms7DKp8SOT-11">
+          <mxGeometry y="26" width="140" height="26" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-15" value="customer_first_name" style="text;strokeColor=none;fillColor=none;align=left;verticalAlign=top;spacingLeft=4;spacingRight=4;overflow=hidden;rotatable=0;points=[[0,0.5],[1,0.5]];portConstraint=eastwest;whiteSpace=wrap;html=1;" vertex="1" parent="ocX54kWvVfms7DKp8SOT-11">
+          <mxGeometry y="52" width="140" height="26" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-13" value="customer_last_name" style="text;strokeColor=none;fillColor=none;align=left;verticalAlign=top;spacingLeft=4;spacingRight=4;overflow=hidden;rotatable=0;points=[[0,0.5],[1,0.5]];portConstraint=eastwest;whiteSpace=wrap;html=1;" vertex="1" parent="ocX54kWvVfms7DKp8SOT-11">
+          <mxGeometry y="78" width="140" height="26" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-14" value="customer_postal_code" style="text;strokeColor=none;fillColor=none;align=left;verticalAlign=top;spacingLeft=4;spacingRight=4;overflow=hidden;rotatable=0;points=[[0,0.5],[1,0.5]];portConstraint=eastwest;whiteSpace=wrap;html=1;" vertex="1" parent="ocX54kWvVfms7DKp8SOT-11">
+          <mxGeometry y="104" width="140" height="26" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-17" style="edgeStyle=orthogonalEdgeStyle;rounded=0;orthogonalLoop=1;jettySize=auto;html=1;exitX=1;exitY=0.5;exitDx=0;exitDy=0;entryX=0;entryY=0.5;entryDx=0;entryDy=0;endArrow=none;startFill=0;" edge="1" parent="1" source="ocX54kWvVfms7DKp8SOT-7" target="ocX54kWvVfms7DKp8SOT-12">
+          <mxGeometry relative="1" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-18" value="1" style="text;strokeColor=none;align=center;fillColor=none;html=1;verticalAlign=middle;whiteSpace=wrap;rounded=0;" vertex="1" parent="1">
+          <mxGeometry x="365" y="190" width="60" height="30" as="geometry" />
+        </mxCell>
+        <mxCell id="ocX54kWvVfms7DKp8SOT-19" value="&lt;span style=&quot;color: rgb(71, 71, 71); font-family: Arial, sans-serif; font-size: 14px; text-align: left; background-color: rgb(255, 255, 255);&quot;&gt;∞&lt;/span&gt;" style="text;strokeColor=none;align=center;fillColor=none;html=1;verticalAlign=middle;whiteSpace=wrap;rounded=0;" vertex="1" parent="1">
+          <mxGeometry x="290" y="249" width="60" height="30" as="geometry" />
+        </mxCell>
+      </root>
+    </mxGraphModel>
+  </diagram>
+</mxfile>