The following are tasks I completed in MySQl workbench for a course project.

Scenario: The client wants you to track information on his customers (first name, last name, email),

his employees (first name, last name, start date, position ), his products, and the

purchases customers make (which customer, when it was purchased, for how much money).

This is an outline I wrote to plan out what tables I wanted, and what columns the client would need.

-- table: customers

-- cust id, first name, last name, email

-- table: employees

-- emp id first name, last name, start date, position

-- table: products

-- product id, product name

-- table: cust purchases

-- cust purchase id, cust id, product id, purchased at, amount_usd

Next, I created an EER (Enhanced Entity-Relationship) Diagram. I included a primary key in each table, and used foreign keys to relate everything to the customer_purchases table. I used reasonable data types for each column.

Next, I created a schema for the client using Workbench's UI. This could also be accomplished with code, using the CREATE SCHEMA function. I created tables, columns, and their corresponding data types using the EER diagram I made.

I also added Nonnull constraints to the columns where that constraint matched the data context, and made the "email" column from the customers table Unique to help ensure data consistency and accuracy.

I inserted three records into each table to ensure everything was working smoothly! I ran a few queries to confirm the tables were relating to each other correctly. My queries ran and produced the predicted output.

USE bubsbooties;

SELECT * FROM customers;

INSERT INTO customers VALUES

('1', 'Marlene', 'Calsbeek', 'abc@xyz.com'),

('2', 'Arlene', 'Ogle', 'xyz@abc.com'),

('3', 'Eldrige', 'Ogle', 'email@gov.com');

SELECT * FROM employees;

INSERT INTO employees VALUES

('1', 'Ted', 'Mosby', '2001-01-01', 'Architect'),

('2', 'Robin', 'Scherbatsky', '2002-02-02', 'Reporter'),

('3', 'Marshall', 'Erickson', '2003-03-03', 'Lawyer');

SELECT * FROM products;

INSERT INTO products VALUES

('1', 'chocolatefrogs', '2001-03-24'),

('2', 'fizzingwhizzbees', '2001-5-15'),

('3', 'pumpkinpasty', '2002-02-24');

SELECT * FROM customer_purchases;

INSERT INTO customer_purchases VALUES

('1', '1', '2', '1'),

('2', '3', '1', '3'),

('3', '2', '3', '2');

SELECT

cust_purch.customer_purchase_id,

customers.first_name,

customers.last_name,

products.product_id,

products.product_name

FROM customer_purchases cust_purch

JOIN customers ON cust_purch.customer_id = customers.customer_id

JOIN products ON products.product_id= cust_purch.product_id

WHERE cust_purch.customer_purchase_id = '1';

To wrap up, I created two user accounts to give the client access to the database. One was a account for a DBA who would get full admin privileges. The second account was for an analyst, and required read only access.

To conclude, this database was created to ensure an optimized data environment that would meet the clients data requirements. This database is set up in a way that allows for optimized query writing, analysis and reporting. It's also designed to tolerate high amounts of data in the case that the business grows significantly.