Database Concepts and E-R Diagrams

Core Concepts

• Entities and Entity Sets: A database models objects (entities) and groups of similar entities (entity sets).
• Attributes: Entities have properties (attributes). Attributes can be simple or composite, single-valued or multi-valued, and derived.
• Relationships and Relationship Sets: Associations between entities are relationships, grouped into relationship sets with properties.
• Degree of Relationships: Relationships can be binary (two entities) or n-ary (more than two). Most relationships are binary.
• Mapping Cardinality Constraints: Define the number of matches in relationships (one-to-one, one-to-many, many-to-one, many-to-many).
• Keys: Uniquely identify entities. Super keys determine each entity and candidate keys are minimal super keys. A primary key is a chosen candidate key.

E-R Diagrams

• Graphical Representation: Diagrams use rectangles (entity sets), diamonds (relationship sets), and ovals (attributes). Primary keys are underlined.
• Cardinality Constraints: Show relationship participation: "one" (arrow) or "many" (line).
• Participation Constraints: Total participation (double line) means all entities in a set are involved. Partial participation means not all entities need to be involved.

Weak Entity Sets

• Dependent Entities: Weak entity sets lack their own primary key and rely on an identifying entity set. Depicted by double rectangles and relationships with double diamonds.
• Derived Keys: Primary key for weak entity sets is derived from the identifying strong entity set, and a partial key.

Extended E-R Features

• Specialization/Generalization: Top-down (specialization) or bottom-up (generalization) creation of subgroups within an entity set by combining entities with shared properties.

Constraints

• Condition-defined: Membership based on conditions.
• User-defined: Specified by users.
• Disjoint: Entity belongs to only one lower-level set.
• Overlapping: Entity can belong to multiple lower-level sets.
• Total: Entity must belong to a lower-level set.
• Partial: Entity may not belong to any lower-level set.

Reduction to Relational Schemas

• Schema Creation: Entity and relationship sets are represented as relation schemas (tables).
• Strong Entity Sets: Strong entity sets have schemas with their own attributes.
• Weak Entity Sets: Weak entity sets have schemas with attributes, plus a column referencing the primary key of their identifying strong entity set.
• Relationship Sets: Many-to-many relationships: schemas include primary keys of participating sets and attributes. One-to-many/many-to-one relations (with relation to the "many" side) can use an added attribute in the table.
• Composite and Multivalued Attributes: Composite attributes are separated into attributes. Multivalued attributes have a separate schema, including their primary key and each value.

Design Issues

• Entity vs. Attribute: Defining a concept as an entity or attribute.
• Entity vs. Relationship Set: Determining if to represent a relationship as an entity or a relationship.
• Binary vs. N-ary Relationships: Choice between binary (two entities) or n-ary (three or more entities).
• Strong vs. Weak: Determining whether to use a strong or weak entity set.
• Specialization/Generalization and Aggregation: Using these features for organization and treatment of relationships.

Explore the fundamental concepts in database design, focusing on entities, attributes, relationships, and keys. This quiz also covers E-R diagrams and their graphical representations. Test your understanding of mapping cardinality constraints and the distinction between different types of keys.