DBMS mcq question with answer

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

58. Which of the following is used to denote the selection operation in relational algebra? (A) Pi (Greek) (B )Sigma (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:

(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:

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

(A) Data definition language

(B) Data manipulation language

(D) Data Dictionary

65. Which of the following is not the data model

(A) ER Model

(B) Relational Model

(D) Dataflow diagram

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

(A) Number of tuples:

(B) Number of attributes:

(D) Number of constraints:

67. in a Hierarchical model records are organized as

(A) Graph: (B) List:

68. Architecture of the database can be viewed as

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

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:

(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 :

(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))

(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

(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

(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.

(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.

(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

(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

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

Monday, November 16, 2020

DBMS mcq question with answer

No comments:

Labels

Search any question

Learn This language

Social media subscribe

Most Popular