34 cards generated

Save your deck before it's gone

These flashcards aren't stored yet — they'll disappear when you leave. Create a free account to keep them, and unlock everything below.

Keep & study
  • Save this deck to your account
  • Study with spaced repetition
  • Export to Anki (.apkg) or PDF
Bigger & better generations
  • Process documents up to 100 pages
  • Images extracted from your PDFs
  • Sharper text extraction & a more advanced AI model
Sign up free → Free forever · No credit card
ER
  • Info
    Klicke auf 'Speichern & Bearbeiten', um alle Funktionen freizuschalten
    Lernen
    Exportieren
    Bearbeiten
    Bild‑Ausblendung
    Verwalten
Öffentlich

Flashcards in this deck (34)

Suche läuft...

  • What is an entity in the ER model?

    An entity is a distinguishable real-world object described by attributes (e.g., a person, place, or thing).

    er entity

  • What is an entity set?

    An entity set is a group of similar entities, e.g., the table of all students.

    er entityset

  • How is a relationship defined in the ER model?

    A relationship is a connection between entities, represented as a tuple of their primary keys, e.g., \(\text{advisor}=\{(76766,\ 98988),\dots\}\).

    er relationship

  • Can a relationship have descriptive attributes?

    Yes. A relationship can carry descriptive attributes, e.g., \(\text{advisor}=\{(76766,\ 98988,\ \text{2023-10-01}),\dots\}\).

    er relationship attributes

  • What is the degree of a relationship?

    The degree is how many entity sets are involved in the relationship.

    er degree

  • Give an example of a binary and a ternary relationship.

    • Binary: Instructor advises Student
    • Ternary: Student works on Project guided by Instructor
    er degree examples

  • What are the four mapping cardinality types listed?

    • One-to-One
    • One-to-Many
    • Many-to-One
    • Many-to-Many
    er cardinality

  • What does total participation mean and how is it notated?

    Every entity must participate in the relationship; notation: double line ══.

    er participation

  • What does partial participation mean and how is it notated?

    An entity may participate in the relationship; notation: single line ─.

    er participation

  • What shapes represent entity sets, relationships, and attributes in ER diagrams?

    • Rectangle: Entity Set
    • Diamond: Relationship Set
    • Oval: Attribute
    er diagram

  • How is a primary key shown in ER diagram notation?

    A primary key is shown as an underlined attribute.

    er keys

  • What does an arrow (→) and a plain line (─) indicate in ER diagrams?

    Arrow (→) indicates the 'to-one' side; plain line (─) indicates the 'many' side.

    er diagram cardinality

  • In the example, what participation is shown for instructors and students in the advisor relationship?

    Instructor has total participation (every instructor advises someone); Student has partial participation (some students have no advisor).

    er example participation

  • What does an entity set become when translating an ER diagram to a relational schema?

    An entity set becomes a table with the same name, attributes, and primary key.

    er translation entities

  • What does a relationship set become in the relational schema translation?

    A relationship set becomes a table containing the primary keys of participating entities, any descriptive attributes, and foreign key constraints.

    er translation relationships

  • How should you choose the primary key for a relationship table in a many-to-many relationship?

    Use the combined primary keys of both participating entity tables as the relationship table's primary key.

    er cardinality many-to-many

  • What primary key is used for a one-to-many relationship table?

    Use the key of the many side as the primary key of the relationship table.

    er cardinality one-to-many

  • When can you avoid creating a separate relationship table and instead add a foreign key column?

    If the relationship is total on one side and to-one on the other, add a foreign key column into the total entity's table instead of a separate table.

    er optimization foreign-key

  • Why must you not use the 'no extra table' trick for partial to-one relationships?

    Because partial to-one relationships would create NULL values in the foreign key column since some entities may not participate.

    er optimization nulls

  • What rule of thumb decides if something should be an attribute or an entity?

    If it has its own properties or multiple entities can share it, make it an entity; otherwise use an attribute.

    design attribute-vs-entity

  • When should a relationship be modeled as an entity instead of a relationship?

    If the interaction can occur more than once between the same pair (duplicates), model it as an entity with its own ID and attributes.

    design relationship-vs-entity

  • What do these ER shapes represent: rectangle, diamond, text/oval, underline?

    • Rectangle = Entity Set
    • Diamond = Relationship Set
    • Text/Oval = Attribute
    • Underline = Primary Key
    er notation shapes

  • What do the arrow, no-arrow, double line, and single line denote in ER diagrams?

    • Arrow (→) = to-one (at most one)
    • No arrow (─) = many
    • Double line (══) = total participation
    • Single line (─) = partial participation
    er notation cardinality

  • Give the example of a recursive relationship shown in the notes.

    A course can be a prerequisite for another course; the same entity type plays two roles labeled course_id and prereq_id.

    er recursive example

  • What does the ER model map from and to?

    The ER model maps real-world entities and relationships into a design and rules to convert that design into relational tables.

    ermodel overview

  • How is an Entity Set translated to a relational schema?

    Entity Set → Table (same name, same attrs, same PK)

    translation entity

  • How is a Relationship translated to a relational schema?

    Relationship → Table (entity PKs + descriptive attrs)

    translation relationship

  • How should a Many-to-Many relationship be represented in the relational schema?

    Many-to-Many → PK = union of both entity PKs

    translation many-to-many

  • What is the primary key rule for a One-to-Many translation?

    One-to-Many → PK = 'many' side key

    translation one-to-many

  • How is a One-to-One relationship translated?

    One-to-One → PK = either key, UNIQUE on the other

    translation one-to-one

  • What should you do when a relationship is Total and To-One?

    Total + To-One → Merge! Add FK column to 'total' entity table

    translation total

  • When should repeating or shared data be modeled as an entity?

    If data is repeating or shared → Entity, not attribute

    design attribute

  • When should an event be modeled as an entity rather than a relationship?

    If an event can happen multiple times → Entity, not relationship

    design event

  • Name core design elements to track in ER design.

    • Entities, attributes, primary keys
    • Relationships and descriptive attributes
    • Cardinality and participation constraints
    • ER diagram notation and roles
    • Translation rules to relational schemas
    • Design decisions: attribute vs entity
    concepts summary
Lernnotizen

ER Model — Quick, Practical Notes

Think of the ER (Entity-Relationship) model as a blueprint for a database: it maps real-world things and their links into a design you can implement as tables.

Core definitions

  • Entity: a distinguishable real-world object (a "noun") described by attributes, e.g., Instructor, Student, Course.
  • Attribute: a property of an entity, e.g., name, salary, credits.
  • Primary key: an attribute (or set) that uniquely identifies an entity instance, usually underlined in diagrams.
  • Entity set: a collection of similar entities (like a table of all students).

Relationships

  • Relationship: a connection between entities (a "verb"); represented as tuples of primary keys.
  • Example (relationship as a set):
\[ \text{advisor} = \{(76766,\ 98988),\ (45565,\ 12345),\ \ldots\} \]
  • Relationships can have descriptive attributes (e.g., date):
\[ \text{advisor} = \{(76766,\ 98988,\ \text{2023-10-01}),\ \ldots\} \]
  • Important constraint: a relationship is a set — you cannot store duplicate identical tuples; if you need repeated occurrences (e.g., meetings on different dates) model them as an entity.

Degree of a relationship

  • Degree = number of entity sets involved.
  • Common types: binary (2 entities), ternary (3 entities). Most relationships are binary.

Cardinality (mapping) — how many to how many

  • One-to-one (1:1): each A links to at most one B and vice versa.
  • One-to-many (1:N): each A links to many B; each B to at most one A.
  • Many-to-one (N:1): symmetric description of 1:N.
  • Many-to-many (M:N): many on both sides.

Notation shortcuts often used in diagrams: arrow (→) marks the "to-one" side; no arrow or a crow's foot marks the "many" side.

Participation (existence constraint)

  • Total (mandatory): every entity in the set must participate; drawn as a double line ══.
  • Partial (optional): entities may or may not participate; drawn as a single line ─.
  • Example: if every instructor must advise at least one student, instructor has total participation; if some students have no advisor, student has partial participation.

ER diagram notation (visual grammar)

  • Rectangle = Entity set (e.g., instructor).
  • Diamond = Relationship set (e.g., advisor).
  • Oval or listed text = Attribute.
  • Underlined attribute = Primary key.
  • Dashed line to relationship = Descriptive attribute of the relationship (e.g., date).
  • Arrow → = "to-one" (at most one). No arrow = many.
  • Double line ══ = total participation.

Text diagram example (compact):

instructor ═══════ advisor ───────── student [ID, name, salary] [ID, name, tot_cred] (date)

  • Here, double line on instructor side = total; single line on student side = partial. Arrow points to the "one" side.

Roles and self-relationships

  • When an entity relates to itself (e.g., course prerequisites), label the participating roles to disambiguate (e.g., course_id, prereq_id).
  • Roles clarify which occurrence of the same entity type is playing which part in the relationship.

Translating ER diagrams → Relational schemas (practical steps)

  1. Entity sets → Tables.
  2. Create a table per entity set with same attributes and same PK.

  3. Example: student(ID, name, tot_cred) with ID as PK.

  4. Relationship sets → Tables or FKs.

  5. Relationship tables include primary keys of participating entities + descriptive attributes.

  6. Primary key choice depends on cardinality:

    • M:N → PK = combination of both entity keys (composite key).
    • 1:N → PK = key of the many side.
    • 1:1 → PK = either side; put UNIQUE on the other key.

  7. Optimization — merge when safe:

  8. If a relationship is total on one side and to-one on the other, you can avoid a separate relationship table by adding a foreign key column to the total entity's table.
  9. Example SQL alternative (instead of a separate advisor table):

-- instructor advises exactly one student (total & to-one) instructor(ID, name, salary, s_id) -- s_id is FK → student(ID)

  • Do NOT do this for partial to-one relationships if it would force meaningful NULLs.

Design decisions: attribute vs entity, relationship vs entity

  • Attribute vs Entity:
  • If a thing can be shared by multiple entities or has its own properties, model it as an entity (e.g., phone with its own data).

  • If it is a simple, unique property owned by one entity, keep it as an attribute.

  • Relationship vs Entity:

  • If a connection between the same pair can occur multiple times (with different dates or other properties), model it as an entity (an event) not as a plain relationship.
  • Example: repeated meetings → use a meeting entity with its own ID and date:

meeting(m_id, date) -- m_i → instructor -- m_s → student

  • Reason: relationships are sets (no duplicate tuple copies); repeated occurrences need distinct keys.

Compact cheat sheet (memory aid)

  • SHAPES: Rectangle = Entity, Diamond = Relationship, Oval = Attribute, Underline = PK.
  • PARTICIPATION: ══ = total, ─ = partial.
  • ARROWS: → = to-one, no arrow = many.
  • TRANSLATION SUMMARY:
  • Entity → Table (same PK + attrs).
  • Relationship → Table (entity PKs + attrs) unless you can merge safely.
  • M:N → composite PK; 1:N → many-side PK; 1:1 → either + UNIQUE.
  • DESIGN RULES:
  • Shared or multi-property items → Entity.
  • Repeated events between same pair → Entity (not relationship).

Typical pitfalls to watch for

  • Treating repeatable interactions as relationships (loses duplicates).
  • Merging FKs incorrectly for optional to-one relationships (introduces NULLs or incorrect constraints).
  • Forgetting to add UNIQUE when choosing which side's key to use in 1:1 translations.

Quick examples (reference)

  • M:N advisor table:

advisor(i_id, s_id) -- PK = (i_id, s_id) i_id → instructor(ID) s_id → student(ID)

  • 1:N where many side is student:

advisor(i_id, s_id) -- PK = s_id

  • 1:1 choice requires UNIQUE on the non-PK side.

Keep these rules as a checklist while designing: pick entities when data repeats or has structure, pick entities for repeatable events, and follow the PK rules when mapping relationships to tables.