Now that we have our relational database designed, we can begin populating some of the tables using
the SSMs interface as well as SQL commands.
Looking at our tables, we can populate all of them except of course, the production defect tracking.
We must populate our source tables first, which includes your roles, which defines the types of employees,
station types, which defines the types of stations, and our defect category which defines the categories
of our defects.
This is because the other tables have relationships with the source tables, and they rely on these
relationships in order to enter data.
For instance, I couldn't enter a staff member without first having a role to assign to that staff member.
So first let's populate our station types.
We'll right click and do edit top 200 rows.
Now of course there's no data in here yet, but SSMs gives you this interface where you can enter data
directly.
ID is our primary key.
It's just an integer.
I'll just type a one in there.
And then for description, the first type of station that we have is operator.
And these are the stations that are on the line.
And I'll hit enter to enter the data.
Next I'll just put two in the ID field.
It has to be a unique entry.
And the next type of station we have is our repair.
Now wouldn't it be great if we could get our ID to automatically increment when a new description was
added?
Well, it turns out we can.
I'll right click on station types and choose design.
And on our ID field we're going to take a look at our properties and we'll go down to the Is identity.
And identity just means that this field should auto increment every time a new record is added.
So we definitely want that.
I'll set that to yes, it increments by one each time.
You could actually put different numbers in here.
And then the seed is going to start at one.
Now we already have data in this table because we just entered these.
So when I save this we're going to get this warning message.
Since we already have data in this table.
What this warning means is that SQL server is going to have to delete and recreate this table in order
to calculate all of our identity values for our primary key.
Now, there are some tables that cannot be recreated, but this table is not one of them.
There is an option in SQL server to prevent making changes to tables that already have data in them,
just as a precaution.
So what we have to do is we have to go turn that option off.
So I'm just going to hit cancel here.
And then you just go to tools Options.
And there is this option here under designer table and database designers prevent saving changes that
require table recreation.
I'm going to turn this off and click okay.
And then I can go back and save this table.
And I'll just accept this.
And now if I go back to my station types I can put in my third type.
And I don't have to put in a value for ID.
And when I hit enter, it doesn't show the update.
But if I right click here and just do select top rows, you'll see that it did put the three in there,
so we can use auto increment to automatically calculate our next ID.
And that saves us a little bit of headache when we're entering stuff from another platform where we
may not know what the highest ID is.
So next we can enter our stations in.
But before we do that, let's go take a look at our production floor to remember what stations we have.
Looking at our production floor, we have four operator stations designated operator station one, two,
three and four, and their descriptions are assembly, power up, operational test and packing.
And these are the tasks that the operators perform at these stations.
Likewise, we have three repair stations designated one, two and three.
And their descriptions show the defect categories that they handle at those stations.
So assembly mechanical, electrical and functional, cosmetic and packing.
And also of course we have our supervisor station.
There's only one.
And its description is going to be the types of problems handled by supervisors, which is operational
and supply chain.
So let's go to the design mode of our stations table.
And let's go ahead and make ID autoincrement.
And so we'll just turn on identity specification is yes we'll leave this at default.
But we need to add another field because we have a station designation like operator station one, 2
or 3, repair station one, 2 or 3.
And we also have a description.
So we could use our station name for operator station one, two and three.
And then we could just add another field.
So I'll right click and insert a column.
Let's just call this description.
And we will make this a Varchar 50 as well and not allow Nulls and save.
And this is a list of the tables that will be affected.
And we can just say yes to this.
Making changes like this.
When you first start populating databases normal.
Because you may not have thought about everything when you laid the database out.
So this is a process known as refactoring.
So now we have the table like we want it.
We can just right click on stations and edit top 200 rows.
And we have set our ID to be autoincrement.
So I'm just going to pull these out so we can kind of see everything.
And we'll just call this operator station number one.
And then the description is going to be assembly.
And then our station type ID.
If we go back and look at our station types, we need to put a one in there because it is an operator
station.
So we will put a one in there and then just hit enter.
Now for this next entry, why don't we take a look at how we can do a SQL command to do this instead
of through the UI?
And here's one of the things I really like about SQL server.
On our stations table I can right click and there is an option called Script Table.
And it will generate the necessary SQL code template to do whatever you want.
You've got a SQL template to create this table.
Delete select insert update.
Well insert is what we really want to do.
So if I highlight insert two, put it to a new Query Editor window, then it is going to create a template
SQL script with an insert command of how I can insert data.
And so this is a typical Insert command.
If you remember this is data definition language.
And so it says insert into stations.
And then it's going to list the columns station name description and type ID.
Notice that it did not list the ID.
And that is because the ID.
Now that it is an identity field, SQL knows that it's going to take care of that.
So here under values, this is where we list the value for each column in order.
And so it's telling us the name of the column and what the data type is.
And then it puts these commas between each of these columns.
So what I want to do is just replace this whole thing.
And we're going to use single quotes to designate a string.
Notice how everything turned red.
Well our next station is going to be operator station number two.
And then I'll put another single quote and then I'll highlight the next one.
If we go back and look at our production floor, this is power up.
So I can just say this is power up here.
And then finally our station type ID and if you recall it is one.
And so here I am just going to put a one.
And you have to make sure that your close parenthesis is here.
And I like having all this up on one line.
And then I can just hit execute.
It'll tell you if it was successful or not.
If we go take a look at our stations, I can just right click select top 1000 rows and it will show
my second station.
So now I can just make changes here.
Here is my station three and that is going to be operational test and hit execute.
And we'll put in our last operator station here.
And this is going to be packing, and I am just going to hit execute.
And we can right click on stations, select all the rows.
And we've entered all of our operator stations.
Coming up is the next assignment where you'll add the data to our source tables.
And you'll make all of our primary key ID fields to be Autoincrement.
So when you're ready, go ahead and move on to the assignment and I'll see you there.