Problem

Design a database for a company that sells products online. The database should store information about the products, the customers, and the orders.
Example input:
-Product: name, price, quantity
-Customer: name, address, email
-Order: product_id, customer_id, quantity
Example output:
-The database should be able to store information about the products, the customers, and the orders.
-The database should be able to track the quantity of products in stock.
-The database should be able to track customer information so that the company can email customers updates about their orders.

Solution

/*

Solution:

CREATE TABLE Products (
  product_id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (product_id)
);

CREATE TABLE Customers (
  customer_id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  PRIMARY KEY (customer_id)
);

CREATE TABLE Orders (
  order_id INT NOT NULL AUTO_INCREMENT,
  product_id INT NOT NULL,
  customer_id INT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (order_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id),
  FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

This solution is optimal because it is normalized. The tables are in 3NF.

*/