SQL Server Mentalist

Hi Folks,

This post is part of Series Database Management Systems

Currently running topic for this series is listed as below :

Series of Database Management Systems

>>Chapter 1 : DBMS [Database Management Systems]

>>Chapter 2 : Database Core Concepts and  Applications

>>Chapter 3 : Record Storage and Primary File Organization

>>Chapter 4 : Index Structures of Files

>>Chapter 5 : Entity-Relationship Model

>>Chapter 6 : Relational Algebra <You are Here>

Continuing from my previous post on this series.

We are going to Cover the Following Points in this article

• Update Operation on Relations
• The Relational Algebra

Update Operation on Relations

Insert, Delete and Modify.

Insert: Insert is used to insert a new tuple or tuples in a relation. Insert can violate any of the 4 types of constraints

A) Domain Constraints: can be violated if an attribute value is given that does not appear in the corresponding domain (allows only permitted values).

B) Key Constraints: can be violated if a key value in the new tuple„t‟ already exists in another tuple in the Relation r(R) [avoids duplicate entries].

C) Entity Constraints: can be violated if the primary key of new tuple‟t‟ is NULL (avoids NULL values).

D) Referential Integrity: can be violated if the value of any foreign key in„t‟ refers to a tuple, that does not exist in the referenced relation [foreign key values should match with primary key values].

E.g.: insert into emp values (1, john….etc.) into emp – acceptable.

insert into emp values (1, smith…etc.) into emp

not acceptable because same eno already exist in the employee relation, violates key constraints.

insert into emp values [null, raj….etc] into emp.

not acceptable because null for the primay key eno; it violates entity integrity constraints.

Insert into emp values (1, joy, 70, etc.) into emp

not acceptable because it violates referential integrity constraints specified on dno, no department tuple exists with dno = 70.

Ø Delete:

1) It is used to delete tuples. Delete operation can violate only referential integrity if the tuple being deleted is referenced by foreign keys from other tuples in the DATABASE. To specify the deletion, a condition on the relation selects the tuple to be deleted.

2) 3 options are available if a deletion operation causes a violation.

a) The 1st option is to reject the deletion.

b) The 2nd option is to attempt to delete the referenced tuples, when link data field is deleted.

c) The 3rd option is to modify the referencing attribute values that

cause the violation.

Delete the emp tuple with eno. = 5 – this deletion is acceptable Delete the dept. tuple with dno = 10 – this deletion is not acceptable because tuples in emp refer to this tuple. Referential integrity violation will result.

Ø Modify:

1) The modify operation is used to change the values of one or more

attributes in a tuple/s some relation „R‟

2) It is necessary to specify a condition on the attributes of relation ‘R’ to select the tuple/s to be modified.

For e.g.: 1. Modify the salary of emp with empno = 101 to 1000.0

3) Modifying an attribute that is neither a primary key nor a foreign key usually causes no problems. The DBMS only needs to check to confirm that the new value is of correct data type and domain.

4) If a foreign key attribute is modified, the DBMS must make sure that the new value refers to n existing tuple in the referenced relation.

5) If you are modifying a primary key, key constraints are violated if that

modified primary key value already exists

E.g.: modify the salary of the employee tuple with eno = 100 to

10000-Acceptable

E.g.: modify the no. of the department tuple with dno = 10 to 40 – not acceptable, because it violates referential integrity.

The Relational Algebra

Relational algebra operations are divided into two groups.

1) Set operations like union, intersection, difference, and Cartesian product.

2) Developed specifically for the relational databases, they are „select,

project and join‟.

6.4.1 The Select Operation

It selects required rows from the table. This operation is used to select the subset of the tuples from a relation that satisfies a selection condition or search criteria. Mathematical symbol s [sigma] is used to denote the SELECT operator. The general syntax for selection operation is shown

below.

Sigma<selection condition>(<relation name>).

The <selection condition> is a Boolean expression, consists of attribute names, comparison operators like =,!=,<,<=,>,>= and Boolean operations like AND, OR and NOT.

E.g. 1. Select the employees who are working in department 10, and whose salary is greater than Rs.5000.

s(DNO=0 AND SLARY>5000)(EMPLOYEE)

E.g. 2. Select the employees who are working in department 10 and earning more than 5000 or employees who are working in department 20 and whose earnings are more than 8000 rupees.

s(DNO=10 AND SALARY > 5000) OR (DNO=20 AND SALARY>8000 (EMPLOYEE)

Project operation:

Projection operation is used to select only few columns from a table. the mathematical symbol p<ATTRIBUTE LIST>(<relation>)

Here, <attribute list> is a list of attributes from the relation r hence the

degree (number of columns) of the result is equal to the number of attributes specified in the attribute list.

Eg 1. Select the name and salary of all the employees.

pNAME. SALARY (EMPLOYEE).

This query selected only name and salary attributes from relation EMPLOYEE

Eg. 2. Select names and addresses of all employees working for department 10.

pNAME, ADDRESS (DNO=10(EMPLOYEE)

Set theoretic operations:

These are used to merge the elements of two sets in various ways, including union, intersection and difference. Three of these operations require the table to be union compatible. The two relations are said to require the table to be union compatible. The two relations are said to be union compatible if the following conditions are satisfied.

1. The two relation/tables (say R & S) that have the same number of columns (have the same degree)

2. Each column of the first relation/table ® must be either the same data type as the corresponding column of the second relation/table(s).

Relations R & S

Intersection (∩):

The intersection operation selects the common tuples from the two relations.

The result of the operation R∩S is

Union ( È ):

The result of this operation denoted by RÈS, is a relation that includes all tuples that are either in R or in S or in both. Duplicate tuples will not appear in the output.

Difference ( – ):

The result of the difference consists of all tuples in R but not in S

Cartesian products (X):

The Cartesian product or cross-product is a binary operation that is used to combine two relations. Assuming R & S as relations with n and m attributes respectively, the Cartesian products R x S can be written as,

R (A1, A2……..An) x S (B1, B2…….Bn)

The result of the above set operation is

Q (A1, A2……..An, B1, B2…….Bn)

Total number of columns in Q: degree (Q) = n + m

Total number of tuples in Q: count (Q) = Number of tuples in R* Number of tuples in S

The relation R has 2 columns and 3 tuples. The relation S has 2 columns and 3 tuples. So the Cartesian product has 4 columns (2+2) and 6 tuples (3 x 2).

The Cartesian product operation applied by itself is generally meaningless. It is useful only when followed by selection and projection operations.

Renaming r (rho):

This operation is used to rename the relations or attributes. The symbol

r(rho) is used to denote the rename operator. In some situations, it is better to break down a complex query into two or more simple querys. We must rename the relations that hold the intermediate result relations. It improves the readability and facilitates better understanding.

The syntax is as follows:

Rename <OLD TABLE> to <NEW TABLE>

1. rs(new attribute names)(R) – It renames both the relations and its attributes.

2. rs® – It renames the relation only.

3. r(new attribute names)(R) – It renames only the attributes.

Here S is new relation and R is original relation.

Ex 1: rE(Employee)

This example renames relation employee into relation E.

Ex.2: To retrieve the employee number and name, who are earning more than 5000 Rs.

pebim ebane (sSal > 5000(employee)

We can split into two operation. Esal ¬ s sal>5000(employee) RESULT ¬ p eno,ename(Esal)

Here intermediate result stored in Esal.

The Join Operation

Join (´): The capability of retrieving data from multiple tables using a single SQL statement is one of the most powerful and useful features of RDBMS. It is the availability of join operation. We know that one table may not give all the information about a particular entity.

The join operation, denoted by ´ is used to combine two relations to retrieve useful information. A join operation matches data from two or more tables; based on the values of one or more columns in each table, it allows us to process more than one table at a time.

For e.g.: The employee table gives only the department id’s, if we want to know the department name, then we have to get the information by joining employee table and dept. table.

In join, only combinations of tuples satisfying the join condition appear in the result.

The general form of a Join operation is

R´<join condition>S

For example by joining employee and department relations, we can get the name of the department in which the employee is working (department name exists in department table).

Select emp_no, ename, dept.dname from emp.dept

Where emp.deptno = dept.dept_no and emp_no = &emp_no.

Emp_dept<–employee ´ e.deptno=d.deptnoDEPT Result<-IIemp.enam,dname)emp_dept)

The first operation in the joint operation will combine the tuples of the employee and department relations on the basis of the dept no.to form a relation called emp_dept. Then the PROJECT operation will create a relation RESULT with the attributes eno. Ename, and dname. To perform join between two relations, there should be a common field between them.

Theta Join: A join condition is of the form

<Condition>and<condition>and<condition>

Where each condition is of the form Ai 0 Bj (dept.deptno = emp.dept_no). Ai is an attribute of R and Bj is an attribute of S. Ai and Bj have the same domain (same values) and 0 is one of the comparison operators (=,<,<=,>,>=,!=).

A join operation with such a general join condition is called a "Theta join".

Equi Join: While joining if the comparison operator is = then it is equijoin.

Eg. Select emp_no.ename.dept.dname from emp.dept. Where emp.deptno = dept.dept_no.

Natural Join: It is denoted by symbol. The standard definition of natural join requires that the join attributes have the same name in both relations. In general, natural join is performed by equating all attribute pairs that have the same name in the two relations. The general format is:

Here list l specifies list of attributes from R and list2 specifies a list of attributes from S.

Department

DNumber	DName
1	Admin
2	Research
3	Accounts

Project

Pnumber	Pname	DNum
10	Library Management	2
20	ERP	1
30	Hospital Management	3
40	Wireless Network	2

Project Dept.

Pnumber	PName	DNum	Dname
10	Library Management	2	Research
20	ERP	1	Admin
30	Hospital Management	3	Accounts
40	Wireless Network	2	research

Here, the joining is done over the attribute DNumber of Department relation and DNum of Project relation. In fact, DNum of Project is a foreign key which references DNumber of Department. Generally, in a natural join, the joining attribute is implicitly considered. Suppose the two relations have no attribute(s) in common, R S is simply the cross product of these two relations. Joining can be done between any set of attributes and need not be always with respect to the primary key and foreign key combinations.

The expected size of the join result divided by maximum size i.e. nR nS leads to a relation called join selectively.

Outer join:

It returns both matching and non matching rows. It differs from the inner join, in that the rows in one table having no matching rows in the other table will also appear in the results table, with nulls in the other attribute position, instead of being ignored as in the case with the inner join. It outputs rows even if they do not satisfy the join condition; the outer join operator is used with the table having n matching rows

In the above example even though there is no matching row with B name, all workers are listed along with age and skill. If there is no match, simply get an empty skill column. The outer join can be used when we want to keep all the tuples in R or in S; those in both relations, whether or not they have matching tuples in the other relation.

Left outer join: It is denoted by . The left outer join operation keeps every tuple in the first or left relation R in relation R S. If no matching tuple is found in S in the join, result is filled with null values.

Right outer join: It is denoted by , and keeps every tuple in the second or right relation S in the result of R

Full outer join: It is denoted by and keeps all tuples in both the left and right relations and when no matching tuples are found, filled with null values as needed.

Division

A division operation (denoted by ¸) is useful for a special kind of query;

occasionally it may be used to solve certain kind of problems.

Consider the relations P (P) and Q (Q) as shown in the figure. The result of dividing P by Q is the relation R and it has two tuples. For each tuple in R, its product with the tuples of Q must be in P. In our example (a1, b1) must both be tuples in P: the same is true for (a5, b1) and (a5, b2)

Examples of the division operations R = P + Q:

Notice here that 123,453 appear in SSN_PNOS in combination with all two tuples in SMITH_PNOS; that is why they appear in the resulting relation SSNS.

Grouping and Aggregate functions:

It is often required in data base applications to find an aggregate value over some column (to find summation). For example, we may wish to find the sum of salary drawn by all the employees, or to find the total number of employees working in a department etc. for which aggregate function can be used.

It is also useful in grouping the tuples in a relation by the value of some of their attributes, and then applying an aggregate function independently to each group.

Commonly used aggregate functions are sum, average, count, maximum and minimum. For example: Group employee tuples by dno, so that each group includes the tuples for employees working in the same department; after that we can apply aggregate function to find the average salary of employees within the department.

Here grouping attributes is a list of attributes of the relation R, and function list is a list of aggregate functions like SUM.AVERAGE, MIN. MAX, COUNT, and attributes are column names.

The steps involved in the evaluation of this are:

1. Partition the relation into groups.

2. Apply aggregate function to each group, output group and aggregate values, one tuple per group.

E.g.: To retrieve each department number, the number of employees working in the department and their average salary.

Resulting relation has the grouping attributes in addition to one attribute for each element in the function list.

Hope you will like Series of Database Management Systems series !

If you have not yet subscribe this Blog , Please subscribe it from “follow me” tab !

So that you will be updated @ real time and all updated knowledge in your mail daily for free without any RSS subscription OR news reading !!

Happy Learning and Sharing !!

For More information related to BI World visit our all Mentalist networks Blog

SQL Server Mentalist … SQL Learning Blog

Business Intelligence Mentalist … Business Intelligence World

Microsoft Mentalist … MVC,ASP.NET, WCF & LinQ

MSBI Mentalist … MS BI and SQL Server

NMUG Bloggers …Navi Mumbai User Group Blog

Architectural Shack … Architectural implementation and design patterns

DBA Mentalist …Advance SQL Server Blog

MVC Mentalist … MVC Learning Blog

Link Mentalist … Daily Best link @ your email

Infographics Mentalist … Image worth explaining thousand Words

Hadoop Mentalist … Blog on Big Data

BI Tools Analysis … BI Tools

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS | About Me |