• Sturcture of relational databases

    • collection of tables with unique name -> RELATION

    • row of the table (entry) -> TUPLE

    • column of the table -> ATTRIBUTE

    • specific instance of RELATION (set of TUPLEs) -> RELATION INSTANCE

    • set of permitted values of each ATTRIBUTEs -> DOMAIN of ATTRIBUTE

    • DOMAINs being indivisible -> ATOMIC DOMAIN

    • Logical design of the db -> DATABASE SCHEMA

    • Programming language notion of type definition -> RELATION SCHEMA

  • Superkey: subset of attributes of the relation which satisfies:

    • no two elements have same values on all attributes in the superkey

  • Candidate key: no proper subet is a superkey (minimal superkey)

  • primary key: candidate key chosen by db designer to primarily idenfy tuples within a relation

  • foreign key constraints: attribute A from relation r1 => primary key B from relation r2 ; value of A for each tuple in r1 == value of B for some tuple in r2

e.g.

┌───────────────────────────────────────────────────┐ │ instructor(ID, name, dept_name, salary) │ : referencing relation └──────────────────────────────────┬────────────────┘ ┌────────┘ │ ▼ ┌───────────────────────────────────────────────────┐ │ department(dept_name, building, budget) │ : reference relation └───────────────────────────────────────────────────┘

  • There could be n to 1 mapping between referencing relation / reference relation
  • composite primary key: multiple attributes form one key

e.g. create table instructor ( ID varchar(5), name varchar(20) not null, name varchar(20) not null, name varchar(20) not null, name varchar(20) not null, )

create table section

primary key (course_id, sec_id, semeters, year), foreign key (course_id) referenced relation foreign key (classroom_number)

Relational query language

pure languages - relational algebra - tuple relational calculus - domain relational calculus

relational algebra

  • procedural language

composition of relation algebra is also a relation

  • consists of six basic operators

    • select (unary) $\sigma$
      • allow comparison, combination (includes set operation)
    • project (unary) $\Pi$
      • selecting only some of the attributes
    • union $\cup$
      • compatible domain: two relations should have save arity (no. of attributes)
      • when attributes have associated types, type of ith ittributes of both input relations must be the same
    • set difference $-$
      • compatible domain: two relations should have save arity (no. of attributes)
    • cartesian product $\times$
      • combine information from any two relation
      • for same attibute name appears in two relation, we specify them by adding relation name (e.g. [instructor.ID], [class.ID])
    • join (= selection from cartesian product) $\outerjoin$
      • getting only tuples that matches the preposition

  • assignment operation : giving a name to a certain query

  • rename operation: shortcut of relational algebra expression

    • rename the name of the relation + attributes
    • only rename operation can change the name of attribute

equivalent query: not same query, but gives same result on any database