Gen AI & DBdiagram DW Schema Deisgn

Data warehouse schema design is crucial for organizing and optimizing data storage, ensuring efficient query performance, maintaining data integrity, and facilitating scalability and flexibility. In this lab, you will learn how we can leverage Generative AI to propose a data warehouse schema, create the said data warehouse in an SQL engine, and generate the schema diagram.

Objectives

For a given scenario, use Generative AI to perform the following tasks:

Propose a data warehouse schema
Generate SQL code to create the said data warehouse
Generate the image of the schema design for the data warehouse

Scenario

A fashion retail organization has hired you as a data engineer to create a data warehouse for them. They want to include data from the following sources.

Employee data
Inventory data
Sales data
Customer Profile data
Seller data

Come up with a fully planned schema diagram for the said data warehouse. You have the flexibility to propose any and all relevant attributes and tables as per requirement.

Propose DW Schema

Warehouse designing may become a daunting process in terms of the planning required to connect the data coming from various sources. In this regard, Generative AI is a useful tool to generate a proposal of how the data should be recorded comprehensively and how the data from different tables can be linked together.

Consider the following prompt.

Input 1

Create a detailed data warehouse schema for a fashion retail store that should contain employee data, sales data, inventory data, customer profiles, and seller information, listing the headers for each of the tables clearly mentioning the primary and foriegn keys for each. 
The sales data should connect to employee data, customer profile, and inventory tables. 
The inventory table should further be connected to the employee table and the seller table.

Response 1

Here is a proposed data warehouse schema for a fashion retail store:

This schema includes the following tables:

Employees: stores employee information, including their department, hire date, salary, and contact information.
Departments: stores department information, including department name and description.
Sellers: stores seller information, including their department, hire date, and contact information.
Customers: stores customer information, including their email, phone number, address, and date of birth.
Products: stores product information, including product name, description, price, and quantity.
Sales: stores sales data, including the employee who made the sale, customer, product, sale date, quantity, and sale price.
Inventory: stores inventory data, including the product and quantity.
Employee_Sales: stores employee sales data, including the employee who made the sale and the sale.
Seller_Inventory: stores seller inventory data, including the seller and product quantity.

The relationships between the tables are established through foreign keys, which ensure data consistency and prevent data duplication. For example, the Employee_Sales table is linked to the Employees table through the EmployeeID foreign key, and the Seller_Inventory table is linked to the Sellers table through the SellerID foreign key.

-- Create the database
CREATE DATABASE fashion_store;

-- Use the database
USE fashion_store;

-- Create the tables

-- Table: Employees
CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  Email VARCHAR(100) NOT NULL,
  Phone VARCHAR(20) NOT NULL,
  Address VARCHAR(255) NOT NULL,
  HireDate DATE NOT NULL,
  Salary DECIMAL(10, 2) NOT NULL,
  Department VARCHAR(50) NOT NULL,
  FOREIGN KEY (Department) REFERENCES Departments(DepartmentID)
);

-- Table: Departments
CREATE TABLE Departments (
  DepartmentID INT PRIMARY KEY,
  DepartmentName VARCHAR(50) NOT NULL,
  Description VARCHAR(255) NOT NULL
);

-- Table: Sellers
CREATE TABLE Sellers (
  SellerID INT PRIMARY KEY,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  Email VARCHAR(100) NOT NULL,
  Phone VARCHAR(20) NOT NULL,
  Address VARCHAR(255) NOT NULL,
  HireDate DATE NOT NULL,
  FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Table: Customers
CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  Email VARCHAR(100) NOT NULL,
  Phone VARCHAR(20) NOT NULL,
  Address VARCHAR(255) NOT NULL,
  DateOfBirth DATE NOT NULL,
  FOREIGN KEY (Email) REFERENCES Employees(EmployeeID)
);

-- Table: Products
CREATE TABLE Products (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100) NOT NULL,
  Description VARCHAR(255) NOT NULL,
  Price DECIMAL(10, 2) NOT NULL,
  Quantity INT NOT NULL,
  FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Table: Sales
CREATE TABLE Sales (
  SaleID INT PRIMARY KEY,
  EmployeeID INT NOT NULL,
  CustomerID INT NOT NULL,
  ProductID INT NOT NULL,
  SaleDate DATE NOT NULL,
  Quantity INT NOT NULL,
  FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
  FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Table: Inventory
CREATE TABLE Inventory (
  InventoryID INT PRIMARY KEY,
  ProductID INT NOT NULL,
  Quantity INT NOT NULL,
  FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Table: Employee_Sales
CREATE TABLE Employee_Sales (
  EmployeeID INT NOT NULL,
  SaleID INT NOT NULL,
  FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
  FOREIGN KEY (SaleID) REFERENCES Sales(SaleID)
);

-- Table: Seller_Inventory
CREATE TABLE Seller_Inventory (
  SellerID INT NOT NULL,
  ProductID INT NOT NULL,
  Quantity INT NOT NULL,
  FOREIGN KEY (SellerID) REFERENCES Sellers(SellerID),
  FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Create DW

Once the tables and their content have been defined, you can use Python and SQL to create the said warehouse. Generative AI can again help you in creating the said warehouse by proposing a code that connects to an SQL server and creates the required data warehouse.

You will leverage the chatting ability of the Generative AI model and ask it to provide an SQL code to create the data warehouse in SQL.

Input 1

Provide an SQL code to create the data warehouse discussed above.

Response 1

This SQL code creates the tables and establishes the relationships between them. The CREATE INDEX statements create indexes on the foreign keys to improve query performance.

-- Create the database
CREATE DATABASE fashion_store;

-- Use the database
USE fashion_store;

-- Create the tables

-- Table: Employees
CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  Email VARCHAR(100) NOT NULL,
  Phone VARCHAR(20) NOT NULL,
  Address VARCHAR(255) NOT NULL,
  HireDate DATE NOT NULL,
  Salary DECIMAL(10, 2) NOT NULL,
  Department VARCHAR(50) NOT NULL,
  FOREIGN KEY (Department) REFERENCES Departments(DepartmentID)
);

-- Table: Departments
CREATE TABLE Departments (
  DepartmentID INT PRIMARY KEY,
  DepartmentName VARCHAR(50) NOT NULL,
  Description VARCHAR(255) NOT NULL
);

-- Table: Sellers
CREATE TABLE Sellers (
  SellerID INT PRIMARY KEY,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  Email VARCHAR(100) NOT NULL,
  Phone VARCHAR(20) NOT NULL,
  Address VARCHAR(255) NOT NULL,
  HireDate DATE NOT NULL,
  DepartmentID INT PRIMARY KEY,
  FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Table: Customers
CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  Email VARCHAR(100) NOT NULL,
  Phone VARCHAR(20) NOT NULL,
  Address VARCHAR(255) NOT NULL,
  DateOfBirth DATE NOT NULL,
  FOREIGN KEY (Email) REFERENCES Employees(EmployeeID)
);

-- Table: Products
CREATE TABLE Products (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100) NOT NULL,
  Description VARCHAR(255) NOT NULL,
  Price DECIMAL(10, 2) NOT NULL,
  Quantity INT NOT NULL,
  DepartmentID INT PRIMARY KEY,
  FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Table: Sales
CREATE TABLE Sales (
  SaleID INT PRIMARY KEY,
  EmployeeID INT NOT NULL,
  CustomerID INT NOT NULL,
  ProductID INT NOT NULL,
  SaleDate DATE NOT NULL,
  Quantity INT NOT NULL,
  FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
  FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Table: Inventory
CREATE TABLE Inventory (
  InventoryID INT PRIMARY KEY,
  ProductID INT NOT NULL,
  Quantity INT NOT NULL,
  FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Table: Employee_Sales
CREATE TABLE Employee_Sales (
  EmployeeID INT NOT NULL,
  SaleID INT NOT NULL,
  FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
  FOREIGN KEY (SaleID) REFERENCES Sales(SaleID)
);

-- Table: Seller_Inventory
CREATE TABLE Seller_Inventory (
  SellerID INT NOT NULL,
  ProductID INT NOT NULL,
  Quantity INT NOT NULL,
  FOREIGN KEY (SellerID) REFERENCES Sellers(SellerID),
  FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Create the relationships between tables
CREATE INDEX idx_EmployeeID ON Sales(EmployeeID);
CREATE INDEX idx_CustomerID ON Sales(CustomerID);
CREATE INDEX idx_ProductID ON Sales(ProductID);
CREATE INDEX idx_SellerID ON Sales(EmployeeID);
CREATE INDEX idx_ProductID ON Sales(ProductID);
CREATE INDEX idx_SellerID ON Seller_Inventory(SellerID);
CREATE INDEX idx_ProductID ON Seller_Inventory(ProductID);

Generate Schema Design

To generate the schema design, you are going to navigate to DBdiagram.io. This website provides an online tool that can be used to create the schema diagram for any database. You are going to use the SQL code generated above to visualize the schema design for the proposed data warehouse.

Steps:

Click here to navigate to the DBdiagram.io.
On the homepage, click “Create your diagram”.
You will see an interface with a default example. Click the top right symbol to login to the interface
Here, you will get an option to login using either your Google, GitHub or any other email ID for free. Login to the system using any of the means available.
On the top left corner of the logged in interface, when you hover your cursor, you can see the option to create a ‘New Diagram’.
For this diagram, you can now hover your cursor over the upload symbol, as shown in the image below, and you will find an option to ‘Import from MySQL’ which you should click.
Copy the code generated in the previous step and paste it in this interface. Once done, click Submit.
The resulting interface will have a modified code, which is being used to create the schema design. In sequence, press the tabs on the interface as shown in the image below. The first tab will close the coding interface, giving the resulting schema design a lot more room for visibility. The second tab resizes the diagram to fit the screen. The third tab highlights the interconnections between the tables.
You can further rearrange the tables manually as per your preference. The final schema design is visible as shown in the image below.
By disabling the dark mode, as shown in the image below, the schema can also be seen in the light mode.