Lesson 1

Modern relational database software has brought point-and-click ease to the task of creating a database. But ease of use is not the same as good design. A database that was never properly designed will be characterized by three familiar problems:

1. missing data,
2. incorrect data, and
3. mismatched data.

Any one of these makes a database nearly useless, no matter how friendly the software around it is. A dropdown menu and a clean user interface do nothing to stop a customer record from losing its address, an order from pointing at a product that no longer exists, or two tables disagreeing about which state abbreviation is correct. These are not rare edge cases either: missing data shows up the first time a required field was left optional, incorrect data shows up the first time the same fact is typed two different ways in two different places, and mismatched data shows up the moment an order survives in the system after the customer who placed it has been deleted. None of these are software bugs in the usual sense. They are design problems, and no amount of polished front-end code fixes a design problem after the fact.

This course is the first step in learning a design methodology that avoids those problems: a way of thinking through what a database needs to store, and how its pieces relate to one another, before a single table gets created. Over the course, you will work through the principles of data modeling, culminating in a complete database design diagram of your own.

After completing this course, you will be able to:

1. Explain relational database design concepts and terminology.
2. Explain the difference between conceptual and physical models.
3. Develop a conceptual design.
4. Analyze user information requirements.
5. Describe the entity/relationship modeling approach.
6. Identify entities.
7. Model relationships and identify attributes.
8. Use primary and foreign keys.

Good database design rests on a small set of ideas that recur throughout this course. A relational database is simply a database built from tables, linked to one another by relationships, so that data can be organized sensibly and retrieved again later without duplication or guesswork. It helps to see the main ideas together once, early on, even though each one gets its own, deeper treatment in a later lesson.

An entity represents a real-world object or concept, a person, a product, an event, and each entity typically becomes a table in the database. An attribute is a characteristic of that entity: for an entity such as "Student," attributes might include StudentID, Name, and Date of Birth.

Entities are connected to one another, and made uniquely identifiable, through keys:

• A primary key uniquely identifies each record in a table; no two records may share one.
• A foreign key is an attribute in one table that points back to the primary key of another, which is how tables relate to each other.
• A composite key uses two or more attributes together to guarantee uniqueness.
• A candidate key is any attribute, or set of attributes, that could serve as the primary key. A table may have several candidates but only one actual primary key.

Picture this in terms of this course's own project: Stories on CD, Inc. needs a Customer entity and an Order entity. CustomerID is the Customer table's primary key, a short, stable value that uniquely identifies each customer regardless of whether two customers happen to share a name. Every row in the Order table then carries that same CustomerID as a foreign key, which is what lets the database answer "which orders belong to this customer" without storing the customer's full name and address redundantly on every single order.

Tables relate to one another in three basic patterns:

• One-to-one (1:1): a single record in one table corresponds to a single record in another.
• One-to-many (1:N): one record relates to several records elsewhere.
• Many-to-many (M:N): many records on each side relate to many on the other, typically implemented with a junction or bridge table in between.

Once those relationships exist, referential integrity is what keeps them trustworthy: a set of rules ensuring that a foreign key value always points to a primary key value that actually exists, rather than to a record that was deleted or never created. For Stories on CD, Customer-to-Order is a one-to-many relationship, one customer can place many orders, and referential integrity is what prevents an Order row from ever pointing at a CustomerID that does not exist in the Customer table, whether because of a data-entry mistake or because that customer record was deleted without anyone checking for related orders first.

Normalization is the process of organizing data to eliminate redundancy and protect data integrity, generally by splitting larger tables into smaller, related ones and connecting them with foreign keys. A series of normal forms, 1NF, 2NF, 3NF, and beyond, provide the guidelines for doing this without introducing anomalies, situations where inserting, updating, or deleting one piece of data accidentally corrupts or loses another.

A single, unnormalized Order table that stores the customer's name and address directly on every order, rather than referencing a separate Customer table, illustrates the problem well. If that customer moves, every single one of their past orders needs updating, not just one record, and it is easy to update some rows and miss others, leaving the database internally inconsistent about where one customer actually lives. Splitting Customer out into its own table, referenced by foreign key, means the address exists in exactly one place no matter how many orders that customer has placed. You will work through the formal normal forms that generalize this kind of fix in detail in the second course of this series.

An ER diagram (entity-relationship diagram) is a visual representation of the whole design: entities, their attributes, and the relationships that connect them. You will build one of these yourself as this course's project. The database schema is the overall blueprint the diagram represents, the tables, relationships, and constraints, considered as a whole.

A handful of supporting concerns round out good design. Data integrity covers the accuracy and reliability of the data itself, whether that means valid values (domain integrity), unique records (entity integrity), or consistent relationships (referential integrity, covered above). Data redundancy is what happens when the same fact is stored in more than one place, exactly what normalization reduces. Constraints such as NOT NULL, UNIQUE, CHECK, and DEFAULT enforce rules at the column level, for example a NOT NULL constraint on an order's CustomerID column, which stops an order from ever being saved without a customer attached to it in the first place. Indexes are data structures that speed up retrieval, at some cost to write performance, since every index has to be updated each time the underlying data changes; an index on CustomerID, for instance, is what makes "show me all of this customer's orders" fast even once the Order table holds millions of rows. And data security, permissions, encryption, and backup protocols, protects the data once it exists, regardless of how well it was designed in the first place. None of these five concerns is the primary subject of this course, design comes first, but each one depends on the design underneath it being sound; a constraint can only enforce a rule that the design already anticipated, and an index can only speed up a relationship that was modeled correctly to begin with.

Taken together, these ideas are what separate a database that merely stores data from one that stores it logically, keeps it accurate, and makes it easy to retrieve, modify, and delete correctly.

One distinction is worth calling out on its own, since it is one of this course's stated learning objectives: the difference between a conceptual model and a physical model.

A conceptual model describes what the business needs, in business language, before any decisions have been made about which database software will store it. For Stories on CD, a conceptual model says that customers place orders, and orders contain audiobook titles, without yet specifying column names, datatypes, or which fields are required. A physical model is the opposite end of the same design: the actual implementation, with real table names, real column names and datatypes, primary and foreign key constraints defined, and indexes chosen for the specific database engine the design will run on.

Between the two often sits a logical model, which adds the detail of attributes and keys to the conceptual model while still staying independent of any particular database product. The practical reason to separate these stages is that business requirements change slowly, a customer is still a customer, but the physical details of a specific database engine, or a decision to switch engines entirely, should not force you to rethink what the business actually needs. Starting from a conceptual model and only later committing to physical details keeps those two kinds of change from getting tangled together.

A database, in the most literal sense, is the set of physical files installed on a computer when you set up the database software. A database model, by contrast, is a concept rather than a physical object: it is what you use to design the tables that will eventually exist inside that database.

A database, once built, is a structured object made up of data and metadata. Data is the actual, descriptive information stored, the names and addresses of your customers, for example. Metadata is the structure the database applies to that data: the customer table definition, specifying which fields exist, how long each one is, and what datatype it holds. A datatype restricts what can go into a field, allowing only a date, or only a number, for instance. Metadata is what turns raw data into something organized and usable. Without it, "John Smith, 123 Main Street" is just a string of characters; with it, the database knows that the first part is a name of a certain maximum length and the second is an address, and can enforce rules, validate input, and relate that row to others accordingly. A database is often pictured as a cylindrical disk holding both metadata and raw data together (see Figure 1.1), and the physical database itself runs on a database server computer.

This course presents relational database design with the help of two kinds of visuals: a gallery of images, and detailed diagrams you will build yourself. You will also work through a course project: designing a database for Stories on CD, Inc., a fictional mail-order company that sells audiobooks. You will complete this project incrementally, through a series of exercises that let you apply each new concept, entities and attributes, keys, relationships, normalization, as it is introduced, to a real, if small, business. The company itself is simple, but the analysis and design principles you apply to it are exactly the ones database designers use on much larger systems for multinational corporations. By the end of the course, the Customer and Order entities introduced earlier in this lesson will have grown into a complete design diagram covering every part of how Stories on CD actually does business.

By the time you reach the end of this course, the ideas introduced in this lesson, entities and attributes, keys, relationships, referential integrity, normalization, and the conceptual-to-physical progression, will no longer be abstract definitions. You will have applied every one of them to Stories on CD, Inc., starting from a plain-language description of how the company takes and fulfills orders and ending with a complete entity-relationship diagram ready to become a physical database design. That progression, from business requirements to a finished diagram, is the design methodology this course exists to teach, and it is the same progression, at whatever scale, that stands behind every well-built relational database.

Data Modeling and Relational Database Design is the first of two courses in the Relational Database Design Fundamentals series; the second covers data analysis and normalization. The next lesson outlines the prerequisites for this course.