In this new series, we're going to learn about the entire process of working with databases—how to create a conceptual model, generate the SQL schema, connect to our database from Visual Studio, write triggers and stored procedures, and finally, how to use our database via a C# application. We'll cover some new things, such as the basics of SQL, and some simple C# code which should be relatively easy to understand for anyone with basic knowledge of Java or C++. We will also work with some new tools like MySQL Workbench.
However, before we start making our database, we need to say a couple of words about databases in general, like why and when we use them and what DBMS and SQL refer to. Generally, a database is a collection of information that is organized so that it can easily be accessed, managed, and updated. Databases can be categorized in many different ways, based on their content, purpose, method of internal organization.
A database management system (DBMS) is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data. A general-purpose DBMS is designed to allow the definition, creation, querying, update, and administration of databases. We will be using the MySQL DBMS, but some other well-known DBMSs include Microsoft SQL Server, Oracle, MongoDB, etc. Going forward, we'll show you how MySQL makes it relatively easy to handle our database.
You've probably heard of SQL many times, but maybe you didn't know exactly what it is. SQL - Structured Query Language - is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS) like the one we'll be using. SQL is a powerful, declarative language based on relational algebra, and it's used to retrieve, insert, update and delete data from the database, write various queries, and create views, triggers, stored procedures, etc. As our series continues, we'll explain the basic syntax and concepts of SQL.
In one of the first steps of our series, we'll create a conceptual (entity-relationship) model from which we'll generate the SQL schema of the database. Please note that a professional might skip this step and write the schema immediately, but for us, the conceptual model is very important because it is a visual, intuitive representation of our database.
It's very important to design your database cleverly from the beginning. Also, note that the word used here was "cleverly," not "correctly," since there are many different ways to design a database which stores the same data. A cleverly modeled database is one which allows the user or administrator to easily acquire data through queries. This is not always a simple task; it's sometimes even considered an art, which is why database professionals are highly respected in the IT field.
The largest part of this series will be dedicated to SQL—performing database operations and writing more complicated queries and structures, like triggers and stored procedures. This can be done using Visual Studio or MySQL Workbench. MySQL workbench, unlike Visual Studio, is a specialized tool for working with MySQL databases.
Finally, after we've learned to manipulate our database with SQL, we'll make a small application using C# which will allow users to work with the database via a graphic interface. In real life situations, users are typically given certain privileges, meaning that not everyone can insert, modify, or delete sensitive data. Database security measures are performed at every level of the database—when creating the schema, writing the triggers, and in the application itself. We'll cover this topic and more in the following articles, so stay tuned and keep visiting our blog to read the latest posts!