Understanding the MySQL System

00:00 You have probably heard of the MySQL term before. Have you ever wondered why we need it and how it is different from the notion of a database? How does it allow Python applications to interact with the database?

00:15 In this lesson, I will illustrate the key concepts of MySQL on the example of the three datasets that you will be working with in this course. By the end of this lesson, you will learn what the MySQL system is, and how it organizes data for efficient storage management and retrieval.

00:37 Firstly, the MySQL system applies to structured data only. These data are organized in tables with rows and columns that represent so-called attribute values.

00:49 They’re typically stored in a tabular format that you are familiar with, such as Excel, CSV, or TSV. Here you see samples of tabular datasets.

01:03 For example, a so-called movies dataset has movie attributes such as title, release_year, genre, and collection_in_mil. And many tabular data naturally invite statistical data analysis.

01:16 However, tabular formats come with their own limitations in regards to the number of rows they can store. And data volume never stays static. With a nonstop movie production, the records in ratings, movies, and revenue tables will grow in numbers.

01:35 All in all, tabular data formats are not suitable for storage and processing of increasingly large volumes of data.

01:46 MySQL uses a relational data modeling approach to capture relations between tables that represent entities. In the given example, entities are movies,

01:58 reviewers, and ratings. In MySQL, a unique identifier is assigned to each row of the table. In so-called parent tables, these unique identifiers are primary keys.

02:14 You can see movie_id and reviewer_id columns in movies and reviewers tables playing a role of primary keys. Notice how they appear in the ratings table.

02:28 A combination of movie_id and reviewer_id uniquely identifies each rating and references it to a specific reviewer and a specific movie. This combination of primary keys is called a foreign key, and the ratings table is in essence, a child table since it carries identities of both parents, movies and reviewers.

02:54 This is how parent-child relations are established in MySQL, and this diagram is a simple example of a relational data model.