Relational Database Design

This course is for anyone who wants to understand relational database design, or data modeling in general. You will learn how to gather requirements, model them, normalize the model, and transform that model into a fully normalized relational database design.

Course info

Rating

(350)

Level

Intermediate

Updated

Jan 6, 2014

Duration

7h 33m

Description

In this course, you will learn all the skills required to design good databases. This starts with information gathering: how to find all required information, and how to ask questions without miscommunications. You will learn how to abstract the information gathered into a data model, how to normalize the data model so that your database will be free of anomalies, and how to transform the final, normalized data model into a relational database design - ready to be implemented.

About the author

SQL Server MVP Hugo Kornelis is co-founder and R&D lead of perFact BV. He develops analysis methods and tools to generate completely functional SQL Server applications from the analysis. Hugo also likes to visit internet forums, read and write books and blogs as well as speak at conferences.

Section Introduction Transcripts

Section Introduction Transcripts

IntroductionHello, my name is Hugo Kornelis and I am presenting this Pluralsight course on Relational Database Design. This is Module 1, the introduction. In this module, I will first give a quick overview of how the hardware used to store computer data has evolved over time. Data storage methods and paradigms have seen many changes, most of which who are influenced by the possibilities and the limitations of the hardware and we'll look at the evolution of database technologies next. After that, I will focus on the Relational model, which is the subject of this course. I will discuss the fundamentals of the relational model and explain the set of rules known as Codd's Rules that should be at the core of any relational database engine and that also influence many of the data modeling rules we will see throughout the rest of the course. I will close off the introduction by describing the differences between Conceptual, Logical, and Physical data models.

ER ModelingHello, my name Hugo Kornelis and I am presenting this Pluralsight course on Relational Database Design. This is Module 2, which covers Entity Relationship Modeling. In this module, I will start with a quick overview of the advantages and risks of Entity Relationship Modeling. After that, I will dive in and start describing all the elements you can encounter in an Entity Relationship diagram, Entity types, Attributes, Relationships, which come in various kinds, and Subtypes.

Gathering InformationHello, my name is Hugo Kornelis and I am presenting this Pluralsight course on Relational Database Design. This is Module 3, where you will learn how to gather the information you need to create the data model. After the previous module, you should now be able to read and write the language of data modeling entity relationship diagrams. But before you can use that knowledge to draw a data model for a specific application, you will need to gather a lot of information. There are two main sources for this information, the people who are involved with the application like managers, subject matter experts, and future end users and documents describing their requirements for this application. In this module, I will first give an overview of the information you need and the sources you can use. After that, I will present the method you can use to scrape all the available information out of any written documentation you have at the start of the project. Then, in the last two sections of this module, I will give you some guidelines on how to interview people.

Creating The Initial ER ModelHello. My name is Hugo Kornelis and I am presenting this Pluralsight course on Relational Database Design. This is module 4, in which you will learn how to create a first version of the Entity Relationship Model. With all the information you gathered using the techniques from the previous module, you are now finally ready to start creating an actual entity relationship diagram. This is done in a series of steps. You first abstract all similar facts into fact types using a template with a placeholders to describe the form of these facts. You also investigate whether duplicates are allowed for each placeholder in the collection of all occurrences of a fact type. You can then use this collection of fact types to find what entity types you will need in a diagram. Each of those entity types will have an identifying fact type, which may or may not already be in your collection. To create the IDEF1x entity relationship diagram, you first put the entity types on the diagram. Once they are there, you can add their attributes and their relationships. Some fact types, are not represented as a normal entity type attribute or relationship, to represent them in IDEF1x, you need to introduce extra artificial entity types.

Converting to RelationalHello, my name Hugo Kornelis and I am presenting this Pluralsight course on Relational Database Design. This is Module 5 in which you will learn how to convert an entity relationship model into a relational design and how to convert a relational design into an entity relationship model. This module covers the conversion between two representations of data design, the entity relationship diagram and the relational database design. After explaining why we need both representations and hence need to be able to switch between the two, I will how a few alternatives for representing the relational database design. The majority of this module will of course focus on the actual conversions. Both from entity relationship to relational and the other way around.

Basic Normalization - Part 1Hello my name is Hugo Kornelis and I am presenting this Pluralsight course on Relational Database Design. This is Module 6 Basic Normalization (Part 1). In this and the next module you will learn to fine tune the design you that you can be certain that your database will have no redundancies or other design flaws. Once you've followed all the steps from the previous modules, you have a data model, either as an entity relationship model or as a collection of tables and columns, that enables the database to store all the information the business needs, and that will prevent old data that would violate any of the identified constraints. But there may still be issues, glitches in the design that can cause unwanted side effects, like redundancy, a data model that allows you to store multiple copies of the same fact, or update, insert and delete anomalies. A data model that makes it impossible to modify effects independent of each other. To remedy this, you need to normalize the data model. After an overview of what normalization is why we need it, I will first describe what functional dependencies are. This is required because functional dependencies play a major role in the normalization process. I then explain the three most important normal forms, quite and imaginatively called First Normal Form, Second Normal Form and Third Normal Form. Interweaved with that description, I will show a method that you can use to make absolutely sure that you find all the functional dependencies so that you can be sure that your database design is perfectly normalized. Because of the amount of material covered, this information will be spread over two modules.

Basic Normalization - Part 2Hello, my name is Hugo Kornelis and I am presenting this Pluralsight course on Relational Database Design. This is Module 7: Basic Normalization (Part 2). This module continues the process of fine tuning the design of a database that we started in the previous module. At the end of the previous module, we had a design in First Normal Form and we had made a start with finding some of the functional dependencies. In this module, we will continue our hunt for the rest of the functional dependencies and further normalize the design. First to Second and then to Third Normal Form.

Higher Normal FormsHello, my name is Hugo Kornelis and I am presenting this Pluralsight course on the Relational Database Design. This is Module 8: Higher Normal Forms. In this module you will learn about normalizing your database beyond Third Normal Form. In the previous module, I covered normalization up to the Third Normal Form, but database science has defined many more normal forms often collectively called the higher normal forms. Many data modelers and many teachers as well will tell you that Third Normal Form is enough for practical applications and that higher normal forms are of academic interest only. Others respond that this statement results from a lack of proper understanding of the higher normal forms. In this module I will describe and illustrate all the higher normal forms so that you can be your own judge on this. At the end of the module I will also briefly cover the subject of denormalization and I will finish the module with some standard patterns that you can use to solve issues that find no direct answer in the normalization rules.

Other ER MethodsHello, my name is Hugo Kornelis and I am presenting this Pluralsight course on Relational Database Design. This is Module 9, the final module in which I will give you a broad overview of the many alternative methods available for diagraming your entity relationship models. In this module we will investigate the many alternative representations that are used in various ER diagraming methods. Entity types may use specific notations for weak or strong entity types, or for subtypes. Attributes can be omitted completely, depicted in a compact way or very elaborate, and some methods even support special types of attributes. Relationships can also be depicted in a variety of ways. Identifying and subtype relationships may have a specific notation and the variations for specifying cardinalities appear unlimited. Plus, all methods have their own ideas of what types of relationships are or are not allowed, but despite these differences, once you see through the graphical differences most methods are actually all quite similar.