Relational Database Transcript

The business world is always changing and with this brings a new level of data management. Never have companies gathered as much data, and those who effectively warehouse and utilize the data gain a unique competitive advantage. But before we can mine, warehouse and analyze the information, we need to gain a better understanding of the infrastructures involved with data management.

Let’s start with the star of the video – data. There are two types of data from which we can choose to build our analysis which are structured and unstructured data. Structured data is usually considered or categorized as quantitative data which is extremely organized and easily understood by machine learning algorithms such as IBMs structured query language, which if that sounds vaguely familiar is long hand for SQL. Structured data is used in many parts of the organization such as with accounting, booking systems and the all-important customer relationship management software. Now, on the other side of the spectrum is unstructured data which is qualitative in nature and cannot be analyzed using the typical data analysis techniques. Just as the name suggests, unstructured data does not have a predefined data model and is managed in non-relational databases known as NoSQL. Unstructured data is now being employed in a variety of applications such as in data mining, predictive data analytics and of course chat bots.

Since the subject of this video is understanding relational databases a little more clearly, we will focus on structured data.

Have you ever wondered how the school manages your account seemingly so effortlessly? With over 20,000 students enrolled at the college every year, and all with unique experiences, needs and program requirements, you would think there is some kind of magic at play. But the system is dependent on a relational database that takes all aspects into account. So let’s have a closer look.

Every relational database or logical data structure uses ways of organizing various aspects of information known as tables, views and indexes. Thinking about what you know about Excel, you know that the spreadsheet is broken down into countless rows and columns. This is the same premise for a data table. Typically, the columns represent the fields that you would be required to fill out in a form, and in the case of your school, it would contain your first and last name, address, birthdate, when you enrolled and of course your program. Since every column represents a field, we store every record (that’s you for example) in a row. The row has all the answers to your questions. Simple right? Well this table will need something that sets you apart from other students. So we assign a unique identifier for who you are, which no other student has in the school – you guessed it, your student number. This number becomes what is known as the primary key for this table. By performing a search query based on the student number, we will get all of the information in this table.

Much like your student number being unique to you, every program offered at the college needs a separate table that contains the requirements for the program such as what courses, when they are offered etc. However, to keep the information straight, each program is given a unique identifier or primary key in that table. The program code that is identified in your student record is known as a foreign key because it serves as a reference identifier to another table. In doing so, the system will be able to link all of your information to all the information required for your program. This is the relational aspect.

So now that we have built a basic student profile, there are other tables involved here as well, for example, accounting has set up a table for what fees are due for each semester based on the program, how much you have paid etc. This accounting table is linked to your account as well, so anytime someone needs to find information out about you, they simply query your student number. This is a very rudimentary explanation of the structure but it gives a basic understanding of the model and other tables would include course description, grades, faculty, and so many others.

The simple yet powerful relational model is used by organizations for a broad variety of information needs. Relational databases are used to track inventories, process ecommerce transactions, manage huge amounts of mission-critical customer information, and much more. A relational database can be considered for any information need in which data points relate to each other and must be managed in a secure, rules-based, consistent way.

The relational model is the best at maintaining data consistency across applications and database copies (called instances). For example, when a customer deposits money at an ATM and then looks at the account balance on a mobile phone, the customer expects to see that deposit reflected immediately in an updated account balance. Relational databases excel at this kind of data consistency, ensuring that multiple instances of a database have the same data all the time.

With so much information being collected through businesses all of the time, a relational data base helps break the information down so that it can be used to track performance, forecast sales, and much much more.