Database Modeling Concepts

Posted on May 23, 2024 in Computers

Data Model

A set of concepts that can describe different levels of abstraction of the structure of a database, which we call a scheme. (External: For the user view; Global: Joint; and Internal: For the computer.) A set of concepts, rules, and conventions that allow us to describe and manipulate real-world data that you want stored in the database.

Entity

An object which we store information about in the database.

Type of Entity

  • Regular or Heavy: The occurrences of this entity have their own existence.
  • Weak: Each occurrence of a regular entity on which it depends.

Interaction

Association or correspondence between entities. The graphical representation is a diamond that contains the name of the relationship (verb) and is linked to associated entities. It is characterized by the name that identifies the relationship, the degree is the number of entities involved in the relationship and may be unary, binary, or ternary.

Type of Correspondence

  • Max occurrences of an entity that can intervene at each occurrence of the entity that now forms the relationship.
  • Cardinality: Maximum and minimum occurrences of this entity, which may be related to an occurrence of one or more entities involved in the relationship.

Associations

  • Optional: Participation in an association indicates whether all instances of an entity involved in the association, but can also instances that are not associated. In this case, we speak of optionality. Optionality occurs when the lower limit is zero connectivity.

Attributes

Each of the features that the entity or relationship has. The set of possible values that can take an attribute is called a domain.

  • Simple: It cannot be decomposed (DNI).
  • Composite: Can be decomposed into other attributes (name).
  • Identifiers: Uniquely distinguish each of the instances of an entity. If there are several candidate attributes identified as the main one is elected and the rest are called alternative identifiers.
  • Descriptors: All attributes that are not attributes identifiers are called descriptors.
  • Multivalued: Those who take multiple values for each of the instances of the entity.
  • Associations: When you need an attribute that does not describe any of the entities of the relationship is represented in the relationship itself.

Dependency of Existence and Identification

Relationship types are classified by the type of entities that link. Regulars have associated regular entities, weak entities have associated weak entities. A weak type of relationship requires that the cardinalities of the entity are regular or strong (1.1).

  • Unit in existence: When in a relationship occurrences of the weak entity cannot exist without the occurrence of the entity to which it belongs regularly.
  • Unit in identification: It happens when in addition to reliance on the existence of weak entity occurrences cannot be identified only with their own attributes and add the key to the regular entity that depends on.

Generalization and Inheritance

Generalization is the type of relationship that exists between an entity and the more specific entity types that depend on it. It creates a hierarchy where a supertype distinguishes the various subtypes dependent.

Inheritance: Attributes of the supertype are inherited by subtypes.

Generalization

We observed that 2 or more entities share several attributes and/or relationships. It follows that there must be a top-level entity.

Specification

It appears that an entity has certain attributes and/or relationships that make sense for some cases but not others. It is convenient to define subtypes.

Types of Generalization

  • Total: All the instance of the supertype is one of the subtypes. The union of the instances of the subtypes is the supertype.
  • Partial: Supertype instances need not belong to any of the subtypes and the union of the subtypes is different from the supertype.
  • Disjunctive or exclusive: Each instance of the supertype can be as most one subtype. The intersection of the instances of the subtypes is empty.
  • Flap: Each instance of the supertype can be in multiple subtypes. The intersection of the instances cannot be empty.

Relational Model

  • Domain: Set containing all the values that can take an attribute.
  • Tuple: To the fields or columns from a table or relation is known by the name attribute and the ranks, tuples.
  • Cardinality: Number of rows that have a relationship.
  • Grade: Number of columns.

Keywords

  • Password: Field or set of fields that uniquely identifies a tuple exclusive.
  • Primary key: Field or lower set of fields that identify uniquely and exclusively to a tuple, can be only one per table. This involves the automatic creation of a primary index by the manager.
  • Key candidates: All fields or smaller set of fields that can be primary keys are called candidate keys.

Rules

  1. The content of the primary key is not always null and unique.
  2. If a domain or domains defined in a relationship R1 a primary key, if these same fields are in a relationship R2, the value of these will be a value in R1 or its value is zero. Therefore, in R2 we will be establishing a foreign key that creates a default secondary index. This allows the validation of the data appear in the database.