CharlesColaco · CharlesColaco · Sep 12, 2024 · Sep 13, 2024 · Sep 18, 2024 · Sep 21, 2024
diff --git a/02_activities/assignments/design_a_logical_model.md b/02_activities/assignments/design_a_logical_model.md
@@ -1,29 +1,65 @@
 # Assignment 1: Design a Logical Model
 
+- Participant Name: Charles Colaco
+
 ## Question 1
+
 Create a logical model for a small bookstore. 📚
 
 At the minimum it should have employee, order, sales, customer, and book entities (tables). Determine sensible column and table design based on what you know about these concepts. Keep it simple, but work out sensible relationships to keep tables reasonably sized. Include a date table. There are several tools online you can use, I'd recommend [_Draw.io_](https://www.drawio.com/) or [_LucidChart_](https://www.lucidchart.com/pages/).
 
+![image info](./images/Answer1.jpg)
+
 ## Question 2
+
 We want to create employee shifts, splitting up the day into morning and evening. Add this to the ERD.
 
+![image info](./images/Answer2.jpg)
+
 ## Question 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._
 
 Bonus: Are there privacy implications to this, why or why not?
+
+![image info](./images/Answer3.jpg)
+
 ```
-Your answer...
+There are privacy concerns in both the architectures:
+
+- Unauthorized personal should not have access to this data, as it is sensitive, and has private information.
+- Data leaks can be a concern.
+- In Type2, there is excess information, that most likely is not relevant to the bookstore's operations.
+There is no need to store customers past addresses, any malicious enity can exploit this information.
 ```
 
 ## Question 4
+
 Review the AdventureWorks Schema [here](https://i.stack.imgur.com/LMu4W.gif)
 
 Highlight at least two differences between it and your ERD. Would you change anything in yours?
+
 ```
-Your answer...
+There are several differences between the AdventureWorks Schema and the bookstore schema
+# Scope
+- The bookstore schema's scope is limited to a simplistic system which can accommodate minimalistic daily operations
+- The AdventureWorks schema is more complex and can accommodate more business domains such as production, purchasing, human resources
+
+# Complexity
+- The bookstore schema is very simple with 9 entities, for a very specific use case
+- The AdventureWorks schema, comparatively has many tables covering more business areas, like suppliers, categories
+
+
+## Proposed changes to the bookstore Schema
+- Create separate entities for Genres, Publishers
+- Create separate entity for inventory
+- Creat separate entity for job title/role
+- Provisions for tax calculations (HST & GST taxes for different provinces)
+- Entity to store Membership/Loyalty information for cutomers
+- Payment method Entity
+- Promotions Entity
 ```
 
 # Criteria
@@ -35,15 +71,17 @@ Your answer...
 🚨 **Please review our [Assignment Submission Guide](https://github.com/UofT-DSI/onboarding/blob/main/onboarding_documents/submissions.md)** 🚨 for detailed instructions on how to format, branch, and submit your work. Following these guidelines is crucial for your submissions to be evaluated correctly.
 
 ### Submission Parameters:
-* Submission Due Date: `September 28, 2024`
-* The branch name for your repo should be: `model-design`
-* What to submit for this assignment:
-    * This markdown (design_a_logical_model.md) should be populated.
-    * Two Entity-Relationship Diagrams (preferably in a pdf, jpeg, png format).
-* What the pull request link should look like for this assignment: `https://github.com/<your_github_username>/sql/pull/<pr_id>`
-    * Open a private window in your browser. Copy and paste the link to your pull request into the address bar. Make sure you can see your pull request properly. This helps the technical facilitator and learning support staff review your submission easily.
+
+- Submission Due Date: `September 28, 2024`
+- The branch name for your repo should be: `model-design`
+- What to submit for this assignment:
+  - This markdown (design_a_logical_model.md) should be populated.
+  - Two Entity-Relationship Diagrams (preferably in a pdf, jpeg, png format).
+- What the pull request link should look like for this assignment: `https://github.com/<your_github_username>/sql/pull/<pr_id>`
+  - Open a private window in your browser. Copy and paste the link to your pull request into the address bar. Make sure you can see your pull request properly. This helps the technical facilitator and learning support staff review your submission easily.
 
 Checklist:
+
 - [ ] Create a branch called `model-design`.
 - [ ] Ensure that the repository is public.
 - [ ] Review [the PR description guidelines](https://github.com/UofT-DSI/onboarding/blob/main/onboarding_documents/submissions.md#guidelines-for-pull-request-descriptions) and adhere to them.

diff --git a/02_activities/assignments/images/Answer1.jpg b/02_activities/assignments/images/Answer1.jpg
diff --git a/02_activities/assignments/images/Answer2.jpg b/02_activities/assignments/images/Answer2.jpg
diff --git a/02_activities/assignments/images/Answer3.jpg b/02_activities/assignments/images/Answer3.jpg
diff --git a/02_activities/homework/homework_1.md b/02_activities/homework/homework_1.md
@@ -4,75 +4,82 @@
 - Weight: 8% of total grade
 
 ## Get to know the farmersmarket.db
+
 Steps to complete this part of the homework:
 
 #### 1) Load Database
+
 - Open DB Browser for SQLite
 - Go to File > Open Database
-- Navigate to your farmersmarket.db 
-	- This will be wherever you cloned the GH Repo (within the **SQL** folder)
-	- ![db_browser_for_sqlite_choose_db.png](./images/01_db_browser_for_sqlite_choose_db.png)
+- Navigate to your farmersmarket.db
+  - This will be wherever you cloned the GH Repo (within the **SQL** folder)
+  - ![db_browser_for_sqlite_choose_db.png](./images/01_db_browser_for_sqlite_choose_db.png)
 
 #### 2) Configure your windows
+
 By default, DB Browser for SQLite has three windows, with four tabs in the main window and three tabs in the bottom right window
+
 - Window 1: Main Window (Centre)
-	- Stay in the Database Structure tab for now
+  - Stay in the Database Structure tab for now
 - Window 2: Edit Database Cell (Top Right)
 - Window 3: Remote (Bottom Right)
-	- Switch this to DB Schema tab (very bottom)
+  - Switch this to DB Schema tab (very bottom)
 
 Your screen should look like this (or very similar)
 ![db_browser_for_sqlite.png](./images/01_db_browser_for_sqlite.png)
 
 #### 3) The farmersmarket.db
+
 There are 10 tables in the Main Window:
-1) booth
-2) customer
-3) customer_purchases
-4) market_date_info
-5) product
-6) product_category
-7) vendor
-8) vendor_booth_assignments
-9) vendor_inventory
-10) zip_data
 
-Switch to the Browse Data tab, booth is selected by default
- ![01_the_browse_data_tab.png](./images/01_the_browse_data_tab.png) 
+1. booth
+2. customer
+3. customer_purchases
+4. market_date_info
+5. product
+6. product_category
+7. vendor
+8. vendor_booth_assignments
+9. vendor_inventory
+10. zip_data
 
+Switch to the Browse Data tab, booth is selected by default
+![01_the_browse_data_tab.png](./images/01_the_browse_data_tab.png)
 
 Using the table drop down at the top left, explore some of the contents of the database
 ![01_the_table_drop_down_at_the_top_left.png](./images/01_the_table_drop_down_at_the_top_left.png)
 
 Move on to the Logical Data Model task when you have looked through the tables
 
-
 ## Logical Data Model
 
 Recall during the module:
 
 I diagramed the following four tables:
+
 - product
 - product_category
 - vendor
 - vendor_inventory
 
 ![01_farmers_market_logical_model_partial.png](./images/01_farmers_market_logical_model_partial.png)
 
-
-Your task: choose two tables and create a logical data model. There are lots of tools you can do this (including drawing this by hand), but I'd recommend [Draw.io](https://www.drawio.com/) or [LucidChart](https://www.lucidchart.com/pages/). 
+Your task: choose two tables and create a logical data model. There are lots of tools you can do this (including drawing this by hand), but I'd recommend [Draw.io](https://www.drawio.com/) or [LucidChart](https://www.lucidchart.com/pages/).
 
 A logical data model must contain:
+
 - table name
 - column names
 - relationship type
 
 Please do not pick the exact same tables that I have already diagramed. For example, you shouldn't diagram the relationship between `product` and `product_category`, but you could diagram `product` and `customer_purchases`.
 
 **A few hints**:
+
 - You will need to use the Browse Data tab in the main window to figure out the relationship types.
 - You can't diagram tables that don't share a common column
-	- These are the tables that are connected
-	-  ![01_farmers_market_conceptual_model.png](./images/01_farmers_market_conceptual_model.png)
+  - These are the tables that are connected
+  - ![01_farmers_market_conceptual_model.png](./images/01_farmers_market_conceptual_model.png)
 - The column names can be found in a few spots (DB Schema window in the bottom right, the Database Structure tab in the main window by expanding each table entry, at the top of the Browse Data tab in the main window)
 
+![image info](./images/homework_1.png)
diff --git a/02_activities/homework/homework_2.sql b/02_activities/homework/homework_2.sql
@@ -1,42 +1,69 @@
 --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), 
 filtered by vendor IDs between 8 and 10 (inclusive) using either:
 	1.  two conditions using AND
 	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 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 AS v 
+INNER JOIN vendor_booth_assignments AS vba  
+ON v.vendor_id = vba.vendor_id 
+ORDER BY v.vendor_name, vba.market_date
+
+--alternative version, so that vendor_id isn't repeated
+SELECT v.*, vba.market_date, vba.booth_number 
+FROM vendor AS v INNER JOIN vendor_booth_assignments AS vba  
+ON v.vendor_id = vba.vendor_id ORDER BY v.vendor_name, vba.market_date
diff --git a/02_activities/homework/homework_3.sql b/02_activities/homework/homework_3.sql
@@ -2,15 +2,31 @@
 /* 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 v.vendor_id,
+       v.vendor_name,
+       Count(vba.vendor_id)AS booths_rented
+FROM   vendor_booth_assignments vba
+       JOIN vendor v
+         ON vba.vendor_id = v.vendor_id
+GROUP  BY v.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 c.customer_id,
+       c.customer_last_name,
+       c.customer_first_name,
+       sum(cp.quantity * cp.cost_to_customer_per_qty) AS customer_total_spend
+FROM   customer c
+       JOIN customer_purchases cp
+         ON cp.customer_id = c.customer_id
+GROUP  BY c.customer_id
+HAVING customer_total_spend > 2000
+ORDER  BY c.customer_last_name,
+          c.customer_first_name;
 
 --Temp Table
 /* 1. Insert the original vendor table into a temp.new_vendor and then add a 10th vendor: 
@@ -23,7 +39,19 @@ 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 TEMP 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');
+
+SELECT *
+FROM temp.new_vendor;
 
 
 -- Date
@@ -32,9 +60,24 @@ VALUES(col1,col2,col3,col4,col5)
 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,
+       Strftime('%m', market_date) AS month,
+       Strftime('%Y', market_date) AS year,
+       Sum(quantity * cost_to_customer_per_qty) AS money_spent
+FROM   customer_purchases
+WHERE STRFTIME('%Y-%m', market_date) = '2022-04'
+--Alternate WHERE clause below
+--WHERE month = '04' AND year = '2022'
+GROUP  BY customer_id, year, month;