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.