I am posting here some questions on different topics. I have compiled these questions from various sources and have edited them to make them more understandable. I hope it may help the candidates.
Q 1. Select which is/are true:
1) A foreign key can reference a non-primary key
2) A unique key column can have multiple NULL value(s).
3) A foreign key may contain null values.
4) A primary key is always NOT NULL.
A) 1,2,3,4
B) 4 only
C) 1,4
d)1,3,4
Ans. (A)
SOLUTION: All statements are TRUE. A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. Means, the non-null portions of the key do not have to match any corresponding portion of a parent key.
According to the SQL standard and Relational Model theory, a unique key (unique constraint) should accept NULL in several rows/tuples — however not all RDBMS implement this feature correctly.
A Primary key is automatically declared as NOT NULL since any Primary key has four properties:
1). It is always NOT NULL.
2) It always carries/allows unique data only.
3) One table can have at most one primary key.
4) primary key column(s) is/are always indexed (for fast retrievals in queries).
Q 2. Which of the following queries cannot be expressed using the basic relational algebra operations? (GATE CS 2000)
A) Department address of every employee
B) Employees whose name is the same as their department name
C) The sum of all employees’ salaries
D) All employees of a given department
Ans. (D)
SOLUTION: The six basic operators of relational algebra are the selection, the projection, the Cartesian product, the set union, the set difference, and the rename (p). None of them can be omitted without losing expressive power. Many other operators have been defined in terms of these six. Among the most important are set intersection, division, and the natural join, but aggregation is not possible with these basic relational algebra operations. So, we cannot run sum of all employees’ salaries with the six operations.
Q 3. Which one of the following statements about normal forms is FALSE?
A) BCNF is stricter than 3NF
B) Lossless, dependency-preserving decomposition into 3NF is always possible
C) Lossless, dependency-preserving decomposition into BCNF is always possible
D) Any relation with two attributes is in BCNF
Ans. (C)
SOLUTION: In some cases, a non-BCNF table cannot be decomposed into tables that satisfy BCNF and preserve the dependencies that held in the original table. Unlike the first three normal forms, BCNF is not always achievable.
However, only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF. A 3NF table with two or more overlapping candidate keys may or may not be in BCNF.
For example, R(A,B,C,D) which has FDs: AB -> C, BC -> D. Here candidate key B is overlapping, i.e. composite candidate keys with at least one attribute in common.
Q 4. How many 8-bit characters can be transmitted per second over a 9600 baud serial communication link using asynchronous mode of transmission with one start bit, eight data bits, two stop bits and one parity bit?
A) 600
B) 800
C) 876
D) 1200
Ans. (B)
SOLUTION: For 9600 baud, 1 bit=1/9600=0.104 mS. Each char would require 12 bits (8 bit data+1 start bit+2 stop bits+1 parity bit). That means, to transmit one char, it would take 1.248 mS (0.104*12). So, applying the formula 1000/1.1248 (mS in one Sec/transmission time for one char)=approx 801.28. Nearest ans is (2)
Q 5. Consider the methods used by processes P1 and P2 for accessing their critical sections whenever needed, as given below. The initial values of shared boolean variables S1 and S2 are randomly assigned.
Method Used by P1
while (S1 == S2) ;
Critica1 Section
S1 = S2;
Method Used by P2
while (S1 != S2) ;
Critica1 Section
S2 = not (S1);
Q 6. Which one of the following statements describes the properties achieved? (GATE CS 2010)
A) Mutual exclusion but not progress
B) Progress but not mutual exclusion
C) Neither mutual exclusion nor progress
D) Both mutual exclusion and progress
Ans. (A)
SOLUTION: Mutual Exclusion requirement is satisfied here since the loop condition in p1 and p2 ensures only one will execute, if ever, in its critical section (CS). But Progress Requirement is not satisfied becoz as per definition of Progress Requirement:
"If no process is executing in its CS and there exist some process(es) that wishes to enter their CS, then the selection of the processes that will enter the CS next CANNOT BE POSTPONED indefinitely."
Here, selection can be delayed. Execution of one is dependent on the other which hinders the progress. Suppose process 1 after executing CS again wants to execute the CS and Process 2 doesn't want to execute CS then in that case p1 has to unnecessarily wait for p2.
NOTE: The question on critical section is also in MCQ book by Timothy Williams in which he mentioned ans. as D. The book has explanation also for this answer. However, I found different answer after researching it on Internet. Please post your comments/ideas becuse it is very imp. to be sure of ans of such confusing Q & A.
Q 7 Location transparency allows :
I. Users to treat the data as if it is done at one location.
II. Programmers to treat the data as if it is at one location.
III. Managers to treat the data as if it is at one location.
Which one of the following is correct ?
A) I, II and III
B) I and II only
C) II and III only
D) II only
Ans. (A)
SOLUTION: Location transparency means the location of data must not matter to the person who accesses/manipulates the data. This is a feature of distributed databases, which applies to every kind of database user. According to a definition on Wikipedia, "The location of a resource doesn't matter to either the software developers or the end-users. This creates the illusion that the entire system is located in a single computer, which greatly simplifies software development."
The I and II are database users. The III is a component of distributed databases. Database Manager components are responsible for providing seamless data access to users without regards to its location. Hence, this covers all 3 choices.
Q 8. Which of the following is correct ?
I. Two phase locking is an optimistic protocol.
II. Two phase locking is pessimistic protocol
III. Time stamping is an optimistic protocol.
IV. Time stamping is pessimistic protocol.
A) I and III
B) II and IV
C) I and IV
D) II and III
Ans. (D)
SOLUTION: Optimistic Vs. Pessimistic approach: The optimistic concurrency control approach doesn't actually lock anything. It is based on the assumption that conflicts of database operations are very less. Means, when when oner transaction is executing, other transactions will not access the same data item being accessed by the executing one. It lets transactions run to completion and only checks for conflicts when they are about to commit. Thus, a transaction is executed without any restrictions until it is committed.
The pessimistic approach believes that some other transaction might try to access the same piece of data. So, in order to prevent any conflict, a transaction will first acquire all the required locks, then perform all the operations. It has two phases:
1. Growing Phase, where a transaction must first acquire all the locks.
2. Shrinking Phase, where a transaction releases all the locks one-by-one.(It cannot issue lock requests here.)
Q 1. Select which is/are true:
1) A foreign key can reference a non-primary key
2) A unique key column can have multiple NULL value(s).
3) A foreign key may contain null values.
4) A primary key is always NOT NULL.
A) 1,2,3,4
B) 4 only
C) 1,4
d)1,3,4
Ans. (A)
SOLUTION: All statements are TRUE. A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. Means, the non-null portions of the key do not have to match any corresponding portion of a parent key.
According to the SQL standard and Relational Model theory, a unique key (unique constraint) should accept NULL in several rows/tuples — however not all RDBMS implement this feature correctly.
A Primary key is automatically declared as NOT NULL since any Primary key has four properties:
1). It is always NOT NULL.
2) It always carries/allows unique data only.
3) One table can have at most one primary key.
4) primary key column(s) is/are always indexed (for fast retrievals in queries).
Q 2. Which of the following queries cannot be expressed using the basic relational algebra operations? (GATE CS 2000)
A) Department address of every employee
B) Employees whose name is the same as their department name
C) The sum of all employees’ salaries
D) All employees of a given department
Ans. (D)
SOLUTION: The six basic operators of relational algebra are the selection, the projection, the Cartesian product, the set union, the set difference, and the rename (p). None of them can be omitted without losing expressive power. Many other operators have been defined in terms of these six. Among the most important are set intersection, division, and the natural join, but aggregation is not possible with these basic relational algebra operations. So, we cannot run sum of all employees’ salaries with the six operations.
Q 3. Which one of the following statements about normal forms is FALSE?
A) BCNF is stricter than 3NF
B) Lossless, dependency-preserving decomposition into 3NF is always possible
C) Lossless, dependency-preserving decomposition into BCNF is always possible
D) Any relation with two attributes is in BCNF
Ans. (C)
SOLUTION: In some cases, a non-BCNF table cannot be decomposed into tables that satisfy BCNF and preserve the dependencies that held in the original table. Unlike the first three normal forms, BCNF is not always achievable.
However, only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF. A 3NF table with two or more overlapping candidate keys may or may not be in BCNF.
For example, R(A,B,C,D) which has FDs: AB -> C, BC -> D. Here candidate key B is overlapping, i.e. composite candidate keys with at least one attribute in common.
Q 4. How many 8-bit characters can be transmitted per second over a 9600 baud serial communication link using asynchronous mode of transmission with one start bit, eight data bits, two stop bits and one parity bit?
A) 600
B) 800
C) 876
D) 1200
Ans. (B)
SOLUTION: For 9600 baud, 1 bit=1/9600=0.104 mS. Each char would require 12 bits (8 bit data+1 start bit+2 stop bits+1 parity bit). That means, to transmit one char, it would take 1.248 mS (0.104*12). So, applying the formula 1000/1.1248 (mS in one Sec/transmission time for one char)=approx 801.28. Nearest ans is (2)
Q 5. Consider the methods used by processes P1 and P2 for accessing their critical sections whenever needed, as given below. The initial values of shared boolean variables S1 and S2 are randomly assigned.
Method Used by P1
while (S1 == S2) ;
Critica1 Section
S1 = S2;
Method Used by P2
while (S1 != S2) ;
Critica1 Section
S2 = not (S1);
Q 6. Which one of the following statements describes the properties achieved? (GATE CS 2010)
A) Mutual exclusion but not progress
B) Progress but not mutual exclusion
C) Neither mutual exclusion nor progress
D) Both mutual exclusion and progress
Ans. (A)
SOLUTION: Mutual Exclusion requirement is satisfied here since the loop condition in p1 and p2 ensures only one will execute, if ever, in its critical section (CS). But Progress Requirement is not satisfied becoz as per definition of Progress Requirement:
"If no process is executing in its CS and there exist some process(es) that wishes to enter their CS, then the selection of the processes that will enter the CS next CANNOT BE POSTPONED indefinitely."
Here, selection can be delayed. Execution of one is dependent on the other which hinders the progress. Suppose process 1 after executing CS again wants to execute the CS and Process 2 doesn't want to execute CS then in that case p1 has to unnecessarily wait for p2.
NOTE: The question on critical section is also in MCQ book by Timothy Williams in which he mentioned ans. as D. The book has explanation also for this answer. However, I found different answer after researching it on Internet. Please post your comments/ideas becuse it is very imp. to be sure of ans of such confusing Q & A.
Q 7 Location transparency allows :
I. Users to treat the data as if it is done at one location.
II. Programmers to treat the data as if it is at one location.
III. Managers to treat the data as if it is at one location.
Which one of the following is correct ?
A) I, II and III
B) I and II only
C) II and III only
D) II only
Ans. (A)
SOLUTION: Location transparency means the location of data must not matter to the person who accesses/manipulates the data. This is a feature of distributed databases, which applies to every kind of database user. According to a definition on Wikipedia, "The location of a resource doesn't matter to either the software developers or the end-users. This creates the illusion that the entire system is located in a single computer, which greatly simplifies software development."
The I and II are database users. The III is a component of distributed databases. Database Manager components are responsible for providing seamless data access to users without regards to its location. Hence, this covers all 3 choices.
Q 8. Which of the following is correct ?
I. Two phase locking is an optimistic protocol.
II. Two phase locking is pessimistic protocol
III. Time stamping is an optimistic protocol.
IV. Time stamping is pessimistic protocol.
A) I and III
B) II and IV
C) I and IV
D) II and III
Ans. (D)
SOLUTION: Optimistic Vs. Pessimistic approach: The optimistic concurrency control approach doesn't actually lock anything. It is based on the assumption that conflicts of database operations are very less. Means, when when oner transaction is executing, other transactions will not access the same data item being accessed by the executing one. It lets transactions run to completion and only checks for conflicts when they are about to commit. Thus, a transaction is executed without any restrictions until it is committed.
The pessimistic approach believes that some other transaction might try to access the same piece of data. So, in order to prevent any conflict, a transaction will first acquire all the required locks, then perform all the operations. It has two phases:
1. Growing Phase, where a transaction must first acquire all the locks.
2. Shrinking Phase, where a transaction releases all the locks one-by-one.(It cannot issue lock requests here.)
No comments:
Post a Comment