DataModel.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Customers table has a one-to-many relationship with the Orders table, as one customer can place many orders.\n",
    "The Orders table has a many-to-one relationship with the Customers table, as many orders can belong to one customer.\n",
    "The Orders table has a one-to-many relationship with the Returns table, as one order can have many returns.\n",
    "The Returns table has a many-to-one relationship with the Orders table, as many returns can belong to one order.\n",
    "The Sales table has a many-to-one relationship with the Products table, as many sales can belong to one product.\n",
    "The Products table and the Orders table do not have any direct relationships with each other."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine, Column, Integer, String, ForeignKey\n",
    "from sqlalchemy.orm import relationship, sessionmaker\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "# create the database engine\n",
    "engine = create_engine('postgresql://username:pass@3567@localhost:5432/online_retailer')\n",
    "\n",
    "# create a session\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()\n",
    "\n",
    "# create the base class\n",
    "Base = declarative_base()\n",
    "\n",
    "# define the Customers table\n",
    "class Customers(Base):\n",
    "    __tablename__ = 'customers'\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    name = Column(String)\n",
    "    age = Column(Integer)\n",
    "    gender = Column(String)\n",
    "    location = Column(String)\n",
    "\n",
    "# define the Orders table\n",
    "class Orders(Base):\n",
    "    __tablename__ = 'orders'\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    customer_id = Column(Integer, ForeignKey('customers.id'))\n",
    "    date = Column(String)\n",
    "    time = Column(String)\n",
    "    product = Column(String)\n",
    "    cost = Column(Integer)\n",
    "\n",
    "    customer = relationship(\"Customers\", backref=\"orders\")\n",
    "\n",
    "# define the Products table\n",
    "class Products(Base):\n",
    "    __tablename__ = 'products'\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    name = Column(String)\n",
    "    category = Column(String)\n",
    "    price = Column(Integer)\n",
    "\n",
    "# define the Sales table\n",
    "class Sales(Base):\n",
    "    __tablename__ = 'sales'\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    product_id = Column(Integer, ForeignKey('products.id'))\n",
    "    date = Column(String)\n",
    "    total_revenue = Column(Integer)\n",
    "    units_sold = Column(Integer)\n",
    "\n",
    "    product = relationship(\"Products\", backref=\"sales\")\n",
    "\n",
    "# define the Returns table\n",
    "class Returns(Base):\n",
    "    __tablename__ = 'returns'\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    order_id = Column(Integer, ForeignKey('orders.id'))\n",
    "    reason = Column(String)\n",
    "    date = Column(String)\n",
    "    refund = Column(Integer)\n",
    "    exchange = Column(String)\n",
    "\n",
    "    order = relationship(\"Orders\", backref=\"returns\")\n",
    "\n",
    "# create the tables\n",
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By linking these tables together through primary and foreign keys, we were able to create a comprehensive data model that allowed us to answer a wide range of business questions. For example, we could use the model to:\n",
    "\n",
    ".Identify the most popular products and categories\n",
    ".Analyze sales trends over time\n",
    ".Identify customer segments based on demographics and purchasing behavior\n",
    ".Analyze the impact of returns on overall revenue and customer satisfaction\n",
    ".Create personalized marketing campaigns based on customer behavior\n",
    ".Overall, this data model helped us make more informed decisions about how to allocate resources and optimize our business strategy."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.9.7"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}