SQL & Databases Data Modeling and Database Design Syllabus

Data Modeling and Database Design syllabus — full chapter and topic list

Expand any chapter to see its topics and sub-topics. This is the whole examinable outline for Data Modeling and Database Design in SQL & Databases, not a summary of it.

1. 1

   Conceptual and Logical Modeling

   4 topics

   • Entity-Relationship Modeling
   • ER Diagram Notations
   • Weak Entities and Identifying Relationships
   • Generalization and Specialization
2. 2

   Normalization

   5 topics

   • Functional Dependencies
   • First Through Third Normal Form
   • Boyce-Codd Normal Form (BCNF)
   • Higher Normal Forms (4NF, 5NF)
   • Anomalies and Redundancy
3. 3

   Denormalization and Trade-offs

   3 topics

   • When to Denormalize
   • Read vs Write Trade-offs
   • Materialized Aggregates
4. 4

   Physical Database Design

   4 topics

   • Mapping Logical to Physical Schema
   • Choosing Data Types and Storage
   • Partitioning Strategies
   • Naming Conventions and Standards
5. 5

   Schema Patterns and Anti-Patterns

   4 topics

   • Many-to-Many and Junction Tables
   • Modeling Hierarchies
   • Slowly Changing Dimensions
   • Common Anti-Patterns (EAV, Polymorphic FKs)
6. 6

   Schema Evolution and Migrations

   3 topics

   • Versioned Migrations
   • Backward-Compatible Changes
   • Zero-Downtime Schema Changes

Data Modeling and Database Design flashcards for SQL & Databases

18 of 51 cards from the Data Modeling and Database Design deck — real questions with worked answers.

1. What is Entity-Relationship (ER) modeling?

   A conceptual, top-down data-modeling technique that represents the real-world problem domain as a collection of entities (things of interest), the attributes that describe them, and the relationships that connect them, independent of any specific DBMS or physical implementation.

2. In ER modeling, define an entity, an attribute, and a relationship.

   An entity is a distinguishable real-world object or concept about which data is stored (e.g., Student). An attribute is a property that describes an entity or relationship (e.g., Name). A relationship is an association among two or more entities (e.g., Student enrolls in Course).

3. Distinguish an entity type from an entity instance (entity set).

   An entity type is the schema-level definition/template (e.g., Employee with attributes ID, Name). An entity instance is a single occurrence of that type. The entity set is the collection of all instances of the entity type at a given time.

4. List the main categories of attributes in ER modeling.

   Simple (atomic) vs. composite; single-valued vs. multivalued; stored vs. derived; and key attributes (uniquely identify an instance). A null value may indicate not-applicable, unknown, or missing.

5. What is a derived attribute versus a stored attribute?

   A stored attribute's value is physically kept in the database. A derived attribute's value is computed from other attributes when needed (e.g., Age derived from DateOfBirth), so it is not stored to avoid redundancy.

6. In classic Chen ER notation, what shapes represent entities, attributes, and relationships?

   Rectangles represent entities, ellipses (ovals) represent attributes, and diamonds represent relationships. Lines connect them; double/dashed ovals denote multivalued/derived attributes, and underlined attribute names denote keys.

7. How are multivalued and composite attributes drawn in Chen notation?

   A multivalued attribute is a double-lined ellipse; a derived attribute is a dashed ellipse; a composite attribute is an ellipse connected to its component sub-attribute ellipses; a key attribute has its name underlined.

8. What does cardinality ratio mean, and what are its four standard types in binary relationships?

   Cardinality ratio specifies the maximum number of relationship instances an entity can participate in. The four types are one-to-one ($1{:}1$), one-to-many ($1{:}N$), many-to-one ($N{:}1$), and many-to-many ($M{:}N$).

9. In ER modeling, distinguish total participation from partial participation.

   Total (mandatory) participation means every entity instance must participate in the relationship, drawn as a double line. Partial (optional) participation means some instances may not participate, drawn as a single line. Together with cardinality this forms (min, max) constraints.

10. What do the (min, max) participation constraints on an entity in a relationship express?

    They give the minimum and maximum number of relationship instances a single entity instance can take part in. min $=0$ means partial participation; min $\geq 1$ means total participation; max gives the cardinality limit.

11. What is a weak entity, and how does it differ from a strong (regular) entity?

    A weak entity cannot be uniquely identified by its own attributes alone and has no key of its own; it depends on an owner (strong/identifying) entity for its existence and identification. A strong entity has its own primary key and exists independently.

12. Define an identifying relationship and a partial key for a weak entity.

    An identifying (existence-dependent) relationship is the association that connects a weak entity to its owner strong entity. A partial key (discriminator) is the set of attributes that uniquely identifies weak-entity instances only within the context of a single owner.

13. How is the full primary key of a weak entity formed?

    By combining the primary key of its owner (identifying) entity with the weak entity's own partial key (discriminator): $\text{PK}_{\text{weak}} = \text{PK}_{\text{owner}} \cup \text{partial key}$.

14. How are weak entities and identifying relationships drawn in Chen notation?

    A weak entity is a double-lined rectangle, the identifying relationship is a double-lined diamond, the partial key attribute has a dashed underline, and the weak entity has total participation (double line) in the identifying relationship.

15. What is generalization in ER/EER modeling?

    A bottom-up abstraction process that combines two or more lower-level entity types sharing common attributes into a single higher-level (generalized) superclass entity type, factoring out the shared attributes.

16. What is specialization, and how does it relate to inheritance?

    Specialization is the top-down process of defining subclasses (subtypes) of a superclass based on distinguishing characteristics. Subclasses inherit all attributes and relationships of the superclass and add their own specific ones (attribute/relationship inheritance).

17. Define the disjoint vs. overlapping constraint in specialization/generalization.

    The disjointness constraint states whether an entity may belong to more than one subclass. Disjoint (d) means an instance is a member of at most one subclass; overlapping (o) means an instance may belong to several subclasses simultaneously.

18. Define the total vs. partial completeness constraint in a specialization.

    Completeness specifies whether every superclass member must belong to some subclass. Total (double line) requires every superclass instance to be a member of at least one subclass; partial (single line) allows a superclass instance to belong to none.

See more Data Modeling and Database Design flashcards →

Planning Data Modeling and Database Design for SQL & Databases

Data Modeling and Database Design (SQL & Databases) FAQ