First of all; what is data?
Data is information that has been translated into a form that is efficient for movement or processing. In simple words, data can be facts related to any object in consideration. For example, your name, age, height, weight, etc. are some data related to you.
What is Database?
A database is an organized collection of structured information or data that is usually stored electronically in a computer system. The database is usually controlled by a database management system (DBMS). When the data and DBMS, as well as the associated applications, are brought together, it is often referred to as the database system.
You can organize data into tables, rows, columns, and index it to make it easier to find relevant information. Database handlers create a database in such a way that only one set of software program provides access of data to all the users. The main purpose of the database is to operate a large amount of information by storing, retrieving, and managing data. There are many dynamic websites on the internet nowadays which are handled through databases. For example, a model that checks the availability of rooms in a hotel. It is an example of a dynamic website that uses a database.
In this article, I will tell you about the “relational database”.
The word RDBMS is termed as “Relational Database Management System.” It is represented as a table that contains rows and column. Before giving an example of relational database, I would like to explain the SQL.
What is Structured Query Language (SQL)?
SQL is a standardized programming language used in almost all relational databases to query, manipulate and define data, as well as to provide access control. SQL was first developed at IBM in the 1970s with great contributions from Oracle, and later the SQL ANSI standard was implemented, laying the foundation for many extensions from companies such as SQL IBM, Oracle, and Microsoft. Although SQL is widely used today, new alternatives have begun to be developed.
In this example, I will tell you the simple database of the “obilet” application that I have made myself.
Database Diagram
First, I created a table called “passenger”. Each of these tables is actually an entity. I added “name, surname, gender, date of birth and phone number”, which are the attributes of the passengers, to the “passenger” table. And an important thing to remember is that each entity table must have an “id”. That’s why we added “passenger id” to our passenger table.
I then created the “ticket” table. I added “ticket time, date, price, payment id, passenger id, bus seat id, departure bus station and arrival bus station” to this table. Actually, this is the most important part, because that’s where our other tables mostly converge, because when we buy a bus ticket, we get a “ticket” as a result, so every piece of information will appear there. That’s why we have shown payment information, passenger information, bus and seat information, departure and arrival bus station information in this way.
We linked our city table to our bus station table, so now we can see the entered bus station information is in which city. We connected the bus station table to the departure and arrival bus station in the ticket table. In this way, we have sent information to the departure and arrival bus station.
Since buses are affiliated with many companies, we create two separate tables as “bus” and “company”, and connected them. However, an important point here is that not every bus has equal seats. So we created two more tables under the name “bus type” and “bus seat”. We added types as “50 seats” and “30 seats” in the “bus type” table. In the “bus seat” table, we added how many seats each bus has. I added up to 50 seat numbers to the seat table.
And finally, we created a “payment” table and added two separate options as “credit card” and “debit card”.
This is how a simple database is created.
Now let’s come to the code part.
The code part is actually much simpler once you have created this diagram correctly. We show all the data in the passenger table with the “select *” command.
When we write the column names we want to select instead of the “ * ”, we can select the data we want.
For example:
We can add a condition to our code with the “where” command. That is, we have listed the females in the passenger table.
In our other example, we used the “between” operator and listed the ticket prices between 100 and 200 TL in the ticket table.
For example:
We added passengers to our passenger table with the “insert” command. Since we ran the code two times, we added it two times.
For example:
Since we added the same passenger information two times, we deleted the last added passenger with the “delete” command.
For example:
With the “like” command, we first listed those passengers whose names begin with the letter “s” in the passenger table. Second, we listed those whose names end with “an”. and finally, we listed the ones with “re” in their name.
For example:
We have listed the average of the prices in the ticket table with the “AVG” command.
For example:
With the “UPDATE” command, we updated the price of the ticket with the ticket id 7 from 335 to 320.
For example:
“JOIN” command is used for joining tables. In our example, we listed the first and last name from the passenger table. We listed and joined the price from the ticket table.
For example:
In this article, I briefly and simply talked about the SQL and database. I hope I could help.
