Relations (Joined, Derived)
Join operations take two relations and return another relation as result. These additional operations are typically used as subquery expressions in the from clause. In relational databases, a join operation matches records in two tables. The two tables must be joined by at least one common field. That is, the join field is a member of both tables. Join condition defines which tuples in the two relations match, and what attributes are present in the result of the join.Join type defines how tuples in each relation are treated that do not match any tuple in the other relation (based on the join condition). Equi-join: It is a join in which the joining condition is based on equality between values in the common columns. Common columns appear redundantly in the resulting table. Natural join: Natural join can be understood as an equi-join but with an elimination of the duplicate columns in the result table. Inner join: An inner join is a join in which the database management system selects records from two tables only when the records have the same value in the common field that links the tables. Outer join: An outer join is a join which returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join conditions. Such rows are not returned by a simple join. There are three types of outer join and they are left outer join, right outer join and full outer join. Derived relation is a type of a relation in which SQL allows a subquery expression to be used in the from clause.
Summary
Join operations take two relations and return another relation as result. These additional operations are typically used as subquery expressions in the from clause. In relational databases, a join operation matches records in two tables. The two tables must be joined by at least one common field. That is, the join field is a member of both tables. Join condition defines which tuples in the two relations match, and what attributes are present in the result of the join.Join type defines how tuples in each relation are treated that do not match any tuple in the other relation (based on the join condition). Equi-join: It is a join in which the joining condition is based on equality between values in the common columns. Common columns appear redundantly in the resulting table. Natural join: Natural join can be understood as an equi-join but with an elimination of the duplicate columns in the result table. Inner join: An inner join is a join in which the database management system selects records from two tables only when the records have the same value in the common field that links the tables. Outer join: An outer join is a join which returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join conditions. Such rows are not returned by a simple join. There are three types of outer join and they are left outer join, right outer join and full outer join. Derived relation is a type of a relation in which SQL allows a subquery expression to be used in the from clause.
Things to Remember
- Join operations take two relations and return another relation as result. These additional operations are typically used as subquery expressions in the from clause.
- In relational databases, a join operation matches records in two tables. The two tables must be joined by at least one common field. That is, the join field is a member of both tables.
- Join condition defines which tuples in the two relations match, and what attributes are present in the result of the join.Join type defines how tuples in each relation are treated that do not match any tuple in the other relation (based on the join condition).
- Equi-join: It is a join in which the joining condition is based on equality between values in the common columns. Common columns appear redundantly in the resulting table.
- Natural join: Natural join can be understood as an equi-join but with an elimination of the duplicate columns in the result table.
- Inner join: An inner join is a join in which the database management system selects records from two tables only when the records have the same value in the common field that links the tables.
- Outer join: An outer join is a join which returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join conditions. Such rows are not returned by a simple join. There are three types of outer join and they are left outer join, right outer join and full outer join.
- Derived relation is a type of a relation in which SQL allows a subquery expression to be used in the from clause.
MCQs
No MCQs found.
Subjective Questions
Q1:
Define Intelligence.
Type: Very_short Difficulty: Easy
Q2:
What are the characteristics of Intelligence ?
Type: Short Difficulty: Easy
<li>It is an innate mental ability which grows and is influenced by the environment.</li>
<li>It shows the capacity to adapt to new or changed situation quickly and correctly.</li>
<li>It consists in the ability to carry on the higher mental processes such as reasoning, criticism, application, judgment etc.</li>
<li>It implies the capacity to learn difficult tasks and the ability to solve increasing difficult</li>
<li>It shows the capacity to observe relationships and detect absurdities.</li>
</ol>
Q3:
What are the factors affecting Intelligence ?
Type: Long Difficulty: Easy
<li><strong>Hereditary Factors </strong></li>
</ol>
<p>Hereditary refers to the characteristics which are transmitted by parents to their offspring through genes. Different researchers have supported for this factor to determine the intelligence. Adoption studies are important to find out the effect of hereditary and environment in intelligence. Compare IQ of adopted children with other members of adoptive family, their biological family. Another method is twin studies. Fraternal twins pair are identical in 50% of their genes, identical twins pairs are identical in 100% of their genes. Researcher have shown that the correlation between the IQ scores of identical twins in high even though they are reared part. Somme investigation have been able to locate identical twins who had been separated inn childhood and rared in different environment. Studies of these types found high concentrations between separated twins to range from o.67 to 0.78. Most of the adoption studies have shown that children’s IQ correlate more strongly with those of biological than those of their adoptive parents. Most of the adoption studies has shown that children’s IQ correlate more strongly with those of biological than those of their adoptive parents.</p>
<ol start="2">
<li><strong>Environmental factors</strong></li>
</ol>
<p>Environmental condition also plays a substantial role. Researcher have found that an enriched environment help in IQ (9good have environment, mother-child interaction, child rearing, schooling). Studies have also found that children of less educated parents have poor environment and opportunities limiting their innate potentials and associated with low IQ. One of well controlled adoption study done in France found that transferring an infant from a family having low social economic status to a home of high economic social status improves IQ score by 12 to 16 points. According to Wahl stern (19997), delays in schooling cause IQ to drop 5 points per year. In studies of identical twins, sibling pairs who are not twins and pairs of unrelated person: it is found that the pairs reared together correspond more closely in IQ than do the pairs reared apart. Severe malnutrition, before birth or during the early years of thee life, can limit neurological development and have long-term impact on cognitive development. The exposure to lead is strongly linked to intellectual deficit. Environmental factors are thought to be responsible for this increase. (Better nutrition, education, exposure to computer Medias, more cognitivity demands jobs.)</p>
Videos
No videos found.
Relations (Joined, Derived)
Joined Relation
Join operations take two relations and return another relation as a result. In general, these additional operations are used as subquery expressions in the from clause. In relational databases, a join operation matches records in two tables. The two tables must be joined by at least one common field. That is, the join field is a member of both tables.Join condition defines which tuples in the two relations match, and what attributes are present in the result of the join.Join type defines how tuples in each relation are treated that do not match any tuple in the other relation (based on the join condition).
Equi-join: It is a joint in which the joining condition is based on equality between values in the common columns. Common columns appear redundantly in the resulting table.
Natural join: Natural join can be understood as an equi-join but with an elimination of the duplicate columns in the result table.
Inner join: An inner join is a join in which the database management system selects records from two tables only when the records have the same value in the common field that links the tables.
Outer join: An outer join is a join which returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join conditions. Such rows are not returned by a simple join. There are three types of outer join and they are:
- LEFT OUTER JOIN: The LEFT OUTER JOIN keeps the stray rows from the "left" table (the one that is listed first in your query statement). In the result set, columns from the other table are filled with NULL values that have no corresponding data.
- RIGHT OUTER JOIN: The RIGHT OUTER JOIN keeps stray rows from the right table and fills columns from the left table with NULL values.
- FULL OUTER JOIN: The FULL OUTER JOIN keeps all stray rows as part of all result set.
Example:
Select *From course Innerjoinprereq on course.course_id = prereq.course_id
Select *From courseleftouterjoin prereq on course.course_id = prereq.course_id
Derived Relation
Derived relation is a type of a relation in which SQL allows a subquery expression to be used in the from clause. Example for the derived relation is given below:
Find the average account balance of those branches where the average account balance is greater than $1200.
select branch_name, avg_balance
from (select branch_name, avg (balance)
from account
group by branch_name )
as branch_avg ( branch_name, avg_balance )
where avg_balance > 1200;
References:
- H.F.Korth and A. Silberschatz,"Database system concepts",McGraw Hill,2010
- A.K.Majumdar and p, Bhattacharya,"Database Management Systems",Tata McGraw Hill,India,2004
- F.Korth, Henry. Database System Concepts. 6th edition.
Lesson
Relational Languages and Relational Model
Subject
Computer Engineering
Grade
Engineering
Recent Notes
No recent notes.
Related Notes
No related notes.