Relational Data Modeling

This chapter covers how to derive a relational data model from an application’s functional requirements, using CourseTracker, a course-history application, as the running example: splitting a spreadsheet row into tables, linking them with keys, naming relationship shapes, and making the model enforceable with constraints, a schema, an ERD, and SQL.

After reading this chapter, you should be able to:

Describe the facts a personal course-history spreadsheet captures, and explain why it is sufficient for one student but not for a shared application
State what CourseTracker does and does not do, and read its functional requirements for the data they imply
Explain what a database provides beyond a spreadsheet, and justify a relational database for CourseTracker’s data
Apply a test for deciding whether two facts belong in the same table, and split a spreadsheet row into courses, professors, and offerings tables
Define primary key and foreign key, explain how a foreign key enforces referential integrity, and explain why a junction table is needed to record that a student took an offering
Build a requirements table and a course-requirements junction table, and extract a redundant text column such as subject area into its own referenced table
Define one-to-many, many-to-many, and one-to-one relationships, and state where the foreign key belongs in each
State the rule behind first, second, and third normal form, and recognize a design that would violate one
Define constraint and choose an appropriate constraint — data type, not null, uniqueness, or check — for a given column rule
Define schema, read an entity-relationship diagram in crow’s-foot notation, and read a SQL CREATE TABLE statement with a composite primary key, as three views of the same design

Relational Data Modeling

Sections