Database Systems MCQ

 Q.1 When R∩S = Ф , then the cost of computing R|><|S is

(A) the same as R × S

(B) greater the R × S

(C) less than R × S

(D) cannot say anything

Ans: (A)

 

Q.2 In SQL the word ‘natural’ can be used with

(A) inner join

(B) full outer join

(C) right outer join

(D) all of the above

Ans: (A)

 

Q.3 The default level of consistency in SQL is

(A) repeatable read

(B) read committed

(C) read uncommitted

(D) serializable

Ans: (D)

 

Q.4 If a transaction T has obtained an exclusive lock on item Q, then T can

(A) read Q

(B) write Q

(C) both read and write Q

(D) write Q but not read Q

Ans: (C)

 

Q.5 Shadow paging has

(A) no redo

(B) no undo

(C) redo but no undo

(D) neither redo nor undo

Ans: (A)

 

Q.6 If the closure of an attribute set is the entire relation then the attribute set is a

(A) superkey

(B) candidate key

(C) primary key

(D) not a key

Ans: (A)

 

Q.7 DROP is a ______________ statement in SQL.

(A) Query

(B) Embedded SQL

(C) DDL

(D) DCL

Ans: (C)

 

Q.8 If two relations R and S are joined, then the non matching tuples of both R and S are ignored in

(A) left outer join

(B) right outer join

(C) full outer join

(D) inner join

Ans: (D)

 

Q.9 The keyword to eliminate duplicate rows from the query result in SQL is

(A) DISTINCT

(B) NO DUPLICATE

(C) UNIQUE

(D) None of the above

Ans: (C)

 

Q.10 In 2NF

(A) No functional dependencies (FDs) exist.

(B) No multivalued dependencies (MVDs) exist.

(C) No partial FDs exist.

(D) No partial MVDs exist.

Ans: (C)

 

Q.11 Which one is correct statement?

Logical data independence provides following without changing application programs:

(i) Changes in access methods.

(ii) Adding new entities in database

(iii) Splitting an existing record into two or more records

(iv) Changing storage medium

(A) (i) and (ii) (B) (iv) only, (C) (i) and (iv) (D) (ii) and (iii)

Ans: (D)

 

Q.12 In an E-R, Y is the dominant entity and X is a subordinate entity. Then which of the following is incorrect :

(A) Operationally, if Y is deleted, so is X

(B) existence is dependent on Y.

(C) Operationally, if X is deleted, so is Y.

(D) Operationally, if X is deleted, & remains the same.

Ans: (C)

 

Q.13 Relational Algebra is

(A) Data Definition Language .

(B) Meta Language

(C) Procedural query Language

(D) None of the above

Ans: (C)

 

Q.14 Which of the following aggregate functions does not ignore nulls in its results?.

(A) COUNT .

(B) COUNT (*)

(C) MAX

(D) MIN

Ans: (B)

 

Q.15 R (A,B,C,D) is a relation. Which of the following does not have a lossless join dependency preserving BCNF decomposition

(A) A->B, B->CD

(B) A->B, B->C, C->D .

(C) AB->C, C->AD

(D) A->BCD

Ans: (D)

 

Q.16 Consider the join of relation R with a relation S. If R has m tuples and S has n tuples, then the maximum and minimum size of the join respectively are

(A) m+n and 0

(B) m+n and |m-n|

(C) mn and 0

(D) mn and m+n

Ans: (C)

 

Q.17 Maximum height of a B+ tree of order m with n key values is

(A) Log_m(n)

(B) (m+n)/2

(C) Log_m/2(m+n)

(D) None of these

Ans: (D)

 

Q.18 Which one is true statement :

(A) With finer degree of granularity of locking a high degree of concurrency is possible.

(B) Locking prevents non – serializable schedules.

(C) Locking cannot take place at field level.

(D) An exclusive lock on data item X is granted even if a shared lock is already held on X.

Ans: (A)

 

Q.19 Which of the following statement on the view concept in SQL is invalid?

(A) All views are not updateable

(B) The views may be referenced in an SQL statement whenever tables are referenced.

(C) The views are instantiated at the time they are referenced and not when they are defined.

(D) The definition of a view should not have GROUP BY clause in it.

Ans: (D)

 

Q.20 Which of the following concurrency control schemes is not based on the serializability property?

(A) Two – phase locking

(B) Graph-based locking

(C) Time-stamp based locking

(D) None of these

Ans: (D)

 

Q.21 Which of the following is a reason to model data?

(A) Understand each user’s perspective of data

(B) Understand the data itself irrespective of the physical representation

(C) Understand the use of data across application areas

(D) All of the above

Ans: (D)

 

Q.22 If an entity can belong to only one lower level entity then the constraint is

(A) disjoint

(B) partial

(C) overlapping

(D) single

Ans: (B)

 

Q.23 The common column is eliminated in

(A) theta join

(B) outer join

(C) natural join

(D) composed join

Ans: (C )

 

Q.24 In SQL, testing whether a subquery is empty is done using

(A) DISTINCT

(B) UNIQUE

(C) NULL

(D) EXISTS

Ans: (D)

 

Q.25 Use of UNIQUE while defining an attribute of a table in SQL means that the attribute

values are

(A) distinct values

(B) cannot have NULL

(C) both (A) & (B)

(D) same as primary key

Ans: (C)

Database System-V

1. What is View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database . The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

2. What is Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application.
A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance. Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, ea ch database table may have only one clustered index. Non-clustered indexes are created outside of the database table and contain a sorted list of references

to the table itself.

3. What is the difference between clustered and a non-clustered index?

A clustered index is a special type of index that reorders the wa y records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

4. What are the different index configurations a table can have?

A table can have one of the following index configurations:

a. No indexes

b. A clustered index

c. A clustered index and many nonclustered indexes

d. A nonclustered index

e. Many nonclustered indexes

5 .What is cursors?

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. In order to work with a cursor we need to perform some steps in the following order:

a. Declare cursor

b. Open cursor

c. Fetch row from the cursor

d. Process fetched row

e. Close cursor

f. Deallocate cursor

Database System-IV

1. What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained a cross and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS ha s the capability to recombine the data items from different files, providing powerful tools for data usage.

2. What is normalization?

Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

3. What are different normalization forms?

1NF: Eliminate Repeating Group

Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data

If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key

If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key

BCNF: Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.

4NF: Isolate Independent Multiple Relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships

There may be practical constrains on information that justify separating logically related many-to-many relationships.

ONF: Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form

A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first

fulfill all the criteria of a 2NF and 1NF database.

4. What is Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

e.g. sp_helpdb, sp_renamedb, sp_depends etc.

5. What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE)

occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table.

Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not atta ched to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

Database System-III

1. What is a "primary key"?

Ans : A PRIMARY INDEX or PRIMARY KEY is something which comes mainly from database theory. From its behavior is almost the same as an UNIQUE INDEX, i.e. there may only be one of each value in this column. If you call such an INDEX PRIMARY instead of UNIQUE, you say something about your table design, which I am not able to explain in few words. Primary Key is a type of a constraint enforcing uniqueness and data integrity for each row of a table. All columns participating in a primary key constraint must possess the NOT NULL property.

2. What is a "functional dependency"? How does it relate to database table design?

Ans : Functional dependency relates to how one object depends upon the other in the database. for example, procedure/function sp2 may be called by procedure sp1. Then we say that sp1 has functional dependency on sp2.

3. What is a "trigger"?

Ans : Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurance of one of the data-modification operations. A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert,update,delete and query statements. Basically, trigger is a set of SQL statements A trigger is a solution to the restrictions of a constraint. For instance: 1.A database column cannot carry PSEUDO columns as criteria where a trigger can. 2. A database constraint cannot refer old and new values for a row where a trigger can.

4. Why can a "group by" or "order by" clause be expensive to process?

Ans : Processing of "group by" or "order by" clause often requires creation of Temporary tables to process the results of the query. Which depending of the result set can be very expensive.

5. What is "index covering" of a query?

Ans : Index covering means that "Data can be found only using indexes, without touching the tables"

6. What is a SQL view?

Ans : An output of a query can be stored as a view. View acts like small table which meets our criterion. View is a precomplied SQL query which is used to select data from one or more tables. A view is like a table but it doesn’t physically take any space. View is a good way to present data in a particular format if you use that query quite often. View can also be used to restrict users from accessing the tables directly.

7. What are two methods of retrieving SQL?

Ans : Use SELECT Query and Projection Relational Algebra to retrieve SQL.

8. What cursor type do you use to retrieve multiple recordsets?

Ans : Explicit Cursor has been used to retrieve multiple records at a time.

9. What types of join algorithms can you have?

Ans : Natural Join, Equi Join, Inner Join, Outer Join (Left Outer Join ,Right Outer Join, Full Outer Join), Cross Join, Self Join, etc.

Database System-II

1. What is the difference between a "where" clause and a "having" clause?

Ans : "Where" is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data.Having clause is a kind of filtering command.

2. What is the basic form of a SQL statement to read data out of a table?

Ans : The basic form to read data out of table is ‘SELECT * FROM table_name; ‘ An answer: ‘SELECT * FROM table_name WHERE xyz= ‘whatever’;’ cannot be called basic form because of WHERE clause.

3. What structure can you implement for the database to speed up table reads?

Ans :  Follow the rules of DB tuning we have to: 1] properly use indexes ( different types of indexes) 2] properly locate different DB objects across different tablespaces, files and so on.3] create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB, LOB and …)

4. What are the tradeoffs with having indexes?

Ans : 1. Faster selects, slower updates. 2. Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index.

5. What is a "join"?

Ans : ‘join’ used to connect two or more tables logically with or without common field.

6. What is "normalization"?

Ans : "Denormalization"? Why do you sometimes want to denormalize? - Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing.

7. What is a "constraint"?

Ans : A constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server: PRIMARY/UNIQUE - enforces uniqueness of a particular table column. DEFAULT - specifies a default value for a column in case an insert operation does not provide one. FOREIGN KEY - validates that every value in a column exists in a column of another table. CHECK - checks that every value stored in a column is in some specified list. Each type of constraint performs a specific type of action. Default is not a constraint. NOT NULL is one more constraint which does not allow values in the specific column to be null. And also it the only constraint which is not a table level constraint.

8. What types of index data structures can you have?

Ans :  An index helps to faster search values in tables. The three most commonly used index-types are: - B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases. - Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only few space and is very fast.(however, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD) - Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.

Database System-I

1.What is foreign Key? What is its purpose?

A non key attribute, whose value are derived from the primary key of some other table, is known as foreign key in the current table.The table in which this non-key attribute i.e. foreign key attribute exists, is called a foreign table.

2.Define the terms  Tuple and  Attribute

Tuples: The rows of tables (relations) are generally referred to as tuples. Attribute: The columns of tables are generally referred to as attribute.

3.What do you understand by the terms Cardinality and Degree of the table?

Degree: The number of attributes in a relation determines the degree of a relation. A relation having 3 attributes is said to be a relation of degree 3.

Cardinality: The number of rows in a relation is known as Cardinality.

4.What is the main function of DBA.

The DBA must be a manager, more than a technician-seeking to meet the needs of people who use the data. Since many user may share the same data resource, the DBA must be prepared to meet the need and objective.

5.Write a query on the customers table whose output will exclude all customers with a rating <=100, unless they are located in Shimla.

SELECT *  FROM customers  WHERE rating >100 OR city =’Shimla’ ;

6.Write a query that selects all orders except those zeros or NULLs in  the amount field.

SELECT * FROM Orders  WHERE amt < >0  AND (amt IS NOT NULL) ;

7.Write a query that lists customers in descending order of rating. Output the rating field first, followed by the customer’s name and number.

SELECT rating, cust-name, cust-num FROM customers ORDER BY rating DESC;

8.Write a command that puts the following values, in their given order, into the salesman table: cust-name-Manisha, city-Manali, comm.- NULL, cust-num-1901.

INSERT INTO salesman (city, cust-name, comm.,cust-num)VALUES(‘Manisha’,NULL,1901) ;

9.What are DDL and DML?

The DDL provides statements for the creation and deletion of tables and indexes.

The DML provides statements to enter, update, delete data and perform complex queries on these tables.

10.What is the difference between Where and Having Clause?

The HAVING clause places the condition on group but WHERE clause places the condition on individual rows

11.What do you understand by constraints?

Constraints are used to enforce rules at table level when ever row is inserted, updated/deleted from table.

Constraints can be defined to one of the Two level.

Column Level: Reference to a single column. can be defined any type of integrity.

Table Level: References one or more columns and is defined separately from definition of the columns in the table.

12.Write some features of SQL?

Recovery ad Concurrency:- Concurrency is concerned with the manner in which multiple user operate upon the Database.

Security: The Security can be maintained by view mechanism.

Integrity Constraints-> Integrity constraints are enforced by the system.

13.Write various database objects available in SQL?

Table: A Table is used to store Data

View: A view is the temporary table created using Original table.

Sequence:   Sequences are used to generate Primary key value.

Index:  They are used to improve queries.

Synonym: They give alternative names to objects.     

14.Write the rules to name an objects?

The maximum length must be 30 character long.

The Object name  should not contain quotation mark.

The name must start with letter.

The use of $ and # is discouraged in the object name.

A name must not be a reserved name.

15.What are group Functions?

The aggregate functions are group functions. They return result based on groups of rows. The group functions are

AVG(), COUNT(), MAX(),  MIN(), SUM()

16.What are  column alias?

In many  cases heading table may not be descriptive and hence it difficult to understand. In  such case we use columns alias It will change column heading with column alias.