Database Management Systems/ B24IS41

Module-2
RELATIONAL MODEL
Q1) Define the following terms as they apply to the relational model of data: domain,
attribute, n-tuple, relation schema, relation state, degree of a relation, relational database
schema, and relational database state.

Domains: A domain D is set of atomic values, atomic means each value in the domain is individual.
A common method for specifying a domain is to specify a data type from which the data values
forming the domain are drawn and also we should specify a name for the domain. Some examples
of domains are.
* Branch_names: The set of names of bank branches of character type.
* Account_Number: The set of 3 digit account numbers.
* Customer_Name: The set of names of persons having account in the bank.
* Balance: The set of values of amount in the account.
These are the logical definitions of domains.
The relational model represents the database as a collection of relations (tables) each of which
is assigned a unique name. Informally each relation resembles a table, where the table consists of
rows and columns. Each row in the table represents a collection of related data values. These values
can be interpreted as facts describing the real world entity. In relational model terminology, a row
is called a tuple and a column header is called an attribute and the table is called a relation. The
data type describing the types of values that can appear in each column is represented by a domain
of possible values.
Relation Schema: A relation schema R, denoted by R(A1,A2,.An), is made up of a relation name R
and a list of attributes A1, A2,….An. Each attribute Ai is the name of role played by some domain D
in the relation schema R.D is called the domain of Ai and is denoted by dom (Ai). A relation schema
is used to describe a relation; R is called name of this relation.

Degree of a Relation: The degree (or arity) of a relation is the number of attributes present in the
relation schema. An example for relation schema for a relation of degree 7 is STUDENT (Name,
Ssn, HomePhone, Address, OfficePhone, Age, GPA), here STUDENT is the name of the relation
which has 7 attributes. The below figure shows an example of STUDENT relation.

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

A relation (or relation state) r of the relation schema R (A1, A2, ... , An), also denoted by r(R), is
a set of n-tuples. r = {t1, t2, ..., tm}. Each n-tuple t is an ordered list of n values t =<v1, v2,
...,vn>, where each value vi, 1 ≤ i≤ n, is an element of dom (Ai) or is a special NULL value. The
ith value in tuple t, which corresponds to the attribute Ai, is referred to as t[Ai] or [Link].

A relational database schema S is a set of relation schemas S = {R1, R2, ... , Rm} and a set of integrity
constraints IC. A relational database state DB of S is a set of relation states DB= {r1, r2, ... , rm} such
that each ri is a state of Ri and such that the ri relation states satisfy the integrity constraints specified in IC.
Figure below shows a relational database schema.
COMPANY={EMPLOYEE,DEPARTMENT,DEPT_LOCATIONS, PROJECT,WORKS_ON, DEPENDENT}.
The underlined attributes represent primary keys.
Relational database schema S is a set of relation schemas S={R1, R2, , Rm}and a set of integrity
constraints IC.
EMPLOYEE
Name SSN Bdate Address Sex Salary Dno
DEPARTMENT
Dname Dnumber
PROJECT
Pname Pnumber Dnum
WORKS_ON
ESSN PNo Hours

Figure: The COMPANY relational database schema

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

Q2) Explain the Characteristics of Relation.

The characteristics of a relation make it different from a file or a table.

1) Ordering of Tuples in a Relation. A relation is defined as a set of tuples. Mathematically,
elements of a set have no order among them; hence, tuples in a relation do not have any particular
order.
2) Ordering of values within a Tuple and an Alternative Definition of a Relation. In a relation,
an n-tuple is an ordered list of n values, so the ordering of values in a tuple is important.
An alternative definition of a relation can be given, making the ordering of values in a tuple
unnecessary. In this definition, a relation schema R={ A1,A2,. . . . . An} is a set of attributes, and a
relation state r(R) is a finite set of mapping r = {t1, t2, tm }, where each tuple ti is a mapping from
R to D is the union of the attribute domains; that is, D=dom(A1) Udom(A2) U Udom(An).
In this definition, t[Ai] must be in dom(Ai) for 1<=i<=n for each mapping t in r. Each mapping ti is
called a tuple.
According to this definition of a tuple as a mapping, a tuple can be considered as a set of
(<attribute>,<value>) pairs, where each pair gives the value of the mapping from an attribute A i to a
value vi from dom (Ai).
Example:
t = < (Name, Dick Davidson), (Ssn, 422-11-2320), (Home_phone, NULL), (Address, 3452 Elgin
Road), (Office_Phone, 749-1253), (Age, 25), (Gps, 3.53) >
3) Values and NULLs in the Tuples. Each value in a tuple is an atomic value; that is, it is not
divisible into components within the framework of the basic relational model.
An important concept is that of NULL values, which are used to represent the values of attributes that
may be unknown or may not apply to a tuple. A special value, called NULL, is used in these cases.
Example: SUDENT tuples have NULL for their officephones.
4) Interpretation (Meaning) of a Relation. The relation schema can be interpreted as a declaration
or a type of assertion. For example, the scheme of the Student relation asserts that, in general, a
student entity has a Name, Ssn, Home_phone, Address, Office_phone,

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

Age, and Gpa. Each tuple in the relation can then be interpreted as a fact or a particular instance of
the assertion. For example, the tuple in the relation asserts the fact that there is a STUDENT whose
Name is Dick Davidson, Ssn is 422-11-2320, Age is 25, and so on.
Q3) List and Explain the Relational Model Notations.
a. A relation schema R of degree n is denoted by R (A1, A2, .................. , An).
b. The letters Q, R, S denotes relation names.
c. The letters q, r, s denote relation states.
d. The letters t, u, v denote tuples.
e. In general, the name of a relation schema such as STUDENT also indicate the current set of tuples
in that relation – the current relation state – whereas STUDENT (Name, Ssn, …) refers only to
the relation schema.
f. An attribute A can be qualified with the relation name R to which it belongs by using the dot
notation R.A – for example, [Link] or [Link]. This is because the same name
may be used for two attributes in different relations. However, all attribute names in a particular
relation must be distinct.
g. An n-tuple t in a relation r( R) is denoted by t = < v1,v2,. . . . . vn>, where vi is the value
corresponding to attribute Ai. The following notation refers to component values of tuples.
i. Both t[Ai] and [Link] (and sometimes t[i]) refer to the value vi int for attribute Ai.
ii. Both t[Au, Aw,…,Az] and t.( Au, Aw,…,Az), where Au, Aw,…,Azis a list of attributes from R, refer
to the subtuple of value <vu, vw, …, vz> from t corresponding to the attributes specified in the list.

Q4) Explain the Relational Model Constraints.

Constraints on databases can generally be divided into three main categories:

1) Constraints that are inherent in the data model are called as inherent model-based or implicit
constraints.
2) Constraints that can be directly expressed in schemas of the data model, typically by specifying
the min DDL and are called as schema-based or explicit constraints.
3) Constraints that cannot be directly expressed in schemas of the data model, and hence must be
expressed and enforced by the application program and are called as application-based or
semantic constraints or business rules.

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

Domain Constraints: Domain constraints specify that the value of each attribute is A must be an
atomic value form the domain dom (A) for that attribute.
The data types associated with domains typically include standard numeric data types for integers
and real numbers. Characters, fixed length strings and variable length strings etc.,
Key Constraints: A relation is defined as set of tuples, and all elements of set are distinct. Hence
all the tuples in a relation must also be distinct i,e no two tuples have the same combination of values
for all their attributes.

Q5) Define the following: Superkey, Minimal Superkey, Candidate Key, Primary Key

Superkey: The subset of attributes of a relation schema R with the property that no two tuples in any
relation instance r of R will have the same combination of values for these attributes. Such setof
attributes is called superkey of the relation schema R.
A set of attributes SK of R such that no two tuples in any valid relation in stance r(R) will have the
same value for SK. That is, for any distinct tuples t1 and t2 in r(R), t1[SK]≠t2[SK]. Any such set of
attributes SK is called a superkey of the relation schema R. A superkey SK specifies a uniqueness
constraint that no two distinct tuples in any state r of R can have the same value for SK.

A key k of a relation schema R is a superkey of R with the additional property that removing any
attribute A from K leaves a set of attribute Kꞌ that is not a super key of R any more. Hence, a key
satisfies two constraints.

1. Two distinct tuples in any state of the relation cannot have identical values for (all) the
attributes in the key.
2. It is a minimal superkey – that is, a superkey from which we cannot remove any attributes
and still have the uniqueness constraint in condition 1 hold.
Minimal superkey: A superkey from which we cannot remove any attributes and still have the
uniqueness constraint hold.
For example, consider the relation with
STUDENT (Name, SSN, HomePhone, Address, Age, GPA), here the attribute set {SSN} is key
of STUDENT because no two student tuples can have same value for SSN. Any set of attributes
that include SSN – for example, {SSN, Name, Age } is a superkey however the superkey {SSN,
Dr. Salma Jabeen, ISE, DBIT
 Database Management Systems/ B24IS41

Name, Age } is not a keyof STUDENT because removing Name or Age or both from the set still
leaves us with superkey. Hence a minimal superkey is a key of the relation. Here {SSN} is a
minimal superkey or key of relation STUDENT.

Candidate Key: In general a relation schema may have more than one key, i,e relation may have
more one attribute or set of attribute which can distinguish each tuple. In such cases each key is
called candidate key.

Primary Key: The candidate key whose vales are used to identify tuples in the relation is called
Primary Key. In the relation schema the attributes that form the primary key of a relation are
underlined.

Q6) Define Entity Integrity, Referential Integrity, Foreign Keys and Other Constraints.
Entity integrity constraints: The entity integrity constraints state that no primary key value can
be null. This is because the primary key value is used to identify individual tuples in the relation.
Note: Key constraints and entity integrity constraints are specified on individual relations.

Referential integrity constraint: is specified between two relations and is used to maintain
consistency among tuples of two relations.
Referential integrity constraints states that a tuple in one relation that refers to another relation
must refer to an existing tuple in that relation. For example consider,

Here the attribute DNO of EMPLOYEE gives the department number for which employee works;
hence DNO value in every EMPLOYEE tuple must match the Dnumber value of some tuples in the
DEPARTMENT relation.

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

Foreign Key: A set of attributes FK in relation schema R1 is a foreign key of R1 if it satisfies the
following two rules.
i. The attributes in FK have the same domain as primary key attributes PK of another relation
schema R2; i,e the attributes FK are said to reference or refer to the relation R2. In other words
a value of FK in tuple t1of R1 = the value of PK for some tuples t2 in R2 (t1[FK]= t2[PK], here
the tuple t1 references or refers to the tuple t2)
ii. A value of FK in a tuple t1 of R1 either occurs as a value of PK for some tuples t2 in R2 or is
null.
In the above example DNO is the foreign key which refers to Dnumber in DEPARTMENT relation.

Other Types of Constraints

Constraints that cannot be directly expressed in schemas of the data model, and hence must be
expressed and enforced by the application program and are called as application-based or semantic
constraints or business rules. Examples of such constraints are the salary of an employee should
not exceed the salary of the empoyee‘s supervisor and the maximum number of hours an employee
can work on all projects per week is 56. Such constraints can be specified and enforced within the
application programs that update the database, or by using a general-purpose constraint specification
language. Mechanisms called triggers and assertions can be used. In SQL- 99, a CREATE
ASSERTION and triggers statement is used for this purpose.

Q7) Discuss the various reasons that lead to the occurrence of NULL values in relations.
OR
Write a note on NULL and three valued logic.
SQL has various rules for dealing with NULL values. NULL is used to represent a missing value, but
that it usually has one of three different interpretations. Consider the following examples to illustrate
each of the meanings of NULL.
1. Unknown value. Aperson‘s date of birth is not known, so it is represented by NULL in the
database.
2. Unavailable or withheld value. A person has a home phone but does not want it to be listed,
so it is withheld and represented as NULL in the database.
3. Not applicable attribute. An attribute LastCollegeDegree would be NULL for a person who
has no college degrees because it does not apply to that person.

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

It is often not possible to determine which of the meanings is intended; for example, a NULL
for the home phone of a person can have any of the three meanings. Hence, SQL does not
distinguish between the different meanings of NULL.
SQL uses a three-valued logic with values TRUE, FALSE and UNKNOWN instead of the
standard two-valued (Boolean) logic with values TRUE or FALSE.
SQL allows queries that check whether an attribute value is NULL. Rather than using = or <>
to compare an attribute value to NULL, SQL uses the comparison operators IS or IS NOT. This is
because SQL considers each NULL value as being distinct from every other NULL value, so equality
comparison is not appropriate.
Example: Retrieve the names of all employees who do not have supervisors.
SELECT Fname FROM EMPLOYEE
WHERE Super_ssn IS NULL;

Q8) Discuss the different types of update operations on relational database. Give example for
each.
The operations of relational model can be categorized in Retrieval and Updates. There are
three basic modification or update operations on relations.
[Link] 2. Delete [Link]
The Insert Operation: it is used to insert new tuples in to a relation. The insert operation provides
a list of attribute values for a new tuple t that is to be inserted into a relation R. Insert canviolate
any of the four constraints, Domain constraints, key constraint, referential integrity, entity integrity.
In such cases two options are available. The first option is to reject the insertion by specifying why
the insertion was rejected. The second option is to attempt to correct the reason for rejecting the
insertion.
Examples:
.Operation:
Insert<‘Madhu‖,‘G‘,‘Kumar‘,NULL,‘10-9-1998‘,‗Bangalore‘,25000,NULL,4>into EMPLOYEE.
Result: This insertion violates the entity integrity constraint (NULL for the primary key Ssn), So it
is rejected.
.Operation:
Insert<‘Anil‘,‘S‘,‘Kumar‘,1234,‘12-6-1998‘,‗Bangalore‘,20000,NULL,4>intoEMPLOYEE.

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

Result: This insertion violates the key constraint because another tuple with the same Ssn value
already exists in the EMPLOYEE relation, and so it is rejected.
.Operation:
Insert<‘Kiran‘,‘G‘,‘Shetty‘,2345,‘13-7-1998‘,‗Mysore‘,22000,NULL,5>into EMPLOYEE.
Result: This insertion violates the referential integrity constraint specified on Dno in EMPLOYEE
because no corresponding tuple exists in DEPARTMENT with Dnumber=5.
.Operation:
Insert<‘Santosh‘,‘J‘,‘Kumar‘,3456,‘6-10-1998‘,‗Bangalore‘,25000,NULL,4>into EMPLOYEE.
Result: This insertion satisfies all constraints, so it is acceptable.
The Delete Operation: It is used to delete tuples from relation. The delete operation can violate
only referential integrity, if the tuple being deleted is referenced by the foreign keys from other
tuples in the database. If the deletion operation causes violation, there are three options. The first
option is to reject the deletion. The second option is to attempt to cascade the deletion by deleting
tuples that references the tuples that is being deleted. The third option is to modify the referencing
attribute values that cause the violation.
.Operation:
Delete the WORKS_ON tuple with Essn=1234 and Pno=10.

Result: This deletion is acceptable and deletes exactly one tupel.

.Operation:
Delete the EMPLOYEE tuple with Ssn=1234.
Result: This deletion is not acceptable, because there are tuples in WORKS_ON that refer to this
tuple. Hence, if the tuple in EMPLOYEE is deleted, referential integrity violations will result.

.Operation:
Delete the EMPLOYEE tuple with Ssn=2345.
Result: This deletion will result in even worse referential integrity violations, because the tuple
involved in referenced by tuples from the EMPLOYEE, DEPARTMENT, WORKS_ON, and
DEPENDENT relations.

The Modify Operation: The Update (or Modify) operation is used to change the values of one or

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

more attributes in a tuple (or tuples) of some relation R. It is necessary to specify a condition on the
attributes of relation R to select the tuple (tuples) to be modified.
.Operation:
Update the salary of the EMPLOYEE tuple with Ssn=1234 to 29000.
Result: Acceptable.
.Operation:
Update the Dno of the EMPLOYEE tuple with Ssn=1234 to 1.
Result: Acceptable.
.Operation:
Update the Dno of the EMPLOYEE tuple with Ssn=1234 to 7.
Result: Unacceptable, because it violates referential integrity.
.Operation:
Update the Ssn of the EMPLOYEE tuple with Ssn=1234 to 2345.
Result: Unacceptable, because it violates primary key constraint by repeating a value that already
exists as a primary key in another tuple; it violates referential integrity constraints because there are
other relations that refer to the existing value of Ssn.
Modifying an attribute that is neither a primary key nor foreign key usually causes no problem; the
DBMS need only check to confirm the new value is of the correct data type and domain.

Q9)Write a note on SELECT and PROJECT relational algebra operations.

The SELECT Operation

The SELECT operation is used to select a subset of the tuples in a relation that satisfy selection
condition. We can use lower case Greek letter sigma (σ) to denote selection. The predicate

(condition) appears as subscript to σ. The argument relation is given in parentheses following the

σ. In general, the syntax for selection operation is

σ<selection condition> (<relation name> )here the <selection condition> is made up of number of clauses
of the form
<attribute name><relational operator><constant value> or
<attribute name><relational operator><attribute name>

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

The relational operator may be any one of the operators {=, <, >, <=, >=, !=}
Clauses can be connected by the Boolean Operators AND, OR, NOT.
For example, select tuples for all employees who either works in department 4 and take salary
more than 25,000 or works in department 5 and take over salary more than 30,000
σ(DNO=4 AND Salary>25000) OR (DNO=5 AND Salary>30000) (EMPLOYEE)

The PROJECT Operation

The Project operation is a unary operation that returns its argument relation, with certain columns
left out. i.e it selects certain columns and discards the other columns. Projection is denoted by the
Greek letter Pi (π). The general form of the PROJECT Operation is

π<attribute list>(<relation name>)

For exampleπName, Salary(EMPLOYEE) selects only Name and Salary column from the
relation EMPLOYEE.
Sequence of Operations and the RENAME Operations
To apply several relational algebra operations, we can either write the operation as single relational
algebra expression by nesting the operation or we can apply one operation at a time and create
intermediate result relation. We must name the relations that hold the intermediate results. For
example, to retrieve Name, Age, and Salary of all employees who works in departmentnumber 5 we
must apply SELECT and a PROJECT operation.

πName, Age, Salary (σ DNO = 5 (EMPLOYEE)). Or we can explicitly show the sequence of operations,
giving a name to each intermediate relation as

DEP5_EMPS σ DNO=5(EMPLOYEE)

RESULT πName, Age, Salary(DEP5_EMPS)

It is simpler to break down the complex sequence of operations by specifying intermediate result
relations than writing a single relational algebra expression. This technique is also useful to rename
the attributes in the intermediate and result relations. To rename the attributes in a relation that
results from applying a relation algebra operation, we simply list the new attribute names in
parentheses. For example

TEMP σ DNO=5 (EMPLOYEE)

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

R(EMPNAME, EMPAGE, EMPSALARY) π Name, Age, Salary(TEMP)

Q10) Explain UNION, INTERSECTION, MINUS (DIFFERENCE), CARTESIAN
PRODUCT (CROSS PRODUCT) Operations.
The set operations are the standard mathematical operations on sets, they apply to relational model
because a relation is defined to be set of tuples and can be used to process the tuples in two relations
as sets.
Union Compatible : Two relations R(A1, A2, . . . . .An ) and S(B1, B2, . . . , Bn) are said to be
union compatible if they have the same degree n and if dom(Ai) = dom(Bi) for 1 <= i<= n. This
means that the two relations have the same number of attributes and that each pair of corresponding
attributes have the same domain.
We can define the three operations UNION, INTERSECTION, and SET DIFFERENCE (also called
MINUS).
UNION: The result of this operation, denoted by R US, is a relation that includes all tuples that are
either in R or in S or in both R and S. Duplicate tuples is eliminated.

Note: RUS=SUR
For example, consider.
STUDENT
FN LN
Susan Yao
Ramesh Shah
Johnny Kohler
Barbara Jones
Amy Ford
Jimmy Wang
Ernest Gilbert
INSTRUCTOR
FNAME LNAME
John Smith
Ricardo Browne
Susan Yao
Francis Johnson
Ramesh Shah

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41
Here the relations STUDENT and INSTRUCTOR are union compatible and their tuple represent
the names of students and instructors respectively.
The result of union operation shows the names of all students and instructors.
STUDENT U INSTRUCTOR
FN LN
Susan Yao
Ramesh Shah
Johnny Kohler
Barbara Jones
Amy Ford
Jimmy Wang
Ernest Gilbert
John Smith
Ricardo Browne
Francis Johnson

INTERSECTION: The result of this operation, denoted byRNS is relation that includes all tuples
that are in common in both R and S.
Note: R ∩ S = S ∩ R
The result of STUDENT ∩ INSTRUCTOR is
FN LN
Susan Yao
Ramesh Shah

Both UNION and INTERSECTION can be treated as an-ary operations applicable to any number
of relations because both are associative operations; that is
RU(SUT) = (RUS)UT and (R N S) N T = R N (S N T)
DEFFERENCE: The result of this operation, denoted by R–S, is a relation that includes all tuples
that are in R but not in S.
Note: R– S≠S–R
The result of INSTRUCTOR–STUDENT is
FN LN
John Smith
Ricardo Browne
Francis Johnson
The INTERSECTION can be expressed in terms of union and set difference as follows:

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41
R∩S=R U S-(R-S)-(S-R)
The CARTESIAN PRODUCT (CROSS PRODUCT) Operation
It is binary set operation denoted by x. This is also known as CROSS PRODUCT or CROSS JOIN.
This operation is used to combine tuples from two relations so that related tuples can be identified.
In general, the result of R(A1,A2, ....,An) x S(B1,B2,Bm) is a relation Q with n + m attributes
Q(A1,A2,...An, B1,B2,Bm) in that order. The resulting relation Q has one tuple for each
combination of tuples – one from R and one from S. Hence if R as nR tuples and S has nS tuples, R
x S will have nR * nS tuples.
For example, consider
Relation ‘R’
Name Place
Ranga Tumkur
Soma Bengaluru

Relation
College Department
SIT CSE
RVCE TCE

The result of RxS is the relation Q as shown below

Realation Q
Name Place College Department
Ranga Tumkur SIT CSE
Ranga Tumkur RVCE TCE
Soma Bengaluru SIT CSE
Soma Bengaluru RVCE TCE
Q11) Explain the JOIN and DIVISION Binary relational operations with an [Link] JOIN
Operation

The JOIN operation denoted by ⋈ is used to combine related tuples from two relations into single

tuples. This operation is more important for any relational database with a more than a single relation.
Because allows to process relationships among relations. The general form JOIN operation on two
relations R(A1,A2, An) and S(B1,B2,…Bm) is

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

R⋈<Join condition>S
The result of join is a relation Q with n+m attributes Q(A1,A2,...An, B1,B2,…Bm) in that order; Q
has one tuple for each combination of tuples – one from R and one from S, whenever the combination
satisfies the join condition.
The join condition is of the form Ai$Bi, where Ai is an attribute of relation R and Bi is an attribute of
relation S and $ is one of the comparison operator {=, <, >, >=, <=, !=}.
Example:
Consider the relations EMPLOYEE(Ename, address, Dnum) and
DEPARTMENT(DeptNum, Dname, Location, Head_name).

DEPT_JOIN ← (EMPLOYEE⋈Dnum=DeptNumANDHead_name=EnameDEPARTMENT

Difference between JOIN and CARTESIAN PRODUCT: In join only the combinations of tuples
satisfying join condition appears in the result; whereas in CARTESIAN PRODUCT all combinations
of tuples are included in the result.

EQUIJOIN: The JOIN, which involves join condition with equality comparison only i,e the only
comparison operator used is = , is called an EQUIJOIN.
Example: Consider the relations
EMPLOYEE(Ename, Address, Dnum) and
DEPARTMENT (DeptNum, Dname, Location).

Then DEPT_JOIN (EMPLOYEE Dnum=DeptNumDEPARTMENT)

Then the result of this EQUIJOIN is the relation DEPT_JOIN (Ename, Address, Dnum, Deptnum,
Dname, Location)
This relation will have all the combination tuples which are having Dnum=Deptnum.
NATURAL JOIN: The result of Equijoin will have one or more pair of attributes that have identical
values in every tuple. In NATURAL JOIN this superfluous attributes are removed. Hence NATURAL
JOIN is basically an EQUIJOIN followed by removal of the superfluous (redundant) attributes.
NATURAL JOIN is denoted by *.The NATURAL JOIN requires the two join attributes to have the
same name in both the relations.
The general definition for NATURAL JOIN is Q R*(<list1>,<list2>)S
Example: DEPT_NAT EMPLOYEE * DEPARTMENT.

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

OUTER JOIN: It is the extension of JOIN operation. The OUTER JOIN operation is used to take the UNION
of tuples from two relations that are not UNION compatible. In normal, join operations (NATURAL or EQUI
JOIN ), only the matching tuples appear in the result, i.e the tuples without related tuples and the tuples with
null in the join attributes are eliminated. In OUTER JOIN all the tuples in relation R and in relation S or both
in the result, whether or not they have matching tuples in the other relation.
There are Three types of OUTER JOIN.
1. LEFTOUTER JOIN: The LEFTOUTER JOIN operation keeps every tuple in first or
left relation R in the result of RS; if no matching tuple is found in S, then attributes of S in
the result are filled or padded with null values. The LEFT OUTER JOIN is denoted by the

symbol

2. RIGHTOUTERJOIN: The RIGHT OUTER JOIN is denoted by . The RIGHT

OUTER JOIN keeps every tuple in second or right relation S in the result of RS.

3. FULL OUTER JOIN: This operation is denoted by . FULL OUTER JOIN keeps
all the tuples in both left and right relation when no matching tuples are found, padding them
with null values as needed.

The DIVISION Operation

The DIVISION operation, denoted by , is useful for a special kind of query that sometimes
occurs in database applications.
The Division operator is used to find records in one relation that are associated with all
records in another relation. It is commonly used when we want to identify entities that
satisfy certain conditions across multiple related data sets.
The Division operator (R ÷ S) can be applied if:
• The attributes of S are a proper subset of the attributes of R.
• The result will include all attributes of R except those that are in S.
• It returns the tuples from R that are associated with every tuple in S.

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41
Example:
R S R ÷ S
A B B A

1 X X 1
1 Y Y 2
2 X
2 Y
3 Z

Q12) Write a note on Aggregate functions.

Some common database requests involving Aggregate functions (SUM, AVERAGE, MINIMUM,
MAXIMUM), Recursive Closer Operations etc., cannot be performed with standard relational
algebra. But most query languages for RDBMS include these capabilities. These additional
operations are.

Aggregate Functions: The Aggregate functions that are applied on numeric values include SUM,
AVERAGE, MAXIMUM, MINIMUM and COUNT used for counting number of tuples.
Example: For query involving aggregate functions is retrieving the average or total salary of all

employees. We can define a FUNCTION operation using the symbol (pronounced as―Script
F) to specify these types of requests as

<grouping attributes> <function list>(<relation name>)

where<grouping list>is a list of attributes of the relation specified in <relation name>, and
<function list>is a list of (<functions><attribute> ) pairs. In each pair <function > is one of the
allowed functions such as SUM, AVERAGE, MAXIMUM, MINIMUM, COUNT, and <attribute>
is an attribute in the relation <relation name>.
Example:Consider a relation EMPLOYEE (Ename, Enum, Dnum, Salary)

Dnum AVERAGESalary(EMPLOYEE).

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

SQL
Q1)With respect to SQL explain the following with an example.
a)CREATETABLEb)ALTER TABLEc) DROPd)CREATESCHEMA
e)CREATEDOMAINcommand
a) CREATETABLECommand

CREATETABLE: The CREATE TABLE command is used to create a new relation by giving it
a name and specifying attributes and constraints. The general syntax is.

CREATETABLE<table name>(attr1 data type constraint , attr2 data type constraint , ...
attrn data type constraint);
attr1, attr2, attrn are the names of the attributes and then their data types are specified andpossibly
some constraints like the Key, entity integrity and referential integrity constraints are then
specified. For example

CREATETABLEEMPLOYEE
(NAME VARCHAR (15) NOTNULL, SSN CHAR (9) NOTNULL,BDATEDATE,
ADDRESS VARCHAR (30), SEX CHAR, SALARY DECIMAL (10,2),
DNOINTNOTNULL,PRIMARYKEY(SSN),
FOREIGNKEY(DNO)REFERENCESDEPARTMENT(DNUMBER));

b) ALTER TABLE Command

ALTER TABLE command is used to change the definition of the base table. The possible alter
table actions include
i. Adding or dropping a column(attribute),
ii. Changing a column definition
iii. Adding or dropping the table constraints.
Adding new attribute (column)
We can add new attribute to the table by using the ADD clause with alter command. The general
syntax to add new attribute (column) is

ALTERTABLE<table name>ADD<attribute name><datatype>;

Here the<attribute name>is new column to be added

<table name>is the name of the table to which the specified new column to be added.

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

Note:Whenthenewattributeisadded,theattributewillhaveNULLsinallthetuplesofthe relation, hence the

NOT NULL constraint is not allowed.

Example: ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12);

Dropping an attribute(column):
WecandropanattributefromthetablebyusingDROPclausealongwithALTERTABLE command, the
general syntax is.

ALTERTABLE<table name>DROP<attribute name>[CASCADE/RESTRICT];

Herethe<attributename>iscolumnnametoberemovedfromtherelation(table).
If CASCADE option is used, then all constraints and views that reference the column are dropped
automatically from the database, along with the column.
IfRESTRICToptionisused,thenspecifiedattribute(column)isdroppedonlyifnoviewsor constraints
references the column.

Example: ALTER TABLE EMPLOYEE DROP ADDRESS CASCADE;

Dropping DEFAULT clause:

WecandropanexistingdefaultclausebyusingtheALTERandDROPDEFAULTclausewith ALTER
TABLE command, the general syntax is

ALTERTABLE<tablename>ALTER<attributename>DROP DEFAULT;

Example:ALTERTABLEDEPARTMENTALTERLOCATIONDROP DEFAULT;
Settingnew default clause:
We can set new default clause using the ALTER and SET DEFAULT clause with ALTER TABLE
command. The general syntax is

ALTERTABLE<tablename>ALTER<attributename>SETDEFAULTvalue;

Herethe<attributename>isthenameofthecolumntowhich thespecifiednewvalueistobestored.
Example:ALTERTABLEEMPLOYEEALTERJOBSETDEFAULT’DAILYWAGE‘;

Droppingconstraint:

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41
Theconstraintwhichhasgiven anamecan bedropped byusingDROPCONSTRAINTclause.

Generalsyntaxis

ALTER TABLE<table name>DROP CONSTRAINT<constraint name>CASCADE

Example:ALTERTABLEEMPLOYEEDROPCONSTRAINTEMPFKCASCADE;

a) DROPCommand

Thiscommandisusedtodeletethetable,ifit isno [Link] DROP

TABLE <table name>[RESTRICT or CASCADE]

Here if the RESTRICT option is chosen the table is dropped only if it is not referenced in any
constraints (such as by foreign key definitions in another relation).

With CASCADE option, all such constraints and views that reference the table are dropped
automatically, along with the table itself.

Note: Schema Evolution (or Schema change) commandsin SQL: Drop, Alter [Link]
commands are used to alter a schema by adding or dropping tables, attributes, constraints and
other schema elements.

b) CREATE SCHEMA Command

The concept of an SQL schema is incorporated in SQL2 in order to group together tables and other
constructs that belong to the same database application. An SQL schema is identified by a
schemaname,and includesan authorizationidentifier toindicatetheuseroraccountwhoowns the
schema, as well as descriptors for each element in the schema. Schema elements include tables,
constraints, views, domains, and other constructs (such as authorization grants) that describe the
schema. A schema is created via the CREATE SCHEMA statement, which can include all the
schema elements‘ definitions. Alternatively, the schema can be assigned a name and authorization
identifier, and the elements can be defined later.
For example, the following statement creates a schema called COMPANY, owned by the user
with authorization identifier ‘Jsmith ‘. Note that each statement in SQL ends with a semicolon.
CREATESCHEMACOMPANYAUTHORIZATION‘Jsmith‘;

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41
TheSQLschemainwhichtherelationsaredeclaredisimplicitlyspecifiedintheenvironmentin

which the CREATE TABLE statements are executed. Alternatively, we can explicitly attach the
schema name to the relation name, separated bya period. For example, by writing
[Link]...

a) CREATEDOMAIN Command

Itispossibletospecifythedatatypeofeachattributedirectly,like CREATE
TABLE EMPLOYEE (Ssn CHAR (9) ….);
Or alternatively, a domain can be declared, and the domain name used with the attribute
specification. This makes it easier to change the data type for a domain that is used by numerous
attributes in a schema, and improves schema readability.
Forexample,wecancreateadomain SSN_TYPEbythefollowingstatement:

CREATEDOMAINSSN_TYPE ASCHAR(9);

We canuse SSN_TYPE in place of CHAR(9) asused in the above example, for the attributesSsn
and Super_ssn of EMPLOYEE, Mgr_ssn of DEPARTMENT, Essn of WORKS_ON, and Essn of
[Link] can alsohave an optionaldefault specification via a DEFAULT clause.

Q2)Explainthedifferent datatypesinSQL?
The data types available for attributes include numeric, character-string, bit-string, Boolean, date
and time, timestamp.

Numeric: Numeric data types include integer numbers of various sizes (INTEGER or INT, and
SMALLINT) and floating-point (real) numbers of various precision (FLOAT or REAL, and
DOUBLE PRECISION). Formatted numbers can be declared by using DECIMAL(i, j)—or
DEC(i, j) or NUMERIC(i, j)—where i, the precision, is the total number of decimal digits and j,
the scale, is the number of digits after the decimal point. The default for scale is zero, and the
default for precision is implementation-defined.

Character-string: Character-string data types are either fixed length-CHAR(n) or

CHARACTER(n), where n is the number of characters or varying length-VARCHAR(n) or
CHAR VARYING(n) or CHARACTER VARYING(n), where n is the maximum number of

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41
[Link] literalstringvalue,itisplacedbetweensingle quotation marks,and

it is case sensitive. For fixed length strings, a shorter string is padded with blank characters to the
right. For example, if the value ‘Smith‘ is for an attribute of type CHAR(10), it is padded withfive
blank characters to become ‘Smith‘if needed. Padded blanks are generally ignored whenstrings are
compared. For comparison purposes, strings are considered ordered in alphabetic order; if a string
str1appears before another string str2 in alphabetic order, then str1isconsideredto be less than str2.
There is also a concatenation operator denoted by|| (double vertical bar) that can concatenate two
strings in SQL. For example, ‘abc‘||’XYZ‘ results in a single string ‘abcXYZ‘. Another variable-
length string data type called CHARACTER LARGE OBJECT or CLOB is also available to
specify columns that have large text values, such as documents. The CLOB maximum length can
be specified in kilobytes (K), megabytes (M), or gigabytes (G). For example, CLOB (20M)
specifies a maximum length of 20 megabytes.

Bit-string: Bit-string data types are either of fixed length n-BIT(n) or varying length-BIT
VARYING(n), where n is the maximum number of bits. The default for n, the length of a
character stringor bitstring,is1. Literalbitstringsare placed between single quotesbutpreceded by a
B to distinguish them from character strings.

Forexample: B’10101‘.

Another variable-length bit string data type called BINARY LARGE OBJECT or BLOB is also
available to specify columns that have large binary values, such as images. The maximum length
of a BLOB can be specified in kilobits (K), megabits (M), or gigabits (G).

Forexample,BLOB(30G)specifiesamaximumlengthof30gigabits.

Boolean: ABoolean data type hasthe traditionalvalues ofTRUE or FALSE. In SQL, because of the
presence ofNULL values,a three-valued logic isused,soa third possible value for a Boolean data
type is UNKNOWN.

DATE and TIME: The DATE data type has ten positions, and its components are YEAR,
MONTH, and DAY in the form YYYY-MM-DD. The TIME data type has at least eightpositions,
with the components HOUR, MINUTE, and SECOND in the form HH:MM:SS.

Literal values are represented bysingle-quoted strings preceded bythe keyword DATE or TIME; for example, DATE

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41
‘2008-09-27‘or TIME ‘09:12:47’.

AdditionaldatatypesusedinSQLareasfollows:
Timestamp: A timestamp data type (TIMESTAMP) includes the DATE and TIME fields, plus a
minimum of six positions for decimal fractions of seconds and an optional WITH TIME
ZONEqualifier. Literal values are represented by single quoted strings preceded by the keyword
TIMESTAMP, with a blank space between data and time.
Forexample,TIMESTAMP’2008-09-2709:12:47.648302‘.

INTERVAL: Another data type related to DATE, TIME, and TIMESTAMP is the INTERVAL
data type. This specifies an interval—a relative value that can be used toincrement or decrement
an absolute value of a date, time, or timestamp. Intervals are qualified to be either
YEAR/MONTH intervals or DAY/TIME intervals.
Q3) Explainthedifferent constraintsanddefault clause inSQL.

Constraints: After the attributes specifications the following constraints can be specified on a
table: Not null, Primary key, Foreign Key, Unique.
NOT NULL: As the SQL allows NULL as attribute value, NOT NULL constraint may be
specified if NULL is not permitted for a particular attribute. The NOT NULL constraint should
always be specified for the primary key attributes of each relation and for any other attributes
whose values are not to be NULL.

Another type of constraint can restrict attribute or domain values using the CHECK clause
following an attribute or domain definition.
For example, suppose that department numbers are restricted to integer numbers between 1 and20;
then, we can change the attribute declaration of Dnumber in the DEPARTMENT table to the
following:
DnumberINTNOTNULLCHECK(Dnumber>0 ANDDnumber<21);

TheCHECKclausecanalsobeusedinconjunctionwiththeCREATEDOMAINstatement. For
example, we can write the following statement:
CREATEDOMAIND_NUM ASINTEGER
CHECK (D_NUM>0ANDD_NUM<21);

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

SpecifyingConstraintsonTuplesUsingCHECK

In addition to key and referential integrity constraints, which are specified by special key words,
other table constraints can be specified through additional CHECK clauses at the end of
aCREATE TABLE statement. These can be called tuple-based constraints because theyapply to
each tuple individually and are checked whenever a tuple is inserted or modified.
For example, suppose that the DEPARTMENT table had an additional attributeDept_create_date,
which stores the date when thedepartment [Link] we could add the following CHECK
clause at the end of the CREATE TABLE statement for the DEPARTMENT table to make sure
that a manager‘s start date is later than the department creation date.
CHECK(Dept_create_date<=Mgr_start_date);

Default clause: It is also possible to define a default value for an attribute by appending the clause
DEFAULT <value> to an attribute definition. DEFAULT clause includes the specified value in
anynew tuple if an explicit value is not provided for that attribute. For example:
CREATE TABLE DEPARTMENT(DNAME VARCHAR(15) NOTNULL,DNUMBERINT
NOT NULL, DLOCATION VARCHAR(15) NOT NULL DEFAULT ‘MYSURU’);
Heredname,dumber,dlocationcannottakenullvaluesanddefaultvaluefordlocationis MYSURU.

PRIMARY KEY: This clause specifies one or more attributes that make up the primarykeyof a
relation.

FOREIGNKEY:Thisclause isusedtospecifyreferentialintegrity.

UNIQUE: Unique isusedtospecifyalternatekeys.

CONSTRAINT: This keyword is used to give a name to a constraint. The names of all constraints
within a particular schema must be unique. A constraint name is used to identify a particular
constraint in case the constraint must be dropped and replaced with another constraint. Example

CREATETABLEEMPLOYEE(,DNOINTNOTNULLDEFAULT1,CONSTRAINTEMPK
PRIMARY KEY(SSN), CONSTRAINT EMPFK FOREIGNKEY(DNO) REFERENCES
DEPARTMENT(DNUMBER)ON DELETE SET NULL);

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

Q4) Explain Update statements [Link] Insert, Delete and Update statement
inSQL.
In SQL three commands can be used to modify the database, they are INSERT ,DELETE ,and
UPDATE.
INSERT Command:
The insert command can be used to add data in to table. INSERT command can be used in
different forms as shown below.
The simplest form is:
INSERTINTO<table name>VALUES(val1,val2,val3,…,valn).
This form can be used to add a single tuple (row) toa relation. Here the values should be listed in
the same order in which the corresponding attributes were specified in the CREATE TABLE
command. For example:
INSERTINTOEMPLOYEEVALUES(‘Mohan‘,’12345‘,’1stmain‘,5000,’Physics‘,‘Lecturer‘);
The second form is:
INSERTINTO<tablename>(attr1,attr2,...,attrn)VALUES(val1,val2,val3,...valn);
This form allows the user to specify explicit attribute names that correspond to the values in the
INSERT command. In this case, attributes with NULL or DEFAULT values are left out. For
example:
INSERTINTOEMPLOYEE(FNAME,EMPNO)VALUES(‘Mohan‘,’123‘);
The third form is:
INSERTINTO<table name>[(attr1,attr2, ........ attrn)]SELECT<attributes>FROM<tables>
WHERE<condition>;
This form of INSERT command insert smultiple tuples into the relation based on result of the query.
For example:
INSERTINTODEPOSITSELECTBRANCH_NAME,LOAN_NUMBER,CUST_NAME
FROMBORROWWHEREBRANCH_NAME=‘Bengaluru‘;
The DELETE Command:
The DELETE command removes tuples (rows) from a table. This command includes WHERE
clause to select the tuples to be deleted. Depending on the number of tuples selected by the
condition in the WHERE clause 0, 1, 2, or several tuples can be deleted by single DELETE
command. If there is no WHERE clause in the DELETE commands, then all tuples (rows) in the
table are deleted. The general syntax is:

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

DELETEFROM<tablename>WHERE<condition>;
Forexample:DELETEFROMEMPLOYEEWHERESALARY=2000;

TheUPDATECommand:
The UPDATE command can be used to modify attribute values of one ormore selected [Link]
general syntax for using the UPDATE command is:
UPDATE<tablename>SETattr1=value,attr2=value,…,WHERE<condition>; The
SET clause specifies the attributes to be modified and their new values.
TheWHEREclauseselectsthetuplestobemodifiedfromasinglerelation(table). For
example: UPDATE EMPLOYEE SET SALARY = SALARY * 1.1WHERE

DNOIN(SELECTDNUMBERFROMDEPARTMENTWHERE
DNAME=’Research‘);

The above command updates the salary of all Research department employees by 10%.
Note:UpdatingtheprimaryKeyvaluesmaypropagatetotheforeignKeyvaluesoftuplesin other relation.

Q5) Give the complete syntax of SELECT statement in SQL and discuss all the clauses
with examples.
ThebasicstructureofanSQL expressionconsistsofsixclauses:SELECT,FROM,WHERE, GROUP
BY, HAVING, and ORDER BY has the following syntax.
SELECT<attributeandfunction
list>FROM< table list>
[WHERE <condition>]
[GROUPBY<groupingattribute(s)>]
[HAVING <group condition>]
[ORDERBY<attributelist>][ASC|DESC];

SELECT:This clause corresponds to the projection operation of the relational algebra. It is used
to list the attributes desired in the result of a query.
FROM:[Link] lists the
relations to be scanned in the evaluation of the expression.
WHERE: This clause corresponds to the selection predicate (condition) of the relational algebra. It consists of conditions
involving attributes of the relations that appears in the from clause.

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41

<attributelist>:Itisthelistofattribute namesthat areto beretrievedbythequery.

<tablelist>:It isthelistofrelationnamesrequiredtoprocessthequery.
<condition>:isaconditionalsearchexpressionthatidentifiesthetuplestoberetrievedby thequery.
Example:RetrievethenameandaddressofallemployeeswhoworkforResearch department.
SELECTENAME,ADDRESSFROMEMPLOYEE,DEPARTMENT
WHEREDNAME=’Research’ANDDNUMBER=DNO;

TheSQLGROUPBYclauseisusedincollaborationwiththeSELECTstatementtoarrange identical data

into groups.
For eg: Retrieve the total no of employeesand total salary of employees in each department,
then GROUP BY query would be as follows:
SELECTDNO,COUNT(*)asNo_of_Emp,SUM(SALARY)
FROMEMPLOYEE GROUP BY DNO;
The HAVING clause provides a condition on the group of tuples associated with each value of
the grouping attributes. Onlythe groups that satisfythe condition are retrieved in the result of the
query. For eg: For each department in which more than 2 employeeswork, retrieve the total
num of employees and total salary of employees.
SELECTDNO,COUNT(*)asNo_of_Emp,SUM(SALARY)FROM
EMPLOYEE GROUP BY DNO HAVING COUNT(*)>2;
The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on
one or more columns.
Foreg:Retrieve theSSNand Nameofemployeesarranged alphabeticallybytheir name.
SELECTSsn, Fname SELECTSsn, Fname
FROMEMPLOYEE OR FROMEMPLOYEE
ORDERBYFname; ORDERBY FnameASC;

[Link],usethekeyword DESC.

Forexample:RetrievetheSSNandNameofemployeesarranged indescendingorderbyname.
SELECT Ssn, Fname
FROM EMPLOYEE
ORDERBYFnameDESC;

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41
Note: Some database sorts queryresults in ascendingorder bydefault. We can use more than one column in the
ORDERBY clause. Make sure whatever column you are usingto sort,that column should be in column-list.

Q6)Conceptually how SQL retrieval query will be executed?Explain.

A query is evaluated conceptually by first applying the FROM clause (to identify all tables
involved in the query or to materialize any joined tables), followed by the WHERE clause to select
and join tuples, and then by GROUP BY and HAVING. Conceptually, ORDER BY is applied at
the end to sort the query result. At last, SELECT clause displays the attributesspecified.
Q7) Explain how SQL deals with Ambiguous Attributes names and how Aliasing can
be done.
In SQL the same name can be used for two or more attributes as long as the attributes are in
different relations. If this is the case, and a query refers to two are more attributes with same
name, we must qualify the attribute name with the relation name to prevent ambiguity. This is
done by prefixing the relation name to the attribute name and separating the two by a
period (.).
For example, suppose if both the relations EMPLOYEE and DEPARTMENT have the attributes
with names NAME and DNUMBER the following query can prevent the ambiguity.
SELECT [Link], AGE, ADDRESS FROM EMPLOYEE,
DEPARTMENT WHERE [Link]=‘Research’ AND
[Link]=[Link];
Using Aliases: The ambiguity also arises in case of the queries that refer to the same relation
twice. In this case the user is allowed to declare the alternative relation names called aliases. An
alias can directly follow the relation name. For example:
Retrieve name, age, and department number of all the employees whose employee number and
department number are same.
[Link],[Link],[Link] FROMEMPLOYEEE,
EMPLOYEE S [Link] = [Link];
Hererelationnames EandSarecalledaliases.
We use this alias naming mechanismin anySQLquery, whether or not the same relation needs to
be referenced more than once.

Forexample,considerthebelow query.
[Link],[Link],[Link]
EMPLOYEEE,DEPARTMENTD
[Link]=’Research’[Link]=[Link];

Dr. Salma Jabeen, ISE, DBIT

 Database Management Systems/ B24IS41
Q8)Explain the consequence of not using WHERE clause and use of *.
AmissingWHEREclauseinaqueryindicatesnoconditionontupleselection,hencealltuples of relation
specified in the FROM clause qualify and are selected for query result.
For example:Retrieve ssn ofall employees.
SELECT SSN FROM EMPLOYEE;
If more than one relation is specified in the FROM clause and there is no WHERE clause then all
possible tuple combination (CROSS PRODUCT) of the specified relations is selected (with no
proper join condition, in correct and large result may result.

Forexample:SELECTENAME, DNAMEFROMEMPLOYEE,DEPARTMENT;

Useof‘*’
To retrieve all the attribute values of the selected tuples, it is not required to list the attribute
names explicitly in SQL, instead we can just specify an asterisk (*) which stands for all the
attributes. For example:
SELECT *FROM EMPLOYEE, DEPARTMENT
WHEREDNAME=‘Research’ANDDNO=DNUMBER;

Q9)Explain why SQL does not automatically eliminate duplicate tuples in query results.
[Link] results of
queries, for the following reasons.
. Duplicate elimination is an expensive operation. One wayto implement it is to sort the tuples
first and then eliminate duplicates.
.Theusermaywantto seeduplicatetuplesin theresultofquery.
. When aggregate function is applied to tuples, in most cases the user do not want to eliminate
duplicates.
Q10)Explain the use of DISTINCT Key word in SQL.
ThekeywordDISTINCTcanbeusedinSELECTclauseto eliminateduplicatetuplesfrom theresult
of the queryi.e., only the distinct tuples remain in the result.
Forexample:Retrievethedistinctsalaryvaluesof employees.
SELECTDISTINCTSALARYFROMEMPLOYEE;

Dr. Salma Jabeen, ISE, DBIT