INFS2608 Lecture 5 - Relational Model and Relational Algebra

What are General Constraints?

*Additional rules* specified by users or database administrators that *define* or *constrain* some aspect of the enterprise (e.g., domain integrity).

What is a super key?

*An attribute, or set of attributes*, that *uniquely identifies a tuple* within a relation

Cartesian Product & Selection

*Cartesian product* and *Selection* can be reduced to a single operation called a *Join*

What is a Theta Join?

- "⨝" = "join" or "bowtie" For R ⨝ (p) S - Defines a relation that contains tuples satisfying the predicate p from the Cartesian Product of R and S. - p must be in form R.someattribute (some comparison operator) S.someattribute - Can rewrite Theta Join in more basic terms, using *Selection* & *Cartesian Product* i.e: *R* *⨝* p *S* = *σ*p(*R* *x* *S*) Hint: Comparison operators are shit like (<, <=, >, >=, !=)

What is set difference?

- *-* = "minus" For R - S: - Defines a relation consisting of the tuples that are *in relation R*, but *not in S*. - R & S must be union compatible

What is a Foreign Key?

- *Attribute, or set of attributes*, within one relation that *matches candidate key of some (possibly same) relation*.

What are Alternate Keys?

- *Candidate keys* that are *not selected to* be primary key.

What are Join Operations?

- *Join* is a derivative of Cartesian product. - *Equivalent* to *combination* of a Cartesian product *followed* by a selection process. - One of the most difficult operations to implement efficiently in RDMBSs, hence why they have intrinsic performance problems.

Basics of Relational Algebra

- *Relational algebra operations* work on one or more relations to define another relation without changing the original relations. - Both *operands* and *results* are *relations*, so output from one operation can become input to another operation.

What is a Candidate key?

- *Superkey (K)* such that *no proper subset is a superkey* within the relation. - In each tuple of R, values of K uniquely identify that tuple (*uniqueness*). - No proper subset of K has the uniqueness property (*irreducibility*).

Relational Algebra & Relational Calculus

- *formal languages* associated with the relational model Informally - *relational algebra* is a (high-level) *procedural language* - *relational calculus* a *non-procedural language*. Formally, both are *equivalent*. - A language that produces a relation that can be derived using relational calculus is *relationally complete* (e.g., *SQL*).

What is a Cartesian Product?

- *x* = Cartesian Product "The Cartesian product X×Y between two sets X and Y is the set of all possible ordered pairs with first element from X and second element from Y" OR R *x* S Defines a relation that is the concatenation of every tuple of relation R with every tuple of relation S. For example, from the picture, (1, a), (2, b) would be a relation.

What is Projection?

- *π* (pi) - Works on a single relation R and defines a relation that contains a *vertical subset of R*, extracting the values of specified attributes & eliminating duplicates. Equation of the form: *π* col1, col2, ..., coln (R) Equivalent SQL: SELECT col1, col2, .. coln FROM R

What is intersection?

- *∩* = "intersection" For R ∩ S: - Defines a relation consisting of the set of all tuples that are in *both* R & S - R and S must be union-compatible Can be expressed in basic operators: R ∩ S = R - (R - S) Uses SQL keyword *INTERSECT*

What is a Union?

- *∪* "Union" i.e R ∪ S - Union of 2 relations R and S (R ∪ S) defines a relation that contains all the tuples of R, or S, or both R and S, duplicate tuples being eliminated - R and S must be *union-compatible* - Maximum about of tuples possible would be (length of R + length of S) - SQL equivalent is UNION keyword

Left Outer Join

- *⟕* symbol For R *⟕* S - Produces a complete set of tuples from Table R, with the matching tuples (where available) in Table S. If there is no match, the right side will contain null.

Right Outer Join

- *⟖* symbol R *⟖* S - Produces a complete set of tuples from Table S, with the matching tuples (where available) in Table R. If there is no match, the left side will contain null.

Full Outer Join

- *⟗* symbol R *⟗* S - Includes the the additional tuples of both Left and Right Outer Joins

What is a Natural Join?

- A type of Equi Join - In an Equi Join you *explicitly* state what table column you're joining on - On the other hand, Natural Joins are *implicit*. - Joins are based on the *common columns* in the two tables being joined. (Common columns = columns that have same name across the tables) - Removes the duplicate column names from your result set - Very risky join method. Don't use.

Updating Views

- All *updates to a base relation* should be *immediately reflected in all views* that reference that base relation - If *view is updaed, underlying base relations should reflect that change too* (typically) Types of views: - Theoretically *not updateable* - Theoretically *updateable* - *partially updateable*

What is selection?

- Also known as Restriction - *σ* (sigma) - Works on a *single relation R* & defines a relation that contains only those *tuples of R that satisfy the specified condition (predicate).* Equation of the form *σ* predicate (R). Equivalent SQL: SELECT * FROM R... WHERE predicate

What is a view (technical terms)?

- Dynamic result of one or more relational operations on base relations to produce another relation. - They're *virtual relations*; *Don't necessarily exist* in the database but are *produced upon request*, at time of request. - *Contents* are defined as a query on one or more base relations. - They're *dynamic*, i.e changes made to their underlying base relations immediately *reflected in the view*.

What are the purposes of Views?

- Powerful and flexible *security* mechanism by *hiding parts of database from certain users.* - *Permits users to access data in a customized way*, i.e same data can be seen by different users in different ways, at same time. - Can *simplify* complex *operations on base relations*.

What is NULL

- Represents value for an attribute that is currently unknown or not applicable for tuple. - Deals with *incomplete* or exceptional *data*. - Represents the absence of a value and is *not the same as zero or spaces*, which are values.

What are some restrictions of the types of modifications that can be made through views？

- Updates are *allowed* if query involves a single base relation & contains a candidate key of base relation - Updates are *not allowed* involving *multiple base relations*. - Updates are *not allowed* involving *aggregation or grouping operations*

Outer Joins

- Used to display rows in the result that do not have matching values in the join column. - Outer joins first perform inner joins (i.e return tuples that are common to both tables), and then return rows didn't have matching values.

What are the 5 basic operations in relational algebra?

1. Selection 2. Projection 3. Cartesian Product 4. Union 5. Set Difference We also have Join, Intersection & Division operations but they *can be expressed in terms of the 5 basic operations*.

The 5 common Join Operations

1. Theta Join 2. Equi Join (a type of Theta Join) 3. Natural Join 4. Outer Join 5. Inner Join

What is a Primary Key?

A *Candidate key selected* to identify tuples uniquely within relation.

What is a Relational Database?

A *collection of normalized relations* with distinct relation names

What is a tuple?

A *row* of a relation (can also be called a record)

What is a relation?

A *table* with columns and rows (can also be called a file)

What is an Equi Join?

A Theta Join where the predicate is just '='

Formula for Cartesian Product of n Sets (probs useless)

Any set of n-tuples from this Cartesian product is a relation on the n sets.

What is Entity Integrity?

In a base relation, *no attribute* of a primary key can be *NULL*.

What is an attribute?

Name *column* of a relation (can also be called a field)

What is a Base Relation?

Named *relation* corresponding to an *entity* in conceptual schema, whose tuples are *physically stored in database*. aka opposite of a view

What are the core properties of relations?

Relation name is unique (from others in relational database schema). - Each cell of relation contains exactly one atomic (single) value. - Each attribute has a distinct name. - Values of an attribute are all within the same domain. - Each tuple is distinct; there are no duplicates. - Order of attributes has no significance. - Order of tuples has no significance (theoretically).

What are relational schemas & relational database schemas?

Relational schema = named relation defined by a set of attribute and domain name pairs. (basically, schema for a table) Relational database schema = Set of relation schemas, each with a distinct name. (basically, schema that describes how the tables connect. schema of schemas lmao)

What is a domain?

Set of *allowable values* for one or more attributes.

What is Cardinality?

The *number of tuples* in a relation.

What is a degree?

The *number* of attributes in a relation

What is union-compatibility?

Two relations R and S , are said to be UNION COMPATIBLE, only if they satisfy these two conditions : 1. Both the relations should be of same arity (same number of attributes) 2. Domain of the similar attributes should be same

What is Referential Integrity?

Whenever a foreign key value is used it must reference a valid, existing primary key in the parent table OR its value must be wholly NULL.