Relational Data Modeling

Description

Relational modeling is often used for building transactional databases. You might say, 'But I'm not planning to become a back-end engineer'. Apart from knowing how to move data, you should also know how to store it effectively which involves designing a scalable data model optimized to drive faster query response time and efficiently retrieve data. Your data model should also ensure optimal data quality with constraints, usage of primary and foreign keys, data validation, and assertions of relationships between different tables, among other techniques.

How to construct and read a data model

A data model consists of three elements: entities, attributes, and relationships. As you move from a conceptual data model to a logical data model, and finally, to a physical data model, you move from a model depicting concepts (entities) and relationships on a high level (conceptual data model) to a specific detailed representation of the database structures (physical data model). In the course, we will review the entire process of designing a relational data model, starting from identifying entities and attributes to converting entities into tables and columns, from defining relationships to determining primary and foreign keys to link the tables.

How to normalize a data model from 0NF to 3NF

Normalization is a process of organizing data in a database using normalization rules/normal forms. The first normal form (1NF) assumes that (1) each table has a primary key, (2) every non-primary key attribute is fully dependent on the primary key, and (3) no single column has multiple values. An entity in the second normal form (2NF) must achieve the first normal form and assume that no attribute is dependent on only a portion of the primary key. The entity in the third normal form (3NF) meets the criteria for 2NF and ensures that each non-primary key attribute doesn't depend on the entity in another key column. As a part of the design process, we will look into normalization.

MySQL server deployment with Docker

There are multiple ways to set up a MySQL server, starting from installing and managing a server instance on your local machine to running a MySQL database as a service on the Amazon cloud. For the practical hands-on, we will be running MySQL as a container on a local machine.

MySQL Workbench 

DBeaver, dbForge Studio, PHPMyAdmin, Toad Edge for MySQL, MySQL Workbench, etc. are all GUI client tools to manage MySQL databases visually. You can also run MySQL commands from the command line with the MySQL command-line client. As part of the practical hands-on, you will install MySQL Workbench, a MySQL GUI client, and create an ER diagram of a database in MySQL Workbench using its modeling capabilities.

Requirements

You should have done our Docker course before starting this course. This guide has the prerequisite that you create and activate a virtual environment for the hands-on parts.

You could review the following related courses in our Academy: 

• Introduction to Python
• Welcome to the Python for Data Engineers training
• Schema Design Data Stores
• Choosing Data Stores