hi there in this video we're going to talk about a very important topic known as erd diagrams entity relationship diagrams that's what erd stands for and this is more of a design concept and this is often created by database developers that are introducing new data models into their database and they want to add new data into their database they have to first design how the data is going to go what table is going to get what kind of data and for that purpose they have come up with this methodology of creating a diagram like this all right and this diagram is basically uh it represents tables and their relationships that's exactly what erd stands for entity relationship diagram think of each of these as entities or tables and the lines between them are the relationships so let's break this down a little bit the customers entity here or customers table has only customer information okay we got the customer id customer name customer address it wouldn't make sense for the customers table to contain items right that doesn't make any sense it wouldn't make sense for the customers table to have orders it's a totally different entity so customers should only have customer information orders should only have order information now keep in mind that the orders is related to customers somehow there is a one-to-many relationship between customers and orders what does that mean that means a particular customer can have many orders so for example if you're a customer you go to the amazon website you add a bunch of things on your shopping cart and you play you press the purchase button you have now placed an order on amazon let's say or ebay and the order contains many items it could have one item or it can have many items that's where the items table comes into play so if you notice these symbols here if a customer can have one or many orders meaning you come to the amazon website today you place an order tomorrow you could place another another order or you know you can place hundreds of orders on amazon you could be a very frequent shopper well you are still one customer right but you're placing many orders that's what this relationship shows there's a one to many relationship here and this pipe here represents a one so a customer can have one or more orders one or more orders an order can contain one or more items okay so this little pros feed this three lines here these three little spikes think of these this is actually known as a crow's foot in in this symbol notation so the crow's foot goes on the many side of the relationship so an order can contain many items one or many same thing with customers can contain like it can have one or many orders and as you can see each of these entities have a unique identifier so each of the items that would go in the items table would have an item id a unique identifier each customer in the customers table will have will be identified uniquely by a customer id same thing with orders each order that gets placed on the website will have a unique order id that's associated with that with that given order so this is how you design a data model i'll show you another example and by the way the tool that i'm using here to display this diagram i'm using a it's called kaku c-a-c-o-o dot this is a fabulous tool for creating software design models like this okay and this is a database design template that i'm using erd that's what this is you can also create flow models all kinds of all kinds of diagrams here okay so let's look at another example here's another one this is also an erd and it has a different kind of relationship between some of these these entities so let's let's go through these so we've got the students entity courses entity and instructors entity so think of these as things and each of these things will be stored in their individual tables right so students would be in the students table and each student will be uniquely identified by student id a student table the students table should not contain any course information okay it should not and the courses table should not contain any student information because you've got course id that uniquely identifies a course for each every single row in the courses table is uniquely identified by the course id how could you have a student name there that wouldn't make sense same thing for students you've got student name student address and then all of a sudden you've got a course that information should be in the courses table and same thing for instructors these are three different tables now let's observe their relationships here you've already we've already went through this kind of a symbol right a crow's feet with a one this is on both sides of the equation here so what this is saying is many students can be enrolled in many courses so if you break it down a student can be enrolled in many courses and a particular course can have many students right it wouldn't make sense for a course to just have one student what kind of course is that right not very popular so a course can have many many students and a student could be enrolled in many many courses right a full-time student let's say but a student can also have just a single course let's say they're taking the semester off except for just one course they want to keep a light semester they could just keep one course that's why there's a one here or pipe so that's what this represents one or more or many so on both sides sides of the equation you can have a really unpopular course that only has a single student in this example now let's look at this side of the relationship with courses and instructors a course must have one instructor okay that's why there's this pipe here notice there's no crow's feet because it wouldn't make sense for a course to have many instructors i imagine you can you could have classes that are taught by uh many instructors you know ideally that's probably what it is in in a college curriculum but in a simple let's say a simple small school a given course is taught let's say by a single instructor in this model let's say that's what this relationship represents a given course can have one instructor it should have one instructor but an instructor could be teaching could be teaching zero or more courses so let's say this particular instructor knows math and english so that given instructor is going to be teaching not only the math course but also the english course okay that's why we have that given instructor teaching multiple courses now there's a zero here right this little circle this represents a zero which means that an instructor could be teaching zero or more courses so you could have an instructor that is currently teaching no courses but they're still employed by the school they might just be busy doing some research or something so they're still an instructor by profession but they're not teaching anything so that's what this zero represents an instructor could also be teaching zero courses or more so hopefully that's uh you're starting to get the point of what an erd diagram is we're uh talking about the different entities and before they're saved in a table we kind of form a relationship like this a diagram to understand how the the data is related and what are the different entities okay so database developers often before working with data or new data when they need to introduce new data in a project or an application they create diagrams like this to work out how the data is going to be stored and used in the application and erd diagrams are really helpful helpful for that now one thing i'll note is there's usually a rough draft when you actually get to the practical implementation of this you'll note that we have a many-to-many relationship this is known as a many-to-many relationship between students and courses but you can't force this relationship exactly like this in the database when you have to practically implement it you're going to need something known as an intermediary table that manages the relationship between the students and and courses for this particular relationship many-to-many relationship now why would we do that let's let's work through this for a second a student's the student's table can only contain students so it wouldn't make sense for let's say we only have 100 students in our school that means we have 100 records in the students table well let's say we have 20 courses and let's say that that one of the students takes five courses we can't have that student repeat five times in the students table because again in the students table each record in the students table is uniquely identified by student id is the primary key so each record is a unique student so they can't we can't assign 10 courses to that one student in that row it doesn't it wouldn't that structure wouldn't make sense it wouldn't work same thing with courses each course is uniquely identified by this course id and let's say the course there's those there's only 10 courses taught in the school well we have a hundred students each row is uniquely identified by a course so what we're going to assign 20 students to that given to that one course we can't add student ids directly into the courses table like that all right we need something known as an intermediary table and i'll go over that in just a moment but let me just show you how we would go about creating these ids now let's come back for a second to this older diagram that we looked at previously this is a bit easier to understand and now i'm going to start adding the columns and where they belong all right so let's think about this customers can have many orders each customer is uniquely identified by a customer id so let's say we have 100 customers in this customers table those 100 customers could have placed thousands of orders so we should not have orders in the customer table now each order is associated with a particular customer so in that case it makes sense to add a customer id as a foreign key here so let me add that customer id okay so the foreign key here notice the primary key in the customers table is customer id in the orders table it's a foreign key so let's say i'm the customer my name is mt as a mod i'll place 10 orders each order is going to have a unique id but each of those orders will also have the same customer id my id it could be placed on different dates it could be different orders but the customer id is going to be the same okay so the foreign key goes onto the many side of the relationship here now let's look at items now there could be many items associated with the same order so we need the orders id here in the items table so let's add that orders id let's make this italic to represent that this is a foreign key the customer id here in the orders table is a foreign key so we can have 10 items associated with the same order id okay now each of those items of course is going to have a unique you know is going to have a unique id right because item id is the primary key each of the 10 items is going to have a unique id but they'll have the same order id because they belong they all of those items belong to the same order now you cannot add item id here in orders because that just doesn't make sense and you can't add the order id in the customers table that doesn't make sense either okay so hopefully you get the idea the foreign key right is always on the many side of the relationship that's why we have customer's id here in the orders table and order id here in the items table so keeping this in mind if we go back we can't directly have the student id in the courses table or the courses id in the students table we need an intermediary table and let me show you what that table is going to look like i'm just going to well let's just create a table here to prove the point so we can call this table let's let me shorten this a little bit we can call this enrollments so the first row is going to have for example the student id one one one one two for example and the course id could be one and then the same student one one two could be enrolled in a different course two and then a different student let's say one one three could be enrolled in one and one one three could be enrolled in course two okay so this table now represents the intermediate this is the intermediary table that will store the relationships between students and customers and this is the kind of data that will go in here it will only contain the foreign keys the foreign key of the students and the foreign key of the co of the courses and so how would we form that relationship in a in a diagram like this why don't you draw on a piece of paper how you draw that relationship out and try it out on your own then you can resume to watch my solution so pause the video now and try that draw the entity relationship to incorporate this enrollments table in this model you can pause the video now okay welcome back hopefully you were able to do it so let me move this up a little bit to make some room and let me just make a another copy of this table and paste it here and let's call this enrollments okay and this will contain enrollment id as the primary key for the enrollment table and it will just contain the student id as the foreign key so i'll make that italic and the courses id course id like that and this will be the internet intermediary table and i'll delete this relationship here we'll put enrollments between these two okay let's move all this stuff further so now we have let's let's make this a little different so that we can see that there's an intermediary table and i'll draw this like that so a course can have zero or more enrollments okay that's what this relationship represents and same thing here on the other side i'm just going to connect it here and put the other end down to this right here and a student could be enrolled in zero or more courses so they could be a student still a member of the school but not enrolled in any courses that's what the zero represents so they could be zero or many enrollments and the foreign keys of the course id and the student id is managed in this enrollments table and the data that goes in this table looks something like this right where the first column here is the student id and the second column here is the course id you can enroll you can ignore the enrollment id here you don't really need it because this table is just going to manage relationships if you want you can have a primary key for enrollments but it's not necessarily the key here is to keep in mind that this enrollment table manages this relationship here okay so hopefully you understand the whole concept of erd now in the oracle sql certified associate exam there's no way they go into this much detail in the exam you may have only one or maximum two questions associated with erd diagrams okay we went into much greater detail as to how to design your tables even or how to think about storing your data in entities that you design like this but the questions associated with erd diagrams there's probably going to be only one and it's going to be much simpler than the concepts that we've covered up to this point between both of these diagrams okay so hopefully you get the idea why don't you think in your mind come up with different data models think about how the data is going to be stored in the tables think about the relationships that you'll have to associate between the two entities and and come up with your own data and practice on this tool and this of course is not going to this practice is not going to help you much on the oracle sql certified associate exam this is just really good information to know and have knowledge about in the practical workspace right this target this course is targeting not only the certification but also how to be successful as a database developer in the workplace so if you have that goal in mind then definitely practice more with these concepts learn you know play around with this create your own data models but this is not something you need to master by any means for passing the oracle sql certified associate exam okay so let's wrap it up thanks for watching i'll see you in the next video