Database Design Principles: Master Database Schemas, Table Design, and Indexing

Question 1

Which of the following is NOT a key principle of database design?

Accepted Answer

Redundancy

Answer

Simplicity

Answer

Normalization

Answer

Flexibility

Question 2

What is the primary purpose of a primary key in a database table?

Accepted Answer

To uniquely identify each row in the table

Answer

To enforce data integrity constraints

Answer

To improve query performance

Answer

To store additional information about each row

Question 3

In database normalization, which normal form guarantees that all non-key attributes are fully dependent on the primary key?

Accepted Answer

Boyce-Codd Normal Form (BCNF)

Answer

First Normal Form (1NF)

Answer

Second Normal Form (2NF)

Answer

Third Normal Form (3NF)

Question 4

What is a key benefit of utilizing an index in a database table?

Accepted Answer

Enhanced data retrieval speed

Answer

Reduced storage space requirements

Answer

Improved data integrity

Answer

Enhanced data security

Question 5

For queries involving range searches, which type of index is most appropriate?

Accepted Answer

B-tree index

Answer

Hash index

Answer

Bitmap index

Answer

Full-text index

Question 6

What is the key distinction between a clustered index and a non-clustered index?

Accepted Answer

A clustered index physically sorts the data in the table based on the index key, while a non-clustered index does not.

Answer

A non-clustered index physically sorts the data in the table based on the index key, while a clustered index does not.

Answer

Both clustered and non-clustered indexes physically sort the data in the table based on the index key.

Answer

Neither clustered nor non-clustered indexes physically sort the data in the table based on the index key.

Question 7

What is the key role of a data dictionary in database design?

Accepted Answer

Storing metadata about the database

Answer

Enforcing data integrity

Answer

Improving query performance

Answer

Providing documentation for the database

Question 8

What is the primary purpose of a foreign key in a database table?

Accepted Answer

Enforcing referential integrity between tables

Answer

Creating a one-to-one relationship between tables

Answer

Creating a one-to-many relationship between tables

Answer

Creating a many-to-many relationship between tables

Question 9

Which of the following is NOT a fundamental aspect of database design principles?

Accepted Answer

Network topology

Answer

Data modeling

Answer

Normalization

Answer

Index design

Question 10

The process of dividing a database into smaller, related tables to reduce data redundancy and enhance data integrity is known as:

Accepted Answer

Normalization

Answer

Denormalization

Answer

Optimization

Answer

Indexing

Question 11

Consider the following table: Customers (customer_id, customer_name). Which of the following would be an appropriate index to optimize queries based on customer ID?

Accepted Answer

Index on customer_id

Answer

Index on customer_name

Answer

Index on both customer_id and customer_name

Answer

No index is necessary

Question 12

A database schema is a:

Accepted Answer

Logical representation of the database structure

Answer

Physical implementation of the database

Answer

Collection of data stored in the database

Answer

Set of rules for accessing and modifying data

Question 13

Which of the following is a fundamental principle of database design that aims to eliminate data duplication and inconsistencies?

Accepted Answer

Normalization

Answer

Denormalization

Answer

Inconsistent data

Answer

Redundancy

Question 14

When designing a table schema, what is the primary objective that should guide the design process?

Accepted Answer

Minimizing data duplication while preserving data integrity

Answer

Maximizing data access speed regardless of data consistency

Answer

Reducing storage requirements at the expense of data redundancy

Answer

Ensuring data integrity even if it compromises data retrieval efficiency

Question 15

What is the primary purpose of an index in a database system?

Accepted Answer

To enhance data retrieval speed by creating a fast lookup mechanism

Answer

To enforce data integrity by preventing invalid data from being inserted

Answer

To reduce data storage requirements by eliminating duplicate data

Question 16

What is the key distinction between a primary key and a foreign key in a database?

Accepted Answer

A primary key uniquely identifies each row in a table, while a foreign key references a primary key in another table.

Answer

A foreign key is mandatory and cannot be null, while a primary key can have null values.

Answer

A primary key is present in every table, while a foreign key is optional.

Question 17

Which of the following is an example of a logical data model?

Accepted Answer

Entity-Relationship Diagram (ERD) which represents data as entities and their relationships

Answer

Hierarchical Data Model which organizes data in a tree-like structure

Answer

Relational Schema which defines the structure of tables and their relationships

Answer

Physical Data Model which specifies the physical storage details of the database

Question 18

Which database normalization form ensures that all data dependencies are based solely on the primary key, eliminating data redundancy?

Accepted Answer

Third Normal Form (3NF)

Answer

First Normal Form (1NF)

Answer

Second Normal Form (2NF)

Question 19

Which of the following is NOT a primary goal of database design?

Accepted Answer

User-friendliness

Answer

Efficiency

Answer

Reliability

Answer

Maintainability

Question 20

What is the primary purpose of a database schema?

Accepted Answer

To define the structure and relationships of data in a database

Answer

To store actual data values

Answer

To enforce business rules

Answer

To provide a visual representation of the database

Question 21

What is the main advantage of indexing in a database?

Accepted Answer

Faster data retrieval

Answer

Improved data security

Answer

Reduced storage space

Answer

Enhanced data consistency

Question 22

Which of the following normalization forms ensures that a table is in second normal form?

Accepted Answer

2NF

Answer

1NF

Answer

3NF

Answer

BCNF

Question 23

What is the primary purpose of a composite primary key?

Accepted Answer

To uniquely identify rows in a table using multiple fields

Answer

To improve data retrieval performance

Answer

To enforce referential integrity

Answer

To reduce data redundancy

Question 24

Which of the following is NOT a type of data dependency?

Accepted Answer

Database integrity

Answer

Functional dependency

Answer

Multi-valued dependency

Answer

Join dependency

Question 25

ER diagrams are used to:

Accepted Answer

Design entities and their relationships

Answer

Define data types and constraints

Answer

Create physical database structures

Answer

Generate SQL queries

Question 26

Which of the following are key principles of database design?

Accepted Answer

Simplicity, scalability, and maintainability

Answer

Scalability

Question 27

What is the primary purpose of a primary key in a database table?

Accepted Answer

To uniquely identify each row in the table

Answer

To enforce data integrity

Question 28

What is the main purpose of an index in a database?

Accepted Answer

To enhance query performance

Answer

To enforce data integrity

Question 29

Which database normalization form is primarily used to eliminate data redundancy?

Accepted Answer

Third Normal Form (3NF)

Answer

2NF

Question 30

What is the main difference between clustered and non-clustered indexes?

Accepted Answer

Clustered indexes physically store the data in the same order as the table, while non-clustered indexes do not.

Answer

Non-clustered indexes are stored in the same order as the table data, while clustered indexes are not.

Question 31

Which of the following is considered a best practice for database design?

Accepted Answer

Using clear and concise column names

Answer

Avoid using foreign keys

Question 32

What is the primary purpose of a database schema?

Accepted Answer

To define the structure of the database

Answer

To store the data in the database

Question 33

Which of the following is a type of database join?

Accepted Answer

INNER JOIN, OUTER JOIN, and CROSS JOIN

Answer

OUTER JOIN

Question 34

What is the main purpose of a transaction in a database?

Accepted Answer

To ensure data integrity by grouping multiple database operations together as a single unit of work.

Answer

To improve query performance

Question 35

Which of the following is NOT a component of a database schema?

Accepted Answer

Procedures

Answer

Tables

Answer

Constraints

Answer

Indexes

Question 36

What is the primary purpose of a primary key in a relational database table?

Accepted Answer

To uniquely identify each row in the table

Answer

To store additional information about rows

Answer

To enforce referential integrity between tables

Answer

To index the table for faster queries

Question 37

What is the key difference between a clustered index and a non-clustered index?

Accepted Answer

A clustered index physically sorts the data based on the index key, while a non-clustered index does not.

Answer

A non-clustered index physically sorts the data based on the index key, while a clustered index does not.

Answer

A clustered index stores data in the same order as the index key, while a non-clustered index does not.

Answer

A non-clustered index stores data in the same order as the index key, while a clustered index does not.

Question 38

Which of the following is NOT a commonly used data type in database design?

Accepted Answer

Array

Answer

Integer

Answer

String

Answer

Date

Question 39

Which of the following is NOT included in a database schema?

Accepted Answer

Data

Answer

Tables

Answer

Attributes

Answer

Relationships

Question 40

What data type is appropriate for storing a customer's name?

Accepted Answer

VARCHAR(50)

Answer

INTEGER

Answer

DATETIME

Answer

DECIMAL

Question 41

What is the primary function of a primary key?

Accepted Answer

To uniquely identify each record in a table

Answer

To enforce data integrity

Answer

To store the most important data

Answer

To establish relationships between tables

Question 42

Which index type offers the best performance for range-based queries?

Accepted Answer

B-tree index

Answer

Hash index

Answer

R-tree index

Answer

Bitmap index

Question 43

Explain the distinction between a clustered and a non-clustered index.

Accepted Answer

Clustered index physically arranges table data based on the index key, while non-clustered index does not.

Answer

Non-clustered index physically sorts the table data based on the index key, while clustered index does not.

Answer

Clustered index is unique, while non-clustered index is not.

Answer

Non-clustered index is unique, while clustered index is not.

Question 44

What is the purpose of a foreign key?

Accepted Answer

To enforce referential integrity between tables

Answer

To create a relationship between two tables

Answer

To store additional information about a related row

Answer

To optimize query performance

Question 45

Which of the following is NOT a type of database constraint?

Accepted Answer

INDEX

Answer

NOT NULL

Answer

PRIMARY KEY

Answer

CHECK

Question 46

What is the purpose of a data dictionary?

Accepted Answer

To store metadata about the database

Answer

To enforce data security

Answer

To optimize query performance

Answer

To backup and restore data

Question 47

Which of the following is a fundamental principle of database design aimed at eliminating data redundancy and anomalies?

Accepted Answer

Normalization

Answer

Aggregation

Answer

Denormalization

Answer

Data duplication

Question 48

What is the primary purpose of a primary key in a database table?

Accepted Answer

Uniquely identify each row in the table

Answer

Speed up data retrieval

Answer

Store additional information about each row

Answer

Enforce foreign key constraints

Question 49

What is the key difference between a clustered index and a non-clustered index in a database?

Accepted Answer

Clustered indexes physically sort data in the order of the index, while non-clustered indexes do not

Answer

Non-clustered indexes require additional storage space

Answer

Clustered indexes are always faster than non-clustered indexes

Question 50

Which of the following is a primary benefit of database normalization?

Accepted Answer

Reduced data redundancy

Answer

Lower query performance

Answer

Increased storage requirements

Answer

More complex queries

Question 51

Which of the following is a potential disadvantage of database denormalization?

Accepted Answer

Increased data redundancy

Answer

Reduced storage requirements

Answer

Improved data integrity

Answer

Faster query performance

Question 52

What is the primary purpose of a data dictionary in a database management system?

Accepted Answer

Store metadata about the database

Answer

Manage user accounts

Answer

Enforce data integrity

Answer

Optimize query performance

Question 53

Which of the following is a commonly used tool for designing database schemas?

Accepted Answer

Entity-Relationship (ER) diagram

Answer

Flowchart

Answer

Spreadsheet

Answer

Word processor

Question 54

What is the primary goal of database optimization?

Accepted Answer

Improve query performance

Answer

Simplify database design

Answer

Enforce data integrity

Answer

Reduce storage requirements