TRB PG Assistant - Database Security and Authorization - integrity constraints, check constraints, referential constraints, views and updates on views Online Test (MCQ Quiz)

1. Which type of constraint ensures that all values in a column are unique?

A) NOT NULL constraint B) PRIMARY KEY constraint C) UNIQUE constraint D) FOREIGN KEY constraint

2. What is the primary purpose of an integrity constraint in a database?

A) To improve query performance B) To enforce data accuracy and consistency C) To define relationships between tables D) To manage user access levels

3. A constraint that restricts the values that can be entered into a column based on a specific condition is called a:

A) Primary key constraint B) Foreign key constraint C) Unique constraint D) Check constraint

4. Which of the following is an example of a check constraint condition?

A) AGE > 18 B) EMPLOYEE_ID IS NOT NULL C) DEPARTMENT_ID = 101 D) SALARY IS UNIQUE

5. A referential constraint is typically implemented using which database object?

A) Trigger B) Index C) Foreign Key D) View

6. What does a referential constraint ensure between two tables?

A) That all values in the referencing column are unique B) That a value in the referencing column must exist in the referenced column of the other table C) That all values in the referenced column are not null D) That the referencing and referenced columns have the same data type

7. In a referential constraint, the table containing the referencing column is known as the:

A) Parent table B) Child table C) Referenced table D) Primary table

8. The table containing the referenced column in a referential constraint is called the:

A) Child table B) Referencing table C) Parent table D) Foreign table

9. What operation is typically forbidden on a referenced primary key if there are referencing foreign keys?

A) UPDATE B) DELETE C) INSERT D) SELECT

10. Which SQL clause is used to define a check constraint?

A) ALTER TABLE ... ADD CONSTRAINT ... CHECK B) CREATE TABLE ... CONSTRAINT ... CHECK C) MODIFY COLUMN ... CHECK D) SET CONSTRAINT ... CHECK

11. A view in a database is best described as:

A) A physical table storing data B) A stored query that presents data from one or more tables C) A temporary table created for calculation D) A security mechanism to encrypt data

12. What is a primary advantage of using views?

A) To reduce the amount of data stored B) To simplify complex queries and restrict data access C) To automatically index all underlying tables D) To enforce referential integrity

13. Can you perform an INSERT operation directly on a view?

A) Never B) Always C) Only if the view is based on a single table and includes all NOT NULL columns D) Only if the view is based on multiple tables

14. What is a potential issue when updating data through a view?

A) The underlying table's structure might change B) The integrity constraints on the underlying table might be violated C) The view definition itself might become invalid D) The database performance will always decrease

15. Which type of view is generally updatable?

A) A view containing aggregate functions (e.g., SUM, AVG) B) A view based on multiple tables joined together C) A simple view based on a single table D) A view that uses DISTINCT keyword

16. If a view is created using a JOIN operation, under what condition might it be updatable?

A) If the join condition is on primary keys B) If the join condition is on foreign keys C) If the update affects only one of the underlying tables and all necessary columns are included D) If the view includes columns from all joined tables

17. What does the `ON DELETE CASCADE` option in a foreign key constraint do?

A) Prevents deletion of rows in the parent table B) Deletes corresponding rows in the child table when a row in the parent table is deleted C) Sets the foreign key column to NULL when a row in the parent table is deleted D) Updates the foreign key column with a default value when a row in the parent table is deleted

18. The `ON UPDATE SET NULL` option for a foreign key constraint means:

A) The foreign key column is set to NULL when the referenced primary key is updated B) The primary key column is set to NULL when the referenced foreign key is updated C) The entire row is updated to NULL when the referenced primary key is updated D) The operation is not allowed if the primary key is updated

19. Which of the following is NOT a type of integrity constraint?

A) Primary Key B) Foreign Key C) Check Constraint D) Trigger

20. A database constraint that prevents inserting a value into a column if that value is not present in a specified list of allowed values is a type of:

A) Unique Constraint B) Referential Constraint C) Check Constraint D) Not Null Constraint

21. What is the primary benefit of using CHECK constraints?

A) To ensure relationships between tables B) To enforce data type rules C) To enforce domain integrity by limiting the range or set of values allowed D) To provide a simplified interface for users

22. When a view is based on a single table, and the update operation modifies a column that is part of the view's selection, what is the most likely outcome?

A) The operation will fail B) The underlying table will be updated C) A new row will be inserted into the underlying table D) The view definition will be altered

23. Which of the following statements about updating views is TRUE?

A) All views are updatable regardless of their complexity. B) Views based on multiple tables are always updatable. C) Updatability of a view depends on its definition and the underlying tables. D) Only views containing SELECT * are updatable.

24. Consider a table `Employees` with columns `EmployeeID` (PK), `Name`, `DepartmentID` (FK referencing `Departments.DepartmentID`), and `Salary`. Which of the following is a valid CHECK constraint for the `Salary` column?

A) CHECK (Salary > 0) B) CHECK (Salary IS NOT NULL) C) CHECK (Salary = 50000) D) CHECK (Salary < 100000)

25. If a `FOREIGN KEY` constraint is defined with `ON DELETE RESTRICT`, what happens if you try to delete a row from the parent table that has related rows in the child table?

A) The row in the parent table is deleted, and related rows in the child table are also deleted. B) The row in the parent table is deleted, and related rows in the child table have their foreign key set to NULL. C) The deletion of the row in the parent table is prevented and an error is raised. D) The deletion is allowed, and the foreign key values in the child table are updated.

26. What is the purpose of the `NOT NULL` constraint?

A) To ensure all values in a column are unique. B) To ensure that a column cannot have a NULL value. C) To define a default value for a column. D) To enforce a relationship with another table.

27. A database designer wants to ensure that every `order_id` entered into the `Orders` table must correspond to an existing `customer_id` in the `Customers` table. Which constraint should be used?

A) UNIQUE constraint on `Orders.order_id` B) CHECK constraint on `Orders.customer_id` C) FOREIGN KEY constraint on `Orders.customer_id` referencing `Customers.customer_id` D) NOT NULL constraint on `Orders.customer_id`

28. Which of the following SQL statements would create a view named `ActiveCustomers` showing customers from the `Customers` table whose `status` is 'Active'?

A) CREATE VIEW ActiveCustomers AS SELECT * FROM Customers WHERE status = 'Active'; B) CREATE TABLE ActiveCustomers AS SELECT * FROM Customers WHERE status = 'Active'; C) CREATE VIEW ActiveCustomers (SELECT * FROM Customers WHERE status = 'Active'); D) CREATE ACTIVE_CUSTOMERS AS SELECT * FROM Customers WHERE status = 'Active';

29. If a view is defined as `CREATE VIEW HighSalaryEmployees AS SELECT EmployeeID, Name FROM Employees WHERE Salary > 100000;`, can `EmployeeID` be updated through this view?

A) Yes, always. B) No, because `EmployeeID` is a primary key. C) Yes, if `EmployeeID` is not part of the WHERE clause. D) No, because the view only selects `EmployeeID` and `Name`, not `Salary`.

30. Which integrity constraint enforces entity integrity?

A) Foreign Key constraint B) Unique constraint C) Primary Key constraint D) Check constraint

31. A constraint that ensures that no two rows in a table have the same value in a specified column (or set of columns) is a:

A) Primary Key constraint B) Unique constraint C) Check constraint D) Foreign Key constraint

32. What is the main difference between a PRIMARY KEY constraint and a UNIQUE constraint?

A) A PRIMARY KEY allows NULL values, while a UNIQUE constraint does not. B) A UNIQUE constraint allows NULL values, while a PRIMARY KEY does not. C) A PRIMARY KEY can have multiple columns, while a UNIQUE constraint can only have one. D) A PRIMARY KEY automatically creates an index, while a UNIQUE constraint does not.

33. Consider a view `ProductSummary` created from `Products` and `Categories` tables. If `ProductSummary` includes columns from both tables, and an update is attempted on a column that exists only in the `Products` table, the update is:

A) Always successful. B) Successful if the `Products` table is the primary table in the join. C) Successful if the update affects only one row in the `Products` table and all required columns are present. D) Never successful.

34. Which of the following actions is typically restricted by a referential constraint when attempting to delete a record from the parent table?

A) Deleting the parent record if no child records exist. B) Deleting the parent record if child records exist. C) Updating the parent record. D) Inserting a new parent record.

35. What is the purpose of defining `ON DELETE SET NULL` for a foreign key?

A) To prevent deletion of the parent record. B) To delete the child records when the parent is deleted. C) To set the foreign key column in the child record to NULL when the parent record is deleted. D) To update the foreign key column with a default value when the parent is deleted.

36. A `CHECK` constraint can be used to enforce:

A) Uniqueness of values in a column. B) The presence of a value in another table. C) A specific data type for a column. D) A condition on the values allowed in a column.

37. If a view is defined using an aggregate function like `COUNT(*)`, can it be updated?

A) Yes, if the underlying table is simple. B) No, views with aggregate functions are generally not updatable. C) Yes, if the `GROUP BY` clause is used. D) Only if the underlying table has a primary key.

38. Which of the following scenarios would typically prevent a view from being updatable?

A) The view selects columns from a single table. B) The view uses the `DISTINCT` keyword. C) The view selects all columns from the underlying table. D) The view includes a `WHERE` clause filtering rows.

39. What is the fundamental role of authorization in database security?

A) To encrypt all data within the database. B) To control who can access what data and perform which operations. C) To ensure data consistency through constraints. D) To optimize query execution plans.

40. Which SQL command is used to grant specific privileges to a user?

A) REVOKE B) DENY C) GRANT D) ALTER USER

41. Which SQL command is used to remove privileges from a user?

A) GRANT B) REVOKE C) DELETE D) DROP USER

42. A user having `SELECT` privilege on a table can:

A) Insert new rows into the table. B) Delete rows from the table. C) Read data from the table. D) Modify existing data in the table.

43. A user having `INSERT` privilege on a table can:

A) Read data from the table. B) Delete rows from the table. C) Add new rows to the table. D) Modify existing data in the table.

44. Which privilege allows a user to modify existing data in a table?

A) SELECT B) INSERT C) UPDATE D) DELETE

45. Which privilege allows a user to remove rows from a table?

A) SELECT B) INSERT C) UPDATE D) DELETE

46. If a user has `UPDATE` privilege on a table but not `INSERT` privilege, they can:

A) Add new records but not change existing ones. B) Change existing records but not add new ones. C) Add and change records. D) Neither add nor change records.

47. What does it mean to grant `ALL PRIVILEGES` on a table to a user?

A) The user can only read the table. B) The user can perform all allowed operations (SELECT, INSERT, UPDATE, DELETE, etc.) on the table. C) The user can modify the table's structure. D) The user cannot perform any operations on the table.

48. Which of the following is a common way to implement authorization in a database system?

A) Using check constraints for all operations. B) Implementing a role-based access control (RBAC) system. C) Encrypting the entire database file. D) Disabling all DDL operations.

49. When updating data through a view that involves a join, if the update affects columns from multiple base tables, what is a common requirement for the view to be updatable?

A) The view must include all primary keys from all base tables. B) Each row in the view must correspond to exactly one row in each base table. C) The join must be an outer join. D) The view must not contain any WHERE clause.

Database Security and Authorization - integrity constraints, check constraints, referential constraints, views and updates on views - Online Test

Test Results