adithya28 · adithya28 · May 17, 2024 · May 17, 2024 · May 17, 2024 · May 19, 2024
diff --git a/01_slides/slides_03.pdf b/01_slides/slides_03.pdf
diff --git a/01_slides/slides_04.pdf b/01_slides/slides_04.pdf
diff --git a/01_slides/slides_05.pdf b/01_slides/slides_05.pdf
diff --git a/01_slides/slides_06.pdf b/01_slides/slides_06.pdf
diff --git a/02_assignments/Assignment 1 question 1.png b/02_assignments/Assignment 1 question 1.png
diff --git a/02_assignments/Assignment 1 question 2.png b/02_assignments/Assignment 1 question 2.png
diff --git a/02_assignments/assignment1Finalsql.sql b/02_assignments/assignment1Finalsql.sql
@@ -0,0 +1,133 @@
+-- MySQL Script generated by MySQL Workbench
+-- Sat Jun  1 23:22:25 2024
+-- Model: New Model    Version: 1.0
+-- MySQL Workbench Forward Engineering
+
+SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
+SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
+SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
+
+-- -----------------------------------------------------
+-- Schema mydb
+-- -----------------------------------------------------
+
+-- -----------------------------------------------------
+-- Schema mydb
+-- -----------------------------------------------------
+CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
+USE `mydb` ;
+
+-- -----------------------------------------------------
+-- Table `mydb`.`Employee`
+-- -----------------------------------------------------
+CREATE TABLE IF NOT EXISTS `mydb`.`Employee` (
+  `employee_id` INT NULL DEFAULT NULL AUTO_INCREMENT,
+  `first_name` VARCHAR(50) NULL DEFAULT NULL,
+  `last_name` VARCHAR(50) NULL DEFAULT NULL,
+  `email` VARCHAR(100) NULL DEFAULT NULL,
+  `position` VARCHAR(50) NULL DEFAULT NULL,
+  PRIMARY KEY (`employee_id`));
+
+
+-- -----------------------------------------------------
+-- Table `mydb`.`Customer`
+-- -----------------------------------------------------
+CREATE TABLE IF NOT EXISTS `mydb`.`Customer` (
+  `customer_id` INT NULL DEFAULT NULL AUTO_INCREMENT,
+  `first_name` VARCHAR(50) NULL DEFAULT NULL,
+  `last_name` VARCHAR(50) NULL DEFAULT NULL,
+  `email` VARCHAR(100) NULL DEFAULT NULL,
+  `phone` VARCHAR(20) NULL DEFAULT NULL,
+  PRIMARY KEY (`customer_id`));
+
+
+-- -----------------------------------------------------
+-- Table `mydb`.`Book`
+-- -----------------------------------------------------
+CREATE TABLE IF NOT EXISTS `mydb`.`Book` (
+  `isbn` VARCHAR(13) NULL DEFAULT NULL,
+  `title` VARCHAR(100) NULL DEFAULT NULL,
+  `author` VARCHAR(100) NULL DEFAULT NULL,
+  `price` DECIMAL(10,2) NULL DEFAULT NULL,
+  `publication_date` DATE NULL DEFAULT NULL,
+  PRIMARY KEY (`isbn`));
+
+
+-- -----------------------------------------------------
+-- Table `mydb`.`Date`
+-- -----------------------------------------------------
+CREATE TABLE IF NOT EXISTS `mydb`.`Date` (
+  `epoch_date` BIGINT NULL DEFAULT NULL,
+  `date` DATE NULL DEFAULT NULL,
+  `timestamp` TIMESTAMP NOT NULL,
+  `day` INT NULL DEFAULT NULL,
+  `month` INT NULL DEFAULT NULL,
+  `year` INT NULL DEFAULT NULL,
+  `is_holiday` TINYINT NULL DEFAULT NULL,
+  PRIMARY KEY (`epoch_date`));
+
+
+-- -----------------------------------------------------
+-- Table `mydb`.`Order`
+-- -----------------------------------------------------
+CREATE TABLE IF NOT EXISTS `mydb`.`Order` (
+  `order_id` INT NULL DEFAULT NULL AUTO_INCREMENT,
+  `customer_id` INT NULL DEFAULT NULL,
+  `order_epoch_date` BIGINT NULL DEFAULT NULL,
+  `order_timestamp` TIMESTAMP NOT NULL,
+  `total_amount` DECIMAL(10,2) NULL DEFAULT NULL,
+  PRIMARY KEY (`order_id`),
+  INDEX (`customer_id` ASC) VISIBLE,
+  INDEX (`order_epoch_date` ASC, `order_timestamp` ASC) VISIBLE,
+  CONSTRAINT ``
+    FOREIGN KEY (`customer_id`)
+    REFERENCES `mydb`.`Customer` (`customer_id`),
+  CONSTRAINT ``
+    FOREIGN KEY (`order_epoch_date` , `order_timestamp`)
+    REFERENCES `mydb`.`Date` (`epoch_date` , `timestamp`));
+
+
+-- -----------------------------------------------------
+-- Table `mydb`.`Sales`
+-- -----------------------------------------------------
+CREATE TABLE IF NOT EXISTS `mydb`.`Sales` (
+  `sales_id` INT NULL DEFAULT NULL AUTO_INCREMENT,
+  `order_id` INT NULL DEFAULT NULL,
+  `isbn` VARCHAR(13) NULL DEFAULT NULL,
+  `quantity` INT NULL DEFAULT NULL,
+  `unit_price` DECIMAL(10,2) NULL DEFAULT NULL,
+  `total_price` DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price),
+  PRIMARY KEY (`sales_id`),
+  INDEX (`order_id` ASC) VISIBLE,
+  INDEX (`isbn` ASC) VISIBLE,
+  CONSTRAINT ``
+    FOREIGN KEY (`order_id`)
+    REFERENCES `mydb`.`Order` (`order_id`),
+  CONSTRAINT ``
+    FOREIGN KEY (`isbn`)
+    REFERENCES `mydb`.`Book` (`isbn`));
+
+
+-- -----------------------------------------------------
+-- Table `mydb`.`Shift`
+-- -----------------------------------------------------
+CREATE TABLE IF NOT EXISTS `mydb`.`Shift` (
+  `shift_id` INT NULL DEFAULT NULL AUTO_INCREMENT,
+  `employee_id` INT NULL DEFAULT NULL,
+  `epoch_date` BIGINT NULL DEFAULT NULL,
+  `timestamp` TIMESTAMP NOT NULL,
+  `shift_type` ENUM('morning', 'evening') NULL DEFAULT NULL,
+  PRIMARY KEY (`shift_id`),
+  INDEX (`employee_id` ASC) VISIBLE,
+  INDEX (`epoch_date` ASC, `timestamp` ASC) VISIBLE,
+  CONSTRAINT ``
+    FOREIGN KEY (`employee_id`)
+    REFERENCES `mydb`.`Employee` (`employee_id`),
+  CONSTRAINT ``
+    FOREIGN KEY (`epoch_date` , `timestamp`)
+    REFERENCES `mydb`.`Date` (`epoch_date` , `timestamp`));
+
+
+SET SQL_MODE=@OLD_SQL_MODE;
+SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
+SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
diff --git a/02_assignments/design_a_logical_model.md b/02_assignments/design_a_logical_model.md
@@ -5,6 +5,11 @@ 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/).
 
+I used MYSQL workbench to make the ERD. I found it more intuitive than making tables in Dawio or lucidchart.
+
+
+
+
 ## Question 2
 We want to create employee shifts, splitting up the day into morning and evening. Add this to the ERD.
 
@@ -13,10 +18,28 @@ The store wants to keep customer addresses. Propose two architectures for the CU
 
 _Hint, search type 1 vs type 2 slowly changing dimensions._
 
+
+Type 1 - Overwriting Address changes
+Type 2 - Retain address chjanges.
+
 Bonus: Are there privacy implications to this, why or why not?
 ```
 Your answer...
 ```
+Yes there are massive privacy implications here . 
+
+Type 1 changes, which overwrite existing data with new data, offer significant privacy advantages by minimizing the amount of personal information stored. 
+This approach reduces the risk of data breaches, as only current data is held, and simplifies compliance with privacy regulations like GDPR or CCPA. 
+Managing data subject requests is more straightforward since only the latest data needs to be reviewed or deleted, enhancing overall data security and privacy. 
+However, the lack of historical data can limit the ability to perform detailed analyses that rely on tracking changes over time, which might be necessary for certain business operations and workforce management.
+
+In contrast, Type 2 changes retain historical data by adding new records for each update, which provides a comprehensive view of a customer's history. While this approach offers valuable insights for personalized marketing,
+customer support, and fraud detection, The risk of data breaches is higher because more sensitive information is available, and managing compliance 
+becomes more complex as all historical records must be reviewed and possibly deleted upon request.
+
+I'd still argue that maintaining historical data is a bonus, not a flaw. if malicious actors needed access to customer addresses, they'd get it elsewhere. Not visit the local bookstore. The only worrying possibility is if the employees with 
+access to the data are bad actors themselves.
+
 
 ## Question 4
 Review the AdventureWorks Schema [here](https://i.stack.imgur.com/LMu4W.gif)
@@ -25,10 +48,23 @@ Highlight at least two differences between it and your ERD. Would you change any
 ```
 Your answer...
 ```
+Well... apart from the given ERD being large and complex, with many cardinalities and relationships. I noticed the following differences.
+
+1. The AdventureWorks schema is highly normalized and designed to handle a complex, enterprise-level database with many tables and relationships. It includes detailed tables for products, inventory, suppliers, and various sales channels. 
+My bookstore ERD is simplified to suit a local bookstore and focuses on essential business operations without the extensive granularity seen in AdventureWorks.
+
+2. The AdventureWorks schema has a few central tables with a lot of foreign-key references ( like the person table for instance ) . I'd argue my ERD is a little less centralized, but it's way less ubiquitous.
+
+Understanding such a complex ERD can be tedious, I'd use Graph DBs when I can here.
+
+
+
+
+
 
 # Criteria
 
-[Assignment Rubric](./assignment_rubric.md)
+[Assignment Rubric](./assignment_rubric.md)	
 
 # Submission Information
 

diff --git a/03_homework/homework_1.md b/03_homework/homework_1.md
@@ -76,3 +76,6 @@ Please do not pick the exact same tables that I have already diagramed. For exam
 	-  ![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)
 
+I established a logical relationship between the vendor,booth and ventor_booth_assignments I will be pasting the image below
+
+- 
diff --git a/03_homework/homework_2.sql b/03_homework/homework_2.sql
@@ -1,19 +1,23 @@
 --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 TOP 10 from customer ORDER BY customer_last_name,customer_first_name
 
 
 --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 BETWEEN 4 AND 9
 
 -- option 2
+SELECT * from customer_purchases WHERE product_id >= 4 AND 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:
@@ -22,21 +26,77 @@ filtered by vendor IDs between 8 and 10 (inclusive) using either:
 */
 -- option 1
 
-
+SELECT 
+    customer_id, 
+    product_id, 
+    quantity, 
+    cost_to_customer_per_qty,
+    (quantity * cost_to_customer_per_qty) AS price
+FROM 
+    purchases
+WHERE 
+    vendor_id >= 8 AND vendor_id <= 10;
+
 -- option 2
 
+SELECT 
+    customer_id, 
+    product_id, 
+    quantity, 
+    cost_to_customer_per_qty,
+    (quantity * cost_to_customer_per_qty) AS price
+FROM 
+    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 
+    products;
 
 /* 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 
+    products;
+
 
 --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 
+    v.vendor_id, 
+    v.vendor_name, 
+    vba.market_date
+FROM 
+    vendor v
+INNER JOIN 
+    vendor_booth_assignments vba ON v.vendor_id = vba.vendor_id
+ORDER BY 
+    v.vendor_name, 
+    vba.market_date;
diff --git a/03_homework/homework_3.sql b/03_homework/homework_3.sql
@@ -2,14 +2,42 @@
 /* 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. */
 
+Here are the SQL queries for each of the tasks described:
+Aggregate
 
+    Counting Vendor Booth Assignments per Vendor ID:
+
+sql
+
+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 
+    c.customer_id, 
+    c.first_name, 
+    c.last_name,
+    SUM(p.quantity * p.cost_to_customer_per_qty) AS total_spent
+FROM 
+    customers c
+JOIN 
+    customer_purchases p ON c.customer_id = p.customer_id
+GROUP BY 
+    c.customer_id, c.first_name, c.last_name
+HAVING 
+    total_spent > 2000
+ORDER BY 
+    c.last_name, c.first_name;
 
 
 --Temp Table
@@ -24,17 +52,34 @@ When inserting the new vendor, you need to appropriately align the columns to be
 VALUES(col1,col2,col3,col4,col5) 
 */
 
+CREATE TABLE temp.new_vendor AS
+SELECT * FROM vendor;
+
+INSERT INTO temp.new_vendor (vendor_id, vendor_name, vendor_type, owner_name, store_type)
+VALUES (1231321, 'Thomass Superfood Store', 'Fresh Focused', 'Thomas Rosenthal', 'Fresh Focused');
 
 
 -- 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', purchase_date) AS purchase_month, 
+    STRFTIME('%Y', purchase_date) AS purchase_year
+FROM 
+    customer_purchases;
+
+
 /* 2. Using the previous query as a base, determine how much money each customer spent in April 2019. 
 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!! */
 
+