Relational Model and Relational Algebra

Relational Model

Definition

• relational database: a set of relations

• Relations :

  • Schema: specifics name of relation, plus name and type of each column
    • Student(sid:string, name:string, login: string, gpa: real)
  • Instance:a table, with rows and columns
    • Number of rows: cardinality
    • Number of fields: degree or arity
    • can think of as set of tuples or records

• Domain constrains

  • the values that appear in a column must be drawn from the domain associated with that column

  • relation schema: $R(f_1:D_1,…,f_n:D_n)$

    • $Dom_i$:set of values with domain names $D_i$

    • An instance of R that specifies the domain constraints in the shcema is a set of tuples with n fields:

      $$

Verfeinerung des relationalen Schemas

“Das im Initialentwurf erzeugte relationale Schema lässt sich oftmals noch verfeinern. Dabei werden einige der Relationen eliminiert , die für die Modellierung von Beziehungstypen eingeführt worden waren. Dies ist aber nur für solche Relationen möglich, die die allgemeinen 1:1-, 1:N- oder N:1-Beziehungen repräsentieren. Die Elimination der Relationen, die die allgemeinen N:M-Beziehungstypen repräsentieren, ist nicht sinnvoll und würde i.A. zu schwerwiegenden “Anomalien“ führen.

Bei der Eliminierung von Relationen gilt es folgende Regel zu beachten:

Nur Relationen mit gleichen Schlüssel zusammenfassen!” ¹

Vermeidung von Null-Werten

Relational Algebra

$\cup$ Union

- Difference

The difference between R and S is the set consisting of tuples that belong to R but not to S. Notated as: $$ R - S = \lbrace t | t \in R \and t \notin S \rbrace $$

$\delta$ Selection

The selection operation is the selection of a number of tuples in a given relation R according to certain conditions F to form a new relation, denoted as: $$ \delta_F(R) = \lbrace t | t \in R \and F(t)= true \rbrace $$ Where $\delta$ is the selection operator, F means select event, it is a logical expression connected by the operation object (property name, constant, simple function), arithmetic comparison operator (<,≤,>,≥,=,≠) and logical operator ($\land,\lor,\neg $), which takes the value of “true” or “false”, its basic form is: $$

$$

$\pi$ Projection

The projection operation is also a monomial operation, which selects certain attributes (columns) from a relation, rearranges these attributes, and finally removes the duplicate rows from the resulting result to obtain a new relation. That is, the vertical decomposition operation is performed on the relation from the perspective of columns, and the corresponding columns are taken out from left to right according to a specified number of attributes and order, and the duplicate tuples are deleted.

$\Join$ Join (Verbund)

The join operation is a binomial operation, which is a Cartesian product of two relations in which a tuple between attributes satisfying certain conditions is selected to form a new relation, and the join is also called theta join.

Der natürliche Verbund

…

$\div$ Division

The division operation is a binomial operation given the relations R(X, Y) and S(Y, Z), where X, Y, Z are attributes or sets of attributes. y in R and y in S can have different attribute names, but must come from the same set of domains.