Relational Data Model

• Implementation of an ER Model in a DBMS
• We will use SQL-DDL (data definition language)
  • Based on set theory and first-order logic
  • How you create tables in SQL

Types

• CHAR(len): string with a max length (always stored as the max length on the disk)
• VARCHAR: variable length string

Attribute Declarations

• NOT NULL
  • Means the value may never be NULL
  • Will reject if it is not provided and there is not a default
• DEFAULT <value>
  • If there is an insert without specifying that value, the default is used
• UNIQUE
  • Cannot have multiple entries with the same value
• PRIMARY KEY
  • Attribute(s) used as the primary key
  • Automatically conforms it to UNIQUE and NOT NULL (can be null in SQLite though)
  • There can only be one per table
• FOREIGN KEY
  • Below

Integrity Constraints (ICs)

• Conditions that must be true for any instance of the database
• Specified when schema is defined
• Checked when relations are modified
• A legal instance of a relation is one that satisfies all ICs
• Integrity Constraints

Foreign Keys

• Set of fields in one relation that used to refer to a tuple in another relation via its keys
• Constraint: Referential integrity
  • The values must be equal to a candidate key value in the referenced table or else null
• Enforcing referential integrity
  • Inserting new reference
    • Throws an error if an invalid reference is inserted
  • Deleting referenced tuple options
    • (default) Just don’t allow a deletion of a referenced tuple
    • CASCADE: Delete all the tuples that referenced it
    • SET NULL / SET DEFAULT: Set the foreign keys that referenced the tuple to null
  • Updating the primary key
    • Need to update all references

From ER Diagram

• Foreign Keys
  • Many-to-one
    • Reference can be an attribute of the entity on the many side
    • If there are extra attributes in the relationship, then a separate table could still be used
      • In that case, the primary key would be the id of the many side
      • Another option would be to just combine the tables
  • Many-to-many
    • References need to be stored in a separate table
    • Primary key of the table would be (id1, id2)
  • Exactly one
    • Make the foreign key NOT NULL
• Subclasses
  • Object oriented
    • Move all instances of the subclass to a different table, removing from the super table
    • All attributes in the subclass table, none in the original
    • One relation per subset of subclasses, with all relevant attributes
  • E/R Style
    • Just the additional attributes in the subclass table
    • One relation for each subclass
  • Using Nulls
    • Have all additional attributes be null
    • Saves space unless there are a ton of null attributes

Views

• A view is just a relation, but we store a definition rather than a set of tuples
• Like a table but instead of storing data, it stores queries on base tables