MySQL Learning Note 002 -- Relational DB

• Relation = table
• Tuple = row
• Attribute = column
• Domain = allowed set of values for an attribute
• Arity: number of attributes in a relation
• SuperKey: a set of one or more attributes, identify uniquely a tuple in the relation
• CandidateKey: The smallest subset among all superKeys

• PrimaryKey: a candidate key that is chosen by the database designer. Automatic unique constraint.

• UniqueKey: A Unique key constraint on one or a set of columns. (There can be many unique constraint defined per table, but only one Primary key constraint defined per table.)

• Relation Schema = a list of attributes and their corresponding domains

  • Format: relation_name (primary key, primary key, attribute)

• ForeignKey: an attribute set A from relation 1 that refers to the primary-key B of relation 2

  • Relation 1 is called referencing relation

  • Relation 2 is called referenced relation

• Referential Integrity Constraint: the values appearing in specified attributes of any tuple in the referencing relation also appear in specified attributes of at least one tuple in the referenced relation.

Join

• Inner Join.

Inner join return rows when there is at least one match of rows between the tables.

• Right Join.

Right join return rows which are common between the tables and all rows of Right hand side table. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.

• Left Join.

Left join return rows which are common between the tables and all rows of Left hand side table. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.

• Full Join.

Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.

Relational Algebra

Operators

• select: σ

Selects tuples that satisfy a given predicate (p). And return a new relation. [σp(r)]

predicate = comparison (among attributes), value, combination

• Allow comparisons using =, ≠, >, ≥. <. ≤
• Combine several predicates using -- ∧ (and), ∨ (or), ¬ (not)

e.g. σ dpt_name=“Physics” ∧ salary>90000 (instructor)

• project: ∏

A unary operation that returns its argument relation, with certain attributes left out

, where A1, A2 are attribute names and r is a relation name.

• No duplicates
• Only k columns, the rest are erased

eg.

• union: ∪

Combine two relations that are compatible --

1. have the same arity
2. domains are compatible

格式一模一样的两个表

• intersection: ∩

Find tuples that are in both compatible relations.

• set difference: –

Find tuples that are in one relation but are not in another compatible relation (r − s = in r not in s)\

• Assignment: ←

Assign the result relation to a temp relation variable.

• Cartesian product: x

Combine information from any two relations. r1 X r2 = each row in r1 combine with each row in r2.

Number of rows? |r1| * |r2|

表的格式不限

• rename: ρ

ρx (E) -- name the result of E as 'x'.

ρx(A1,A2,...,An) (E) -- name the result of E as 'x', and name the attributes as A1, A2..An.

• join: ⋈

Combine a select operation and a Cartesian-Product operation into a single operation.