This post has already been read 722 times!
| Cardinality | Minimum No. of tables |
| 1:1 cardinality with partial participation of both entities | 2 |
| 1:1 cardinality with total participation of atleast 1 entity | 1 |
| 1:n cardinality | 2 |
| m:n cardinality | 3 |
Note: This is a general observation. Special cases need to be taken care. We may need extra table if attribute of a relationship can’t be moved to any entity side.
Keys of a relation: There are various types of keys in a relation which are:
- Candidate Key: The minimal set of attributes which can determine a tuple uniquely. There can be more than 1 candidate key of a relation and its proper subset can’t determine tuple uniquely and it can’t be NULL.
- Super Key: The set of attributes which can determine a tuple uniquely. A candidate key is always a super key but vice versa is not true.
- Primary Key and Alternate Key: Among various candidate keys, one key is taken primary key and others are alternate keys.
Normal Forms
- First Normal Form: A relation is in first normal form if it does not contain any multi-valued or composite attribute.
- Second Normal Form: A relation is in second normal form if it does not contain any partial dependency. A dependency is called partial dependency if any proper subset of candidate key determines non-prime (which are not part of candidate key) attribute.
- Third Normal Form: A relation is in third normal form if it does not contain any transitive dependency. For a relation to be in Third Normal Form, either LHS of FD should be super key or RHS should be prime attribute.
- Boyce-Codd Normal Form: A relation is in Boyce-Codd Normal Form if LHS of every FD is super key. The relationship between Normal Forms can be represented as: 1NF⊃2NF⊃3NF⊃BCNF
Relational Algebra: Procedural language with basic and extended operators.
| Basic Operator | Semantic |
| σ (Selection) | Select rows based on given condition |
| ∏ (Projection) | Project some columns |
| X (Cross Product) | Cross product of relations, returns m*n rows where m and n are number of rows in R1 and R2 respectively. |
| U (Union) | Return those tuples which are either in R1 or in R2. Max no. of rows returned = m+n andMin no. of rows returned = max(m,n) |
| – (Minus) | R1-R2 returns those tuples which are in R1 but not in R2. Max no. of rows returned = m and Min no. of rows returned = m-n |
| ρ (Rename) | Renaming a relation to other relation. |
| Extended Operator | Semantic |
| ∩ (Intersection) | Returns those tuples which are in both R1 and R2. Max no. of rows returned = min(m,n) and Min no. of rows returned = 0 |
|
⋈c (Conditional Join) |
Selection from two or more tables based on some condition (Cross product followed by selection) |
|
⋈c(Equi Join) |
It is a special case of conditional join when only equality condition is applied between attributes. |
|
⋈(Natural Join) |
In natural join, equality condition on common attributes hold and duplicate attributes are removed by default. Note: Natural Join is equivalent to cross product if two relations have no attribute in common and natural join of a relation R with itself will return R only. |
|
/(Division Operator) |
Division operator A/B will return those tuples in A which is associated with every tuple of B.Note:Attributes of B should be proper subset of attributes of A. The attributes in A/B will be Attributes of A- Attribute of B. |
| Extended Operator | Semantic |
| ∩ (Intersection) | Returns those tuples which are in both R1 and R2. Max no. of rows returned = min(m,n) and Min no. of rows returned = 0 |
|
⋈c (Conditional Join) |
Selection from two or more tables based on some condition (Cross product followed by selection) |
|
⋈c(Equi Join) |
It is a special case of conditional join when only equality condition is applied between attributes. |
|
⋈(Natural Join) |
In natural join, equality condition on common attributes hold and duplicate attributes are removed by default. Note: Natural Join is equivalent to cross product if two relations have no attribute in common and natural join of a relation R with itself will return R only. |
|
/(Division Operator) |
Division operator A/B will return those tuples in A which is associated with every tuple of B.Note:Attributes of B should be proper subset of attributes of A. The attributes in A/B will be Attributes of A- Attribute of B. |
SQL: As opposed to Relational Algebra, SQL is a non-procedural language.
| Operator | Meaning |
| Select | Selects columns from a relation or set of relations.Note: As opposed to Relational Algebra, it may give duplicate tuples for repeated value of an attribute. |
| From | From is used to give input as relation or set of relations from which data needs to be selected. |
| where | Where is used to give condition to be used to filter tuples |
| Group By | Group By is used to group the tuples based on some attribute or set of attributes like counting the no. of students group by department. |
| Aggregate functions | Find the aggregated value of an attribute. Used mostly with group by. e.g.; count, sum, min max. select count(*) from student group by dept_idNote: we can select only those columns which are part of group by. |
| Nested Queried | When one query is a part of other query. Solving nested queries questions can be learnt in |