Lesson 11

This module discussed how business objects and their characteristics are converted into entities and attributes. This is in preparation for creating an ER diagram. You also learned that multi-valued attributes violate the single-value rule for relational databases, and then learned two approaches to resolving multi-valued attributes. Finally, you learned about constraints that can be placed on attributes and entity relationships to help ensure data integrity.

• Learning Objectives
  Having completed this module, you should be able to:
  1. Define entities and entity attributes
  2. Explain the purpose of entity identifiers
  3. List rules for creating entity identifiers
  4. Describe instances of entities
  5. Describe attribute domains and domain types
  6. Explain the problem with multi-valued attributes
  7. Describe how to resolve multi-valued attributes by adding more attributes
  8. Describe how to resolve multi-valued attributes by creating a new entity
  9. List entity and attribute constraints

The single-value rule in relational databases states that each attribute (column) in a table must contain atomic (indivisible) values—meaning a field should not store multiple values or lists. This rule is a fundamental part of First Normal Form (1NF) in database normalization.
Key Aspects of the Single-Value Rule

1. Atomicity of Data
   • Each column must contain a single value per row, rather than multiple or composite values.
   • Example: A "PhoneNumbers" column should not store multiple phone numbers in a single field (e.g., "555-1234, 555-5678").
2. No Repeating Groups
   • Columns should not hold multiple values for the same attribute.
   • Example: Instead of having columns Product1, Product2, and Product3 in an Orders table, products should be stored in a separate OrderDetails table.
3. Compliance with First Normal Form (1NF)
   • The single-value rule ensures that a table adheres to 1NF, which prevents data redundancy and inconsistencies.

Example of Violating vs. Following the Single-Value Rule
Incorrect Design (Violating the Single-Value Rule)

CustomerID | CustomerName| PhoneNumbers
-----------|-------------|-----------------------
1001       | John Doe    | 555-1234, 555-5678
1002       | Jane Smith  | 555-9999

- Here, the `PhoneNumbers` column stores multiple values in a single field, breaking the single-value rule.
Correct Design (Following the Single-Value Rule)
A separate table is used to store multiple phone numbers properly.
Customers Table

CustomerID | CustomerName
-----------|-------------
1001       | John Doe
1002       | Jane Smith

CustomerPhones Table

PhoneID | CustomerID | PhoneNumber
--------|-----------|------------
1       | 1001      | 555-1234
2       | 1001      | 555-5678
3       | 1002      | 555-9999

- This design follows the single-value rule by ensuring that each column holds only one value per row.
Why the Single-Value Rule Matters

• Prevents data anomalies (e.g., difficulty in updating or deleting specific values).
• Improves query performance (avoids parsing and searching within text-based multi-value fields).
• Ensures data integrity (each value is properly indexed and related).

This module introduced you to the following terms:

1. BLOB domain type: Short for “binary large object” ; a domain type used to store binary objects such as graphics in a relational database.
2. Boolean expression: An expression that results in a value of either TRUE or FALSE. Boolean expressions are also called coparison expressions, conditional expressions, and relational expressions.
3. conceptual model: A description of the structure of a database.
4. data value: Data entered at the intersection of a row (record) and column (field); the data describes or identifies the subject of the record.
5. Data integrity: A term used to describe the quality (in terms of accuracy, consistency, and validity) of data in a database, in the sense that values required to enforce data relationships actually exist. Problems with data integrity occur when a value in one table that’s supposed to relate to a value in another can’t, because the second value either has been deleted or was never entered.
6. domain:Determines the type of data values that are permitted for that attribute.
7. domain constraints: Rules that require values of attributes to come from specific domains (e.g., text, numbers, date, etc.).
8. entity: A single stand-alone unit or a business object about which data are stored in a database; usually synonymous with a database table.
9. instance of an entity:The equivalent of a row (record) in a database with values entered for the entity's attributes.
10. key attribute: An attribute that identifies an entity; an entity identifier.
11. multi-valued attribute: An attribute with more than one data value; must be resolved into a single-valued attribute.
12. non-key attribute: An attribute that describes an entity.
13. requirements analysis: The stage in the database design cycle when designers find out everything they can about the data the client needs to store in the database and the conditions under which that data needs to be accessed.
14. single-valued attribute: An attribute with more than one data value; must be resolved into a single-valued attribute.

The next module discusses entity relationships.

Before moving on to the next module, click the Quiz link below to check your understanding of entities and attributes.
Entities Attributes - Quiz