OPIM - knowledge check 2

Entity-Relationship (ER) Model

a high-level representation of data requirements. It focuses on what data is needed while ignoring implementation details like specific software.

Entity

A person, place, product, concept, or activity.

Relationship

A statement about two entities

Reflexive Relationship

A special case where an entity relates to itself.

Attribute

A descriptive property of an entity

Implementation Rule

Types usually become tables/columns, while Instances become the actual rows/data values

ER Diagram (ERD)

A visual tool

Entities

Rectangles with rounded corners

Relationships

Lines connecting the rectangles

Attributes

Placed inside the entity rectangles

Naming Convention

Written as "Entity-Relationship-Entity" and read clockwise.

Glossary (Data Dictionary)

A text-based document (repository) containing synonyms and descriptions that complement the diagram

Conceptual Design

Developing the ER model and capturing requirements (Ignoring implementation).

Logical Design

Converting the ER model into tables, columns, and keys for a specific system.

Physical Design

Adding indexes and specifying how data is organized on storage media.

Entities

Usually nouns (e.g., Person, Product).

Relationships

Usually verbs (e.g., Manages, Belongs To)

Attributes

Nouns that denote specific data (e.g., Names, Dates, Amounts).

Entity

The name of the entity it describes

Qualifier

Describes the meaning (e.g., "First", "Last").

Type

A standard category like "Name", "Number", or "Count".

Conceptual Design

Develops the ER model; ignores implementation details.

• Step 1: Discover entities, relationships, and attributes.

• Step 2: Determine cardinality.

• Step 3: Distinguish strong/weak entities.

• Step 4: Create supertype/subtype entities.

Logical Design

Converts the ER model into tables, columns, and keys for a specific system.

• Step 5-7: Implement entities, relationships, and attributes.

• Step 8: Apply Normal Form.

Physical Design

Adds indexes and specifies storage on media.

ER Diagrams

Depict entities as rectangles with rounded corners, relationships as lines, and attributes inside entity rectangles. Attributes are considered optional detail.

The Glossary

Also called a data dictionary or repository. It documents names, synonyms, and full-sentence descriptions.

Relationship Names

Read the full name clockwise around the center of the relationship.

Relationship Maximum (The "Crow's Foot")

This represents the greatest number of instances of one entity that can relate to another.

• Singular (One): Shown as a short bar across the relationship line.

• Plural (Many): Shown as three short lines that look like a bird's foot (Crow's Foot).

Relationship Minimum (Required vs. Optional)

This represents the least number of instances that can relate.

• Required (One): Shown as a short bar.

• Optional (Zero): Shown as a circle.

• Placement Tip: In a diagram, maxima symbols appear next to the entity, while minima symbols appear farther away.

Attribute Cardinality

• Attribute Maximum: Plural attributes are marked with a "P".

• Attribute Minimum: Required attributes are marked with an "R".

• Default: Unless marked, attributes are presumed to be singular and optional.

Unique Attributes

• Unique Attribute: Each value describes at most one entity instance (e.g., VIN number).

• Unique Composite: A combination of attributes that is unique (e.g., Airline Code + Flight Number).

• Notation: In diagrams, unique attributes are followed by a "U".

Cardinality

refers to the maxima and minima of relationships and attributes.

Singular (One)

A short bar across the line.

Plural (Many)

Three short lines converging at a point (Crow's Foot).

Required (One)

A short bar

Optional (Zero)

A circle

“P”

Plural attribute

“R”

Required attribute

“U”

Unique attribute

Strong Entity

Has one or more identifying attributes (unique, singular, and required). One often becomes the Primary Key.

Weak Entity

Does not have an identifying attribute. It depends on an identifying relationship to an identifying entity.

Identifying Relationship Notation

Shown with a diamond next to the identifying entity on the ER diagram.

The 3 Phases of Database Design

1. Conceptual Design: Captures requirements; ignores implementation. (Steps 1–4: Discovery, Cardinality, Strong/Weak, Supertype/Subtype).

2. Logical Design: Converts ER model to tables/keys for a specific system. (Steps 5–8: Implement objects, Apply Normal Form).

3. Physical Design: Adds indexes and specifies storage on media.

Supertype

The "Parent" entity. It contains the common attributes shared by all subgroups (e.g., EmployeeID, Name, HireDate).

Subtype

The "Child" entity. It contains only the attributes unique to that specific group (e.g., HourlyRate for Hourly employees, or Degree for Faculty).

Inheritance

Every instance of a subtype "inherits" all attributes and relationships from the supertype.

Primary Keys

The primary key of the supertype and all its subtypes must be identical.

Relationships

Relationships connected to the supertype apply to all subtypes. Relationships connected to a specific subtype apply only to that subtype.

Specialization (Top-Down)

Starting with a general entity (e.g., Student) and breaking it into more specific ones (Undergrad, Graduate) based on differing characteristics.

Generalization (Bottom-Up)

Noticing that several entities (e.g., Car, Truck, Motorcycle) share common traits and grouping them under a higher-level entity (Vehicle).

Identifying Relationship

A relationship where the Primary Key of the parent entity is required to uniquely identify the child entity.

Non-Identifying Relationship

A relationship where the child entity has its own unique ID and doesn't "need" the parent to be identified

The Mechanics of Foreign Keys (FK)

A Foreign Key is how we actually "do" a relationship in a real database. It is a column in one table that points to the Primary Key of another.

Where does the FK go?

In a 1:M (One-to-Many) relationship, the Foreign Key always goes on the "Many" side.

Example: One Department has Many Employees. The DepartmentID (FK) goes into the Employee table.

Referential Integrity

A rule stating that every Foreign Key value must match an existing Primary Key value in the parent table. (You can't have an employee in Department #99 if Department #99 doesn't exist!)

Composite Key

A Primary Key made of two or more attributes (e.g., StudentID + ClassID to identify a specific Enrollment).

Concatenation

The formal term for combining these attributes

Unary (Recursive) Relationships

A Unary Relationship occurs when an entity has a relationship with itself. This is often called a Recursive relationship.

Primary/Foreign Key Rule: In a 1:M unary relationship, the Foreign Key and the Primary Key are in the same table.

Ternary Relationships

A Ternary Relationship involves three different entities associated through a single relationship.

The "Diamond" Rule

In many ER notations (like Chen or standard academic models), a ternary relationship is shown as a diamond connected to three rectangles.

Complexity Warning

Many database tools (and even logical design steps) require you to convert these into a "Weak Entity" or an "Associative Entity" (see below) because most software can only handle binary (two-way) links.

The Five Main Rules for Tables (Relations)

1. Values are Atomic: Each cell must contain exactly one value. No lists or multiple pieces of data in one box (e.g., you can't put two phone numbers in one "Phone" cell).

2. Attribute Names are Unique: You cannot have two columns named "Phone" in the same table.

3. All Rows are Unique: No two rows can be identical. This is why every table needs a Primary Key.

4. Sequence of Columns is Irrelevant: The data doesn't change meaning if you swap the "Name" and "Phone" columns.

5. Sequence of Rows is Irrelevant: The data doesn't change meaning if you sort by Last Name vs. ID.

Entity Integrity Rule

The Primary Key cannot be NULL (empty). Every row must be identifiable.

Referential Integrity Rule

A Foreign Key value must either match an existing Primary Key in the related table or be NULL (if the relationship is optional). This ensures you don't have an "orphan" record pointing to a non-existent parent.

Mapping 1:M (One-to-Many) Relationships

This is the most common relationship in database design.

• The Rule: Take the Primary Key from the "One" side and place it as a Foreign Key in the table on the "Many" side.

• Example: One Department has many Employees.

  • Action: Put DepartmentID into the Employee table.

Mapping M:N (Many-to-Many) Relationships

Relational databases cannot handle M:N relationships directly between two tables.

• The Rule: Create a new table (often called an Intersection Table or Associative Relation).

• The Components: This new table will have:

  1. The Primary Key of Table A.

  2. The Primary Key of Table B.

  3. Any attributes that belonged to the relationship itself (like Date or Quantity).

• The PK of the New Table: Usually a Composite Key made of both Foreign Keys.

Mapping 1:1 (One-to-One) Relationships

These are rarer and require a choice.

• The Rule: You can put the Foreign Key in either table, but the best practice is to put it in the table with Mandatory Participation (the side that "must" have a record).

• Example: One Employee has one Office. If every office must have an employee, put EmployeeID in the Office table.

Mapping Weak Entities (Identifying Relationships)

Recall that a Weak Entity doesn't have a full identity of its own.

• The Rule: The table for the weak entity must include the Primary Key of the Identifying (Strong) Entity as part of its own Composite Primary Key.

• Visual: In your schema, you will see two underlined attributes in the weak entity table.