Learning more about basic database terminology is a necessary step that will help us when we start coding.
Let’s go through the entire process of creating a database. Assume our database containing customer sales data has not been set up yet, ok?
So, imagine you are the shop owner and you realize you have been selling goods quite well recently, and you have more than a million rows of data.
What do you need, then? A database!
But you know nothing about databases. Who do you call, an SQL specialist?
No. You need a database designer.
She will be in charge of deciding how to organize the data into tables and how to set up the relations between these tables. This step is crucial. If the database design is not perfect from the beginning, your system will be difficult to work with and wouldn’t facilitate your business needs; you will have to start over again. Considering the time and data (…and money!) involved in the process, you want to avoid going back to point 0.
What do database designers actually do? They plot the entire database system on a canvas using a visualization tool. There are two main ways to do that. One is drawing an Entity-Relationship diagram, an ER diagram for brief. It looks like this… and, as its name suggests, the different figures represent different data entities and the specific relationships we have between entities. The connections between tables are indicated with lines. This way of representing databases is powerful and professional, but it is complicated. We will not focus in-depth on ER diagrams in this post, but you should know they exist and refer to the process of database design.
Another form of representation of a database is the relational schema. This is an existing idea of how the database must be organized. It is useful when you are certain of the structure and organization of the database you would like to create.
More precisely, a relational schema would look like this. It represents a table in the shape of a rectangle. The name of the table is at the top of the rectangle. The column names are listed below. All relational schemas in a database form the database schema. You can also see lines indicating how tables are related in the database. As you probably noticed, we have some other features, but we will leave their explanation for now.
Once the database design process is done, the next step would be to create the database. To this moment, it has been ideas, planning, abstract thinking, and design. At this stage, it would be correct to say SQL can be used to set up the database physically, as opposed to contriving it abstractly.
Then, you can enjoy the advantages of data manipulation. It will allow you to use your dataset to extract business insights that aim to improve the performance and efficiency of the business you are working for. This process is interesting. Remember – well thought-out databases that are carefully designed and created are crucial prerequisites for data manipulation. If we have done good work with these steps, we could write effective queries and navigate in a database rather quickly.
You will often hear the term database management. It comprises all these steps a business undertakes to design, create, and manipulate its databases successfully.
Finally, database administration is the most frequently encountered job amongst all. A database administrator is the person providing daily care and maintenance of a database. Her scope of responsibilities is narrower regarding the ones carried out by a database manager, but she is still indispensable for the database department of a company.
This post allowed us to learn certain terms you will be using often when dealing with basic database terminology and working with SQL.
Check out more of our database guides Database vs Spreadsheet, and Relational Database Essentials and enroll in our SQL course.
Next Video: Understanding ELIF