Database Creation for Fictional Company

/* Objective: Create a database from the ground up for a fictional baby merchandise company
that specializes in baby booties with 3 plausible records for each table */


-- Create necessary tables to track customers, employees, products, and purchases
CREATE SCHEMA bubsbooties;
USE bubsbooties;


-- Creating customers table


CREATE TABLE `bubsbooties`.`customers` (
  `customer_id` BIGINT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`customer_id`),
  UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE);
  
  -- Creating employees table
  
  CREATE TABLE `bubsbooties`.`employees` (
  `employee_id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NOT NULL,
  `start_date` DATE NOT NULL,
  `position` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`employee_id`));
  
  -- Creating products table
  
  CREATE TABLE `bubsbooties`.`products` (
  `product_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `price` DECIMAL(5,2) NOT NULL,
  PRIMARY KEY (`product_id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) VISIBLE);
  
  -- Creating purchases table
  
  CREATE TABLE `bubsbooties`.`customer_purchases` (
  `purchase_id` BIGINT NOT NULL,
  `customer_id` BIGINT NOT NULL,
  `purchase_date` DATE NOT NULL,
  `cost` DECIMAL(5,2) NOT NULL,
  `product_id` INT NOT NULL,
  `employee_id` INT NOT NULL,
  PRIMARY KEY (`purchase_id`));
  
  -- Creating relationships to the three lookup tables for the purchase table (fact table)
  
ALTER TABLE `bubsbooties`.`customer_purchases` 
ADD CONSTRAINT `1`
  FOREIGN KEY (`customer_id`)
  REFERENCES `bubsbooties`.`customers` (`customer_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `2`
  FOREIGN KEY (`product_id`)
  REFERENCES `bubsbooties`.`products` (`product_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `3`
  FOREIGN KEY (`employee_id`)
  REFERENCES `bubsbooties`.`employees` (`employee_id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
  
  -- Insert 3 records for each table
INSERT INTO products VALUES
(1,'Mickey Ice Cream','2022-03-23'),
(2, 'Mickey Ears', '2018-09-10'),
(3,'Tron figure','2023-03-01');

-- Customers
INSERT INTO customers VALUES
(1,'Jorge','Soto','jls@jorge.com'),
(2, 'Gen','Perez','gen@gmail.com'),
(3,'Joe','Smith','smith@gmail.com'); 

-- employees
INSERT INTO employees VALUES
(1,'Mark','Adams','Cashier','2020-03-04'),
(2,'John','Lee','Manager','2019-05-02'),
(3,'John','Soto','Sales man','2021-09-07');

-- Customer Puchases
INSERT INTO customer_purchases VALUES
(1,3,2,3,'2023-02-15','30.99'),
(2,1,1,2,'2023-02-19','6.99'),
(3,3,3,1,'2023-02-23','25.99');