Assignment: Data Storage and Querying | Database Management System (DBMS) - Software Development PDF Download

MCQs (Multiple Choice Questions)

Q.1. Which of the following is not a primary key constraint in a relational database?
(a) Unique constraint
(b) Not null constraint
(c) Foreign key constraint
(d) Check constraint

Ans. (c)

Q.2. In a relational database, which normal form ensures that there are no partial dependencies?
(a) First normal form (1NF)
(b) Second normal form (2NF)
(c) Third normal form (3NF)
(d) Fourth normal form (4NF)

Ans. (c)

Q.3. Which of the following statements about indexes in a database is true?
(a) Indexes speed up data modification operations
(b) Indexes consume additional disk space
(c) Indexes are not useful for querying large tables
(d) Indexes are automatically created for all columns in a table

Ans. (b)

Q.4. Which of the following SQL statements is used to delete data from a table?
(a) INSERT
(b) SELECT
(c) DELETE
(d) UPDATE

Ans. (c)

Q.5. Which of the following is an example of a non-relational database?
(a) MySQL
(b) Oracle
(c) MongoDB
(d) PostgreSQL

Ans. (c)

HOTS (Higher Order Thinking Skills) Questions

Q.1. Explain the difference between a primary key and a foreign key in a relational database.

A primary key is a unique identifier for a row in a table. It ensures that each row is uniquely identified within the table. A foreign key, on the other hand, is a column or set of columns in a table that refers to the primary key of another table. It establishes a relationship between two tables, enforcing referential integrity.

Q.2. Discuss the advantages and disadvantages of using indexes in a database.

Advantages of using indexes in a database include faster data retrieval for queries involving indexed columns, improved query performance, and efficient sorting and grouping of data. Indexes can significantly enhance the speed of read operations. However, indexes also have disadvantages. They consume additional disk space, increase the overhead for data modification operations (such as INSERT, UPDATE, and DELETE), and require maintenance when the indexed columns are modified.

Q.3. Consider a table named "Employees" with the following columns: ID, Name, Salary. Write an SQL query to find the employee(s) with the highest salary.

SQL query to find the employee(s) with the highest salary:

SELECT *

FROM Employees

WHERE Salary = (SELECT MAX(Salary) FROM Employees);

Q.4. What are the ACID properties in database transactions? Explain each property briefly.

ACID properties in database transactions:

• Atomicity: A transaction is treated as a single unit of work that either succeeds completely or fails completely. It ensures that all changes made within a transaction are either committed or rolled back.
• Consistency: A transaction brings the database from one consistent state to another. It ensures that data remains valid and meets the defined integrity constraints.
• Isolation: Each transaction operates independently of other transactions. It provides concurrency control to prevent interference between simultaneous transactions.
• Durability: Once a transaction is committed, its changes are permanent and survive any subsequent failures or system restarts. The data changes are stored in a way that ensures durability.

Q.5. Explain the concept of normalization in database design. Why is it important?

Normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves breaking down larger tables into smaller tables and defining relationships between them. Normalization helps eliminate data anomalies and ensures data integrity. It minimizes data duplication and provides a flexible and scalable database design.

Fill in the blanks

1. In a relational database, a ____________ is a unique identifier for a row in a table.

Ans. primary key

2. SQL stands for ____________.

Ans. Structured Query Language

3. A database ____________ is a set of rules that determine how data is organized and stored in a database.

Ans. schema

4. The process of combining tables in a database to retrieve related information is called ____________.

Ans. join

5. In a database, a ____________ is a temporary storage area used to store intermediate results during query execution.

Ans. temporary table

True or False

1. In a database, the primary key can consist of multiple columns.

Ans. True

2. Joins are used to combine tables based on common columns.

Ans. True

3. In a relational database, a transaction is a collection of database operations that must be performed together as a single unit.

Ans. True

4. The ORDER BY clause in an SQL query is used to specify the columns to be selected.

Ans. False

5. Database indexes can improve query performance but have no impact on data modification operations.

Ans. False

Hands-On Questions

Q.1. Create a table named "Students" with the following columns: ID (integer), Name (varchar), and Age (integer). Ensure that the ID column is the primary key.

'sql CREATE TABLE Students ( ID INT PRIMARY KEY, Name VARCHAR(50), Age INT );'

Q.2. Write an SQL query to retrieve all the students whose age is greater than 20.

SELECT * FROM Students WHERE Age > 20;

Q.3. Consider a table named "Orders" with the following columns: OrderID (integer), CustomerID (integer), and OrderDate (date). Write an SQL query to retrieve the number of orders placed by each customer.

SELECT CustomerID, COUNT(OrderID) AS OrderCount

FROM Orders

GROUP BY CustomerID;

Q.4. Write an SQL query to update the salary of all employees in the "Employees" table by adding a 10% increment.

UPDATE Employees SET Salary = Salary * 1.10;

Q.5. Consider a table named "Books" with the following columns: BookID (integer), Title (varchar), and AuthorID (integer). Write an SQL query to retrieve the titles of all books along with the corresponding author names.

SELECT Books.Title, Authors.AuthorName

FROM Books

INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID;