Relational Concepts

1. Relational Concepts Fundamental very data-modeling technique has its own set of terms, definitions, and techniques.

2. Why Do You Need a Data Model? • A model is an abstraction or representation of a subject that looks or behaves like all or part of the original. • Benefits: • A model reduces overall risk by ensuring that the requirements of the final product will be satisfactorily met. • A model helps the developers envision how the final product will interface with other systems or functions. • A model helps all the people involved understand how to relate to the ultimate product and how it will pertain to their work function. • Finally a model ensures that the people building the product and thoserequesting it have the same expectations about the ultimate outcome of the effort.

3. Relational Data-Modeling Objects • Subject • Entity • Element or Attribute • Relationships

4. Subject • A subject area is the subset of the enterprise’s data and consists of related entities and relationships. Customers, Sales, and Products are examples of subject areas.

5. Entity • An entity is generally defined as a person, place, thing, concept, or event in which the enterprise has both the interest and the capability to capture and store information. • An entity is unique within the data model. • Four types of entities from which to build logical or business data models and data warehouse models :

6. Entity (Continue) • A Primary or Fundamental Entity is defined as an entity that does not depend on any other entity for its existence, such as Customer, Sale, and Product. • A Subtype Entity is a logical division or category of a parent (supertype) entity. Examples of subtypes for the Customer entity are Retail Customer and Wholesale Customer. • An Attributive or Characteristic Entity is an entity whose existence depends on another entity. • An Associative or Intersection Entity is an entity that is dependent upon two or more entities for its existence

7. Element or Attribute • An element or attribute is the lowest level of information relating to any entity. • Four types of attribute • A primary key serves to uniquely identify • A foreign key is a key that exists because of a parent-child relationship between a pair of entities. • A nonkey element or attribute

8. Element or Attribute (Continue) Characteristics of a Good Key : • The key is not null over the scope of integration. • The key is unique over the scope of integration. • The key is unique by design not by circumstance. • The key is persistent over time. • The key is in a manageable format. • The key should not contain embedded intelligence but rather is a generic string.

9. Sample data model

10. Relationships • A relationship documents the business rule associating two entities together. • There are different characteristics of relationships used in documenting the business rules of the enterprise: • Cardinality denotes the maximum number of occurrences of one entity that can be related to another entity. Usually these are expressed as “one” or “many.” • Optionality or modality indicates whether an entity occurrence must participate in a relationship. This characteristic tells you the minimum number (zero or optional) of occurrences in the relationship.

11. Relationships (Continue) Two of different types of relationships: • An identifying relationship is one in which the primary key of the parent entity becomes a part of the primary key of the child entity. • A nonidentifying relationship is one in which the primary key of the parent entity becomes a nonkey attribute of the child entity. An example of this type of relationship is a recursive relationship.

12. Identifying and nonidentifying relationships.

13. Normalization Normalization has these characteristics as well: • Verification of the structural correctness and consistency of the data model • Independence from any physical constraints • Minimization of storage space requirement by eliminating the storage of data in multiple places

14. Normalization (Continue) Finally, normalization: • Removes data inconsistencies since data is stored only once, thus eliminating the possibility of conflicting data • Diminishes insertion, updating, and deletion anomalies because data is stored only once • Increases the data structure stability because attributes are positioned in entities based on their intrinsic properties rather than on specific application requirements

15. First Normal Form • The attribute is dependent on the key. • That every entity have a primary key that uniquely identifies it and that the entity contain no repeating or multivalued groups.

16. Second Normal Form • the attributes must be dependent on the whole key. • the entity must be in 1NF and every nonprimary attribute must be dependent on the entire primary key for its existence.

17. Third Normal Form • the attribute must be dependent on nothing but the key • the entity must be in 2NF, and the nonkey fields must be dependent on only the primary key, and not on any other attribute in the entity, for their existence.

18. Recommended Readings Chapter 2 Mastering Data Warehouse and Design Relation and Dimensional Techniques Claudia Imhoff, Nicholas Galemmo,Jonathan G. Geiger Wiley Publishing, Inc. 2003,