Category: videolister

ER-diagram & SQL/DBeaver-schema

Hello, once again, and welcome all to an example on how to go from an ER diagram into a DB schema!

We will look into this example, by usin Videolister, which I did an update on a little while ago. To develop and transform the videolister tool into an online tool, an ER-diagram (ER=Entity Relationship) was made for the new multi-user tool.

I used ERDplus to make it, but draw.io would be just as sufficient.

The ER diagram for Videolister

After making the ERDplus, I used DBeaver program to transform it into a database schema.

SQL/DBeaver diagram of Videolister

Though I used DBeaver to do this, it is also possible to create this using other similar programs, or the SQL language. With similar programs, and in DBeaver, the SQL portions are often created for you and your work in making the schema stays mostly visual.

Here are some examples of SQL that DBeaver created for “Linkkikokoelma” and “Videolinkki” tables:

CREATE TABLE Linkkikokoelma (

      kokoelma_ID INTEGER NOT NULL,

      nimi VARCHAR NOT NULL,

      kuvaus VARCHAR,

      käyttäjänimi VARCHAR NOT NULL,

      CONSTRAINT Linkkikokoelma_PK PRIMARY KEY (kokoelma_ID),

      CONSTRAINT Linkkikokoelma_Käyttäjä_FK FOREIGN KEY (käyttäjänimi) REFERENCES Käyttäjä(käyttäjänimi)

);

CREATE TABLE Videolinkki (

      kokoelma_ID INTEGER NOT NULL,

      URL VARCHAR NOT NULL,

      otsikko VARCHAR NOT NULL,

      kuvaus VARCHAR,

      arvosana INTEGER,

      CONSTRAINT Videolinkki_PK PRIMARY KEY (URL,kokoelma_ID),

      CONSTRAINT Videolinkki_Videolinkkilista_FK FOREIGN KEY (kokoelma_ID) REFERENCES Videolinkkilista(kokoelma_ID)

);

After the tables have been added and connected visually, it’s time to add data into the tables. Here are some examples using the same tables as above:

Data that was inputed onto Linkkikokoelma table
Some of the data inputed onto Videolinkki table

All in all, I think DBeaver was quite easy to pick up and use (at least somewhat) effectively. It was very helpful in managing the general flow of database creation and modification.

That was all for this time; until next time!

Links for those interested:

ERDplus: https://erdplus.com/standalone

DBeaver: https://dbeaver.io/

Videolister – a List of links

I worked on a project that I am calling Videolister.

The idea for the project came from one of my friends. They mentioned storing link to various ukulele tutorials in a simple text editor, not all in one place, and how it was a chore to go through. So I thought of making a simple tool for maintaining list of links to youtube videos.

This was great for me as I was looking for a java object oriented programming exercise, and I had just found what I was looking for.

Bellow are some screenshots and explanations.

Here is the main screen of the program, after choosing a List. In this main screen you manage all added videos in the specific List.

Should you click the “Modify tags” button in the earlier view, you’d be met with this pop-up. In here you create and manage the tags that can then be added to your video for easier identification and searching.

Here is the “filter config” window, opened by the button with the same name on the main screen. It is essentially a rudimentary search system. It applies a filter on every video on the List and only shows those that match the given parameters.

I should have some pictures still saved from earlier versions and other neat things to give a sort of tutorial on how to make one. I might add it here at some point. Who knows?

Still there is one more picture for me to share:

This is a printed table of how the data is stored for the program to read. It’s not all the data it needs, but I thought it would be nice to get to see this for some. So there you go!

That was all this time around. Thanks for reading!