On your journey to become a full-stack software developer, you will have to learn about databases. You may have already begun thinking about different database technologies like MySQL or Oracle 12c and other techniques for utilizing these databases. You have to start thinking about how to interact with a database to store and retrieve data and structure that information. In this blog, I will discuss how to extract data out of a database utilizing SQL queries. First, I will discuss a planning technique that many developers use to map out their data storage by creating a schema.
What do I mean when I say schema? A schema is how you describe the technique of organizing a database and connecting the relationships between its different pieces or "entities." It will tell you how specific data relates to other data. Think of a schema as a "blueprint for your database." A schema is something that you will design and plan during the starting stages of a project. It gives you a visual picture of your data before you begin writing code.
Design a database for a movie collection. We will only keep track of necessary information about the movies for this blog: their genre and their director. A real-life application of a movie database will require much more information, but I have chosen to keep things simple for this demonstration.
This blog's schema was designed in dbdiagram.io, an easy to use tool for drawing entity-relationship diagrams.
When you see or hear the term 'entity' while dealing with database diagrams, it refers to a distinct unit in our diagram that has data associated with it.
Our movie collection diagram shows a movie entity, a director entity, and a genre entity. Our entities in our schema represent a table. In the table, we list each of the attributes that are related to that table name.
We are discussing relational database systems and working with multiple tables. So we will need to make connections to show relationships between the tables. The connections shown in the diagram below represent a one to many relationships. Each movie will only have one director or one genre, but each genre is related to many movies, and a director could have made many movies. Those connections are shown below with the line.
The dir_id from our director table connects to the id_director in our movie table. The gen_id in our genres table connects to our id_genre in our movie table. You will see how we can utilize those connections later to display data from our table.
Let us create our table; you use SQL commands to make your table in the database; the CREATE TABLE statement will tell the database to name the table accordingly. In our movie, director, and genres tables, we stated our rows' names and declaring what data type they will be. The ALTER TABLE commands will establish connections between our tables. Since the director and genre tables are related to many different movies, we will add a foreign key to them and reference our main movie table.
Now that we've designed our tables, we can fill them with some movies. We can use INSERT INTO to state the table we would like to enter data. Then we can enter the column name in parentheses and their VALUES after.
If we would like to view all the data from our movie table, we can use the SELECT * FROM to select all the data of that table from the database.
You can see in id_genre and id_director the corresponding number to the genre and director they are related to.
Let us look at similar syntax to grab all of the data from our other tables.
If we would like to see our movie table and include the genre and director, we can utilize our connections to the other tables and join the data to our main table. In the code below, the LEFT JOIN keyword returns all movies from the left table (movie) and the matched genre from the right table (genres). The same for the related data in the director's table.
You can see below that we have all the data on our movies. It is essential to learn how to display data in your MySQL database because you may need to grab just the right kind of information for a website. Let us take a final look at our movies and all of their properties.
The journey to becoming a full-stack developer can seem overwhelming. But the accomplishment of learning a concept of computer science that has been around your whole life can be empowering. I hope you learned a little more about database planning and building a schema.
Stay Focused || Love your code
Resources:
Schema designed on dbdiagram.io
Top comments (0)