MySQL Container + Schema Setup

🧩 Objective:

• Pull and run a MySQL container using Docker.

• Connect to it using CLI.

• Create a new database and define at least 5 tables with relationships.

Step 1: Pull the MySQL Image

Step 2: Run the MySQL Container

docker run --name my-mysql -e MYSQL_ROOT_PASSWORD=rootpass -e MYSQL_DATABASE=myapp -p 3306:3306 -d mysql:8.0

• MYSQL_ROOT_PASSWORD=rootpass: sets root password

• MYSQL_DATABASE=myapp: creates a default DB on start

• Port 3306 is exposed

Step 3: Access MySQL CLI Inside the Container

docker exec -it my-mysql mysql -uroot -prootpass

Create Schema – 5 Tables with Relationships

You’re now in MySQL. Use the myapp database and run:
USE myapp;

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0
);

CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE reviews (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
product_id INT,
rating INT CHECK (rating BETWEEN 1 AND 5),
comment TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);

✅ Deliverables:

• Screenshot or logs of:

  • Docker container running

  • MySQL login

  • Table creation queries executed successfully

• (Bonus) Insert sample data and run a JOIN query (e.g., user orders with item names)