This week at GET HIP we learned about databases, specifically relational databases. In addition, we learned how to interact with the databases through SQL, Structured Query Language. We started out by learning about tables which contain attributes and records. Then we were taught the different ways tables can relate to each other: One-To-One, One-To-Many, and Many-To-Many. The first means that there is only record (child) related to another record (parent), possibly in the same table. In an Entity-Relationship Diagram (ERD), which describes relationships between entities, it would be denoted like this:
The next kind of relationship, One-To-Many means that a record is related to zero or more other records. In an ERD:
Finally, the Many-To-Many means that zero or more records can be related to zero or more other records. In an ERD:
Here is a complete example of an ERD that features all three types of relationships and details the attributes of each entity.
Now the question is how do we get data from the database? Well, this is where SQL comes in handy. Imagine we want to grab all the SchoolClasses where the student_id is the same as student number 5. You will notice that the SchoolClass above has no student_id attribute, impossible then right! False, the student_id attribute is essentially “inherited” from the Student table, this student_id attribute is referred to as a foreign ID, it relates the SchoolClass record to the correct Student record. Now that we’ve hashed that out, time for some code.
select * from school_class_student where student_id = 5
This SQL statement does multiple things for us, the “select” is the keyword that says we are going to grab some data from the database, we are going to read it. The next character, the “*”, is referred to as a wildcard and it means that we will take every record that matches what we are looking for. The “from” keyword specifies what table we are going to grab the information from. (This is the time you are probably asking “there is no such thing as the school_class_student table”, but there is. Because the relationship between a SchoolClass and a Student is Many-To-Many, there is an intermediate table between the two that contains connectors that point to the proper records in each). The “where” keyword is how we specify the restraints we want to look for, in this case “student_id = 5”. This SQL statement would read, in plain English, “Select all the records from the table school_class_student where the student_id is equal to 5”.
SQL is a very powerful language that enables us to interact with databases, while I just described a select statement (traditionally called a read statement), there are 3 other kinds, Create, Update, and Delete. These four types form what is called CRUD operations, the four most basic types of interaction one can perform to a database. An example of a create statement in SQL is as follows:
insert into students set (id, email, password, name, salt, grade) values (29, ‘email@example.com’, ‘hunter2’, ‘TesterMcTesterson’, ‘FAU428Fa82udS871jaDF28’, 10);
This statement effectively inserts a new record into the students table where the id is 29, the email is firstname.lastname@example.org, the password is hunter2, the name is TesterMcTesterson, the salt is that string of characters, and the grade is 10.
As you can see, SQL is a versatile language that includes all someone could want/need for database interaction. Databases and SQL are what make every application possible.