Download of Schema
First, we will download sample database from this location:
https://dev.monetdb.org/Assets/VOC/voc_dump.zip
This will give us ZIP file. Inside of it, there is SQL script with the schema for our new database.
What is a Schema?
Database is made of tables, views, indices and so on. Inside of Database, each of these objects belong to some schema. Database is organizationally divided into schemas. There is no overlap between schemas. There could be some special objects that are outside of a scheme, like roles.
Creation, usage and modification of schema elements is strictly done by the user or the role that owns that scheme. During creation of a scheme, we should decide who will be owner, because later it will not be possible to change ownership. If we want several people to own one schema then we should set a role as an owner of a scheme.
Only 'monetdb' user and 'sysadmin' role are allowed to create new schemes.
Creation of a New User
For our schema we will create a new user. First, we will enter mclient with 'monetdb' privileges.
> mclient -u monetdb -d voc 
For creation of a user, we need username (USER), password (WITH PASSWORD), user's full name (NAME), and default schema for that user (SCHEMA). If a user wants to use table from some schema he can use schema as a prefix "myschema.mytable". If a user doesn't provide schema as a prefix, then the default schema will be used.
sql> CREATE USER "voc" WITH PASSWORD 'voc' NAME 'VOC Explorer' SCHEMA "sys";
As a 'monetdb' user we can create new schema. We will say that previously created "voc" user is the owner of that schema.
sql> CREATE SCHEMA "voc" AUTHORIZATION "voc"; 
We will set that new schema as the default schema for our user.
sql> ALTER USER "voc" SET SCHEMA "voc"; 
sql> \q -- we can exit mclient with "quit" or "\q"Populating our Schema with Database Objects
Our schema is currently empty, but we have definitions of all the tables, view, indices … inside of our downloaded SQL script. We will use that script to populate our schema. We type:
> mclient -u voc -d voc ~/Desktop/voc_dump.sql 
We can use mclient command "\d" to list all the tables and views inside of our database. This is what we would get:
| sql> \d TABLE voc.craftsmen TABLE voc.impotenten TABLE voc.invoices TABLE voc.passengers TABLE voc.seafarers TABLE voc.soldiers TABLE voc.total TABLE voc.voyages |  |
DBeaver Database Manager Program
We will install DBeaver database manager program in order to peruse our database.
> sudo snap install dbeaver-ce 
This program is GUI program, so we will open it in our desktop environment. In the program we will click on the arrow (1), and then we will select Other (2) to open other servers. There we will find MonetDB. We will click on it (3), and a new dialog will open. In this dialog, host and port are already entered. We just need to enter Database/Schema (4), Username and Password (which is "voc") (5).
DBeaver will not have driver for MonetDB database installed, so it will offer you to download it. Just accept that offer.
At the end, objects of our database will appear inside of pane on the left side of a program. There, we should double click on schema name (1). After that we can select tab "ER Diagram" (2). There, after some rearangement, we will see ER diagram of our database (3). As we can see, tables are organised in star schema with "voyages" table as the only fact table. All tables are connected with foreign key constraints, where foreign key is also primary key inside of dimension tables. The only exception is Inovice table where foreign key columns are not primary key columns, and that is why that relationship is shown with dashed line (4).
DBeaver is an excellent program. If you want to learn more about it you can try this linkedin tutorial.
https://www.linkedin.com/learning/dbeaver-essential-training?trk=learning-topics_trending-courses_related-content-card&upsellOrderOrigin=default_guest_learning