Database Entities and Attributes | Cardinality & Weak Entities

# CHAPTER 5

Entities, Attributes, and Relationships #

1. Introduction #

In Chapter 3, we briefly introduced the terminology: Tables are Entities, Columns are Attributes, and Bridges are Relationships. To build complex enterprise architectures, we must dive much deeper into these three pillars. Not all Entities are created equal—some can survive on their own, while others mathematically cannot exist without a parent. Not all Attributes are simple text strings. In this chapter, we will master the theoretical classification of data elements.

2. Learning Objectives #

By the end of this chapter, you will be able to:

• Differentiate between Strong and Weak Entities.

• Classify Attributes (Simple, Composite, Derived, Multi-valued).

• Define Database Relationships mathematically.

• Understand the critical concept of "Cardinality".

3. Entities: Strong vs. Weak #

An Entity is a distinct object in the business domain. (e.g., Student, Course, Vehicle). However, architects classify them into two strict categories:

• Strong Entity: An entity that exists completely independently. A User can exist in the database even if they have zero orders. A Product can exist even if no one has bought it yet.

• Weak Entity: An entity that *cannot mathematically exist* without its parent. Think of an OrderItem (a line on a receipt). Can a line item exist without an Order receipt to attach it to? No. If the Order is deleted, the OrderItem MUST be deleted. It is a Weak Entity.

*(Recognizing Weak Entities is crucial, because they dictate where we must enforce ON DELETE CASCADE rules in our SQL!).*

4. Classifying Attributes #

An Attribute describes an Entity. But they come in different flavors:

• Simple Attribute: Atomic and indivisible. (e.g., Age = 25, ZipCode = 90210).

• Composite Attribute: An attribute made of smaller parts. (e.g., Address is composite because it is made of Street, City, State, and Zip). In a relational database, you must break composite attributes down into simple columns!

• Derived Attribute: An attribute that should NOT be physically stored in the database, because it can be mathematically calculated on the fly. (e.g., Don't store Age; store DateofBirth. You can always derive age from the current date!).

• Multi-Valued Attribute: An attribute that has multiple values. (e.g., A user has 3 phone numbers). Rule: Relational databases *hate* multi-valued attributes in a single column. They must be moved to their own separate Weak Entity table!

5. Relationships #

A Relationship is an association between two or more Entities.

• *A Teacher (Entity) INSTRUCTS (Relationship) a Class (Entity).*

• *A Doctor (Entity) TREATS (Relationship) a Patient (Entity).*

6. The Concept of Cardinality #

Cardinality is the mathematical measurement of a Relationship. It answers the question: "Exactly *how many* instances of Entity A can be associated with Entity B?"

The three primary Cardinalities are:

1. 1. One-to-One (1:1): One User has exactly One Passport.

1. 2. One-to-Many (1:N): One Customer places Many Orders. (But an Order belongs to exactly One Customer).

1. 3. Many-to-Many (M:N): A Student takes Many Classes. A Class contains Many Students.

7. Mini Project: Classifying a Hospital Database #

Let's analyze a Hospital requirement and classify the components.

Scenario: A Hospital tracks Doctors, Patients, and their Appointments. A Patient must provide an Emergency Contact Name.

Classification:

• Doctor -> Strong Entity (Can exist alone).

• Patient -> Strong Entity (Can exist alone).

• Appointment -> Weak Entity (An appointment cannot exist without BOTH a Doctor and a Patient).

• Emergency Contact Name -> Simple Attribute of the Patient.

• Cardinality: A Doctor has Many Appointments, a Patient has Many Appointments (M:N relationship resolved by the Appointment entity!).

8. Common Mistakes #

• Storing Derived Attributes: Beginners often create an ordertotal column on an orders table, AND store the individual prices of items in the orderitems table. If a price changes, the ordertotal is now mathematically incorrect. The total is a Derived Attribute; it should be calculated dynamically using SUM(price) in a query, not stored physically!

9. Best Practices #

• Eradicate Multi-Valued Attributes: If a stakeholder says "A user can have up to 3 email addresses", do not create email1, email2, and email3 columns. Create a Weak Entity table called useremails with a Foreign Key pointing to the User. This ensures perfect normalization.

10. Exercises #

1. 1. If an Entity physically cannot exist without a parent Entity, what is it called?

1. 2. What is the database term for an attribute (like Age) that can be calculated from other data and should generally not be stored physically?

11. Database Design Challenges #

Look at the following Attribute: FullName (e.g., "John Robert Doe"). What type of attribute is this, and how must you restructure it to fit into a perfectly designed Relational Database Table? *(Answer: It is a Composite Attribute. It must be broken down into Simple Attributes: firstname, middlename, lastname columns).*

12. MCQ Quiz with Answers #

Question 1

In Entity-Relationship modeling, what does the term "Cardinality" describe?

A The physical hard drive space a table consumes. B The mathematical measurement of the relationship between entities... C The data type of a column. D The security clearance required to view a table.

Check Answer

13. Interview Questions #

• Q: Differentiate between a Multi-Valued Attribute and a Composite Attribute. Provide an example of how you would architect a solution for a Multi-Valued Attribute (e.g., a user with multiple phone numbers) in a strict Relational Database.

• Q: A developer wants to add an age column to the users table. As a Database Architect, explain why this is a "Derived Attribute" anti-pattern, and propose the correct structural solution.

14. FAQs #

Q: Can a Weak Entity be related to another Weak Entity? A: Yes! A cascading chain of weak entities is common. An Order (Strong) has OrderItems (Weak). An OrderItem might have Item_Customizations (Weak). If the Order dies, the entire chain is wiped out via cascading deletes.

15. Summary #

You have mastered the vocabulary of Database Architecture. By classifying Entities as Strong or Weak, breaking down Composite and Multi-Valued Attributes, and defining the mathematical Cardinality of your relationships, you are prepared to draw complex system blueprints.

16. Next Chapter Recommendation #

We have classified our data logically in our heads. Now, we must put it on paper for the rest of the engineering team to see. In Chapter 6: ER Diagrams Explained, we will learn the universal visual language of Database Architects: Entity-Relationship Diagrams.