1. Introduction to Relational Databases

1. What is a relational database?

• A relational database is a type of database where the way the pieces of data relate to each other is critical to the operation of the database
• Relational databases are known for their characteristic table structure for data
• They are also known for the language used to work with them, SQL (Structured Query Language)

2. How does a relational database store data?

• As previously mentioned, relational databases are known for their table structure for data, but what does that mean?

• Let’s start with an example and build a relational database table from it

• Example:

  • You’re tasked with managing information in a school, and you need to store the student’s information in a relational database
  • To start with you have three students to store:
    • John Doe, student ID: 1, age: 15, current grade: 10
    • Jane Smith, student ID: 2, age: 16, current grade: 11
    • Sam Brown, student ID: 3, age: 14, current grade 9

• Judging by what we were provided from the school’s administration, we have four pieces of information we need to store for each student:

  • Name
  • Student ID
  • Age
  • Current Grade

• Given we’re only storing data about students, all this data can fall into one relation or table

  • A table is how a relational database stores data about a specific relation of data
  • Each table is made up of rows and columns

• So, we’ll start by creating a table for the students

• As mentioned above, a table needs rows and columns

  • A column in a table is used to represent a specific attribute of the data
    • In our case with the students, we have four pieces of data (or attributes) for each student, so we’d need four columns
  • Each column in a table must have a name and a datatype
    • The name is used to help identify what the attribute is
      • A column full of numbers is really difficult to use if you don’t know what those numbers represent
    • The datatype enforces the rules about what can be put into the column
      • Some common datatypes are: integer (whole numbers), varchar (strings) and date
      • This makes sure you don’t end up with someone writing their name in the age column, as the age column would only accept numbers

• With all this in mind, our table should look something like this:

Students

• Finally, we need the last piece of the table, the rows
  • A row in a table is used to represent a single entity of data
    • Each row must have a value for each column, and the values must conform to the column’s datatype
    • In our case, a row would represent a single student, and we would need three rows
    • Each row would be one student with their ID, name, age and grade
• After adding in our rows, our table now looks like:

Students

3. Examples of creating tables