Section A — 1 Mark Questions
1. Define a Relation in the context of the Relational Model. [1 Mark]
Answer: A relation is a table with columns and rows, representing a set of tuples that share common attributes.
2. What does DDL stand for in SQL? [1 Mark]
Answer: Data Definition Language.
3. Which SQL statement is used to remove a table from a database? [1 Mark]
Answer: DROP TABLE statement.
4. What is the primary purpose of a View? [1 Mark]
Answer: To provide a virtual table that simplifies complex queries or restricts access to sensitive data.
5. Name one fundamental relational algebra operation. [1 Mark]
Answer: Selection (σ).
6. What is the function of the Projection (π) operator? [1 Mark]
Answer: It selects specific columns from a relation while discarding others.
7. Which SQL command is used to modify existing records in a table? [1 Mark]
Answer: UPDATE command.
8. What is a Tuple in a database table? [1 Mark]
Answer: A tuple represents a single row in a database relation.
9. What does the 'Union' operation do in relational algebra? [1 Mark]
Answer: It combines the results of two relations, removing duplicate rows.
10. Identify the SQL command used to add new data to a table. [1 Mark]
Answer: INSERT INTO statement.
11. What is an Attribute? [1 Mark]
Answer: An attribute represents a named column in a database relation.
12. What is a Cartesian Product? [1 Mark]
Answer: A binary operation that combines every row of one table with every row of another table.
13. Which SQL statement is used to retrieve data from a database? [1 Mark]
Answer: SELECT statement.
14. What is the role of the RENAME operator? [1 Mark]
Answer: It allows assigning a new name to a relation or an attribute.
15. What is an Inner Join? [1 Mark]
Answer: An operation that returns rows when there is a match in both tables based on a join condition.
16. Define the term 'Schema' in a relational database. [1 Mark]
Answer: The logical structure or blueprint of the database.
17. What does the 'Set Difference' operator produce? [1 Mark]
Answer: It returns tuples that are in the first relation but not in the second.
18. What is the purpose of the 'DELETE' DML command? [1 Mark]
Answer: To remove one or more rows from a table based on a condition.
19. What is a Left Outer Join? [1 Mark]
Answer: A join that returns all records from the left table and matched records from the right table.
20. What is the purpose of the 'ALTER' DDL command? [1 Mark]
Answer: To modify the structure of an existing database object, such as adding or dropping columns.
Section B — 2/3 Mark Questions
1. Differentiate between DDL and DML. [2 Marks]
Answer: DDL (Data Definition Language) is used to define or alter the database structure (e.g., CREATE, ALTER), whereas DML (Data Manipulation Language) is used to manage and manipulate the data within the tables (e.g., INSERT, UPDATE, DELETE).
2. Explain the purpose of the Selection (σ) operator in relational algebra with an example. [2 Marks]
Answer: The selection operator filters rows based on a given predicate. Example: σage>20(Students) retrieves all rows from the 'Students' table where the age is greater than 20.
3. Why are Views considered beneficial? [3 Marks]
Answer: Views are beneficial because they: (i) Provide security by hiding sensitive columns, (ii) Simplify complex queries by storing them as virtual tables, and (iii) Maintain physical data independence by decoupling the user's view from the underlying table structure.
4. Describe the Cartesian Product operation. [2 Marks]
Answer: The Cartesian Product (denoted by ×) combines all rows of the first relation with all rows of the second. If table A has 'n' rows and table B has 'm' rows, the resulting table will have 'n × m' rows.
5. Explain the difference between INNER JOIN and FULL OUTER JOIN. [3 Marks]
Answer: An INNER JOIN returns only the rows where there is a match in both tables. A FULL OUTER JOIN returns all rows from both tables, filling with NULL values where no match exists on either side.
6. List three DDL commands and their functions. [3 Marks]
Answer: (i) CREATE: Used to create a new database or table. (ii) ALTER: Used to modify the structure of an existing database object. (iii) DROP: Used to permanently delete a database or table.
7. Explain the concept of Projection (π). [2 Marks]
Answer: Projection is a unary operation used to choose specific columns from a relation. It acts as a vertical filter that results in a new relation containing only the specified attributes.
8. What is the function of the SET DIFFERENCE operator? [2 Marks]
Answer: It takes two relations and returns a set containing tuples that appear in the first relation but not in the second, denoted as R - S.
9. How does a Right Outer Join differ from a Left Outer Join? [3 Marks]
Answer: A Left Outer Join retains all rows from the 'left' table, while a Right Outer Join retains all rows from the 'right' table. Both fill in missing matches with NULLs, but the primary orientation of which table preserves all its data differs.
10. State the significance of the Relational Model. [2 Marks]
Answer: The relational model provides a structured way to store data in tables based on the concept of relations, allowing for data integrity and efficient querying through standardized languages like SQL.
Section C — 5 Mark Questions
1. Explain the structure of Relational Databases in detail. [5 Marks]
Answer:
Overview
A relational database is based on the relational model, which organizes data into tables (relations). Each table represents an entity, and rows represent instances of that entity.
Core Components
- Relations: The table itself, which must have a unique name and contain unique tuples.
- Attributes: The columns of the table, defining the properties of the entity.
- Tuples: The rows of the table, containing specific values for each attribute.
- Domain: The set of permissible values for a given attribute.
- Keys: Primary keys ensure that each row can be uniquely identified, while foreign keys maintain relationships between tables.
Importance
This structure allows for high levels of data consistency and supports complex relationships, making it the industry standard for database management.
2. Discuss fundamental relational algebra operations. [5 Marks]
Answer:
Introduction
Relational algebra is a procedural query language that takes instances of relations as input and yields instances of relations as output.
Fundamental Operations
- Selection (σ): Selects rows based on a condition; used for horizontal filtering.
- Projection (π): Selects specific columns; used for vertical filtering.
- Union (∪): Combines all tuples from two relations, removing duplicates.
- Set Difference (-): Finds tuples present in the first relation but absent in the second.
- Cartesian Product (×): Pairs every row of one relation with every row of another.
Conclusion
These operations form the basis for more complex queries and SQL operations, enabling efficient manipulation and retrieval of data within a database system.
3. Elaborate on DML statements in SQL. [5 Marks]
Answer:
Introduction
Data Manipulation Language (DML) statements are the commands used to interact with the data contained within the database tables.
Key DML Statements
- SELECT: Used to retrieve data from one or more tables based on defined criteria. It is the most frequently used DML command.
- INSERT: Used to add one or more new records (rows) into an existing table.
- UPDATE: Used to modify existing data within a row or across multiple rows that meet specific conditions.
- DELETE: Used to remove specific rows from a table based on a 'WHERE' clause. If no condition is specified, all rows may be removed.
Application
DML commands are essential for applications that require dynamic interaction with data, such as user registration forms, inventory updates, and report generation.
4. Compare different types of JOINS with examples. [5 Marks]
Answer:
| Join Type | Description |
|---|---|
| Inner Join | Returns only rows with matches in both tables. |
| Left Join | Returns all rows from the left table and matching rows from the right. |
| Right Join | Returns all rows from the right table and matching rows from the left. |
| Full Join | Returns all rows when there is a match in either the left or right table. |
Detailed Explanation
Joins are used to combine records from two or more tables in a database. Without joins, data would need to be stored in a single, massive table, which leads to redundancy. By using common fields (foreign keys), joins enable the reconstruction of related information from separate tables, ensuring that the database remains normalized and efficient.
5. Discuss the role of Views and DDL statements. [5 Marks]
Answer:
DDL Statements
Data Definition Language (DDL) is used to define or modify the structure of the database. Commands like CREATE, ALTER, and DROP define the 'schema'—the shape, type, and relationships of the data container. For example, ALTER TABLE allows adding a column to reflect new business requirements.
Views
A View is a virtual table that does not exist physically but is generated by a query. It is defined using DDL-like syntax (CREATE VIEW). Views are crucial for:
- Data Hiding: Masking complex logic or sensitive columns from users.
- Simplification: Users query the view instead of writing complex multi-join SQL queries.
- Consistency: Providing a consistent interface even if the underlying table structure changes.
Together, DDL and Views ensure that the database is both structurally sound and secure for the end-user.