Relational Database Design and Modeling for Software Engineers

Relational database design is the work of deciding which tables should exist, what each one holds, and how they connect. Get it right and most queries become simple. Get it wrong and the same fact ends up stored in three places, where two of them will eventually disagree. This course teaches those decisions rather than the syntax. It starts with what a database actually is, and how that differs from a folder of files. A database describes itself. The catalog holds the tables, the columns, the types and the rules. A program can ask what exists, instead of having the layout built into its code. From there the course covers what a database management system does, what the relational model really says, and where non-relational stores differ. The scaling comparison is treated honestly. Relational databases do scale out, and the real difference is the cost of joins and transactions that cross machines. Data modeling comes next. A model has three levels. The conceptual level names the things the business cares about. The logical level turns them into tables, keys and relationships. The physical level decides types, indexes and storage. Keeping the levels apart is what stops an argument about a column type from derailing a discussion about what the business means. The entity-relationship chapter is the visual heart of the course. It covers entities and attributes, including the composite, multivalued and derived kinds, and it is exact about the notation. A double ellipse means multivalued. A dashed ellipse means derived. Relationships get their degree, their cardinality ratio and their participation. The chapter is clear that total participation is what becomes NOT NULL later. Weak entities, partial keys and identifying relationships each get their own treatment. The chapter ends by building a full diagram for an employee management system. The relational model chapter turns pictures into tables. It separates the kinds of key carefully, because interviews test exactly this. A superkey identifies a row and may carry columns it does not need. A candidate key is a superkey with nothing to spare. A primary key is the candidate key you chose. A prime attribute is any column inside some candidate key. The chapter covers the integrity constraints that make keys mean something. It then gives the mechanical procedure for converting a diagram into tables, one relationship shape at a time. Functional dependency is the tool everything after it runs on. The chapter explains what a dependency claims. It is a rule about every possible row, not a pattern in the data you happen to have. It covers trivial, partial and transitive dependencies, the inference rules, and how to compute the closure of a set of attributes. Closure is the practical skill here, because it is how you test whether a set of columns is a superkey. Normalization is the largest chapter, and the one most learners find hardest. Each form gets its own lesson, from first normal form through second, third and Boyce-Codd. The higher forms then handle multivalued and join dependencies. The chapter is careful about two things people commonly get wrong. First normal form does not require column values to be unique. It also does not reduce redundancy, and usually increases it, because splitting a row copies every other value on that row. Every decomposition is checked for losslessness. The chapter explains why third normal form, rather than Boyce-Codd, is usually where a real design stops. The course closes with complete case studies. Each one starts from what the system must do, then writes down the questions the schema must answer. It finds the entities, draws the diagram, and converts it into SQL you could run. The domains are a hospital, a hotel, a bank, Instagram and an e-commerce platform. The rest are an online food delivery system, a library, a ticket booking system and a warehouse inventory system. Each study also names the decisions worth questioning, because knowing what a design cannot do is part of defending it. Who this course is for. It is for software engineers who create tables regularly and were never taught how. It is for anyone preparing for an interview that includes schema design. That is a wider group than it sounds. Backend and low level design rounds often ask you to model a domain. A schema with the wrong keys is very visible to an interviewer. How the course works. The lessons are text-based, with a diagram wherever a picture explains more than a paragraph would. Every chapter ends with a quiz. The vocabulary heavy chapters carry a flashcard deck for the terms that get confused with each other. The normalization chapter includes runnable SQL exercises that check your decomposition against a real database. What comes next. Grokking Database Fundamentals is the companion to this course. This one is about designing the schema. That one is about how the engine underneath behaves: storage, indexing, transactions, concurrency, replication and sharding. Design first, then internals, is the usual order, though they are independent enough to take in either.