Chapter 3 - Data Modeling (1).pdf

Data Models ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Learning Objectives ▪ In this chapter, you will learn: ▪ Ab...

Data Models ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Learning Objectives ▪ In this chapter, you will learn: ▪ About data modeling and why data models are important ▪ About the basic data-modeling building blocks ▪ What business rules are and how they influence database design ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 2  Learning Objectives ▪ In this chapter, you will learn: ▪ How the major data models evolved ▪ About emerging alternative data models and the need they fulfill ▪ How data models can be classified by their level of abstraction ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 3  What is Conceptual Database Design? ▪ Process of describing the data, relationships between the data, and the constraints on the data. ▪ After analysis - Gather all the essential data required and understand how the data are related ▪ The focus is on the data, rather than on the processes. ▪ The output of the conceptual database design is a Conceptual Data Model ( + Data Dictionary) ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Data Modeling and Data Models  Data modeling: Iterative and progressive process of creating a specific data model for a determined problem domain ▪ Data models: Simple representations of complex real-world data structures ▪ Useful for supporting a specific problem domain ▪ Model - Abstraction of a real-world object or event ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 5  Gathering Information for Conceptual Data Modeling ▪ Two perspectives ▪ Top-down ▪ Data model is derived from an intimate understanding of the business. ▪ Bottom-up ▪ Data model is derived by reviewing specifications and business documents. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Importance of Data Models ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 7  Business Rules ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 8  Sources of Business Rules ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 9 Reasons for Identifying and Documenting Business Rules ▪ Help standardize company’s view of data ▪ Communications tool between users and designers ▪ Allow designer to: ▪ Understand the nature, role, scope of data, and business processes ▪ Develop appropriate relationship participation rules and constraints ▪ Create an accurate data model ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 10  Translating Business Rules into Data Model Components ▪ Nouns translate into entities ▪ Verbs translate into relationships among entities ▪ Relationships are bidirectional ▪ Questions to identify the relationship type ▪ How many instances of B are related to one instance of A? ▪ How many instances of A are related to one instance of B? ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 11  The Relational Model ▪ Based on a relation ▪ Relation or table: Matrix composed of intersecting tuple and attribute ▪ Tuple: Rows ▪ Attribute: Columns ▪ Describes a precise set of data manipulation constructs ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Entity-Relationship (ER) Modeling ▪ ER Modeling is a top-down approach to database design. ▪ Entity Relationship (ER) Diagram ▪ A detailed, logical representation of the entities, associations and data elements for an organization or business ▪ Notation uses three main constructs ▪ Data entities Chen Model and ▪ Relationships Crow’s Foot Model) ▪ Attributes ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Chen Notation Entity Name Verb Phrase Attribute Name Person, place, object, event named property or or concept about which data characteristic of an is to be maintained entity Represents a set or collection of objects in the real world that Association share the same properties between the instances of one or more entity types ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Crow’s Foot Entity Attribute Relationship EntityName Entity Name Verb phrase List of Attributes Acceptable ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  The E-R Model Notations ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  E-R Model Constructs ▪ Entities - a person, a place, an object, an event, or a concept in the user environment about which the organization wishes to maintain data ▪ Entity instance–person, place, object, event, concept (often corresponds to a row/tuple in a table) ▪ Entity Set–collection of entities (often corresponds to a table) ▪ Relationships - How entities act upon each other or are associated with each other. ▪ Relationship instance–link between entities (corresponds to primary key-foreign key equivalencies in related tables) ▪ Relationship type–category of relationship or link between entity sets ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  E-R Model Constructs ▪ Attributes - property or characteristic of an entity or relationship type ▪ Properties or characteristics of an entity or relationship type (often corresponds to a field in a table) ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  E-R Model Constructs A supplier sends zero or more (optional many) shipments. A shipment is sent by one and only (mandatory one) one supplier. A supplier supplies zero or more items. An item is supplied by one or more suppliers. An item is used on one or more products. A product uses one or more items A product is requested on zero or more orders. An order requests one or more products. A customer submits zero or more orders An order is submitted by one and only one customer. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Entities ▪ Entity – a person, a place, an object, an event, or a concept in the user environment about which the organization wishes to maintain data ▪ Entity type – a collection of entities that share common properties or characteristics ▪ Entity instance – A single occurrence of an entity type ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Entity Type and Entity Instances ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  An Entity… ▪ SHOULD BE: ▪ An object that will have many instances in the database ▪ An object that will be composed of multiple attributes ▪ An object that we are trying to model ▪ SHOULD NOT BE: ▪ An output of the database system (e.g., a report) ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Strong vs. Weak Entities, and Identifying Relationships ▪ Strong entity ▪ exists independently of other types of entities ▪ has its own unique identifier underlined with single line ▪ Weak entity ▪ dependent on a strong entity (identifying owner)…cannot exist on its own ▪ does not have a unique identifier (only a partial identifier) ▪ entity box and partial identifier have double lines ▪ Identifying relationship ▪ links strong entities to weak entities ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Example of a weak identity and its identifying relationship Strong entity Weak entity ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Attributes ▪ Attribute–property or characteristic of an entity or relationship type ▪ Classifications of attributes: ▪ Required versus Optional Attributes ▪ Simple versus Composite Attribute ▪ Single-Valued versus Multivalued Attribute ▪ Stored versus Derived Attributes ▪ Identifier Attributes ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Required vs. Optional Attributes Required – must have a value for every Optional – may not have a value for entity (or relationship) instance with every entity (or relationship) instance which it is associated with which it is associated ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Simple vs. Composite Attributes ▪ Composite attribute – An attribute that has meaningful component parts (attributes) The address is broken into component parts ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Multi-valued and Derived Attributes Multivalued – may take on more than Derived – values can be calculated from one value for a given entity (or related attribute values (not physically relationship) instance stored in the database) Entity with multivalued attribute (Skill) and derived attribute (Years Employed) Multivalued Derived an employee can Calculated have more than one from date skill employed and current date ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Identifiers (Keys) ▪ Identifier (Key)–an attribute (or combination of attributes) that uniquely identifies individual instances of an entity type ▪ Simple versus Composite Identifier ▪ Candidate Identifier–an attribute that could be a key…satisfies the requirements for being an identifier ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Criteria for Identifiers ▪ Choose Identifiers that ▪ Will not change in value ▪ Will not be null ▪ Avoid intelligent identifiers (e.g., containing locations or people that might change) ▪ Substitute new, simple keys for long, composite keys ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Simple and composite identifier attributes The identifier is boldfaced and underlined ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Naming Attributes ▪ Name should be a singular noun or noun phrase ▪ Name should be unique ▪ Name should follow a standard format ▪ e.g. [Entity type name { [ Qualifier ] } ] Class ▪ Similar attributes of different entity types should use the same qualifiers and classes ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Defining Attributes ▪ State what the attribute is and possibly why it is important ▪ Make it clear what is and is not included in the attribute’s value ▪ Include aliases in documentation ▪ State source of values ▪ Specify required vs. optional ▪ State min and max number of occurrences allowed ▪ Indicate relationships with other attributes ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Modeling Relationships ▪ Relationship Types vs. Relationship Instances ▪ The relationship type is modeled as lines between entity types…the instance is between specific entity instances ▪ Relationships can have attributes ▪ These describe features pertaining to the association between the entities in the relationship ▪ Two entities can have more than one type of relationship between them (multiple relationships) ▪ Associative Entity–combination of relationship and entity ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Relationship types and instances a) Relationship type (Completes) b) Relationship instances ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Degree of Relationships ▪Degree of a relationship is the number of entity types that participate in it ▪Unary Relationship ▪Binary Relationship ▪Ternary Relationship ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Degree of relationships Entities of One entity two different related to types related Entities of three another of to each other different types the same related to each entity type other ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Cardinality of Relationships ▪ One-to-One ▪ Each entity in the relationship will have exactly one related entity ▪ One-to-Many ▪ An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity ▪ Many-to-Many ▪ Entities on both sides of the relationship can have many related entities on the other side ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Examples of relationships of different degrees a) Unary relationships ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Examples of relationships of different degrees (cont.) b) Binary relationships ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Examples of relationships of different degrees (cont.) c) Ternary relationship Note: a relationship can have attributes of its own ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Cardinality Constraints ▪ Cardinality Constraints—the number of instances of one entity that can or must be associated with each instance of another entity ▪ Minimum Cardinality ▪ If zero, then optional ▪ If one or more, then mandatory ▪ Maximum Cardinality ▪ The maximum number ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Examples of cardinality constraints a) Mandatory cardinalities A patient history is A patient must have recorded recorded for one and at least one history, and can only one patient have many ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Examples of cardinality constraints (cont.) b) One optional, one mandatory A project must be An employee can be assigned assigned to at least one to any number of projects, or employee, and may be may not be assigned to any assigned to many at all ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Examples of cardinality constraints (cont.) c) Optional cardinalities A person is married to at most one other person, or may not be married at all ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Examples of multiple relationships a) Employees and departments Entities can be related to one another in more than one way ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Examples of multiple relationships (cont.) b) Professors and courses (fixed lower limit constraint) Here, min cardinality constraint is 2. At least two professors must be qualified to teach each course. Each professor must be qualified to teach at least one course. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Multivalued attributes can be represented as relationships simple composite ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Associative Entities ▪ An entity–has attributes ▪ A relationship–links entities together ▪ When should a relationship with attributes instead be an associative entity? ▪ All relationships for the associative entity should be many ▪ The associative entity could have meaning independent of the other entities ▪ The associative entity preferably has a unique identifier, and should also have other attributes ▪ The associative entity may participate in other relationships other than the entities of the associated relationship ▪ Ternary relationships should be converted to associative entities ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  A binary relationship with an attribute Here, the date completed attribute pertains specifically to the employee’s completion of a course…it is an attribute of the relationship. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  An associative entity (CERTIFICATE) Associative entity is like a relationship with an attribute, but it is also considered to be an entity in its own right. Note that the many-to-many cardinality between entities in the previous has been replaced by two one-to-many relationships with the associative entity. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  An associative entity – bill of materials structure This could just be a relationship with attributes…it’s a judgment call. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Cardinality constraints in a ternary relationship ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Simple example of time-stamping The Price History Time stamp – a time value that is attribute is both associated with a data value, often multivalued and indicating when some event occurred that composite. affected the data value ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  E-R diagram with associative entity for product assignment to product line over time The Assignment Modeling time-dependent data has become associative entity shows more important due to regulations such as the date range of a HIPAA and Sarbanes-Oxley. product’s assignment to a particular product line. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Data model for Pine Valley Furniture Company in Microsoft Visio notation Different modeling software tools may have different notation for the same constructs. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Activity 3 ▪ Create a Business Rules (at least 5, also should describe a many to many and a ternary relationship degree scenario). ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.