Relational Database


Constraints of Relational Database: Keys (1)

  • Name
    Key
    Type
    Description
    • An attribute used as a criterion for searching or ordering tuples that satisfy conditions in a database.
    1. Candidate Key: A subset of attributes used to uniquely identify tuples / Must satisfy uniqueness and minimality.
    2. Primary Key: Specifically chosen from candidate keys to be the main key / No duplicates allowed. Cannot have NULL values.
    3. Alternate Key: Remaining candidate keys other than the primary key when there are multiple candidate keys (subkeys)
    4. Super Key: Satisfies uniqueness but not minimality.
    5. Foreign Key: An attribute or set of attributes referencing the primary key of another relation.
관계형 데이터베이스의 제약 조건 중 키

Constraints of Relational Database: Integrity (2)

  • Name
    Integrity
    Type
    Description
    • Accuracy in which the values of data stored in the database match the actual values in the real world.

    Types of Integrity

    1. Entity Integrity: Primary keys must never have NULL or duplicate values.
    2. Referential Integrity: Values of non-existing foreign keys are not allowed.
    3. Domain Integrity: Given attribute values must belong to the defined domain.
    4. Key Integrity: One or more keys must exist.
  • Name
    Enhancing Data Integrity
    Type
    Description
    • Data integrity directly impacts data quality, so it is necessary to define and enhance appropriate integrity based on data characteristics.
    • Data integrity can be reinforced using applications, database triggers, and constraint conditions. (Add procedural SQL to execute integrity conditions on database trigger events. / Maintain integrity by setting database constraint conditions.)
관계형 데이터베이스의 제약 조건 중 무결성

Relational Algebra and Relational Interpretation (3)

  • Name
    Relational Algebra
    Type
    Description
    • A procedural language used to derive necessary information from a database and retrieve information.
    • Provides operators and operation rules.
    • Specifies the order of operations to be performed to obtain answers to queries.
  • Name
    Pure Relational Operators
    Type
    Description
    1. SELECT(σ): Retrieves a subset of tuples satisfying selection conditions and generates a new relation / Also known as horizontal operation.
    2. PROJECT: Extracts specified attribute values from a given attribute list and generates a new relation (removing duplicates if any) / Also known as vertical operation.
    3. JOIN: Combines two relations around common attributes to create a new relation.
    4. DIVISION: Retrieves only attributes from tuples of R that have all attribute values of S, excluding attributes that S has.
  • Name
    General Relational Operators
    Type
    Description
    • To process general set operators such as union, intersection, and difference, joining conditions must be met. (Join conditions require the same number of attributes and corresponding attributes must have the same domain.)
    1. Union: Retrieves the union of tuples and removes duplicate tuples from the generated relation.
    2. Intersection: Retrieves the intersection of tuples present in two relations.
    3. Difference: Retrieves the difference of tuples present in two relations.
    4. Cartesian Product: Retrieves ordered pairs of tuples from two relations.
  • Name
    Relational Interpretation
    Type
    Description
    • A method of expressing operations on relational data.
    • Has a non-procedural nature that defines desired information only.
    • Uses mathematical expressions when defining desired information.

Anomalies / Functional Dependencies (4)

  • Name
    Anomaly
    Type
    Description
    • Phenomenon where data duplication occurs due to dependencies of certain attributes within a table, causing operational issues with the table due to the duplication.

    Types of Anomalies

    1. Insertion Anomaly
    2. Deletion Anomaly
    3. Update Anomaly
  • Name
    Insertion Anomaly
    Type
    Description
    • Phenomenon where insertion of data into a table becomes impossible due to unintended values.

    For instance, data cannot be inserted into places where the primary key is absent.

  • Name
    Deletion Anomaly
    Type
    Description
    • Cascade deletion problem occurs where unintended values are deleted simultaneously when deleting a tuple.

    When using a key that is not the primary key and has two or more keys to delete data, data from two or more keys are deleted simultaneously (duplicate deletion leads to information loss).

  • Name
    Update Anomaly
    Type
    Description
    • Phenomenon where information inconsistency occurs when updating attribute values of tuples in a table, as only certain tuples' information is updated.
  • Name
    Functional Dependency
    Type
    Description
    • Functional dependency represents the meaning of data and serves as a constraint condition representing the real world, and is a condition that must always be maintained in the database.

    For example, in a database with student ID and name, since student ID determines name (student ID → name), student ID is the determinant and name is the dependent.

    1. Full Functional Dependency: When it is fully dependent on the primary key.
    2. Partial Functional Dependency: When it is determined by the primary key.
이상 / 함수적 종속