Assignment: RDBMS | Database Management System (DBMS) - Software Development PDF Download

Multiple-Choice Questions (MCQs) 

Q.1. Which of the following is not a property of a relational database?
(a) Data is stored in tables
(b) Data is organized in rows and columns
(c) Data is stored in a hierarchical structure
(d) Data can be related using keys

Ans. (c)

Relational databases store data in tables, not in a hierarchical structure like hierarchical databases.

Q.2. What does ACID stand for in the context of a relational database?
(a) Atomicity, Consistency, Isolation, Durability
(b) Association, Connectivity, Inheritance, Data
(c) Access, Control, Integrity, Distribution
(d) Aggregation, Constraints, Indexing, Data

Ans. (a)

ACID is an acronym that represents the four properties of a reliable database transaction.

Q.3. Which of the following is an example of a primary key in a relational database?
(a) A column that contains unique values for each row
(b) A column that allows NULL values
(c) A column that stores calculated values
(d) A column that stores text data

Ans. (a)
 A primary key is a column or a combination of columns that uniquely identifies each record in a table.

Q.4. In a relational database, a foreign key is used to:
(a) Establish relationships between tables
(b) Identify the primary key of a table
(c) Define constraints on data types
(d) Perform aggregate functions on data

Ans. (a)

A foreign key is used to establish relationships between tables by referencing the primary key of another table.

Q.5. Which SQL command is used to add a new record to a table?
(a) UPDATE
(b) DELETE
(c) INSERT
(d) ALTER

Ans. (c)
The INSERT command is used to add a new record to a table in a relational database.

HOTS (Higher Order Thinking Skills) Questions

Q.1. Explain the concept of normalization in the context of a relational database. Why is normalization important?

Normalization is the process of organizing data in a database to eliminate redundancy and improve data integrity. It involves breaking down a large table into smaller tables and establishing relationships between them using primary and foreign keys. Normalization helps in minimizing data duplication, improving data consistency, and reducing update anomalies.

Q.2. Compare and contrast primary keys and foreign keys in a relational database. Provide examples to illustrate their usage.

Primary keys and foreign keys are both used to establish relationships between tables in a relational database. However, there are some differences between them:

• A primary key is a column or a combination of columns that uniquely identifies each record in a table. It ensures data integrity and enforces the uniqueness constraint. Example: EmployeeID in an Employee table.
• A foreign key is a column or a combination of columns that references the primary key of another table. It establishes a relationship between two tables and enforces referential integrity. Example: EmployeeID in a Orders table, referencing the EmployeeID in the Employee table.

Q.3. Discuss the advantages and disadvantages of using a relational database management system (RDBMS) compared to other types of database systems.

Advantages of using a relational database management system (RDBMS):

• Data integrity: RDBMS enforces data integrity through the use of primary keys, foreign keys, and constraints.
• Flexibility: RDBMS allows for easy modification and expansion of the database schema.
• Scalability: RDBMS can handle large amounts of data and support concurrent users.
• Querying capabilities: RDBMS provides a powerful SQL-based querying language for retrieving and manipulating data.
• Security: RDBMS offers security features such as user authentication, access control, and data encryption.

Disadvantages of using a relational database management system (RDBMS):

• Performance: RDBMS may experience performance issues when dealing with complex queries or large datasets.
• Overhead: RDBMS requires additional hardware resources and maintenance compared to other database systems.
• Lack of flexibility for unstructured data: RDBMS is not suitable for handling unstructured or semi-structured data efficiently.
• Cost: Implementing and maintaining an RDBMS can be costly, especially for small-scale applications.

Q.4. Explain the purpose and usage of indexes in a relational database. How can indexes improve query performance?

Indexes in a relational database are data structures that improve the speed of data retrieval operations. They work like an index in a book, allowing the database to quickly locate the desired data. The advantages of using indexes are:

• Faster query execution: Indexes reduce the number of disk I/O operations required to locate data, resulting in faster query execution.
• Improved data access: Indexes enable the database to locate specific records efficiently based on the indexed columns.
• Optimization of join operations: Indexes can optimize join operations by allowing the database to quickly match records between tables.
• Sorting and ordering: Indexes can speed up sorting and ordering operations on indexed columns.

However, indexes also have some disadvantages. They require additional storage space and can slow down data modification operations (such as inserts, updates, and deletes) as the indexes need to be updated along with the data.

Q.5. Describe the concept of referential integrity in a relational database. Why is referential integrity important and how can it be enforced?

Referential integrity is a concept in relational databases that ensures the consistency and integrity of data relationships between tables. It enforces that the values in a foreign key column of a table match the values in the primary key column of the referenced table. Referential integrity is important because it prevents orphaned records (records with invalid references) and maintains the integrity of the data relationships.

Referential integrity can be enforced through the use of foreign key constraints. When a foreign key constraint is defined, the database management system checks that any value inserted or updated in the foreign key column matches a valid value in the referenced primary key column. If a foreign key constraint is violated, the database system rejects the operation.

Fill in the Blanks

1. A __________ is a collection of related data organized in tables.

database

2. The process of breaking down a large table into smaller tables to eliminate data redundancy is known as __________.

normalization

3. In a relational database, a __________ key uniquely identifies each record in a table.

primary

4. The __________ command is used to retrieve data from a relational database.

SELECT

5. A __________ key is a column or combination of columns that uniquely identifies a record in another table.

foreign

True/False

1. In a relational database, data is stored in a hierarchical structure.

False

2. A foreign key can be NULL in a relational database. 

True

3. SQL stands for Structured Query Language. 

True

4. An index in a database is used to physically sort the data in a table.

False

5. Referential integrity ensures that data in a foreign key column references an existing primary key value. 

True

Hands-On Questions

Q.1. Consider the following tables:

STUDENTS

+------+------------+-------+

| ID | NAME | GRADE |

+------+------------+-------+

| 1001 | Alice | A |

| 1002 | Bob | B |

| 1003 | Claire | A |

+------+------------+-------+

COURSES

+------+--------------+

| CODE | NAME |

+------+--------------+

| C01 | Mathematics |

| C02 | Science |

| C03 | History |

+------+--------------+

Create the necessary SQL statement(s) to establish a relationship between the STUDENTS and COURSES tables using a foreign key.

SQL statement(s) to establish a relationship between the STUDENTS and COURSES tables using a foreign key:

ALTER TABLE STUDENTS ADD COLUMN COURSE_CODE VARCHAR(10);

ALTER TABLE STUDENTS ADD CONSTRAINT FK_STUDENTS_COURSES FOREIGN KEY (COURSE_CODE) REFERENCES COURSES(CODE);

Q.2. Consider the following table:

ORDERS

+-------+------------+-------+

| ORDER | DATE | TOTAL |

+-------+------------+-------+

| 1001 | 2023-01-01 | 100.0 |

| 1002 | 2023-01-02 | 50.0 |

| 1003 | 2023-01-03 | 75.0 |

+-------+------------+-------+

Write an SQL query to retrieve all orders with a total greater than 80.

SQL query to retrieve all orders with a total greater than 80:

SELECT * FROM ORDERS WHERE TOTAL > 80;

Q.3. Explain the different types of join operations in SQL. Provide an example of each type of join.

Types of join operations in SQL:

• INNER JOIN: Returns records that have matching values in both tables.
  Example: SELECT * FROM Table1 INNER JOIN Table2 ON Table1.key = Table2.key;
• LEFT JOIN: Returns all records from the left table (Table1) and the matching records from the right table (Table2).
  Example: SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.key = Table2.key;
• RIGHT JOIN: Returns all records from the right table (Table2) and the matching records from the left table (Table1).
  Example: SELECT * FROM Table1 RIGHT JOIN Table2 ON Table1.key = Table2.key;
• FULL OUTER JOIN: Returns all records when there is a match in either the left table or the right table.
  Example: SELECT * FROM Table1 FULL OUTER JOIN Table2 ON Table1.key = Table2.key;

Q.4. Consider the following table:

EMPLOYEES

+------+-----------+---------+

| ID | NAME | MANAGER |

+------+-----------+---------+

| 1001 | Alice | NULL |

| 1002 | Bob | 1001 |

| 1003 | Claire | 1001 |

| 1004 | David | 1003 |

+------+-----------+---------+

Write an SQL query to retrieve the names of all employees along with the name of their respective managers.

SQL query to retrieve the names of all employees along with the name of their respective managers:

SELECT E.NAME AS Employee, M.NAME AS Manager

FROM EMPLOYEES E

LEFT JOIN EMPLOYEES M ON E.MANAGER = M.ID;

Q.5. Explain the concept of transaction in the context of a relational database. How does a transaction ensure data consistency and integrity?

A transaction in a relational database is a sequence of database operations that are treated as a single unit. It ensures that all the operations within the transaction are completed successfully or none of them are applied. A transaction ensures data consistency and integrity by following the ACID properties:

• Atomicity: The transaction is treated as a single unit, and all its operations are executed or rolled back together.
• Consistency: The database remains in a consistent state before and after the transaction.
• Isolation: The intermediate state of a transaction is not visible to other concurrent transactions.
• Durability: Once a transaction is committed, its changes are permanent and will survive any subsequent failures.

By enforcing these properties, a transaction guarantees that the database remains in a valid state even in the presence of concurrent operations or system failures.