Database Design Beginner Quiz
30 questions on Database Design.
Question 1: What is Database Design?
- A. Picking the color scheme for a database dashboard
- B. The process of producing a detailed data model of a database to organize data according to a specific set of rules β (correct answer)
- C. Writing SQL queries
- D. Configuring server hardware
Explanation: Database design dictates how tables are structured and how they relate to one another before any actual coding begins.
Question 2: What does ERD stand for in database design?
- A. Entity Relational Data
- B. Exact Row Definition
- C. Entity-Relationship Diagram β (correct answer)
- D. Encrypted Record Database
Explanation: An ERD is a visual chart showing how entities (tables) relate to each other in a system.
Question 3: In an Entity-Relationship Diagram (ERD), what does a "Rectangle" typically represent?
- A. An Attribute
- B. A Relationship
- C. An Entity (a Table) β (correct answer)
- D. A Primary Key
Explanation: In standard Chen or Crow's Foot notation, rectangles represent entities (like "Customer" or "Order").
Question 4: What is a "Primary Key"?
- A. The first password to access the DB
- B. A column (or set of columns) that uniquely identifies each row in a table β (correct answer)
- C. The largest column in a table
- D. A column that allows duplicate values
Explanation: A primary key ensures row uniqueness and cannot contain NULL values.
Question 5: What is a "Foreign Key"?
- A. A key used by external servers
- B. A column in one table that references the Primary Key of another table, establishing a link between them β (correct answer)
- C. A secondary index
- D. A key used for database backups
Explanation: Foreign keys enforce referential integrity. For example, user_id in the orders table points to id in the users table.
Question 6: What does the process of "Normalization" achieve?
- A. It makes the database look pretty
- B. It reduces data redundancy (duplication) and improves data integrity β (correct answer)
- C. It increases the database size
- D. It deletes old records automatically
Explanation: Normalization splits large, repetitive tables into smaller, linked tables to ensure that every piece of data is stored in exactly one place.
Question 7: What is the First Normal Form (1NF) primary rule?
- A. Every table must have a foreign key
- B. Data must be duplicated
- C. Each column must contain atomic (indivisible) values, and there should be no repeating groups β (correct answer)
- D. All tables must be joined
Explanation: 1NF means you cannot have a column like phone_numbers containing "555-1234, 555-5678". Each value must be in its own row.
Question 8: What is a "One-to-Many" relationship?
- A. One row in Table A can have many matching rows in Table B β (correct answer)
- B. One row in Table A has exactly one matching row in Table B
- C. Many rows in Table A match many rows in Table B
- D. A table with one row and many columns
Explanation: This is the most common relationship. For example, ONE User can place MANY Orders.
Question 9: How is a "Many-to-Many" relationship typically resolved in a relational database?
- A. By putting an array inside the column
- B. By creating a third "Junction" or "Linking" table that holds foreign keys to both tables β (correct answer)
- C. It cannot be resolved
- D. By combining both tables into one giant table
Explanation: For example, Students and Courses. A Student takes many Courses, and a Course has many Students. A student_courses junction table resolves this.
Question 10: What is an "Index" in database design?
- A. A table of contents at the beginning of the DB
- B. A data structure that improves the speed of data retrieval operations on a table β (correct answer)
- C. An auto-incrementing ID
- D. A backup file
Explanation: Indexes prevent the database from doing a "Full Table Scan." They work exactly like the index at the back of a textbook.
Question 11: What is a "Composite Key"?
- A. A key made of metal and plastic
- B. A Primary Key that consists of two or more columns to uniquely identify a row β (correct answer)
- C. An encrypted key
- D. A key that automatically changes
Explanation: In a junction table like student_courses, the combination of student_id and course_id together forms the unique composite primary key.
Question 12: What does "Referential Integrity" mean?
- A. All references must be cited
- B. Every foreign key value must match an existing primary key value in the related table, preventing "orphaned" records β (correct answer)
- C. The database must be integrated with the web server
- D. All tables must have the same number of rows
Explanation: It prevents you from adding an order for user_id = 999 if User 999 does not exist in the users table.
Question 13: What is a "Constraint" in a database?
- A. A limit on how much data the hard drive can hold
- B. A rule enforced on data columns to ensure accuracy and reliability (e.g., NOT NULL, UNIQUE) β (correct answer)
- C. A network timeout
- D. A password policy
Explanation: Constraints like UNIQUE prevent duplicate emails, and NOT NULL prevents empty fields, keeping the data clean.
Question 14: What is Denormalization?
- A. The process of intentionally introducing redundancy into a database to improve read performance β (correct answer)
- B. Making a database normal
- C. Deleting the database
- D. Removing all primary keys
Explanation: While normalization saves space and ensures integrity, heavy normalization requires many slow JOINs. Denormalization optimizes for heavy read operations.
Question 15: In a "One-to-One" relationship, how are tables usually linked?
- A. With a junction table
- B. The Primary Key of one table acts as the Foreign Key to the other table β (correct answer)
- C. By duplicating all data
- D. Using arrays
Explanation: For example, Users and User_Profiles. The user_id in the profiles table is both a Foreign Key and often the Primary Key for that table.
Question 16: What is Second Normal Form (2NF)?
- A. The table must be in 1NF and all non-key attributes must be fully dependent on the entire Primary Key β (correct answer)
- B. The table must have two columns
- C. The table must have two foreign keys
- D. The table must not use integers
Explanation: This applies mostly to tables with Composite Keys. It ensures that a column isn't dependent on only *half* of the composite key.
Question 17: What is Third Normal Form (3NF)?
- A. The table must have three columns
- B. The table must be in 2NF and have no transitive dependencies (non-key columns cannot depend on other non-key columns) β (correct answer)
- C. The table must be split into three tables
- D. The table must use a junction table
Explanation: If you have a user_id and a zip_code column, adding a city column violates 3NF because city depends on the zip_code, not the user_id.
Question 18: What is an "Entity" in data modeling?
- A. A single row of data
- B. A real-world object or concept (like a Customer, Product, or Order) that exists on its own and will be represented as a Table β (correct answer)
- C. A database column
- D. A SQL query
Explanation: Entities form the backbone of the ERD. Attributes describe the entity, and Relationships connect them.
Question 19: What is an "Attribute" in data modeling?
- A. An entity
- B. A characteristic or property of an Entity (which becomes a Column in a table) β (correct answer)
- C. A foreign key
- D. A stored procedure
Explanation: If the Entity is "User", its Attributes are "Name", "Email", and "Age".
Question 20: What does "Cardinality" refer to in an ERD?
- A. The number of rows in a table
- B. The numerical relationship between entities (e.g., One-to-One, One-to-Many) β (correct answer)
- C. The color of the diagram
- D. The primary key size
Explanation: Cardinality defines the rules of the relationship. Crow's foot notation uses symbols to represent "exactly one", "many", "zero or one", etc.
Question 21: When should you use a VARCHAR data type instead of CHAR?
- A. When the string length is highly variable (e.g., names, emails) β (correct answer)
- B. When the string length is exactly the same every time (e.g., state abbreviations, hashes)
- C. When storing numbers
- D. When storing dates
Explanation: VARCHAR (Variable Character) only consumes the space needed for the string. CHAR pads the string with spaces to fill the exact length specified.
Question 22: What is the risk of adding too many Indexes to a table?
- A. The database will crash
- B. It significantly slows down
INSERT, UPDATE, and DELETE operations β (correct answer)
- C. It slows down
SELECT queries
- D. It breaks foreign keys
Explanation: Every time data is modified, the database must also update all related indexes. Over-indexing creates massive write-performance penalties.
Question 23: What is an "OLTP" database?
- A. Online Transaction Processing: Optimized for rapid, real-time CRUD operations and highly normalized β (correct answer)
- B. Online Analytical Processing: Optimized for complex data warehousing and reporting
- C. Offline Transaction Processing
- D. Object Level Table Protocol
Explanation: Most standard web applications (e-commerce, social media) use OLTP structures to handle thousands of small, concurrent transactions quickly.
Question 24: What is an "OLAP" database?
- A. Online Transaction Processing
- B. Online Analytical Processing: Optimized for heavy reads, complex queries, and reporting across massive historical datasets β (correct answer)
- C. A NoSQL graph database
- D. A cache layer
Explanation: OLAP databases (Data Warehouses) are heavily denormalized and designed for data scientists to run massive aggregate queries without slowing down the main OLTP application.
Question 25: What is "Data Integrity"?
- A. Ensuring the database is not hacked
- B. The overall completeness, accuracy, and consistency of data over its entire lifecycle β (correct answer)
- C. Deleting old data
- D. Encrypting passwords
Explanation: Data integrity is maintained via constraints (Primary Keys, Foreign Keys, UNIQUE, NOT NULL).
Question 26: What is a "Surrogate Key"?
- A. A natural key like a Social Security Number
- B. An artificial, system-generated Primary Key (like an Auto-Incrementing Integer or UUID) that has no business meaning β (correct answer)
- C. A secondary index
- D. A foreign key
Explanation: Surrogate keys are preferred over Natural keys (like an email address) because they never change, preventing massive cascading updates if a user changes their email.
Question 27: What is a "Natural Key"?
- A. An auto-incrementing ID
- B. A Primary Key formed of attributes that already exist in the real world (e.g., Email Address, SSN, ISBN) β (correct answer)
- C. A foreign key
- D. A UUID
Explanation: While natural keys seem logical, they are risky. If a book's ISBN changes, or a user updates their email, every foreign key pointing to it must also be updated.
Question 28: In Crow's Foot Notation, what does a line ending with three prongs (a crow's foot) represent?
- A. One
- B. Many β (correct answer)
- C. Zero
- D. Mandatory
Explanation: The crow's foot visually indicates the "Many" side of a One-to-Many or Many-to-Many relationship.
Question 29: What is a "Self-Referencing" (Recursive) relationship?
- A. When two tables have the exact same columns
- B. When a table contains a Foreign Key that points back to its own Primary Key β (correct answer)
- C. When a database backs itself up
- D. When a query calls itself
Explanation: This is commonly used for hierarchies, like an Employees table where the manager_id column is a foreign key pointing to the id column of the SAME Employees table.
Question 30: What is "ACID" in database design?
- A. A set of properties (Atomicity, Consistency, Isolation, Durability) that guarantee database transactions are processed reliably β (correct answer)
- B. A query language
- C. A security protocol
- D. A caching mechanism
Explanation: ACID compliance ensures that if a banking transfer fails halfway through, the database rolls back to the starting state rather than leaving money in limbo.