Author: Agnieszka Kozubek

Chief Scientist at Vertabelo

Vertabelo Challenge #1: database model for the game of Set

Today we publish the first  Vertabelo Challenge . Vertabelo Challenge is a fun and educational way to earn a discount for Vertabelo. Complete four challenges and we’ll email you a coupon for 50% off your Vertabelo subscription.The game of SetSet is a card game that you play with a special deck of cards. Each card has four features:shape (oval, diamond, squiggle),color (pink, purple, green),number of figures (1 to 3),shading (solid, opaque, hollow).The deck consists of 81 cards.

The Boyce-Codd Normal Form (BCNF)

Why do you need all of this normalization stuff? The main goal is toavoid redundancyin your data. Redundancy can lead to various anomalies when you modify your data .Every fact should be stored only onceand 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

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 aboutcandidate keysin a table.A candidate key is a set of columns such thatall other columns in the table are dependent on it, and the set isminimal, that is if you remove a column, then the resulting set is not a candidate key.Example: Table Person

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 aboutfunctional 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. I will try to keep things simple and down-to-earth. (The operative word being try ;) )

Update Anomalies

Let’s take a look at the following table:What’s wrong with this table? It’s difficult to modify data in it. Upon modification, several anomalies can occur:Insert anomaliesIt’s impossible to insert a product into the table if the product hasn’t been bought by a customer yet. Similarly, it’s impossible to insert a customer who hasn’t made a purchase yet.Update anomaliesIt’s difficult to update data in the table. If you want to change the name of the product, you have to update all rows where the product is bought. You cannot change the price of the product for all future purchases.

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 DescriptionYou 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. In real life, preparing a description is a

ON DELETE RESTRICT vs NO ACTION

When you create a foreign key in your database, you can specify what happens upon delete of the parent row. There are usually four possibilities:ON DELETE SET NULLON DELETE CASCADEON DELETE NO ACTIONON DELETE RESTRICTToday we’ll investigate the subtle difference between the last two options.In Some Databases There Is No Difference at AllIn Oracle, there is no RESTRICT keyword . The only option is NO ACTION. In MySQL, there is

Milestones of Relational Databases

Computer databases first emerged in the late 1960s after computers had become powerful enough to handle large amounts of financial, human resources and bank data. Let’s take a look at the history of how modern databases came about.1960s:navigational databases, mostly hierarchical databases and network databases, were first used1970:E.F. Codd from IBM introduces the concept of relational databases and the first normal form1971:second and third normal form are introduced by Codd1973:project INGRES (the predecessor of PostgreSQL) begins in Berkeley

How to Count XML Nodes in PostgreSQL 9.1

Recently I was given a task which involved counting, with a single SQL query, all tables in each model version of Vertabelo. Vertabelo internally stores each model version as an XML file (download a sample XML file ). So my task was to count all XML nodes satisfying a certain XPATH expression. Vertabelo runs on PostgreSQL 9.1, so PostgreSQL 9.1 tools were all I could use.To begin at the end, the final query looks like this:

MySQL’s group_concat Equivalents in PostgreSQL, Oracle, DB2, HSQLDB, and SQLite

group_concat in MySQLMySQL has a very handy function which concatenates strings from a group into one string. For example, let’s take a look at thechildrentable with data about parents’ and children’s names.To get the names of children of each person as a comma-separated string, you use thegroup_concatfunctions as follows:The result:To make sure the names of the children in each string arealphabetically orderedand to use semicolon „;” as aseparator, use this query: