1.     Retrieve and print the data of an employee having name=’Rishi' using Record.

2.     Retrieve and print the data of all the employees using Explicit Cursors.

3.     Write a PL/SQL code block to display the names and GPA of students from student table using an explicit cursor.

4.    Write a PL/SQL code block that displays the names, GPA of students along with the grades of students after calculation from student table using an explicit cursor.

Add a column grade to the student table; update the grades of students to the table after calculation. (The criteria of grade can be considered as grade = A if gpa>3.7; and grade = B, otherwise).

1.     Write a PL/SQL code block for calculating addition of Three numbers.

3.     Write a PL/SQL code block to find largerst of three numbers.

 

4.     Write a PL/SQL code block to print table of a given number.

5.     Write a PL/SQL code block to compute the factorial of a number.

6.     Write a PL/SQL code block to determine whether the number is prime or not.

7.     Write a PL/SQL code block to display n terms of a fibonacci series.

Q2. Insert DoB for each Student in corresponding table using above instance of Student table. 

Q3. Find average of GPA round off to 2 decimal places. 

Q4. Find year of DoB of Student having less than 1000. 

Q6. Display name of all Students in uppercase and name of college they applied in lower case. 

Q7. Find fourth alphabet of each student. (Hint: use substring)

 Q8. Find sID and sName of student whose sName has string length greater than 3. 

Q9. Find floor, ceiling and truncate (to one decimal place) value of average GPA. 

Q10. Display details of all students whose sID is even. 

Q11. Compute Square Root of 900 and 247 . 

Q12. Consider the string “Peter Piper picked a peck of pickled peppers. A peck of pickled peppers Peter Piper picked. If Peter Piper picked a peck of pickled peppers, Where the peck of pickled peppers Peter Piper picked?” Find 6th occurrence of string „pick‟. (Hint: use INSTR) 

Q13. Consider String „Satya Nadella‟ replace this using the key (Hint: use translate) a d e l N S t y 1 2 3 4 5 6 7 8 

Q14. Display sID, sname and DoB in this format „February 26, 2014‟ 

Q15. Convert the text ‟26/02/2014‟ to date. 

Q16. Compute on which date is next Saturday and last day of this month?

For Download - click Here

1: Which of the following statement is true in case of “dropping a table”? 

A: Table structure and data is deleted C: Drops the indexes 

B: Invalidates the dependent objects D: All the above 

2: There are two relations GATE_2014(exam_date, exam_center, branch_id) and Candidate (rollno, name, bid, refno, choice_of_date). In the Candidate relation, bid is foreign key which refers to the key  of GATE_2014. Suppose an insertion into the Candidate relation and deletion from GATE_2014 relation is done, then which if the following is true?  

A: Insertion into the Candidate relation can cause inconsistency  

B: Deletion from the GATE_2014 relation can cause inconsistency 

C: Both operations can cause inconsistency 

D: None of them causes inconsistency 

3: Different values for the same data item is referred to as ............... 

A: data consistency B: data inconsistency 

C: data integrity D: data duplication 

4: How to ensure no duplication of data for a column of a relation while creating it? A: Alter B: Not NULL C: Distinct D: Unique 

5: Consider the following relation schema: 

Student (ID, NAME, DEPT) is a student relation: 

What is the output of the following SQL query? 

Select * from Student where name like ' a_ e_ _ _%' 

A: Displays all the students whose name has a as the last alphabet and e as the third last alphabet with  atleast 5 alphabets. 

B: Displays all the students whose name has a as the first alphabet and e as the third alphabet with  atleast 6 alphabets. 

C: Displays all the students whose name has a as the last alphabet and e as the third last alphabet with  atleast 6 alphabets. 

D: Displays all the students whose name has a as the first alphabet and e as the third last alphabet with  atleast 7 alphabets. 

6: What does the TRUNCATE command do? 

A: Removes all the data from the table C: It can be roll backed B: It deletes the complete structure from the database D: All of these 

7: Which is a special type of integrity constraint that relates two relations & maintains consistency across the relations: 

A: Entity Integrity Constraints C: Key Constraints B: Referential Integrity Constraints D: Domain Constraints 

8: The ::::::::::::::::::::: is essentially used to search for a specific data in a record: A: Like B: Null C: In D: Equal to (=)  

9: Which can be used to delete all the rows if a table? 

A: Delete * from table_name C: Delete table_name 

B: Delete from table_name D: all rows cannot be deleted at a time 

10: How to use the cascade operation in deletion of the primary keys referenced by the foreign keys? A: ON CASCADE B: SET CASCADE C: ON DELETE CASCADE D: ON CASCADE  DELETE 

11: Consider attributes ID , CITY and NAME : Which one of this can be considered as a super key ? A: NAME B: ID C: CITY D: CITY, ID

12: Given the relations employee (name, salary, deptno)and department (deptno, deptname,  address):Which of the following queries cannot be expressed using the basic relational algebra  operations (U, -, x, π, σ, p)? (GATE CS 2000) 

A: Department address of every employee C: The sum of all employees’ salaries B: Employees whose name is the same as their dept name D: All employees of a given department 

13: Consider Students (rollno, name, address ) Enroll (rollno, courseno, coursename) where primary  keys are shown in italics: The number of tuples in the student and Enroll tables are 120 and 8  respectively: What are the maximum and minimum number of tuples that can be present in (Student  *Enroll) , where * denotes natural join ?[GATE 2004] 

A: 8,8 B: 120,8 C: 960,8 D: 960,120 

14: A file manipulation command that extracts some of the records from a file is called A: SELECT B: PROJECT C: JOIN D: PRODUCT 

15: Data independence means 

A: data is defined separately and not included in programs: C: programs are not dependent  on the logical attributes of data 

B: programs are not dependent on the physical attributes of data D: both B and C 

16: Grant and revoke are ::::::: statements: 

A: DDL B: TCL C: DCL D: DML 

17: State true or false :- i) A candidate key is a minimal super key: ii) A candidate key  can also refer to as surrogate key: 

A: i-true, ii-false B) i-false, ii-true C) i-true, ii-true D) i false, ii-false 

18: ………………… is preferred method for enforcing data integrity 

A: Constraints B: Stored Procedure C: Triggers D: Cursor 

19: The language that requires a user to specify the data to be retrieved with specifying exactly how to  get it is 

A: Procedural DML B: Non-Procedural DML C: Procedural DDL D: Non-Procedural DDL 

20: The relational model feature is that there 

A: is no need for primary key data C: are explicit relationships among records B: is much more data independence than some other database models D: are tables with many  dimensions 

21: Some of the utilities of DBMS are ::::::::::::: 

i) Loading ii) Backup iii) File organization iv) Process Organization A) i, ii, and iv only B) i, ii and iii only 

C) ii, iii and iv only D) All i, ii, iii, and iv 

22: State true or false: i) Select operator is not a unary operator: ii) Project operator chooses subset of  attributes or columns of a relation: 

A: i-True, ii-False B: i-True, ii-True C: i-False, ii-True D: i-False, ii False 

23: It is an abstraction through which relationships are treated as higher level entities: A: Generalization B: Specialization 

C: Aggregation D: Inheritance

24: Which of the syntax is correct for insert statement? 

i) insert into <table_name> values <list of values> 

ii) insert into <table_name> (column list) values <list of values> 

A: i-only B: ii-only C: Both of them D: None  of them 

25: Which if the following is not the type of data integrity: 

A: Key integrity B: Domain integrity C: Entity integrity D: Referential  integrity 

26: A table as shown below: 

GATE ROLL  NO 

MARKS 

22456 65 

22457 48 

22458 58 

22459 NULL 

The following sequence of SQL statements was successfully executed on table GATE14CSE: 

UPDATE GATE14CSE set MARKS = MARKS + 5; 

SELECT avg(MARKS) from GATE14CSE; 

What is the output of the select statement? 

A: 47:75 B: 46:50 C: 62 D: None of these: 

26: The ::::::::::::::::::::: is essentially used to search for patterns in target string: A: Like B: Null C: In D: Equal to (=)  

27: The relation book(title,price) contains the titles and prices of different books: Assuming that no two  books have the same price, what does the following sql query list ?[GATE 2005] Select title from book as B where (select count(*) from book as T where T:price>B:price)<5 A: titles of the 5 most expensive books C: title of the 5th most inexpensive  books  

B: titles of the 4 most expensive books D: title of the 5th most expensive  books  

28: The language that requires a user to specify the data to be retrieved without specifying exactly how  to get it is 

A: Procedural DML B: Non-Procedural DML C: Procedural DDL D: Non-Procedural DDL 

29: Which one of the following statements is false? 

A: The data dictionary is normally maintained by the database administrator 

B: Data elements in the database can be modified by changing the data dictionary: C: The data dictionary contains the name and description of each data element: D: The data dictionary is a tool used exclusively by the database administrator: 

30: For like predicate which of the following is true: 

i) % matches zero of more characters: ii) _ matches exactly one character: A: i-only B: ii-only C: Both of them D: None of them

31. Specialization is denoted by ________ in ER diagram a. Triangle labeled IS A 

b. Rectangle labeled IS A 

32. Match the following 

c. Rectangle Labeled HAS A d. Triangle labeled HAS A 

A)Composite attribute ------ i) Attribute whose value is calculated from other attributes B)Multivalued Attribute ---- ii)Attribute that can be further subdivided to yield additional  attributes 

C)Derived Attribute -------- iii) Attribute that can have more values 

a. A-i ,B-ii, C-iii 

b. A-ii ,B-i, C-iii, 

33. Buffer manager is responsible for 

c. A-ii ,B-iii, C-i, d. A-iii ,B-ii, C-i, 

a. fetching data from disk storage into main memory, and deciding what data to cache in  main memory: 

b. allocation of space on disk storage and the data structures used to represent information  stored on disk 

c. storing details such as how many users, which user has which permissions etc d. None of the above 

34. _______stores metadata about the structure of the database, in particular the schema of the  database 

a. Indices 

b. Data Dictionary 

35. Degree of relationships is 

a. number of participating entity  types 

b. number of participating relations 

c. Data files 

d. Database log 

c. number of participating attribute  types 

d. number of participating  constraints 

36. Which of the following language is used to define the integrity constraints 

a. DCL b. DML 

c. DDL 

d. All of the above 

37. Generalization is a bottom up design process that combines a number of entity sets, which  share the same features into a higher level entity set 

a. True 

b. False 

38. Out of the following which is Open Source DBMS a. MySQL 

b. Microsoft SQL Server 

c. Can’t say 

d. None of these 

c. Microsoft Access d. Oracle 

39. An attribute or combination of attributes in one table whose values must either match the  primary key in another table or be null is called __________ 

a. Secondary Key b. Super key 

c. Candidate Key d. Foreign Key 

40. Each table in a database can have _______ primary key 

a. Only one 

b. At least one 

41. Database is collection of ____: a. Programs 

b. Data 

c. More than one d. Any number of 

c. None of these d. Modules 

42. __________________ is collection of interrelated data and set of program to access them: 

a. Programming language 

b. Data Structure 

43. DBMS should provide following feature(s) ______: a. Protect data from system crash 

b. Authorized access 

44. Which of the following is considered as DBMS ? 

c. Database 

d. Database Management System 

c. Safety of the information stored d. All of these 

a. Access b. Oracle

c. All of these d. Foxpro 45. Before use of DBMS information was stored using __________: 

a. None of these b. Cloud Storage 

c. File Management System d. Data System 

46. Given the basic ER and relational models, which of the following is INCORRECT? a. An attribute of an entity can have more than one value 

b. An attribute of an entity can be composite 

c. In a row of a relational table, an attribute can have more than one value d. In a row of a relational table, an attribute can have exactly one value or a NULL value 47. Collection of information stored in database at particular instance of time is called as  __________: 

a. Instance of Database 

b. Data Structure 

48. Overall design of the database is called as _________: a. None of these 

b. Database Abstraction 

49. Database supports _______ types of database schema's: 

c. Database Schema d. Objects in Database 

c. Database Schema d. Database Instance 

a. 3 b. 2 c. 4 d. 1 50. Consider the following relations A, B, C: How many tuples does the result of the following  relational algebra expression contain? Assume that the schema of A U B is the same as that of  A: 

Table A 

Id Name Age ---------------- 12 Arun 60 15 Shreya 24 99 Rohit 11 

Table B 

Id Name Age ---------------- 15 Shreya 24 25 Hari 40 98 Rohit 20 99 Rohit 11 

Table C 

Id Phone Area ----------------- 10 2200 02  99 2100 01 

a. 7 b. 4 c. 5 d. 9 51. Consider the following ER diagram: 

The minimum number of tables needed to represent M, N, P, R1, R2 is 

a. 2 b. 3 c. 4 d. 5 52. Let E1 and E2 be two entities in an E/R diagram with simple single-valued attributes: R1 and  R2 are two relationships between E1 and E2, where R1 is one-to-many and R2 is many-to many: R1 and R2 do not have any attributes of their own: What is the minimum number of  tables required to represent this situation in the relational model? 

a. 2 

b. 3 

c. 4 

d. 5 

53. Ability to modify schema of database in one level without affecting the schema definition in higher  level is called as __________: 

a. Data Migration 

b. Data Abstraction

c. Data Isolation 

d. Data Independence 

54. With regard to expressive power of the formal relational query languages, which of the following  statements is true ? [GATE 2002] 

a. relational algebra is more powerful than relational calculus 

b. relational algebra has same power as relational calculus 

c. relational algebra has same power as safe relational calculus 

d. None of the above 

55. The term _______ is used to refer to a row 

(A) Attribute (B) Tuple (C) Field (D) Instance 

56. The term attribute refers to a ___________ of a table: 

(A) Record (B) Column (C) Tuple (D) Key 

57. Database __________ , which is the logical design of the database, and the database _______,which is a  snapshot of the data in the database at a given instant in time: 

(A) Instance, Schema (B) Relation, Schema  

(C) Relation, Domain (D) Schema, Instance 

58. Which of the following is used to denote the selection operation in relational algebra?  (A) Pi (Greek) (B )Sigma (Greek) 

(C) Lambda (Greek) (D) Omega (Greek)  

59. A top-to-bottom relationship among the items in a database is established by a  (A) Hierarchical schema (B) Network schema (C) Relational Schema (D) All of the  above 

60. DML is provided for 

(A) Description of logical structure of database: 

(B) Addition of new structures in the database system: 

(C) Manipulation & processing of database: 

(D) Definition of physical structure of database system: 

61. The database schema is written in 

(A) HLL (B) DML (C) DDL (D) DCL 

62. An entity set that does not have sufficient attributes to form a primary key is a (A) Strong entity set: (B) Weak entity set: (C) simple entity set: (D) primary entity set: 63. in an E-R diagram attributes are represented by 

(A) rectangle: (B) Square: 

(C) Ellipse: (D) Triangle: 

64. Which of the following is not the database language? 

(A) Data definition language  

(B) Data manipulation language 

(C) Data Control Language 

(D) Data Dictionary  

65. Which of the following is not the data model 

(A) ER Model 

(B) Relational Model 

(C) Object oriented model 

(D) Dataflow diagram 

66. In the relational modes, cardinality is termed as:  

(A) Number of tuples: 

(B) Number of attributes: 

(C) Number of tables: 

(D) Number of constraints: 

67. in a Hierarchical model records are organized as  

(A) Graph: (B) List: 

(C) Links: (D) Tree 

68. Architecture of the database can be viewed as  

(A) Two levels: (B) Four levels: 

(C) Three levels: (D) One level: 

69. Relational calculus is a 

(A) Procedural language 

(B)Non- Procedural language 

(C)Data definition language

(D)High level language 

70. 16: Cartesian product in relational algebra is 

(A) Unary operator (B) Binary operator (C) Ternary operator (D)not defined 

71. DML is provided for 

(A)Description of logical structure of database: 

(B)Addition of new structures in the database system: 

(C) Manipulation & processing of database: 

(D)Definition of physical structure of database system: 

72. In a relational model, relations are termed as 

(A) Tuples: (B)Attributes (C) Tables (D) Rows: 

73. In case of entity integrity, the primary key may be 

(A) not Null (B)Null (C) both Null & not Null (D)any value: 74. "The way a particular application views the data from the database that the application uses is a" (A) Module (B) Relational model: (C) Schema (D) Sub Schema 75. Count function in SQL returns the number of 

(A) Values (B) Distinct values (C) Groups (D) Columns: 76. Relational Algebra is 

(A)Meta Language 

(B)Data Definition Language : 

(C) Procedural query Language 

(D)None of the above 

77. Key to represent relationship between tables is called 

(A) Primary key (B) Secondary Key (C) Foreign Key (D) None of these 

78. Which of the following are the properties of entities? 

(A) Groups (B) Table (C) Attributes (D) Switchboards 

79. In an E-R diagram an entity set is represent by a 

(A) Rectangle: (B) Ellipse: (C) Diamond box: (D) Circle: 

80. The statement in SQL which allows to change the definition of a table is 

(A) Alter: (B) Update: (C) Create: (D) Select: 

81. Which of the following operation is used if we are interested in only certain columns of a table? (A) PROJECTION (B) SELECTION (C) UNION (D) JOIN 82. Which of the following is a valid SQL type? 

(A) CHARACTER (B) NUMERIC (C) FLOAT (D) All of the above 

83. The RDBMS terminology for a row is 

(A) tuple: (B) relation: (C) attribute ( D) degree: 84. The users who use easy-to-use menu are called 

(A) Sophisticated end users: (B) Naïve users: (C)Stand-alone users: (D) Casual end users 85. Which are the two ways in which entities can participate in a relationship? 

(A) Passive and active (B) Total and partial (C) Simple and Complex (D) All of the above 86. Which of the following is a comparison operator in SQL? 

(A) = (B) LIKE (C) BETWEEN (D) All of the above 87. Which of the following is record based logical model? 

(A) Network Model (C) Object oriented model 

(B) E-R Model (D) None of these 

88. Using Relational Algebra the query that finds customers, who have a balance of over 1000 is (A) PCustomer_name(s balance >1000(Deposit)) 

(B) s Customer_name(Pbalance>1000(Deposit)) 

(C) PCustomer_name(s balance >1000(Borrow)) 

(D) s Customer_name(Pbalance>1000(Borrow)) 

89. An instance of relational schema R (A, B, C) has distinct values of A including NULL values: Which  one of the following is true? 

(A) A is a candidate key 

(B) A is not a candidate key 

(C) A is a primary Key  

(D) Both (A) and (C) 

90. 

1. A ..................... specifies the actions needed to remove the drawbacks in the current design of  database. 

A) 1 NF 

B) 2 NF 

C) 3 NF 

D) Normal form 

2. Fifth Normal form is concerned with 

A) Functional dependency 

B) Multivalued dependency 

C) Join dependency 

D) Domain key 

3. In 2NF 

A) No functional dependencies exist. 

B) No multivalued dependencies exist. 

C) No partial functional dependencies exist 

D) No partial multivalued dependencies exist 

4. The ................. is related to the concept of multi-valued dependency. 

A) fourth normal form 

B) fifth normal form 

C) boyce codd normal form 

D) third normal form 

5. A table is in the ....................... if only candidate keys are the determinants. 

A) functional dependency 

B) transitive dependency 

C) 4 NF 

D) BCNF 

6. In a given relationship R, if an attribute A uniquely defines all other attributes, then the attribute A  is a key attribute which is also known as the .......... key.  

A) candidate 

B) join 

C) functional 

D) None of above 

7. Empdt1(empcode, name, street, city, state,pincode).For any pincode, there is only one city and state.  Also, for given street, city and state, there is just one pincode. In normalization terms, empdt1 is a  relation in 

a) 1 NF only 

b) 2 NF and hence also in 1 NF 

c) 3NF and hence also in 2NF and 1NF 

d) BCNF and hence also in 3NF, 2NF and 1NF 

8. Missing data in a field:  

A) is a null value 

 B) is illegal  

 C) will cause a warning  

 D) will cause an error 

9. Every time attribute A appears, it is matched with the same value of attribute B, but not the same  value of attribute C. Therefore, it is true that: 

A) A→B 

B) A→C 

C) A→(B,C) 

D) (BC)→A 

10.A functional dependency is a relationship between or among 

A) Table 

B) Row 

C) Relation  

D) Attribute

11. The relation employee (ID,name, street, Credit, street, city, salary) is decomposed into employee1  (ID, name) employee2 (name, street, city, salary) This type of decomposition is called a) Lossless decomposition 

b) Lossless-join decomposition 

c) Both a and b 

d) None of the mentioned 

12. There are two functional dependencies with the same set of attributes on the left side of the arrow: A->BC 

A->B 

This can be combined as 

a) A->BC 

b) A->B 

c) B->C 

d) None of the mentioned 

13. A -> B, B -> C 

BC -> A, A -> D 

E -> A, D -> E 

Which of the following is not a key? 

a) A 

b) E 

c) C 

d) D 

14. A relation empdt1 is defined with attributes empdt1(empcode, name, street, city, state,pincode).  For any pincode, there is only one city and state. Also, for given street, city and state, there is just one  pincode. In normalization terms, empdt1 is a relation in 

a) 1NF only 

b) 2NF and Hence also in 1NF 

c) 3NF and Hence also in 2NF and 1NF 

d) BCNF and hence also in 3NF , 2NF and 1NF 

15. A table has fields F1, F2 , F3 , F4, F5 with the following functional dependencies Fi —> F3, F2 —> F4, (FI,F2) —> F5 In terms of normalization, this table is in a) 1NF 

b) 2NF 

c) 3NF 

d) None of these 

16. In a schema with attributes A, B, C, D and E following set of functional dependencies are given A --> B, A -->C, CD —>E, B --> D, E —> A 

Which of the following functional dependencies is not the implied by the above set. a) CD→AC 

b) BD→CD 

c) BC→ CD 

d) AC→BC 

17. Let R(A, B, C, D, E, P, G) be a relational schema in which the following functional dependencies  are known to hold: 

AB —> CD, DE —> P, C --> E, P --> C and B —> G .The relational schema R is a) In BCNF 

b) In 3NF, But not in BCNF 

c) In 2NF, But not in 3NF 

d) Not in 2NF 

18. A functional dependency of the form  

x → y is trivial if 

a) y ⊆ x 

b) y ⊂ x 

c) x ⊂ y 

d) x ⊂y and y ⊂ x 

19. The employee salary should not be greater than Rs. 2000. This is 

A) Integrity constraint

B) Referential constraint 

C) Over-defined constraint 

D) Feasible constraint 

20. The SQL statement SELECT ROUND (45.926, -1) FROM DUAL;  

a) Is Illegal 

b) Prints Garbage 

c) Prints 045.926 

d) Prints 50 

21. Which of the following must be enclosed in double quotes?  

a) Dates 

b) Column Alias 

c) Stings  

d) All the above 

22. Let the statement SELECT column1 FROM myTable; return 10 rows. The statement SELECT  ALL column1 FROM myTable; will return 

a) Less than 10 rows 

b) More than 10 rows 

c) Exactly 10 rows 

d) None of these 

23. What is an SQL virtual table that is constructed from other tables?  

a) View 

b) A relation 

c) Just Another table 

d) Query Result 

24. The SQL ALTER statement can be used to:  

a) Change the table data 

b) Change the table structure 

c) Delete rows from the table 

d) Add rows from the table 

25. When three or more AND and OR conditions are combined, it is easier to use the SQL keyword(s):  a) NOT IN Only 

b) LIKE Only 

c) IN Only 

d) Both IN and NOT IN 

1. Relation R(A,B,C) 

• currently has only the tuple (0,0,0), and 

• it must always satisfy the functional dependencies 

• A → B and B → C. 

Identify from the list below the tuple that may be inserted into R legally. 

A. (0,0,1) B. (0,1,2) C. (1,0,0) D. All are legal E.  None is legal 

2. Consider the relation R(ABCDE) with FD's: 

D → C; C, E → A; D → A; A, E → D. 

Which of the following is a key. 

A. ABDE B. BCE C. CDE D. All of these are keys E. None of these are keys 3. Let R(ABCD) be a relation with functional dependencies 

A → B; C → D; A, D → C; B, C → A 

Which of the following is a lossless-join decomposition of R? 

A. {ABC, BD} B. {AB, CD} C. {AC, CD} D. {ABC, CD} E. None are 4. Let R(ABCD) be a relation with functional dependencies 

A → B; C → D; A, D → C; B, C → A 

Decompose into BCNF. Which of the following is a decomposition of R into BCNF? A. {AB, AC, BD} B. {AB, AC, CD} C. {AB, AC, BCD} D. All are E. None are 

5. Consider the relation R(ABCD) with the functional dependencies: 

A,C,D → B; A,C → D; D → C; A,C → B 

Which of the following is true: 

A. R is in neither BCNF nor 3NF B. R is in BCNF but not 3NF

C. R is in 3NF but not in BCNF D. R is in both BCNF and 3NF 6. Let R(A,B,C,D,E,P,G) be a relational schema in which the following FDs are known to hold:  AB->CD, DE->P, C->E, P->C, B->G  

The relation schema R is  

(a) in BCNF (b) in 3NF, but not in BCNF (c) in 2NF, but not in 3NF (d)  not in 2NF  

 7. Consider the following relational schemes for a library database:  

Book (Title, Author, Catalog_no, Publisher, Year, Price)  

Collection (Title, Author, Catalog_no)  

With the following functional dependencies:  

I. Title,Author -> Catalog_no  

II. Catalog_no -> Title,Author,Publisher,Year  

III. Publisher,Title,Year -> Price  

Assume {Author, Title} is the key for both schemes. Which of the following statements is true?  (a) Both Book and Collection are in BCNF (b) Both Book and Collection are in 3NF only  (c) Book is in 2NF and Collection is in 3NF (d) Both Book and Collection are in 2NF only Statement for Linked Answer Questions 8 and 9:  

Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F={CH→G, A→BC, B→CFH, E→A, F→EG} is a set of functional dependencies (FDs) so that F+ is exactly  the set of FDs that hold for R. 

8. How many candidate keys does the relation R have? 

(A) 3 (B) 4 (C) 5 (D) 6 

9. The relation R is 

(A) in 1NF, but not in 2NF. (B) in 2NF, but not in 3NF. (C) in 3NF, but not in BCNF.  (D) in BCNF. 

10. The following functional dependencies are given: 

AB → CD, AF → D, DE → F, C → G , F → E, G → A 

Which one of the following options is false? 

(A)CF+ = {ACDEFG}(B)BG+ = {ABCDG} (C)AF+ = {ACDEFG} (D)AB+ = {ABCDFG} 11. Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional dependencies hold: {A–>B, BC–>D, E–>C, D–>A}. What are the candidate keys of R? 

(a) AE, BE (b) AE, BE, DE (c) AEH, BEH, BCH (d) AEH, BEH, DEH 12. Given the following two statements: 

S1: Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF. 

S2: AB→C, D→E, E→C is a minimal cover for the set of functional dependencies AB→C, D→E, AB→E,  E→C 

Which one of the following is CORRECT? 

(A) S1 is TRUE and S2 is FALSE. (B) Both S1 and S2 are TRUE. (C) S1 is FALSE and S2 is TRUE. (D) Both S1 and S2 are FALSE. 

13. A prime attribute of a relation scheme R is an attribute that appears  

(A) in all candidate keys of R. (B) in some candidate key of R. (C) in a foreign key of R(D) only in the  primary key ofR. 

14. Given the following relation instance. 

x y z 

1 4 2 

1 5 3 

1 6 3 

3 2 2 

Which of the following functional dependencies are satisfied by the instance? 

(a) XY -> Z and Z -> Y (b) YZ -> X and Y -> Z (c) YZ -> X and X -> Z (d) XZ -> Y and Y -> X 15. Which of the following is TRUE?  

(A) Every relation is 3NF is also in BCNF  

(B) A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R  (C) Every relation in BCNF is also in 3NF  

(D) No relation can be in both BCNF and 3NF 

16. Consider a schema R(A, B, C, D) and functional dependencies A -> B and C -> D. Then the decomposition of R into R1 (A, B) and R2(C, D) is (GATE CS 2001) 

a) dependency preserving and loss less join b) loss less join but not dependency preserving c) dependency preserving but not loss less join d) not dependency preserving and not loss less join 17. Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema? 

(A) The schema is in BCNF (B) The schema is in 3NF but not in BCNF (C) The schema is in 2NF but not in 3NF (D) The schema is not in 2NF 18. 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 

19. Given the following statements: 

S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL. S2: Given the table R(a,b,c) where a and b together form the primary key, the following is a valid table  definition. 

CREATE TABLE S ( a INTEGER, d INTEGER, e INTEGER, PRIMARY KEY (d),  FOREIGN KEY (a) references R) 

Which one of the following statements is CORRECT? 

(A) S1 is TRUE and S2 is FALSE. (B) Both S1 and S2 are TRUE. (C) S1 is FALSE and S2 is TRUE. (D) Both S1 and S2 are FALSE. 20. Consider the following relations:  

Consider the following SQL query.  

SELECT S.Student_Name, sum(P.Marks) FROM Student S, Performance P WHERE S.Roll_No = P.Roll_No  GROUP BY S.Student_Name 

The number of rows that will be returned by the SQL query is _____________.  

A) 2 B) 3 C) 6 D) None of these 

21. Given the following schema: 

Employees (emp-id, first-name, last-name, hire-date, dept-id, salary) 

Departments (dept-id, dept-name, manager-id, location-id) 

You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query: 

SQL>SELECT last-name, hire-date 

FROM employees 

WHERE (dept-id, hire-date) IN 

(SELECT dept-id, MAX(hire-date) 

FROM employees JOIN departments USING(dept-id) 

WHERE location-id = 1700 

GROUP BY dept-id); 

What is the outcome? 

(A) It executes but does not give the correct result. (B) It executes and gives the correct  result. 

(C) It generates an error because of pairwise comparison. 

(D) It generates an error because the GROUP BY clause cannot be used with table joins in a subquery. 22. SQL allows duplicate tuples in relations, and correspondingly defines the multiplicity of tuples inthe  result of joins. Which one of the following queries always gives the same answer as the nested query shown  below:  

select * from R where a in (select S.a from S)

(A) select R.* from R, S where R.a=S.a (B) select distinct R.* from R,S where  R.a=S.a 

(C) select R.* from R,(select distinct a from S) as S1 where R.a=S1.a (D) select R.* from R,S where  R.a=S.a and is unique R 

23. Consider the following relational schema:  

employee(empId, empName, empDept) 

customer(custId, custName, salesRepId, rating) 

salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a  sale to at least one customer. What does the following query return? 

SELECT empName FROM employee E WHERE NOT EXISTS (SELECT custId FROM customer C WHERE C.salesRepId = E.empId AND C.rating <> ’GOOD’); 

(A) Names of all the employees with at least one of their customers having a ‘GOOD’ rating. (B) Names of all the employees with at most one of their customers having a ‘GOOD’ rating. (C) Names of all the employees with none of their customers having a ‘GOOD’ rating. (D) Names of all the employees with all their customers having a ‘GOOD’ rating. 

24. The following table has two attributes A and C where A is the primary key and C is the foreign key referencing A with on-delete cascade. 

A C 

----- 

2 4 

3 4 

4 3 

5 2 

7 2 

9 5 

6 4 

The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (2,4) is deleted is: 

(a) (3,4) and (6,4) (b) (5,2) and (7,2) (c) (5,2), (7,2) and (9,5) (d) (3,4), (4,3) and  (6,4) 

25.Given relations r(w, x) and s(y, z), the result of 

select distinct w, x from r, s 

is guaranteed to be same as r, provided 

(A) r has no duplicates and s is non-empty (B) r and s have no duplicates (C) s has no duplicates and r is non-empty (D) r and s have the same number of tuples 26. Which of the following statements are TRUE about an SQL query?  

P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause  Q : An SQL query can contain a HAVING clause only if it has GROUP BY clause  R : All attributes used in the GROUP BY clause must appear in the SELECT clause  S : Not all attributes used in the GROUP BY clause need to appear in the SELECT clause  (A) P and R (B) P and S (C) Q and R (D) Q and S  27. Consider the following relations A, B and C:  

Table A 

Id Name Age 

12 Arun 60 

15 Shreya 24 

99 Rohit 11 

Table B 

Id Name Age 

15 Shreya 24 

25 Hari 40 

98 Rohit 20 

99 Rohit 11 

Table C 

Id Phone Area 

10 2200 02  

99 2100 01

How many tuples does the result of the following SQL query contain?  

SELECT A.Id FROM A  

WHERE A.Age > ALL(SELECT B.Age  

FROM B WHERE B.Name = ‘Arun’)  

(a) 4 (B) 3 (C) 0 (D) 1  

28. Database table by name Loan_Records is given below. 

Borrower Bank_Manager Loan_Amount 

Ramesh Sunderajan 10000.00 

Suresh Ramgopal 5000.00 

Mahesh Sunderajan 7000.00 

What is the output of the following SQL query? 

SELECT Count(*)  

FROM ( (SELECT Borrower, Bank_Manager FROM Loan_Records) AS S  

NATURAL JOIN  

(SELECT Bank_Manager, Loan_Amount FROM Loan_Records) AS T );  

(A) 3 (B) 9 (C) 5 (D) 6 

29. Consider a database table T containing two columns X and Y each of type integer. After the creation of the  table, one record (X= 1, Y= l) is inserted in the table.  

Let MX and MY denote the respective maximum values of X and Y among all records in the table at any point  in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1,  2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change.  What will be the output of the following SQL query after the steps mentioned above are carried out?  SELECT Y FROM T WHERE X=7;  

(A) 127 (B) 255 (C) 129 (D) 257  30. A relational schema for a train reservation database is given below. 

Passenger (pid, pname, age) 

Reservation (pid, class, tid) 

Table: Passenger 

pid pname age 

----------------- 

0 Sachin 65 

1 Rahul 66 

2 Sourav 67 

3 Anil 69 

Table : Reservation 

pid class tid 

--------------- 

0 AC 8200 

1 AC 8201 

2 SC 8201 

5 AC 8203 

1 SC 8204 

3 AC 8202 

What pids are returned by the following SQL query for the above instance of the tables? 

SELECT pid FROM Reservation, Passenger 

WHERE class ‘AC’ AND 

 EXISTS (SELECT * FROM Passenger 

 WHERE age > 65 AND Passenger. pid = Reservation.pid) 

(A) 1, 0 (B) 1, 2 (C) 1, 3 (D) 1, 5 

31. Let r be a relation instance with schema R = (A, B, C, D). We define r1 = ‘select A,B,C from r’ and r2 = ‘select A, D from r’. Let s = r1 * r2 where * denotes natural join. Given that the decomposition of r into r1 and r2 is lossy, which one of the following is TRUE? (a) s is subset of r (b) r U s = r (c) r is a subset of s (d) r * s = s 32. Consider the following relational schema:

Suppliers(sid:integer, sname:string, city:string, street:string) 

Parts(pid:integer, pname:string, color:string) 

Catalog(sid:integer, pid:integer, cost:real) 

Consider the following relational query on the above database: 

SELECT S.sname FROM Suppliers S 

 WHERE S.sid NOT IN (SELECT C.sid FROM Catalog C 

 WHERE C.pid NOT IN (SELECT P.pid  

 FROM Parts P WHERE P.color<> 'blue')) 

Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query? 

(A) Find the names of all suppliers who have supplied a non-blue part. 

(B) Find the names of all suppliers who have not supplied a non-blue part. 

(C) Find the names of all suppliers who have supplied only blue parts. 

(D) Find the names of all suppliers who have not supplied only blue parts. 

33. The relation book (title, price) contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list? 

 select titlefrom book as B where (select count(*) from book as T where T.price > B.price) < 5 

(a) Titles of the four most expensive books (b) Title of the fifth most inexpensive book (c) Title of the fifth most expensive book (d) Titles of the five most expensive books 33. Consider the table employee(empId, name, department, salary) and the two queries Q1 ,Q2 below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary than anyone in the department 5, which one of the statements is TRUE for any arbitrary employee table? 

Q1 : Select e.empId From employee e 

 Where not exists 

 (Select * From employee s where s.department = “5” and s.salary >=e.salary) Q2 : Select e.empId From employee e 

 Where e.salary > Any (Select distinct salary From employee s Where s.department = “5”) 

(A) Q1 is the correct query (B) Q2 is the correct query (C) Both Q1 and Q2 produce the same answer. (D) Neither Q1 nor Q2 is the correct query 

MCQ: If every functional dependency in set E is also in closure of F then this is classified as 

A. FD is covered by E 

B. E is covered by F 

C. F is covered by E 

D. Fplus is covered by E 

Answer B 

MCQ: If there is more than one key for relation schema in DBMS then each key in relation schema is  classified as 

A. prime key 

B. super key 

C. candidate key 

D. primary key 

Answer C

MCQ: Form of dependency in which set of attributes that are neither a subset of any of keys nor  candidate key is classified as 

A. transitive dependency 

B. full functional dependency 

C. partial dependency 

D. prime functional dependency 

Answer A 

MCQ: Property of normalization of relations which guarantees that functional dependencies are  represented in separate relations after decomposition is classified as 

A. nonadditive join property 

B. independency reservation property 

C. dependency preservation property 

D. additive join property 

Answer C 

MCQ: Rule which states that addition of same attributes to right side and left side will results in other  valid dependency is classified as 

A. referential rule 

B. inferential rule 

C. augmentation rule 

D. reflexive rule 

Answer C 

MCQ: Functional dependency of two sets E and F are considered as equivalent if 

A. E + F = E - F 

B. E* = F 2 

C. E2 = F 2 

D. E* = F * 

Answer D 

MCQ: Process of analyzing relation schemas to achieve minimal redundancy and insertion or update  anomalies is classified as 

A. normalization of data 

B. denomination of data 

C. isolation of data 

D. de-normalization of data 

Answer A 

MCQ: Considering relational database, functional dependency between two attributes A and B is  denoted by 

A. A → B 

B. B ← A 

C. AB → R 

D. R ← AB 

Answer A

MCQ: If attribute of relation schema R is member of some candidate key then this type of attributes  are classified as 

A. atomic attribute 

B. candidate attribute 

C. nonprime attribute 

D. prime attribute 

Answer D 

MCQ: Procedure of storing higher normal form relations which are in lower normal form as a base  relation is classified as 

A. isolation of data 

B. de-normalization of data 

C. normalization of data 

D. denomination of data 

Answer B 

MCQ: If attribute of relation schema R is not a member of some candidate key then this type of attribute  is classified as 

A. nonprime attribute 

B. prime attribute 

C. atomic attribute 

D. candidate attribute 

Answer A 

MCQ: In reflexive rule (IR1), true dependencies generated are classified as 

A. trivial 

B. nontrivial 

C. inferential 

D. functional 

Answer A 

MCQ: Extensions of relation that satisfy constraint of functional dependency are considered as 

A. legal extensions 

B. semantic extensions 

C. state extension 

D. relation extensions 

Answer A 

MCQ: If each tuple have relation R within it then this type of relation is classified as 

A. primary relation 

B. prime relation 

C. nested relation 

D. atomic relation 

Answer C 

MCQ: In normalization of relations, property which is critical and must be achieved is classified as 

A. nonadditive join property 

B. additive join property

C. independency reservation property 

D. dependency preservation property 

Answer A 

MCQ: In functional dependency between two sets of attributes A and B then set of attributes A of  database is classified as 

A. top right side 

B. down left side 

C. left hand side 

D. right hand side 

Answer C 

MCQ: Considering functional dependency, one in which removal of some attributes does not affect  dependency is called 

A. full functional dependency 

B. partial dependency 

C. prime functional dependency 

D. transitive dependency 

Answer B 

MCQ: Constraint between two different attributes sets is classified as 

A. modification anomaly 

B. functional dependency 

C. insertion anomaly 

D. deletion anomaly 

Answer B 

MCQ: Joining property which guarantees that spurious tuple generation problem is not created after  decomposition is called 

A. lossless join property 

B. nonadditive join property 

C. additive join property 

D. both a and b 

Answer D 

MCQ: Normalization form which is based on transitive dependency is classified as 

A. first normal form 

B. second normal form 

C. fourth normal form 

D. third normal form 

Answer D 

MCQ: Considering functional dependency, one in which removal from some attributes must affect  dependency is called 

A. full functional dependency 

B. partial dependency 

C. prime functional dependency 

D. transitive dependency 

Answer A

MCQ: Normal form which only includes indivisible values or single atomic values is classified as 

A. third normal form 

B. first normal form 

C. second normal form 

D. fourth normal form 

Answer B 

MCQ: Concept in normalization of relations which is based on full functional dependency is classified  as 

A. fourth normal form 

B. third normal form 

C. first normal form 

D. second normal form 

Answer D 

MCQ: In tuples, interpretation of values of attribute is considered as 

A. commands of relation 

B. clauses of relation 

C. schemas of relation 

D. semantics of relation 

Answer D 

MCQ: Rule which states that set of attributes determines any of its subset is classified as 

A. closure rule 

B. reflexive rule 

C. referential rule 

D. inferential rule 

Answer B 

MCQ: In functional dependency between two sets of attributes A and B then set of attributes A of  database is classified as 

A. top right side 

B. down left side 

C. left hand side 

D. right hand side 

Answer D 

MCQ: Rule that always generate true dependencies is called 

A. referential rule 

B. inferential rule 

C. closure rule 

D. reflexive rule 

Answer D 

1. Which normal form is considered adequate for normal relational database design? (a) 2NF (b) 5NF (c) 4NF (d) 3NF

2. Which of the following is TRUE? 

(a) Every relation in 2NF is also in BCNF 

(b) A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every  key of R 

(c) Every relation in BCNF is also in 3NF 

(d) No relation can be in both BCNF and 3NF 

3.The employee information in a company is stored in the relation 

Employee (name, sex, salary, deptName) 

Consider the following SQL query 

Select deptName From Employee Where sex = ‘M’ Group by deptName Having avg(salary) >  (select avg (salary) from Employee) 

It returns the names of the department in which 

(a) the average salary is more than the average salary in the company 

(b) the average salary of male employees is more than the average salary of all male employees in the  company 

(c) the average salary of male employees is more than the average salary of 

employees in the same department. 

(d) the average salary of male employees is more than the average salary in the company 

4. Assume that, in the suppliers relation above, each supplier and each street within a city has a unique  name, and (sname, city) forms a candidate key. No other functional dependencies are implied other  than those implied by primary and candidate keys. Which one of the following is TRUE about the  above schema? 

(a) The schema is in BCNF (b) The schema is in 3NF but not in BCNF (c) The schema is in 2NF but not in 3NF (d) The schema is not in 2NF 

5. Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F={CH→G,  A→BC, B→CFH, E→A, F→EG} is a set of functional dependencies (FDs) so that F + is exactly the  set of FDs that hold for R. 

How many candidate keys does the relation R have? 

(a) 3 (b) 4 (c) 5 (d) 6 6. The relation R is 

(a) in 1NF, but not in 2NF. (b) in 2NF, but not in 3NF. (c) in 3NF, but not in BCNF.  (d) in BCNF. 

7. The ................. is related to the concept of multi-valued dependency. 

A) fourth normal form B) boyce codd normal form C) fifth normal form D)  third normal form

8. A table is in BCNF if it is in 3NF and if every determinant is a ............... key. A) dependent B) normal C) candidate  D) Both B & C 

9. A table is in 3NF if it is in 2NF and if it has no ......................  

A) functional dependencies B) transitive dependencies C) trivial functional dependency D) multivalued dependencies 

10. Consider a relation with 3 attributes. How many maximum candidate keys could be atmost at the  same time? 

A. 2 B. 3 C. 4 D. 5  

11. Let F = {AB-> C, AC-> B, B->D, BC->A} is set of FD of R(A,B,C,D). How many no of functional  dependencies can you get in the minimal cover of F? 

A. 3 B. 4 C. 5 D. 6 

12. Consider a relation R(ABCD) with FD's {A-> D, B ->D, D->BC} which is decomposed into  R1(AB) and R2(BCD). Which of the following is true? 

A. Lossless join and dependency preserving decomposition B. Lossless join but not dependency  preserving decomposition 

C. Lossy join and dependency preserving decomposition D. Lossy join and not dependency  preserving decomposition 

13. Consider a relation R(ABCDE) with FD's {A->D,B->C,D->E,CE->B}. If we project R onto  schema(ABC), what is true about the keys of ABC? 

A.Only A is the key B. Only AB is the key C. Only AB,AC are keys D.  Only AB, BC, AC keys 

14. A relation R(ABCD) has FD's F = {A->C, B->D}. How many minimal no of relational tables  require lossless join and dependency preserving BCNF decomposition? 

A. 2 B. 3 C. 4 D. 5 

15. Consider a relation R(ABCDE) with FD's F = {A->BC,C->D,B->E,A->E,B->A}. Which of the  following is true regarding R? 

A. BC is superkey but not candidate key B. AC is candidate key so also superkey  C. There is only one candidate key of R D. None of these. 

16. A relation R(ABCD) with F ={AB->C,C->D,D->C,A->B} is in 

A. 1NF but not in 2NF B. 2NF but not in 3NF C. 3NF but not in BCNF D.  BCNF 

17. Consider a relation R(ABCDEFG) with F = {A->B,B->C,C->D,D->E,E->F,F->A}. Find the no of  candidate keys? 

A. 1 B. 3 C. 4 D. 6 18. Find the highest normal form of R?

A. 1NF B.2NF C. 3NF D. BCNF 

19. For which of the following set of FD's, the relation R(ABCDE) is in 3NF but not in BCNF. 

I. AB->CD, B->E,AC->BD II. A->B,BC->E,ED->A III. A->B,B->C,C->D,D->E,E->A IV. AB- >CD,AC->BED,D->A 

A. I,II,III,IV B. II,III C. II only D. II,IV 

20. Consider a relation R(ABCD) with functional dependency as A->B,AB->C,BC->D. Find the  minimal cover. 

A. A->B,B->D B. AB->C,C->D C. A->B,A->C,BC->D D. A->D 

21. Consider a relation R(ABCDE) with FD's {A->B,B->C,D->E}. Find the next higher normal form  decomposition set for R? 

A. D={R1(AB), R2(BC), R3(DE)} B. {R1(ABC),R2(CD),R3(DE)} C. D={R1(ABC), R2(CDE)} D. D={R1(AB), R2(BC), R3(DE)} 22. Given R(ABCDE) with FD's AB->C,DE->C,B->D. Indicate all 3NF violations? 

A. AB->C,DE->C B. AB->C,B->D C. AB->C,DE->C and B->D D. None of  these 

23. 14. A relation R(ABCD) has FD's F = {A->C, B->D}. Find the highest normal form? A. 1NF B.2NF C. 3NF D. BCNF  

24. Consider a relation R(ABCDE) with FD's {A->D,B->C,D->E,CE->B}. Find the highest normal form? A. 1NF B.2NF C. 3NF D. BCNF 

25. Consider relation R(ABCD) as follows: 

A B C D 

a b z 1 

e b r 1 

a d z 1 

e d r 1 

a f z 2 

e f r 2 

Find the total no of functional dependencies possible?(Ignore self dependencies such as A->A or B->B  or AB->AB) 

A.10 B.15 C. 12 D. 9