Kompakt databas - Sammanfattning av Relational Algebra PDF

Preview

Summary

Sammanfattning av Relational Algebra...

Description

Transactions & ACID In fact, a common design goal overarching database technology is that database transactions should respect:    

Atomicity (all or nothing) Consistentcy (regarding constraints & transactions preserve them) Isolation (appear to be executed as if by itself) Durability (transaction never lost once completed)

Datamodel Definition: A Data Model consists of  A mathematical representation of data e.g. tree-based, graph-based (semistructured model) or table based (relational model), custom structures  Operations on data e.g. insert/delete/update/query  Constraints e.g. uniqueness constraints, keys, data value constraints The Relational Model

The Relational Schema Student(studentId: integer, name: string, enrollement: date), or: Student(studentId, name, enrollement) Contents And Keys “contents” are sets of tuples (set semantics). Underlined attributes form a key which are sets of attributes that determine all other attributes of a tuple. Can consist of multiple attributes eg. Student(studentId, name, enrollement) Relational Algebra Designed to be used to express most queries, resulting in a query language.

Set Semantics



Union: return tuples in either R or S



Intersection: return tuples in both R and S



Difference: R-S = tuples in R but not in S



Selection:



Projection: π A 1 , A 2, … , An (R) , for a set A1,A2,…,An of attributes of R returns the relation that only has the columns for the attributes L of R.



Renaming

σ C (R) returns tuples where boolean expression C in the attributes is satisfied.



Cartesian Product: Set of all possible concatenations of tuples in R followed by tuples in S



Theta Join: Defined as



Natural Join:

R ⋈C S=σ C (R X S)

σ R ⋈ S=π L (¿¿ C(R X S)) ¿

Bag Semantics A bag is an unordered collection of items where identical items may appear multiple times. It generalizes the notion of a set of items. Suppose some element t is contained in bag A n times and in bag B m times:    



Union: contains t exactly n + m times Intersection: contains t exactly min(n, m) times Difference: contains exactly max(0, n-m) Selection: Just do the tuple-wise matching as before, but we allow multiplicity now Projection: Just as before, but with multiplicity Cartesian Product: Just as before but with bags



Natural Join: Just apply



σ R ⋈ S=π L (¿¿ C(R X S)) definition from before but with bags ¿

Extended Relational Algebra



Duplicate elimination:



Aggregation Operations: B = {2, 1, 4, 2, 1}

MIN(B) = 1, MAX(B) = 4, AVG(B) = (10/5), COUNT(B) = 5, SUM(B) = 10

γ a , MIN ( c ) →mymin , MAX ( d ) → mymax (R)



Grouping:



Extended projection:



Sorting:

τ L (R) sort R by the attributes specified in the list L...