Data Models & ERM

• Data Modeling


  - first step in designing a database

  - process of creating a specific data model for a determined problem domain
• Data Model


  - representation usually a graphic, of complex "real-world" data structures.

  - used in the database design phase of the Database Life Cycle.
• Importance of Data Models

  1. Facilitate interaction among the designer, the application programmer, and the end user.

  2. End-users have different views and needs for data.

  3. Data model organizes data for various users.

  4. Data model is an abstraction (Cannot draw required data out of the data model)
• Basic building blocks of data models

  1. Entity

  2. Attribute

  3. Relationship
• Entity


  A person, place, thing, concept, or event for which data can be collected and stored. (often corresponds to a table)
• Attribute


  - characteristic of an entity, object, or relationship type

  - equivalent of fields in file systems
• Required attribute


  must have a value
• Optional attribute


  may be left empty
• Identifiers


  one or more attributes that uniquely identify each entity instance
• Composite identifier


  PK composed of more than one attribute
• Derived attribute


  value may be calculated from other attributes (not physically stored in the database)
• Composite attributes


  can be subdivided
• Simple attributes


  cannot be subdivided
• Single-value attribute


  only a single value
• Multivalued attributes


  many values
• M:N relationships and multivalued attributes


  - should not be implemented

  - Create several new attributes for each of the original multivalued attributes' components

  - new entity composed of original multivalued attributes' components
• Relationship


  instance - Describes an association among entities. (corresponds to PK-FK equivalencies in related tables).

  
  

  type - category of relationship, a link between an entity type

  
  

  - difficult to establish if only one side of the relationship is known
• Entity-relationship (ER) model (ERM)


  - data model that describes relationships (1:1, 1:M, and M:N) among entities at the conceptual level with the help of ER diagrams.
• Entity relationship diagram (ERD)


  - diagram that depicts an entity relationship model's entities, attributes, and relations.
• Entity instance (entity occurrence)


  row in a relational table.
• Entity set


  collection of like entities.
• Connectivity


  type of relationship between entities. Classifications include 1:1, 1:M, and M:N.
• Chen notation


  - See entity relationship (ER) model

  - attributes: represented by ovals connected to an entity rectangle with a line
• Crow's Foot notation


  - representation of the entity relationship diagram that uses a three-pronged symbol to represent the "many" sides of the relationship

  
  

  - attributes: written in the attribute box below the entity rectangle
• class diagram notation


  set of symbols used in the creation of class diagrams
• Cardinality or Cardinalities


  - refers to the relationship between tables, rows, and elements.

  - set of tuples/row in a table

  - number of entities to which another entity can be associated through a relationship.

  - Expresses mini and maxi number of entity occurrences associated with one occurrence of a related entity
• Three types of Relationships

  1. one to one

  2. one to many

  3. many to many
• One -to -many (1:M or 1..*) relationship


  Associations among two or more entities that are used by data models.

  
  

  In a 1:M relationship, one entity instance is associated with many instances of the related entity

  
  

  Entity names are often capitalized as a convention.

  
  

  Examples:

  ✓ PAINTER paints PAINTING

  ✓ CUSTOMER generates INVOICE
• many -to -many (M:N or..) relationship


  Association among two or more entities in which one occurrence of an entity is associated with many occurrences of a related entity and one occurrence of the related entity is associated with many occurrences of the first entity .

  
  

  Examples:

  ✓ EMPLOYEE learns SKILL

  ✓ STUDENT takes CLASS
• one-to-one (1:1) relationship


  one entity instance is associated with only one instance of the related entity.

  
  

  Example:

  ✓ EMPLOYEE manages STORE

  ✓ EMPLOYEE assigned PARKING_SPACE
• Constraints


  - restriction placed on the data.

  - Expressed in the form of rules
• Business rules


  - brief, precise, and unambiguous description of a policy, procedure, or principle within a specific organization.

  
  

  - Description of operations to create or enforce actions within an organization's environment.

  
  

  - Describe characteristics of data as viewed by the company.
• Naming Conventions


  - Entity names should be descriptive of the objects in the business environment and use terminology that is familiar to the users.

  
  

  - An attribute name should also be descriptive of the data represented by that attribute.

  
  

  - It is also a good practice to prefix the name of an attribute with the name or abbreviation of the entity in which it occurs.
• Hierarchical Model


  - An early database model whose basic concepts and characteristics formed the basis for subsequent database development

  
  

  - This model is based on an upside-down tree structure in which each record is called a segment

  
  

  - Segment - the equivalent of a file system's record type.
• Network Models


  - created to represent complex data relationships more effectively than the hierarchical model

  
  

  - to improve database performance, and to impose a database standard.
• NM concepts

  Schema - Conceptual organization of entire database as viewed by the database administrator

  
  

  Subschema - Database portion "seen" by the application programs

  
  

  Data manipulation language (DML) - Defines the environment in which data can be managed and is used to work with the data in the database.

  
  

  Data definition language (DDL) - Enables the administrator to define the schema components
• Relational Model


  Developed by E. F. Codd of IBM in 1970, the relational model is based on mathematical set theory and represents data as independent relations

  
  

  Each relation (table) is conceptually represented as a two dimensional structure of intersecting rows and columns.

  The relations are related to each other through the sharing of common entity characteristics (values in columns).

  
  

  table (relation) A logical construct perceived to be a two-dimensional structure composed of intersecting rows (entities) and columns (attributes) that represents an entity set in the relational model

  
  

  tuple In the relational model, a table row
• Degree


  set of attributes/columns in a table
• Degree of Relationship or Relationship Degree


  represents number of entity types that are associated with a relationship
• Types of degree

  1. Unary

  2. Binary

  3. Ternary

  4. N-ary