3.

11 Worksheets

3.11 Worksheets
Worksheet 1

Multiple Choice Questions

1. What is an example of RDBMS?
A. SQL
B. MS SQL Server
C. IBM DB2
D. All of the above
2. What is TRUE about RDBMS?
A. Representation of data is done in form of Column.
B. Every table contains its own candidate key.
C. There is a collection of organized set of tables.
D. It is uncommonly used database.
3. A small entity that contains the specific information of each record in the table is known as ...........
A. Row
B. Column
C. Field
D. Record
4. An operation is part of a transaction if it is .......... related.
A. Logically
B. Analytically
C. Reasonably
D. None
5. What is the purpose of the UNION operator in SQL?
A. It combines the results of two or more SELECT statements.
B. It performs a pattern match on a string.
C. It retrieves the maximum value in a column.
D. It filters the rows returned by the SELECT statement.
6. Which SQL command is used to update existing data in a database table?
A. MODIFY
7. By normalizing relations or sets of relations, one minimizes .
A. Data
B. Fields
C. Redundancy
D. Database
8. In addition to removing undesirable characteristics, normalization also eliminates a nomalies.
A. Insert
B. Update
C. Delete
D. All of the above
9. To access the contents of the database, u serperf ormstransactions.
A. Single
B. Two

150
 3.11 Worksheets

C. Three
D. Multiple
10. A common approach to normalization is to t helargertableintosmallertablesandlinkthemtogetherbyusingrelationships.
A. Add
B. Subtract
C. Multiply
D. Divide

Subjective Questions
1. Define SQL?
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
2. List out the Numeric Data and character Data Types in MySQL?
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
3. List out the commands under DML and their syntax.
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................

151
 3.11 Worksheets

....................................................................................................
....................................................................................................
....................................................................................................
4. Based on the given table “SALE” answer the question (i) and (ii)

PRODID QTY RATE AMOUNT

1 10 100 1000
2 5 50 250
3 10 20 200
4 20 100 2000

(i) Can we take QTY column of the above table as Primary Key? If no give reason?
(ii) Which column is best suitable for applying Primary Key?
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
5. What is referential Integrity? How it is implemented in any table?
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
6. Table T1 contains 10 Rows and 4 Columns; Table T2 contains 20 Rows and 3 Columns. After performing
Cartesian product of T1 and T2, what will be the degree and cardinality of Resultant output?
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................

152
 3.11 Worksheets

....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................

153
 3.11 Worksheets

Worksheet 2

Multiple Choice Questions

1: What For each attribute of a relation, there is a set of permitted values, called the of that attribute.
A. Dictionaries
B. Domain
C. Directory
D. Relation
2: What does NULL value specify in RDBMS?
A. The field is set to Zero
B. The field is set to Infinite
C. The field is left blank
D. The field is set to Whole Number
3: RDBMS applications stores data .
A. In tabular form
B. As file
C. Both a and b
D. None of the above
4: What is the purpose of the WHERE clause in SQL?
A. It specifies the columns to be retrieved.
B. It filters the rows returned by the SELECT statement.
C. It orders the results in ascending or descending order.
D. It creates a new table.
5: In what format data is stored in DBMS?
A. Hierarchal form
B. Navigational form
C. Both A. and B.
D. None of the above
6: Redundancy is reduced in a database table by using the f orm.
A. Abnormal
B. Normal
C. Special
D. None
7: X is read from a database and stored in a buffer in main memory with the o peration.
A. Read
B. Write
C. Commit
D. Rollback
8: In practical applications, how many types of Normal Forms are there?
A. 3
B. 4
C. 5
D. 6
9: In DBMS –
A. There is no relation between the tables

154
 3.11 Worksheets

B. There is relation between the tables

C. There is custom relation between the databases
D. There is data value relation between the databases
10: Which of the following is not a type of Normal Form?
A. 1NF
B. 2NF
C. 3NF
D. 10NF

Subjective Questions
1. Write any 2 characteristics of a Relation.
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
2. What is alternate Key?
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
3. From the following Tables: Table 3.2 (EMP) AND Table 3.3 (JOB) answer the questions

Table 3.2: Employee Table

EMPNO ENAME JOB SALARY DEPTNO
E001 PETER ADMIN 4500 10
E002 SCOTT SALESMAN 3500 20
E003 ALBERT CLERK 2800 10
E004 RUSSEL CLERK 2900 40

(a). Identify Primary Key from both the tables

155
 3.11 Worksheets

Table 3.3: Job Table

DEPTNO DNAME DLOCATION DHEAD
10 PETER ADMIN 4500
20 SCOTT SALESMAN 3500
30 ALBERT CLERK 2800
40 RUSSEL CLERK 2900

(b). Identify the foreign key column in the table EMP

(c). Can we delete the record of PETER from table JOB?
(d). If not, give a reason
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
Table 3.4: Visitor Table
VisitorID VisitorName ContactNumber
V001 ANAND 9898989898
V002 AMIT 9797979797
V003 SHYAM 9696969696
V004 MOHAN 9595959595

4. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
5. Mr. Peter created a table in MySQL. later on, he found that there should have been another column in the table.
Which command should he use to add another column to the table.
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................

156
 3.11 Worksheets

....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
6. Give 1 example for aggregate functions (count, max, min, sum).
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................

157
 3.11 Worksheets

Worksheet 3

Multiple Choice Questions

1: What is the purpose of the GROUP BY clause in SQL?
A. It filters the rows returned by the SELECT statement.
B. It groups rows with the same values into summary rows.
C. It orders the results in ascending or descending order.
D. It specifies the columns to be retrieved.
2: ......... is used to permanently save the work.
A. Read
B. Write
C. Commit
D. Rollback
3: Distributed database is supported by -
A. RDBMS
B. DBMS
C. Both RDBMS DBMS
D. Neither RDBMS nor DBMS
4: RDBMS supports .......... users.
A. One
B. Two
C. None
D. Multiple
5: An undo operation is called a .........
A. Rollback
B. Commit
C. Write
D. Read
6: What is the purpose of the DISTINCT keyword in SQL?
A. It filters the rows returned by the SELECT statement.
B. It specifies the columns to be retrieved.
C. It removes duplicate rows from the result set.
D. It orders the results in ascending or descending order.
7: How many properties of transactions are there?
A. 4
B. 5
C. 6
D. 7
8: DBMS deals with ....... amount of data.
A. Large
B. Small
C. Custom
D. None
9: What is the purpose of the UNION operator in SQL?
A. It combines the results of two or more SELECT statements.

158
 3.11 Worksheets

B. It performs a pattern match on a string.

C. It retrieves the maximum value in a column.
D. It filters the rows returned by the SELECT statement.
10: “Address” field of a table cannot be a part of Primary key as it is likely to:
A. Dependent
B. Changed
C. Too Long
D. Not Changed

Subjective Questions
1. Observe the following table and answer the questions TABLE 3.5 VISITOR and answer the question (i) , (ii)
and (iii)

Table 3.5: Visitor Table

VisitorID VisitorName ContactNumber
V001 ANAND 9898989898
V002 AMIT 9797979797
V003 SHYAM 9696969696
V004 MOHAN 9595959595

(i) Write the name of most appropriate columns which can be considered as Candidate keys
(ii) Out of selected candidate keys, which one will be the best to choose as Primary Key?
(iii) What is the degree and cardinality of the table
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
2. What is the difference between Primary Key and Candidate Key?
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................

159
 3.11 Worksheets

....................................................................................................
....................................................................................................
....................................................................................................
3. What are the differences between DELETE and DROP commands of SQL?
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
4. Write the SQL query to display price of products without duplicate values.
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
5. Write the SQL query to drop the table employee.
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
6. Write the SQL query to count the number of product with product code is PEN.
....................................................................................................
....................................................................................................
....................................................................................................

160
 3.11 Worksheets

....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................

161
 3.11 Worksheets

Worksheet 4

Multiple Choice Questions

1: There needs to be which of the following conditions for each nontrivial dependency of function X on function
Y for a relation to be in third normal form.
A. A super key is X.
B. Every element of Y is a part of some candidate key, i.e, Y is a prime attribute.
C. Either A or B
D. None of the above
2: What is TRUE about the First Normal Form (1NF)?
A. If a relation contains an atomic value, it will be 1NF.
B. A table attribute cannot contain more than one value, according to this rule.
C. A single-valued attribute can only be stored in it.
D. All of the above
3: What is TRUE about Isolation?
A. By using the data used during a transaction, the second transaction will not be able to use it until the first
has been executed.
B. The data item X cannot be accessed by any other transaction T2 until the transaction T1 is completed and
the data item X is used by the transaction T1.
C. It enforced the isolation property via its concurrency control subsystem.
D. All of the above
4: 2NF relations are those that are in 1NF with all the attribute types dependent on the ...... key.
A. Primary
B. Foreign
C. Composite
D. Alternate
5: When a relation contains an atomic value, it is a ...... relation.
A. 1NF
B. 2NF
C. 3NF
D. BCNF
6: Transactions that are ...... do not expose all changes.
A. Committed
B. Rollbacked
C. Aborted
D. None of the above
7: What is TRUE about atomicity?
A. The transaction cannot be partially completed, since there is no midway.
B. In each transaction, either the entire transaction is executed or it is not.
C. Both A and B
D. None of the above
8: ....... states that all operations of a transaction must occur simultaneously; otherwise, the transaction will be
aborted.
A. Atomicity
B. Consistency

162
 3.11 Worksheets

C. Isolation
D. Durability
9: In a database, prior to and after a transaction, properties are used to ensure .......
A. Consistency
B. Redundancy
C. Latency
D. Anonymity
10: Column names of any table must be:
A. Must be numeric type
B. Must be unique
C. Must be in sorted order
D. Must not be greater than 40 characters

Subjective Questions
1. Write the SQL query to update product code to PEC for product ID 1002.
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
2. Write short notes on following relational terms:
a. Tuple
b. Attribute
c. Relation
d. Domain
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
3. Write the SQL query to display the maximum, minimum, average of price and total quantity of all products.

163
 3.11 Worksheets

....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
4. Differentiate between DBMS and RDBMS. Discuss its different functions.
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
5. Observe the following Table 3.6 TEACHER and Table 3.7 TASK carefully and write the names of the RDBMS
operation out of (i) EQUI JOIN(ii) NATURAL JOIN(iii) SELECTION (iv)CARTESIAN PRODUCT, which
has been used to product the output as shown below. Also find the Degree and Cardinality of final RESULT.

Table 3.6: Teacher Information

Teacher_Code Teacher_Name Subject
T001 Amit Biology
T002 Anand Hindi
T003 Mohan Physics

Table 3.7: Task

Teacher Name Subject Task Name Completion Date
Amit Biology SBSB 30-04-2020
Amit Biology EBSB 31-05-2020
Amit Biology GANGA_QUEST 30-04-2020
Anand Hindi SBSB 30-04-2020
Anand Hindi EBSB 31-05-2020
Anand Hindi GANGA_QUEST 30-04-2020
Mohan Physics SBSB 30-04-2020
Mohan Physics EBSB 31-05-2020
Mohan Physics GANGA_QUEST 30-04-2020

164
 3.11 Worksheets

....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
6. Write the SQL queries:
(a). Retrieve all tasks along with the respective teacher information.
(b). Find out the completion date and subject for each task.
(c). Get the teacher name, subject, and task name for tasks completed on 31-05-2020.
(d). List all tasks along with the teacher’s subject and the completion date.
(e). Find out the tasks completed by each teacher with their respective subjects.
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................
....................................................................................................

165