1
00:00:00,760 --> 00:00:07,840
In this video we're going to start designing our database to store the data coming from our farm.

2
00:00:07,840 --> 00:00:15,700
So far our database we will be using a school server Express and we will be designing at least two tables

3
00:00:15,700 --> 00:00:22,690
based on what we have here one to store the rental record and another one to store the type of car because

4
00:00:23,110 --> 00:00:28,120
well this is a drop done this and that this stuff cars is subject to change.

5
00:00:28,120 --> 00:00:31,900
Maybe one gets added one gets taken away you know.

6
00:00:31,910 --> 00:00:38,890
So we want to create a table where we're actually storing the list of cars available for rental.

7
00:00:39,640 --> 00:00:43,930
So a first step is to install a secure express addition.

8
00:00:44,230 --> 00:00:50,590
So if you already have a school server then you're fine but if you don't then you can proceed to go

9
00:00:50,590 --> 00:00:57,520
to Microsoft comes that I mean give you the link or I can just google Askew a server and then when you

10
00:00:57,520 --> 00:01:03,250
get to this site you just think downloads and the one that we're interested in is express so you can

11
00:01:03,250 --> 00:01:09,850
get developer you can get the Express expresses you know it's free and it's a smaller edition so it

12
00:01:09,850 --> 00:01:18,250
takes less to install less time and fewer resources but then developer is fully featured and it's you

13
00:01:18,250 --> 00:01:20,470
know it takes a longer time.

14
00:01:20,470 --> 00:01:26,920
So for this purpose I would encourage that you just get express you won't be losing much along the way

15
00:01:26,920 --> 00:01:32,470
anyway and then it's a straight forward installation step you can check all my youtube video on the

16
00:01:32,460 --> 00:01:37,900
installing it if you need some more guidance but you can go ahead and install it and the management

17
00:01:37,920 --> 00:01:43,810
studio and then I'm going to just pick up from the fact that the management studio has been installed

18
00:01:43,810 --> 00:01:50,200
the server husband installed and then we start designing our database now after installing a secure

19
00:01:50,200 --> 00:01:50,650
server.

20
00:01:50,650 --> 00:01:57,190
EXPRESS you'll be able to open the management studio and then you'll be able to log into your server

21
00:01:57,220 --> 00:02:04,660
so you'll see the name of your P.C. backslash and the name Eskew will express and once you see that

22
00:02:04,660 --> 00:02:11,260
you can just connect if you don't see that then you probably want to you can drop me a line and ask

23
00:02:11,260 --> 00:02:12,730
me for some guidance.

24
00:02:12,730 --> 00:02:18,400
Otherwise if you click on it and you're unable to connect then you probably want to go and do your services

25
00:02:19,000 --> 00:02:25,420
check for askew Express and make sure that that service is running and then you can try again either

26
00:02:25,420 --> 00:02:32,080
way feel free to reach out to me for further details though once you have connected to your server you

27
00:02:32,080 --> 00:02:33,760
can start building your database.

28
00:02:33,790 --> 00:02:36,060
So I'm going to just right click

29
00:02:38,690 --> 00:02:41,790
and click new database.

30
00:02:41,810 --> 00:02:49,110
This launches a dialog box I'm going to call my database car rental and then I click Okay.

31
00:02:49,340 --> 00:02:53,540
Once the database is created it will appear in the object explorer to your left.

32
00:02:53,540 --> 00:03:00,020
So I'm just going to drill down both the tables right click and trick new and I'm creating a new table

33
00:03:01,180 --> 00:03:05,050
so the first table that I'm creating like I said is car rental.

34
00:03:05,050 --> 00:03:10,870
So I'll see what the table is for the car into a red card.

35
00:03:10,900 --> 00:03:18,370
So the data that I intend to collect would be in keeping with what I would have designed my form to

36
00:03:18,400 --> 00:03:18,880
colleagues.

37
00:03:18,910 --> 00:03:27,150
So I want the customer name the cost the data into their data returned and the type of car are it's

38
00:03:27,160 --> 00:03:29,980
a type of car will be a foreign key.

39
00:03:30,010 --> 00:03:36,310
So at this stage I'm hoping everybody on the basic database design and why we have foreign keys and

40
00:03:36,310 --> 00:03:37,660
primary keys.

41
00:03:37,660 --> 00:03:42,130
If not then you can tickle my other course on database development.

42
00:03:42,130 --> 00:03:49,810
I have my Chris soft escudos server for everyone and I have my Eskew a data base development and mastery.

43
00:03:49,810 --> 00:03:55,090
So both those courses bring an end to end with database development and they explain why we have foreign

44
00:03:55,090 --> 00:03:57,200
keys and relationships and all those things.

45
00:03:57,220 --> 00:04:02,770
So I'm not going to spend this course going through those details I'm going to continue assuming that

46
00:04:02,770 --> 00:04:04,390
you have the basic information.

47
00:04:04,840 --> 00:04:14,610
So we need table to collect these fields and then we have another section for a foreign key and we remember

48
00:04:14,620 --> 00:04:21,800
we're creating another table just to keep the types of cars and that will inform the dropdown list here.

49
00:04:21,820 --> 00:04:29,640
So back in my school server management studio I went to go ahead and create the car Intel table first

50
00:04:29,650 --> 00:04:31,680
with an idea of type int.

51
00:04:31,930 --> 00:04:35,440
And it will be foreign key primary keys sorry.

52
00:04:35,440 --> 00:04:36,190
So right.

53
00:04:36,330 --> 00:04:39,060
Say said primary key.

54
00:04:39,160 --> 00:04:46,850
And I wanted to be auto incrementing so I got down to the identity selection and see is it easier.

55
00:04:46,870 --> 00:04:48,410
So I just looked at the No.

56
00:04:48,550 --> 00:04:49,680
And it becomes yes.

57
00:04:49,690 --> 00:04:51,870
And that means it will count automatically.

58
00:04:51,870 --> 00:04:52,830
Right.

59
00:04:52,990 --> 00:04:56,530
The other field I want is customer name.

60
00:04:56,560 --> 00:05:03,280
And this one can be in via char maybe a hundred characters long.

61
00:05:03,280 --> 00:05:13,750
Next I want the date for entered and Askew it actually has a date time data type also on dates returned

62
00:05:15,450 --> 00:05:19,120
and this also will be a beat time data type.

63
00:05:19,410 --> 00:05:21,220
I do believe I had costs.

64
00:05:21,220 --> 00:05:27,860
So on cost and this would be double so they do have a double.

65
00:05:27,880 --> 00:05:28,810
Do the double.

66
00:05:28,810 --> 00:05:34,030
Some of those some flawed stimulus verify that is a data type it is not.

67
00:05:34,030 --> 00:05:35,220
It's actually a decimal.

68
00:05:35,220 --> 00:05:36,010
Sorry about that.

69
00:05:36,400 --> 00:05:38,050
So in it for cost.

70
00:05:38,050 --> 00:05:47,170
We're using decimal and we can leave that default sitting there and then I'm going to see a car or type

71
00:05:47,170 --> 00:05:55,330
of car I.D. So type of car I.D. And that's integer we can leave it at that.

72
00:05:55,330 --> 00:05:57,250
Then we can click save.

73
00:05:57,250 --> 00:06:04,690
So we can save here or control us and then we're seeing this as a car and a red card.

74
00:06:04,690 --> 00:06:05,550
All right.

75
00:06:05,710 --> 00:06:08,620
So the table name is car into a record.

76
00:06:08,620 --> 00:06:14,530
And once again it will have all the basic details surrounding an actual car in tow.

77
00:06:14,650 --> 00:06:20,890
So if I walked into the place or into a car and they put me in that application the expectation is that

78
00:06:20,890 --> 00:06:27,490
when they fill this form with my information and the details of their info it will get saved in this

79
00:06:27,670 --> 00:06:28,450
table.

80
00:06:28,450 --> 00:06:28,890
All right.

81
00:06:29,230 --> 00:06:34,630
So the next thing that's I want to do is create another table and that is just for the list of cars.

82
00:06:34,660 --> 00:06:41,190
So I go back to object explorer or I click open a new table and then I want an I.D..

83
00:06:41,560 --> 00:06:49,510
And this is integer right click set its primary key and then make its identity scrolling through the

84
00:06:49,510 --> 00:06:55,400
properties go into identity selection dropping it though and is identity should be.

85
00:06:55,450 --> 00:06:56,130
Yes.

86
00:06:56,140 --> 00:06:57,160
All right.

87
00:06:57,220 --> 00:07:00,970
And then I want the name of the vehicle.

88
00:07:00,970 --> 00:07:02,810
So I just want I.D. and name.

89
00:07:02,920 --> 00:07:04,090
That's literally all I want.

90
00:07:04,090 --> 00:07:08,990
I want each car to have its own I.T. unique identifier and its name.

91
00:07:09,010 --> 00:07:09,750
Right.

92
00:07:09,790 --> 00:07:15,740
And I'm going to meet that that's an n var char of size 50.

93
00:07:15,970 --> 00:07:16,900
All right.

94
00:07:16,900 --> 00:07:23,220
And then I see of this table and I'm calling these types of cars.

95
00:07:23,290 --> 00:07:24,950
So that's all I want.

96
00:07:25,000 --> 00:07:29,950
The next thing I need to do is make a relationship between the I.D. or the primary key for the type

97
00:07:29,950 --> 00:07:37,150
of car and the the column that is supposed to be the foreign key for the type of car.

98
00:07:37,180 --> 00:07:42,220
So I just right click anywhere instead of the design view for either table really.

99
00:07:42,220 --> 00:07:43,660
And then I see relationships.

100
00:07:43,660 --> 00:07:44,670
Well actually no.

101
00:07:44,680 --> 00:07:45,700
Not for either two.

102
00:07:45,760 --> 00:07:53,590
I wanted for car into a record so I right click instead of car into the record and then I go to relationships

103
00:07:53,960 --> 00:08:01,990
click ad and then I drop down the tables and specifications when I need to drop it down I just don't

104
00:08:01,990 --> 00:08:03,910
need the ellipses really.

105
00:08:03,910 --> 00:08:10,030
So I just think it's this ellipses button and then it's going to see it whereas a Primary Key table

106
00:08:10,030 --> 00:08:17,770
the Primary Key is coming from the type of car table or the types of car stable and the column that

107
00:08:17,770 --> 00:08:19,890
is the primary key is I.

108
00:08:20,260 --> 00:08:27,140
And then the field in car and so that it matches to is type of car I.D..

109
00:08:27,160 --> 00:08:32,770
So I dropped bone and then I'm just making the link something that a primary key from the types of cars

110
00:08:32,780 --> 00:08:39,840
table should reflect in the type of car i.e. the column in the car in the record table.

111
00:08:39,880 --> 00:08:40,710
Right.

112
00:08:40,730 --> 00:08:42,150
And then click Okay.

113
00:08:42,240 --> 00:08:45,080
Click close and then save.

114
00:08:45,280 --> 00:08:49,290
And then all things being equal everything should be all right.

115
00:08:49,300 --> 00:08:52,600
If you get an error that point does make sure that they're both in.

116
00:08:52,600 --> 00:08:56,850
So here in the foreign key table or in the car into a record table.

117
00:08:56,860 --> 00:08:57,970
This should be in it.

118
00:08:58,180 --> 00:09:04,590
And the primary keys should also be if those steps are foreign to you or you're not familiar with them

119
00:09:04,600 --> 00:09:07,480
once again I encourage you to just go through my course.

120
00:09:07,480 --> 00:09:14,710
My school developed the database development and mastery or Microsoft school serve for everyone.

121
00:09:14,710 --> 00:09:20,080
Those two courses will get you up to speed with everything you need to know about database design.

122
00:09:20,080 --> 00:09:20,750
So far no.

123
00:09:20,760 --> 00:09:24,390
That's it for designing our database that we are.

124
00:09:24,810 --> 00:09:31,900
You know we developed to a company or wind farms application in the next video we will look at how we

125
00:09:31,900 --> 00:09:35,530
actually link this application to the database.
