Introduction to Relational Model
Attributes
- The parameters in the database schema is known as attributes.
- The set of allowed values for the attribute is known as the domain of attribute.
- Attribute values are conventionally preferred to be atomic
null
is a special value and is available in the domain of each attribute. It indicates that the value is unknown.- The
null
value needs to be checked thoroughly since it may cause complications.
Schema and Instance
- Schema: A basic template of the database.
- Instance: Actual data in the database.
Schema
A schema is mathematically as a relation.
If there are \(n\) attributes say, \(A_1, A_2, A_3, ..., A_n\). Then \(R=(A_1, A_2, A_3, ...,A_n)\) is the relational schema.
Thus each row is an \(n\)-tuple value \(r \subset R\).
Instance
A set of tuples formed using the schema is known as the instance.
Some properties of instance:
- It can be unordered.
- There can't be identical rows.
Keys
Since we know that each row in the database is unique, we consider a set \(K\) such that \(K \subset R\).
Superkey
Now \(K\) is a superkey of \(R\) is \(K\) is sufficient enough to identify unique rows in the database for each possible relation \(R\).
Candidate key
Now if the superkey \(K\) is minimal, i.e., no subset of \(K\) is a key then \(K\) becomes a candidate key.
Primary key
One of the candidate key is selected to be the primary key
Choosing a primary key
While choosing a candidate key to be a primary key make sure that the primary key gives you some additional information.
Surrogate key
A surrogate key is a synthetic key created when the data is not unique, hence it is not derived from the data itself.
Foreign key
When referencing to another table the original table must contain a key known as foreign key that can be used as identifier for the referenced table.
Compound key
When more than one attributes are combined to be used as key, then it is called a compound or composite key.
Relational operators
SELECT
Operator
The SELECT
operator selects some specific rows based on the condition given.
Let there be a relation \(r\) already defined. Then the SELECT
operator denoted using \(\sigma_{\text{conditions}}(r)\) filters out the required rows based on the conditions given and gives them as output.
PROJECT
Operator
The PROJECT
operator selects some specific columns based on the columns asked for.
Let there be a relation \(r\) already defined. Then the PROJECT
operator denoted using \(\pi_{\text{columnnames}}(r)\) filters out the required columns based on the column_names given and removes the duplicate columns if left after the projection and gives them as output.
Tip
It is sometimes used to retrieve the original relations from the cartesian product using the following formula: \(r=\pi_r(r\text{ x }s)\)
UNION
Operator
Gives you a UNION
of two relations and returns only the unique rows after the operation. Denoted by $ \cup $
DIFFERENCE
Operator
Let us say that \(X\) and \(Y\) are two relation defined beforehand. Then the DIFFERENCE
of \(X\) and \(Y\), i.e., \(X-Y\) returns the rows that are present in \(X\) and not in \(Y\).
INTERSECTION
Operator
Let us say that \(X\) and \(Y\) are two relation defined beforehand. Then the INTERSECTION
operator returns the rows that are common to both \(X\) as well as \(Y\). It is denoted using \(\cap\).
Note
\(r\cap s = r - (r - s)\)
RENAME
Operator
RENAME
allows us to call a relation, by other name. It is denoted by \(\rho_{\text{new name}}(\text{old name})\)
Abstract
You can compose several relational operators.
NATURAL JOIN
Operator
If two relations have some identical attributes, then NATURAL JOIN
returns only the union of those rows where the identical attributes have the same values in all the relations. It is denoted using \(\bowtie\).
Example: \(r\bowtie s\)
Points to Note
- Each query input and output is a table.
- Data in the output table appears in atleast one of the input table.
- Relational algebra may not work for some complex calculations.