Database

• DB is an organized collection of inter-related data that models some aspects of the real world.
• DBMS is a software that allows users to store and analyze information in a database.
  • A general purpose DBMS supports the definition, creation, querying, update, and administration of databases in accordance to some data models

Data models

Relevant data models today are:

• Relational
• Column
• Key value
• Graph
• Document (JSON, objects, etc.)
• Array (tensor, vector, matrix)

Relational model

Key ideas

• 3 key ideas:
  • Structure: the data model and content definition is independent from its physical implementation (for example, memory address, disk offset, etc.) by using relations
  • Integrity: users can also specify constraints to data to avoid invalid data (for example, data types)
  • Manipulation: declarative API for access and modification via relations
• Consequence: users are isolated from low-level implementation, letting DBMS optimizes the implementation according to the environment, content, and workload
  • Can also re-optimize in runtime!
  • The hierarchy as follows:
    • Storage
    • Physical layout (pages, files, etc.)
    • ================ separation = independence!
    • Logical schema (SQL)
    • External schema (SQL views)
    • App logic…

Basic concepts

• A relation is an unordered set that contains the relationship of attributes that represent entities
  • N-ary relation = table with n columns
• A tuple is a set of attribute values (or domains) in the relation
  • Values are usually scalar
• A relation has a primary key to uniquely identify a single tuple
  • Some DBMS auto create primary key if not defined
  • DBMSs can auto generate unique primary keys via identity column (type SEQUENCE in psql, AUTO_INCREMENT in mysql)
  • Primary key can be 1 column or combination of multiple columns