Using Visio to Create a Database

Using Visio to Create a Database


Hello. Welcome to COP4708. In this session, we are going to learn how
to use Visio to create a database design. Let’s open Microsoft Visio. and you can click on the database model diagram and that will show here. and then create which will open a new diagram page with the objects of that database, entities, and relationships. To begin, we are going to select the entity or the table that we are going to create at the beginning and drop it here. You can actually zoom in just to see the creation and make it around 80 This is Table1 and you have the properties sheet down here. So you can actually fill that information
in the Properties sheet. The first one– The first table we are going to call “Order_Item”. You can write it like this or you can write it in all caps. Then you select “Columns” which is the second one in the properties
sheet. and start adding the different columns. For example, we need an OrderID and that will be required. We need an SKU number and that will be required, and we can consider
it as a primary key for the item and that will be the ID or how we can identify
the different items. And then, we have the quanity which is also required. And the price, that’s also required, and the extendedPrice and in this case, it will be calculated so I won’t have to worry about it being required or not because i will bound calculations behind that which will be Quantity times the Price and that will give me the ExtendedPrice. But, let’s look at the data types. As you can see here, the default was Character with a size 10 We can change that by just clicking on that and select the proper
data type. In this case, we are going to select Integer
for the OrderID. We are going to select also Integer for the
SKU unless we have stock keying unit that has
letters. In this case, we’re going to change it, but must of the Stock Keying Units are in
numbers, so that’s why i make in Integer. And then we have the quantity being integers since we are not using a breakable item. Breakable items means that items that can
be sorted by 3½, 4½, something like that. Instead, it’s integer and only whole numbers. For the Price and the ExtendedPrice, it’s
Currency, not Character. So we can actually change it to Currency. So I selected the data types, we added the names for the columns, we selected the Null/Not Null or Required/Not
Required and we selected the primary key for the table. So that’s Table number 1 being created in the design stage. Now we can create another table and to do that, all we need to do is just to drag another one and drop it beside the first one. And then we’ll go to “Definition” and select the name for the second table. In this case, it will be “Retail_Order”. Again, we go to Columns and start adding the different columns for that table. OrderID, and that will be your primary key. Now, notice that we used this column before in the previous table, so what’s the idea? The idea is to be a primary key in this table and will be a referring key in the other table. And that will be the link, or the connection
column between the two tables. We can start actually changing the type as
we create the column. StoreNumber, and that will be also Integer and will be required. StoreZip Code and that will be Character, but we are going
to make it length… we’re going to change the length to 9 just to show you that those characters, actually the size for the character can change with the length that you want. We have OrderMonth and again, I’ll change the character size
to read 12. The ZIP code will be required. The month will be required. I will make the year as not required. and make it as integer. And we have the OrderTotal and that is Currency. And that’s required. So we have the required columns, the primary
key, we have the data type, and we have the different
columns being added. If you select the primary ID, you can see that the Primary ID was selected
already. If it was not selected, all you would need
to do is highlight and add, and that will be your Primary ID. In this case, like I said, we already selected
the primary key when we were creating the columns. So, we define the table and in the columns and define or identify the primary key for that table. And that will be the second table. Now, to create a relationship, we have “Relationship” here. We can just drag and drop it and then just identify the link. As you can see here, it did use the OrderID
as my link between the two tables. If you click on that relation, you can see the name of the relation, you can select if you need it to be identifying
or non-identifying, if you want the cardinality to be one or more or zero or more or zero or one or exactly one So you can select the type of relation that you would like to build here. Again, if this is not showing here since you created the relation but it’s not
showing, what can be done in this case is just select the text and see that it is many-to-many relationship. And now it’s documented here. and it’s a many-to-many relationship. So we have the first table, we have the second table, we have a relation, and we have a many-to-many relationship. To make that mandatory or optional, instead of using this, we can just add shade
over circle or lines as needed. so you don’t have to worry about this one, just add a circle or add the lines to make
it mandatory or make it optional. If you have any questions about how to create
the design, please let me know, and I will be more than
glad to help. Thank you.

3 comments

  1. it's just create model, where create database self? for example how create script for SQL Server database create model or upload/sync?

Leave a Reply

Your email address will not be published. Required fields are marked *