Please enable JavaScript.

Coggle requires JavaScript to display documents.

Zybooks 4 - Coggle Diagram

- - - - refers to maxima and minima of relationships and attributes
      - relationship maximum
        
        greatest number of instances of one entity that can relate to a single instance of another.
      - has two, for both ends, like a UML diagram
  - - - greatest number of attribute values that can describe each entity instance.
      - usually specified as one or many
    - - least number of attribute values that can describe each entity instance. usually specifies as zero or one.
  - - - a unique attribute has at most one entity instance for each attribute value
      - a singular attribute has at most one attribute value for each entity instance
- - - - Stable - should not change. If change, cascade
      - Simple - Smaller values make it faster. 2-byte faster than 15-byte string
      - meaningless - don't describe stuff
  - - - must have unique and non-NULL primary key
      - stable, simple, meaningless
    - - single-column primary key created by the database designer where no suitable single-column or composite primary key exists.
      - usually generated automatically
      - tick the boxes
  - - - THe primary key is identical to the supertype primary key
      - The primary key is also a foreign key that references the supertype primary key
      - Foreign key implements the IsA identifying relatinoship. Foreign keys that implement identifying relatinships usually have the following - cascasde primary key updata dn delete - restrict on foreign key insert and update
  - - - The primary key is usually composite and includes
        
        A foreign key the references the primary key of the identifying table
        
        Another column that makes the composite primary key unique. If no suitable column is available in the weak table, an artificial column can be created
        
        If the identifying relationship is one-one, maximum, the second column is unnecessary and the primary key includes the foreign key only.
        
        The foreign key implements the identifying relationship and usually has the following referential integrity actions
        
        Cascade on primary key update and delete
        
        Restrict on foreign key insert and update
- - - - Multivalued dependence
        
        both of these entail dependencies between three or more columns, However, multivalued and join dependencies are complex, uncommon, and not discussed in this material.
      - Join dependence
  - - - The repetition of related values in a table.
      - causes database management problems.
      - When related values are updated, all copies must be changed. which makes queries slow and complex.
      - If copies are not updated uniformly, the copies become inconsistent and the correct version is uncertain.
    - - rules for designing tables with less redundancy.
      - numbered, first through fifth.
      - Additional normal form, Boyce-Codd, improved version of third normal form
      - Six normal forms comprise a sequence, with each successive normal form allowing less redundancy
  - - - In a first normal form table, every non-key column depends on the primary key.
      - Each primary key value appears in exactly one row,
      - and each non-key cell contains exactly one value
      - C1
        
        So each primary key value is related to exactly one non-key value
        
        C2
        
        A first normal form table has no duplicate rows.
        
        Every row contains a different primary key value and therefore every row is different
    - - table has a primary key
      - every non-key column depends on the primary key
      - the table cannot have duplicate rows
      - every cell cell contains exactly one value
  - - - A table is in second normal form when all non-key columns depend on the whole primary key.
      - In other words, a non-key column cannot depend on part of a composite primary key.
      - A table with a simple primary key is automatically in second normal form.
  - - - Redundancy can occur in a second normal form table when a non-key column depends on another non-key column.
      - Informally, a table is in third normal form when all non-key columns depend on the key, the whole key, and nothing but the key. A formal definition appears elsewhere in this material.
      - A candidate key
        
        Simple or composite column that is unique and minimal.
        
        Minimal means all columns are necessary for uniqueness.
        
        A table may have several candidate keys
        
        The database designer designates one candidate key as the primary key
      - Non key
        
        column is a column that is not contained in a candidate key
      - Third normal Form
        
        is whenever a non-key column A depends on column B, then B is unique
        
        Columns A and B may be simple or composite.
        
        Although B is unique, B is not necessarily minimal and therefore is not necessarily a candidate key
- - - - entities are usually nouns, but not always
      - relationships are often verbs
      - attributes are usually nouns
  - - - so like employee rather than employees
    - - Entity
        
        name of the entity the attribute describes
        
        if its obvious in ER diagrams, you can sometimes ommit it
      - Qualifier
        
        describes meaning of attribute
        
        like first, last, alternate
        
        can sometimes be omitted
      - Type
        
        chosen from list of standard attribute types such as Name, Number Count
  - - - to avoid confusion, we have one chosen name or we have it documented in the glossary as synonyms
    - - should also include counter examples and stuff
- - - - cascade on primary key update and delete
      - restrict on foreign key insert and update
- - - - Each unique attribute instance describes at most one entity instance
      - Each entity instance has at least one required attribute instance.
      - Each entity instance can have zero optional attribute instances.
    - - UNIQUE is specified on columns derived from unique attributes.
      - NOT NULL is specified on columns derived from required attributes.
      - PRIMARY KEY is specified for primary key columns. The PRIMARY KEY keyword automatically enforces unique and required, so additional keywords NOT NULL and UNIQUE are unnecessary.
- - - - entity
        
        person, place, product, concept or activity
      - relationship
        
        statement about two entities
        
        usually about two different entities, but the two entities may be the same - reflexive relationship
      - attribute
        
        descriptive property of an entity
  - - - a glossary, aka data dictionary or repository - documents additional detail in text format.
      - Includes, names, synonyms, and descriptions of entities, relationship and attributes.
      - For simple databases with few users, a database designer may record the glossary with a text editor.
      - For more complex databases, the designer may use a database or software tool specifically designed for glossaries
    - - In entity-relationship modeling, a type is a set
        
        An entity type
        
        set of things
        
        like employees in a company
        
        relationship type
        
        set of related things
        
        employee-manages-department = set of (employee, department) pairs where the employee manages the department
        
        Attribute type
        
        set of values
        
        like all employee salaries
      - An instance is an element of a set
  - - - Analysis
        
        develops an entity-relationship model, capturing data requirements while ignoring implementation details
      - Logical Design
        
        converts the entity-relationship model into tables, columns, and keys for a particular database system.
      - Physical design
        
        Adds indexes and specifies how tables are organized on storage media
- - - - depict relationship names inside a diamond
      - Depict weak entities and identifying relationships with double lines
      - Depict subtype entities with IsA relationships rather than inside of supertype entites
      - Use color, dashed lines, or double lines to convey additional information
    - - One popular convention is called "crow's foot notation",
        
        depicts cardinality as a circle (zero), a short line (one), or three short lines (many).
  - - - Allow relationships between three or more entities
      - Decompose a complex model into a group of related entities, called a subject area
      - Refer to strong entities as independent and weak entities as dependent
    - - UML
      - IDEF1X
      - Chen notation - not really standardized