1
00:00:05,540 --> 00:00:09,170
Our first step is going to be to create the production database.

2
00:00:09,170 --> 00:00:12,260
So we'll go ahead and open SSMs.

3
00:00:12,680 --> 00:00:18,860
We'll connect to our database server and then on databases we'll just right click and choose New Database.

4
00:00:18,890 --> 00:00:23,060
We're going to call this database production.

5
00:00:23,060 --> 00:00:25,400
And we're going to leave everything at default.

6
00:00:25,400 --> 00:00:26,900
And just click okay.

7
00:00:27,450 --> 00:00:29,580
So now we have a new database.

8
00:00:29,580 --> 00:00:33,630
The next step is going to be to create some tables.

9
00:00:33,630 --> 00:00:36,390
But first let's take a look at our requirements.

10
00:00:37,090 --> 00:00:44,050
When designing our tables and their relationships, the first question we need to ask is what is the

11
00:00:44,050 --> 00:00:48,250
end goal of this application and what does it need to look like?

12
00:00:48,280 --> 00:00:52,870
The answer, of course, is the production defect report itself.

13
00:00:52,870 --> 00:00:54,970
It is the output of our application.

14
00:00:54,970 --> 00:00:57,190
It's the reason we're building this.

15
00:00:57,670 --> 00:01:04,750
We need to ensure that the tables in our database contain the information that we need to analyze and

16
00:01:04,750 --> 00:01:07,090
correct causes of production defects.

17
00:01:07,120 --> 00:01:13,840
Defining this table first can guide us in designing our supporting tables and relationships.

18
00:01:14,610 --> 00:01:21,810
Looking back at our process, we have stations along our manufacturing line that do certain parts of

19
00:01:21,810 --> 00:01:22,830
the process.

20
00:01:22,830 --> 00:01:25,530
And at these stations we have operators.

21
00:01:25,530 --> 00:01:32,070
So it would be good to know which operator found the defect and what type of defect that it is.

22
00:01:32,100 --> 00:01:38,070
We also have repair personnel, and they are seated at three different types of stations, each of which

23
00:01:38,070 --> 00:01:40,620
handles different types of defects.

24
00:01:40,620 --> 00:01:45,510
And then we have a supervisor that is identifying operational and supply chain issues.

25
00:01:45,510 --> 00:01:51,810
So it stands to reason that we need a table for our stations and a table for our staff.

26
00:01:51,810 --> 00:01:57,390
Since we're assigning defect categories and defect reasons, we would need a table for each of these

27
00:01:57,390 --> 00:01:58,080
as well.

28
00:01:58,080 --> 00:02:04,530
So for our defect tracking table design, we want to know the time that the defect occurred, what operator

29
00:02:04,530 --> 00:02:06,540
recorded it at what station.

30
00:02:06,540 --> 00:02:12,240
They will also select a defect category and a defect reason and then also add their comments.

31
00:02:12,240 --> 00:02:18,990
Then for the repair bench, we need to know what repair person created the repair at what station,

32
00:02:18,990 --> 00:02:20,610
and also their notes.

33
00:02:20,610 --> 00:02:26,250
If a supervisor was involved in identifying the cause of the problem, we would also need to know who

34
00:02:26,280 --> 00:02:30,690
the supervisor was and their notes and the final root cause of the defect.

35
00:02:30,690 --> 00:02:38,790
From this design, we can surmise that we need entity tables for staff stations, defect categories,

36
00:02:38,790 --> 00:02:40,410
and defect reasons.

37
00:02:40,410 --> 00:02:46,170
To begin analyzing our defect tracking table design, let's first focus on the staff.

38
00:02:46,170 --> 00:02:52,350
Our first column is operator, which is part of our staff, where it may seem like common sense to create

39
00:02:52,350 --> 00:02:56,880
a table for each type of staff operator, repairmen and supervisors.

40
00:02:56,880 --> 00:02:59,430
But if you think about it, people can change roles.

41
00:02:59,430 --> 00:03:05,070
Either operator or repairman can be promoted to become supervisors, and people come and go.

42
00:03:05,070 --> 00:03:06,420
So a better design.

43
00:03:06,420 --> 00:03:12,840
Taking advantage of the nature of relational databases, we could just have one table that has all of

44
00:03:12,840 --> 00:03:19,590
our staff and then it could relate to a roles table that has the different roles that people play operator,

45
00:03:19,590 --> 00:03:25,560
repairman, and supervisor, and just create a relationship between our staff table and our roles table

46
00:03:25,560 --> 00:03:28,140
that denotes the role that that person is playing.

47
00:03:28,140 --> 00:03:32,670
This is going to result in a more efficient and flexible database design.

48
00:03:32,670 --> 00:03:40,050
So let's open SSMs backup and create our Staff and Roles table in SSMs.

49
00:03:40,050 --> 00:03:46,620
Under our production database we're going to highlight tables and then right click and choose New Table.

50
00:03:47,690 --> 00:03:50,810
And this is the table design interface.

51
00:03:50,810 --> 00:03:54,050
And we're certainly going to want to have a primary key.

52
00:03:54,050 --> 00:03:55,940
This is a relational database.

53
00:03:55,940 --> 00:03:58,190
And we can just call this ID.

54
00:03:58,850 --> 00:04:02,330
And under data type we just want this to be some type of number.

55
00:04:02,330 --> 00:04:07,970
I typically use integers if it's just going to be kind of a small set of data.

56
00:04:08,210 --> 00:04:10,580
So we'll do data type of integer.

57
00:04:10,850 --> 00:04:13,970
And then we are going to make this the primary key.

58
00:04:13,970 --> 00:04:18,140
So I'll right click and choose set primary key for the next field.

59
00:04:18,140 --> 00:04:20,360
This is going to be the role description.

60
00:04:20,360 --> 00:04:23,540
So we could just call this description.

61
00:04:23,540 --> 00:04:28,670
We're going to be putting in a string that describes the type of job.

62
00:04:28,670 --> 00:04:32,570
So we want to make sure that we have something that is variable length.

63
00:04:32,570 --> 00:04:35,570
So we could either use varchar or Nvarchar.

64
00:04:35,570 --> 00:04:37,280
The in varchar is Unicode.

65
00:04:37,280 --> 00:04:39,860
And we have to assign a length to it.

66
00:04:39,860 --> 00:04:42,290
So we can just use in Varchar 50.

67
00:04:42,290 --> 00:04:47,570
Which means we can create a Unicode string up to 50 characters in length.

68
00:04:47,570 --> 00:04:49,280
That sounds like it'll be good.

69
00:04:49,280 --> 00:04:51,620
We also don't want this to be null.

70
00:04:51,620 --> 00:04:53,390
Then we can just hit save.

71
00:04:53,390 --> 00:04:55,460
And it's going to ask us for a name.

72
00:04:55,460 --> 00:04:59,390
And we can just call this roles and just click okay.

73
00:04:59,900 --> 00:05:05,990
Now if we right click and do refresh, we can see that we have our new roles table here.

74
00:05:06,500 --> 00:05:08,390
Now let's create our staff table.

75
00:05:08,390 --> 00:05:10,400
We'll right click Choose New Table.

76
00:05:10,400 --> 00:05:13,580
And we're going to do an ID as well.

77
00:05:13,580 --> 00:05:15,440
I tend to make that uppercase.

78
00:05:15,440 --> 00:05:18,740
And we're going to make this an integer.

79
00:05:18,740 --> 00:05:21,020
So we can just put in int.

80
00:05:21,020 --> 00:05:23,120
We're not going to allow nulls.

81
00:05:23,120 --> 00:05:25,280
And we're going to make this a primary key.

82
00:05:25,280 --> 00:05:28,340
Now we want to have the last name and first name.

83
00:05:28,340 --> 00:05:30,830
So we'll create those columns next.

84
00:05:30,830 --> 00:05:34,490
And I tend to just use underscores for this.

85
00:05:34,490 --> 00:05:39,170
As far as data types we're also going to use a variable string length.

86
00:05:39,170 --> 00:05:45,170
And I'll just go with Nvarchar 50 I don't know many people that have last names longer than 50.

87
00:05:45,170 --> 00:05:48,590
We're not going to allow a null for a last name.

88
00:05:48,590 --> 00:05:52,010
And then we will also create a first name.

89
00:05:54,050 --> 00:05:57,140
And we will also make this an nvarchar.

90
00:05:57,560 --> 00:06:02,450
Now we need to make our relationship back to our roles.

91
00:06:02,450 --> 00:06:08,870
And so this field needs to be the same data type as the key field back in the roles table that we're

92
00:06:08,870 --> 00:06:10,640
going to make a relationship with.

93
00:06:11,250 --> 00:06:17,160
So our next column, let's just call it roll underscore ID.

94
00:06:17,160 --> 00:06:22,320
And then we're going to make our data type an int and uncheck allow nulls.

95
00:06:22,350 --> 00:06:29,700
Now in order to create the relationship we're just going to right click on the column and do relationships.

96
00:06:29,970 --> 00:06:32,070
We'll select Add Relationship.

97
00:06:32,560 --> 00:06:34,150
And we're going to give it a name.

98
00:06:34,150 --> 00:06:35,740
We will call this.

99
00:06:37,110 --> 00:06:43,530
Role to staff and the FK is going to be our foreign key.

100
00:06:44,290 --> 00:06:48,100
And then we're going to expand the tables and columns.

101
00:06:48,770 --> 00:06:52,190
And we'll hit the button with the three dots on it.

102
00:06:52,190 --> 00:06:57,770
And our primary key table that's going to be our relationship is going to go back to roles.

103
00:06:57,860 --> 00:07:01,160
And then our foreign key of course is going to be table one.

104
00:07:01,160 --> 00:07:04,370
We haven't given this an actual name yet.

105
00:07:04,370 --> 00:07:12,380
So we'll say that the ID column in roles is going to be a foreign key in table one.

106
00:07:13,200 --> 00:07:16,500
Role ID and that's going to create the relationship.

107
00:07:16,500 --> 00:07:19,200
We'll click okay and close.

108
00:07:19,200 --> 00:07:21,840
And then we're going to save this.

109
00:07:21,840 --> 00:07:24,720
And we'll name this table staff.

110
00:07:26,020 --> 00:07:27,010
And click okay.

111
00:07:27,010 --> 00:07:35,080
And then we will say yes to the message, and then we will refresh again and we should see both of our

112
00:07:35,080 --> 00:07:35,920
tables.

113
00:07:36,310 --> 00:07:39,550
Now let's take a look at our database diagrams.

114
00:07:39,550 --> 00:07:42,940
I'm going to right click and do new database diagram.

115
00:07:42,940 --> 00:07:47,830
Now this is a very common error inside SQL Server Management Studio.

116
00:07:47,830 --> 00:07:49,300
It's been around for a while.

117
00:07:49,300 --> 00:07:56,440
And when you see this basically what it means is the schema of the database has so many changes it doesn't

118
00:07:56,440 --> 00:07:58,180
match the cached version.

119
00:07:58,180 --> 00:08:05,380
Then what you want to do is we're just going to restart Management Studio and we'll reconnect, and

120
00:08:05,380 --> 00:08:11,650
then we'll go back down to production and we'll do new database diagram and the problem goes away.

121
00:08:11,650 --> 00:08:13,510
So that's a free tip for you.

122
00:08:13,510 --> 00:08:17,860
I've seen that error a few times here and there, especially when I'm making a lot of changes.

123
00:08:17,860 --> 00:08:24,400
So now since I've added a database diagram we only have two tables.

124
00:08:24,400 --> 00:08:31,360
And when I add these and close this, you can see that we have successfully created this relationship.

125
00:08:31,360 --> 00:08:32,860
And we hover over this.

126
00:08:32,890 --> 00:08:39,820
It shows exactly the relationship between the primary key and roles and the foreign key in staff that

127
00:08:39,820 --> 00:08:41,170
gives the role ID.

128
00:08:43,850 --> 00:08:48,170
Coming up next is an assignment where you will put what you've learned to use.

129
00:08:48,200 --> 00:08:53,930
You'll create the remaining entity tables for our application as well as define the defect tracking

130
00:08:53,960 --> 00:08:54,560
table.

131
00:08:54,560 --> 00:08:58,430
So when you're ready move on to the next assignment and we'll get started.