wunder beta📘 How do you design a database from requirements?
Requirements, ERDs, and normalization—how a database project turns messy asks into a schema you can query.
What you’ll learn
- Scoping the Database Project: Requirements and the Design LifecycleFrame a database project by gathering requirements and locating your work within the conceptual-logical-physical design lifecycle.A database project is structured by the database design lifecycle, which separates the abstract questions of what data means (conceptual), how it maps to the relational model (logical), and how it runs on a specific DBMS (physical). It begins with requirements collection and analysis, whose central deliverable is a requirements specification that names the data, the operations on it, and the rules that govern it. Treating these phases as distinct keeps design decisions traceable and prevents premature commitment to implementation details. The data dictionary, started here, becomes the shared vocabulary for everything that follows.
- Conceptual Modeling: Entities, Relationships, and Business RulesBuild a conceptual entity-relationship model that captures entities, attributes, relationships, and the constraints implied by business rules.Conceptual design turns the requirements specification into an entity-relationship model that represents the problem domain independently of any DBMS. The core constructs are entity types, attributes, relationship types, and the structural constraints - multiplicity (cardinality) and participation - that encode the organization's business rules. Identifying keys and resolving identity early makes the later mapping to relations mechanical. A good conceptual model is judged by how faithfully it reflects the requirements, not by how it will be stored.
- Logical Design: Mapping to Relations and NormalizingTransform the conceptual model into a normalized relational schema using functional dependencies and the normal forms through 3NF/BCNF.Logical design maps the conceptual model onto the relational model: entities become relations, attributes become columns, keys become primary keys, and relationships are realized through foreign keys or junction tables. The resulting schema is then refined by normalization, which uses functional dependencies to remove update anomalies by progressing through 1NF, 2NF, 3NF, and BCNF. Normalization is a validation technique - it checks and improves a design rather than replacing modeling judgment. The output is a relational schema with relations, keys, and integrity constraints, still independent of any particular DBMS product.
- Physical Design, SQL, and TransactionsImplement the logical schema in SQL on a real DBMS and reason about indexing and transaction guarantees that keep the data correct and performant.Physical design realizes the logical schema on a specific DBMS: defining tables and constraints in SQL DDL, choosing data types and storage, and adding indexes to support the workload. SQL is standardized as ISO/IEC 9075, so core DDL and DML are portable across vendors even though products extend it. Correctness under concurrency and failure is provided by transactions, which guarantee the ACID properties; the SQL standard defines isolation levels that trade strictness against concurrency. Physical design choices should be driven by the actual query and update workload, measured rather than guessed.
- Building and Defending Your Database Project (Capstone)Assemble the full deliverable - from requirements to a working SQL implementation - and defend your design decisions in a case memo.The capstone integrates every phase into one coherent artifact: a scoped requirements specification, a validated conceptual model, a normalized logical schema, and a physical implementation in SQL with sample data and queries. Equally important is the design rationale - documenting why each significant choice was made and which requirement or business rule it satisfies. The case memo is where you present this reasoning concisely, anticipate trade-offs, and show that the implementation actually answers the user transactions you set out to support. A strong project is traceable end to end: every table earns its place from a requirement.
Questions this course answers
Which deliverable is the primary output of the requirements collection and analysis phase?
Requirements collection and analysis precedes modeling; its central deliverable is the requirements specification, which consolidates functional requirements (the data and operations needed) and non-functional qualities such as performance, security, and compliance. Tables, ER diagrams, and physical schemas are produced in later phases.
Designing in separate conceptual, logical, and physical phases primarily supports which property?
Layering the design isolates DBMS- and storage-specific decisions in the physical level, leaving the conceptual model (meaning) stable when the product or storage strategy changes. That insulation is data independence. Atomicity, referential integrity, and index performance are concerns addressed within specific phases, not the reason for the layering itself.
An entry recording the name, meaning, data type, and allowed values for each data item belongs in the:
The data dictionary fixes a common vocabulary by recording each item's name, meaning, type, allowed values, and source. The transaction log records changes for durability, the optimizer plan describes query execution, and an index file is a physical access structure - none of which serve as the shared definition reference.
Which statement best describes a candidate key in an entity type?
A candidate key is a minimal (no removable attribute) set of attributes that uniquely identifies every instance. Frequency of querying and storage size are irrelevant to keyhood, and a foreign key references another relation's key rather than identifying instances of its own entity.
The rule 'every loan must involve exactly one member, but a member may have many loans' is captured by which combination of constraints on the Member-Loan relationship?
'Exactly one member per loan' plus 'many loans per member' is a one-to-many cardinality ratio, and 'must involve' on the loan side is mandatory (total) participation. Many-to-many and one-to-one misstate the cardinality, and the degree of the relationship is binary, not ternary.
Where should the date a loan was made be modeled when the loan is represented as a relationship between Member and Book?
A value that depends on the association between two entities - not on either entity alone - is a relationship attribute and belongs on the relationship (the LOANS diamond). Placing it on Member or Book would misassign its dependency, and relationship attributes are a conceptual-level construct, not a physical-phase concern.
Grounded in trusted sources
- Connolly, T. and Begg, C. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management, 6th ed. Pearson. Chapters 10-11 (Database System Development Lifecycle; Database Analysis and Fact-Finding) and 16-19 (the database design methodology).
- Elmasri, R. and Navathe, S. B. (2016). Fundamentals of Database Systems, 7th ed. Pearson. Chapter 3, Section 3.1 (Using High-Level Conceptual Data Models for Database Design - the database design process, requirements collection and analysis, and conceptual design).
- Elmasri, R. and Navathe, S. B. (2016). Fundamentals of Database Systems, 7th ed. Pearson. Chapters 3 and 4 (the Entity-Relationship Model and the Enhanced ER Model).
- Connolly, T. and Begg, C. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management, 6th ed. Pearson. Chapters 12 and 16.
- Elmasri, R. and Navathe, S. B. (2016). Fundamentals of Database Systems, 7th ed. Pearson. Chapters 9, 14, and 15 (Relational Mapping; Functional Dependencies and Normalization).
- Connolly, T. and Begg, C. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management, 6th ed. Pearson. Chapters 14 and 17.
Every Wunder lesson is built from real, reputable sources — never invented.
Related courses
Wunder is a personalized learn-anything platform — tell it any topic and it builds a beautiful, fact-checked course in minutes, with narration, a knowledge check, and a college-style University track.
© 2026 Wunder Learning LLC · Terms & Privacy