1
00:00:05,240 --> 00:00:07,430
What is a relational database?

2
00:00:07,460 --> 00:00:13,370
Let's take a look at the battery code database in SSMs and I will explain it to you.

3
00:00:13,370 --> 00:00:18,260
The battery code database is known as a relational database, and it represents a company that sells

4
00:00:18,260 --> 00:00:18,920
batteries.

5
00:00:18,920 --> 00:00:21,710
In this company we have batteries to sell.

6
00:00:21,710 --> 00:00:25,820
And we keep track of this in a table called inventory.

7
00:00:25,820 --> 00:00:28,310
And here you can see the inventory that we have.

8
00:00:28,310 --> 00:00:34,850
Likewise in our company we have employees and we record these in the employees table.

9
00:00:34,850 --> 00:00:37,130
And they are the people that sell the batteries.

10
00:00:37,130 --> 00:00:42,410
So if we're going to sell batteries, we have to have customers and we keep track of these in the customers

11
00:00:42,410 --> 00:00:43,070
table.

12
00:00:43,070 --> 00:00:49,310
So here the customers that we do business with and when an employee sells batteries to a customer,

13
00:00:49,310 --> 00:00:52,580
then we keep track of these in our orders table.

14
00:00:52,580 --> 00:00:58,850
In order to illustrate how a relational database works, we can create a database diagram that shows

15
00:00:58,850 --> 00:01:04,790
the relationships between our customers, employees, inventory, and orders.

16
00:01:04,790 --> 00:01:07,610
So I'll right click and create a new database diagram.

17
00:01:07,610 --> 00:01:13,550
And I'm just going to add these tables holding down the control key so I can select them individually.

18
00:01:14,610 --> 00:01:16,410
And then I'll just click add.

19
00:01:18,260 --> 00:01:23,960
And a database diagram in SSMs just shows us the relationships between tables.

20
00:01:23,960 --> 00:01:26,210
And I can just kind of drag these around.

21
00:01:26,210 --> 00:01:29,720
So I have employees and I've got orders.

22
00:01:30,260 --> 00:01:35,000
We'll put customers over here and then just kind of pull up inventory.

23
00:01:35,300 --> 00:01:39,020
So each of these blocks are the tables that contain our data.

24
00:01:39,020 --> 00:01:44,330
And each table has a primary key indicated by the key icon.

25
00:01:44,330 --> 00:01:49,790
And you can see that there are lines between the tables that indicate our relationships.

26
00:01:49,790 --> 00:01:54,230
And you can hover over these lines to see what the relationships are.

27
00:01:54,230 --> 00:01:59,780
So here we have a relationship between employees and orders on the employee ID.

28
00:02:00,020 --> 00:02:05,030
And if you think about orders we need this link so that we know who actually made the order.

29
00:02:05,060 --> 00:02:08,210
Likewise we have customers that are creating orders.

30
00:02:08,210 --> 00:02:13,160
So we have a relationship between customers and orders on our customer ID key.

31
00:02:13,160 --> 00:02:16,310
And finally, with orders we need to know what is sold.

32
00:02:16,310 --> 00:02:20,930
So we have a relationship between the orders table and the inventory.

33
00:02:20,930 --> 00:02:25,940
These relationships are defined by the primary key in a source table.

34
00:02:25,940 --> 00:02:30,680
And then what we call a foreign key in the table that is requesting the data.

35
00:02:30,680 --> 00:02:34,640
And if you look at the relationship, it has an FK at the end.

36
00:02:34,640 --> 00:02:36,920
And that means it is a foreign key.

37
00:02:36,920 --> 00:02:44,930
So to sum up, our orders table has foreign keys for our customer, for the employee that made the sale,

38
00:02:44,930 --> 00:02:48,200
and for the stock ID of the item that was sold.

39
00:02:48,200 --> 00:02:53,720
If we go back and look at our orders table, it's really just a bunch of numbers and we can't really

40
00:02:53,720 --> 00:02:55,550
tell that much information.

41
00:02:55,550 --> 00:03:00,740
It would be great to know the name of the customer that made the order, the name of the employee that

42
00:03:00,740 --> 00:03:03,710
made the sale, and the name of the item that was sold.

43
00:03:03,710 --> 00:03:06,320
And this is where the relations come into play.

44
00:03:06,320 --> 00:03:09,620
If I look at the customer ID, it's 122.

45
00:03:09,620 --> 00:03:16,160
So if we go back and look at our customers table and find 122, we see the name of the customer is al

46
00:03:16,160 --> 00:03:16,580
dente.

47
00:03:17,360 --> 00:03:20,840
And if we look at the employee ID, which is 690.

48
00:03:20,870 --> 00:03:23,390
We can open the employees table.

49
00:03:24,680 --> 00:03:28,640
And we see that Kathleen Poitras is the one who made the cell.

50
00:03:28,640 --> 00:03:35,210
And looking at our stock ID for this order, it's 119 and looking at our inventory table.

51
00:03:35,930 --> 00:03:39,350
We can see that the two pack nine volt batteries was sold.

52
00:03:39,350 --> 00:03:45,800
But what if we wanted a report that showed all of our order information, but instead of just the customer

53
00:03:45,800 --> 00:03:51,140
ID, employee ID, and stock ID, we actually showed the name of the customer, the name of the employee

54
00:03:51,140 --> 00:03:54,440
that sold it, and the name of the product that was sold.

55
00:03:54,440 --> 00:03:56,960
That would be a lot more user friendly.

56
00:03:56,960 --> 00:04:03,170
But how do we take advantage of the relationships in our relational database to create our desired report?

57
00:04:03,170 --> 00:04:05,570
One quick way is to create a view.

58
00:04:05,600 --> 00:04:11,750
Views are really just stored SQL code that shows desired data from various related tables, like the

59
00:04:11,750 --> 00:04:13,640
orders report that we just discussed.

60
00:04:13,640 --> 00:04:17,090
Of course, we haven't covered SQL coding yet, but no worries.

61
00:04:17,090 --> 00:04:22,730
SSMs offers a user interface that allows users to visually create views without knowing how to write

62
00:04:22,730 --> 00:04:23,360
SQL.

63
00:04:23,360 --> 00:04:27,110
So let's create a view that represents our desired orders report.

64
00:04:27,110 --> 00:04:31,970
In order to demonstrate how relationships are used to generate the desired data set.

65
00:04:31,970 --> 00:04:35,390
Later in this course, we'll learn how to do the same using SQL code.

66
00:04:35,390 --> 00:04:38,540
So we'll just right click on views and select New View.

67
00:04:38,540 --> 00:04:42,200
And then let's add the tables that we want for our output.

68
00:04:42,200 --> 00:04:48,500
I'll hold my control key down and select customers employees inventories and orders.

69
00:04:49,070 --> 00:04:51,680
Then I'll click add and then close.

70
00:04:51,680 --> 00:04:54,920
So you can see our tables are represented up here.

71
00:04:54,920 --> 00:04:57,740
And we also have our relationships.

72
00:04:58,510 --> 00:05:00,700
I'll just make these a little larger.

73
00:05:00,850 --> 00:05:04,780
And these are showing all of the columns that are in each table.

74
00:05:04,780 --> 00:05:11,680
This area down here shows you the SQL code that is generated by our actions here in the user interface.

75
00:05:11,680 --> 00:05:16,060
So from our customers table we might want to know the first name and last name.

76
00:05:16,360 --> 00:05:19,960
And from employees we probably want the same thing for inventory.

77
00:05:19,960 --> 00:05:23,380
We would want the description of the item that was sold.

78
00:05:23,380 --> 00:05:27,970
And then for the orders, the order, ID, order, date and quantity.

79
00:05:28,810 --> 00:05:29,890
In this section.

80
00:05:29,890 --> 00:05:33,250
This shows the output columns and what their names are.

81
00:05:33,790 --> 00:05:40,060
Then we can just save this and call this our orders view and click okay.

82
00:05:41,250 --> 00:05:43,590
Then we're going to right click refresh.

83
00:05:43,620 --> 00:05:44,700
We'll see our view.

84
00:05:44,700 --> 00:05:49,140
And then we can right click and select top 1000 rows just like we do on a table.

85
00:05:49,140 --> 00:05:50,670
And here we have our output.

86
00:05:50,670 --> 00:05:53,700
Now these may not be in the order that we want.

87
00:05:53,700 --> 00:05:57,180
So we can go back to our view and rearrange these.

88
00:05:57,180 --> 00:06:03,690
So I would like the order ID to be first followed by the order date.

89
00:06:03,690 --> 00:06:06,510
And then I have my customer's first name and last name.

90
00:06:06,510 --> 00:06:09,570
And let's say I want the item sold to be next.

91
00:06:10,080 --> 00:06:11,340
And the quantity.

92
00:06:11,900 --> 00:06:14,390
And then the employee that sold it.

93
00:06:14,390 --> 00:06:21,590
So if we save this and then we'll close the previous view and then select again, you'll see that we

94
00:06:21,590 --> 00:06:23,900
have this in the order that we want.

95
00:06:23,930 --> 00:06:29,690
Now let's say that we wanted to add in the price of the item that they purchased.

96
00:06:29,750 --> 00:06:37,340
Then we can go back to our view and then go to our inventory table and add in our customer price.

97
00:06:37,640 --> 00:06:39,380
Then we could save this.

98
00:06:39,380 --> 00:06:41,900
And you see this table area down here.

99
00:06:41,900 --> 00:06:44,330
We can just right click and do execute SQL.

100
00:06:44,330 --> 00:06:47,000
And it will put the results down here.

101
00:06:47,000 --> 00:06:50,690
So we can see it without having to go back and execute it.

102
00:06:52,440 --> 00:06:59,010
So coming up, you'll have an assignment where you will create a database diagram of the battery code

103
00:06:59,010 --> 00:07:02,220
database and also create your own view.

104
00:07:02,220 --> 00:07:04,560
So I'll see you in the next assignment.