MongoDB database

The MongoDB Atlas which is a fully-managed cloud database came in hand when deciding where to host our MongoDB database. Its free of charge sandbox mode offers a 512mb storage with shared clusters, CPU and RAM. We used this database to store 3 different collections (same as tables in relational databases):

• Cars (AdID, Car Make,Car Model, Number of fields describing equipment, Price)
• Ads (AdID, SellerID, Title, URL of the ad )
• Sellers (SellerID, Name, City, County)

As you can see all of them can be joined on their unique ID values

PostgreSQL Warehouse

We set up the PostgreSQL database on a droplet(a virtual machine) from DigitalOcean which we are using as a cloud server. Each droplet comes with pre-installed UbuntuOS on it. The database was set up using a Docker Image using the following commands:

docker pull bitnami/postgresql
docker run --name postgresql -v /path/to/postgresql-persistence/conf/:/bitnami/postgresql/conf/ -p 5432:5430 -e POSTGRESQL_PASSWORD=pass bitnami/postgresql:latest

This allowed us to set up our warehouse on the server. The warehouse logic is presented with the following diagram:

In order to create the tables presented in the diagram above, the following code was used:

Create Table public.Marka(
    skey int GENERATED BY DEFAULT AS identity not null primary key,
    Ime varchar(10)  --ke bide i key za da se povrzi so bazata
)

WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

Create Table public.Model(
    skey int GENERATED BY DEFAULT AS identity not null primary key,
    Ime varchar(10)
)

WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

Create Table public.Zemja(
    skey int GENERATED BY DEFAULT AS identity not null primary key,
    Ime varchar(15)
)

WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

Create Table public.Seller(
    skey int GENERATED BY DEFAULT AS identity not null primary key,
    ODB_SellerID int,
    VendorName varchar(50)
)

WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;


ALTER TABLE public.Marka
    OWNER to postgres;

ALTER TABLE public.Model
    OWNER to postgres;

ALTER TABLE public.Zemja
    OWNER to postgres;

ALTER TABLE public.Seller
    OWNER to postgres;


Create Table public.fact_Make_Model_Country(
    Marka_skey int NOT NULL references public.Marka(skey),
    Model_skey int NOT NULL references public.Model(skey),
    Zemja_skey int NOT NULL references public.Zemja(skey),
    Avg_Price real not null,
    Car_Count int not null,
    Avg_Mileage real not null   
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

Create Table public.fact_Seller_Make(
    Marka_skey int NOT NULL references public.Marka(skey),
    Seller_skey int NOT NULL references public.Seller(skey),
    Car_Count int not null
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;