Database Systems: ER Diagrams and Relationships

Attributes

• Single-value attributes can have only a single value.
• Multivalued attributes can have many values.
• Stored attributes must be entered by the user and stored in the database.
• Derived attributes are calculated from other attributes and are not required to be stored in the database.
• M:N relationships and multivalued attributes should not be implemented in relational tables.
• Create several new attributes for each of the original multivalued attributes' components.
• Create a new entity composed of original multivalued attributes' components.

Attributes Representation in ERD

• Chen notation: attributes are represented by an oval containing its name and connected to the entity with a line.
• Crow's Foot notation: attributes are written in an attribute box below the entity rectangle.

Types of Entities

Regular/Strong Entities

• Existence does not depend on any other entity.
• Has a key attribute.
• Examples: Employees, Departments, Projects, and Students entities.

Weak/Subordinate Entities

• Existence depends on another entity.
• Has no key attributes of its own, but may have a Partial key.
• Weak entities are identified by the combination of their Partial key (if exists) and Primary key of its identifying entity.
• Examples: Dependent and Invoice entities.

Associative/Composite/Bridge Entities

• Existence depends on two or more entities.
• Used to implement M:N relationship between entities.
• Composed of all key attributes of all connected entities.
• May contain additional attributes that play no role in the connective process.
• Has no key attributes of its own, but may have a Partial key.
• Associative entities are identified by the combination of their Partial key (if exists) and all Primary keys of their identifying entities.
• Examples: student grades depend on the student and the course.

Generalization

• Encapsulates common characteristics of many subordinate entities.
• Defines a set-subset relationship between a class and a set of member classes.
• Used with Enhanced Entity Relationship Diagram (EER/E2R).
• Examples: a four-wheeler is a type of vehicle, and a truck is a type of four-wheeler.

Aggregation

• Consists of or an aggregation of other entities.
• Defines a new entity from a set of entities which are identified as components of the root entity.
• Used with Enhanced Entity Relationship Diagram (EER/E2R).
• Examples: a car consists of engine, chassis, gear box, etc.

Relationships

• Association between entities.
• Participants are entities that participate in a relationship.
• Relationships between entities always operate in both directions.
• More than one relationship type can exist with the same participating entity types.
• The relationship type has three main characteristics: Degree, Strength, and Cardinality/Constraints.
• A weak entity must participate in an identifying relationship type with an owner or identifying entity type.

Relationships Degree

• Unary/Recursive: Relationship between an entity and itself.
• Binary: Relationship between two different entities.
• Ternary: Relationship between three different entities.
• Nary: Relationship between N different entities.