A data element is an attribute that cannot be logically decomposed. A set of related data elements forms a data structure or a data composite; for example, the set of attributes associated with each occurrence of an entity is a data structure. The key to an entity is the attribute or group of attributes that uniquely distinguishes one occurrence from all other occurrences. A foreign key is a key to some other entity; for example, a Supplier code might be associated with the ERD for Inventory Management System data.
For a variety of reasons, some relationships are more stable and easier to maintain than others. Cardinality, a measure of the related entity’s relative number of occurrences, is an important predictor of the strength of the relationship. Three types are ;
1. In a one-to-one relationship Can often be collapsed by merging them. means an instance of the first entity is associated with only one instance of the second entity. Similarly, each instance of the second entity is related to one instance of the first entity.
*Note that not all one-to-one relationships can be collapses. For example, imagine a relationship between Athletes and Drug tests. There is one Drug test per Athlete and one Athlete per Drug test, so the relationship is clearly one-to-one. You might argue that Drug test is an attribute of Athlete, but what if the law requires that Drug test data be kept confidential? Because merging the data would make it relatively easy to link a specific person to a specific test result, merging them would probably violate the law, so there is a good logical reason to maintain separate entities.
2. In a one-to-many relationship means that one instance of the first entity is related to many instances of the second entity, while an instance of the second entity is associated with only one instance of the first entity.
*One-to-many relationships tend to be the most stable. Consequently, a primary objective of entity-relationship modeling is to convert one-to-one and many-to-many relationships into one-to-many relationships.
3. In a many-to-many relationship Can cause maintenance problems. An instance of the first entity is related to many instances of the second entity and the same is true in the reverse direction also.
*How does that affect data maintenance? Imagine that Inventory, Item ordered, and Supplier are three files. The file called Item ordered holds product codes and Supplier codes, and it is indexed on both fields. Detailed information on products and/or Suppliers can be obtained by accessing the Inventory file or the Supplier file, as appropriate. Dropping a product affects Inventory and Item ordered, but not Supplier. Adding a Supplier affects Supplier and Item ordered, but not Inventory. Because Item ordered is indexed on both keys, it is easy to maintain.
Other types of ERD for Inventory Management System are multiple relationships between entities, relationships leading to associative entities, relationship of entity with itself, EXCLUSIVE-OR and AND relationships. ERD is a model that identifies the concepts exist in a system and may also be more abstract, not necessarily capturing every table needed within a database, but serving to diagram the major concepts.
Every entity will have many attributes, but only a subset, which are relevant for the system under study, will be chosen. For example, an employee entity will have professional attributes like name, designation, salary, etc. and also physical attributes like height, weight, etc. But only one set will be chosen depending on the context. Attributes are classified as entity keys and entity descriptors.
Attribute Types: They express the properties of the entities.
1. Atomic / Simple Attribute
2. Composite Attributes
3. Single-Valued
4. Multi Valued
5. Stored Attribut
6. Derived Attribute
• Simple (or atomic) attribute can't be broken down into smaller components.
• Composite attributes, can be broken down into component parts.
• Single-valued attribute (represented by a single line ellipse) is an attribute which has only one value in a table.
• Multi-valued attribute has multiple values per instance, such as: all courses taken by a student, shown above. It is represented by double line ellipse
• Stored attribute - a regular attribute
• Derived attribute which is calculated from a stored attribute, and thus should not be shown on the ERD.
Relationships of ERD for Inventory Management System describe the association between entities. Optionality is of two types, namely, mandatory and optional.
1. Mandatory relationship means associated with every instance of the first entity there will be atleast one instance of the second entity.
2. Optional relationship means that there may be instances of the first entity, which are not associated with any instance of the second entity. For example, employee-spouse relationship has to be optional because there could be unmarried employees. It is not correct to make the relationship mandatory.