Transcription
Hey, welcome to Giraffe Academy! My name is Mike. In this tutorial, I'm gonna show you guys how to convert an ER diagram into a database schema.
So basically, we're gonna take all of this information inside this ER diagram, which we created in the last tutorial, and we're actually gonna take this and use it to create an actual database schema. From this diagram right here, we'll be able to create and define actual database tables that we can use in our relational database.
So let's go ahead and get started. I'm gonna show you guys, basically step-by-step, how we can start converting this into database tables and database relations.
**Step 1: Mapping of Regular Entity Types**
For each regular entity type, we want to create a relation, which is just a table that includes all of the simple attributes of that entity. So here we have all of our regular entities: branch, client, and employee. What we want to do is just create relations, or basically database tables, for each one of those regular entities. The columns of those relations are gonna be all of these attributes that we define.
From that, everything that's inside of the green squares will give us something like this: we have our employee relation, which includes the employee ID (the primary key), first name, last name, birthdate, sex, and salary. We also have the branch with that information and the client.
I also want to show you guys that when we had a composite attribute, like the name attribute over here, we're just storing the sub attributes, so we're storing first name and last name. Alright, so here we have three relations, and let's see if we can start adding to those.
**Step 2: Mapping of Weak Entity Types**
Over here, we have one weak entity type, which is inside of this green square. For each weak entity type, we want to create a relation or a table that includes all of the simple attributes of the weak entity. The primary key for the new relation should be the partial key of the weak entity plus the primary key of its owner.
In this case, the primary key of the owner is going to be branch ID because the branch is the owner. In other words, the branch is the entity that's participating in the identifying relationship with branch supplier.
So over here, we'll see what happens: we get this new table, branch supplier, where the primary key is composed of branch ID, supplier name, and supply type. For this table, we included the supplier's name and the branch ID; both of those come together to make our composite key, which is a compound key. Then we have supply type, and we end up with this.
Now we have four tables: employee, branch, client, and branch supplier, and they all have their associated attributes.
**Step 3: Mapping of Binary One-to-One Relationship Types**
Now, a binary relationship is a relationship that has two entities participating in it. For example, all of the actual relationships up here are binary; in other words, there are two parties that are participating.
What we want to do is map one-to-one relationships. We only have a single one-to-one relationship here; it says "manages" or "relationships." A branch can be managed by one employee, and an employee can manage one branch.
For each one-to-one binary relationship, we want to include one side of the relationship as a foreign key in the other entity's relation, and we want to favor total participation. So, in this case, we want to include the primary key of one of these entities as a foreign key in the other entity's relation.
If a particular entity has total participation in the relationship, then you want to add the foreign key onto that entity. In this case, the branch has total participation, so we're gonna add the employee's ID as a foreign key in the branch relation. If both of them have partial participation or both have total participation, then you can just use your own discretion, but in this case, it's pretty clear that we're gonna use the branch.
So over here on the branch relation, I added in a foreign key, which is just manager ID. This is a foreign key that points to this employee ID up here, so that's how we're gonna link those two together.
**Step 4: Mapping of Binary One-to-Many Relationship Types**
Unlike a one-to-one relation, now we're looking for one-to-many. You'll see that we have three of them here: branch handles a client, an employee supervises or is supervised by another employee, and a branch has employees working for it.
What we want to do is include the one side's primary key as a foreign key on the many side relation or table. So basically, this means that in this case, we have a branch and an employee. I want to include the branch's primary key as a foreign key in the employee relation.
On the employee relation, now we're gonna have a branch ID column that will store a foreign key to the branch. The same goes down here for client and branch; the branch is on the one side, and that means we will store in the client table a foreign key to the branch.
The same goes for the supervisor relationship; on the employee table, we want to store a foreign key to the supervisor.
So over here, let’s take a look: in the employee table, we have super ID and branch ID. That's because the branch was on the one side of the relationship, and the employee was on the many side, which means that we're going to store the branch's ID as a foreign key on the employee table. The same goes for the supervisor ID; it's pointing to the employee table, so we have to store it on the employee table.
Then, down here in the client table, we stored branch ID as a foreign key, and again that's because the branch was on the one side of that cardinality relationship.
**Step 5: Mapping of Binary Many-to-Many Relationship Types**
In this case, we only have one instance of this: a client can work with an employee, and an employee can work with a client. What we want to do in this case is create a new relation, a new table whose primary key is a combination of both entities’ primary keys. We’re also going to include any relationship attributes.
Over here, the client's primary key is client ID, and the employee's primary key is employee ID. So what we're going to do is create a new table where we have a composite key, which is the employee ID and the client ID. Actually, this would be what we call a compound key because both of its keys are actually foreign keys.
We want to store in this relationship any attributes on the relationship, which in this case is just sales. Down here, we created a new table or a new relation called “works with,” and you’ll notice that the key is employee ID and client ID. Both of these individual parts of the primary key are actually foreign keys themselves, so this is a special situation.
Then over here, we have total sales, which was the attribute that we stored on the relationship like that.
Basically, that allows us to take this ER diagram and convert it into relations. Now, if you have more complex relationships, like if you have non-binary relationships, then it gets a little bit more complex when we're mapping them. In this case, I'm just looking at basic ER diagrams. I don't want to get too complex.
So in this case, in five steps, we were able to convert the ER diagram into a set of relations. If you do have more advanced types of ER diagrams, then there are gonna be more steps. But for our case, there are only five steps that we need to convert this into relations.
Now what we have here is our database tables. Each of these relations is itself a database table. When I'm designing my database now, I know I need to have an employee table with all this stuff, a branch table with all this stuff, a client table, etc.
What we can also do, and what you'll see a lot of times, is people will draw little arrows to define the relationships. This can get a little bit messy, which is why I kind of saved it for the end, but you can see over here for employees, for foreign keys, I'm drawing arrows to what they relate to.
So for example, the super ID has an arrow going back to employee ID, and branch ID has a line going over to branch ID. Manager ID points to employee ID, and so forth. This is basically just mapping out all the different relationships.
Like I said, it can get a little messy and pretty difficult to read if you have more than a couple of tables, but you'll see people doing this a lot.
Now that we have our relations, our database tables, we could actually create a database. Over here, I have an example of what a database might look like from these relations. We have our actual database.
So up here, we have all of our employees, and you'll notice that we have our employee IDs. We can define the supervisor of each employee. For instance, Angela Martin's supervisor is employee number 101, which is Michael Scott. You'll see how easy it is now for us to define all this stuff. Angela Martin also works at branch ID number two, so that links over to branch two, which is Scranton, etc.
Then we have our client table over here and our works with table. The works with table has the employee ID and the client ID, and then we have our branch supplier table. All of these got basically put into our database, and then we started putting information in there.
What this is, is a way for us to go from just a set of requirements to our actual finished database table.
Designing relational database schemas is not super easy. If you have a very simple database or data storage requirements, then obviously the schema is going to be very simple, and you might not need something like an ER diagram. But with something like this, the ER diagram is hugely useful.
Here’s the thing: you don't necessarily need the ER diagram, but it's a really great way to convert requirements into an actual database schema or a set of relations. That kind of shows you guys how you can do that.
Now, I only showed you one example, and I think this is a good example because it covers all of the main use cases. The only way you're gonna get good at using ER diagrams, building them, and converting them into database schemas is just by doing it a bunch and practicing.
Obviously, I'm not gonna spend dozens of videos doing dozens of these examples, but hopefully this example shows you the basics, and now you can design your own ER diagrams and convert them into database schemas following those rules. All the rules that I showed you for converting ER diagrams into relations will apply to any ER diagram.
Thanks for watching! If you enjoyed the video, please leave a like and subscribe to Giraffe Academy to be the first to know when we release new content. Also, we're always looking to improve, so if you have any constructive criticism, questions, or anything, leave a comment below.
Finally, if you're enjoying Giraffe Academy and you want to help us grow, head over to girafeacademy.com/contribute and invest in our future.