Tag: Database Design 101

Problem Set 2 – Identifying Entities and Attributes

function toggleDiv(id) { $("#hide-show-div-" + id).toggle(); var button = $("#hide-show-button-" + id); var label = button.prop('value'); if (label == 'Hide Solution') { button.prop("value", "Show Solution"); } else { button.prop("value", "Hide Solution"); } } 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: Countries Description: 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.

Problem Set 1 – Identifying Entities

function toggleDiv(id) { $("#hide-show-div-" + id).toggle(); var button = $("#hide-show-button-" + id); var label = button.prop('value'); if (label == 'Hide Solution') { button.prop("value", "Show Solution"); } else { button.prop("value", "Hide Solution"); } } 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 School Mr. Trotter, the proprietor of a rapidly-growing language school, wants to introduce a new system in his company.

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 What will the data in our system be about?

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 with simple applications like the sieve of Eratosthenes or enumerating the Fibonacci sequence.

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.

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 is atomic. It is common to say that a relation is in 1NF if all its attributes are atomic.

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.

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

iframe.video-plugin { width: 735px; height: 415px; border: 0px solid #CCC; margin: 0px; } @media all and (max-width: 767px) { iframe.video-plugin { width: 250px; height: 141px; } } 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 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

iframe.video-plugin { width: 735px; height: 415px; border: 0px solid #CCC; margin: 0px; } @media all and (max-width: 767px) { iframe.video-plugin { width: 250px; height: 141px; } } 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

iframe.video-plugin { width: 735px; height: 415px; border: 0px solid #CCC; margin: 0px; } @media all and (max-width: 767px) { iframe.video-plugin { width: 250px; height: 141px; } } 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

iframe.video-plugin { width: 735px; height: 415px; border: 0px solid #CCC; margin: 0px; } @media all and (max-width: 767px) { iframe.video-plugin { width: 250px; height: 141px; } } 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 the nouns and noun phrases you would use to describe the domain.