Mobile Database Normalization u0026 Schema Design Essentials Quiz Quiz

Assess your understanding of mobile database normalization principles and effective schema design for efficient data storage, reduced redundancy, and optimal app performance. Explore key concepts like normal forms, functional dependencies, and schema strategies relevant to mobile application development.

1. Identifying Data Redundancy

   Which of the following is the most accurate definition of data redundancy in a mobile database schema?

   1. Allowing users to enter duplicate usernames
   2. Using different data types for similar information
   3. Having unique constraints on each table column
   4. Storing the same piece of data in multiple locations within a database

   Explanation: Data redundancy refers to storing identical data in multiple places within a database, which can waste storage and lead to inconsistencies. Unique constraints enforce value uniqueness, but aren't about redundancy. Allowing duplicate usernames is a data entry policy issue, not schema redundancy. Using different data types may cause compatibility problems but doesn't inherently mean redundant data.

2. Understanding First Normal Form (1NF)

   A mobile app logs user actions, storing in a table with one column containing a list of actions separated by commas. Why does this structure violate First Normal Form (1NF)?

   1. Tables must always have a primary key
   2. Each row must have a unique identifier
   3. Column names cannot contain commas
   4. Data in each column should be atomic, not a list

   Explanation: First Normal Form requires each column to contain only single, indivisible values, not a list. While primary keys and unique identifiers are important, they are addressed in later normalization and general database design, not specifically 1NF. The use of commas in column names is not related to normalization.

3. Second Normal Form (2NF) Basics

   Which scenario best describes a violation of Second Normal Form (2NF) in a mobile database schema with a composite primary key?

   1. A non-key attribute depends on only part of the composite key
   2. A table has missing foreign keys
   3. Two tables contain columns with the same name
   4. A table has a primary key consisting of a single column

   Explanation: 2NF is violated when a non-key attribute depends only on part of a composite primary key, leading to partial dependency. A single-column primary key cannot have partial dependency issues. Having columns with the same name in different tables is not a normalization violation by itself. Missing foreign keys pertain to referential integrity, not 2NF.

4. Third Normal Form (3NF) Explanation

   Which of the following statements correctly identifies Third Normal Form (3NF) for a mobile app's user profile table?

   1. The table contains no repeating groups or arrays
   2. All non-key columns are dependent only on the primary key and not on other non-key columns
   3. Each column is uniquely constrained
   4. There are no composite keys in the table

   Explanation: 3NF ensures that all non-key columns depend only on the primary key, removing transitive dependencies. No repeating groups refers to 1NF, not 3NF. Composite keys are not prohibited in 3NF. Uniqueness constraints are a separate concept and do not guarantee compliance with 3NF.

5. Schema Design for Mobile Efficiency

   When designing a schema for a mobile app that frequently displays user orders, which approach can improve query speed and lower device resource use?

   1. Normalize data up to 3NF and denormalize order summaries for fast retrieval
   2. Avoid using indexes to reduce write overhead
   3. Store all user and order data in a single large table
   4. Only use text data types for all columns

   Explanation: Normalizing up to 3NF reduces redundancy, and selectively denormalizing common order summaries improves performance. Storing all data in one table can slow queries and use excessive resources. Indexes, despite increasing write cost, improve query speed, which is vital for mobile apps. Using only text types ignores appropriate data typing and can harm efficiency.

6. Recognizing Functional Dependencies

   In a mobile database, if each user has a unique email and their phone number is stored in the same row, what functional dependency does this represent?

   1. Each email and phone number are unrelated
   2. Email determines the phone number
   3. Phone number determines the user email
   4. The schema is not normalized

   Explanation: The email field uniquely identifies a user, so it functionally determines associated data like phone number. The reverse is not guaranteed, as phone numbers may not be unique. Saying the schema is not normalized lacks context, and claiming no relationship misses the definition of functional dependency.

7. Purpose of Primary Keys

   Why is it important to define a primary key for each table in a mobile app's database schema?

   1. It allows unlimited storage capacity
   2. A primary key is needed only in relational databases
   3. It restricts the number of columns in the table
   4. A primary key ensures each record is uniquely identified

   Explanation: Primary keys uniquely identify each record, supporting data integrity and reliable lookups. They do not affect storage capacity or restrict column count. While most common in relational databases, unique identifiers help in any structured data, not just relational models.

8. Avoiding Update Anomalies

   Which normalization benefit helps prevent update anomalies in a mobile app’s contact list database?

   1. Eliminating redundant data reduces inconsistency when updating records
   2. Grouping unrelated attributes in one table saves storage
   3. Allowing duplicate rows lets users easily edit contacts
   4. Denormalizing all tables speeds up information updates

   Explanation: Reducing redundancy means data is stored in one place, so updates do not create inconsistencies. Allowing duplicate rows increases the chance of update errors. Grouping unrelated attributes wastes storage and complicates updates. Full denormalization may boost read speed but raises inconsistency risks during updates.

9. Handling One-to-Many Relationships

   If a mobile messaging app must store chat messages from multiple users in different groups, which schema is appropriate for a one-to-many relationship?

   1. Create a messages table with a foreign key referencing a groups table
   2. Store all group messages as comma-separated values in one row
   3. Avoid relational links to keep tables simple
   4. Place group names directly in every user profile table

   Explanation: Using a foreign key in the messages table efficiently tracks which group each message belongs to, representing a one-to-many link. Storing multiple messages in a single field breaks normalization. Including group names in every user profile is redundant and not scalable. Omitting relational links sacrifices data integrity.

10. Choosing Column Data Types

    When designing a mobile database schema for storing user birthdays, which column data type should be used?

    1. Varchar for every value
    2. Integer data type
    3. Binary data type
    4. Date or DateTime data type

    Explanation: A date or datetime type stores dates efficiently and supports date operations. Integer is inappropriate because birthdays are not solely numeric. Varchar allows flexibility but doesn't enable date-specific functions. Binary is used for non-textual data, not dates.