module 4

Multiple Choice

Which of the following best describes a view in MySQL?

• A copy of the data stored in a table

• A logical window into the data stored in underlying tables

• A physical backup of the database

• A temporary table created for each query

Open Ended

Explain two important benefits of using views in a database system.

Multiple Select

Which of the following are true about views in SQL? Select all that apply.

• Views do not duplicate data

• Views always display current data when opened

• Only one view can be created per table

• The name of a view can be used similarly to a table name

Fill in the Blank

Fill in the blank: The SQL command to create a view starts with ___ VIEW view_name AS.

Open Ended

Given the table 'Students' and the view 'data_science_students', what would be the result of the query: SELECT * FROM data_science_students?

Multiple Choice

Which SQL command would you use to remove the view 'data_science_students' from the database?

• DELETE VIEW data_science_students;

• REMOVE VIEW data_science_students;

• DROP VIEW data_science_students;

• ERASE VIEW data_science_students;

Multiple Choice

What is the main purpose of using indexes in a database?

• To increase the speed of retrieving data

• To store more data

• To ensure data security

• To reduce the size of the database

Multiple Choice

Which of the following is a primary benefit of creating indexes in a relational database system?

• Improving database performance

• Increasing storage requirements

• Reducing data redundancy

• Enhancing user interface design

Multiple Choice

Which SQL command is used to delete an index from a table?

• REMOVE INDEX

• DELETE INDEX

• DROP INDEX

• CLEAR INDEX

Open Ended

Explain the difference between a single-field index and a multiple-field (composite) index in SQL.

Fill in the Blank

Fill in the blank: The SQL statement to create an index on the 'department' column of the Employees table is CREATE INDEX idx_department ON Employees (___);

Multiple Select

Select all statements that are true about how indexes affect query performance.

• Indexes speed up data retrieval in large tables.

• Indexes always reduce the size of the database.

• Indexes require processing resources to maintain.

• Without indexes, queries may require a full table scan.

Open Ended

Describe a scenario where using a composite index would be more beneficial than a single-field index.

Multiple Choice

Which SQL command would you use to view all indexes on the Employees table?

• SHOW INDEX FROM Employees;

• SELECT INDEXES FROM Employees;

• LIST INDEXES ON Employees;

• DISPLAY INDEXES Employees;

Open Ended

Explain the importance of physical security in database management and provide two examples of how it can be implemented.

Multiple Select

Which of the following statements about entity integrity and referential integrity is/are correct? Select all that apply.

• Entity integrity ensures primary keys cannot be null.

• Referential integrity ensures foreign keys match primary keys in another table.

• Entity integrity allows duplicate primary keys.

• Referential integrity is not related to foreign keys.

Fill in the Blank

Legal-values integrity in a database ensures that only ___ values are accepted in a field.

Open Ended

Given the table definitions for Departments and Employees, how does the FOREIGN KEY constraint help maintain data integrity between these tables?

Multiple Choice

What will happen if you try to insert an employee with a dept_id that does not exist in the Departments table?

• The insertion will fail due to referential integrity rules.

• The employee will be added with a null dept_id.

• The employee will be added, but the dept_id will be ignored.

• The insertion will succeed without any errors.

Fill in the Blank

To add a new field to a table in Access, you can use the ___ in Table Design View.

Multiple Select

Which of the following operations can be performed using the SQL ALTER TABLE command?

• Adding and removing tables

• Adding and removing fields

• Creating and dropping indexes

• All of the above

Multiple Choice

Which SQL command would you use to change the datatype of an existing column in a table?

• MODIFY column_name datatype

• ADD column_name datatype

• DROP COLUMN column_name

• RENAME COLUMN old_name TO new_name

Open Ended

Explain the difference between INNER JOIN and LEFT JOIN in SQL. Provide an example scenario where each would be used.

Open Ended

What steps should you take if you encounter orphan records when trying to enforce referential integrity?

Multiple Choice

Which of the following is a benefit of creating indexes in a relational database system?

• Improves database performance

• Increases data redundancy

• Slows down query processing

• Reduces data security