This Afternoon
- Create a new Supplier
- Create several new products from the supplier
- Use a TRANSACTION to do this (BEGIN ... COMMIT)

This Morning, in Pairs:

0. Decide on whose database you'll use, and then connect both PGAdmins to one database
1. Create a Spreadsheet with at least 2 of your tables in your data model you made on Wednesday and Thursday
2. Add all the columns to the top of the sheet
3. Add data to the sheets
4. Make sure there is at least one Primary Key in every table that is an INT (or Serial) data type
5. Make sure you have at least one relationship (like Books and Publishers)
6. Write a JOIN between two tables

\copy publishers (publisher_id, name) FROM 'C:\Users\annhoward\Downloads\publishers.csv' DELIMITER ',' CSV HEADER;

Example Spreadsheet:
https://docs.google.com/spreadsheets/d/15pXH-OEI2ti-j2PA0STDyimExcn7BK-8JHc1vbOhTsA/edit?gid=2037872191#gid=2037872191

CREATE TABLE Suppliers (
    supplier_id SERIAL PRIMARY KEY,
    supplier_name VARCHAR(100) NOT NULL,
    contact_info TEXT
);

CREATE TABLE Products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price NUMERIC(10, 2),
    supplier_id INT,
    FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id)
);

CREATE TABLE Inventory (
    inventory_id SERIAL PRIMARY KEY,
    product_id INT,
    quantity_in_stock INT,
    reorder_level INT,
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE Replenishment (
    replenishment_id SERIAL PRIMARY KEY,
    product_id INT,
    replenishment_quantity INT,
    replenishment_date DATE,
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone_number VARCHAR(20)
);

CREATE TABLE Feedback (
    feedback_id SERIAL PRIMARY KEY,
    customer_id INT,
    feedback_text TEXT,
    feedback_date DATE,
    rating INT CHECK (rating >= 1 AND rating <= 5),  -- Assuming rating is between 1 and 5
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE Stores (
    store_id SERIAL PRIMARY KEY,
    store_location VARCHAR(100) NOT NULL
);

CREATE TABLE StoreFeedback (
    store_feedback_id SERIAL PRIMARY KEY,
    store_id INT,
    feedback_id INT,
    FOREIGN KEY (store_id) REFERENCES Stores(store_id),
    FOREIGN KEY (feedback_id) REFERENCES Feedback(feedback_id)
);