Entity Relationship Diagram (ERD) Training Video

so I need to create something on the computer to hold some information for my job it needs to hold the contact info for each employee and all of the projects they are assigned I'm not sure how to do this how do I put information from each part together so I can find it well it sounds like you need to create a database a database is an organized collection of data ideal for storing and retrieving information which means it's a bunch of tables that hold all of your information but they are linked so you don't get a lot of duplicate data this makes it easier to find your information later because you can do a search for the data you want instead of seeing everything at once ok you convinced me I mean one of those database things but how am I supposed to make one of them it sounds really difficult actually the most difficult part is figuring out where the data needs to go but you can make the step a lot easier by creating a diagram beforehand what kind of diagram are we talking about I would recommend an ER D that is an entity relationship diagram it separates the information you need for each table and shows how the tables link together I like it because it's a great way to see the overall design of a database sounds good where do we start well first you need to know some basic terminology used by professionals when they are talking about databases and diagrams some of the common terms used are entities attributes relationships and cardinality cardinality I thought that was a baseball team you're gonna have to explain those a little bit more I will but first let's figure out all of the things I need to teach you before we start working on your database for work I'm going to walk you through a similar database for education I will explain what an ER diagram is as well as the parts and purpose of it I will also explain how you can apply business rules in the design of an ER diagram then we will go back and apply this new knowledge to your business situation I guess I should pay attention to start off an entity relationship diagram or ard is a graphical representation of the data requirements for a database maybe meaning that it takes all of the parts of a database and puts them in a box in line form there are five major parts to an ard entities attributes primary keys relationships and cardinality yeah I think you mentioned those those parts will be used to create the er D then the diagram acts like blueprints for a building you simply take the parts of the diagram and turn them into parts of the database the final diagram should look kind of like this now to explain those parts first we have entities an entity represents a person place or thing you want to track in a database in this example student this will become a table in the database so you're saying a student as an entity kind of student is more of a container which will hold many students entity basically means that all of the things that are like a student will be grouped together under student so there will be this table of students yes each occurrence of this entity is an entity instance this will become each record or row in a table oh so this is like each individual student a single student is an instance of the entity student yeah you've got the idea now we have attributes an attribute describes various characteristics about an individual entity these will become the columns in the table weight it does what well it tells us more about the entities each of those students now has a first name the first name is an attribute because it tells us more about each instance of student so each individual student has a first name got it yes but it is important to note that these attributes can be unique to each student but they don't have to be meaning that each student can have their own name but if there are two students with the same first name it is still okay but make sense good because that brings us to the next part primary keys a primary key is an attribute or group of attributes that uniquely identifies an instance of the entity slow down it's an attribute that identifies what remember how I said that attributes don't have to be unique to each student well a primary key is like a special attribute that must be unique a primary key will uniquely identify each instance or make it so that no two rows will have the same value for that attribute I think I understand so at first the students only had first names but if two of them had the same first name it would be confusing and they might get mixed up but now you added student ID which means that no two students have the same number yeah you've got the idea keep in mind that sometimes you need more than one attribute to make each instance unique in those cases you need to use a composite key but we'll explain that one more when we get to business rules sometimes we need a composite key instead of primarykey got it alright now that each of our students has their own unique student ID we can start forming relationships relationships are they going to hold hands not that kind of relationship a relationship describes how one or more entities interact with each other usually this is described with a verb we're going to create a link between the entity student and the entity phone number so that when we create the database we can easily find the contact information for each student instead of filtering through rows of numbers sounds beneficial how do we do this exactly in the diagram we're working with now we are going to show the association between student and phone number using a verb a student adds a phone number seems pretty straightforward just draw a line between entities showing they're connected through relationship yes however relationships can be between two instances of entities or maybe a lot of instances or maybe you don't even need an instance this complex idea is explained through cardinality oh so we're not going to a sporting event sorry to disappoint living on cardinality is the count of instances that are allowed or are necessary between entity relationships okay that one is a little hard to grasp let me simplify then remember how entities will be tables and an instance of the entity is a row in the table cardinality just tells us how many rows we need from one table before we can link it to another table so so let's go back to our student phone number relationship a student can have zero one or multiple phone numbers right but each phone number must belong to a student yeah I can see why you wouldn't want to have a phone number in the database unless it belongs to a student exactly now cardinality is broken down into two parts minimum and maximum minimum cardinality represents the minimum number of instances that are required in the relationship and maximum cardinality represents the maximum number of instances that are allowed wait so the types of cardinality or what minimum tells us the fewest number of rows we need for a relationship and maximum tells us we cannot exceed a certain number of rows in our relationship an easy way to represent cardinality on a diagram is through crowfoot notation crows foot you mean like the bird I kind of looks like its foot at least there are four different ways to represent cardinality is in CrossFit notation one mandatory means that you must have at least one and only one instance many mandatory means that you must have at least one but you can have several instances one optional means that you don't have to have an instance but if you do you can only have one many optional means that you don't have to have an instance but if you do there's not a limit on how many instances you can have so the mandatory versus optional refers to the minimum and the one versus many refers to the maximum that's right now that you know some of the terminology and parts of an erd let's see if we can combine them with business rules to see how they influence the diagram a student can have zero one or several telephone numbers they may or may not have a telephone number how to apply this to an ard create a student entity and a separate phone entity then create a one-to-many relationship from the student to phone number this means that one student can have many phone numbers then show optional versus mandatory it is optional for a student to have a phone number however it is mandatory that a phone number is connected to a student student a graduates from a university moves out of the country and cancels her phone service a year later student B moves from out of state to that same University when she signs up for a new phone service she is assigned the same phone number that student a had cancelled now there are two different students with the same phone number how to apply this to an ard as a rule the primary key must be unique and can only appear once in an entity if the phone number alone is used as the primary key it can only be used once and you will get an error if you try to use it for another student if you use the student ID alone as the primary key you will only be able to have one phone number per student to solve this issue you will need to use a composite primary key this uses a combination of student ID and phone number to create a unique identifier [Music] at any given time a student can either be an undergraduate student or a graduate student they can also be at one time an undergraduate student and then become a graduate student depending on the type of student they are we need to collect specific information that applies only to that type of student how to apply this to an ard we need to create three separate entities students undergraduate and graduate the student entity will hold the information that is common to both types of students such as names addresses etc the undergraduate entity will hold the data that is only applicable to undergraduate students such as the high school they graduated from and their high school GPA the Graduate entity will hold the information that is only applicable to the graduate students such as the undergraduate university major and undergraduate GPA this will allow you to track information that is specific to a type of student without taking up extra space for the students this rule doesn't apply to one student can take many courses and at the same time one course can be taken by many students this is determined during registration how to apply this to an ER D you need to create a many-to-many relationship if you were to try and express a many-to-many relationship using only two tables you would get an error message this is impossible the way to properly create a many-to-many relationship is by using a bridge table creating a one-to-many relationship between each main table and the bridge table when we introduce a bridge table in this case the registration table we can combine the data from both tables while still keeping it organized and uniquely identified now let's see if you can apply this knowledge to your business situation I think I can do this I will create entities for each table I need then I will write in all of the attributes I need for each entity then I will use unique identifiers for primary keys which will allow me to form relationships I will use crows foot notation to show cardinality and then I'll have an excellent diagram after that how hard could it be to build a database Wow it looks like you've got the right idea now let's see how your finished ERD will look you [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music]

Channel: Gina Baldazzi

Views: 937,230

Keywords: ERD, Training, Final

Id: -fQ-bRllhXc

Length: 15min 3sec (903 seconds)

Published: Tue Jan 29 2013