Author: Agnieszka Kozubek
Agnieszka is a Chief Content Officer at Vertabelo. Before coming to Vertabelo, she worked as a Java programmer. She has a PhD in mathematics and over 10 years of experience in teaching mathematics and computer science at the University of Warsaw. In her free time, she enjoys reading a good book, going mountain hiking and practicing yoga.
Vertabelo XML version 2.1
Vertabelo internally stores your model in an XML format. You can download the XML file and use it to extract some information by using a script. Let’s take a look at the structure of this XML file.
if (typeof VertabeloEmbededObject === 'undefined') {var VertabeloEmbededObject = "loading";var s=document.createElement("script");s.setAttribute("type","text/javascript");s.setAttribute("src", "https://my.vertabelo.com/js/public-model/v1/api.js");(document.getElementsByTagName("head")[0] || document.documentElement ).appendChild(s);} Here is the XSD file and an example of the XML file for the above sample database model created in Vertabelo.
How to filter tables in the Vertabelo reverse engineering tool
Sometimes you don't want the reverse engineering tool to extract all of the tables in your database. For example, the database may contain technical tables which are logically are not part of the model. This is when the -regexp option comes in handy.
Use the -regexp option and the tool will extract all tables and views with names matching the regular expression. The tool prints the regular expression it uses so you can verify that your shell parsed the expression correctly.
The Boyce-Codd Normal Form (BCNF)
Why do you need all of this normalization stuff? The main goal is to avoid redundancy in your data. Redundancy can lead to various anomalies when you modify your data. Every fact should be stored only once and you should know where to look for each fact. The normalization process brings order to your filing cabinet. You decide to conform to certain rules where each fact is stored.
Nowadays the go-to normal forms are either the Boyce-Codd normal form (BCNF), which we will cover here today, or the third normal form (3NF), which will be covered later.
On Keys
Today we continue our series of posts on data normalization. In the previous post on data normalization I explained what functional dependency is. Today we will talk about candidate keys in a table.
A candidate key is a set of columns such that all other columns in the table are dependent on it, and the set is minimal, that is if you remove a column, then the resulting set is not a candidate key.
On Functional Dependencies
Do you remember the post about update anomalies? I promised you we’d explain how to design tables which have no update anomalies. So here we go!
Today we begin a series of posts on data normalization. We will talk about functional dependencies, a concept that needs to be explained before we dive deeply into database schema normalization.
The subject is rather abstract and theoretical but I will try to restrain myself from going too deep into mathematics.
Update Anomalies
Let’s take a look at the following table:
Customer Purchase date Product name Amount Price Total price Joe Smith 2014-02-14 Yoga mat 1 80 80 Jane Bauer 2014-02-16 Yoga block 2 30 60 Joe Smith 2014-02-14 Yoga block 2 30 60 Joe Smith 2014-02-14 Yoga strap 1 10 10 Thomas Apple 2014-02-18 Dumbbells 2kg 2 30 60 Jane Bauer 2014-02-16 Yoga mat 1 80 80 What’s wrong with this table?
How to Create a Database Model From Scratch
So you want to create your first database model but you don’t know how to start? Read on!
I assume you already know a little about tables, columns, and relationships. If you don’t, watch our video tutorials before you continue.
Start With a System Description You should always start creating a database model with a description of a system. In a classroom situation, a system description is given to you by a teacher.
Database Design 101
A good data modeling exercise for beginners is to create a data model of an online store. Every time I give this exercise to my students, I’m surprised at how difficult it is for them.
Find the Concepts... Let’s see how it can be done. We know we have to create a table for every concept in the domain. Think about the nouns and noun phrases you would use to describe the domain.
How to import an existing database into Vertabelo: Reverse Engineering
The newest Vertabelo feature is Reverse Engineering: a way to import an existing database into Vertabelo. The Reverse Engineering tool is a simple command-line Java application that you can download from our website. It connects to your database, reads the table and view details and stores the info in an XML file. Import the XML file into your database model and voila - your database is in Vertabelo!
How to use the reverse engineering application Download the Reverse Engineering application from our website, then run the Reverse Engineering application.
Theory and History: Why are Relational Databases “Relational”?
Many people wonder why relational databases are called “relational.” Some think that it’s because of a logical entity-relationship model you often start your design with. Or, because you have tables and relationships (aka foreign keys) between them. But that’s not the case.
The name comes from the mathematical notion of “relation.” It all started with E. F. Codd who in 1970 (in the article A Relational Model of Data for Large Shared Data Banks) proposed something now called relational algebra as the mathematical foundation of databases.
