Data Models and 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
• 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
• 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.
• Crow's Foot notation


  representation of the entity relationship diagram that uses a three-pronged symbol to represent the "many" sides of the relationship
• 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.
• Three types of Relationships


  1. one to one

  2. one to many

  3. many to many
• One to many relationship


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

  
  

  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 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 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


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

  1. Unary

  2. Binary

  3. Ternary

  4. N-ary
• Unary (Degree 1)


  association with only one entity
• Binary (Degree 2)


  - two entity sets are participating

  - most used relationship
• Ternary (Degree 3)


  - exists when there are three types of entity
• N-ary (n Degree)


  - exists when there are n(many) types of entities
• Foreign Key


  - primary key from one table that is used in another table

  - connection between tables
• Primary Key


  - unique identifier

  - avoid data redundancy
• Design View


  - designing metadata

  - gives a more detailed view of the structure of the form
• Datasheet view


  - for end user and adding records

  - allows to see many records at once in a tabular format
• Relational Databases


  Database technology involving tables that represent entities and primary/foreign keys that represent relationships
• Information management


  - concerns a cycle of organizational activity

  - gathering information from various sources, keeping, and sharing it with those who require it and eventually deciding whether to achieve or delete it.

  - form of table - each table