1
L .J. INSTITUTE OF ENGINEERING & TECHNOLOGY
GTU B.E. SEM – III (Computer Engineering)
Sub: - Database Management System (2130703)
Question Bank with Solution (Year – 2016)
Unit 2: Relational Model
1. Explain candidate key, primary key and foreign key[LJIET] Dec 2009
Candidate Key
A key for which no proper sub set is a superkey, such minimal key is called as candidate key.
E.g Consider Customer relation with relational schema customer( cust_id, cust_name, street, city). {cust_id}
and {cust_name, street} both are candidate keys
Primary key
A candidate key chosen by databse designer as the principal means of identifying tuples within a relation is
called as primary key. It should be chosen such that its attribute value are never changed or very rarely change
E.g. relational schema customer ( cust_id, cust_name, street, city) primary key is {cust_id} customer (
cust_id, cust_name, street, city)
Foreign key
A relational schema r1 may include among its attribute the primary key of another relational schema r2. This
attribute is called as foreign key. Relation r1 is called as referencing relation and relation r2 is called as
referenced relation
E.g. Account (acc_no, balance, branch) and deposit (cust_name, acc_no).
In deposit relation acc_no, primary key of Account relation appears as normal attribute is foreign key
2. Explain following relational algebra operation
(i)Natural join operation
(ii) Selection and projection operation [LJIET]Dec 2009
OR
List relational algebra operators and explain any two with example [LJIET]May 2011
OR
Explain selection and projection operation with example. LJIET]Dec 2011
(i) Natural join operation
Natural Join operation is a binary relational algebra operation that allows us to combine certain
selection and Cartesian product into one operation.
It is denoted by |X| symbol
The natural join operation forms a Cartesian product of its two argument, perform selection forcing
equality on those attribute that appear to both relational schemas, and finally removes duplicate
attribute
E.g. relations instructor and teaches, computing instructor natural join teaches considers only those
pairs of tuples where both the tuple from instructor and the tuple from teaches have the same value
on the common attribute ID
“Find the names of all instructors together with the course id of all courses they taught.” We express this
query by using the natural join as follows:
πname, course id (instructor |X| teaches)
It combines each such pair of tuples into a single tuple on the union of the two schemas; that is, (ID,
name, dept name, salary, course id).
Prepared By: - Prof. Munira Topia, Computer Engineering Department, L. J. Institute of Engineering & Technology, Ahmedabad
2
(ii) Selection and projection operation
Selection operation
The select operation selects tuples that satisfies a given predicate.
Greek letter sigma is ( Ϭ ) used to denote selection operation
Predicate appears as subscript to Ϭ
The argument relation is in parentheses after Ϭ
We combine several predicate into larger predicate by using connectives and (^) or (v) not(⌐)
E.g. Ϭ salary>=85000(instructor)
Projection Operation
The project operation is a unary operation that returns its argument relation, with certain attributes
left out.
If any duplicate rows are eliminated.
Projection is denoted by the uppercase Greek letter pi (π). We list those attributes that we wish to
appear in the result as a subscript to π. The argument relation follows in parentheses
Suppose we want to list all instructors’ ID, name, and salary, but do not care about the dept name
o
πID, name, salary(instructor)
3. Consider following schema and represent given statements in relation algebra form.
* Branch(branch_name,branch_city)
* Account(branch_name, acc_no, balance)
*Depositor(Customer_name, acc_no)
( i ) Find out list of customer who have account at ‘abc’ branch.
( ii ) Find out all customer who have account in ‘Ahmedabad’ city and balance is greater than 10,000.
(iii) find out list of all branch name with their maximum balance. [LJIET]Mar 2010
(i) π Ϭ (Deposit |X| Account)
Customer_name branch_name=’abc’
π Ϭ
(ii) customer_name branch_city=’Ahmedabad’ ^ balance > 10000 (Deposit |X| Account |X| Branch)
(iii) branch_name G Max(balance) (Account)
4. What is join? Explain various type of joins with example [LJIET]Dec 2010, May2012
OR
Explain various types of join with suitable example[LJIET]May2013Nov2013
OR
What is a Join? Explain different types of outer join with appropriate example May 2014
The join operation allows the combining of two relations by merging pairs of tuples, one from each relation, into a
single tuple.
There are a number of different ways to join relations
1. Natural Join
2. Outer Join
2.1 Left Outer join
2.2 Right Outer Join
2.3 Full outer join
Prepared By: - Prof. Munira Topia, Computer Engineering Department, L. J. Institute of Engineering & Technology, Ahmedabad
3
natural join
It considers only those pairs of tuples with the same value on those attributes that appear in the schemas of both
relations
Consider the example “Find the names of all customers who have a loan at the bank, and find the amount of the
loan.”We express this query by using the natural join as follows:
Since the schemas for borrower and loan have the attribute loan-number in common, the natural-join operation
considers only pairs of tuples that have the same value on loan-number.
It combines each such pair of tuples into a single tuple on the union of the two schemas
(customer-name, branch-name, loan-number, amount).
After performing the projection, we obtain the relation in Figure
Outer join
Refer ans. 14
5. What is Relational Algebra? Define Relational Algebra Operation cross product with example. [LJIET]May
2012
The relational algebra is a procedural query language.
It consists of a set of operations that take one or two relations as input and produce a new relation as their
result.
The fundamental operations in the relational algebra are select, project, union, set difference, Cartesian
product, and rename.
There are several other operations—namely, set intersection, natural join, division, and assignment.
The Cartesian-product operation, denoted by a cross (×), allows us to combine information from any two
relations.
We write the Cartesian product of relations r1 and r2 as r1 × r2.
For example, the relation schema for r = borrower × loan is
(borrower.customer-name, borrower.loan-number, loan.loan-number, loan.branch-name, loan.amount)
Prepared By: - Prof. Munira Topia, Computer Engineering Department, L. J. Institute of Engineering & Technology, Ahmedabad
4
With this schema, we can distinguish borrower.loan-number from loan.loan-number.
This simplification does not lead to any ambiguity. We can then write the relation schema for r as
(customer-name, borrower.loan-number, loan.loan-number,branch-name, amount)
The tuples of r out of each possible pair of tuples: one from the borrower relation and one from the
loan relation.
Thus, r is a large relation, as you can see from Figure, which includes only a portion of the tuples that
make up r.
Assume that we have n1 tuples in borrower and n2 tuples in loan.
Then, there are n1 ∗ n2 ways of choosing a pair of tuples—one tuple from each relation; so there are n1
∗ n2 tuples in r.
Some tuples t in r, it may be that t[borrower.loan-number] _= t[loan.loan-number].
If relations r1(R1) and r2(R2), then r1 × r2 is a relation whose schema is the concatenation of R1 and
R2. Relation R contains all tuples t for which there is a tuple t1 in r1 and a tuple t2 in r2 for which t[R1]
= t1[R1] and t[R2] = t2[R2].
6. What is NULL? Explain[LJIET]May 2012
An attribute takes a null value when an entity does not have a value for it.
The null value may indicate “not applicable”—that is, that the value does not exist for the entity. For example,
one may have no middle name.
Null can also designate that an attribute value is unknown. An unknown value may be either missing (the value
does exist, but we do not have that information) or not known (we do not know whether or not the value
actually exists).
For instance, if the name value for a particular customer is null, we assume that the value is missing, since every
Prepared By: - Prof. Munira Topia, Computer Engineering Department, L. J. Institute of Engineering & Technology, Ahmedabad
5
customer must have a name. A null value for the apartment-number attribute could mean that the address does not
include an apartment number (not applicable), that an apartment number exists but we do not know what it is
(missing), or that we do not know whether or not an apartment number is part of the customer’s address
(unknown).
7. Define: (1) Primary key (2) Foreign key (3) Unique Key (4) Not null (5) Commit (6) Candidate key (7
Rollback. [LJIET]May 2013
Primary key
A candidate key chosen by database designer as the principal means of identifying tuples within a relation
called as primary key.
It should be chosen such that its attribute value are never changed or very rarely change
E.g. relational schema customer ( cust_id, cust_name, street, city) primary key is {cust_id} custom
(cust_id, cust_name, street, city)
Foreign key
A relational schema r1 may include among its attribute the primary key of another relational schema r
This attribute is called as foreign key. Relation r1 is called as referencing relation and relation r2 is calle
as referenced relation
E.g. Account (acc_no, balance, branch) and deposit (cust_name, acc_no).
In deposit relation acc_no, primary key of Account relation appears as normal attribute is foreign key
Unique key
1. Unique key will not allow duplicate values
2. Unique index is automatically created
3. A table can have more than one unique key
Syntax: <column_name> <datatype> UNIQUE
Not null:
The not null constraint on an attribute specifies that the null value is not allowed for that attribute
The constraint excludes the null value from the domain of that attribute.
For example, the not null constraint on the name attribute of the instructor relation ensures that the name of a
instructor cannot be null
create table instructor
(ID varchar (5),name varchar (20) not null, dept name varchar (20),salary numeric (8,2))
Candidate Key
A key for which no proper sub set is a superkey, such minimal key is called as candidate key. E.g Consider
Customer relation with relational schema customer( cust_id, cust_name, street, city). {cust_id} and
{cust_name, street} both are candidate keys
Prepared By: - Prof. Munira Topia, Computer Engineering Department, L. J. Institute of Engineering & Technology, Ahmedabad
6
Commit
Commit ends the current transaction and makes permanent any changes made during the transaction. All
transactional locks acquired on tables are released
Syntax: Commit;
Rollback
It ends the current transaction but undoes any changes made during transaction. All transactional locks
acquired on tables are released
Syntax: rollback [[work] to savepoint <savepointName>];
8. Explain following terms with suitable example.
(1) Primary Key (2) Candidate Key (3) Foreign Key (4) On Delete Cascade LJIET]Dec 2013
Refer above answers
(4) On Delete Cascade
An error is generated when a record in referenced table(Master Table) is deleted and corresponding records
exist in a referencing table(detail/ Child table) prevents the delete operation
When ON DELETE CASCADE option is specified in the foreign key definition, then if record is deleted in
mater table all records in child table along with record in master table will be deleted
Example:
Create table Employee
( ename varchar2(30) primary key,
salary number(8,2));
Create table Works_on
( ename varchar2(30) references Employee ON DELETE CASCADE,
project varchar2(20));
whenever a record from Employee table is deleted
9. Write short note on Relational algebra.[LJIET]May 2014
The relational algebra is a procedural query language.
The relational algebra defines a set of operations on relations, paralleling the usual algebraic
operations such as addition, subtraction or multiplication, which operate on numbers.
Just as algebraic operations on numbers take one or more numbers as input and return a number as
output, the relational algebra operation typically take one or two relations as input and return a relation
as output.
The select, project, and rename operations are called unary operations, because they operate on one
relation. The other three operations operate on pairs of relations and are, therefore, called binary
operations.
Prepared By: - Prof. Munira Topia, Computer Engineering Department, L. J. Institute of Engineering & Technology, Ahmedabad
7
10. List all the Relational algebra operators. Explain the working of Cartesian product Operation and the Division
Operation with an appropriate example. [LJIET]May 2014 Dec 2014
The select, project, and rename operations are called unary operations, because they operate on one relation.
The other three operations operate on pairs of relations and are, therefore, called binary operations
The Cartesian-product operation
Denoted by a cross (×), allows us to combine information from any two relations.
We write the Cartesian product of relations r1 and r2 as r1 × r2.
For example, the relation schema for r = borrower × loan is
(borrower.customer-name, borrower.loan-number, loan.loan-number, loan.branch-name, loan.amount)
With this schema, we can distinguish borrower.loan-number from loan.loan-number.
This simplification does not lead to any ambiguity. We can then write the relation schema for r as
(customer-name, borrower.loan-number, loan.loan-number,branch-name, amount)
The tuples of r out of each possible pair of tuples: one from the borrower relation and one from the loan
relation.
Thus, r is a large relation, as you can see from Figure, which includes only a portion of the tuples that make
up r.
Prepared By: - Prof. Munira Topia, Computer Engineering Department, L. J. Institute of Engineering & Technology, Ahmedabad
8
Assume that we have n1 tuples in borrower and n2 tuples in loan.
Then, there are n1 ∗ n2 ways of choosing a pair of tuples—one tuple from each relation; so there are n1
∗ n2 tuples in r.
The Division Operation
The division operation, denoted by ÷, is suited to queries that include the phrase “for all.”
Suppose that we wish to find all customers who have an account at all the branches located in Brooklyn.
We can obtain all branches in Brooklyn by the expression
r1 = Πbranch-name (σbranch-city =“Brooklyn” (branch))
The result relation for this expression appears in Figure
We can find all (customer-name, branch-name) pairs for which the customer has an account at a branch by writing
r2 = Πcustomer-name, branch-name (depositor � account)
Below Figure 2 shows the result relation for this expression.
Prepared By: - Prof. Munira Topia, Computer Engineering Department, L. J. Institute of Engineering & Technology, Ahmedabad
9
To find customers who appear in r2 with every branch name in r1. The operation that provides exactly those
customers is the divide operation.
formulate the query by writing
Πcustomer-name, branch-name (depositor � account)
÷ Πbranch-name (σbranch-city =“Brooklyn” (branch))
The result of this expression is a relation that has the schema (customer-name) and that contains the tuple
(Johnson).
Formally, let r(R) and s(S) be relations, and let S ⊆ R; that is, every attribute of schema S is also in schema R. The
relation r ÷ s is a relation on schema R − S (that is, on the schema containing all attributes of schema R that are not
in schema S).
A tuple t is in r ÷ s if and only if both of two conditions hold:
1. t is in ΠR−S(r)
2. For every tuple ts in s, there is a tuple tr in r satisfying both of the following:
a. tr[S] = ts[S]
b. tr[R − S] = t
11. What is database schema? Explain the select, project, natural join, union and Cartesian product operations.
[LJIET] Jan 2013
The database schema, which is the logical design of the database
The concept of a relation schema corresponds to the programming-language notion of type definition.
It is convenient to give a name to a relation schema, just as we give names to type definitions in
programming languages.
we use Account-schema to denote the relation schema for relation account. Thus,
Account-schema = (account-number, branch-name, balance)
We denote the fact that account is a relation on Account-schema by
account(Account-schema)
In general, a relation schema consists of a list of attributes and their corresponding domains.
Prepared By: - Prof. Munira Topia, Computer Engineering Department, L. J. Institute of Engineering & Technology, Ahmedabad
10
12. Explain different types of outer join with example. [LJIET]May 2014
The outer-join operation is an extension of the join operation to deal with missing information.
Suppose that we have the relations with the following schemas, which contain data on full-time employees:
employee (employee-name, street, city)
ft-works (employee-name, branch-name, salary)
Consider the employee and ft-works relations in Figure 3.31. Suppose that we want to generate a single
relation with all the information (street, city, branch name, and salary) about full-time employees.
A possible approach would be to use the naturaljoin operation as follows:
employee |X| ft-works
The result of this expression appears in Figure 3.32
Notice that we have lost the street and city information about Smith, since the tuple describing Smith is
absent from the ft-works relation; similarly, we have lost the branch name and salary information about
Prepared By: - Prof. Munira Topia, Computer Engineering Department, L. J. Institute of Engineering & Technology, Ahmedabad
11
Gates, since the tuple describing Gates is absent from the employee relation.
We can use the outer-join operation to avoid this loss of information.
There are actually three forms of the operation:
1. left outer join, denoted ]X|
2. right outer join, denoted |X[
3. full outer join, denoted ]X[ . All three forms of outer join compute the join, and add extra tuples to the
result of the join.
Prepared By: - Prof. Munira Topia, Computer Engineering Department, L. J. Institute of Engineering & Technology, Ahmedabad
12
Prepared By: - Prof. Munira Topia, Computer Engineering Department, L. J. Institute of Engineering & Technology, Ahmedabad
