Chapter 4 Relational Algebra and Relational Calculus

Unary Operation  There are two of unary operations. They are selection and projection.

  

 Selection or Restriction

Opredicate(R) The selection operation works on a single relation R and defines a relation that contains only those tuples of R that satisfy the specified condition (predicate).

Example : List all staff with a salary greater than 10,000.

Osalary>10000(Staff)
The input relation is staff and the predicate is salary>10000. The selection operation defines a relation containing only those staff tuples with a salary greater than 10000. More complex predicates can be generated using the logical operators ^(And),v (OR ) and ~(NOT)

Projection 

IIa1,....,an(R)  The projection operation works on a single relation R and defines a relation that contains a vertical subset of R, extracting the values of specified attributes and eliminating duplicates.

Example : Produce a list of salaries  for all staff, showing only the StaffNo,fName,lName, and salary details.

IIstaffNo,fName,lName,salary(Staff) 

Unioin :
R U S The union of two relations R and S defines a relation that contains all the tuples of R, or S or both R and S, duplicate tuples being eliminated. R and S must be union-compatible.
Example : 
List all cities where there is either a branch office or a property for rent.

IIcity(Branch) U IIcity(PropertyForRent)

 Set difference
R-S  The set difference operations defines a relation consisting of the tuples that are in relation R, but not in S. R and S must be union-compatible
 IIcity(Branch) - IIcity(PropertyForRent) ( take only different )

Intersection
 
   

Chapter 3 Relation Model

1.) Relation Data Structure
- Relation : A relation is a table with columns and rows. Or we can say a primary key field.
- Attribute : An attribute is a named column of a relation.
- Domain : A domain is the set of allowable values for one or more attributes.
- Tuple : A tuple is a row of a relation.
- Degree : The degree is number of attributes or number of fields.
- Cardinality : The cardinality of a relation is the number of tuples it contains.
- Relational database : A collection of normalized relations with distinct relation.

Mathematical Relations:
 Cartesian Product 
  D1 = {2,4} , D2 = {1,3,5} 
D = D1 x D2 = { (2,1),(2,3),(2,5),(4,1),(4,3),(4,5) };
  n
X Di
i=1 


2.) Database Relations
- Relation Schema : A named relation defined by a set of attribute and domain name pairs.
Let A1,A2,....An with domians D1,D2,........,Dn then the set{A1:D1,A2:D2,........,An:Dn}

- Relational Database Schema: A set of relation schema, then we can write the relational database schema, or simply relational schema, R, as : R={R1,R2,.....,Rn}
- Property of Relations:
  + the relation has a name that is distinct from all other relation names in the relational schema.
  + Each cell of the relation contains exactly one atomic (single) value;
  + Each attribute has a distinct name.
  + The value of an attribute are all from the same domain.
  + Each tuple is distinct. There are no duplicate tuples.
  + The order of attributes has no significance.
  + In a relation, the possible value s for a given position are determined by the set, or domain, on which the position is defined. In a table the values in each column must come from the same attribute domain.
  + In a set, no elements are repeated. there are no duplicate tuples.

3.) Relational Keys
- Super key :  An attribute, or set of attributes, that unique identified a tuple  within a relation.
- Candidate Key :  A super key such that no proper subset is a super key within the relation.
- Primary Key :  The candidate key that is selected to identify tuples uniquely within relation.
- Foreign Key : An attribute, or set of attributes, within one relation that matches the candidate key of some ( possibly the same ) relation.

Null represents a value for an attribute that is currently unknown or is not applicable for this tuple.

3.) Entity Integrity In a base relation, no attribute of a primary key can be null.

Referential Integrity If a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation or the foreign key value must be wholly null.

General Constraints Additional rules specified by the users or database administrators of a database that define or constrain some aspect of the enterprise.

4.) Terminology
Base Relation : A named relation corresponding to an entity in the conceptual schema whose tuples are physically stored in the database.

View: The dynamic result of one or more relational operations operating on the base relations to produce another relation. A view is a virtual relation that does not necessary exist in the database but can be produced upon request by particular user at the time of request.

The purpose of Views
- It provides a powerful and flexible security mechanism by hiding parts of the database from certain users. Users are not aware of the existence of any attributes or tuples that are missing from the view.
- It permits users to access data in a way that is customized to their needs, so that the same data can be seen by different users in different ways, at the same time.
- It can simplify complex operations on the base relations.
- A user might need Branch tuples that contain names of mangers as well as the other attributes already in Branch.
- Some members of staff should see staff tuples without salary attribute.
- Attributes may be renamed or the order of attributes changed.
- Some members of staff should see only property records for those properties that they manage.

Updating Views

- Updates are allowed through a view defined using a simple query involving a single base relation and containing either the primary key or a candidate key of the base relation.
- Updates are not allowed through views involving multiple base relations.
- Updates are not allowed through views involving aggregation or grouping operations.