Part 1: ACME Relational Database Design with PostgreSQL

Data Exploration to Determine Schema Design

import pandas as pd
import os

csv_path = 'data'

customers_df = pd.read_csv(os.path.join(csv_path, 'customers.csv'))
phones_df = pd.read_csv(os.path.join(csv_path, 'customer_phones.csv'))
emails_df = pd.read_csv(os.path.join(csv_path, 'customer_emails.csv'))
addresses_df = pd.read_csv(os.path.join(csv_path, 'customer_addresses.csv'))
products_df = pd.read_csv(os.path.join(csv_path, 'products.csv'))
purchases_df = pd.read_csv(os.path.join(csv_path, 'purchases.csv'))
ratings_df = pd.read_csv(os.path.join(csv_path, 'user_ratings.csv'))

# TODO: explore datasets to identify schema needs (create more cells as needed)

def df_eda(x):
    df = x

    if df.empty:
        print("Given dataframe is empty please review your input dataframe")
    else:
        print('************** data information *********************')
        print(df.info())
        print('************* data statistics  **********************')
        print(df.describe(include='all'))
        print('************** null validation  *********************')
        print(df.isnull().sum())
        print('************** duplicate validation  *********************')
        print(df.duplicated().sum())
        print('************** sample dataset rows ******************')
        print(df.head(5))
        

# Performing EDA on customers dataset
df_eda(customers_df)
print('***************************************')
print([id.item() for id in customers_df['customer_id'].unique()])

************** data information *********************
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 15 entries, 0 to 14
Data columns (total 4 columns):
 #   Column       Non-Null Count  Dtype 
---  ------       --------------  ----- 
 0   customer_id  15 non-null     int64 
 1   first_name   15 non-null     object
 2   last_name    15 non-null     object
 3   created_at   15 non-null     object
dtypes: int64(1), object(3)
memory usage: 608.0+ bytes
None
************* data statistics  **********************
        customer_id first_name   last_name           created_at
count     15.000000         15          15                   15
unique          NaN         15          15                   15
top             NaN      Alice  Wonderland  2024-01-15 10:30:00
freq            NaN          1           1                    1
mean       9.600000        NaN         NaN                  NaN
std        5.877317        NaN         NaN                  NaN
min        1.000000        NaN         NaN                  NaN
25%        5.000000        NaN         NaN                  NaN
50%        9.000000        NaN         NaN                  NaN
75%       14.500000        NaN         NaN                  NaN
max       19.000000        NaN         NaN                  NaN
************** null validation  *********************
customer_id    0
first_name     0
last_name      0
created_at     0
dtype: int64
************** duplicate validation  *********************
0
************** sample dataset rows ******************
   customer_id first_name   last_name           created_at
0            1      Alice  Wonderland  2024-01-15 10:30:00
1            2        Bob    Martinez  2024-01-16 11:45:00
2            3    Charlie   Chocolate  2024-01-17 09:20:00
3            4    Dorothy        Gale  2024-01-18 14:15:00
4            6      Frodo     Baggins  2024-01-20 08:45:00
***************************************
[1, 2, 3, 4, 6, 7, 8, 9, 10, 12, 14, 15, 16, 18, 19]

# Performing EDA on phones dataset
df_eda(phones_df)
print('***************************************')
print(f"Customer Id's in Phone: {[id.item() for id in phones_df['customer_id'].unique()]}")
print(f"Phone Id's in Phone: {[id.item() for id in phones_df['phone_id'].unique()]}")
print('***************************************')
print(phones_df.groupby('customer_id')['phone_id'].count())
print('***************************************')
print(phones_df[phones_df['customer_id'].isin([1,3,8,15])])

************** data information *********************
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 19 entries, 0 to 18
Data columns (total 4 columns):
 #   Column       Non-Null Count  Dtype 
---  ------       --------------  ----- 
 0   phone_id     19 non-null     int64 
 1   customer_id  19 non-null     int64 
 2   phone        19 non-null     object
 3   created_at   19 non-null     object
dtypes: int64(2), object(2)
memory usage: 736.0+ bytes
None
************* data statistics  **********************
         phone_id  customer_id     phone           created_at
count   19.000000    19.000000        19                   19
unique        NaN          NaN        19                   19
top           NaN          NaN  555-0101  2024-01-15 10:30:00
freq          NaN          NaN         1                    1
mean    12.526316     9.000000       NaN                  NaN
std      7.805756     5.897269       NaN                  NaN
min      1.000000     1.000000       NaN                  NaN
25%      5.500000     3.500000       NaN                  NaN
50%     12.000000     8.000000       NaN                  NaN
75%     19.500000    14.500000       NaN                  NaN
max     25.000000    19.000000       NaN                  NaN
************** null validation  *********************
phone_id       0
customer_id    0
phone          0
created_at     0
dtype: int64
************** duplicate validation  *********************
0
************** sample dataset rows ******************
   phone_id  customer_id     phone           created_at
0         1            1  555-0101  2024-01-15 10:30:00
1         2            1  555-0102  2024-01-15 10:31:00
2         3            2  555-0103  2024-01-16 11:45:00
3         4            3  555-0104  2024-01-17 09:20:00
4         5            3  555-0105  2024-01-17 09:21:00
***************************************
Customer Id's in Phone: [1, 2, 3, 4, 6, 7, 8, 9, 10, 12, 14, 15, 16, 18, 19]
Phone Id's in Phone: [1, 2, 3, 4, 5, 6, 9, 10, 11, 12, 13, 14, 17, 19, 20, 21, 22, 24, 25]
***************************************
customer_id
1     2
2     1
3     2
4     1
6     1
7     1
8     2
9     1
10    1
12    1
14    1
15    2
16    1
18    1
19    1
Name: phone_id, dtype: int64
***************************************
    phone_id  customer_id     phone           created_at
0          1            1  555-0101  2024-01-15 10:30:00
1          2            1  555-0102  2024-01-15 10:31:00
3          4            3  555-0104  2024-01-17 09:20:00
4          5            3  555-0105  2024-01-17 09:21:00
8         11            8  555-0111  2024-01-22 10:10:00
9         12            8  555-0112  2024-01-22 10:11:00
14        20           15  555-0120  2024-01-29 13:45:00
15        21           15  555-0121  2024-01-29 13:46:00

# Performing EDA on emails dataset
df_eda(emails_df)
print('***************************************')
print(f"Customer Id's in emails df: {[id.item() for id in emails_df['customer_id'].unique()]}")
print(f"email Id's in email : {[id.item() for id in emails_df['email_id'].unique()]}")
print('***************************************')
print(emails_df.groupby('customer_id')['email_id'].count())
# print('***************************************')
print(emails_df[emails_df['customer_id'].isin([1])])

************** data information *********************
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 16 entries, 0 to 15
Data columns (total 4 columns):
 #   Column       Non-Null Count  Dtype 
---  ------       --------------  ----- 
 0   email_id     16 non-null     int64 
 1   customer_id  16 non-null     int64 
 2   email        16 non-null     object
 3   created_at   16 non-null     object
dtypes: int64(2), object(2)
memory usage: 640.0+ bytes
None
************* data statistics  **********************
         email_id  customer_id                  email           created_at
count   16.000000     16.00000                     16                   16
unique        NaN          NaN                     16                   16
top           NaN          NaN  [email protected]  2024-01-15 10:30:00
freq          NaN          NaN                      1                    1
mean    10.375000      9.06250                    NaN                  NaN
std      6.163062      6.07145                    NaN                  NaN
min      1.000000      1.00000                    NaN                  NaN
25%      4.750000      3.75000                    NaN                  NaN
50%     10.500000      8.50000                    NaN                  NaN
75%     15.250000     14.25000                    NaN                  NaN
max     20.000000     19.00000                    NaN                  NaN
************** null validation  *********************
email_id       0
customer_id    0
email          0
created_at     0
dtype: int64
************** duplicate validation  *********************
0
************** sample dataset rows ******************
   email_id  customer_id                           email           created_at
0         1            1           [email protected]  2024-01-15 10:30:00
1         2            1      [email protected]  2024-01-15 10:31:00
2         3            2               [email protected]  2024-01-16 11:45:00
3         4            3  [email protected]  2024-01-17 09:20:00
4         5            4            [email protected]  2024-01-18 14:15:00
***************************************
Customer Id's in emails df: [1, 2, 3, 4, 6, 7, 8, 9, 10, 12, 14, 15, 16, 18, 19]
email Id's in email : [1, 2, 3, 4, 5, 8, 9, 10, 11, 12, 14, 15, 16, 17, 19, 20]
***************************************
customer_id
1     2
2     1
3     1
4     1
6     1
7     1
8     1
9     1
10    1
12    1
14    1
15    1
16    1
18    1
19    1
Name: email_id, dtype: int64
   email_id  customer_id                       email           created_at
0         1            1       [email protected]  2024-01-15 10:30:00
1         2            1  [email protected]  2024-01-15 10:31:00

# Performing EDA on addresses dataset
df_eda(addresses_df)
print('***************************************')
print(f"Customer Id's in emails df: {[id.item() for id in addresses_df['customer_id'].unique()]}")
print('***************************************')
print(addresses_df.groupby('customer_id')['address'].nunique())

************** data information *********************
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 32 entries, 0 to 31
Data columns (total 4 columns):
 #   Column       Non-Null Count  Dtype 
---  ------       --------------  ----- 
 0   address_id   32 non-null     int64 
 1   customer_id  32 non-null     int64 
 2   address      32 non-null     object
 3   created_at   32 non-null     object
dtypes: int64(2), object(2)
memory usage: 1.1+ KB
None
************* data statistics  **********************
        address_id  customer_id             address           created_at
count    32.000000    32.000000                  32                   32
unique         NaN          NaN                  32                   32
top            NaN          NaN  1 Rabbit Hole Lane  2024-01-15 10:30:00
freq           NaN          NaN                   1                    1
mean     19.250000     9.687500                 NaN                  NaN
std      10.737333     5.738593                 NaN                  NaN
min       1.000000     1.000000                 NaN                  NaN
25%      10.750000     5.500000                 NaN                  NaN
50%      20.000000     9.000000                 NaN                  NaN
75%      28.250000    15.000000                 NaN                  NaN
max      36.000000    19.000000                 NaN                  NaN
************** null validation  *********************
address_id     0
customer_id    0
address        0
created_at     0
dtype: int64
************** duplicate validation  *********************
0
************** sample dataset rows ******************
   address_id  customer_id                    address           created_at
0           1            1         1 Rabbit Hole Lane  2024-01-15 10:30:00
1           2            1        42 Tea Party Circle  2024-01-15 10:31:00
2           3            2  123 Construction Site Ave  2024-01-16 11:45:00
3          21            2          456 Toolbox Drive  2024-01-16 11:46:00
4           4            3     456 Candy Factory Road  2024-01-17 09:20:00
***************************************
Customer Id's in emails df: [1, 2, 3, 4, 6, 7, 8, 9, 10, 12, 14, 15, 16, 18, 19]
***************************************
customer_id
1     2
2     2
3     2
4     2
6     3
7     2
8     2
9     2
10    2
12    2
14    2
15    2
16    3
18    2
19    2
Name: address, dtype: int64

# Performing EDA on products dataset
print(products_df)

   product_id           product_name product_type  \
0           1  Industrial Large Gear   large_gear   
1           2   Precision Small Gear   small_gear   

                                         description  price  stock_quantity  \
0  Heavy-duty large gear for industrial applications  450.0              50   
1             Precision small gear for detailed work   75.0             200   

            created_at  
0  2024-01-01 08:00:00  
1  2024-01-01 08:00:00  

# Performing EDA on emails dataset
df_eda(purchases_df)
# print('***************************************')
print(f"Customer Id's in purchases df: {[id.item() for id in purchases_df['customer_id'].unique()]}")
# print('***************************************')
print(purchases_df.groupby('customer_id')['purchase_id'].count())

************** data information *********************
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 21 entries, 0 to 20
Data columns (total 9 columns):
 #   Column                 Non-Null Count  Dtype  
---  ------                 --------------  -----  
 0   purchase_id            21 non-null     int64  
 1   customer_id            21 non-null     int64  
 2   large_gear_quantity    21 non-null     int64  
 3   small_gear_quantity    21 non-null     int64  
 4   large_gear_unit_price  21 non-null     float64
 5   small_gear_unit_price  21 non-null     float64
 6   total_price            21 non-null     float64
 7   purchase_date          21 non-null     object 
 8   status                 21 non-null     object 
dtypes: float64(3), int64(4), object(2)
memory usage: 1.6+ KB
None
************* data statistics  **********************
        purchase_id  customer_id  large_gear_quantity  small_gear_quantity  \
count     21.000000    21.000000            21.000000            21.000000   
unique          NaN          NaN                  NaN                  NaN   
top             NaN          NaN                  NaN                  NaN   
freq            NaN          NaN                  NaN                  NaN   
mean      14.000000     9.428571             1.190476             8.190476   
std        8.831761     6.021390             1.123345             5.895923   
min        1.000000     1.000000             0.000000             0.000000   
25%        7.000000     4.000000             0.000000             5.000000   
50%       14.000000     9.000000             1.000000             8.000000   
75%       21.000000    15.000000             2.000000            12.000000   
max       30.000000    19.000000             4.000000            20.000000   

        large_gear_unit_price  small_gear_unit_price  total_price  \
count               21.000000              21.000000    21.000000   
unique                    NaN                    NaN          NaN   
top                       NaN                    NaN          NaN   
freq                      NaN                    NaN          NaN   
mean               300.000000              60.714286  1150.000000   
std                217.370651              30.178043   397.806486   
min                  0.000000               0.000000   375.000000   
25%                  0.000000              75.000000   900.000000   
50%                450.000000              75.000000  1200.000000   
75%                450.000000              75.000000  1350.000000   
max                450.000000              75.000000  1800.000000   

              purchase_date     status  
count                    21         21  
unique                   21          1  
top     2024-02-01 10:30:00  completed  
freq                      1         21  
mean                    NaN        NaN  
std                     NaN        NaN  
min                     NaN        NaN  
25%                     NaN        NaN  
50%                     NaN        NaN  
75%                     NaN        NaN  
max                     NaN        NaN  
************** null validation  *********************
purchase_id              0
customer_id              0
large_gear_quantity      0
small_gear_quantity      0
large_gear_unit_price    0
small_gear_unit_price    0
total_price              0
purchase_date            0
status                   0
dtype: int64
************** duplicate validation  *********************
0
************** sample dataset rows ******************
   purchase_id  customer_id  large_gear_quantity  small_gear_quantity  \
0            1            1                    2                   10   
1            2            2                    0                    5   
2            3            3                    1                    8   
3            4            4                    3                    0   
4            6            6                    0                   12   

   large_gear_unit_price  small_gear_unit_price  total_price  \
0                  450.0                   75.0       1650.0   
1                    0.0                   75.0        375.0   
2                  450.0                   75.0       1050.0   
3                  450.0                    0.0       1350.0   
4                    0.0                   75.0        900.0   

         purchase_date     status  
0  2024-02-01 10:30:00  completed  
1  2024-02-01 11:15:00  completed  
2  2024-02-01 14:20:00  completed  
3  2024-02-02 09:45:00  completed  
4  2024-02-02 15:10:00  completed  
Customer Id's in purchases df: [1, 2, 3, 4, 6, 7, 8, 9, 10, 12, 14, 15, 16, 18, 19]
customer_id
1     2
2     1
3     2
4     1
6     1
7     2
8     1
9     2
10    1
12    1
14    1
15    2
16    1
18    1
19    2
Name: purchase_id, dtype: int64

purchases_df

	purchase_id	customer_id	large_gear_quantity	small_gear_quantity	large_gear_unit_price	small_gear_unit_price	total_price	purchase_date	status
0	1	1	2	10	450.0	75.0	1650.0	2024-02-01 10:30:00	completed
1	2	2	0	5	0.0	75.0	375.0	2024-02-01 11:15:00	completed
2	3	3	1	8	450.0	75.0	1050.0	2024-02-01 14:20:00	completed
3	4	4	3	0	450.0	0.0	1350.0	2024-02-02 09:45:00	completed
4	6	6	0	12	0.0	75.0	900.0	2024-02-02 15:10:00	completed
5	7	7	2	6	450.0	75.0	1350.0	2024-02-03 10:20:00	completed
6	8	8	1	10	450.0	75.0	1200.0	2024-02-03 11:45:00	completed
7	9	9	4	0	450.0	0.0	1800.0	2024-02-03 14:15:00	completed
8	10	10	0	20	0.0	75.0	1500.0	2024-02-04 09:30:00	completed
9	12	12	1	5	450.0	75.0	825.0	2024-02-04 15:45:00	completed
10	14	14	0	15	0.0	75.0	1125.0	2024-02-05 11:30:00	completed
11	15	15	2	12	450.0	75.0	1800.0	2024-02-05 14:20:00	completed
12	16	16	1	0	450.0	0.0	450.0	2024-02-06 09:45:00	completed
13	18	18	2	6	450.0	75.0	1350.0	2024-02-06 15:30:00	completed
14	19	19	1	10	450.0	75.0	1200.0	2024-02-07 10:20:00	completed
15	21	1	0	10	0.0	75.0	750.0	2024-02-08 09:30:00	completed
16	22	3	2	0	450.0	0.0	900.0	2024-02-08 13:20:00	completed
17	24	7	0	12	0.0	75.0	900.0	2024-02-09 10:30:00	completed
18	25	9	2	6	450.0	75.0	1350.0	2024-02-09 14:20:00	completed
19	28	15	0	20	0.0	75.0	1500.0	2024-02-10 11:30:00	completed
20	30	19	1	5	450.0	75.0	825.0	2024-02-10 16:20:00	completed

# Performing EDA on ratings dataset
ratings_df

	rating_id	customer_id	product_id	rating	review_text	rating_date
0	1	1	1	5	Excellent quality large gear. Very durable.	2024-02-05 10:30:00
1	2	1	2	4	Good small gear for the price.	2024-02-05 10:35:00
2	3	2	2	5	Perfect for high-speed operations!	2024-02-06 11:15:00
3	4	3	1	4	Solid performance on large gear.	2024-02-06 14:20:00
4	5	3	2	3	Decent small gear but could be better.	2024-02-06 14:25:00
5	6	4	1	5	Best value for a large gear.	2024-02-07 09:45:00
6	9	6	2	4	Works well for most applications.	2024-02-08 15:10:00
7	10	7	1	5	Outstanding large gear quality.	2024-02-08 10:20:00
8	11	7	2	4	Reliable small gear.	2024-02-08 10:25:00
9	12	8	1	3	Average large gear.	2024-02-09 11:45:00
10	13	8	2	5	Excellent small gear!	2024-02-09 11:50:00
11	14	9	1	4	Very satisfied with this large gear.	2024-02-09 14:15:00
12	15	10	2	5	Top notch small gear.	2024-02-10 09:30:00
13	18	12	1	4	Meets our needs.	2024-02-11 15:45:00
14	20	15	1	4	Solid choice for large gear.	2024-02-12 14:20:00

Observations

• Customers:
  1. There are total 15 unique customers, ranging from 1 to 19, where id's 5,11 and 13 are missing.
  2. There are no null or missing values.
  3. Customer First & Last name needs to converted to String and created date needs to be converted to datetime.
  4. customer_id - Primary Key, First & Last Names - Ideally both should not be empty, but one can be empty, but as we don't have any empty, we can leave without NOT NULL CONSTRAINT and Created_at can not be null hence it can be have NOT NULL Constraint.
• Phones:
  1. Phones dataset has 19 rows with customers 1, 3, 8 and 15 having 2 phones numbers each.
  2. Customers data join to Phones using customer_id as foreign key using 1 to Many relation.
• Emails:
  1. Emails dataset has 16 rows with customer 1 having 2 email id's.
  2. There is one to many relation between customer and email dataset.
• Address:
  1. Each customer has two address and there is no active/current or inactive/old indicators.
  2. Customer Joins to Address (One-to-Many)
• Products:
  1. This is pure lookup table and contains only two products, Small gear and large gear and currently only one version of pricing is available based on created_at. Hence we can consider future price changes will be added as additional rather than updating existing records.
• Purchases:
  1. Current Purchases table has multiple purchases by some customers.
  2. Current data structure fails 1-NF due to having product related quantity and unit price as individual columns.
  3. Also Total_Price is depends on each of the product related columns.
  4. To satisfy table 1 - 3 NFs, we need to split the table to minimum 2 tables and assuming total_price is received as part of payment transaction and not calculated, if it is calculated, it needs to be removed form these tables and move to separate payment table.
• Ratings:
  1. Based on given dataset, ratings are at Customer and Product level and there is only one rating per customer per product.
  2. Customer_ID and Product_ID are foreign keys and rating id is primary key.
  3. Table in given structutre satisfies 1-3NFs hence no changes needed.

Logical Representation of Relational tables

• CUSTOMERS (CUSTOMER_ID PK)

  • CUSTOMER_ID -- (1:N) JOIN PHONES (PHONE_ID PK) ON (CUSTOMER_ID FK)
  • CUSTOMER_ID -- (1:N) JOIN EMAILS (EMAIL_ID PK) ON (CUSTOMER_ID FK)
  • CUSTOMER_ID -- (1:N) JOIN ADDRESS (ADDRESS_ID PK) ON (CUSTOMER_ID FK)

• PURCHASES: To Satisfy 1-3NF, need to split table to multiple tables as below.

  • CUST_PURCHASES: Fields - PURCHASE_ID (PK), CUSTOMER_ID (FK), TOTAL_PRICE, STATUS
  • PURCHASE_DTLS: Fields - PURCHASE_ID , PRODUCT_ID , QUANTITY, UNIT_PRICE , PURCHASE_DATE (PURCHASE_ID & PRODUCT_ID - COMPOSITE PK)

• PRODUCTS (PRODUCT_ID PK)

  • JOIN with PURCHASES table's using PRODUCT_ID

• RATINGS ( RATING_ID PK)

  • CUSTOMER_ID (FK) * PRODUCT_ID (FK)

type(csv_path)
os.listdir(csv_path)

['user_ratings.csv',
 'products.csv',
 'purchase_dtls.csv',
 'customer_purchases.csv',
 'purchases.csv',
 'customers.csv',
 'customer_addresses.csv',
 'customer_phones.csv',
 'customer_emails.csv']

print(purchases_df[purchases_df['customer_id'] == 1])

customer_purchases = purchases_df[['purchase_id','customer_id','total_price','purchase_date','status']]

    purchase_id  customer_id  large_gear_quantity  small_gear_quantity  \
0             1            1                    2                   10   
15           21            1                    0                   10   

    large_gear_unit_price  small_gear_unit_price  total_price  \
0                   450.0                   75.0       1650.0   
15                    0.0                   75.0        750.0   

          purchase_date     status  
0   2024-02-01 10:30:00  completed  
15  2024-02-08 09:30:00  completed  

products_df[products_df['product_type']=='small_gear'].product_id.item()

2

purchases_df[['purchase_id','large_gear_quantity','large_gear_unit_price']][purchases_df['large_gear_quantity']>0]

	purchase_id	large_gear_quantity	large_gear_unit_price
0	1	2	450.0
2	3	1	450.0
3	4	3	450.0
5	7	2	450.0
6	8	1	450.0
7	9	4	450.0
9	12	1	450.0
11	15	2	450.0
12	16	1	450.0
13	18	2	450.0
14	19	1	450.0
16	22	2	450.0
18	25	2	450.0
20	30	1	450.0

purchase_dtls = pd.DataFrame(columns=['purchase_id','product_id','quantity','unit_price'])

large_gear_purchases = purchases_df[['purchase_id','large_gear_quantity','large_gear_unit_price']][purchases_df['large_gear_quantity']>0].copy()
large_gear_purchases.columns = ['purchase_id','quantity','unit_price']
large_gear_purchases['product_id'] = products_df[products_df['product_type']=='large_gear'].product_id.item()

small_gear_purchases = purchases_df[['purchase_id','small_gear_quantity','small_gear_unit_price']][purchases_df['small_gear_quantity']>0].copy()
small_gear_purchases.columns = ['purchase_id','quantity','unit_price']
small_gear_purchases['product_id'] = products_df[products_df['product_type']=='small_gear'].product_id.item()

purchase_dtls = pd.concat([large_gear_purchases,small_gear_purchases]).sort_values(by='purchase_id').reset_index(drop=True)

# customer_purchases & purchase_dtls validation

#print(f')

print(f" Total No. customers purchased large gear in purchases_df: \
{purchases_df[purchases_df['large_gear_quantity']>0]['customer_id'].count().item()}")
print(f" Total No. purchases with large gear in purchase_dtls: \
{purchase_dtls[purchase_dtls['product_id'] == 1]['purchase_id'].count().item()}")

print(f" Total No. customers purchased small gear in purchases_df: \
{purchases_df[purchases_df['small_gear_quantity']>0]['customer_id'].count().item()}")
print(f" Total No. purchases with small gear in purchase_dtls: \
{purchase_dtls[purchase_dtls['product_id'] == 2]['purchase_id'].count().item()}")


print(f"Total Large gear purchase quantity in purchase df: {purchases_df['large_gear_quantity'].sum().item()}")
print(f"Total Large gear purchase quantity in purchase dtls: \
{purchase_dtls[purchase_dtls['product_id'] == 1]['quantity'].sum()}")

print(f"Total Small gear purchase quantity in purchase df: {purchases_df['small_gear_quantity'].sum().item()}")
print(f"Total Small gear purchase quantity in purchase dtls: \
{purchase_dtls[purchase_dtls['product_id'] == 2]['quantity'].sum()}")

print(f"Aggregated Total Price in purchase df: {purchases_df.total_price.sum().item()}" )
print(f"Aggregated total Price in customer_purchases: {customer_purchases.total_price.sum().item()}")

 Total No. customers purchased large gear in purchases_df: 14
 Total No. purchases with large gear in purchase_dtls: 14
 Total No. customers purchased small gear in purchases_df: 17
 Total No. purchases with small gear in purchase_dtls: 17
Total Large gear purchase quantity in purchase df: 25
Total Large gear purchase quantity in purchase dtls: 25
Total Small gear purchase quantity in purchase df: 172
Total Small gear purchase quantity in purchase dtls: 172
Aggregated Total Price in purchase df: 24150.0
Aggregated total Price in customer_purchases: 24150.0

Creating new csv files for Normalizing Purchase Tables.

As below exisiting data load and query code doesn't exists multiple purchase csv files to support purchases table is splitted to Normalize and considering expectaion dataload script need to load the csv files into the tables created and all tables should be normalized. Going with the option to create new csv files into source folder to load the normalized purchase tables through existing script.

To consider existing Purchase csv file structure as Normalized, multiple product related columns does not support 1NF, All non key columns does not support entire primary key alone or each column in a combined primary key and total_price is a derived column based on non key columns, won't support 3NF. Hence, creating a table to load purchases csv but creating separate tables to load Normalize purchase data.

customer_purchases_write_path = os.path.join(csv_path, 'customer_purchases.csv')
purchase_dtls_write_path = os.path.join(csv_path, 'purchase_dtls.csv')

customer_purchases.to_csv(customer_purchases_write_path, index=False)
purchase_dtls.to_csv(purchase_dtls_write_path, index=False)

#validate file write
test_cp_df = pd.read_csv(os.path.join(csv_path, 'customer_purchases.csv'))
print(test_cp_df.head(2))

test_pdtls_df = pd.read_csv(os.path.join(csv_path, 'purchase_dtls.csv'))
print(test_pdtls_df.head(2))

   purchase_id  customer_id  total_price        purchase_date     status
0            1            1       1650.0  2024-02-01 10:30:00  completed
1            2            2        375.0  2024-02-01 11:15:00  completed
   purchase_id  quantity  unit_price  product_id
0            1         2       450.0           1
1            1        10        75.0           2

type(csv_path)
os.listdir(csv_path)

['user_ratings.csv',
 'products.csv',
 'purchase_dtls.csv',
 'customer_purchases.csv',
 'purchases.csv',
 'customers.csv',
 'customer_addresses.csv',
 'customer_phones.csv',
 'customer_emails.csv']

Relational Schema DDL

%load_ext sql
%sql postgresql://postgres:[email protected]:5432/postgres

%config SqlMagic.autopandas = True
%config SqlMagic.feedback = False
%config SqlMagic.displaycon = False

print("Connected to PostgreSQL")

Connecting to 'postgresql://postgres:****@db-host:5432/postgres'

Connected to PostgreSQL

%%sql
-- Drop existing tables to start fresh
DROP TABLE IF EXISTS customer_purchases CASCADE;
DROP TABLE IF EXISTS purchase_dtls CASCADE;
DROP TABLE IF EXISTS user_ratings CASCADE;
DROP TABLE IF EXISTS purchases CASCADE;
DROP TABLE IF EXISTS customer_addresses CASCADE;
DROP TABLE IF EXISTS customer_emails CASCADE;
DROP TABLE IF EXISTS customer_phones CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS customers CASCADE;

-- TODO: write your queries here
# CUSTOMERS TABLE DDL
CREATE TABLE public.customers (
    customer_id INT
    ,first_name VARCHAR(25) NOT NULL # Current we have only max of 8 char length
    ,last_name VARCHAR(25) NOT NULL # Max length of existing test data 10 char.
    ,created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
    
    ,CONSTRAINT pk_customer_id PRIMARY KEY (customer_id)
);

# CUSTOMERS_PHONES TABLE DDL
CREATE TABLE public.customer_phones(
    phone_id INT
    ,customer_id INT NOT NULL
    ,phone VARCHAR(20) NOT NULL # To allow future full 10 digit number, separators, country codes
    ,created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
    
    ,CONSTRAINT pk_phone_id PRIMARY KEY (phone_id)
    
    ,CONSTRAINT fk_customer_phone FOREIGN KEY(customer_id)
         REFERENCES customers(customer_id)
         ON DELETE CASCADE
);

# customer_emails TABLE DDL
CREATE TABLE public.customer_emails(
    email_id INT
    ,customer_id INT NOT NULL
    ,email VARCHAR(50) NOT NULL
    ,created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
    
    ,CONSTRAINT pk_email_id PRIMARY KEY (email_id)
    
    ,CONSTRAINT fk_customer_email FOREIGN KEY(customer_id)
         REFERENCES customers(customer_id)
         ON DELETE CASCADE
    
    ,CONSTRAINT chk_email_format
         CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

# customer_addresses table DDL
CREATE TABLE public.customer_addresses(
    address_id INT
    ,customer_id INT NOT NULL
    ,address VARCHAR(80)
    ,created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
    
    ,CONSTRAINT pk_address_id PRIMARY KEY (address_id)
    
    ,CONSTRAINT fk_customer_addr FOREIGN KEY(customer_id)
         REFERENCES customers(customer_id)
         ON DELETE CASCADE
);

# products table DDL
CREATE TABLE public.products(
    product_id INT
    ,product_name VARCHAR(80) NOT NULL
    ,product_type VARCHAR(40) NOT NULL
    ,description  VARCHAR(255)
    ,price NUMERIC(10, 2) NOT NULL DEFAULT 0.00
    ,stock_quantity INT NOT NULL DEFAULT 0
    ,created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
    
    ,CONSTRAINT pk_product_id PRIMARY KEY (product_id)
    
    ,CONSTRAINT chk_price
         CHECK (price >= 0)
);

# user_ratings table DDL
# Can use customer & product id's as combined primary key, but if in future org. allows rating on same it will fail. 
CREATE TABLE user_ratings(
    rating_id INT
    ,customer_id INT
    ,product_id	INT
    ,rating INT NOT NULL
    ,review_text TEXT
    ,rating_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
    
    ,CONSTRAINT pk_rating_id PRIMARY KEY (rating_id)
    
    ,CONSTRAINT fk_customer_rating FOREIGN KEY (customer_id)
         REFERENCES customers(customer_id)
         ON DELETE SET NULL
    
    ,CONSTRAINT fl_product_rating FOREIGN KEY (product_id)
         REFERENCES products(product_id)
         ON DELETE CASCADE
    
    ,CONSTRAINT check_rating
         CHECK (rating >=1 )
);

# purchases table DDL 
CREATE TABLE public.purchases(
    purchase_id INT PRIMARY KEY
    ,customer_id INT REFERENCES customers(customer_id)
    ,large_gear_quantity INT DEFAULT 0
    ,small_gear_quantity INT DEFAULT 0
    ,large_gear_unit_price NUMERIC
    ,small_gear_unit_price NUMERIC
    ,total_price NUMERIC
    ,purchase_date TIMESTAMPTZ
    ,status VARCHAR(10)
);

# customer_purchases table DDL
CREATE TABLE public.customer_purchases(
    purchase_id INT
    ,customer_id INT
    ,total_price NUMERIC(10,2) NOT NULL
    ,purchase_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
    ,status VARCHAR(10) NOT NULL
    
    ,CONSTRAINT pk_cust_purchase_id PRIMARY KEY (purchase_id)
    
    ,CONSTRAINT fk_customer_purchases FOREIGN KEY (customer_id)
         REFERENCES customers(customer_id)
         ON DELETE RESTRICT
    
    ,CONSTRAINT check_total_price
         CHECK (total_price >= 0)
);

# purchase_dtls table DDL
CREATE TABLE public.purchase_dtls(
    purchase_id INT
    ,product_id INT NOT NULL
    ,quantity INT NOT NULL
    ,unit_price NUMERIC(10,2) NOT NULL
    
    ,CONSTRAINT pk_pdtls_purchase_product_id PRIMARY KEY (purchase_id, product_id)
    
    ,CONSTRAINT check_quantity
         CHECK (quantity >= 1)
    ,CONSTRAINT check_unit_price
         CHECK (unit_price >= 0)
);

Loading Test Data

from sqlalchemy import create_engine, inspect as sa_inspect, text

engine = create_engine('postgresql://postgres:[email protected]:5432/postgres')

# Comment out any tables you haven't created yet to build up your schema gradually
tables = [
    "customers",
    "customer_phones",
    "customer_emails",
    "customer_addresses",
    "products",
    "purchases",
    "user_ratings",
    "customer_purchases",
    "purchase_dtls",
]

# Verify all tables in the list exist before attempting to load
existing = sa_inspect(engine).get_table_names()
missing = [t for t in tables if t not in existing]
if missing:
    raise RuntimeError(f"Tables not found: {missing} — run your CREATE TABLE DDL first.")

# Print column types and primary/foreign keys for each table
print("Schema overview:")
with engine.connect() as conn:
    for table in tables:
        print(f"\n  {table}")
        cols = conn.execute(text(
            "SELECT column_name, data_type FROM information_schema.columns "
            "WHERE table_schema = 'public' AND table_name = :t ORDER BY ordinal_position"
        ), {"t": table})
        for col in cols:
            print(f"    {col.column_name}: {col.data_type}")
        keys = conn.execute(text(
            "SELECT tc.constraint_type, kcu.column_name "
            "FROM information_schema.table_constraints tc "
            "JOIN information_schema.key_column_usage kcu "
            "  ON tc.constraint_name = kcu.constraint_name "
            "  AND tc.table_schema = kcu.table_schema "
            "WHERE tc.table_schema = 'public' AND tc.table_name = :t "
            "AND tc.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY') "
            "ORDER BY tc.constraint_type, kcu.column_name"
        ), {"t": table})
        for key in keys:
            print(f"    [{key.constraint_type}] {key.column_name}")

# Load data
print("\nLoading data...")
for table in tables:
    df = pd.read_csv(os.path.join(csv_path, f'{table}.csv'))
    df.to_sql(table, engine, if_exists='append', index=False)
    print(f"  {table}.csv → {table} table")

Schema overview:

  customers
    customer_id: integer
    first_name: character varying
    last_name: character varying
    created_at: timestamp with time zone
    [PRIMARY KEY] customer_id

  customer_phones
    phone_id: integer
    customer_id: integer
    phone: character varying
    created_at: timestamp with time zone
    [FOREIGN KEY] customer_id
    [PRIMARY KEY] phone_id

  customer_emails
    email_id: integer
    customer_id: integer
    email: character varying
    created_at: timestamp with time zone
    [FOREIGN KEY] customer_id
    [PRIMARY KEY] email_id

  customer_addresses
    address_id: integer
    customer_id: integer
    address: character varying
    created_at: timestamp with time zone
    [FOREIGN KEY] customer_id
    [PRIMARY KEY] address_id

  products
    product_id: integer
    product_name: character varying
    product_type: character varying
    description: character varying
    price: numeric
    stock_quantity: integer
    created_at: timestamp with time zone
    [PRIMARY KEY] product_id

  purchases
    purchase_id: integer
    customer_id: integer
    large_gear_quantity: integer
    small_gear_quantity: integer
    large_gear_unit_price: numeric
    small_gear_unit_price: numeric
    total_price: numeric
    purchase_date: timestamp with time zone
    status: character varying
    [FOREIGN KEY] customer_id
    [PRIMARY KEY] purchase_id

  user_ratings
    rating_id: integer
    customer_id: integer
    product_id: integer
    rating: integer
    review_text: text
    rating_date: timestamp with time zone
    [FOREIGN KEY] customer_id
    [FOREIGN KEY] product_id
    [PRIMARY KEY] rating_id

  customer_purchases
    purchase_id: integer
    customer_id: integer
    total_price: numeric
    purchase_date: timestamp with time zone
    status: character varying
    [FOREIGN KEY] customer_id
    [PRIMARY KEY] purchase_id

  purchase_dtls
    purchase_id: integer
    product_id: integer
    quantity: integer
    unit_price: numeric
    [PRIMARY KEY] product_id
    [PRIMARY KEY] purchase_id

Loading data...
  customers.csv → customers table
  customer_phones.csv → customer_phones table
  customer_emails.csv → customer_emails table
  customer_addresses.csv → customer_addresses table
  products.csv → products table
  purchases.csv → purchases table
  user_ratings.csv → user_ratings table
  customer_purchases.csv → customer_purchases table
  purchase_dtls.csv → purchase_dtls table

Querying Test Data in Database

counts = {}
with engine.connect() as conn:
    for table in tables:
        result = conn.execute(text(f"SELECT COUNT(*) FROM {table}"))
        counts[table] = result.scalar()

print("Row counts:")
for table in tables:
    print(f"  {counts[table]:>4}  {table}")

Row counts:
    15  customers
    19  customer_phones
    16  customer_emails
    32  customer_addresses
     2  products
    21  purchases
    15  user_ratings
    21  customer_purchases
    31  purchase_dtls

---

Part 2: ACME 3D Printing NoSQL Database Design with MongoDB

from pymongo import MongoClient
from datetime import datetime
import json

def get_mongo_connection():
    """Create a MongoDB connection"""
    try:
        client = MongoClient('mongodb://root:[email protected]:27017/?authSource=admin')  # Update with your connection string
        db = client['acme_3d_printing']
        print("Connected to MongoDB")
        return db
    except Exception as e:
        print(f"Error connecting to MongoDB: {e}")
        return None
    
# Drop MongoDB collections if they exist (to start fresh)
db = get_mongo_connection()
if db is not None:
    db.customers.drop()
    db.products.drop()
    db.purchases.drop()
    print("Dropped existing MongoDB collections")

Connected to MongoDB
Dropped existing MongoDB collections

Customer Collection

# TODO: create one or more customer documents (Python dictionaries) that represent
# your customer schema design
cust_dict1 = {
    '_id': 1001,
    'first_name' : 'John',
    'last_name' : 'doe',
    'email' : {
        'primary' : '[email protected]',
        'secondary' : '[email protected]',
    },
    'created_at' : '2023-04-01',
    'industry' : 'Aerospace',
    'industry_products' : [
        {
            'product_id' : 101,
            'product_name' : '3D Printer X1',
            'category' : '3D Printer'
        },
        {
            'product_id' : 102,
            'product_name' : '3DPXI Glass',
            'category' : 'Accessories'
        },
        {
            'product_id' : 103,
            'product_name' : '3D Printer Y2',
            'category' : '3D Printer'
        },
    ],
    'recent_purchases' : [
        {
            'purchase_id' : 10010,
            'purchase_date' : '2023-04-16',
            'total_amount' : 500.00,
            'items' : [
                {
                    'product_id' : 101,
                    'product_name' : '3D Printer X1',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 499.99
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 10009,
            'purchase_date' : '2023-04-15',
            'total_amount' : 50.00,
            'items' : [
                {
                    'product_id' : 102,
                    'product_name' : '3DPXI Glass',
                    'quantity' : 2,
                    'unit_price_at_purchase' : 25.00
                },
            ],
            'status' : 'completed'
        },

        {
            'purchase_id' : 10008,
            'purchase_date' : '2023-04-15',
            'total_amount' : 500.00,
            'items' : [
                {
                    'product_id' : 101,
                    'product_name' : '3D Printer X1',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 499.99
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 10007,
            'purchase_date' : '2023-04-14',
            'total_amount' : 25.00,
            'items' : [
                {
                    'product_id' : 102,
                    'product_name' : '3DPXI Glass',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 25.00
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 10006,
            'purchase_date' : '2023-04-13',
            'total_amount' : 500.00,
            'items' : [
                {
                    'product_id' : 101,
                    'product_name' : '3D Printer X1',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 499.99
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 10005,
            'purchase_date' : '2023-04-13',
            'total_amount' : 50.00,
            'items' : [
                {
                    'product_id' : 102,
                    'product_name' : '3DPXI Glass',
                    'quantity' : 2,
                    'unit_price_at_purchase' : 25.00
                },
            ],
            'status' : 'completed'
        },

        {
            'purchase_id' : 10004,
            'purchase_date' : '2023-04-04',
            'total_amount' : 500.00,
            'items' : [
                {
                    'product_id' : 101,
                    'product_name' : '3D Printer X1',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 499.99
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 10003,
            'purchase_date' : '2023-03-25',
            'total_amount' : 25.00,
            'items' : [
                {
                    'product_id' : 102,
                    'product_name' : '3DPXI Glass',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 25.00
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 10002,
            'purchase_date' : '2023-03-16',
            'total_amount' : 500.00,
            'items' : [
                {
                    'product_id' : 101,
                    'product_name' : '3D Printer X1',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 499.99
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 10001,
            'purchase_date' : '2023-03-14',
            'total_amount' : 25.00,
            'items' : [
                {
                    'product_id' : 102,
                    'product_name' : '3DPXI Glass',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 25.00
                },
            ],
            'status' : 'completed'
        },
    ]

}

cust_dict2 = {
    '_id': 1002,
    'first_name' : 'Gala',
    'last_name' : 'Pagos',
    'email' : {
        'personal' : '[email protected]',
        'work' : '[email protected]',
    },
    'created_at' : '2022-02-27',
    'industry' : 'small business',
    'industry_products' : [
        {
            'product_id' : 104,
            'product_name' : 'Desk Phone Stand',
            'category' : 'Practical Gadgets'
        },
        {
            'product_id' : 105,
            'product_name' : 'wall holder',
            'category' : 'Practical Gadgets'
        },
        {
            'product_id' : 106,
            'product_name' : 'Dragon Toy',
            'category' : 'Novelties'
        },
             {
            'product_id' : 107,
            'product_name' : 'Moon Lamp',
            'category' : 'Home Decor'
        },
        {
            'product_id' : 108,
            'product_name' : 'Custom Prototype',
            'category' : 'Niche Applications'
        },
        {
            'product_id' : 109,
            'product_name' : 'Business Logo',
            'category' : 'Niche Applications'
        },
    ],
    'recent_purchases' : [
        {
            'purchase_id' : 1010,
            'purchase_date' : '2023-05-01',
            'total_amount' : 2500.00,
            'items' : [
                {
                    'product_id' : 100,
                    'product_name' : 'New 3D Printer Model X',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 2500.00
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 1009,
            'purchase_date' : '2023-04-28',
            'total_amount' : 24.00,
            'items' : [
                {
                    'product_id' : 104,
                    'product_name' : 'Desk Phone Stand',
                    'quantity' : 2,
                    'unit_price_at_purchase' : 12.00
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 1008,
            'purchase_date' : '2023-04-25',
            'total_amount' : 3.00,
            'items' : [
                {
                    'product_id' : 105,
                    'product_name' : 'wall holder',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 3.00
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 1007,
            'purchase_date' : '2023-04-20',
            'total_amount' : 45.00,
            'items' : [
                {
                    'product_id' : 106,
                    'product_name' : 'Dragon Toy',
                    'quantity' : 3,
                    'unit_price_at_purchase' : 15.00
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 1006,
            'purchase_date' : '2023-04-18',
            'total_amount' : 60.00,
            'items' : [
                {
                    'product_id' : 107,
                    'product_name' : 'Moon Lamp',
                    'quantity' : 2,
                    'unit_price_at_purchase' : 30.00
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 1005,
            'purchase_date' : '2023-04-10',
            'total_amount' : 300.00,
            'items' : [
                {
                    'product_id' : 106,
                    'product_name' : 'Custom Prototype3',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 300.00
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 1004,
            'purchase_date' : '2023-04-05',
            'total_amount' : 75.00,
            'items' : [
                {
                    'product_id' : 107,
                    'product_name' : 'Business Logo1',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 75.00
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 1003,
            'purchase_date' : '2023-03-28',
            'total_amount' : 12.00,
            'items' : [
                {
                    'product_id' : 104,
                    'product_name' : 'Desk Phone Stand',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 12.00
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 1002,
            'purchase_date' : '2022-09-20',
            'total_amount' : 15.00,
            'items' : [
                {
                    'product_id' : 106,
                    'product_name' : 'Dragon Toy',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 15.00
                },
            ],
            'status' : 'completed'
        },
        {
            'purchase_id' : 1001,
            'purchase_date' : '2022-06-10',
            'total_amount' : 30.00,
            'items' : [
                {
                    'product_id' : 107,
                    'product_name' : 'Moon Lamp',
                    'quantity' : 1,
                    'unit_price_at_purchase' : 30.00
                },
            ],
            'status' : 'completed'
        }
    ]

}

Product Collection

# TODO: create at least two product documents (Python dictionaries) that represent
# the different types of products that your product schema supports
products_list = [
        {
            '_id' : 101,
            'product_name' : '3D Printer X1',
            'description' : 'Best 3D Printer',
            'category' : '3D Printer',
            'unit_price' : 499.99,
            'manufacturer' : 'Acme Inc.',
            'stock_quantity' : 100,
            'industry_tags' : ['Aerospace', 'Education', 'Medical'],
            'attributes' : {
                'build_volume' : '200x200x200mm',
                'material_compatibility' : ['PLA', 'ABS'],
                'nozzle_diameter' : '0.4mm'
            }
        },
        {
            '_id' : 102,
            'product_name' : '3DPXI Glass',
            'description' : 'Best 3DPXI Glass',
            'category' : 'Accessories',
            'unit_price' : 25.00,
            'manufacturer' : 'Acme Inc.',
            'stock_quantity' : 200,
            'industry_tags' : ['Aerospace', 'Education', 'Medical'],
            'attributes' : {
                'color' : 'Red',
                'material_type' : 'PLA',
                'diameter' : '1.75mm',
                'weight_kg' : 1
            }
        },
        {
            '_id' : 103,
            'product_name' : '3D Printer Y2',
            'description' : 'Best 3D Printer with auto features',
            'category' : '3D Printer',
            'unit_price' : 499.99,
            'manufacturer' : 'Acme Inc.',
            'stock_quantity' : 100,
            'industry_tags' : ['Aerospace', 'Education', 'Medical'],
            'attributes' : {
                'build_volume' : '200x200x200mm',
                'material_compatibility' : ['PLA', 'ABS'],
                'nozzle_diameter' : '0.4mm'
            }
        },
        {
            '_id' : 104,
            'product_name' : 'Desk Phone Stand',
            'description' : 'Adjustable Desk Phone Stand',
            'category' : 'Practical Gadgets',
            'unit_price' : 12.00,
            'manufacturer' : 'Acme Inc.',
            'stock_quantity' : 50,
            'industry_tags' : ['Household','Smallbusiness','General'],
            'attributes' : {
                'material' : 'Plastic',
                'color'    : ['black','gray'],
                'no_parts' : 2,
            }
        },
        {
            '_id' : 105,
            'product_name' : 'wall holder',
            'description' : 'wall holder for keys and hats',
            'category' : 'Practical Gadgets',
            'unit_price' : 3.00,
            'manufacturer' : 'Acme Inc.',
            'stock_quantity' : 50,
            'industry_tags' : ['Household','Smallbusiness','General'],
            'attributes' : {
                'material' : 'Plastic',
                'color'    : ['black','white','gray'],
                'no_parts' : 2,
            }
        },
        {
            '_id' : 106,
            'product_name' : 'Dragon Toy',
            'description' : 'Dragon Toy for kids',
            'category' : 'Novelties',
            'unit_price' : 15.00,
            'manufacturer' : 'Acme Inc.',
            'stock_quantity' : 50,
            'industry_tags' : ['Household','Smallbusiness','General'],
            'attributes' : {
                'material' : 'PLA',
                'color'    : ['black','white','gray'],
                'no_parts' : 1,
            }
        },
        {
            '_id' : 107,
            'product_name' : 'Moon Lamp',
            'description' : 'Moon Lamp for kids',
            'category' : 'Home Decor',
            'unit_price': 30.00,
            'manufacturer' : 'Acme Inc.',
            'stock_quantity' : 50,
            'industry_tags' : ['Household','Smallbusiness','General'],
            'attributes' : {
                'material' : 'ABS',
                'color'    : ['black','white','gray'],
                'no_parts' : 1,
            }
        },
        {
            '_id' : 108,
            'product_name' : 'Custom Prototype3',
            'description' : 'Custom Prototype for business',
            'category' : 'Niche Applications',
            'unit_price' : 300.00,
            'manufacturer' : 'Acme Inc.',
            'stock_quantity' : 50,
            'industry_tags' : ['Smallbusiness','General'],
            'attributes' : {
                'material' : 'ABS',
                'color'    : ['black','white','gray','red','custom'],
                'no_parts' : 1,
            }
        },
        {
            '_id' : 109,
            'product_name' : 'Business Logo1',
            'description' : 'small Business Logo for business',
            'category' : 'Niche Applications',
            'unit_price' : 75.00,
            'manufacturer' : 'Acme Inc.',
            'stock_quantity' : 50,
            'industry_tags' : ['Smallbusiness','General'],
            'attributes' : {
                'material' : 'ABS',
                'color'    : ['black','white','gray','red','custom'],
                'no_parts' : 1,
            }
        },
]

Purchases Collection

# TODO: create one or more purchase documents (Python dictionaries) that represent
# your purchase schema design
purchases_list = [
    {
        '_id': 10010,
        'customer_id': 1001,  # Reference to cust_dict2
        'purchase_date': '2023-04-16',
        'total_amount': 499.99,
        'status': 'completed',
        'items': [
            {
                'product_id': 101,  # Reference to '3D Printer X1'
                'product_name': '3D Printer X1',
                'quantity': 1,
                'unit_price_at_purchase': 499.99
            }
        ]
    },
    {
        '_id': 1009,
        'customer_id': 1002,  # Reference to cust_dict3
        'purchase_date': '2023-04-28',
        'total_amount': 24.00,
        'status': 'pending',
        'items': [
            {
                'product_id': 104,  # Reference to 'Desk Phone Stand'
                'product_name': 'Desk Phone Stand',
                'quantity': 2,
                'unit_price_at_purchase': 12.00
            }
        ]
    }
]

Loading Collections into MongoDB

db.list_collection_names()

[]

# dropped collection names customers, products and purchases

customers_collection = db["customers"]
products_collection  = db.create_collection("products")
purchases_collection = db.purchases

print(db.list_collection_names())

['products']

# TODO: use insert_one or insert_many to load your collections into MongoDB
cci = customers_collection.insert_many([cust_dict1, cust_dict2])
print(f"Customers Inserted: {cci.inserted_ids}")

pdci = products_collection.insert_many(products_list)
print(f"Products Inserted: {pdci.inserted_ids}")

pcci = purchases_collection.insert_many(purchases_list)
print(f"Purchases Inserted: {pcci.inserted_ids}")

Customers Inserted: [1001, 1002]
Products Inserted: [101, 102, 103, 104, 105, 106, 107, 108, 109]
Purchases Inserted: [10010, 1009]

[(x, db[x].count_documents({})) for x in db.list_collection_names()]

[('customers', 2), ('products', 9), ('purchases', 2)]

NoSQL Modeling Design Decisions

1. Why are recent purchases and industry products embedded in the customer document?

• In ACME website, when customers accessed orders page, it should list top 10 most recent purchases made by the customer.
• And in Industry page of the website, it should list other products associated with customer industry that ACME is selling.
• To handle above two functionalities and avoiding logic to fetch customer purchases and then filter for top 10 and also avoiding joining multiple documents for initial default view, improving the performance and user experience by displaying required information by directly getting from customer document based on customer login.

2. What are the trade-offs of embedding vs. referencing?

• Embedding: Placing the document within the document.Embedded document data changes should be infrequent or rare.
  Pros:
  • This increases performance when related data is read from single document.
  • No transaction is needed to mention when updating the document as Atomicity is default at single document level. Cons:
  • When embedded data is large and mainted it's own separate document, will have data duplication.
  • Increase in size of main document (where embeded exists)
  • Any small change to frequent changes label requires entire document update.
  • Chance of invalid or old data if duplicated.
• Referencing: Placing the lookup or key field in document, this is preferred when the data changes more frequently and large volume. Pros:
  • Updates only relevant document without touching other.
  • No data duplication
  • Ensure data validity
  • document size, small and focused Cons:
  • Requires lookup/join when reading data accross multiple documents, impacting read performance.
  • Requires Transactions to ensure atomicity.

3. How does this schema handle products with different attributes?

   • Schema will be flexible for any changes and allowing to add different attributes accross the products.

MongoDB Demonstration Queries

# TODO: write 2 or more queries that demonstrate how your collections
# can be used by application code

for customer in db.customers.find():
  print(customer['_id'])

1001
1002

for product in db.products.find({'unit_price' : {'$gte': 50}}):
  print(f" {product['_id']} - {product['product_name']} - ${product['unit_price']}" )

 101 - 3D Printer X1 - $499.99
 103 - 3D Printer Y2 - $499.99
 108 - Custom Prototype3 - $300.0
 109 - Business Logo1 - $75.0

# Get products attributes for the products custumer 1001 purchased
pipeline_customer_products = [
    {'$match': {'_id': 1001}},
    # products bought by customer is in recent purchase embeded, we need to unwind it.
    {'$unwind': '$recent_purchases'},
    # Since product id is in items dict, we need to unwind items.
    {'$unwind': '$recent_purchases.items'},
    # join products table using product id
    {
    '$lookup': {
        'from' : 'products',
        'localField' : 'recent_purchases.items.product_id', 
        'foreignField' : '_id',
        'as' : 'purchased_produds'
        }
    },
    {'$unwind': '$purchased_produds'},

    {'$project': {
#         '_id': 0,
#         'customer_id' : '$_id',
#         'customer_name' : {'$concat' : ['$first_name',' ', '$last_name']},
#         'purchase_id' : '$recent_purchases.purchase_id',
#         'purchase_date' : '$recent_purchases.purchase_date',
        'product_name' : '$purchased_produds.product_name',
        'product_attributes' : '$purchased_produds.attributes'
    }}

]

for doc in db.customers.aggregate(pipeline_customer_products):
    print(doc)

{'_id': 1001, 'product_name': '3D Printer X1', 'product_attributes': {'build_volume': '200x200x200mm', 'material_compatibility': ['PLA', 'ABS'], 'nozzle_diameter': '0.4mm'}}
{'_id': 1001, 'product_name': '3DPXI Glass', 'product_attributes': {'color': 'Red', 'material_type': 'PLA', 'diameter': '1.75mm', 'weight_kg': 1}}
{'_id': 1001, 'product_name': '3D Printer X1', 'product_attributes': {'build_volume': '200x200x200mm', 'material_compatibility': ['PLA', 'ABS'], 'nozzle_diameter': '0.4mm'}}
{'_id': 1001, 'product_name': '3DPXI Glass', 'product_attributes': {'color': 'Red', 'material_type': 'PLA', 'diameter': '1.75mm', 'weight_kg': 1}}
{'_id': 1001, 'product_name': '3D Printer X1', 'product_attributes': {'build_volume': '200x200x200mm', 'material_compatibility': ['PLA', 'ABS'], 'nozzle_diameter': '0.4mm'}}
{'_id': 1001, 'product_name': '3DPXI Glass', 'product_attributes': {'color': 'Red', 'material_type': 'PLA', 'diameter': '1.75mm', 'weight_kg': 1}}
{'_id': 1001, 'product_name': '3D Printer X1', 'product_attributes': {'build_volume': '200x200x200mm', 'material_compatibility': ['PLA', 'ABS'], 'nozzle_diameter': '0.4mm'}}
{'_id': 1001, 'product_name': '3DPXI Glass', 'product_attributes': {'color': 'Red', 'material_type': 'PLA', 'diameter': '1.75mm', 'weight_kg': 1}}
{'_id': 1001, 'product_name': '3D Printer X1', 'product_attributes': {'build_volume': '200x200x200mm', 'material_compatibility': ['PLA', 'ABS'], 'nozzle_diameter': '0.4mm'}}
{'_id': 1001, 'product_name': '3DPXI Glass', 'product_attributes': {'color': 'Red', 'material_type': 'PLA', 'diameter': '1.75mm', 'weight_kg': 1}}

---

Part 3: Graph Database Design with Neo4j

# Setup: Import Neo4j libraries and establish connection.
# Hint: This code is provided for you in the reference guide, but feel free to modify it if needed.
from neo4j import GraphDatabase

class Neo4jConnection:
    def __init__(self):
        self.driver = GraphDatabase.driver("bolt://db-host:7687", auth=("neo4j", "****"))

    def close(self):
        self.driver.close()

    def execute_query(self, query, parameters=None):
        with self.driver.session() as session:
            result = session.run(query, parameters)
            return [record for record in result]

# Connect to Neo4j
#neo4j_conn = Neo4jConnection()
neo4j_conn = GraphDatabase.driver("bolt://db-host:7687", auth=("neo4j", "****"), notifications_min_severity="OFF")


# Drop all Neo4j nodes and relationships (to start fresh)
try:
    neo4j_conn.execute_query("MATCH (n) DETACH DELETE n")
    print("Deleted all existing Neo4j nodes and relationships")
except Exception as e:
    print(f"Note: {e}")

Deleted all existing Neo4j nodes and relationships

Node Types

cust_dict2['email']['work']

'[email protected]'

# TODO: write Cypher CREATE statements for your nodes (create more cells as needed)
# Creating Nodes and Relations through single query for some nodes as part of practice.

records, summary, keys = neo4j_conn.execute_query(
    """
    CREATE (c:Customer {id: $customer_id})
    SET c.first_name = $first_name,
        c.last_name = $last_name,
        c.created_at = $created_at
    RETURN c
    """,
    customer_id=cust_dict1['_id'],
    first_name=cust_dict1['first_name'],
    last_name=cust_dict1['last_name'],
    created_at=cust_dict1['created_at']
)
# This syntax is of old format and this requires open the session.
with neo4j_conn.session() as session2:
  query = """
  CREATE (c:Customer {id: $customer_id})
  SET c.first_name = $first_name,
      c.last_name  = $last_name,
      c.created_at = $created_at
  RETURN c
  """

  session2.run(query,
              customer_id = cust_dict2['_id'],
              first_name = cust_dict2['first_name'],
              last_name  = cust_dict2['last_name'],
              created_at = cust_dict2['created_at']
              )

  # We can create nodes using MERGE statement, this ensures no duplicate nodes are created and update nodes if already existed.
  c1_pemail_n_records, c1_pemail_n_summary, c1_pemail_n_keys = neo4j_conn.execute_query(
      """
      MERGE (e:Email {address:  $email_address})
      RETURN e
      """,
      email_address = cust_dict1['email']['primary']
  )

  c1_semail_n_records, c1_semail_n_summary, c1_semail_n_keys = neo4j_conn.execute_query(
      """
      MERGE (e:Email {address: $email_address})
      RETURN e
      """,
      email_address = cust_dict1['email']['secondary']
  )

  # Creating relation between existing nodes
  c1_pemail_r_records, c1_pemail_r_summary, c1_pemail_r_keys = neo4j_conn.execute_query(
     """
     MATCH (c:Customer {id:  $customer_id})
     MATCH (e:Email {address: $email_address})
     MERGE (c)-[:HAS_EMAIL {type: $email_type}]->(e)
     RETURN c,e
     """,
     customer_id = cust_dict1['_id'],
     email_address = cust_dict1['email']['primary'],
     email_type = 'primary'
  )

  c1_semail_n_records, c1_semail_n_summary, c1_semail_n_keys = neo4j_conn.execute_query(
      """
      MATCH (c:Customer {id: $customer_id})
      MATCH (e:Email {address: $email_address})
      MERGE (c)-[:HAS_EMAIL {type: $email_type}]->(e)
      RETURN c,e
      """,
      customer_id = cust_dict1['_id'],
      email_address = cust_dict1['email']['secondary'],
      email_type = 'secondary'
  )

  # creating nodes along with relations
  c2_pemail_n_records, c2_pemail_n_summary, c2_pemail_n_keys = neo4j_conn.execute_query(
      """
      MERGE (e:Email {address: $email_address})
      ON CREATE SET e.type = $email_type
      MERGE (c:Customer {id: $customer_id})
      MERGE (c)-[:HAS_EMAIL]->(e)
      RETURN c,e
      """,
      customer_id = cust_dict2['_id'],
      email_address = cust_dict2['email']['personal'],
      email_type = 'personal'
  )

  c2_wemail_n_records, c2_wemail_n_summary, c2_wemail_n_keys = neo4j_conn.execute_query(
      """
      MERGE (e:Email {address: $email_address})
      ON CREATE SET e.type = $email_type
      MERGE (c:Customer {id: $customer_id})
      MERGE (c)-[:HAS_EMAIL]->(e)
      RETURN c,e
      """,
      customer_id = cust_dict2['_id'],
      email_address = cust_dict2['email']['work'],
      email_type = 'work'
  )

Relationships

products_list[3]

{'_id': 104,
 'product_name': 'Desk Phone Stand',
 'description': 'Adjustable Desk Phone Stand',
 'category': 'Practical Gadgets',
 'unit_price': 12.0,
 'manufacturer': 'Acme Inc.',
 'stock_quantity': 50,
 'industry_tags': ['Household', 'Smallbusiness', 'General'],
 'attributes': {'material': 'Plastic',
  'color': ['black', 'gray'],
  'no_parts': 2}}

purchases_list[1]

{'_id': 1009,
 'customer_id': 1002,
 'purchase_date': '2023-04-28',
 'total_amount': 24.0,
 'status': 'pending',
 'items': [{'product_id': 104,
   'product_name': 'Desk Phone Stand',
   'quantity': 2,
   'unit_price_at_purchase': 12.0}]}

# TODO: write Cypher CREATE statements for your relationships (create more cells as needed)
# Crating nodes and relations in single query, as part of practice Cypher query.

# Create product & Purchase nodes and create relations

p101_records, p101_summary,  p101_keys = neo4j_conn.execute_query(
    """
    MERGE (pd:Product {id: $product_id})
    ON CREATE SET pd.name = $product_name,
                  pd.description = $description,
                  pd.category = $category,
                  pd.unit_price = $unit_price,
                  pd.manufacturer = $manufacturer,
                  pd.stock_quantity = $stock_quantity
    MERGE (pr:Purchase {id: $purchase_id})
    ON CREATE SET pr.purchase_date = $purchase_date,
                  pr.total_amount = $total_amount,
                  pr.status = $status
    MERGE (c:Customer {id: $customer_id})
    MERGE (c)-[:MADE]->(pr)
    MERGE (c)-[:INTERESTED_IN]->(pd)
    MERGE (pr)-[:CONTAINS {quantity: $quantity, unit_price_at_purchase: $unit_price_purchase}]->(pd)
    RETURN pd,pr,c
    """,
    product_id = products_list[0]['_id'],
    product_name = products_list[0]['product_name'],
    description = products_list[0]['description'],
    category = products_list[0]['category'],
    unit_price = products_list[0]['unit_price'],
    manufacturer = products_list[0]['manufacturer'],
    stock_quantity = products_list[0]['stock_quantity'],
    purchase_id = purchases_list[0]['_id'],
    purchase_date = purchases_list[0]['purchase_date'],
    total_amount = purchases_list[0]['total_amount'],
    status = purchases_list[0]['status'],
    customer_id = purchases_list[0]['customer_id'],
    quantity = purchases_list[0]['items'][0]['quantity'],
    unit_price_purchase = purchases_list[0]['items'][0]['unit_price_at_purchase']

)

# Create product & Purchase nodes and create relations

p104_records, p104_summary,  p104_keys = neo4j_conn.execute_query(
    """
    MERGE (pd:Product {id: $product_id})
    ON CREATE SET pd.name = $product_name,
                  pd.description = $description,
                  pd.category = $category,
                  pd.unit_price = $unit_price,
                  pd.manufacturer = $manufacturer,
                  pd.stock_quantity = $stock_quantity
    MERGE (pr:Purchase {id: $purchase_id})
    ON CREATE SET pr.purchase_date = $purchase_date,
                  pr.total_amount = $total_amount,
                  pr.status = $status
    MERGE (c:Customer {id: $customer_id})
    MERGE (c)-[:MADE]->(pr)
    MERGE (c)-[:INTERESTED_IN]->(pd)
    MERGE (pr)-[:CONTAINS {quantity: $quantity, unit_price_at_purchase: $unit_price_purchase}]->(pd)
    RETURN pd,pr,c
    """,
    product_id = products_list[1]['_id'],
    product_name = products_list[1]['product_name'],
    description = products_list[1]['description'],
    category = products_list[1]['category'],
    unit_price = products_list[1]['unit_price'],
    manufacturer = products_list[1]['manufacturer'],
    stock_quantity = products_list[1]['stock_quantity'],
    purchase_id = purchases_list[1]['_id'],
    purchase_date = purchases_list[1]['purchase_date'],
    total_amount = purchases_list[1]['total_amount'],
    status = purchases_list[1]['status'],
    customer_id = purchases_list[1]['customer_id'],
    quantity = purchases_list[1]['items'][0]['quantity'],
    unit_price_purchase = purchases_list[1]['items'][0]['unit_price_at_purchase']

)

# Validating Nodes and Relations created
records, summary, keys = neo4j_conn.execute_query("MATCH (n) RETURN labels(n) AS label, count(n) AS count ORDER BY label")
print("\nNode Counts:")
for record in records:
    print(f"  {record['label']}: {record['count']}")

records, summary, keys = neo4j_conn.execute_query("MATCH ()-[r]->() RETURN type(r) AS type, count(r) AS count ORDER BY type")
print("\nRelationship Counts:")
for record in records:
    print(f"  {record['type']}: {record['count']}")

Node Counts:
  ['Customer']: 2
  ['Email']: 4
  ['Product']: 2
  ['Purchase']: 2

Relationship Counts:
  CONTAINS: 2
  HAS_EMAIL: 4
  INTERESTED_IN: 2
  MADE: 2

Recommendation Queries

Customers who bought X also bought Y (Required)

# TODO: write your "people also purchased" recommendation query (create more cells as needed)
# Created only few products and purchases, hence we will not have matching results
product_id_for_recommendation = 104 # Example: '3D Printer X1'

records, summary, keys = neo4j_conn.execute_query(
    """
    MATCH (p1:Product {id: $productId})
    <-[:CONTAINS]-(purch:Purchase)
    <-[:MADE]-(c:Customer)
    -[:MADE]->(other_purch:Purchase)
    -[:CONTAINS]->(p2:Product)
    WHERE p1.id <> p2.id // Exclude the original product
    RETURN p2.name AS RecommendedProduct, count(DISTINCT c) AS CoPurchasedByCustomersCount
    ORDER BY CoPurchasedByCustomersCount DESC
    LIMIT 5
    """,
    productId=product_id_for_recommendation
)

print(f"'People also purchased' recommendations for Product ID: {product_id_for_recommendation} (Name: {products_list[3]['product_name'] if products_list[3]['_id'] == product_id_for_recommendation else 'N/A'}):\n")
if records:
    for record in records:
        print(f"- {record['RecommendedProduct']} (Co-purchased by {record['CoPurchasedByCustomersCount']} customers)")
else:
    print("No co-purchased products found or insufficient data.")

'People also purchased' recommendations for Product ID: 104 (Name: Desk Phone Stand):

No co-purchased products found or insufficient data.

Popular products in your industry (Optional Standout)

# TODO (Optional Standout): write your industry recommendation query (create more cells as needed)
print("Sufficient data is not loaded to graph database to support this query.")

Sufficient data is not loaded to graph database to support this query.

Products similar to X (Optional Standout)

# TODO (Optional Standout): write your product similarity query (create more cells as needed)
print("Sufficient data is not loaded to graph database to support this query.")

Sufficient data is not loaded to graph database to support this query.

Graph Design Decisions

1. What nodes and relationships did you create and why?

   • Created Customer, Email, Product and Purchase nodes
   • Created "HAS_EMAIL" between customer and email, "MADE" between Customer and Purchase, "CONTAINS" between Purchase and Products and "INTERESTED_IN" between Customer and Product.
   • We can have Email as property of Customer node, however it would be better to have it as a separate node itself, enabling to query for email directly and validate it's existance and also if it is allowed to share email between family those customers can be identified through email and it's relation.
   • Similarly each purchase and product will have it's own node and purchase is linked with product based on it's existance in the purchase using "MADE" relation, enabling to lookup products through purchase or purchases through product.
   • Also added relation between "Customer" and "Product" with "INTERESTED_IN" relation, this enables for customer based marketing and helps to identify Products and generating suggestions based on other customer purchases having similar interested products along with purchases.

2. How does your graph structure enable the "people also purchased" recommendation?

   • Created Relations between Customers -> Purchases -> Products <- customers, this enables to identify customers purchases similar products and look for the other products purchased by the customers along with the same product.

3. What properties did you add to relationships and why?

   • Properties enables to retrive information involving both the nodes. Or explaining how those two nodes are associated.
   • Relation between Customer & Email contains "HAS_Email"; this provides which customer email belongs to or otherway as well and relation has propeties that suggest what type of email it is, like primary, Secondary, Personal or Work. And this enables further adding additioanl properties to support whether to reach through specific email or not, active or not.
   • Depending on the business domain and program requirements we can add additional properties in nodes between relations to support further filtering or detail analysis.
   • Similarly "CONTAINS" relationship provide the product details at the instance of the purchase, even though product details might varry with time, we can capture required information of the product at the time of purchase in Purchase & Product relation.

4. How would you handle graph growth as more customers and products are added?

   • Depend up on how the Graph database is designed and intend to query. We can follow below approaches.
     • If graph is designed considering Query patterns, this enables to create effective indexes.
     • Determining Data Archiving and Time to Live strategy.
     • Determining Horizontal or Vertical Scalling.
     • Enabling Auto Scalling strategies as needed.
     • Proper query tuning.

Optional Standout: GraphRAG with Neo4j + Vector Search

# Import GraphRAG chatbot
import sys
import os

project_path = os.path.abspath('.')
if project_path not in sys.path:
    sys.path.insert(0, project_path)

from utils.graphrag_chatbot import Neo4jGraphRAG

print("Neo4j GraphRAG with vector search ready")

Neo4j GraphRAG with vector search ready

# Note: This optional standout section works best if your graph includes:
#   - Customer nodes with customer_id (integer), first_name, and last_name properties
#   - Product nodes with product_name and base_price properties
#   - WORKS_IN (Customer → Industry) relationships with Industry nodes having a name property
#     (optional standout work — without these, the chatbot responds without industry context)
# Update the customer_id values in the example cells below to match IDs in your own graph.
# If you see a 'Missing Authorization key' error, double-check that you replaced
# the empty string for api_key below with your Vocareum OpenAI API key.

# Initialize GraphRAG with vector search
try:
    chatbot = Neo4jGraphRAG(
        neo4j_uri="bolt://db-host:7687",
        neo4j_user="neo4j",
        neo4j_password="****",
        api_key="",  # Add your Vocareum API key
        base_url="https://openai.vocareum.com/v1"
    )
    print("GraphRAG initialized with vector search")
    print(f"Model: {chatbot.model}")
    
    # Create embeddings for products (run once)
    print("\n Creating product embeddings...")
    chatbot.embed_products()
    
except Exception as e:
    print(f"\n Error: {e}")
    chatbot = None

Received notification from DBMS server: <GqlStatusObject gql_status='01N52', status_description='warn: property key does not exist. The property `product_name` does not exist. Verify that the spelling is correct.', position=<SummaryInputPosition line=3, column=26, offset=60>, raw_classification='UNRECOGNIZED', classification=<NotificationClassification.UNRECOGNIZED: 'UNRECOGNIZED'>, raw_severity='WARNING', severity=<NotificationSeverity.WARNING: 'WARNING'>, diagnostic_record={'_classification': 'UNRECOGNIZED', '_severity': 'WARNING', '_position': {'offset': 60, 'line': 3, 'column': 26}, 'OPERATION': '', 'OPERATION_CODE': '0', 'CURRENT_SCHEMA': '/'}> for query: '\n                MATCH (p:Product)\n                RETURN p.product_name AS name, p.base_price AS price,\n                       p.description AS description, elementId(p) AS id\n            '
Received notification from DBMS server: <GqlStatusObject gql_status='01N52', status_description='warn: property key does not exist. The property `base_price` does not exist. Verify that the spelling is correct.', position=<SummaryInputPosition line=3, column=50, offset=84>, raw_classification='UNRECOGNIZED', classification=<NotificationClassification.UNRECOGNIZED: 'UNRECOGNIZED'>, raw_severity='WARNING', severity=<NotificationSeverity.WARNING: 'WARNING'>, diagnostic_record={'_classification': 'UNRECOGNIZED', '_severity': 'WARNING', '_position': {'offset': 84, 'line': 3, 'column': 50}, 'OPERATION': '', 'OPERATION_CODE': '0', 'CURRENT_SCHEMA': '/'}> for query: '\n                MATCH (p:Product)\n                RETURN p.product_name AS name, p.base_price AS price,\n                       p.description AS description, elementId(p) AS id\n            '

GraphRAG initialized with vector search
Model: gpt-4o-mini

 Creating product embeddings...

 Error: Error code: 400 - {'error': {'code': None, 'message': 'Missing Authorization key', 'param': None, 'type': 'invalid_request_error'}}

if chatbot:
    customer_id = 1  # Update to match a customer_id in your graph
    # Example questions to try:
    #   "What products are available?"
    #   "What products are popular in my industry?"
    #   "What are the best products for aerospace companies?"
    print("Chat with your graph database. Type 'q' to quit.\n")
    while True:
        question = input("You: ").strip()
        if question.lower() == "q":
            break
        if question:
            response = chatbot.chat(question, customer_id=customer_id)
            print(f"Assistant: {response}\n")
else:
    print("Chatbot not initialized")

Chatbot not initialized

# Clean up: Close Neo4j connection
if chatbot:
    chatbot.close()
    print("Neo4j connection closed")