1 00:00:04,880 --> 00:00:10,520 Now that we have our relational database designed, we can begin populating some of the tables using 2 00:00:10,520 --> 00:00:13,280 the SSMs interface as well as SQL commands. 3 00:00:13,280 --> 00:00:18,800 Looking at our tables, we can populate all of them except of course, the production defect tracking. 4 00:00:18,800 --> 00:00:26,150 We must populate our source tables first, which includes your roles, which defines the types of employees, 5 00:00:26,150 --> 00:00:33,650 station types, which defines the types of stations, and our defect category which defines the categories 6 00:00:33,650 --> 00:00:35,150 of our defects. 7 00:00:35,150 --> 00:00:41,300 This is because the other tables have relationships with the source tables, and they rely on these 8 00:00:41,300 --> 00:00:43,310 relationships in order to enter data. 9 00:00:43,310 --> 00:00:49,430 For instance, I couldn't enter a staff member without first having a role to assign to that staff member. 10 00:00:49,430 --> 00:00:52,880 So first let's populate our station types. 11 00:00:52,880 --> 00:00:56,990 We'll right click and do edit top 200 rows. 12 00:00:56,990 --> 00:01:02,330 Now of course there's no data in here yet, but SSMs gives you this interface where you can enter data 13 00:01:02,330 --> 00:01:02,990 directly. 14 00:01:02,990 --> 00:01:04,550 ID is our primary key. 15 00:01:04,550 --> 00:01:06,020 It's just an integer. 16 00:01:06,080 --> 00:01:07,790 I'll just type a one in there. 17 00:01:07,790 --> 00:01:12,590 And then for description, the first type of station that we have is operator. 18 00:01:12,590 --> 00:01:14,900 And these are the stations that are on the line. 19 00:01:14,900 --> 00:01:17,030 And I'll hit enter to enter the data. 20 00:01:17,030 --> 00:01:20,060 Next I'll just put two in the ID field. 21 00:01:20,060 --> 00:01:22,250 It has to be a unique entry. 22 00:01:22,250 --> 00:01:27,050 And the next type of station we have is our repair. 23 00:01:27,050 --> 00:01:33,740 Now wouldn't it be great if we could get our ID to automatically increment when a new description was 24 00:01:33,740 --> 00:01:34,280 added? 25 00:01:34,280 --> 00:01:35,870 Well, it turns out we can. 26 00:01:35,870 --> 00:01:39,710 I'll right click on station types and choose design. 27 00:01:39,830 --> 00:01:45,710 And on our ID field we're going to take a look at our properties and we'll go down to the Is identity. 28 00:01:45,710 --> 00:01:51,290 And identity just means that this field should auto increment every time a new record is added. 29 00:01:51,290 --> 00:01:52,820 So we definitely want that. 30 00:01:52,820 --> 00:01:56,090 I'll set that to yes, it increments by one each time. 31 00:01:56,090 --> 00:01:58,400 You could actually put different numbers in here. 32 00:01:58,400 --> 00:02:00,590 And then the seed is going to start at one. 33 00:02:00,590 --> 00:02:04,670 Now we already have data in this table because we just entered these. 34 00:02:04,670 --> 00:02:08,270 So when I save this we're going to get this warning message. 35 00:02:08,270 --> 00:02:10,940 Since we already have data in this table. 36 00:02:10,940 --> 00:02:17,870 What this warning means is that SQL server is going to have to delete and recreate this table in order 37 00:02:17,870 --> 00:02:22,010 to calculate all of our identity values for our primary key. 38 00:02:22,010 --> 00:02:26,900 Now, there are some tables that cannot be recreated, but this table is not one of them. 39 00:02:26,900 --> 00:02:33,800 There is an option in SQL server to prevent making changes to tables that already have data in them, 40 00:02:33,800 --> 00:02:35,360 just as a precaution. 41 00:02:35,360 --> 00:02:39,590 So what we have to do is we have to go turn that option off. 42 00:02:39,590 --> 00:02:41,930 So I'm just going to hit cancel here. 43 00:02:41,930 --> 00:02:45,500 And then you just go to tools Options. 44 00:02:45,500 --> 00:02:52,040 And there is this option here under designer table and database designers prevent saving changes that 45 00:02:52,040 --> 00:02:53,780 require table recreation. 46 00:02:53,780 --> 00:02:56,270 I'm going to turn this off and click okay. 47 00:02:56,270 --> 00:02:59,780 And then I can go back and save this table. 48 00:02:59,780 --> 00:03:02,330 And I'll just accept this. 49 00:03:02,330 --> 00:03:07,850 And now if I go back to my station types I can put in my third type. 50 00:03:07,850 --> 00:03:11,360 And I don't have to put in a value for ID. 51 00:03:11,360 --> 00:03:14,150 And when I hit enter, it doesn't show the update. 52 00:03:14,150 --> 00:03:20,690 But if I right click here and just do select top rows, you'll see that it did put the three in there, 53 00:03:20,690 --> 00:03:24,890 so we can use auto increment to automatically calculate our next ID. 54 00:03:24,890 --> 00:03:30,020 And that saves us a little bit of headache when we're entering stuff from another platform where we 55 00:03:30,020 --> 00:03:32,060 may not know what the highest ID is. 56 00:03:32,060 --> 00:03:35,330 So next we can enter our stations in. 57 00:03:35,330 --> 00:03:41,000 But before we do that, let's go take a look at our production floor to remember what stations we have. 58 00:03:41,530 --> 00:03:47,350 Looking at our production floor, we have four operator stations designated operator station one, two, 59 00:03:47,350 --> 00:03:53,050 three and four, and their descriptions are assembly, power up, operational test and packing. 60 00:03:53,050 --> 00:03:56,830 And these are the tasks that the operators perform at these stations. 61 00:03:56,830 --> 00:04:00,760 Likewise, we have three repair stations designated one, two and three. 62 00:04:00,760 --> 00:04:06,910 And their descriptions show the defect categories that they handle at those stations. 63 00:04:06,910 --> 00:04:10,540 So assembly mechanical, electrical and functional, cosmetic and packing. 64 00:04:10,540 --> 00:04:13,000 And also of course we have our supervisor station. 65 00:04:13,000 --> 00:04:13,810 There's only one. 66 00:04:13,810 --> 00:04:20,230 And its description is going to be the types of problems handled by supervisors, which is operational 67 00:04:20,230 --> 00:04:21,670 and supply chain. 68 00:04:21,670 --> 00:04:25,510 So let's go to the design mode of our stations table. 69 00:04:25,510 --> 00:04:29,050 And let's go ahead and make ID autoincrement. 70 00:04:29,320 --> 00:04:34,720 And so we'll just turn on identity specification is yes we'll leave this at default. 71 00:04:34,720 --> 00:04:41,520 But we need to add another field because we have a station designation like operator station one, 2 72 00:04:41,520 --> 00:04:43,510 or 3, repair station one, 2 or 3. 73 00:04:43,510 --> 00:04:46,000 And we also have a description. 74 00:04:46,000 --> 00:04:50,050 So we could use our station name for operator station one, two and three. 75 00:04:50,050 --> 00:04:52,660 And then we could just add another field. 76 00:04:52,660 --> 00:04:55,000 So I'll right click and insert a column. 77 00:04:55,000 --> 00:04:57,100 Let's just call this description. 78 00:04:57,100 --> 00:05:04,300 And we will make this a Varchar 50 as well and not allow Nulls and save. 79 00:05:04,300 --> 00:05:08,080 And this is a list of the tables that will be affected. 80 00:05:08,080 --> 00:05:10,030 And we can just say yes to this. 81 00:05:10,030 --> 00:05:11,770 Making changes like this. 82 00:05:11,770 --> 00:05:14,500 When you first start populating databases normal. 83 00:05:14,500 --> 00:05:19,060 Because you may not have thought about everything when you laid the database out. 84 00:05:19,060 --> 00:05:21,940 So this is a process known as refactoring. 85 00:05:21,940 --> 00:05:23,950 So now we have the table like we want it. 86 00:05:23,950 --> 00:05:28,690 We can just right click on stations and edit top 200 rows. 87 00:05:28,690 --> 00:05:31,930 And we have set our ID to be autoincrement. 88 00:05:31,930 --> 00:05:34,780 So I'm just going to pull these out so we can kind of see everything. 89 00:05:34,780 --> 00:05:38,560 And we'll just call this operator station number one. 90 00:05:38,560 --> 00:05:42,550 And then the description is going to be assembly. 91 00:05:42,550 --> 00:05:44,770 And then our station type ID. 92 00:05:44,770 --> 00:05:51,820 If we go back and look at our station types, we need to put a one in there because it is an operator 93 00:05:51,820 --> 00:05:52,450 station. 94 00:05:52,450 --> 00:05:56,590 So we will put a one in there and then just hit enter. 95 00:05:56,590 --> 00:06:03,010 Now for this next entry, why don't we take a look at how we can do a SQL command to do this instead 96 00:06:03,010 --> 00:06:04,270 of through the UI? 97 00:06:04,270 --> 00:06:07,030 And here's one of the things I really like about SQL server. 98 00:06:07,030 --> 00:06:13,090 On our stations table I can right click and there is an option called Script Table. 99 00:06:13,090 --> 00:06:18,370 And it will generate the necessary SQL code template to do whatever you want. 100 00:06:18,370 --> 00:06:21,970 You've got a SQL template to create this table. 101 00:06:21,970 --> 00:06:24,640 Delete select insert update. 102 00:06:24,640 --> 00:06:27,640 Well insert is what we really want to do. 103 00:06:27,640 --> 00:06:35,560 So if I highlight insert two, put it to a new Query Editor window, then it is going to create a template 104 00:06:35,560 --> 00:06:39,310 SQL script with an insert command of how I can insert data. 105 00:06:39,310 --> 00:06:42,250 And so this is a typical Insert command. 106 00:06:42,250 --> 00:06:44,620 If you remember this is data definition language. 107 00:06:44,620 --> 00:06:47,050 And so it says insert into stations. 108 00:06:47,050 --> 00:06:52,030 And then it's going to list the columns station name description and type ID. 109 00:06:52,180 --> 00:06:55,270 Notice that it did not list the ID. 110 00:06:55,270 --> 00:06:56,980 And that is because the ID. 111 00:06:56,980 --> 00:07:01,870 Now that it is an identity field, SQL knows that it's going to take care of that. 112 00:07:01,870 --> 00:07:07,390 So here under values, this is where we list the value for each column in order. 113 00:07:07,390 --> 00:07:12,970 And so it's telling us the name of the column and what the data type is. 114 00:07:12,970 --> 00:07:17,020 And then it puts these commas between each of these columns. 115 00:07:17,020 --> 00:07:20,530 So what I want to do is just replace this whole thing. 116 00:07:20,530 --> 00:07:23,890 And we're going to use single quotes to designate a string. 117 00:07:23,890 --> 00:07:25,600 Notice how everything turned red. 118 00:07:25,600 --> 00:07:31,600 Well our next station is going to be operator station number two. 119 00:07:31,600 --> 00:07:36,520 And then I'll put another single quote and then I'll highlight the next one. 120 00:07:36,520 --> 00:07:39,340 If we go back and look at our production floor, this is power up. 121 00:07:39,340 --> 00:07:42,460 So I can just say this is power up here. 122 00:07:42,460 --> 00:07:47,320 And then finally our station type ID and if you recall it is one. 123 00:07:48,210 --> 00:07:50,850 And so here I am just going to put a one. 124 00:07:50,850 --> 00:07:54,420 And you have to make sure that your close parenthesis is here. 125 00:07:54,420 --> 00:07:57,090 And I like having all this up on one line. 126 00:07:57,090 --> 00:08:00,120 And then I can just hit execute. 127 00:08:00,150 --> 00:08:02,910 It'll tell you if it was successful or not. 128 00:08:02,910 --> 00:08:08,850 If we go take a look at our stations, I can just right click select top 1000 rows and it will show 129 00:08:08,850 --> 00:08:10,200 my second station. 130 00:08:10,740 --> 00:08:13,260 So now I can just make changes here. 131 00:08:13,260 --> 00:08:19,890 Here is my station three and that is going to be operational test and hit execute. 132 00:08:20,440 --> 00:08:23,740 And we'll put in our last operator station here. 133 00:08:23,740 --> 00:08:28,030 And this is going to be packing, and I am just going to hit execute. 134 00:08:28,030 --> 00:08:32,290 And we can right click on stations, select all the rows. 135 00:08:32,290 --> 00:08:35,380 And we've entered all of our operator stations. 136 00:08:37,270 --> 00:08:42,310 Coming up is the next assignment where you'll add the data to our source tables. 137 00:08:42,730 --> 00:08:47,770 And you'll make all of our primary key ID fields to be Autoincrement. 138 00:08:47,770 --> 00:08:52,570 So when you're ready, go ahead and move on to the assignment and I'll see you there.