NEW
Font size
WorksheetsUnderstanding DBMS Keys and Constraints
Total questions: 20
Worksheet time: 10mins
What is a primary key in a database?
A primary key is a unique identifier for a record in a database.
A primary key is used to store large text data.
A primary key can be duplicated across records.
A primary key is a type of foreign key.
Define a foreign key and its purpose.
A foreign key is a type of index that speeds up queries.
A foreign key is used to delete records from a table.
A foreign key is a unique identifier for a single table.
A foreign key is a field that links two tables by referencing the primary key of another table, ensuring data integrity.
What are the characteristics of a candidate key?
A candidate key can be duplicated across rows.
A candidate key must be unique, irreducible, and non-nullable.
A candidate key must be a composite key.
A candidate key can contain null values.
Explain the concept of a composite key.
A composite key is a single column that uniquely identifies a record.
A composite key is a unique identifier made up of multiple columns in a database.
A composite key is used to store images in a database.
A composite key is a type of foreign key that links to another table.
What is the difference between a primary key and a unique key?
A primary key can be null and does not enforce uniqueness.
A primary key cannot be null and uniquely identifies records, while a unique key can be null and also enforces uniqueness.
A primary key can have duplicate values but must be unique across tables.
A unique key can only be used in one table and cannot be referenced by foreign keys.
What are domain constraints in a database?
Domain constraints define the relationships between tables.
Domain constraints are used to enforce referential integrity.
Domain constraints determine the structure of the database schema.
Domain constraints specify the valid values for attributes in a database.
How do referential integrity constraints work?
Referential integrity constraints maintain consistency between related tables by enforcing rules on foreign key relationships.
They only apply to primary keys in a single table.
They allow any data to be entered without restrictions.
They are used to optimize database performance.
What happens if a foreign key constraint is violated?
The operation is rejected, maintaining referential integrity.
The operation is completed successfully, ignoring the constraint.
The database automatically deletes the related records.
A warning is issued, but the operation proceeds anyway.
Can a primary key be null? Why or why not?
Yes, a primary key can be null.
A primary key can be null if it is part of a composite key.
A primary key can be null if it is defined as optional.
No, a primary key cannot be null.
What is the role of a surrogate key in a database?
The role of a surrogate key in a database is to provide a unique identifier for records that is independent of the actual data.
A surrogate key is used to store large amounts of data.
A surrogate key is a foreign key that links tables together.
A surrogate key is a type of index that speeds up queries.
Describe the difference between entity integrity and referential integrity.
Entity integrity focuses on unique and non-null primary keys, while referential integrity ensures valid foreign key relationships between tables.
Entity integrity is concerned with data normalization, while referential integrity deals with data encryption.
Entity integrity allows duplicate primary keys, whereas referential integrity does not allow null values.
Entity integrity is about data types, while referential integrity is about indexing.
What is a natural key? Provide an example.
A natural key is a key that has a real-world meaning, such as a Social Security Number (SSN).
A natural key is a key that has no relation to real-world entities, such as a primary key.
A natural key is a key that is randomly generated, like a UUID.
A natural key is a key that is only used internally within a database.
How can domain constraints improve data quality?
Domain constraints improve data quality by enforcing rules that prevent invalid data entries.
They allow for more flexible data entry.
They increase the amount of data stored.
They automatically correct data errors.
What is a self-referencing foreign key?
A self-referencing foreign key is a foreign key that points to a different table.
A self-referencing foreign key is a primary key that does not reference any table.
A self-referencing foreign key is a foreign key that points to a view.
A self-referencing foreign key is a foreign key that points to the same table.
Explain the significance of normalization in relation to keys.
Normalization increases data duplication and complexity.
Normalization is only relevant for non-relational databases.
Normalization is significant as it ensures that keys maintain data integrity and reduce redundancy.
Normalization has no impact on the efficiency of keys.
What is a check constraint? Give an example.
CREATE TABLE Users (ID int, Age int NOT NULL);
Example: CREATE TABLE Users (ID int, Age int CHECK (Age >= 18));
CREATE TABLE Users (ID int, Age int UNIQUE);
CREATE TABLE Users (ID int, Age int DEFAULT 0);
How do you enforce referential constraints in SQL?
Create indexes to enforce referential constraints.
Use foreign keys to enforce referential constraints.
Use triggers to enforce referential constraints.
Use primary keys to enforce referential constraints.
What is the purpose of a unique constraint?
To allow duplicate values in a database column.
To enforce uniqueness of values in a database column.
To delete records from a database table.
To create indexes for faster data retrieval.
Can a table have multiple foreign keys?
Yes, but only if the foreign keys reference the same table.
Yes, a table can have multiple foreign keys.
No, a table can only have one foreign key.
No, foreign keys are not allowed in tables.
What is the impact of cascading updates on referential constraints?
Cascading updates only affect primary keys in the same table.
Cascading updates require manual intervention to maintain referential integrity.
Cascading updates delete child records when parent records are modified.
Cascading updates maintain referential integrity by automatically updating foreign keys in child tables when primary keys in parent tables are changed.
