[DB DESIGN] “All students in a university” is an example of what database design concept?
Entity set (or Entity class).
WHY: In database modeling, an entity set represents a broad collection of objects or concepts of the same type that share the same attributes.
[DB DESIGN] Reinforcement: Define an entity set in plain English.
A collection of similar items that share the same properties and will eventually become a table in your database.
MNEMONIC: SET means a STACK OF EQUIVALENT THINGS.
[DB DESIGN] Mastery: Business Scenario: If “Student” is an entity set, what is “John Doe, ID 12345”?
An entity instance (or simply an entity).
EDGE CASE: An entity set can technically be empty in the database before the first student record is actually created.
[DB DESIGN] A small company database has an Order entity that cannot exist without a Customer. Each order is identified only by the customer who placed it. What type of entity is this?
Weak entity.
WHY: It lacks a primary key of its own and depends entirely on an identifying relationship with a parent entity to exist.
[DB DESIGN] Reinforcement: Define a weak entity in plain English.
An entity that requires a relationship with another specific entity to exist and be uniquely identified.
MNEMONIC: WEAK entities need a STRONG parent to survive.
[DB DESIGN] Mastery: Business Scenario: If a weak entity “Dependent” is linked to an “Employee”, what happens if the “Employee” record is deleted?
The “Dependent” records must also be deleted (known as a cascade delete), because a weak entity cannot exist in the database without its identifying parent.
[DB DESIGN] “Manager manages Employee”, where both are in the same Employee table, is an example of what type of relationship?
Unary relationship (or Recursive relationship).
WHY: An entity type is relating back to itself within the same table.
[DB DESIGN] Reinforcement: Define a unary relationship in a database.
A relationship that exists between instances of a single, identical entity type.
MNEMONIC: UNARY means UNO, one table relating to itself.
[DB DESIGN] Mastery: Business Scenario: How do you physically implement a unary relationship like “Employee manages Employee” in SQL?
Add a “ManagerID” column to the Employee table that functions as a foreign key pointing to the same table’s “EmployeeID” primary key.
[DB DESIGN] What does “modality” refer to in a database relationship?
The minimum number of entity occurrences that can be involved in a relationship (typically 0 for optional, or 1 for mandatory).
WHY: Modality differentiates between what CAN happen and what MUST happen in the business logic.
[DB DESIGN] Reinforcement: Define modality in database design in plain English.
The structural rule determining if participating in a relationship is optional or required.
MNEMONIC: MODALITY means MANDATORY OR DISCRETIONARY.
[DB DESIGN] Mastery: Business Scenario: If a customer can register an account without placing an order, what is the modality of the Order entity?
Optional (0). The minimum number of orders required for a newly registered customer is zero.
[DB DESIGN] If a student must be enrolled in at least one course, the modality for “Course” in the “Student-EnrollsIn-Course” relationship is:
Mandatory (or 1).
WHY: The word “must” implies a strict minimum requirement of 1, meaning the relationship is not optional.
[DB DESIGN] Reinforcement: Define a mandatory relationship constraint.
A condition where an entity must actively participate in a relationship for the record to be valid.
MNEMONIC: MANDATORY means MUST HAVE.
[DB DESIGN] Mastery: Business Scenario: How is a mandatory modality enforced at the table level in an SQL database?
Using constraints like NOT NULL on foreign keys, or utilizing application-level validation triggers before an INSERT occurs.
[DB DESIGN] An associative entity is used to represent what kind of relationship?
A many-to-many (M:N) relationship.
WHY: Relational databases cannot directly store many-to-many relationships, so an associative table breaks it into two readable one-to-many relationships.
[DB DESIGN] Reinforcement: Define an associative entity in plain English.
A table created purely to connect two other tables that have a many-to-many relationship.
MNEMONIC: ASSOCIATIVE entities ASSOCIATE many with many.
[DB DESIGN] Mastery: Business Scenario: Can an associative entity have its own data attributes?
Yes. For example, an Enrollment associative entity connecting Students and Courses will typically store the “Grade” for that specific student-course combination.
[DB DESIGN] The database tracks that a doctor prescribes a medication to a patient. What type of relationship is this?
Ternary relationship (Degree 3).
WHY: The specific event (prescription) simultaneously involves three distinct entities: Doctor, Patient, and Medication.
[DB DESIGN] Reinforcement: Define a ternary relationship.
A simultaneous association among instances of three completely different entity types.
MNEMONIC: TERNARY equals THREE interacting tables.
[DB DESIGN] Mastery: Business Scenario: How do you resolve a ternary relationship into a relational database schema?
Create a central associative entity that contains foreign keys pointing to all three participating primary entities (e.g., DoctorID, PatientID, and MedicationID).
[DB DESIGN] A “required” attribute in an Entity-Relationship Diagram is typically indicated by:
Bold text in ERDs, and implemented as a NOT NULL constraint in the physical database schema.
WHY: It prevents the database from accepting a row if that specific mandatory column is empty.
[DB DESIGN] Reinforcement: Define a required attribute constraint.
A structural rule stating a specific data field cannot be left blank during data entry.
MNEMONIC: REQUIRED means REJECTED IF BLANK.
[DB DESIGN] Mastery: Business Scenario: What happens if an application tries to insert a record missing a required attribute?
The Database Management System (DBMS) will throw a fatal error and abort the entire transaction to maintain strict data integrity.
