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 Set Set 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 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.

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 NULL ON DELETE CASCADE ON DELETE NO ACTION ON DELETE RESTRICT Today we’ll investigate the subtle difference between the last two options. In Some Databases There Is No Difference at All In Oracle, there is no RESTRICT keyword. The only option is NO ACTION.

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 used 1970: E.F. Codd from IBM introduces the concept of relational databases and the first normal form 1971: second and third normal form are introduced by Codd

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:

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.