Schema Design Questions

Ritik Yadav

Score: 🔥 35

Beginner

Took 10 minutes

Coding Principles

Schema Design

80%

Questions Attempted

10 / 10

Correct Answers

Incorrect Answers

Points Earned

🔥 8

Question 1

To change the data type of a column in an existing table, which clause should be used with the ALTER TABLE statement?

CHANGE COLUMN
UPDATE COLUMN
MODIFY COLUMN
TRANSFORM COLUMN

Explanation:

The MODIFY COLUMN clause within an ALTER TABLE statement is used to alter the definition of an existing column, including changing its data type.

Question 2

You have a table with columns for StudentID, StudentName, CourseID, and CourseName. StudentID determines StudentName, and CourseID determines CourseName. What normal form is this table in?

First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
None of the above

Explanation:

The table is in 1NF because it likely has atomic values and no repeating groups. However, it's not in 2NF because CourseName depends only on CourseID, not the entire primary key (if it's a composite key of StudentID and CourseID).

Question 3

In which scenario might a Snowflake Schema be preferred over a Star Schema?

When storage space is a major concern
When query performance is the top priority
When data integrity is critical, and redundancy needs to be minimized
When dealing with small datasets

Explanation:

The normalization in Snowflake Schemas reduces data duplication, which is beneficial for maintaining consistency and accuracy in the data warehouse.

Question 4

What is a potential drawback of using a Snowflake Schema compared to a Star Schema?

Increased data redundancy
More complex queries due to normalization
Slower query performance due to denormalization
Limited ability to model complex relationships

Explanation:

The normalized structure of Snowflake Schemas, while promoting data integrity, can lead to more joins in queries, potentially impacting performance.

Question 5

What is the primary purpose of a Primary Key in a database table?

To establish a relationship with another table.
To ensure that all columns in the table have a value.
To uniquely identify each row within the table.
To define the order in which data is stored.

Explanation:

A Primary Key serves as a unique identifier for each row in a table. It guarantees that no two rows will have the same Primary Key value, ensuring data integrity and enabling efficient data retrieval.

Question 6

What happens to records in the 'Orders' table with 'ON UPDATE CASCADE' if a customer's ID is updated in the 'Customers' table?

The corresponding customer IDs in the 'Orders' table are automatically updated.
The 'Orders' table is unaffected, and the old customer ID remains.
An error occurs, and the update operation on the 'Customers' table is rolled back.
The corresponding orders are deleted from the 'Orders' table.

Explanation:

With 'ON UPDATE CASCADE', when a customer's ID (the primary key) in the 'Customers' table is updated, the corresponding customer IDs in the 'Orders' table (the foreign key) are automatically updated to maintain referential integrity.

Question 7

Which data validation technique prevents invalid data from being entered into the database by checking it against predefined rules?

Data Masking
Data Scrubbing
Data Transformation
Check Constraints

Explanation:

Check constraints define rules at the column level to validate data upon insertion or updates, ensuring that only data meeting the specified criteria is allowed.

Question 8

What is the key difference between a database and a schema?

A database is a collection of tables, while a schema defines the structure of a single table.
A database stores the actual data, while a schema defines the rules for accessing that data.
A database is a broader concept encompassing one or more schemas, each defining a logical structure within the database.
There is no difference; the terms are interchangeable.

Explanation:

A database can be thought of as a container holding one or more schemas. Each schema represents a logical grouping of database objects like tables, views, and constraints, organizing the data within the database.

Question 9

What is the role of a column in a database table?

To define a row in the table.
To represent a specific attribute or characteristic of an entity.
To link the table to another table.
To store the entire dataset of the table.

Explanation:

Each column in a database table corresponds to a specific attribute or property of the entities represented in the table. For instance, in a table of employees, columns might include 'EmployeeID,' 'FirstName,' 'LastName,' etc.

Question 10

Which of the following is a key benefit of using a well-designed schema for a data warehouse?

Increased data redundancy for backups
Simplified data loading from various sources
Improved query performance and efficiency
Reduced need for data modeling and analysis

Explanation:

A well-structured schema, particularly Star or Snowflake, is optimized for analytical queries, allowing for faster retrieval of insights from large datasets.