Relational Algebra

-
Relational Algebra

Relational algebra defines the theoretical way of manipulating table contents using the eight relational database operators: SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE.

  • UNION combines all rows from two tables, excluding duplicate rows. The tables must have the same attribute characteristics (the no of columns, the names of columns and domains must be identical. Those tables are said to be union compatible tables)
  •  INTERSECT yields only the rows that appear in both tables.( The tables must be union-compatible)

  • DIFFERENCE yields all rows in one table that are not found in the other table. it subtracts one table from the other ( The tables must be union-compatible)
  • PRODUCT yields all possible pairs of rows from two tables—also known as the Cartesian product. Therefore, if one table has six rows and the other table has three rows, the PRODUCT yields a list composed of 6 × 3= 18 rows.
  • SELECT, also known as RESTRICT, yields values for all rows found in a table that satisfy a given condition. SELECT yields a horizontal subset of a table.

  • PROJECT yields all values for selected attributes. PROJECT yields a vertical subset of a table.
  • JOIN allows information to be combined from two or more tables.

o   Natural Join (Inner Join)
This is a binary operator that combines the PRODUCT and SELECT operators into a single operation. In other words, it joins two tables together using a shared key
(primary/ foreign key)

Table 1


Table 2



Result


o   Equijoin
Links tables on the basis of an equality condition that compares specified columns of each table. The outcome of the equijoin does not eliminate duplicate columns

o   Theta Join
If any other comparison operator is used, the join is called a theta join

o   Outer Join
Natural joins can “lose” some tuples from one relation, if they do not have a match in the other relation. Outer join may retain the tuples which would be lost by natural join.
§  Left outer Join
Left outer join of A and B retains all of A

§  Right outer Join
Right outer join of A and B retains all of B

  • DIVIDE extracts rows whose column values match those in the second table, but only returns columns that don’t exist in the second table.

           Table 1
          
          Table 2



           
           Result
           

Comments

Post a Comment

Popular posts from this blog

Normalization

-
Image
Normalization is a process for evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihood of data anomalies. Normalization starts by identifying the dependencies of a given relation and progressively breaking up the relation (table) into a set of new relations (tables) based on the identified dependencies. Normal Forms 0NF            Multi-valued attributes exists 1NF           Any multi-valued attributes (repeating groups) have been removed. 2NF           Any partial functional dependencies have been removed 3NF           Any transitive dependencies have been removed BCNF           Any anomalies resulting from functional dependencies have been removed 4NF           Any multi valued dependencies have been removed 5NF           Any remaining anomalies have been removed The table in 0NF : eliminate the nulls by making sure that each repeating group attribute contains an appropriate data va
Read more

Database Types

-
Types of Databases Databases can be classified according to the number of users, o    Single user database - A single-user database that runs on a personal computer is called a desktop database o    Multi user database ·          Workgroup database - the multiuser database supports a relatively small number of users (usually fewer than 50) or a specific department within an organization ·          Enterprise database Databases can be classified according to the database location(s), o    Centralized database - a database that supports data located at a single Site. o    Distributed database – a database that supports data distributed across several different sites The most popular way of classifying databases today, however, is based on how they will be used and on the time sensitivity of the information gathered from them. o    operational database (sometimes referred to as a transactional or production database) - A database that is designed primarily to suppor
Read more

Entity Relationship Diagram (Pat II)

-
Image
Mapping Binary Relationships One to one Relationship  Partial participation (Partial participation is represented by single lines.)           Not every professor teaches every student. Every student is not learnt by every professor.             Relational Mapping                                                                   Professor ( Name , Phone , Address )                                                                                       ↑FK            Course   ( CourseId  , CourseName , Credits , Name) Total Participation (Total participation is represented by double lines.)             Every professor teaches every student. Every student is learnt by every professor           Relational Mapping                      Professor ( Name , Phone , Address , CourseId , CourseName , Credits) One to many Relationship (Partial participation)           Relational Mapping                                          
Read more