CS348: Databases

Back to UWaterloo

Introduction

Relational Model

relational model organizes information in a finite number of relations

integrity constraints are laws, that must be true in every valid database instance

example:

SQL

builds on the relational calculus

First Order SQL

Summary:

Details:

Example: List all authors who always publish with someone else

SELECT DISTINCT a1.name 
FROM author a1, a2 
WHERE NOT EXISTS (
  SELECT * FROM publications p, wrote w 
  WHERE p.pubid == w.publication
    AND a1.aid = w.author
    AND a2.aid NOT IN (
      SELECT author
      FROM wrote
      WHERE publication = p.pubid
        AND author <> a1.aid
    )
)

Modifying Database

More SQL syntax for actually modifying state based on first order logic

Aggregation

Extension of first order SQL

Data Modeling and Entity-Relationship Model

Normalization Theory

Query Execution

We use relational algebra for implementing query execution

Transaction Execution