1. Relational Model & Relational Algebra

• Database - an organized collection of inter-related data that models some aspects of the real-world (e.g., modeling the students in a class or a digital music store)
• DBMS (DataBase Management System) - a software that allows applications to store and analyze information in a database (e.g. MySQL, Oracle, MongoDB, Snowflake, ...)
• A general-purpose DBMS is designed to allow the definition, creation, querying, update, and adminstration of databases in accordance with some data model.
• Data model - a collection of concepts for describing the data in database (e.g. relational, NoSQL, array/matrix/vectors)
• Schema - a description of a particular collection of data based on a data model.

 

Flat File Strawman

• Database is stored as comma-separated value (CSV) files that the DBMS manages.
• Issues with Flat File
  • Data Integrity
    • How do we ensure that the artist is the same for each album entry?
    • What if somebody overwrites the album year with an invalid string?
    • How do we treat multiple artists on one algum?
    • What happens when we delete an artist with an album?
  • Implementation
    • How do we find a particular record?
    • What if we now want to create a new application that uses the same database?
    • What if two threads try to write to the same file at the same time?
  • Durability
    • What if the machine crashes while our program is updating a record?
    • What if we want to replicate the database on multiple machines for high availability?

 

Relational Model

• The relational model defines a database abstraction based on relations to avoid maintenance overhead
  • Store database in simple data structures (relations).
  • Access data through high-level language, DBMS figures out best execution strategy.
  • Physical storage left up to the DBMS implementation.
• The relational data model defines three concepts:
  • Structures: The definition of relations and their contents. This is the attributes the relations have and the values that those attributes can hold.
  • Integrity: Ensure the database's content satisfy constraints. An example constraint would be that any value for the year attribute has to be a number.
  • Manipulation: How to access and modify a database's contents.
• Relation - an ordered set that contains the relationship of attributes that represent entities. It allows optimization.
• Tuple - a set of attribute values (also known as its domain) in the relation
• n-ary relation - a relation with n attributes
• Keys
  • Primary key - uniquely identifies a single tupe.
  • Foreign key - specifies that an attribute from one relation has to map to a tuple in another relation

 

Data Manipulation Languages (DMLs)

• Methods to store and retrieve information from a database.
• Procedural - the query specifies the (high-level) strategy the DBMS should use to find the desired result based on sets / bags. (relational algebra)
• Non-Procedural (Declarative) - the query specifies only what data is wanted and not how to find it. (relational calculus)

 

Relational Algebra

• A set of fundamental operations to retrieve and manipulate tuples in a relation.
  • select, projection, union, union, intersection, difference, product, join, ...