The Relational Model

The Relational Model

1. A tuple

   • an attribute, or field.
     • A domain
       • the set of possible values they can take.
     • Each element in a tuple
     • the components of a room (door, window, etc)
     • each attribute has a domain (type of window, color of the door, etc).
     • corresponds to a single column in the table
   • The elements in each tuple are named and not ordered
     • so it is easy to access and query specific pieces of information.
   • a mathematical concept
     • that represents an ordered list of elements.
   • often referred to as a single record
   • frequently implemented as a row in a table.
   • A tuple is like a room
     • it has multiple attributes (doors, windows, etc)
     • it's ordered.
   • corresponds to a single row in the table

2. A relation

   • A set
     • Every relation is a set
       • every relation is a mathematical set of ordered elements
     • Not every set is a relation
       • to qualify as a relation
         • a set must also adhere to certain mathematical constraints
           • such as the requirement that each tuple in the set have the same number of elements and that the values of those elements be atomic or scalar.
   • A Table
     • a common implementation of a relation
     • a table is a physical implementation of a relation.
     • Every relation can be perceived as a table
       • a relation is often implemented as a table
         • in a relational database management system (RDBMS).
           • Object-oriented databases (OODBs)
             • a type of database management system (DBMS)
             • based on the object-oriented programming (OOP) paradigm
             • They store and manage data in the form of objects
               • which are instances of classes and have attributes and methods
             • allow for the modeling of complex relationships and structures
               • such as inheritance and polymorphism
                 • which are not easily represented in relational databases
             • key features
               • Support for encapsulation
                 • which allows for the hiding of internal details and implementation of an object from the outside world.
               • Support for inheritance
                 • which allows for the creation of new classes based on existing classes.
               • Support for polymorphism
                 • which allows for the use of a single interface to represent multiple types of objects.
             • typically used in applications that require the modeling and manipulation of complex data structures and relationships
             • the object identifier (OID).
               • The alternative to the primary key used in object-oriented databases
               • a unique identifier assigned to an object in an object-oriented database
               • It is used to identify and retrieve objects stored in the database
                 • similar to how a primary key is used to identify and retrieve records in a relational database
     • Not every table is a correct perception of a relation
       • a table may not adhere to the mathematical constraints of a relation
         • such as having duplicate rows or not having unique keys.
     • A candidate key
       • a set of one or more columns in a table that can uniquely identify each row in the table
       • it is a set of columns that can be used as a primary key
       • A candidate key must have the following properties
         • Uniqueness
           • No two rows can have the same values in the candidate key columns.
         • Non-nullability
           • The columns in the candidate key cannot contain null values.
       • The purpose of a candidate key is to ensure that every row in a table can be uniquely identified and to prevent data duplication.
   • a set of tuples, or rows
     • that are organized in a table.
   • A relation is like a floor
     • it has multiple rooms (tuples)
   • it's organized.

3. A schema

   • a plan of how data is organized
   • it's like a blueprint.
     • A schema is like the blueprint of the house
       • it shows the overall structure and organization of the house.
   • It includes logical constraints
     • such as table names, attributes, and entity relations.
   • commonly represented visually
     • to convey the overall structure of the data.
   • Some databases use the term schema interchangeably with the database itself
     • for example, MySQL uses schema interchangeably with the database.

4. three main aspects

   • structural
     • The data in a relational database is perceived by the user as tables, and nothing but tables.
       • the data is organized in a tabular format, with rows and columns, and the user interacts with the data through these tables.
     • Structural aspect is like the recipe
       • it shows how the ingredients (data) should be organized and presented.
   • manipulation
     • The relational model provides a set of operators
       • such as SELECT, JOIN, and UPDATE
         • can be used to manipulate the data in the tables.
         • These operators allow the user to query, update, and modify the data in the tables.
     • Manipulation aspect is like cooking tools
       • it allows the cook to manipulate the ingredients in different ways (query, update, modify) to create different dishes.
   • integrity
     • The relational model also includes integrity constraints
       • which are rules that ensure the consistency and accuracy of the data in the tables.
       • These constraints can include things like unique keys, referential integrity, and domain constraints.
       • constraints that maintain referential integrity
         • Primary key constraint
           • Ensures that each row in a table has a unique identifier
           • A Composite Key
             • a primary key made up of two or more columns
             • It is used when no single column can uniquely identify a record.
         • Foreign key constraint
           • Enforces referential integrity by ensuring that any value entered into a foreign key column in one table must match a primary key value in the referenced table.
         • NOT NULL constraint
           • Ensures that a column does not contain null values.
         • UNIQUE constraint
           • Ensures that no duplicate values can be entered into a specific column or set of columns.
         • CHECK constraint
           • Allows for the definition of custom rules to validate data input.
         • DEFAULT constraint
           • Allows for the definition of a default value for a column when no value is specified.
     • The integrity aspect of the relational model answers the question of how one can mess up the database if they wanted to.
     • Integrity aspect is like hygiene and quality control
       • it ensures that the ingredients are fresh and safe to eat, and the dish is cooked correctly.

5. proposed in 1969 by Edgar F. Codd

   • as a way to organize data in a structured and efficient manner.

6. includes a set of operators

   • such as SELECT, JOIN, and UPDATE
     • that can be used to manipulate the data in the relations.

7. SQL (Structured Query Language)

   • a practical implementation of the relational concept
     • a table as implemented by MySQL is a physical representation of data stored on disk.
   • the standard language used to manage relational databases.
   • implemented with specific storage engines and have unique features and performance characteristics
     • such as the ability to define indexes, partition tables and use of specific data types.
   • Data manipulation
     • Requesting data
     • Inserting data
     • Deleting data
     • Changing data
   • Data definition
     • Creating tables
     • Deleting tables
     • Changing tables
   • Data types
     • BOOLEAN
       • true/false
     • INTEGER
     • DECIMAL
       • exact fixed point number —good for money
     • DOUBLE
       • 64 bit floating point numbers
     • DATE
     • DATETIME
       • a date plus time —good for creation/modification times
     • CHAR(n)
       • fixed length character string
     • VARCHAR(n)
       • variable length character string
     • TEXT
       • strings of text
     • BLOB
       • binary storage
   • Natural join
     • based on matching column names and types
     • return only the rows that have matching values in both tables being joined
     • Relational model: JOIN is the ‘natural join’
   • Inner join
     • return only the rows that have matching values in both tables being joined
     • uses an explicit join condition specified in the ON clause.
   • Cartesian product
     • also known as a cross join
     • returns every possible combination of rows from both tables being joined
     • It does not rely on matching column names or an explicit join condition, but rather returns all possible combinations of rows from the two tables
       • This can result in a large number of rows and is typically not desired unless it is the intended outcome.
     • SQL: 'JOIN' is the cartesian product of the tables

8. relational databases is like a library.

   • A library has books
     • which are organized in shelves
     • each book has its own specific information
   • a librarian uses a catalog system
     • to find a specific book

9. an abstract theory of data management

   • such as set theory and predicate logic.
   • that is based on sound mathematical principles

10. It provides a way to organize data

    • in a structured and efficient manner.

11. a relational variable

    • a mathematical construct that represents a set of tuples (rows) with a specific set of attributes (columns)
    • a pure mathematical construct
      • which is independent of any specific implementation

12. Manipulation

    • Three fundamental operators
      • Restrict
      • Project
      • Join
      • The result of any of these operations will be a table

13. Relational expressions

    • Output from each expression is a table
    • Output from one expression can form input to another
    • Relational expressions can be nested

14. Optimisation

    • Relational languages are declarative languages
    • The query optimizer is vital!

15. Two types of languages

    • Procedural
      • Series of steps
      • Interpreter
      • Need to ensure steps produce result
    • Declarative
      • Describe what you want
      • Interpreter figures out the steps
      • Optimiser is very important!
      • Less code = more productive

16. the Catalogue

    • a table or set of tables that contains metadata about the database
      • this includes information about the tables, columns, and constraints in the database, as well as information about the users and their permissions.