Tag: database design 101

Problem Set 2 – Identifying Entities and Attributes

In an earlier article on data modeling we promised to give you a set of exercises to practice finding entities and attributes. Here is the second installment of our problem set. Enjoy.Problem 1: CountriesDescription:Find the right entities and their attributes to represent all the countries in the world, their interior regions (which can be called states, provinces, or regions) and their cities. We want to represent each country’s name, continent, date of independence, type of government and population. For each region (or province, state, etc.) we wish to store the capital city, the name of the governor, and the population. Finally, for each city we want to have the name, founding date, population, and the number of schools per inhabitant. We would also like to represent what every country calls its interior regions.

Problem Set 1 – Identifying Entities

In an earlier article on data modeling we promised to give you a set of exercises to practice finding entities. Well, here they are. Have fun!Problem 1: Language SchoolMr. Trotter, the proprietor of a rapidly-growing language school, wants to introduce a new system in his company. He can no longer keep track of all the information on his own, so he’s asked for our help. He is a bit chaotic, though. This is what he said:

Database Modeling Course (2)

You’re finally ready to get down to real data modeling. We’ll start off with entities and their attributes. Entities are the basic building block of every data model. In this post, you’ll find out what they are and how to identify them.What Is an Entity? What is a Specific Instance of an Entity?Data models help us to identify what kind of information we’ll store in our system. We use such models to address the question

Database Modeling Course (1)

Data modeling is an essential step in the process of creating any complex software. It helps developers understand the domain and organize their work accordingly. In this article, which begins a new series devoted to database modeling, we’ll try to convince you why you should include it in your projects and what it looks like.Do I Really Need Data Modeling?As a novice developer, you often start your programming adventure withsimpleapplications like the sieve of Eratosthenes or enumerating the Fibonacci sequence. In such cases, a data model is not required – these programs are quite simple and they have a clearly defined input and clearly defined output. The difficulty of these tasks lies in the algorithm which has to work correctly and quickly. The data themselves are easy to understand: you provide some parameters and you get a set of numbers as a result.

Why SQL Is Neither Legacy, nor Low-Level, nor Difficult, nor the Wrong Place for (Business) Data Logic, but Is Simply Awesome!

The following fallacies are things that I hear all the time.SQL is legacy. Why can’t we work with more modern tech?– Timeless. E.g. by someone who thinks that NoSQL databases are “modern”.SQL is low level, like assembler. Would you prefer to work with assembler or with Java? Similarly, would you prefer to work with SQL or with Hibernate?– Timeless. Someone who thinks that SQL is low-level.In this article, let me clear up some common SQL prejudices and other fallacies. SQL is neither legacy, nor low-level. Here’s why:

What Is the Actual Definition of First Normal Form (1NF)?

The First Normal Form (1NF)is exceptional. The other normal forms (2NF, 3NF, BCNF) talk about functional dependencies and 1NF has nothing to do with functional dependencies. Moreover, we have precise definitions for other normal forms and there is no generally accepted definition of 1NF.Does 1NF Equate to “Atomic Attributes”?When you look at various descriptions of 1NF the word that you see most often isatomic. It is common to say that a relation is in 1NF if all its attributes are atomic. A good, theory oriented book by C.J. Date (

How (And How Not) to Decompose Relations

When you read about normalization you usually get the set of conditions that a database in the nᵗʰ normal form should satisfy and the set of rules, a sort of a cook-book, for obtaining that normal form. But why these rules are safe to apply to your denormalized relations is a skip material. Here, I would like to present some elementary concepts on how we decompose relations and what can go wrong.The concepts I want to present may seem a bit complicated but, on the other hand, they are so basic that you rarely see it written. Still, they are essential for understanding how the normalization process works. One may prepare a perfect meal just by following a recipe but no one can master the art of cooking without understanding what’s going on behind-the-scenes. The same holds true with databases.

How to Model Inheritance in a Relational Database

In the process of designing our entity relationship diagram for a database, we may find that attributes of two or more entities overlap, meaning that these entities seem very similar but still have a few differences. In this case, we may create a subtype of the parent entity that contains distinct attributes. A parent entity becomes a supertype that has a relationship with one or more subtypes.First, let’s take a closer look at a simple class diagram.

N-ary relationship types

When we design a database, we draw an entity relationship diagram (ERD). It helps us understand what kind of information we want to store and what kind of relationships there are. It is imperative that this diagram is easy to read and understand.The number of entities in a relationship is the arity of this relationship. The aim of this article is to give some examples and show how big an impact the arity of relationships has on not only the readability of the diagram, but also the database itself.

Vertabelo Challenges: test your database modeling skills!

Today we begin a new series on our blog:Vertabelo Challenges. Once in a while we’ll publish a new database modeling challenge on our blog so you can test your database modeling skills in Vertabelo. Don’t worry – the challenges will be fairly easy. Follow our Database Design 101 series on the blog and on Vertabelo Youtube channel , and you should be able to conquer all of them in no time.

Database Design 101

In this video you will learn how to start creating your database model. You will find out why nouns are important and how you should handle them when creating a database model.If you want to learn more, read our beginner tutorial on how to create a database model .

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

Database Design 101

In this video tutorial you will learn about references – how to create a relationship between the tables, how it affects their structure, and how it looks in the data.

Database Design 101

Vertabelo presents part 2 of our Database Design 101 series that brings you easy-to-understand introduction to databases. This time we will focus on table columns and most commonly used data types.

Database Design 101

Welcome to our new Database Design 101 series that brings you an easy-to-understand introduction to databases. Let’s begin with answering the question what a database is and how data is stored in databases.

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 thenouns and noun phrases you would use to describe the domain. Roughly, every noun iseither a concept, an attribute of a concept, or an example