Our first step is going to be to create the production database.
So we'll go ahead and open SSMs.
We'll connect to our database server and then on databases we'll just right click and choose New Database.
We're going to call this database production.
And we're going to leave everything at default.
And just click okay.
So now we have a new database.
The next step is going to be to create some tables.
But first let's take a look at our requirements.
When designing our tables and their relationships, the first question we need to ask is what is the
end goal of this application and what does it need to look like?
The answer, of course, is the production defect report itself.
It is the output of our application.
It's the reason we're building this.
We need to ensure that the tables in our database contain the information that we need to analyze and
correct causes of production defects.
Defining this table first can guide us in designing our supporting tables and relationships.
Looking back at our process, we have stations along our manufacturing line that do certain parts of
the process.
And at these stations we have operators.
So it would be good to know which operator found the defect and what type of defect that it is.
We also have repair personnel, and they are seated at three different types of stations, each of which
handles different types of defects.
And then we have a supervisor that is identifying operational and supply chain issues.
So it stands to reason that we need a table for our stations and a table for our staff.
Since we're assigning defect categories and defect reasons, we would need a table for each of these
as well.
So for our defect tracking table design, we want to know the time that the defect occurred, what operator
recorded it at what station.
They will also select a defect category and a defect reason and then also add their comments.
Then for the repair bench, we need to know what repair person created the repair at what station,
and also their notes.
If a supervisor was involved in identifying the cause of the problem, we would also need to know who
the supervisor was and their notes and the final root cause of the defect.
From this design, we can surmise that we need entity tables for staff stations, defect categories,
and defect reasons.
To begin analyzing our defect tracking table design, let's first focus on the staff.
Our first column is operator, which is part of our staff, where it may seem like common sense to create
a table for each type of staff operator, repairmen and supervisors.
But if you think about it, people can change roles.
Either operator or repairman can be promoted to become supervisors, and people come and go.
So a better design.
Taking advantage of the nature of relational databases, we could just have one table that has all of
our staff and then it could relate to a roles table that has the different roles that people play operator,
repairman, and supervisor, and just create a relationship between our staff table and our roles table
that denotes the role that that person is playing.
This is going to result in a more efficient and flexible database design.
So let's open SSMs backup and create our Staff and Roles table in SSMs.
Under our production database we're going to highlight tables and then right click and choose New Table.
And this is the table design interface.
And we're certainly going to want to have a primary key.
This is a relational database.
And we can just call this ID.
And under data type we just want this to be some type of number.
I typically use integers if it's just going to be kind of a small set of data.
So we'll do data type of integer.
And then we are going to make this the primary key.
So I'll right click and choose set primary key for the next field.
This is going to be the role description.
So we could just call this description.
We're going to be putting in a string that describes the type of job.
So we want to make sure that we have something that is variable length.
So we could either use varchar or Nvarchar.
The in varchar is Unicode.
And we have to assign a length to it.
So we can just use in Varchar 50.
Which means we can create a Unicode string up to 50 characters in length.
That sounds like it'll be good.
We also don't want this to be null.
Then we can just hit save.
And it's going to ask us for a name.
And we can just call this roles and just click okay.
Now if we right click and do refresh, we can see that we have our new roles table here.
Now let's create our staff table.
We'll right click Choose New Table.
And we're going to do an ID as well.
I tend to make that uppercase.
And we're going to make this an integer.
So we can just put in int.
We're not going to allow nulls.
And we're going to make this a primary key.
Now we want to have the last name and first name.
So we'll create those columns next.
And I tend to just use underscores for this.
As far as data types we're also going to use a variable string length.
And I'll just go with Nvarchar 50 I don't know many people that have last names longer than 50.
We're not going to allow a null for a last name.
And then we will also create a first name.
And we will also make this an nvarchar.
Now we need to make our relationship back to our roles.
And so this field needs to be the same data type as the key field back in the roles table that we're
going to make a relationship with.
So our next column, let's just call it roll underscore ID.
And then we're going to make our data type an int and uncheck allow nulls.
Now in order to create the relationship we're just going to right click on the column and do relationships.
We'll select Add Relationship.
And we're going to give it a name.
We will call this.
Role to staff and the FK is going to be our foreign key.
And then we're going to expand the tables and columns.
And we'll hit the button with the three dots on it.
And our primary key table that's going to be our relationship is going to go back to roles.
And then our foreign key of course is going to be table one.
We haven't given this an actual name yet.
So we'll say that the ID column in roles is going to be a foreign key in table one.
Role ID and that's going to create the relationship.
We'll click okay and close.
And then we're going to save this.
And we'll name this table staff.
And click okay.
And then we will say yes to the message, and then we will refresh again and we should see both of our
tables.
Now let's take a look at our database diagrams.
I'm going to right click and do new database diagram.
Now this is a very common error inside SQL Server Management Studio.
It's been around for a while.
And when you see this basically what it means is the schema of the database has so many changes it doesn't
match the cached version.
Then what you want to do is we're just going to restart Management Studio and we'll reconnect, and
then we'll go back down to production and we'll do new database diagram and the problem goes away.
So that's a free tip for you.
I've seen that error a few times here and there, especially when I'm making a lot of changes.
So now since I've added a database diagram we only have two tables.
And when I add these and close this, you can see that we have successfully created this relationship.
And we hover over this.
It shows exactly the relationship between the primary key and roles and the foreign key in staff that
gives the role ID.
Coming up next is an assignment where you will put what you've learned to use.
You'll create the remaining entity tables for our application as well as define the defect tracking
table.
So when you're ready move on to the next assignment and we'll get started.