 Hello and welcome to video lecture on Entity Relationship Diagram. At the end of this session, students will be able to draw Entity Relationship Diagram. An Entity Relationship Diagram can express the overall logical structure of a database graphically. An Entity Relationship Diagram consists of the following major components, rectangles divided into two parts representing Entity Sets. The first part which is shaded blue contains the name of the Entity Set. The second part contains the names of all the attributes of the Entity Set. Diamonds represent relationship sets. Undivided rectangles represent the attributes of a relationship set. Attributes that are part of the primary key are underlined. Diamonds link Entity Sets to relationship sets. Dash lines link attributes of a relationship set to the relationship set. Double lines indicate total participation of an Entity in a relationship set. Double diamonds represent identifying relationship sets linked to the weak Entity Sets. Consider the Entity Relationship Diagram in the given figure, which consists of two Entity Sets, instructor and student, related through a binary relationship set advisor. The attributes associated with instructor are ID, name and salary. The attributes associated with students are ID, name and total credit. Attributes of an Entity Set that are members of the primary key are underlined. If a relationship set has some attributes associated with it, then we enclose the attributes in a rectangle and link the rectangle with a dashed length to the diamond representing that relationship set. In this example, we have the date descriptive attribute attached to the relationship set advisor to specify the date on which an instructor becomes the advisor. Mapping Cardinalities The relationship set advisor between the instructor and student Entity Sets may be one to one, one to many, many to one or many to many. To distinguish among these types, we draw either a directed line or an undirected line between the relationship set and the Entity Set. In one-to-one mapping, we draw a directed line from the relationship set advisor to both Entity Sets instructor and student as shown in the figure. This indicates that an instructor may advise at most one student and a student may have at most one advisor. In one-to-many mapping, we draw a directed line from the relationship set advisor to the Entity Set instructor and an undirected line to the Entity Set student as shown in the figure. This indicates that an instructor may advise many students but as students may have at most one advisor. In many-to-one, we draw an undirected line from the relationship set advisor to the Entity Set instructor and a directed line to the Entity Set student as shown in the figure. This indicates that an instructor may advise at most one student but a student may have many advisors. In many-to-many mapping, we draw an undirected line from the relationship advisor to both Entity Sets instructor and student as shown in the figure. This indicates that an instructor may advise many students and a student may have many advisor. If a relationship set has some attributes associated with it, then we enclose the attributes in a rectangle and link the rectangle with a dashed line to the diamond representing that relationship set. For example, in the given figure, we have the date descriptive attribute attached to the relationship set advisor to specify the date on which an instructor becomes the advisor. Entity relationship diagrams also provide a way to indicate more complex constraints on the number of times each entity participates in a relationship set. A line may have an associated minimum and maximum cardinality as shown in the form of L dot dot H where L is the minimum and H is the maximum cardinality. A minimum value of 1 indicates total participation of the Entity Set in the relationship set. A maximum value of 1 indicates that the entity participates in at most one relationship while a maximum value star indicates no limit. For example, consider a figure the line between advisor and student has a cardinality constraint of 1 dot dot 1 meaning the minimum and the maximum cardinality are both 1 that is each student must have exactly one advisor. The limit 0 dot dot star on the line between advisor and instructor indicates that an instructor can have 0 or more students thus the relationship advisor is 1 to many from instructor to student and further the participation of student in advisor is total implying that a student must have an advisor. It is easy to misinterpret the 0 dot dot star on the left edge and think that the relationship advisor is many to 1 from instructor to student. This is exactly the reverse of the correct interpretation. Figure shows how composite attributes can be represented in the entity relationship notation. Here a composite attribute name the component attributes first name, middle, initial and last name replaces the simple attribute name of instructor. As another example suppose we were to add an address to the instructor entity set the address can be defined as the composite attribute address with the attributes street, city, state and zip code. The attribute street itself a composite attribute whose component attributes are street number, street name and apartment number. Figure also illustrates a multivalued attribute phone number denoted by phone number within curly brackets and a derived attribute is depicted by age followed by round brackets. We indicate role in entity relationship diagrams by labeling the lines that connect diamonds to rectangles. Figure shows the role indicators course ID and prerequisite ID between the course entity set and the prerequisite relationship set. Non-binary relationship sets can be specified easily in an entity relationship diagram. Figure consists of the three entity set instructors, student and project related through a relationship set project guide. We can specify some types of many to one relationships in the case of non-binary relationship sets. Suppose a student can have at most one instructor as a guide on a project. This constraint can be specified by an arrow pointing to instructor on the edge from project guide relationship. We permit at most one arrow out of a relationship set since an entity relationship diagram with two or more arrows out of a non-binary relationship set can be interpreted in two ways. To avoid confusion we permit only one arrow out of a relationship set in which case the two interpretations are equivalent. Now pause the video for some time, answer the following question. UPS prides itself on having up to date information on the processing and current location of each SIP item. To do this, UPS relies on a company-wide information system. Shipped items are the heart of the UPS product tracking information system. Shipped items can be characterized by item number, weight, dimensions, insurance amount, destination and final delivery date. Shipped items are received into the UPS system at a single retail center. Retail centers are characterized by their type, unique ID and address. Shipped items make their way to their destination via one or more standard UPS transportation events. These transportation events are characterized by a unique schedule number, a type and delivery route. Now draw the entity relationship diagram for the following problem statement. This is the answer. Three entities are possible in this problem statement. Shipped item, transportation event and retail centers. Here two relationship sets are possible. Shipped via and received from are the relationships which are drawn in diamond shape. Thank you.