Databases | SQL | Oracle | MS Access Answers

Differentiate 3NF & BCNF

Write short notes on the following with suitable examples.

a. 1NF b. 2NF c. 3NF d. BCNF

Explain briefly about the need of normalization.

Q3. Given a relation R( P, Q, R, S, T, U, V, W, X, Y) and Functional Dependency set FD = { PQ → R, PS → VW, QS → TU, P → X, W → Y }, determine whether the given R is in 2NF?Describe anomalies of database.(10marks)

Q2.

EMP_ID

FIRST_NAME

LAST_NAME

SALARY

DEPARTMENT

0011

Preeti

Raheja

100000

HR

0023

Geetika

Jindal

80000

Admin

0032

Suresh

Sai

300000

HR

0044

Yash

Bhatia

500000

Admin

0056

Kumar

Gautam

500000

Admin

0067

Kiran

SaiTeja

200000

Account

0073

Raj

Goyal

75000

Account

0089

Priya

Jindal

90000

Admin

Create the given database with the condition that EMP_ID and FIRST_NAME will be the primary key of table and all unique values in LAST_NAME (3 Marks)

Perform the following queries on created database.  

1. SQL query to fetch Last name of employees whose department is Account and salary is more than 80000.                                                                                   (2 Mark)

2. Write an SQL query to fetch “FIRST_NAME” from the table in upper case.                     (1 Mark)

3. SQL query to extract the average salary given to the employees.                                 (1 Mark)

4. Change the department of KUMAR from Admin to Account.                                                  (1 Mark)

5. Delete the FIRST_NAME column from the database.                                               (2 Marks).

Q1. (a) What is Serializability?Explain different types of Serializability.

(b) Check the given schedule for conflict serializability:

	S1: R1(X) R1(Y) R2(X) R2(Y) W2(Y) W1(X)

All the steps for checking the serializability should be written properly and diagram should be drawn well if any

Consider the following relation with set of functional dependencies R(ABCDEF) ABC->D, ABD->E, CD->F, CDF->B, BF->D

a) Identify the candidate key(s) in the relation (with proper steps followed).

b) Identify which normal form this relation is in and reason for the answer.

num number:= #

fact number:= 1;

temp number;

begin

temp := num;

while (num > 0)

loop

fact := fact * num;

num := num - 1;

end loop;

Dbms_Output.Put_line('factorial of ' || temp || ' is ' || fact);

Consider the following schedule of transactions

R1(A) W1(A) R2(A) W2(B) W1(B) Commit1 Commit2

Which of the following properties are true for the above schedule and justify the reason for your answer

with explanation?

a) Conflict Serializable

b) View Serializable

c) Recoverable

Consider the following relation with set of functional dependencies R(ABCDEF) ABC->D, ABD->E, CD->F, CDF->B, BF->D a) Identify the candidate key(s) in the relation (with proper steps followed). b) Identify which normal form this relation is in and reason for the answer