portfolio

Project 1: CRM Database Design and Implementation

Description: SQL-based CRM system with normalized tables for clients, salespersons, contacts, addresses, and interactions
Relevance: Demonstrates database schema design, normalization, foreign key relationships, and understanding of relational data modeling—foundational skills for designing staging tables and dimensional models
Justification: Data engineers must create well-structured database schemas for data warehouses; this shows my ability to implement proper constraints and business logic

Show SQL Hide SQL

drop database if exists week3;
create database week3;
use week3;
CREATE TABLE `client` (
	`client id` INT NOT NULL AUTO_INCREMENT,
	`salesperson id` INT(100) NOT NULL,
	PRIMARY KEY (`client id`)
);

CREATE TABLE `client address` (
	`street` varchar(255) NOT NULL,
	`client id` INT(255) NOT NULL,
	`address id` INT(255) NOT NULL AUTO_INCREMENT,
	`city` varchar(255) NOT NULL,
	`state` varchar(2) NOT NULL,
	`zip` varchar(8) NOT NULL,
	`country` varchar(15) NOT NULL,
	`contact id` INT(15) NOT NULL,
	PRIMARY KEY (`address id`)
);

CREATE TABLE `salesperson` (
	`salesperson id` INT NOT NULL AUTO_INCREMENT,
	`first name` varchar(25) NOT NULL,
	`last name` varchar(25) NOT NULL,
	`email` varchar(100) NOT NULL,
	`phone number` varchar(20) NOT NULL,
	PRIMARY KEY (`salesperson id`)
);

CREATE TABLE `contact` (
	`contact id` INT NOT NULL AUTO_INCREMENT,
	`first name` varchar(50) NOT NULL,
	`last name` varchar(50) NOT NULL,
	`email` varchar(100) NOT NULL,
	`phone number` varchar(100) NOT NULL,
    `client id` int not null,
	PRIMARY KEY (`contact id`)
);

CREATE TABLE `interaction` (
	`interaction id` INT NOT NULL AUTO_INCREMENT,
	`type` varchar(255) NOT NULL,
	`date` DATETIME NOT NULL,
	`description` varchar(255) NOT NULL,
	`contact id` INT NOT NULL,
	PRIMARY KEY (`interaction id`)
);

ALTER TABLE `client` ADD CONSTRAINT `client_fk0` FOREIGN KEY (`salesperson id`) REFERENCES `salesperson`(`salesperson id`);

ALTER TABLE `client address` ADD CONSTRAINT `client address_fk1` FOREIGN KEY (`contact id`) REFERENCES `client`(`client id`);

ALTER TABLE `contact` ADD CONSTRAINT `contact_fk0` FOREIGN KEY (`client id`) REFERENCES `client`(`client id`);

ALTER TABLE `interaction` ADD CONSTRAINT `interaction_fk0` FOREIGN KEY (`contact id`) REFERENCES `contact`(`contact id`);

Project 2. Inventory Management Database System

Description: Advanced SQL implementation featuring self-referential foreign keys, junction tables for many-to-many relationships, and ENUM data types
Relevance: Shows understanding of complex data relationships, dimensional modeling patterns, and slowly changing dimensions
Justification: Modern data warehouses use similar patterns (bridge tables, hierarchical relationships, controlled vocabularies); demonstrates ability to implement advanced database patterns used in analytics platforms like Snowflake and Databricks

Show SQL Hide SQL

-- MariaDB dump 10.19  Distrib 10.11.6-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: 3.94.250.169    Database: ABC_Johnson
-- ------------------------------------------------------
-- Server version	10.6.16-MariaDB-0ubuntu0.22.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `finished_goods`
--

DROP TABLE IF EXISTS `finished_goods`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `finished_goods` (
  `finished_goods_id` int(11) NOT NULL,
  `product_name` varchar(20) DEFAULT NULL,
  `product_price` decimal(5,0) DEFAULT NULL,
  `product_ship_date` date DEFAULT NULL,
  PRIMARY KEY (`finished_goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `finished_goods`
--

LOCK TABLES `finished_goods` WRITE;
/*!40000 ALTER TABLE `finished_goods` DISABLE KEYS */;
INSERT INTO `finished_goods` VALUES
(2230,'spike',50,'2024-05-15'),
(2234,'run',40,'2024-05-15');
/*!40000 ALTER TABLE `finished_goods` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `order_inventory`
--

DROP TABLE IF EXISTS `order_inventory`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `order_inventory` (
  `raw_material_id` int(11) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `entry_type` enum('ordered','received','used','initial_inventory') DEFAULT NULL,
  KEY `raw_material_id` (`raw_material_id`),
  CONSTRAINT `order_inventory_ibfk_1` FOREIGN KEY (`raw_material_id`) REFERENCES `raw_material` (`raw_material_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `order_inventory`
--

LOCK TABLES `order_inventory` WRITE;
/*!40000 ALTER TABLE `order_inventory` DISABLE KEYS */;
INSERT INTO `order_inventory` VALUES
(2151,20,'initial_inventory');
/*!40000 ALTER TABLE `order_inventory` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `raw_material`
--

DROP TABLE IF EXISTS `raw_material`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `raw_material` (
  `raw_material_id` int(11) NOT NULL,
  `order_at` int(11) DEFAULT NULL,
  `reorder_quantity` int(11) DEFAULT NULL,
  `color` varchar(255) DEFAULT NULL,
  `size` int(3) DEFAULT NULL,
  `previous_raw_material_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`raw_material_id`),
  KEY `previous_raw_material_id` (`previous_raw_material_id`),
  CONSTRAINT `raw_material_ibfk_1` FOREIGN KEY (`previous_raw_material_id`) REFERENCES `raw_material` (`raw_material_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `raw_material`
--

LOCK TABLES `raw_material` WRITE;
/*!40000 ALTER TABLE `raw_material` DISABLE KEYS */;
INSERT INTO `raw_material` VALUES
(2151,10,20,'blue',7,NULL),
(2223,12,15,'red',9,NULL),
(2225,10,12,'green',6,NULL),
(2227,12,20,'yellow',10,2223);
/*!40000 ALTER TABLE `raw_material` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `raw_material_finished_goods`
--

DROP TABLE IF EXISTS `raw_material_finished_goods`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `raw_material_finished_goods` (
  `raw_material_id` int(11) DEFAULT NULL,
  `finished_goods_id` int(11) DEFAULT NULL,
  KEY `raw_material_id` (`raw_material_id`),
  KEY `finished_goods_id` (`finished_goods_id`),
  CONSTRAINT `raw_material_finished_goods_ibfk_1` FOREIGN KEY (`raw_material_id`) REFERENCES `raw_material` (`raw_material_id`),
  CONSTRAINT `raw_material_finished_goods_ibfk_2` FOREIGN KEY (`finished_goods_id`) REFERENCES `finished_goods` (`finished_goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `raw_material_finished_goods`
--

LOCK TABLES `raw_material_finished_goods` WRITE;
/*!40000 ALTER TABLE `raw_material_finished_goods` DISABLE KEYS */;
INSERT INTO `raw_material_finished_goods` VALUES
(2151,2230),
(2151,2234);
/*!40000 ALTER TABLE `raw_material_finished_goods` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-05-28  2:59:21

Project 3. Project Budget Development (Project Management)

Description: Excel-based budget for website development with dynamic formulas, contingency planning, and multi-worksheet organization
Relevance: Financial planning and resource allocation for technical projects
Justification: Data engineers must estimate cloud infrastructure costs, justify investments, and manage expenses for services like Snowflake, Databricks, and AWS

Project 3: XLSX spreadsheet

Project 4: Change Control Management Plan (Project Management)

Description: Formal change management process with stakeholder roles, approval authorities, and impact assessment procedures
Relevance: Production data pipelines require rigorous change management to prevent data quality issues and service disruptions
Justification: Demonstrates understanding of controlled change implementation critical in regulated environments where schema changes must follow formal approval processes

Project 4: the change management plan

Project 5: Project Schedule Analysis and Critical Path Management (Project Management)

Description: 16-week project schedule with five phases, task dependencies, and critical path analysis
Relevance: Coordinating multiple parallel workstreams with interdependencies in data engineering projects
Justification: Shows ability to plan sprints, estimate timelines, identify blockers, and communicate project status—essential for managing data warehouse migrations and multi-system integrations

Project 5 analysis