Hi guys this is Mina and today's video is going to be a continuation of our
dimensional modeling project. Today we are going to design a
physical model of our star schema in sql server
management studio specifically within the user interface
if you have not watched my previous video I highly
recommend that you go back and follow this link and watch
it because we designed the conceptual and
logical dimensional models based on a gold exploration case study
and we did that in microsoft excel. It's going to help us to design our
physical model and i highly recommend that you watch
this previous video. So just a quick recap of what we did in my previous
video, we designed a conceptual model star
schema and here we identified all the
dimensions and the fact table and we use shapes
to indicate this star schema where we have the fact table in the center
surrounded by multiple dimensions
we then progressed to the logical model where we
basically took our conceptual model and listed all the attributes for
the dimensions and the fact table and then we showed the
primary and foreign key relationships. So as you can see here, these are our
dimensions and they are surrounding the fact table
which is in the center and each dimension has a primary key
that primary key is listed within the fact table as a foreign key
and that is the relationship between the dimension tables
and the fact table each dimension also has
its own attributes that we listed. So today we are going to translate this
logical model into a physical model within sql server
management studio. A physical dimensional model
shows the physical table structure and the database model being built in a
database management software and here we'll be using sql
server management studio. To follow along in
this video you need to have sql server management
studio already installed on your machine, if not there
are several articles on the web to help you install one.
Our physical model will contain all our tables and columns
it will show the data types of each column
and the primary as well as the foreign keys that are being used to identify the
relationships between the table. Kkay so I'm going to
launch my sql server management studio. This is the sql server management
studio interface and to begin with we need to
create a database so we're going to right click on
databases and click on new database and give it a name i'm going to call it
star schema tutorial
okay let's refresh your databases and open it. Look for the database that
you just created here is our star schema database, so i'm going to
click on the plus sign beside it. Alright so the database diagrams is
where our physical model is going to reside
and as you can see we do not have any diagram yet. okay so once we create the
diagrams they will also show up under the tables
and we do not have any tables yet. These are system tables and that does not
belong to us. So to start with I'll right-click on the
database diagram and click on new diagram. Alright
so in this canvas i'm going to create the dimensions and the fact table and
design a star schema so I hope you guys follow along as we
design this physical model. Alright so from our
logical model the first on our list, i'm going to
create the contractor table so i'm gonna call this drilling
contractor
okay that's the table name or dimension name
and what are some of the columns that we have, we have contractor ID
and under physical model that's where we define
our data type and for primary key we're going to give it an integer
it's an integer and primary key shouldn't contain or shouldn't
allow nulls so i'm going to uncheck this. the next column is the contractor name
and that i'm going to give it nvarchar yeah of length 50 okay that can contain
those it's all right and contract a hire date
hire date that's gonna be a date
data type alright and then contractor
end date, that's also a date data type the next on our list
is employee so i'm gonna call this table
employee table
we have employeeID which is a primary key and it's also an
integer it doesn't allow nulls okay so employee first name
it's an nvarchar of length 50 okay i have last name
which is also nvarchar okay we have email
and so on and so forth so just end here. Alright guys you you saw what i just
did so let's just just gonna go ahead and
populate this off scene. so these are all our tables and
if you wish to add a description which can tell your colleagues what
particular column means you can right click beside that
column and go to table view modify custom
and add a description to that column
after that you can just right-click again and go to table view and
custom you have your description then you can just say that
this is a unique
identifier
of
contractor table so your primary key contractor id is a
unique identifier alternatively you can add an annotation
by right clicking and you can add annotation and say
exploration
drilling activity okay by
Mina and the date today's day is
August 13th 2020
okay so this is a description annotation that you can leave for your colleagues
to refer to. The next thing will be for us to identify all primary keys
and just right click beside the primary key and set
primary key for all the tables. This is our primary key
employee id is a primary key drilling id is a primary key for the
fact table datekey is a primary key
and drilling issues well the next step is to
identify the foreign keys and you can simply do that by dragging the primary
key onto the corresponding column in the fact table so contract
id maps onto contractor id here
okay so okay contractorID okay there we have it. so primary key
table is contract and this is the column
and then the foreign key drilling activity fact table
and contractID is also the column okay i like the name everything here seems
good okay so that's the relationship i'm just
gonna go ahead and do the same thing for all the tables so you can follow along
just watch so location id goes on to locationID
Concession to concession okay employeeID to employeeID
okay. As you can see here i have two dates columns or two date
foreign keys in the fact table mapping to
one date table. This is what we call a role playing
dimension so the date dimension is playing multiple roles which is drilling
start date and drilling end date if you want to know more about role
playing dimensions please comment below and let me know and
i will go ahead and do a video on that for you
okay so once you are done you can arrange all your tables
also if you wish to make your primary key an identity key you can
simply right click on it and go to properties this is the
property panel so you go to identity
specifications and then you check yes
and it will tell you that identity incremental is
one and the seed value is one it means that
the primary key is going to start from one and will be increasing
by one so you can simply set your identity key
here under the property panel. This is our physical
model star schema and SQL server management studio.
Guys so this date as i talked about earlier
is mapping to two columns in the flat table it's a role-playing dimension
let me know below if you want me to do a video on a
role-playing dimension you can also fit to screen. So this is our star
schema and this time you can click ctrl + s
to save this diagram and it should show up
under your database so i'm just going to refresh
and open my database diagram and there we have it
so i can close this and open my diagram again and i have my physical model here
in my database management system you can also go to tables and see all
these tables listed under your tables here so i have all the tables listed
here and right now we don't have any data
in these tables yet you can select to see
even if we query this any of these tables we shouldn't expect to see any
result
no data yet. We are going to go ahead and populate these
these tables in future in future videos so stay tuned if you are interested in
seeing how to populate tables in sql server management studio
that's our data warehouse guys this is our
dimensional model to store our exploration
gold exploration data set thank you so much for tagging along with
me on this tutorial and i hope you like subscribe and
share this video. I will be showing you guys how to
design the same physical model, we are going to use T-SQL code
to design a physical model in another video
okay have a good day and stay safe. Bye