In this lesson, I’m going to give you a very short and simple explanation of what a relational database is and how it is organized from the logical standpoint. I want to make sure you understand concepts like table, column, row, and primary and foreign keys. These are very important concepts, and you really need to have them clear before starting to work with SQL.
If you already have them clear, then feel free to skip this lesson.
So, what is a relational database?
Here is a definition that is simple and easy to understand:
“A relational database is a collection of related information that has been organized into structures known as tables. Each table contains rows that are further organized into columns. These tables are stored in the database in structures known as schemas, which are areas where database users may store their tables. Each user may also choose to grant permissions to other users to access their tables.”
Okay, let’s dissect this definition to see exactly what it means:
“A relational database is a collection of related information that has been organized into structures known as tables…”
So, you can think of a relational database as something like the image below. Here the blue rectangle represents my database, in which I have my information stored into these 6 tables:
Very simple, right?
Let’s continue.
“Each table contains rows that are further organized into columns.”
Here I have my Products table:
As you can see I have 5 products stored in it. Product 1 is aspirin, product 2 is penicillin, and so on, and I have one row for every product. So, the row for aspirin includes all of the information I have stored in this table about Aspirin. That is, the product id, its name, price, and expiration date, and these smaller bits of information are “the columns”, so every product I store here can have an id, a name, a price, and an expiration date.
The number of columns is defined by the structure of the table. When you create a table, you decide what columns it will have. And the number of rows depends on the data you decide to store in it. An Empty table has 0 rows.
Columns are sometimes referred to as fields, and rows are sometimes referred to as records, but it is a little more common in other databases to call them that way. In Oracle a record is a type of composite variable that is used to store data in the PL/SQL language, so, to avoid confusion, try to stick to calling them rows and columns.
Okay, continuing with the database definition:
“These tables are stored in the database in structures known as schemas, which are areas where database users may store their tables. Each user may also choose to grant permissions to other users to access their tables.”
In the above example, I have two schemas: Sales and HR or human resources. The sales schema corresponds to the sales database user, and the HR schema to the HR database user. So, the sales user has the Products, Invoices and Invoice_Details tables in his schema, and HR has Employees, Departments, and Countries. And by default, the Sales user cannot access the tables in the HR schema, and HR cannot access the tables in the Sales schema, but they can grant access if they want to.
Take a look at this.
This is what is called a relational diagram.
These diagrams are very useful because they allow you to have a clear picture of how a database is organized. Each one of those boxes represents a table, and under the table name, there is the definition of the table’s columns, which in the case of the Products table are product_id, product_name, price, and expiration_date, as we saw before.
Primary Keys
The columns that have a P at the left margin of their box, are what is called “the primary key” of the table. And, the primary key is just a column or group of columns, that allows you to uniquely identify a row in the table.
So, in the case of the products table, the primary key is the product id, and what that means is that since aspirin has “1” as its product id, no other product can have 1 as product Id. If we try to store another product using product id 1, we will get an error, because since the product id is the primary key, we can only have one product with product id 1 in the whole table. It cannot have duplicates.
In the case of composite primary keys, or in other words, keys that include more than one column, what must be unique is the combination of the primary key columns. For example, in the invoice details table, I could have a row with Invoice number 1, and Product Id 1, and another row with Invoice number 1, but product Id 2. So, I can have as many rows as I want with the same invoice number, but any combination of invoice number and product Id can only appear once.
Okay, and to conclude this lesson, let’s briefly talk about foreign keys.
Foreign Keys
In this relational diagram, foreign keys are marked with an F, and they are also included at the bottom of the box.
Foreign keys are what create relationships between tables, and in simple words, they are used to help users store correct data in their tables, or keep data integrity.
This is just a quick introduction, because foreign keys are going to be covered in detail in another lesson.
So, as you see in the previous image, in the Invoice_Details, we have a foreign key for the product id, and one for the invoice number, and that’s why we see those arrows there, that represent the relationship between the tables.
What this means, is that in the Invoice_Details table, I can only store rows with a product Id that exists in the Products table, and I can only store rows with an invoice number that exists in the Invoice table. So, in this table, I have a product_Id column that references the product_Id column in the Products table, where it is the primary key, and I have an invoice_number column that references the invoice_number column of the Invoice table, where it is the primary key.
And as we saw with primary keys, foreign keys can also be composed of more than one column.
Alright, now that you know what a relational database is, and how it is organized, you are ready to move ahead and continue this learning adventure.
In the next lesson, you will start learning about what SQL is and the parts of it this course covers, and we are getting closer to the moment when you will start firing SQL commands!
Yes, we are getting there!