Hey guys, in this video, we will be looking at how we can go about forward engineering a data model into a database. Now, like we would have discussed, the data model is a visual representation of a database and all its objects, including tables, views, stored procedures and functions. And so we would have created a database before no and then reverse engineered it into that data model representation of itself. This time, instead of writing scripts or going into the user interface and creating the database upfront, we want to actually take you from a modeling perspective because everybody has their own style some people do better by modeling it before they generate the database and some people do the database and then look at the model just to verify what they have done. So in this video, we want to cater to those who are visuals first. So from the my SQL workbench homescreen we can go ahead and do this er D looking at On, and I have this sample model here, but we will be creating our own.

So we just click that plus sign beside the word models, which will bring us to an interface that is well pretty blank. But then if you take the time to just appreciate what is there, then you can see that is not very scary. So firstly, we want to change the name of the schema, our database, and we can just write we can as double click on this tab here and then it will bring up this menu allowing us to make a change. So let's make a simple Amazon flow and some which actually call it Amazon clone. And in Amazon clone we will be having some tables to track customers and their orders. Now after we rename the schema, we can go ahead and start creating tables.

So the first table I want to create his customer so I can just double click on this Add table icon here and it will Bring up table one by default, that's actually just default. And below it will allow me to start editing. So I'm going to call it customers. And Firstly, I want an ID column. So it's already generating one for me, Id customers. So my naming convention would have me there, just say ID or customer ID.

So just say Id leave it as an integer, a primary key and also added to be a auto incrementing. And I'm going to keep this one simple. So I'm just going to see email address and full name. So for our customers, we are storing their email addresses and their full name. So that is one way to create a table but then that way is not the visual way that I would have sold to you within the first minutes of this course. So that is one way we can use the workbench interface to create the table without writing any code.

But then we did say we want it to be visual For visuals first, so what we can do is actually add a diagram by double clicking this command that says Add diagram. And then that will actually give us this blank template to allow us to start adding tables and such. So, to the left on the catalog tree, you'll see that you actually have your schema almost on throne. And then you'll have the tables list that we created customers just know. If I wanted to see customers over on this canvas, I can just drag it and drop it. And then there we go, it generates that entity representation off it here for us to see that if I wanted to add another table to this blank canvas, I could just click this icon here that says place new table if you hover over it, that's just that square with that grid and click anywhere on the canvas and that's where it will drop that table.

And then if I double click on this newly generated icon or entity representation, then you'll see that the menu opens Up, which is pretty familiar to us by now. So I can just see orders. And that's the name of my table. And I can start adding columns. So Id, and this is my primary key, which is auto incrementing. See all orders of our code, so maybe order, pulled order number, and maybe a date, time for the order.

All right, that as soon as that data type, and we see a full complement and listing of data types here, so of course, you want to choose the appropriate one. So this time, it's date, time. So it's the time so I'm going to do this. Oh, alright. So date time does not take a size so var char takes a size the time does not. For some reason, we click bedtime.

My school is giving us this, these parentheses, I'm sure you can put values in there, but you really don't need buddies because you really just want at the time so you can just remove those parentheses. If you Got that arrow that I just got, and you should be fine. But I'm going to rename this to order date, so that we don't get confused between the data type and the column name. And if our column comes up that you don't want, you can just right click it and say delete selected, and it will remove it. And you'll see that whatever change you mean down here, it updates that visual representation pretty much in real time. Now, one other column that we probably want in Orders table is customer ID, and I'm going to just add it here.

So I'll say customer, ID, and customer ID would be of type in snow. I'll explain the purpose of customer ID even though I won't get into too much detail because this is not the lesson for it. But do recall that when we started off looking at relationships, and how data is related to each other, we did see that we have a table you can have two disparate sets of data. So we have a Customers table and we have an Orders table. is two entirely different sets of data. However, an order can only exist if a customer made it.

So we need to be able to associate this order with a customer. Now, instead of repeating the email address and full name of the customer every time because if I made 10 orders, then that would be 10 Records in the Orders table, each with my name and email address being repeated. Instead of repeating all that data, what we do is creates what we call a foreign key inside the table that has that dependency, which would be in this case orders, depending on the Customers table. So customer ID would be the representation of a customer in the Orders table. All right, so that's what customer ID is here for. So I'm just going to leave it there.

We looked at the data model for the world database and we saw lines. I'm not going to go ahead and draw lines here because this is not the list and for that, but I'm just letting you know that this is going to come so you can just prepare yourself for it. But essentially what I wanted to drive home was the ability or is the ability to go ahead and create tables and columns using this graphical user interface method and this visual method, and you can just add as many tables as you need. And then if you have no use for it, you can just click it and press Delete. And you'll see that over in the catalog tree, it gets updated as you make changes here. All right.

Now when you're finished with all of your changes, and you're satisfied with your model, of course, this is not our final work, but for now, let's just work with it. Then we can just go ahead and click database and click forward engineer. So forward engineer will look similar to reverse engineer where it will ask us to connect to some instance in this case, I'll leave it at the local default instance that was given to me and I click next and then they will ask you for options. I typically don't check any of these because whatever I would have done in the model would would kind of, well, these options would kind of be asking you do you want to do everything you did in the model. So I'm very deliberate in designing the database, whatever I would have designed is what I want to keep. So I'm not going to take any of these.

I'm going to leave this screen as is, at least within the context of this lesson. And then we click Next. And then it will ask us, what do we want to import. So right now, we only have two tables, what if it is that you had a view and a routine and a trigger and other objects, then you could just take all of them, and they would make sure that when they're generating the database, they would actually include all of these for creation. So right now I only have the tables second is ontic. These it's no harm if you take them or untick them since they're all zero, and then we click Next.

And then what happens really is that it uses this model to generate chord that is similar to the chord we would have written when we were actually creating a database. And we can just scan through this because at this point, we should all be comfortable with the chord. And like I said, when we were doing that lesson it, we can use the word database or schema interchangeably. So we said CREATE DATABASE, they say create schema, but the end result is the same. And then they add on this part, if not exists. So pretty much they're saying that if there is no schema by this name, then created if there is then there will be no creation again, so it won't repeat itself for it.

This is a nice way to skip an error. So you can actually use this in your script. If you're creating a database or a table, once you're seeing creates, you can just see creates the table if not exists. And that way you'll skip any this already exists arrows. And this is just using some default character set, I guess for Western coating. And like I said, when they generate the code, you'll see a number of things that you probably didn't write.

And I probably didn't show you. But the end result is pretty much the same. And it's good to have an appreciation for all sides of the fence. So you can go ahead and just take time to appreciate all of this, but the end result is that you should copy this to clipboard, I usually copy it to clipboard just in case you can also save it to file so this would be good now if you're doing it on your development computer, but the real database will be deployed on another computer, then you can just save this to file which will just save an SQL script which you can just reopen on the other computer and execute. Alright, so it's allowing you to export this creation operation to another machine if you so desire. So since I'm on my machine, I can just copy to clipboard and then we click Next.

But it really go ahead goes ahead and connects and makes those changes. And when we click close, and we go back and we try to connect to our default instance. Then we see here that our newly created database is listed here. All right, sorry, the copy to clipboard just in case of anything. I usually do that here. Once again, you could also save the file just so you have an archive or for further use, but the reality is that once you forward engineer having created the database using this er D approach, you can always forward engineer your database and it will deploy it on whatever instance you have selected.

So that is how you go about generating a database from a data model.