PREPARED BY SHARIKA T R, SNGCE

Question Paper Discussion

1. What are the basic data types available for attributes in SQL? (3)
Ans. The basic data types available for attributes include
 char(n): Fixed length character string, with user-specified length n.
 varchar(n): Variable length character strings, with user-specified maximum length n.
 int: Integer (a finite subset of the integers that is machine-dependent).
 smallint: Small integer (a machine-dependent subset of the integer domain type).
 numeric(p,d): Fixed point number, with user-specified precision of p digits, with d
digits to the right of decimal point. (ex., numeric(3,1), allows 44.5 to be stores exactly,
but not 444.5 or 0.32)
 real, double precision: Floating point and double-precision floating point numbers,
with machine-dependent precision.
 float(n): Floating point number, with user-specified precision of at least n digits.

2. List the aggregate functions in SQL. (3)

Ans. Aggregate functions are used to summarize information from multiple tuples into a
single-tuple summary.
Grouping is used to create sub-groups of tuples before summarization.
A number of built-in aggregate functions exist:
 COUNT function returns the number of tuples or values as specified in a query.
 SUM function returns the sum of a multiset
 MAX function returns maximum of a multiset
 MIN function returns minumum of a multiset and
 AVG function returns average of a multiset
These functions can be used in the SELECT clause or in a HAVING clause

3. Let E = {B A, DA, ABD} is a set of Functional Dependencies. Find a minimal

cover for E.
Ans. Step 1
All above dependencies are in canonical form that is, they have only one attribute on the
right-hand side
Step 2
we need to determine if AB → D has any redundant attribute on the left-hand side;
that is, can it be replaced by B → D or A → D?
Since B → A, by augmenting with B on both sides (IR2), we have BB → AB, or B → AB (i).
However, AB → D as given (ii).
Hence by the transitive rule (IR3), we get from (i) and (ii), B→ D. Thus AB → D may be
replaced by B → D.

1
PREPARED BY SHARIKA T R, SNGCE

We now have a set equivalent to original E, say

E’: {B → A, D → A, B → D}.
No further reduction is possible in step 2 since all FDs have a single attribute on the left-hand
side.
Step 3
we look for a redundant FD in E’.
By using the transitive rule on B → D and D → A, we derive B→ A. Hence B → A is
redundant in E’ and can be eliminated.
Therefore, the minimal cover of E is {B → D, D → A}.

4. Define Boyce-Codd normal form(BCNF). Give an example of a relation that is in 3NF but
not in BCNF. (3)
Ans. Boyce-Codd normal form (BCNF) was proposed as a simpler form of 3NF, but it was
found to be stricter than 3NF. That is, every relation in BCNF is also in 3NF; however, a
relation in 3NF is not necessarily in BCNF. BCNF is stricter than 3NF.
A table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X
should be the super key of the table.
Relation Std_tech(Student, course, teacher)
With FD={Student, course}--> teacher
Teacher-->course
This is in 3nf not in bcnf

5. Consider the following relations for bank database (Primary keys are underlined):
Customer (customer-name, customer-street, customer-city)
Branch (branch-name, branch-city, assets)
Account (account-number, branch-name, balance)
Depositor (customer-name, account-number)
Loan (loan-number, branch-name, amount)
Answer the following in SQL:
i) Create tables with primary keys and foreign keys (5)
ii) Create an assertion for the sum of all loan amounts for each branch must be less than the
sum of all account balances at the branch. (4)
Ans ii) create assertion sum-constraint check
(not exists (select * from branch
where (select sum(amount) from loan
where (loan.bname = branch.bname >=
(select sum)amount) from account
where (account.bname = branch.bname)))

6. Given R(A,B,C,D,E) with the set of FDs, F = {AB→CD, ABC →E, C →A}.
i) Find any two candidate keys of R (3)

2
PREPARED BY SHARIKA T R, SNGCE

ii) What is the normal form of R? Justify your answer. (6)

Ans. I)
Ii) AB→CD //SK(AB) determines prime(C)or non prime(D) allowed in 3NF
ABC →E // SK(AB) determines non prime(E) allowed in 3NF
C →A //Prime(C) determine Prime(C) allowed in 3NF
So this is in 3NF

7. a) What are Armstrong’s axioms? (3)

b) Write an algorithm to compute the attribute closure of a set of attributes (X)
under a set of functional dependencies (F).(3)
c) Explain three uses of attribute closure algorithm. (3)
Ans. Refer notes

8. What are the different types of single-level ordered indices? Explain. (10)
Ans. Explain Primary, Clustered and Secondary level indexing with example and figure

9. a) What is a B+-tree? (2)

b) Describe the structure of both internal and leaf nodes of a B+-tree of order p
Ans. Refer notes

10. Differentiate between static hashing and dynamic hashing. (10)

Ans. Refer notes

11. Illustrate the GROUP BY clause with the help of a real example. (3)

3
PREPARED BY SHARIKA T R, SNGCE

12. Determine any two candidate keys of the relation R(A,B,C,D,E,F) with FDs
AB→C,C→AD, D→EF, F→B.

Ans.

13. Give an example for a relation that has insertion, deletion and update anomalies. Which
type(s) of functional dependency can formally model these anomalies? Quote one such
0dependency from your example(3)

14. Illustrate the use of assertions with a typical example. (3)

4
PREPARED BY SHARIKA T R, SNGCE

Ans.

15. Consider a relation (A,B,C,D,E,F) with A as the only key. Assume that the dependencies
E→F and C→DEH hold on R.
(i) Is R is in 2NF? If not, decompose to 2NF.
(ii) Is R is in 3NF? If not, decompose to 3NF. (6)
Ans.

16. In the following tables ADVISOR and TAUGHTBYare foreign keyd referring to the
table PROFESSOR. ROLLNO and COURSEID in ENROLLMENT refer to tables with
primary keys of the same name.
STUDENT(ROLLNO, NAME, AGE, GENDER, ADDRESS, ADVISOR)
COURSE(COURSEID, CNAME, TAUGHTBY, CREDITS)
PROFESSOR(PROFID,PNAME, PHONE)
ENROLLMENT(ROLLNO, COURSEID, GRADE)
Write SQL expressions for the following queries:
(i) Names of courses taught by ‘Prof. Raju’.
(ii) Names of students who have not enrolled for any course taught by ‘Prof. Ganapathy’.

5
PREPARED BY SHARIKA T R, SNGCE

(iii) For each course, name of the course and number of students enrolled for the course.

17. Consider a relation R={A,B,C,D,E,F} and a set of functional dependencies

F={A→BC,C→BD,BF→E,F→D}. Find the closure of A. Is A a candidate key? Justify.(3)

Ans.

6
PREPARED BY SHARIKA T R, SNGCE

18.

Ans.

19. Given a relation R(A,B,C). Find the minimal cover of the set of functional dependencies
given;
F= {A→BC, B→C, A→B, AB→C}
Ans.

7
PREPARED BY SHARIKA T R, SNGCE

20. Consider the relation R = {A, B, C, D, E, F, G, H} and the set of functional dependencies
F = {A→DE, B→F, AB→C, C→GH, G→H}. What is the key for R? Decompose R into
2NF and then 3NF relations. (9)
Ans.

8
PREPARED BY SHARIKA T R, SNGCE

21. Consider the following set F of functional dependencies for relation schema
R = (A, B, C, D, E).
F= {A → BC ,CD → E, B → D, E → A}
Compute the canonical cover of F. (3)
Ans. The given set of FDs F is:- A → BC CD → E B → D E → A The left side of each FD in
F is unique. Also none of the attributes in the left side or right side of any of the FDs is
extraneous. Therefore the canonical cover Fc is equal to F.

22. Suppose that we have an ordered file with 400,000 records stored on a disk with block size
4,096 bytes. File records are of fixed size and are unspanned,with record length 200 bytes.
How many blocks are needed for the file? Approximately, how many block accesses are
required for a binary search in this file? On an average, how many block accesses are
required for a linear search, if the file is nonordered? (6)

Ans.

9
PREPARED BY SHARIKA T R, SNGCE

23. Given below are two sets of FDs for a relation R(A,B,C,D,E).Are they equivalent?
F1 = {AB, ABC, DAC, DE}
F2 = {ABC, DAE} (5)

Ans.

10
PREPARED BY SHARIKA T R, SNGCE

24. Suppose that we decompose the schema R = (A, B, C, D, E) into

R1(A, B, C)
R2(A, D, E)
Test whether the given decomposition is a lossless-join decomposition, if the following set
F of functional dependencies holds in R:
F= {A BC, D E, B  D, E  A} (5)
Ans.

11
PREPARED BY SHARIKA T R, SNGCE

25. Given a relation R(A,B,C,D,E,F,G, H) with keys BD and C and functional dependencies
D→G, E→F and H→C, decompose the R into the highest normal form possible.(9)

Ans.

12
PREPARED BY SHARIKA T R, SNGCE

26. Consider the following relations for bank database (Primary keys are underlined):
Customer (customer-name, customer-street, customer-city)
Branch (branch-name, branch-city, assets)
Account (account-number, branch-name, balance)
Depositor (customer-name, account-number)
Loan (loan-number, branch-name, amount)
Answer the following in SQL:
i) Create tables with primary keys and foreign keys
ii) Create an assertion for the sum of all loan amounts for each branch must be less than the
sum of all account balances at the branch.
Ans.

13