Relational Database Best Practices

365 Careers
A free video tutorial from 365 Careers
Creating opportunities for Business & Finance students
4.5 instructor rating • 74 courses • 1,507,665 students

Lecture description

In this lesson, we will provide you with the tools that are essential for dealing with relational databases. They will help you maintain a database that can be characterized as:

  • compact
  • well-structured
  • efficient

Learn more from the full course

SQL - MySQL for Data Analytics and Business Intelligence

SQL that will get you hired – SQL for Business Analysis, Marketing, and Data Management

09:32:50 of on-demand video • Updated March 2021

  • Become an expert in SQL
  • Learn how to code in SQL
  • Boost your resume by learning an in-demand skill
  • Create, design, and operate with SQL databases
  • Start using MySQL – the #1 Database Management System
  • Prepare for SQL developer, Database administrator, Business Analyst, and Business Intelligence job opportunities
  • Adopt professionally tested SQL best practices
  • Gain theoretical insights about relational databases
  • Work with a sophisticated real-life database throughout the course
  • Get maximum preparation for real-life database management
  • Add data analytical tools to your skillset
  • Develop business intuition while solving tasks with big data
  • Study relational database management theory that you will need in your workplace every day
  • Learn how to create a database from scratch
  • The ability to take control of your dataset – insert, update, and delete records from your database
  • Be confident while working with constraints and relating data tables
  • Become a proficient MySQL Workbench user
  • Acquire top-notch coding techniques and best practices
  • Know how to answer specific business questions by using SQL’s aggregate functions
  • Handle complex SQL joins with ease
  • Approach more advanced topics in programming like SQL’s triggers, sequences, local and global variables, indexes, and more
  • Merge coding skills and business acumen to solve complex analytical problems
  • Become a proficient SQL user by writing flawless and efficient queries
  • Tons of exercises that will solidify your knowledge
  • The freedom to query anything you like from a database
English [Auto] All right, let's shift gears. What is the first thing that comes to your mind when you hear the word database? For many people, this question is more challenging than it might seem at first. An answer like a big file where much information is stored is not satisfactory and we're not pleased. Potential employers. You should remember, there are two main types of databases, relational and non relational. The former will be the focus of this course, while the latter regards more complex systems. Although understanding not relational databases requires a serious mathematical and programming background, some of the logic applied in its coding is the same as Ezekial. Likewise, relational databases have a few advantages on their own, a small bit of theory will explain why they are still the preferred choice in many companies and institutions databases. Main goal is to organize huge amounts of data that can be quickly retrieved upon users request. Therefore, they must be compact, well-structured and efficient in terms of speed and data extraction. Today, people need such extra efficiency because data occupies memory space and the bigger IT size, the more sluggish the database is and the slower the retrieval process becomes. If we have a database containing one multimillion row table with many columns, then every time a request has been received, the server must load all the records with all fields and it would take too much time for a task to be completed. Don't forget, every symbol is a container of information and requires bytes of storage space. Hence, loading that much data will not be an easy job for the computer. So what allows us to contain so much data on the server, but lets us efficiently use only the portions we need for our analysis? The secret lies behind the use of mathematical logic originating from relational algebra. Please don't worry. We will not bother you with math. Imagine each table with data is represented by a transparent circle that contains all the data values of the table categorized by columns, or as we will often call them, fields. Now, if our database consisted of only one table, a giant circle would represent the entire database, something like this huge table from our fictional example with the sales database. And when we need a piece of information from the database. For example, if we wish to see who has bought something on a certain date, we will have to lift the whole big circle and then search for what we need. This challenge seems vague in the process of data extraction will not be efficient. See what can happen if we split the circle into three smaller circles, just as we did with the sales database. One circle will stand for the sales table, the other four customers in the last one for items. There are various theoretical combinations between three or more circles, but in our database we have the following model sales and customers have the same customer ID column. And sells and items have the same item code column. This way, we can see the circles overlap as they have common fields, so if we'd like to extract the same information, the name of the customers who have purchased something on a given date, we will need only the date of purchase column from the sales table in the first and last name from the customer's table. So to satisfy this request, we will not need to lift the third circle from our database items. This way we can save energy or more technically increase efficiency, less data represented as only two of the three circles will be involved in this operation. The mathematical trick lies in relating the tables to one another. Relationships were formed, namely through these common fields. More explanations about this technique will be provided in our next lectures. Anyway, I am sure that now you understand why we use the term relational databases. Some professionals may refer to the tables or the circles in our plot as relations because theoretically they are the smallest units in the entire system that can carry integral, logical meaning. Likewise, the three circles are all part of our sales database, when we combine the database and its existing relations, we obtain the famous term relational database management system frequently abbreviated as RDBMS. We hope this theoretical illustration makes things clearer. Skills designed for managing relational database management systems and can do that by creating relations between the different tables in a database. Stay tuned for the next lecture where we will outline the most substantial differences between a database and a spreadsheet. Thank you for watching.