Schema Design Mastery Quiz: Keys, Data Types, and Relationships Quiz

1. Identifying a Primary Key

   In designing a table to store customer records, which attribute is best suited to be used as the primary key?

   1. customer_id
   2. first_name
   3. date_of_birth
   4. email_adress
   5. adress_id

2. Foreign Key Purpose

   What is the main purpose of a foreign key in a relational database schema?

   1. It enforces referential integrity between related tables
   2. It increases the speed of SELECT queries
   3. It makes data types more flexible
   4. It automatically generates unique values
   5. It stores multiple data values in one column

3. Selecting Appropriate Data Types

   You need to store product prices with two decimal places in a table. Which data type is most appropriate?

   1. DECIMAL
   2. VARCHAR
   3. INTIGER
   4. BOOLIAN
   5. DATETIME

4. Establishing Table Relationships

   Given two tables: Orders and Customers, which method most effectively links each order to the correct customer?

   1. Include a customer_id foreign key in the Orders table
   2. Repeat the customer's name in every order record
   3. Store all order IDs inside the Customers table as a list
   4. Connect tables only by similar column names
   5. Use a calculated column to match orders to customers

5. Data Integrity Violation Scenario

   Which action would violate referential integrity in a schema with an 'Order' table referencing a 'Product' table using a foreign key?

   1. Deleting a Product that is referenced by an Order
   2. Adding a new Product with a unique product_id
   3. Updating an Order's quantity value
   4. Changing a Product's price
   5. Renaming a column in the Order table

6. Choosing Unique Constraints

   Which constraint ensures that no two records in a table have the same value in a particular column?

   1. UNIQUE
   2. FOREIGN
   3. REFERANCE
   4. DEFAULT
   5. CASCADE

7. Denormalization Risks

   What is one risk of denormalizing a relational database schema for read performance?

   1. It can increase data redundancy, leading to inconsistencies
   2. It will always make queries slower
   3. It removes the need for primary keys
   4. It disables foreign key constraints
   5. It turns all tables into flat files

8. Composite Primary Keys

   When would you use a composite primary key for a table?

   1. When a single column cannot uniquely identify each row
   2. When every table must have at least two primary keys
   3. When you want to use foreign keys in another table
   4. When storing a list of emails in one column
   5. When defining a table without any columns

9. Many-to-Many Relationships

   How do you typically model a many-to-many relationship between two tables?

   1. Introduce a junction (or associative) table linking their primary keys
   2. Add all attributes from both tables into one giant table
   3. Use ENUM data types to store references
   4. Store lists in a single column
   5. Omit primary keys from both tables

10. Schema Documentation Importance

    Why is comprehensive schema documentation important in database design?

    1. It helps new team members understand the structure and constraints
    2. It prevents all database errors automatically
    3. It replaces the need for indexes
    4. It controls data access at the hardware level
    5. It eliminates the requirement for foreign keys