What is a relational database?
Let's take a look at the battery code database in SSMs and I will explain it to you.
The battery code database is known as a relational database, and it represents a company that sells
batteries.
In this company we have batteries to sell.
And we keep track of this in a table called inventory.
And here you can see the inventory that we have.
Likewise in our company we have employees and we record these in the employees table.
And they are the people that sell the batteries.
So if we're going to sell batteries, we have to have customers and we keep track of these in the customers
table.
So here the customers that we do business with and when an employee sells batteries to a customer,
then we keep track of these in our orders table.
In order to illustrate how a relational database works, we can create a database diagram that shows
the relationships between our customers, employees, inventory, and orders.
So I'll right click and create a new database diagram.
And I'm just going to add these tables holding down the control key so I can select them individually.
And then I'll just click add.
And a database diagram in SSMs just shows us the relationships between tables.
And I can just kind of drag these around.
So I have employees and I've got orders.
We'll put customers over here and then just kind of pull up inventory.
So each of these blocks are the tables that contain our data.
And each table has a primary key indicated by the key icon.
And you can see that there are lines between the tables that indicate our relationships.
And you can hover over these lines to see what the relationships are.
So here we have a relationship between employees and orders on the employee ID.
And if you think about orders we need this link so that we know who actually made the order.
Likewise we have customers that are creating orders.
So we have a relationship between customers and orders on our customer ID key.
And finally, with orders we need to know what is sold.
So we have a relationship between the orders table and the inventory.
These relationships are defined by the primary key in a source table.
And then what we call a foreign key in the table that is requesting the data.
And if you look at the relationship, it has an FK at the end.
And that means it is a foreign key.
So to sum up, our orders table has foreign keys for our customer, for the employee that made the sale,
and for the stock ID of the item that was sold.
If we go back and look at our orders table, it's really just a bunch of numbers and we can't really
tell that much information.
It would be great to know the name of the customer that made the order, the name of the employee that
made the sale, and the name of the item that was sold.
And this is where the relations come into play.
If I look at the customer ID, it's 122.
So if we go back and look at our customers table and find 122, we see the name of the customer is al
dente.
And if we look at the employee ID, which is 690.
We can open the employees table.
And we see that Kathleen Poitras is the one who made the cell.
And looking at our stock ID for this order, it's 119 and looking at our inventory table.
We can see that the two pack nine volt batteries was sold.
But what if we wanted a report that showed all of our order information, but instead of just the customer
ID, employee ID, and stock ID, we actually showed the name of the customer, the name of the employee
that sold it, and the name of the product that was sold.
That would be a lot more user friendly.
But how do we take advantage of the relationships in our relational database to create our desired report?
One quick way is to create a view.
Views are really just stored SQL code that shows desired data from various related tables, like the
orders report that we just discussed.
Of course, we haven't covered SQL coding yet, but no worries.
SSMs offers a user interface that allows users to visually create views without knowing how to write
SQL.
So let's create a view that represents our desired orders report.
In order to demonstrate how relationships are used to generate the desired data set.
Later in this course, we'll learn how to do the same using SQL code.
So we'll just right click on views and select New View.
And then let's add the tables that we want for our output.
I'll hold my control key down and select customers employees inventories and orders.
Then I'll click add and then close.
So you can see our tables are represented up here.
And we also have our relationships.
I'll just make these a little larger.
And these are showing all of the columns that are in each table.
This area down here shows you the SQL code that is generated by our actions here in the user interface.
So from our customers table we might want to know the first name and last name.
And from employees we probably want the same thing for inventory.
We would want the description of the item that was sold.
And then for the orders, the order, ID, order, date and quantity.
In this section.
This shows the output columns and what their names are.
Then we can just save this and call this our orders view and click okay.
Then we're going to right click refresh.
We'll see our view.
And then we can right click and select top 1000 rows just like we do on a table.
And here we have our output.
Now these may not be in the order that we want.
So we can go back to our view and rearrange these.
So I would like the order ID to be first followed by the order date.
And then I have my customer's first name and last name.
And let's say I want the item sold to be next.
And the quantity.
And then the employee that sold it.
So if we save this and then we'll close the previous view and then select again, you'll see that we
have this in the order that we want.
Now let's say that we wanted to add in the price of the item that they purchased.
Then we can go back to our view and then go to our inventory table and add in our customer price.
Then we could save this.
And you see this table area down here.
We can just right click and do execute SQL.
And it will put the results down here.
So we can see it without having to go back and execute it.
So coming up, you'll have an assignment where you will create a database diagram of the battery code
database and also create your own view.
So I'll see you in the next assignment.