Let's start with components of ERD i.e the major components of ERD before we actually
draw a particular ERD 1) Entities So Entities, what about them, anyone? A real object It seems like you have studied object-oriented
programming What is an entity? Entity? ... Let's just cut the crap. Entity is a table. I mean, I could say ten different things about entity Like entity is a real life thing, it's an
object, some falana, some malala..... But at the end of the day, Entity in an ER
diagram is nothing but a table. Every entity that you are going to draw in
your diagram will translate to a table The symbol for entity is: Rectangle You must name the entity. E.g User Or you could say Student or something else Basically this is your one table in your
database So what is a weak entity? Weak Entity? ..a weak table..(a lame joke, plz laugh) Weak Entity: I will give you a really simple example that will make it very very clear for you Let us suppose there is this table called
as: User Assume it as a user of any social networking site And then there is this one more table called as Profile User table will store information about a
user like; user id, name, email id, age, gender, country etc And the profile will store information like: About him/her, his/her hobbies, profile picture etc. Now, in any social networking site if you see Each user will have one profile, right? But profile belongs to a user i.e a profile can't exist independently without a user If there is no user, there is no profile So this entity depends on this entity for
its existance So that can also means that this entity will
not have a primary key of its own (not a rule) Profile will not need any separate primary
key, because user id will only be its primary key basically, these two tables will be related. A user has a profile. the relationship will be based upon some common column if the primary key of User table is user_id, then it will also be the primary key for Profile table We do not need a separate primary key for profile because profile only exists because of particular user when we delete that user, let us say you
delete your own facebook account Your profile information will also be automatically
gone it no longer exists So that way we will denote this entity (Profile)
as a weak entity, this is the symbol for it So weak entity totally depends on another
entity for its existence. Usually, the weak entities do not have their
own primary keys. Usually their primary keys are the foreign
keys i.e primary keys of some other entities (their
parent entities) Or their primary key could be a composite key which includes primary key of another table (parent) and one attribute of their own So we haven't talked about attributes in detail yet So what are attributes? I am really sorry that we did not touch upon
attributes earlier so attributes are very very simple to understand attributes are the columns of your table So if entity is your table, then columns of
the table are your attributes Are there any types of these attributes? OK, you seem to have done your homework Yes, there are some types of these attributes the most known is: Primary Attribute
(Primary Key) Which uniquely identifies a row in the table so we usually denote that (Primary key) Attributes are denoted by these OVALS or ellipses And primary key is denoted by underlining
the name of that particular attribute This (underlining) signifies that this particular
attribute is the primary key Then apart from the primary key, attributes do have certain types: So derived attribute is something whose value
is derived from some other attributes E.g my user table has a column (attribute)
as date of birth (dob) And then have one more attribute, something called as age Now I denote age as a derived attribute This is the symbol: the dotted ellipse it means that value of age will be derived from that of date of birth (dob) Like we will subtract the dob from the current date to get the age. That way it is calculated from the value of some other attribute. That is why it is a derived attribute. second is a multivalued attribute e.g if you are storing tweet so on twitter you have tweets they must be stored in some table, right? in Tweet table you have some attribute called as: tags hashtags or tags so one tweet can have more than one hashtags so basically the multivalued attribute is like that. It's an attribute who can have multiple values And they are usually denoted by this: inner ellipse so this is the notation for denoting multivalued attributes And then we have something called as composite attributes Composite attributes are like: E.g in user table we have another attribute called as: name but we have split the name into two more attributes such as first name and last name So now, name becomes a composite attribute Which can be further split into child attributes like first_name and last_name So this is the way to show the composite attributes Isn't there any attribute type for the foreign key? We do not show foreign key (separately) in an ERD Why so? Ok, that is an obvious question and I have a very obvious answer for you if I say that user table is related to profile table let us say, User "has a" profile then they are related because they must have some column in common you always say that two tables are in a relationship when there is some column common between the two tables and the foreign key of this table has to be the primary key of this table (again not a rule) and primary key is already denoted here so if it is denoted here, it means it will be automatically present in the profile table by some name, either by the same name (user_id) or some other name Now that we have depicted the "has a" relationship already, we do not need to show the foreign key column again sorry, foreign key column again it understood that, this table is in (has a ) relationship with this table, that means there is a common column, there is primary key and foreign key involved the primary key column is already shown which will become the foreign key in another table in the relationship (has a relationship) So we do not really show the foreign key separately there are three popular types of relationships: one-to-one relationship one-to-many relationship and many-to-many relationship or I would say these three are the different cardinalities that are available with respect to Relationships You should answer this question: One user can have how many profiles? One profile strictly one profile? because we are going to use this: Crow's foot notation to denote the cardinalities So we say, one user strictly has one profile so this is the notation for: strictly one And one particular profile belongs to how many users? strictly one one profile belongs to strictly one user so do not confuse this with: ok but I can open multiple accounts on Facebook in that case, Facebook treats you as two separate users, even if you are the same person then we have, as we said one-to-many, many-to-many right? So when we draw full-fledge ERD at that time we will again study those all types So I will tell you the notations right now then one-to-many let us take an example: User and Email Ids One user can have many email ids One on Yahoo, one on Google, one on Outlook maybe some of the users do not have an email id So one user can have zero or more email ids notation is like this: zero or more first we go forward from one entity to another and then we go reverse One email id belongs to strictly one user, strictly is the word it is actually one to many relationship but we have also included this zero and strictly one to make it more specific (to give example of notations) Any example for many-to-many? By the way nobody asked this text in these diamonds, nothing but the name of the relationship We will talk about it more when we draw the ERD Any example for many-to-many? A movie producer and a movie A producer can make One? Even if you make one movie you will become a producer yeah like lifetime achievement award so one producer can make one-or-more movies so this is the symbol for one-or-more now we go reverse So one movie is made by at least one or more producers so that is many-to-many relationship example So this is our software BBarters, ok User What about its attributes? You want to help me with that? userid I will presume, userid will also be the primary key user's name and we can further decompose it into first_name and last_name maybe we can have date of birth and then we can derive age from it see the first step while drawing an ERD is: to identify your entities 1) Identify the entities and draw them first And then think about relationships Is there any rule to draw attributes only on the upside? No nothing like that, you can draw attributes anywhere And you don;t even have to draw them ( not a rule) Some top level ER diagrams do not have attributes just to keep them clean, you see this is going to get really really messy now with all the attributes and all the relationships So many times you will see that there are no attributes that are shown in the ERD and you might just say: "Oh, that's incorrect!". But that is not true. It is just that the level of abstraction that they are maintaining. next entity? Profile As I discussed earlier, Profile is going to be our weak entity. about me profile pic When we say profile pic, mostly it is going to store the path of the profile pic, right? Actually, you can store an entire image also. As BLOB. Blog blog Id (bid) title and text we can say content and the tags, yeah I think tags will be multivalued attribute There can be many tags associated with a particular blog #karma #karma2 #karma3 Any other entity that you can think about? they (users) should select some interests like technology or sports Girls??? Political Girls? that's a good interest learn to keep it to yourself Interest interestId (iid) name Any other entity that we can think about? So four entities that we have identified in our BBarters software Now let's talk about relationship between User and Profile User "has a" Profile Now let's talk about cardinality of this relationship One user has strictly one profile One profile belongs to strictly one user So this is a one-to-one relationship and Profile table will have a foreign key on userId which we have not shown because there is a relationship It is implied that there will be some column of some kind user_id here. It is implied so we do not show it here. Now we are saying that Profile is a weak entity because, if there is no User there is no Profile And Profile doesn't have a primary key of its own. It totally depends on its foreign key for its uniqueness. Next we talk about relationship between User and Interest User "choses" interest What will be the cardinality of it? One user can have one or many interests he has to chose one interest (its a business rule of our software) Talking about reverse relationship One interest belongs to zero or many users Can there be any relationship between Interest and Blog See if you have chosen your interest as Technology then you can write Blogs about technology, right? Like if a user chooses his interest as Political Girls, then he can write Blog about Political Girls And I am waiting for that But you have to provide one interest. You can say this blog is about Technology (for e.g). You can't just say this blog is about nothing. So one Blog is strictly "about" one interest. Can one interest have zero or more blogs? Either somebody can write a blog about them (a particular Interest) like many of them (the blogs) or people won't write about particular interest like Political Girls So one interest can have zero or more blogs Can there be a relationship between User and Blog? Yes. Obviously. Who is going to write the blog? users So user "writes" blog What about Cardinality of this? One user can write zero or many blogs Now one blog is written by strictly one user So is it safe to say that Blog (table) will have a foreign key column called as userId Do we show it here? No. Because it is implied. Is that the only relationship possible between a user and a blog? Do you think user only writes the blog and that's that? like My job is done. He also reads the blog. So one more relationship between Blog and User. User "reads" blog. What is the cardinality of this? A user can read zero or many blogs. A blog is read by; zero or many users. Now there is one more thing in our bbarters software, that is Friends. It is a social sharing site so people become friends of each other How do we depict that relationship? "Friends" Now, who is friends with whom? Which are the two entities that are involved in this friendship? Both are users. Like in Facebook, you are both users of Facebook then you are friends. So in this case, User is involved with User in a relationship User "friends" with User So this is called as your recursive relationship When the same entity is in relationship with itself then that is called as Recursive relationship What about the cardinality? A user can be friends with zero or more users And then again, as there are two friends First user can be friends with zero or more other users And the other user also can be friends with other zero or more users So it's actually a many-to-many relationship comments a user "comments" on a blog but then this comments relationship also has some data it stores something (comments) right? so there is one more entity involved in this relationship i.e Comment So this is an example of ternary relationship in this "comments" relationship there are three entities involved User, Comment and Blog three entities are involved that is why it is called as ternary relationship Now how to show cardinality? Now if I want to write this cardinality I will have to consider User and Comment together let us suppose you made a comment e.g "Chuck it!" so this "chuck it!" comment was made by me (a user) So one user and a particular comment ("chuck it!") belongs to how many blogs? that particular comment by that particular user belongs to a particular blog so you could say that it is strictly one If you make that same comment somewhere else, that belongs to the other blog Now if you want to write this cardinality then you have to consider user and blog together for a particular user commenting on a particular blog How many comments he/she can write? zero-to-many for a particular user on a particular blog he can write zero-or-many comments and last here, so we have to consider these two together for a particular blog and a particular comment on that blog is written by - strictly one user this is how you create your ER diagram. It is really fun and really simple. (No sarcasm intended) Awesome. Thank you for watching this video.
