BCNFStar

BCNFStar is a tool for interactive database schema management. It helps you to

• normalize or denormalize your schema (by splitting or joining tables)
• create star schemas with a dedicated optimized mode
• replace primary and foreign keys by automatically generated surrogate keys
• find valid foreign key candidates (based on inclusion dependencies) present in the data
  • and investigate why others are invalid
  • same for functional dependencies, which are the basis for normalising (splitting) tables
• integrate new tables into an existing schema (coming soon!)

It makes sure all transformations are valid for a given data instance and generates SQL downloads for transforming

Setup

Java

This project uses metanome, which requires java. We recommand Java 8 -11

Node

This project requires nodejs. After installing node, you need to execute

npm install

from the project root folder, which will also install all dependencies in the server and frontend projects.

Database

BCNFStar currently works on Postgres and Microsoft SQL Server databases. You need to set an environment variable called DB_TYPE to either postgres, mssql (or sqledge). Regardless of the database type, a .pgpass-like is needed. Its path needs to be in an environment variable called DB_PASSFILE. Environment variables can be placed in a file called .env.local in the project root like this:

DB_TYPE="postgres"
DB_PASSFILE="~/.pgpass"

Redis

Since metanome jobs might take a lot of time and resources, we queue them. This requires running a Redis instance for storing the queue through server restarts, which can be obtained from many standard pacakge managers on Unix or from a tarball. On Windows, you can use this download. If you host Redis on a different machine or change its config, you may pass REDIS_HOST and REDIS_PORT env variables.

Deploying

To build the production app, run

npm run build

from the project root, which will build both the server and the frontend. After that, you can start the server by invoking

npm run start

Docker

If want to use a docker container for deployment you can skip the steps bellow. Just follow the next commands.

First set up your personal DB configuration in . By default we use a standard postgres database configuration.

- DB_HOST=host.docker.internal
- DB_PORT=5432
- DB_DATABASE=postgres
- DB_USER=postgres
- DB_TYPE=postgres
- DB_PASSWORD=

To build your docker container run

docker build . -t bcnfstar

to create bcnfstar docker image and run

docker-compose up

to create docker containers for redis and BCNFStar.

You can open BCNFStar on http://localhost/#/.

Troubleshooting

Something doesn't work? Always try to run npm install && npm run build first.