Entities and Attributes

Break down BookVault's business requirements into database entities and their properties, building the foundation for your first entity-relationship model.

Understanding Entities

An entity represents something in the real world that your database needs to track. Think of Netflix tracking movies, or Amazon tracking products. For BookVault, we need to track customers, books, orders, and authors.

But here's the crucial part. An entity isn't just one specific book or one specific customer. It's the blueprint for all books or all customers. The entity "Book" describes what information we store about every book in our inventory.

Each individual book — like "Harry Potter and the Philosopher's Stone" — becomes an entity instance. That's one specific record in our books table.

Identifying BookVault Entities

Start with the nouns in your business requirements. BookVault customers buy books written by authors. Orders contain multiple books from different categories.

What Are Attributes

Attributes describe the properties of an entity. If Customer is the noun, then first name, email address, and city are the adjectives. They're the specific pieces of information we store about each customer.

Think of Excel columns. Each column header represents an attribute. The Customer entity might have attributes like first_name, last_name, email, and city. Every customer record fills in those columns with their specific values.

Attribute Types and Examples

Attributes come in different flavors. Simple attributes store one value — like a customer's email address. Composite attributes can be broken down — like a full address containing street, city, and postal code.

BookVault Entity Breakdown

Time to map BookVault's entities and attributes systematically. We'll start with the Customer entity and build from there.

Choosing the Right Attributes

Not every piece of information becomes an attribute. Ask yourself: "Do I need to search, sort, or filter by this data?" If BookVault never searches customers by their middle initial, don't store it.

Consider derived attributes carefully. Should we store a customer's age, or just their birth date? Birth dates are permanent. Ages change daily. Store the birth date and calculate age when needed.

Attribute Constraints and Rules

Attributes need rules. Can they be empty? What's the maximum length? Should email addresses be unique across all customers?

These constraints protect your data quality. Amazon doesn't let you create an account without an email address. Netflix won't accept a movie without a title. BookVault shouldn't allow books without prices.

Translating to SQL

Here's how BookVault's Customer entity becomes a database table. Notice how each attribute becomes a column with specific data types and constraints.

-- BookVault Customer entity as SQL table
-- Defines structure and rules for all customer records
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    city VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Table 'customers' created successfully.

Structure:
- customer_id: Auto-incrementing primary key
- first_name: Required, max 50 characters
- last_name: Required, max 50 characters  
- email: Required, unique, max 255 characters
- city: Optional, max 100 characters
- created_at: Automatically set to current timestamp

Common Entity-Attribute Mistakes

Most database disasters start here. You see the symptoms later — slow queries, data inconsistencies, impossible reports. But the root cause traces back to poor entity and attribute design.

Another classic error: storing calculated values unnecessarily. If you can derive total_amount from order items, don't store it in the orders table. Calculate it when needed, unless performance absolutely demands otherwise.

The 5-Minute Entity Test

Before moving to the next lesson, try this. Look at your entity list and ask:

• Can I explain what each entity represents in one sentence?
• Would I actually search or filter by each attribute?
• Are my attribute names specific and unambiguous?
• Can each attribute contain only one logical value?

If you answered yes to all four, you're ready for the next step: understanding how these entities connect to each other through keys and relationships.

Quiz