• 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