Introduction to Database

1. What is a database and how does it differ from a traditional file system?

- A) A database is a collection of tables.

- B) A database is a structured collection of data stored electronically.

- C) A traditional file system is more secure than a database.

- D) A database requires less storage space than a traditional file system.

Answer B

2. Define a Database Management System (DBMS) and list its key functions.

- A) A DBMS is a software for managing databases and its key functions include data storage, retrieval,
and update.

- B) A DBMS is a programming language for databases.

- C) A DBMS only allows data retrieval.

- D) A DBMS is hardware used to store data.

Answer A

3. What are the advantages of using a DBMS?

- A) Improved data sharing and security.

- B) Increased data redundancy.

- C) Less complex data structures.

- D) None of the above.

Answer D

4. Which of the following is a relational database?

- A) A flat file database.

- B) A database where data is stored in hierarchical format.

- C) A database where data is stored in tables with relationships.

- D) A NoSQL database.

Answer C
5. What is SQL?

- A) A programming language.

- B) A query language used for accessing and manipulating databases.

- C) A type of hardware.

- D) An operating system.

Answer B

6. Write a SQL query to create a table named "Students". Which option is correct?

- A) CREATE STUDENTS;

- B) CREATE TABLE Students;

- C) CREATE Students (ID INT, Name VARCHAR(50));

- D) `CREATE TABLE Students (ID INT, Name VARCHAR(50));

Answer D

7. How would you retrieve all records from a table named "Employees" where the "Department" is
"HR"?

- A) SELECT * FROM Employees;

- B) `SELECT * FROM Employees WHERE Department = 'HR';

- C) SELECT * WHERE Department = 'HR' FROM Employees;

- D) SELECT FROM Employees WHERE Department = 'HR';

Answer B

8. What is normalization?

- A) A process to increase data redundancy.

- B) A process to eliminate redundant data and ensure data dependencies.

- C) A process to create complex data structures.

- D) A process to design flat file databases.

Answer B

9. Which of the following is a property of a database transaction (ACID)?

- A) Asynchronous

- B) Consistency

- C) Independence

- D) Distribution

Answer B

10. Which SQL command is used to update existing data in a database?

- A) INSERT

- B) UPDATE

- C) DELETE

- D) SELECT

Answer B

11. What is the purpose of the `GROUP BY` clause in SQL?

- A) To filter the results of a query.

- B) To sort the results of a query.

- C) To aggregate data based on one or more columns.

- D) To join tables based on a common column.

Answer C

12. Which of the following is a valid SQL constraint?

- A) NOT EMPTY

- B) UNIQUE

- C) DUPLICATE

- D) DISTINCT

Answer B

13. What does the SQL `JOIN` operation do?

- A) It combines columns from multiple tables.

- B) It filters rows based on a condition.

- C) It updates rows in a table.

- D) It creates a new table.

Answer A

14. What is the purpose of the `HAVING` clause in SQL?

- A) To filter rows before aggregation.

- B) To filter rows after aggregation.

- C) To sort the results of a query.

- D) To define a new table.

Answer B

Conceptual Database Design and ER Modeling

1. What is an Entity-Relationship (ER) model?

- A) A model for designing hardware.

- B) A diagram that shows the relationships between software applications.

- C) A conceptual model used to represent data structures and relationships.

- D) A flowchart for system processes.

Answer C

2. In an ER model, what is an entity?

- A) A type of relationship between data.

- B) A unique object or concept that can be identified and stored.

- C) A database constraint.

- D) A type of attribute.

Answer A

3. Which of the following is a primary key in an ER model?

- A) A unique identifier for each entity instance.

- B) A non-unique attribute.

- C) A composite attribute.

- D) A derived attribute.

Answer A

4. Differentiate between strong and weak entities. Which statement is correct?

- A) Strong entities depend on weak entities for identification.

- B) Weak entities do not have their own key attributes.

- C) Weak entities can exist independently.

- D) Strong entities are identified by their own key attributes.

Answer D

5. What is a composite attribute?

- A) An attribute that cannot be divided further.

- B) An attribute composed of multiple components.

- C) An attribute with multiple values.

- D) An attribute derived from another attribute.

Answer B

6. Describe a one-to-many relationship in an ER model.

- A) Each entity in the relationship is related to one and only one other entity.

- B) An entity in the relationship can be related to multiple entities in the other set.

- C) Each entity in the relationship is related to multiple entities in both sets.

- D) None of the above.

Answer B

7. What is a recursive relationship?

- A) A relationship between two different entities.

- B) A relationship among more than two entities.

- C) A relationship between an entity and itself.

- D) A derived relationship.

Answer C

8. What is an associative entity?

- A) An entity used to represent ternary relationships.

- B) An entity that acts as a bridge between two entities in a many-to-many relationship.

- C) An entity that cannot exist without a weak entity.

- D) An entity with no attributes.

Answer B

9. What is generalization in an ER model?

- A) Creating a new entity from existing entities based on common attributes.

- B) Specializing an entity into multiple sub-entities.

- C) Aggregating entities into a single entity.

- D) None of the above.

Answer A

10. How do you handle a ternary relationship in an ER model?

- A) By creating three separate binary relationships.

- B) By creating an associative entity to represent the relationship.

- C) By combining all entities into one.

- D) By ignoring the relationship.

Answer B

11. What is a multi-valued attribute in an ER model?

- A) An attribute with a fixed set of values.

- B) An attribute that can have multiple values for a single entity.

- C) An attribute that is derived from other attributes.

- D) An attribute used to establish relationships between entities.

Answer B

12. In an ER diagram, how is a relationship between two entities typically represented?

- A) With a rectangle.

- B) With a diamond.

- C) With an oval.

- D) With a triangle.

Answer B

13. What is the significance of a foreign key in a relational database?

- A) It uniquely identifies each row in a table.

- B) It establishes a relationship between tables.

- C) It enforces uniqueness for a column.

- D) It indicates mandatory participation in a relationship.

Answer B

14. Which of the following is true about a weak entity?

- A) It has a primary key that uniquely identifies each instance.

- B) It cannot exist without a corresponding strong entity.

- C) It is always independent of other entities.

- D) It does not have any attributes.

Answer B

15. What is the difference between `INNER JOIN` and `OUTER JOIN` in SQL?

- A) INNER JOIN returns matched rows from both tables, while OUTER JOIN returns all rows from one
table.

- B) INNER JOIN returns all rows from both tables, while OUTER JOIN returns only matched rows.

- C) `INNER JOIN` returns matched rows from both tables, while `OUTER JOIN` returns matched rows
plus unmatched rows from one or both tables.
- D) INNER JOIN only returns rows from one table, while OUTER JOIN returns rows from both tables.

Answer C

Relational Data Model and Constraints

1. Which of the following best describes the concept of a "superkey" in a relational database?

- A) A single attribute that uniquely identifies a tuple.

- B) A minimal set of attributes that can uniquely identify a tuple.

- C) A set of attributes that can uniquely identify a tuple, not necessarily minimal.

- D) A key composed of multiple foreign keys.

Answer C

2. What is a "candidate key" in a relational database?

- A) Any set of attributes that can uniquely identify a tuple.

- B) A minimal superkey.

- C) A primary key.

- D) An attribute that serves as a foreign key.

Answer B

3. In the context of relational databases, what does the "referential integrity constraint" ensure?

- A) The primary key of a table cannot be null.

- B) Every non-key attribute is fully functionally dependent on the primary key.

- C) Foreign key values must match primary key values in the referenced table or be null.

- D) Every attribute in a table must have a unique value.

Answer C

4. Which normal form requires that a relation has no partial dependency on a candidate key?

- A) First Normal Form (1NF)

- B) Second Normal Form (2NF)

- C) Third Normal Form (3NF)

- D) Boyce-Codd Normal Form (BCNF)

Answer B

5. What is a "transitive dependency" in a relational database?

- A) A functional dependency between two non-key attributes via the primary key.

- B) A functional dependency between two key attributes.

- C) A situation where a non-key attribute depends on another non-key attribute.

- D) A situation where a primary key depends on a foreign key.

Answer C

6. Which normal form is considered a stricter version of the Third Normal Form (3NF)?

- A) Boyce-Codd Normal Form (BCNF)

- B) Fourth Normal Form (4NF)

- C) Fifth Normal Form (5NF)

- D) Domain-Key Normal Form (DKNF)

Answer A

7. What is the main goal of achieving Boyce-Codd Normal Form (BCNF) in relational database design?

- A) To remove partial dependencies.

- B) To eliminate transitive dependencies.

- C) To ensure that every determinant is a candidate key.

- D) To reduce data redundancy.

Answer C

8. Which of the following statements is true regarding multi-valued dependencies in relational

databases?

- A) They are allowed in First Normal Form (1NF).

- B) They are disallowed in Fourth Normal Form (4NF).

- C) They are a type of transitive dependency.

- D) They occur only in relations with a single attribute.

Answer B

9. What is a "foreign key constraint" in a relational database?

- A) A constraint that ensures each row in a table has a unique identifier.

- B) A constraint that ensures a value in one table must match a value in another table.

- C) A constraint that ensures attributes are not null.

- D) A constraint that allows for cascading updates and deletes.

Answer A

10. In which normal form is a relation if it is in 2NF and every non-key attribute is non-transitively
dependent on the primary key?

- A) Second Normal Form (2NF)

- B) Third Normal Form (3NF)

- C) Boyce-Codd Normal Form (BCNF)

- D) Fourth Normal Form (4NF)

Answer B

11. Which of the following is true about a relation in Boyce-Codd Normal Form (BCNF)?

- A) It must be in 1NF and 2NF only.

- B) Every determinant is a superkey.

- C) It cannot contain any multivalued attributes.

- D) Every non-trivial functional dependency has a superkey as its determinant.

Answer D

12. What is the primary purpose of a primary key constraint in a relational database?

- A) To ensure that an attribute or a combination of attributes uniquely identifies each row.

- B) To enforce referential integrity between tables.

- C) To prevent duplicate values in the primary key column(s).

- D) To allow null values in the primary key column(s).

Answer C
13. Which of the following normal forms eliminates the possibility of insert, update, and delete
anomalies?

- A) First Normal Form (1NF)

- B) Second Normal Form (2NF)

- C) Third Normal Form (3NF)

- D) Boyce-Codd Normal Form (BCNF)

Answer C

14. What is an "inclusion dependency" in the context of relational databases?

- A) A constraint that ensures all foreign key values must be unique.

- B) A constraint that requires that a set of values in one relation must be a subset of values in another
relation.

- C) A dependency that arises when an attribute depends on another attribute through transitivity.

- D) A type of multi-valued dependency.

Answer A

15. Which normal form addresses the issue of multi-valued dependencies?

- A) First Normal Form (1NF)

- B) Second Normal Form (2NF)

- C) Fourth Normal Form (4NF)

- D) Fifth Normal Form (5NF)

Answer C

16. In a relational database, what does the term "lossless join" refer to?

- A) A join operation that removes duplicate rows.

- B) A join operation that combines tables without losing any data.

- C) A property ensuring that decomposing a relation into two relations and then joining them back
results in the original relation.

- D) A join operation that retains only unique values.

Answer C
17. Which of the following is a reason to use normalization in relational database design?

- A) To increase data redundancy.

- B) To minimize insertion, deletion, and update anomalies.

- C) To denormalize the database for faster queries.

- D) To simplify the database schema.

Answer B

18. What is a "domain-key normal form" (DKNF)?

- A) A normal form that ensures all constraints on the data are a logical consequence of the definition
of keys and domains.

- B) A normal form that removes all transitive dependencies.

- C) A normal form that ensures all attributes are fully functionally dependent on the primary key.

- D) A normal form that addresses multi-valued dependencies.

Answer A

19. Which normal form is concerned with the removal of join dependencies?

- A) Fourth Normal Form (4NF)

- B) Boyce-Codd Normal Form (BCNF)

- C) Third Normal Form (3NF)

- D) Fifth Normal Form (5NF)

Answer D

20. What is the purpose of the `CHECK` constraint in SQL?

- A) To ensure that an attribute value is unique across the table.

- B) To enforce a condition that each row in the table must satisfy.

- C) To create a foreign key relationship between two tables.

- D) To specify the default value for a column.

Answer B

Basic Concepts
1. What are the key differences between a relational database and a NoSQL database?

- A) NoSQL databases are always faster than relational databases.

- B) Relational databases use tables with rows and columns, while NoSQL databases can use various
models like document, key-value, graph, or column-family.

- C) NoSQL databases do not support ACID properties.

- D) Relational databases cannot handle large amounts of unstructured data.

Answer B

2. Explain the ACID properties in the context of database transactions.

- A) Atomicity, Consistency, Isolation, Durability

- B) Availability, Consistency, Integrity, Durability

- C) Atomicity, Compatibility, Isolation, Durability

- D) Availability, Consistency, Isolation, Durability

Answer A

3. What is a primary key and why is it important?

- A) A key used to encrypt data in the database.

- B) A unique identifier for a row in a table, ensuring that each record can be uniquely identified.

- C) A key that allows multiple null values.

- D) A key that allows duplicate values.

Answer B

4. What is normalization and why is it necessary in database design?

- A) The process of designing a database to reduce redundancy and improve data integrity.

- B) The process of denormalizing data to improve query performance.

- C) The process of encrypting data for security.

- D) The process of compressing data to save space.

Answer A

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

- A) A clustered index sorts and stores the data rows in the table based on their key values, while a
non-clustered index does not.

- B) A clustered index can have multiple indexes per table, while a non-clustered index can only have
one.

- C) A clustered index is used for OLAP, while a non-clustered index is used for OLTP.

- D) A clustered index is created automatically, while a non-clustered index must be created manually.

Answer A

SQL (Structured Query Language)

6. Write an SQL query to find all employees who earn more than the average salary.

- A) SELECT * FROM employees WHERE salary > AVG(salary);

- B) SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

- C) SELECT * FROM employees WHERE salary > (AVG(salary) FROM employees);

- D) SELECT * FROM employees HAVING salary > (SELECT AVG(salary) FROM employees);

Answer B

7. How do you create a new table in SQL? Provide an example.

- A) CREATE TABLE table_name (column1 datatype, column2 datatype);

- B) ADD TABLE table_name (column1 datatype, column2 datatype);

- C) MAKE TABLE table_name (column1 datatype, column2 datatype);

- D) NEW TABLE table_name (column1 datatype, column2 datatype);

Answer A

8. Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

- A) INNER JOIN returns only matched rows; LEFT JOIN returns matched rows and all rows from the left
table; RIGHT JOIN returns matched rows and all rows from the right table; FULL OUTER JOIN returns all
rows from both tables.

- B) INNER JOIN returns all rows from both tables; LEFT JOIN returns only matched rows; RIGHT JOIN
returns matched rows and all rows from the left table; FULL OUTER JOIN returns only matched rows.
- C) INNER JOIN returns matched rows and all rows from the right table; LEFT JOIN returns matched
rows and all rows from the left table; RIGHT JOIN returns only matched rows; FULL OUTER JOIN returns
all rows from both tables.

- D) INNER JOIN returns all rows from both tables; LEFT JOIN returns all rows from the left table; RIGHT
JOIN returns all rows from the right table; FULL OUTER JOIN returns only matched rows.

Answer A

9. What is a subquery and when would you use one?

- A) A query that retrieves data from multiple tables; used to combine results.

- B) A query that runs within another query; used to perform operations that require intermediate
results.

- C) A query that updates data in a table; used to modify existing records.

- D) A query that deletes data from a table; used to remove unwanted records.

Answer B

10. How can you optimize a slow-running query?

- A) By adding more columns to the SELECT statement.

- B) By avoiding the use of indexes.

- C) By using indexes, reducing the number of columns in SELECT, and optimizing joins.

- D) By using more subqueries.

Answer C

11. What is database sharding and how does it improve performance?

- A) The process of merging multiple databases; it improves performance by reducing the number of
databases.

- B) The process of splitting a database into smaller, more manageable pieces; it improves
performance by distributing load across multiple servers.

- C) The process of compressing data in the database; it improves performance by reducing storage
requirements.

- D) The process of encrypting data in the database; it improves performance by enhancing security.

Answer B
12. Explain the concept of database replication and its types.

- A) The process of copying data from one database to another; types include synchronous and
asynchronous replication.

- B) The process of deleting data from a database; types include hard and soft deletion.

- C) The process of compressing data in a database; types include lossless and lossy compression.

- D) The process of merging data from multiple databases; types include full and partial merge.

Answer A

13. How does a transaction log work in a database system?

- A) It records all transactions and changes made to the database; helps in recovery and rollback.

- B) It compresses data in the database; helps in reducing storage requirements.

- C) It deletes old data from the database; helps in maintaining database size.

- D) It encrypts data in the database; helps in enhancing security.

Answer B

14. What are the differences between OLTP (Online Transaction Processing) and OLAP (Online Analytical
Processing)?

- A) OLTP is used for batch processing; OLAP is used for real-time processing.

- B) OLTP handles large volumes of data; OLAP handles small volumes of data.

- C) OLTP is designed for managing transaction-oriented applications; OLAP is designed for analytical
and decision-making applications.

- D) OLTP requires complex queries; OLAP requires simple queries.

Answer C

15. Describe the CAP theorem and its implications for distributed databases.

- A) Consistency, Availability, Partition Tolerance; a distributed system can achieve only two out of the
three.

- B) Compression, Availability, Performance; a distributed system can achieve only two out of the
three.
- C) Consistency, Accuracy, Performance; a distributed system can achieve only two out of the three.

- D) Consistency, Adaptability, Partition Tolerance; a distributed system can achieve only two out of
the three.

Answer A

Ch 4 and ch 5 questions 👇👇👇

Data Modeling Using ER Model

1. Which of the following correctly describes a weak entity?

- A. An entity that does not have a primary key.

- B. An entity that has its own unique key and does not depend on any other entity.

- C. An entity that cannot be uniquely identified by its own attributes alone and relies on a foreign key
from another entity.

- D. An entity that is used only for derived attributes.

- Answer: C

2. In an ER model, which scenario would justify the use of a ternary relationship?

- A. A student enrolls in a course.

- B. A customer makes a purchase from a store.

- C. A supplier provides a part to a project.

- D. An employee works on multiple projects.

- Answer: C

3. Given an ER model with entities Author, Book, and Publisher, and the relationships Author writes
Book and Publisher publishes Book, what would be an appropriate primary key for the Book entity?

- A. BookID

- B. (AuthorID, PublisherID)

- C. (AuthorID, BookID)

- D. (PublisherID, BookID)

- Answer: A
4. Which of the following is NOT a characteristic of a good ER diagram?

- A. It includes all the necessary entities and relationships.

- B. It maximizes redundancy to ensure data is replicated.

- C. It clearly distinguishes between different types of relationships (one-to-one, one-to-many, many-

to-many).

- D. It uses standard notations and symbols consistently.

- Answer: B

5. When transforming an ER diagram into a relational schema, how is a many-to-many relationship

typically handled?

- A. By creating a single table that combines the attributes of both entities.

- B. By creating a new associative table that includes foreign keys from both related entities.

- C. By creating a foreign key in one of the related tables.

- D. By ignoring the relationship since it cannot be represented directly.

- Answer: B

Logical Database Design (Functional Dependency and Normalization)

6. Which of the following statements about functional dependencies (FDs) is true?

- A. FDs are always bidirectional.

- B. If A → B and B → C, then A → C (transitivity).

- C. FDs can only exist within a single table.

- D. FDs are only relevant in non-relational databases.

- Answer: B

7. Consider the relation R(A, B, C, D) with the following functional dependencies: {A → B, B → C, C → D}.
What is the highest normal form that R satisfies?

- A. 1NF

- B. 2NF

- C. 3NF

- D. BCNF
- Answer: D

8. A relation is in BCNF if and only if:

- A. It is in 1NF and 2NF.

- B. Every determinant is a candidate key.

- C. It has no transitive dependencies.

- D. It has a single candidate key.

- Answer: B

9. Consider a relation schema R(A, B, C, D) with functional dependencies {A → B, C → D}. What is the key
for R?

- A. A

- B. C

- C. (A, C)

- D. (B, D)

- Answer: C

10. Which of the following describes a multivalued dependency?

- A. A → B, B → A

- B. A →→ B means if two tuples have the same A value, then their B values are independent of each
other.

- C. A →→ B means B is a subset of A.

- D. A → B means B is a unique attribute dependent on A.

- Answer: B

11. Given a relation R(A, B, C) with functional dependencies {A → B, B → C}, which of the following
decompositions is a lossless-join decomposition?

- A. R1(A, B) and R2(B, C)

- B. R1(A, B) and R2(A, C)

- C. R1(A, C) and R2(B, C)

- D. R1(A, B, C) and R2(B, C)

- Answer: A

12. Which of the following statements is true about 4NF (Fourth Normal Form)?

- A. A relation in 4NF must also be in BCNF.

- B. 4NF deals with functional dependencies.

- C. 4NF eliminates all types of dependencies.

- D. A relation in 4NF can have multivalued dependencies.

- Answer: A

13. Which of the following is true about entity integrity in an ER model?

- A. An entity's primary key can have null values.

- B. Each entity must have a unique primary key.

- C. Foreign keys can serve as primary keys.

- D. An entity can have multiple primary keys.

- Answer: B

14. In an ER diagram, what does a diamond shape represent?

- A. An entity

- B. An attribute

- C. A relationship

- D. A primary key

- Answer: C

15. Which scenario best illustrates the use of a recursive relationship in an ER model?

- A. Students enroll in courses.

- B. Employees manage other employees.

- C. Authors write books.

- D. Departments have employees.

- Answer: B
16. In ER modeling, which type of attribute can be broken down into smaller components?

- A. Composite attribute

- B. Multi-valued attribute

- C. Derived attribute

- D. Key attribute

- Answer: A

17. Which of the following best describes a supertype/subtype relationship in an ER model?

- A. A relationship where one entity is dependent on another for its existence.

- B. A relationship where one entity can be classified into multiple subtypes.

- C. A relationship where multiple entities share the same attributes.

- D. A relationship that must be resolved into a join table.

- Answer: B

18. Consider a relation R(A, B, C, D) with functional dependencies {A → B, A → C, B → D}. Which of the
following decompositions is in 3NF?

- A. R1(A, B, C) and R2(B, D)

- B. R1(A, B) and R2(A, C, D)

- C. R1(A, B) and R2(B, C, D)

- D. R1(A, D) and R2(B, C)

- Answer: A

19. Which of the following statements is true about the decomposition of a relation into 2NF?

- A. It eliminates all partial dependencies.

- B. It eliminates all transitive dependencies.

- C. It ensures that every determinant is a candidate key.

- D. It guarantees that the relation is free of all anomalies.

- Answer: A
20. Given a relation schema R(A, B, C, D) with functional dependencies {A → B, C → D, B → C}, what is
the minimal cover for these FDs?

- A. {A → B, B → C, C → D}

- B. {A → B, A → C, A → D}

- C. {A → B, B → D}

- D. {A → B, C → D}

- Answer: A

21. Which of the following is a key feature of a lossless-join decomposition?

- A. Ensuring no spurious tuples are generated after decomposing and joining.

- B. Reducing the size of the database.

- C. Ensuring each table has a single attribute.

- D. Eliminating all redundancy.

- Answer: A

22. Consider a relation R(A, B, C) with the functional dependency A → B and A → C. If R is decomposed
into R1(A, B) and R2(A, C), which normal form does this decomposition achieve?

- A. 1NF

- B. 2NF

- C. 3NF

- D. BCNF

- Answer: C

23. Which of the following correctly defines a candidate key in a relation schema?

- A. A set of attributes that uniquely identifies a tuple and is a minimal superkey.

- B. A set of attributes that can be null.

- C. A primary key that is not used.

- D. A set of attributes that do not uniquely identify a tuple.

- Answer: A
24. In the context of normalization, what is the primary goal of moving a relation to 5NF?

A. To eliminate all redundancy due to join dependencies.

B. To ensure every non-key attribute is fully functionally dependent on the primary key.

C. To remove all multivalued dependencies.

D. To achieve lossless decomposition.

# Answer# A

25. Consider a relation R(A, B, C, D, E) with functional dependencies {A → B, C → D, D → E}. Which of the
following decompositions is in BCNF?

A. R1(A, B) and R2(C, D, E)

B. R1(A, B) and R2(C, D) and R3(D, E)

C. R1(A, B, C) and R2(C, D, E)

D. R1(A, B, D) and R2(C, E)

# Answer # B

26. Which of the following best describes the concept of a surrogate key in a database?

- A. A key that is derived from other attributes in the table.

- B. A key that is a combination of two or more columns.

- C. A system-generated unique identifier, typically an integer.

- D. A foreign key that refers to another table.

- Answer: C

27. In an ER model, what does a double ellipse represent?

- A. A derived attribute

- B. A composite attribute

- C. A multi-valued attribute

- D. A key attribute

- Answer: C
28. Which of the following normalization forms is concerned with eliminating transitive dependencies?

- A. 1NF

- B. 2NF

- C. 3NF

- D. BCNF

- Answer: C

29. In the context of ER modeling, what is the purpose of generalization?

- A. To combine several entities into a higher-level entity.

- B. To split an entity into more specific entities.

- C. To define relationships between different entities.

- D. To create foreign keys in a relational schema.

- Answer: A

30. Which of the following is true for a relation that is in 2NF but not in 3NF?

- A. It has no partial dependencies but may have transitive dependencies.

- B. It has no transitive dependencies but may have partial dependencies.

- C. It has both partial and transitive dependencies.

- D. It is free from all types of dependencies.

- Answer: A

31. Consider a relation R(A, B, C, D) with functional dependencies {A → B, A → C, B → D, C → D}. Which

attribute is not a candidate key?

- A. A

- B. B

- C. C

- D. D

- Answer: B
32. In a relational database, what is a foreign key constraint?

- A. A rule that enforces a unique identifier for each record in a table.

- B. A rule that maintains referential integrity by ensuring that a foreign key value in one table
matches a primary key value in another table.

- C. A rule that ensures data in a table is normalized.

- D. A rule that enforces that no duplicate rows exist in a table.

- Answer: B

33. Which of the following statements about denormalization is true?

- A. It increases redundancy to improve performance.

- B. It reduces redundancy to save storage space.

- C. It ensures all tables are in BCNF.

- D. It is a technique used only in non-relational databases.

- Answer: A

34. Which of the following is a characteristic of a good primary key?

- A. It should be a composite key.

- B. It should be a natural key.

- C. It should be stable and never change.

- D. It should always be a foreign key.

- Answer: C

35. Consider a relation R(A, B, C, D) with functional dependencies {A → B, B → C, C → D}. If R is

decomposed into R1(A, B) and R2(B, C, D), which of the following is true?

- A. The decomposition is in 1NF but not 2NF.

- B. The decomposition is in 2NF but not 3NF.

- C. The decomposition is in 3NF but not BCNF.

- D. The decomposition is in BCNF.

- Answer: D

36. What is the main advantage of using a surrogate key over a natural key?

- A. Surrogate keys are always shorter than natural keys.

- B. Surrogate keys are system-generated and ensure uniqueness without depending on actual data
values.

- C. Surrogate keys are easier to read and understand.

- D. Surrogate keys automatically create indexes on the table.

- Answer: B

37. Which of the following best describes a snowflake schema in a data warehouse?

- A. A schema where data is organized in a star-like structure with a central fact table and surrounding
dimension tables.

- B. A normalized version of a star schema where dimension tables are further divided into related
tables.

- C. A schema where all tables are denormalized to improve query performance.

- D. A schema that only supports hierarchical data models.

- Answer: B

38. Which of the following best describes the process of aggregation in ER modeling?

- A. Combining two or more tables into one.

- B. Defining a higher-level entity set based on a relationship set.

- C. Dividing a large table into smaller, related tables.

- D. Removing redundancy from a database.

- Answer: B

39. What is a key characteristic of the 5NF (Fifth Normal Form)?

- A. It deals with the elimination of transitive dependencies.

- B. It ensures that every join dependency in the relation is implied by the candidate keys.

- C. It removes all types of functional dependencies.

- D. It ensures that every non-key attribute is functionally dependent on the primary key.
- Answer: B

40. Consider a relation R(A, B, C, D, E) with functional dependencies {A → B, B → C, C → D, D → E}. What

is the key for R?

- A. A

- B. B

- C. C

- D. D

- Answer: A

Chapter 5 Physical DB design 👇👇👇👇

1. Which of the following best describes the primary goal of physical database design?

- A. To define the logical structure of the database.

- B. To optimize query performance and storage efficiency.

- C. To ensure data normalization.

- D. To define the relationships between entities.

- Answer: B

2. Which of the following strategies can help improve the performance of read-heavy databases?

- A. Normalization

- B. Indexing

- C. Using surrogate keys

- D. Implementing foreign key constraints

- Answer: B

3. What is the purpose of a clustered index in a database?

- A. To enforce uniqueness of data values.

- B. To physically sort the data rows in the table according to the index key.

- C. To create a secondary index for quick access.

- D. To store frequently accessed data in memory.

- Answer: B

4. Which of the following is a disadvantage of using too many indexes on a table?

- A. Improved query performance

- B. Increased storage requirements

- C. Slower data retrieval

- D. Reduced normalization

- Answer: B

5. Which of the following best describes a materialized view?

- A. A virtual table that is always up-to-date with the base tables.

- B. A physical copy of the result set of a query, stored for fast access.

- C. A temporary table created during query execution.

- D. A view that only stores the query definition, not the data.

- Answer: B

6. In which scenario is partitioning a database table most beneficial?

- A. When the table has a small number of rows.

- B. When the table experiences heavy insert operations.

- C. When the table is frequently accessed by large range queries.

- D. When the table is normalized to the highest form.

- Answer: C

7. Which of the following describes the purpose of using a RAID (Redundant Array of Independent Disks)
configuration in a database system?

- A. To normalize the database schema.

- B. To improve data redundancy and performance.

- C. To create additional indexes.

- D. To implement foreign key constraints.

- Answer: B
8. What is the main advantage of using columnar storage for a database table?

- A. Faster writes and updates

- B. Improved performance for read-heavy analytical queries

- C. Easier to normalize

- D. Simplifies transaction management

- Answer: B

9. Which type of index is best suited for queries that filter on multiple columns?

- A. Unique index

- B. Single-column index

- C. Composite index

- D. Bitmap index

- Answer: C

10. What is the primary benefit of using a database cache?

- A. To reduce the size of the database.

- B. To increase the speed of data retrieval by storing frequently accessed data in memory.

- C. To enforce referential integrity.

- D. To facilitate database normalization.

- Answer: B

11. Which of the following is an example of horizontal partitioning?

- A. Dividing a table into smaller tables based on different attributes.

- B. Splitting a table into different tables with the same attributes but different rows.

- C. Storing data in a denormalized format.

- D. Creating multiple indexes on a table.

- Answer: B
12. In physical database design, what is the purpose of using denormalization?

- A. To reduce redundancy and improve data integrity.

- B. To improve read performance by reducing the number of joins.

- C. To enforce unique constraints.

- D. To simplify database schema design.

- Answer: B

13. Which of the following best describes the role of a database optimizer?

- A. To execute SQL queries.

- B. To translate logical queries into efficient physical execution plans.

- C. To maintain database indexes.

- D. To enforce data integrity constraints.

- Answer: B

14. What is the primary advantage of using in-memory databases?

- A. Reduced storage costs

- B. Improved security

- C. Faster data access and transaction processing

- D. Simplified database schema

- Answer: C

15. Which of the following is true about the use of SSDs (Solid State Drives) in database systems?

- A. SSDs offer lower storage capacity compared to HDDs.

- B. SSDs provide faster read and write speeds than traditional HDDs.

- C. SSDs are more cost-effective than HDDs for large data volumes.

- D. SSDs require less power than HDDs but are slower.

- Answer: B
16. What is the purpose of using data compression in physical database design?

- A. To increase the speed of data retrieval.

- B. To reduce storage requirements and improve query performance.

- C. To enforce data integrity constraints.

- D. To facilitate database normalization.

- Answer: B

17. Which of the following describes the concept of sharding in a database system?

- A. Replicating data across multiple nodes for redundancy.

- B. Dividing a large database into smaller, more manageable pieces called shards, distributed across
multiple servers.

- C. Creating multiple indexes on a table for faster access.

- D. Using foreign keys to maintain referential integrity.

- Answer: B

18. Which of the following is a key consideration when choosing a storage engine for a database?

- A. The color of the server hardware.

- B. The type of data and query workload.

- C. The database administrator’s preference.

- D. The presence of foreign key constraints.

- Answer: B

19. What is the impact of increasing the block size in a database file system?

- A. It reduces the number of I/O operations for large data retrievals.

- B. It increases the number of I/O operations for small data retrievals.

- C. It ensures data integrity and normalization.

- D. It simplifies the database schema.

- Answer: A

20. Which of the following is a disadvantage of using RAID 0 (striping) in a database system?
- A. Increased data redundancy.

- B. Improved read and write performance.

- C. No data protection; a single disk failure can result in data loss.

- D. Higher cost compared to other RAID levels.

- Answer: C

Security Measures in Physical Database Design

21. Which of the following is a common technique to secure data at rest in a relational database?

- A. Using hashed indexes

- B. Implementing data encryption

- C. Applying database normalization

- D. Enabling cross-site scripting (XSS) protection

- Answer: B

22. In database security, what is the primary purpose of using role-based access control (RBAC)?

- A. To simplify the database schema design.

- B. To manage user privileges and enforce the principle of least privilege.

- C. To enhance the performance of database queries.

- D. To create physical database backups.

- Answer: B

23. Which of the following describes the purpose of database auditing?

- A. To optimize query performance.

- B. To track and log database activities for security and compliance.

- C. To enforce foreign key constraints.

- D. To perform regular database backups.

- Answer: B

24. Which of the following is NOT a common physical security measure for database servers?
- A. Implementing biometric access controls

- B. Using firewalls and intrusion detection systems

- C. Encrypting network traffic with SSL/TLS

- D. Keeping servers in a locked, access-controlled environment

- Answer: C

25. What is the primary benefit of using partitioning in physical database design?

- A. Simplifies database schema

- B. Improves query performance and manageability for large datasets

- C. Increases redundancy

- D. Ensures data integrity and normalization

- Answer: B

26. Which of the following best describes the purpose of a RAID 5 configuration in a database system?

- A. Provides data striping with no redundancy

- B. Offers mirroring with no performance improvement

- C. Delivers data striping with parity, balancing performance and redundancy

- D. Ensures maximum data redundancy without performance benefits

- Answer: C

27. In the context of physical database design, what is a hot spot, and why should it be avoided?

- A. A frequently accessed table that should be split to improve performance

- B. A highly concentrated area of data access that can lead to I/O bottlenecks

- C. A large index that requires frequent updates

- D. A security vulnerability in the database

- Answer: B

28. Which of the following indexing strategies is most suitable for a table with a high volume of insert
operations?

- A. Clustered indexing
- B. Non-clustered indexing

- C. Bitmap indexing

- D. Hash indexing

- Answer: D

29. What is the primary goal of denormalization in a relational database?

- A. To enforce foreign key constraints

- B. To reduce the number of joins and improve read performance

- C. To eliminate redundancy and maintain data integrity

- D. To simplify the logical design of the database

- Answer: B

30. Which of the following scenarios would justify the use of denormalization?

- A. A database with frequent write operations and updates

- B. A database with complex queries that require multiple joins

- C. A highly transactional database needing strong consistency

- D. A database designed for high data integrity and normalization

- Answer: B

31. Which of the following is a potential disadvantage of denormalization?

- A. Improved read performance

- B. Increased storage requirements and potential data anomalies

- C. Simplified query design

- D. Reduced complexity in data modeling

- Answer: B

32. In the context of database tuning, what is the purpose of query optimization?

- A. To ensure data normalization

- B. To improve the performance of data retrieval and manipulation operations

- C. To create additional indexes

- D. To enforce referential integrity

- Answer: B

33. Which of the following tuning techniques involves restructuring SQL queries to improve
performance?

- A. Indexing

- B. Query rewriting

- C. Partitioning

- D. Data archiving

- Answer: B

34. In a relational database, what is the impact of increasing the size of database buffers?

- A. Reduces the need for disk I/O operations by storing more data in memory

- B. Increases the need for disk I/O operations by decreasing memory usage

- C. Simplifies the database schema design

- D. Ensures data integrity and normalization

- Answer: A

35. Which of the following is an advantage of using an SSD over an HDD for database storage?

- A. Lower storage costs

- B. Improved read/write performance and lower latency

- C. Higher capacity for the same physical size

- D. Increased durability and longer lifespan

- Answer: B

36. What is the main purpose of using a database connection pool?

- A. To reduce the number of indexes on a table

- B. To manage and reuse database connections, improving application performance

- C. To simplify database schema design

- D. To enforce data integrity constraints

- Answer: B

37. Which of the following describes the role of a database load balancer?

- A. To distribute database queries across multiple servers to improve performance and reliability

- B. To enforce referential integrity

- C. To ensure data normalization

- D. To create backup copies of the database

- Answer: A

38. Which of the following describes the use of data sharding in a physical database design?

- A. Replicating data across multiple servers for redundancy

- B. Partitioning data horizontally to distribute across multiple servers for scalability

- C. Combining multiple tables into a single large table

- D. Storing data in a denormalized format for faster access

- Answer: B

39. In physical database design, which of the following is a benefit of using compression techniques?

- A. Simplifies the logical data model

- B. Reduces storage space and can improve I/O performance

- C. Ensures data consistency and integrity

- D. Facilitates easier database backups

- Answer: B

40. Which of the following is true about the use of database snapshots?

- A. Snapshots are real-time replicas of the database used for high availability.

- B. Snapshots are point-in-time copies of the database that can be used for backup and recovery.

- C. Snapshots eliminate the need for traditional backups.

- D. Snapshots improve query performance by caching frequently accessed data.

- Answer: B

ch 6 relational calculus and relational algebra 👇👇👇👇👇

Relational Algebra

1. Which of the following relational algebra operations is used to extract tuples from a relation that
satisfy a given predicate?

- A. Union (∪)

- B. Projection (π)

- C. Selection (σ)

- D. Cartesian Product (×)

- Answer: C

2. What is the result of performing the natural join operation on two relations R(A, B) and S(B, C)?

- A. A relation containing all tuples from R and S

- B. A relation containing only tuples that have the same value for attribute B in both R and S

- C. A relation containing the Cartesian product of R and S

- D. A relation containing all tuples from R and the common tuples from S

- Answer: B

3. Which of the following operations is not a fundamental operation in relational algebra?

- A. Selection (σ)

- B. Intersection (∩)

- C. Union (∪)

- D. Projection (π)

- Answer: B

4. Consider two relations R(A, B) and S(B, C). Which relational algebra operation would you use to
combine these two relations on the attribute B?

- A. σ

- B. π
- C. ⨝ (Natural Join)

- D. ∪

- Answer: C

5. In relational algebra, what is the result of the division operation R ÷ S when R(A, B) and S(B) are given?

- A. A relation containing all tuples of A in R that are paired with every tuple in S

- B. A relation containing all tuples of B in S that are paired with every tuple in R

- C. A Cartesian product of R and S

- D. An intersection of R and S

- Answer: A

6. Which relational algebra operation is used to rename the attributes of a relation?

- A. Projection (π)

- B. Selection (σ)

- C. Rename (ρ)

- D. Join (⨝)

- Answer: C

7. What does the expression πA(R) represent in relational algebra?

- A. Selection of tuples from relation R where the attribute A has a specific value

- B. Projection of the attribute A from relation R

- C. Cartesian product of relation R with itself

- D. Renaming attribute A in relation R

- Answer: B

8. Which of the following statements about the relational algebra union operation ( ∪) is true?

- A. It requires both operand relations to have the same set of attributes.

- B. It combines tuples from two relations with the same attribute names but different domains.

- C. It generates the Cartesian product of the two operand relations.

- D. It filters tuples from one relation based on a condition.

- Answer: A

9. What is the result of the expression σA>10(R) ∩ σB<5(R) in relational algebra?

- A. All tuples from R where A is greater than 10 or B is less than 5

- B. All tuples from R where A is greater than 10 and B is less than 5

- C. All tuples from R where A is greater than 10 and B is greater than 5

- D. All tuples from R where A is less than 10 and B is greater than 5

- Answer: B

10. In relational algebra, which operation is used to eliminate duplicate tuples from a relation?

- A. Projection (π)

- B. Selection (σ)

- C. Union (∪)

- D. Difference (-)

- Answer: A

Relational Calculus

11. Which of the following is a characteristic of tuple relational calculus (TRC)?

- A. It uses logical connectives to form queries.

- B. It directly specifies how to retrieve the data.

- C. It is procedural.

- D. It uses the SELECT and FROM clauses.

- Answer: A

12. In tuple relational calculus, how would you express a query to find all tuples t such that t[A] = 'Smith'
in relation R?

- A. {t | R(t) ∧ t.A = 'Smith'}

- B. σA='Smith'(R)
- C. πA='Smith'(R)

- D. {t | t ∈ R ∧ t[A] = 'Smith'}

- Answer: D

13. Which of the following is true about domain relational calculus (DRC)?

- A. It uses domain variables that take on values from the attributes' domains.

- B. It uses tuple variables to refer to tuples in the relation.

- C. It specifies a sequence of operations to retrieve data.

- D. It is procedural like relational algebra.

- Answer: A

14. In domain relational calculus, which of the following is the correct representation for finding all pairs
of (x, y) where x is from attribute A and y is from attribute B of relation R?

- A. {〈x, y〉 | ∃R(x, y)}

- B. {〈x, y〉 | R(x, y)}

- C. {〈x, y〉 | ∃t ∈ R (t[A] = x ∧ t[B] = y)}

- D. {〈x, y〉 | R(t) ∧ t[A] = x ∧ t[B] = y}

- Answer: D

15. Which of the following relational calculus expressions is used to find tuples in relation R where the
attribute A is greater than 10?

- A. {t | t ∈ R ∧ t[A] > 10}

- B. σA>10(R)

- C. πA>10(R)

- D. {〈x〉 | R(x) ∧ x > 10}

- Answer: A

16. In tuple relational calculus, how do you express a query to find all tuples t such that there is no tuple
u in relation R where t[A] = u[B]?

- A. {t | t ∈ R ∧ ¬∃u ∈ R (t[A] = u[B])}

- B. σ¬∃u.B=A(R)
- C. π¬∃u.B=A(R)

- D. {t | R(t) ∧ ¬∃u (R(u) ∧ t[A] = u[B])}

- Answer: D

17. Which of the following is a key difference between relational algebra and relational calculus?

- A. Relational algebra is declarative, while relational calculus is procedural.

- B. Relational algebra specifies how to retrieve data, while relational calculus specifies what data to
retrieve.

- C. Relational calculus is used for updating data, while relational algebra is used for querying.

- D. Relational algebra supports recursion, while relational calculus does not.

- Answer: B

18. Which of the following relational calculus expressions correctly represents the query to find all
tuples where attribute A equals 20 in relation R?

- A. {t | R(t) ∧ t[A] = 20}

- B. {〈x〉 | R(x) ∧ x = 20}

- C. {t | t ∈ R ∧ t[A] = 20}

- D. σA=20(R)

- Answer: A

19. In domain relational calculus, how is a query to find all values of x from attribute A of relation R
where x > 30 represented?

- A. {x | ∃R (x > 30)}

- B. {〈x〉 | R(x) ∧ x > 30}

- C. {x | ∃t ∈ R (t[A] = x ∧ x > 30)}

- D. {〈x〉 | R(x, y) ∧ x > 30}

- Answer: C

20. In tuple relational calculus, how would you express a query to find all tuples t such that for some
tuple u in relation R, t[A] = u[B] and u[C] = 'Jones'?

- A. {t | R(t) ∧ ∃u ∈ R (t[A] = u[B] ∧ u[C] = 'Jones')}

- B. σ∃u.B=A ∧ C='Jones'(R)

- C. {t | t ∈ R ∧ ∃u ∈ R (t[A] = u[B] ∧ u[C] = 'Jones')}

- D. π∃u.B=A ∧ C='Jones'(R)

- Answer: A

Relational Algebra

1. Which of the following operations in relational algebra combines two relations and produces a new
relation containing tuples that are present in both input relations?

a) Selection

b) Projection

c) Cartesian Product

d) Outer Join

Answer: c) Cartesian Product

2. In relational algebra, which operation is used to select tuples from a relation that satisfy a certain
condition?

a) Projection

b) Outer Join

c) Selection

d) Aggregate

Answer: c) Selection

3. When applying a projection operation in relational algebra:

a) The resulting relation contains only selected attributes from the input relation

b) The resulting relation contains all attributes from the input relation

c) The resulting relation contains only the primary key attribute

d) The resulting relation contains attributes with NULL values removed

Answer: a) The resulting relation contains only selected attributes from the input relation

4. Which of the following relational algebra operations combines projection and selection?
a) Union

b) Intersection

c) Division

d) Natural Join

Answer: d) Natural Join

5. An inner join operation in relational algebra:

a) Combines tuples from two relations that satisfy a given condition

b) Combines all tuples from two relations, regardless of any condition

c) Combines tuples from two relations that do not satisfy a given condition

d) Combines tuples from two relations based on matching attribute values

Answer: d) Combines tuples from two relations based on matching attribute values

6. Which of the following operations in relational algebra is used to combine tuples from two relations,
including those that do not have matching attribute values?

a) Natural Join

b) Inner Join

c) Outer Join

d) Division

Answer: c) Outer Join

7. Set operations in relational algebra include:

a) Union, Intersection, Difference

b) Selection, Projection, Join

c) Cartesian Product, Outer Join, Inner Join

d) Aggregate, Division, Projection

Answer: a) Union, Intersection, Difference

8. The division operation in relational algebra:

a) Combines tuples from two relations based on matching attribute values

b) Retrieves tuples from one relation that are not present in another relation

c) Retrieves tuples from one relation that are associated with all tuples in another relation

d) Retrieves tuples from two relations that satisfy a given condition

Answer: c) Retrieves tuples from one relation that are associated with all tuples in another relation

9. Which of the following aggregate functions is commonly used in relational algebra?

a) MAX

b) UNION

c) DIFFERENCE

d) INNER JOIN

Answer: a) MAX

10. An outer join operation in relational algebra:

a) Combines only tuples with matching attribute values from two relations

b) Combines all tuples from two relations, filling in NULL values for non-matching tuples

c) Combines tuples from two relations that do not satisfy a given condition

d) Combines tuples from two relations based on a given condition, excluding non-matching tuples

Answer: b) Combines all tuples from two relations, filling in NULL values for non-matching tuples

11. Which relational algebra operation is used to combine tuples from two relations, keeping only the
tuples that appear in both relations?

a) Union

b) Intersection

c) Difference

d) Cartesian Product

Answer: b) Intersection

12. When performing a projection operation in relational algebra:

a) Duplicate tuples are removed from the result

b) All attributes from the input relation are included in the result
c) Only distinct attribute values are retained in the result

d) The resulting relation contains all tuples from the input relation

Answer: c) Only distinct attribute values are retained in the result

13. What is the result of the set difference operation in relational algebra?

a) Tuples that are common to both input relations

b) Tuples that appear in the first relation but not in the second relation

c) Tuples that appear in either of the input relations

d) Tuples that are present in both input relations

Answer: b) Tuples that appear in the first relation but not in the second relation

14. In relational algebra, what is the purpose of the division operation?

a) To retrieve tuples from one relation that satisfy a given condition

b) To retrieve tuples from one relation that are associated with all tuples in another relation

c) To combine tuples from two relations based on matching attribute values

d) To find the maximum value of an attribute in a relation

Answer: b) To retrieve tuples from one relation that are associated with all tuples in another relation

15. Which of the following is an example of an aggregate function in relational algebra?

a) Projection

b) Difference

c) COUNT

d) Natural Join

Answer: c) COUNT

16. In relational algebra, what does the Cartesian product operation produce?

a) Tuples that satisfy a given condition in both input relations

b) Tuples that appear in either of the input relations

c) All possible combinations of tuples from both input relations

d) Tuples that are common to both input relations

Answer: c) All possible combinations of tuples from both input relations

17. Which of the following set operations in relational algebra combines tuples from two relations while
eliminating duplicates?

a) Union

b) Intersection

c) Difference

d) Cartesian Product

Answer: a) Union

18. When performing an outer join operation in relational algebra:

a) Only tuples with matching attribute values from both relations are retained

b) Tuples from both relations are retained, filling in NULL values for non-matching tuples

c) Tuples that do not satisfy a given condition are eliminated

d) Only tuples from one relation are retained, excluding tuples from the other relation

Answer: b) Tuples from both relations are retained, filling in NULL values for non-matching tuples

19. Which of the following operations in relational algebra is used to combine tuples from two relations
based on common attribute values?

a) Difference

b) Intersection

c) Natural Join

d) Cartesian Product

Answer: c) Natural Join

20. What does the aggregate function SUM do in relational algebra?

a) Retrieves the sum of all attribute values in a relation

b) Retrieves the average of all attribute values in a relation

c) Retrieves the maximum value of an attribute in a relation

d) Retrieves the total number of tuples in a relation

Answer: a) Retrieves the sum of all attribute values in a relation

21. In relational algebra, what is the purpose of the projection operation?

a) To select tuples from a relation that satisfy a given condition

b) To combine tuples from two relations based on matching attribute values

c) To retrieve a subset of attributes from a relation

d) To remove duplicates from a relation

Answer: c) To retrieve a subset of attributes from a relation

22. Which of the following operations in relational algebra combines two relations and eliminates
duplicate tuples?

a) Union

b) Intersection

c) Difference

d) Cartesian Product

Answer: b) Intersection

23. When performing an outer join operation in relational algebra, which tuples are included in the
result?

a) Only tuples with matching attribute values from both relations

b) Tuples from both relations, including NULL values for non-matching tuples

c) Only tuples from the first relation

d) Only tuples from the second relation

Answer: b) Tuples from both relations, including NULL values for non-matching tuples

24. What is the result of the set union operation in relational algebra?

a) Tuples that are common to both input relations

b) Tuples that appear in either of the input relations

c) Tuples that appear in the first relation but not in the second relation
d) All tuples from both input relations, eliminating duplicates

Answer: d) All tuples from both input relations, eliminating duplicates

25. Which of the following is an example of an aggregate function that can be applied to a relation in
relational algebra?

a) Projection

b) Union

c) COUNT

d) Outer Join

Answer: c) COUNT

26. What does the division operation in relational algebra accomplish?

a) Retrieves tuples from one relation that are present in another relation

b) Retrieves tuples from one relation that are not present in another relation

c) Retrieves tuples from one relation that are associated with all tuples in another relation

d) Retrieves tuples from two relations that satisfy a given condition

Answer: c) Retrieves tuples from one relation that are associated with all tuples in another relation

27. When performing a natural join operation in relational algebra:

a) All attributes from both relations are included in the result

b) Only common attributes from both relations are included in the result

c) Tuples with matching attribute values from both relations are retained

d) Tuples with non-matching attribute values from both relations are retained

Answer: c) Tuples with matching attribute values from both relations are retained

28. Which of the following operations in relational algebra combines tuples from two relations, retaining
only the tuples that appear in the first relation but not in the second relation?

a) Intersection

b) Difference

c) Union
d) Cartesian Product

Answer: b) Difference

29. In relational algebra, what is the purpose of the aggregate function AVG?

a) Retrieves the sum of all attribute values in a relation

b) Retrieves the average of all attribute values in a relation

c) Retrieves the maximum value of an attribute in a relation

d) Retrieves the total number of tuples in a relation

Answer: b) Retrieves the average of all attribute values in a relation

30. When applying the projection operation in relational algebra, what happens to duplicate tuples?

a) They are removed from the result

b) They are retained in the result

c) They are combined with other tuples in the result

d) They are modified to include NULL values

Answer: a) They are removed from the result

Additional questions

1. Consider two relations, Employees and Departments, with the following schemas and sample data:

Employees (EmpID, Name, DeptID)

- EmpID: Employee ID (Primary Key)

- Name: Employee name

- DeptID: Department ID (Foreign Key)

Departments (DeptID, Name)

- DeptID: Department ID (Primary Key)

- Name: Department name

Which relational algebra operation would be used to retrieve the names of all employees who work in
the 'Sales' department?

a) Selection
b) Projection

c) Natural Join

d) Outer Join

Explanation:

To retrieve the names of all employees who work in the 'Sales' department, we need to select tuples
from the Employees relation where the DeptID matches the DeptID of the 'Sales' department in the
Departments relation.

Correct Answer: a) Selection

2. Consider two relations, Orders and Customers, with the following schemas and sample data:

Orders (OrderID, CustomerID, ProductID, Quantity)

- OrderID: Order ID (Primary Key)

- CustomerID: Customer ID (Foreign Key)

- ProductID: Product ID (Foreign Key)

- Quantity: Quantity of the product ordered

Customers (CustomerID, Name, City)

- CustomerID: Customer ID (Primary Key)

- Name: Customer name

- City: Customer city

Which relational algebra operation would be used to retrieve the names of all customers who have not
placed any orders?

a) Difference

b) Division

c) Outer Join

d) Projection

Explanation:

To retrieve the names of all customers who have not placed any orders, we need to find the set
difference between all customers and those who have placed orders.
Correct Answer: a) Difference

3. Consider two relations, Students and Courses, with the following schemas and sample data:

Students (StudentID, Name, Age)

- StudentID: Student ID (Primary Key)

- Name: Student name

- Age: Student age

Courses (CourseID, Name, Credits)

- CourseID: Course ID (Primary Key)

- Name: Course name

- Credits: Number of credits for the course

Which relational algebra operation would be used to retrieve the names of all students who are enrolled
in at least one course

a) Projection

b) Outer Join

c) Natural Join

d) Intersection

Explanation:

To retrieve the names of all students who are enrolled in at least one course, we need to perform a
natural join operation between the Students and Courses relations based on a common attribute such as
StudentID or CourseID.

Correct Answer: c) Natural Join

4. Consider two relations, Employees and Departments, with the following schemas and sample data:

Employees (EmpID, Name, DeptID)

- EmpID: Employee ID (Primary Key)

- Name: Employee name

- DeptID: Department ID (Foreign Key)

Departments (DeptID, Name)

- DeptID: Department ID (Primary Key)

- Name: Department name

Which relational algebra operation would be used to retrieve the names of all departments with no
employees?

a) Difference

b) Projection

c) Outer Join

d) Intersection

Example Data:

Employees:

| EmpID | Name | DeptID |

|-------|---------|--------|

|1 | Alice | 101 |

|2 | Bob | 102 |

|3 | Charlie | 101 |

|4 | David | 103 |

|5 | Eve | 102 |

Departments:

| DeptID | Name |

|--------|----------|

| 101 | Sales |

| 102 | Marketing|

| 103 | HR |

Answer: a) Difference

5. Consider two relations, Orders and Products, with the following schemas and sample data:
Orders (OrderID, ProductID, Quantity)

- OrderID: Order ID (Primary Key)

- ProductID: Product ID (Foreign Key)

- Quantity: Quantity of the product ordered

Products (ProductID, Name, Price)

- ProductID: Product ID (Primary Key)

- Name: Product name

- Price: Price of the product

Which relational algebra operation would be used to retrieve the names of all products that have not
been ordered?

a) Outer Join

b) Intersection

c) Difference

d) Natural Join

Example Data:

Orders:

| OrderID | ProductID | Quantity |

|---------|-----------|----------|

|1 | 101 |2 |

|2 | 102 |1 |

|3 | 103 |3 |

|4 | 101 |1 |

Products:

| ProductID | Name | Price |

|-----------|----------|-------|
| 101 | Laptop | 1000 |

| 102 | Phone | 800 |

| 103 | Tablet | 600 |

| 104 | Headphones| 100 |

Answer: c) Difference

3. Consider two relations, Students and Grades, with the following schemas and sample data:

Students (StudentID, Name)

- StudentID: Student ID (Primary Key)

- Name: Student name

Grades (StudentID, CourseID, Grade)

- StudentID: Student ID (Foreign Key)

- CourseID: Course ID (Foreign Key)

- Grade: Grade obtained by the student in the course

Which relational algebra operation would be used to retrieve the names of all students who have not
received any grades?

a) Outer Join

b) Projection

c) Difference

d) Intersection

Example Data:

Students:

| StudentID | Name |

|-----------|---------|

| 101 | Alice |

| 102 | Bob |

| 103 | Charlie |
| 104 | David |

Grades:

| StudentID | CourseID | Grade |

|-----------|----------|-------|

| 101 | 201 |A |

| 102 | 202 |B |

| 103 | 201 |B |

| 104 | 203 |A |

Answer: c) Difference

1: Consider a relation named "Employees" with attributes (EmpID, Name, Age, Department). Which of
the following relational algebra operations would be used to retrieve the names of all employees who
are younger than 30 years old?

a) Selection

b) Projection

c) Combined Projection and Selection

d) Cross Product

Answer: a) Selection

2: Consider a relation named "Students" with attributes (StudentID, Name, Age) and another relation
named "Courses" with attributes (CourseID, Name, Credits). Which relational algebra operation would
be used to retrieve the names of all students along with their corresponding course names?

a) Projection

b) Inner Join

c) Outer Join

d) Cross Product

Answer: b) Inner Join

3:Consider two relations, "Employees" and "Departments", with attributes (EmpID, Name,
DepartmentID) and (DepartmentID, DepartmentName) respectively. Which of the following operations
combines tuples from both relations based on matching attribute values?

a) Cross Product

b) Outer Join

c) Inner Join

d) Combined Projection and Selection

Answer: c) Inner Join

4: Consider a relation named "Orders" with attributes (OrderID, CustomerID, ProductID) and another
relation named "Customers" with attributes (CustomerID, Name, City). Which relational algebra
operation would be used to retrieve all orders along with the corresponding customer names?

a) Projection

b) Outer Join

c) Inner Join

d) Combined Projection and Selection

Answer: c) Inner Join

5: Consider two relations, "Employees" and "Projects", with attributes (EmpID, Name) and (ProjectID,
ProjectName) respectively. Which relational algebra operation would be used to retrieve all
combinations of employees and projects?

a) Selection

b) Projection

c) Cross Product

d) Inner Join

Answer: c) Cross Product

6: Consider a relation named "Orders" with attributes (OrderID, CustomerID, ProductID) and another
relation named "Products" with attributes (ProductID, ProductName, Price). Which operation in
relational algebra would be used to retrieve orders along with the corresponding product names?

a) Projection

b) Outer Join
c) Inner Join

d) Combined Projection and Selection

Answer: c) Inner Join

7:Consider two relations, "Students" and "Grades", with attributes (StudentID, Name) and (StudentID,
CourseID, Grade) respectively. Which relational algebra operation would be used to retrieve the names
of all students along with their corresponding grades?

a) Projection

b) Outer Join

c) Inner Join

d) Combined Projection and Selection

Answer: c) Inner Join

8: Consider a relation named "Employees" with attributes (EmpID, Name, DepartmentID) and another
relation named "Departments" with attributes (DepartmentID, DepartmentName). Which relational
algebra operation would be used to retrieve the names of all employees along with their corresponding
department names?

a) Projection

b) Outer Join

c) Inner Join

d) Combined Projection and Selection

Answer: c) Inner Join

9:Consider two relations, "Sales" and "Expenses", with attributes (Date, SaleAmount) and (Date,
ExpenseAmount) respectively. Which relational algebra operation would be used to retrieve the total
profit (SaleAmount - ExpenseAmount) for each date?

a) Projection

b) Selection

c) Difference

d) Combined Projection and Selection

Answer: d) Combined Projection and Selection

10: Consider a relation named "Students" with attributes (StudentID, Name) and another relation named
"Courses" with attributes (CourseID, Name). Which relational algebra operation would be used to
retrieve all combinations of students and courses?

a) Projection

b) Outer Join

c) Cross Product

d) Combined Projection and Selection

Answer : c) Cross Product