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.
After making the ERDplus, I used DBeaver program to transform it into a database schema.
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:
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!