Timedora
Free Practice Quiz Question List

7 Basic Relational Database Design Online Quiz Questions

Use this free practice quiz with 20 questions to review 7 Basic Relational Database Design, test your knowledge, and prepare for your next test or exam.

20 questions
01
Choose one
1 point

An online shop allows each order to contain multiple products, and each product to appear in multiple orders. Which design represents this many-to-many relationship?

  1. A

    Store a list of product IDs in a products column on each order.

  2. B

    Create an order_items table that associates orders with products.

  3. C

    Add product_id as a column on customers.

  4. D

    Create a separate order table for every product.

02
True or false
1 point

A table satisfies first normal form if each order stores its entire list of product IDs in one cell, rather than storing product associations in separate rows. Is this statement true or false?

  1. A

    True

  2. B

    False

03
Fill in the blank
1 point

In the order_items schema, quantity must be greater than zero and must be present. Besides its CHECK constraint, quantity is declared .

04
Choose one
1 point

An orders table contains customer_id and customer_email. To avoid the redundancy described by third normal form, where should the customer's email be stored?

  1. A

    Copy the customer's email into every order because it is not an order key.

  2. B

    Store the customer's email in products so all shop data is together.

  3. C

    Store the customer's email with the customer, not as a repeated copy in each order.

  4. D

    Store all customer emails in a single list in the orders table.

05
Written response
1 point

In the online shop schema, which table stores a customer's email address?

06
Choose all
1 point

Select all options that correctly describe how constraints in the online shop schema enforce its rules.

  1. A

    Use CHECK (quantity > 0) to reject zero or negative quantities.

  2. B

    Use a foreign key from orders.customer_id to customers.customer_id to require a referenced customer.

  3. C

    Use UNIQUE on products.sku to prevent duplicate SKU values.

  4. D

    Use NOT NULL on customers.email to require an email value.

  5. E

    A descriptive column name alone prevents invalid values.

07
Fill in the blank
1 point

Under the assumption that a product occurs at most once per order, the order_items composite primary key consists of and .

08
True or false
1 point

Given the stated assumption that each product appears at most once per order, the order_items composite primary key (order_id, product_id) prevents duplicate lines for that same order-product pair. Is this statement true or false?

  1. A

    True

  2. B

    False

09
Choose one
1 point

A product's current price may change after a customer places an order. Where should the design preserve the price actually charged for that order?

  1. A

    Use products.current_price to recalculate all past order prices whenever it changes.

  2. B

    Store the charged price in order_items.unit_price as a historical snapshot.

  3. C

    Store only the product name on each order line and infer its old price later.

  4. D

    Use the customer's email as the historical price record.

10
Written response
1 point

An order has two lines: one has quantity 3 at a recorded unit price of 12.50 USD, and the other has quantity 2 at 4.25 USD. Using the recorded unit prices, what is the order total? Enter the total as a number in USD.

11
Choose all
1 point

Select all deletion outcomes that match the supplied online shop schema.

  1. A

    Deleting an order also deletes its order_items rows through ON DELETE CASCADE.

  2. B

    Deleting a product referenced by an order_items row is blocked unless the references are dealt with.

  3. C

    Deleting a customer with orders still referencing them is not allowed unless the orders are dealt with first.

  4. D

    Deleting a product automatically deletes every order that included it.

12
Open ended
1 point

A shop's requirements may change: instead of allowing a product at most once per order, they may allow the same product on separate lines. Explain how the order_items key should reflect this change and identify the references and line data the table still needs.

13
Choose one
1 point

The schema does not store an order total. How should an application calculate the current recorded total for an order while avoiding a stored total that could disagree with its lines?

  1. A

    Store a total on every product and use it as the order amount.

  2. B

    Calculate the total by multiplying the current product price by the number of orders.

  3. C

    Sum each order line's quantity multiplied by its historical unit price.

  4. D

    Use the number of order_items rows as the total amount.

14
True or false
1 point

If orders.customer_id is NOT NULL and references customers.customer_id with a foreign key, an order can still refer to a customer row that does not exist. Is this statement true or false?

  1. A

    True

  2. B

    False

15
Choose one
1 point

A shop’s requirements say that a customer may place many orders, but each order belongs to one customer. What is the relationship cardinality from customers to orders?

  1. A

    One-to-one

  2. B

    One-to-many, from customers to orders

  3. C

    Many-to-many

  4. D

    Many-to-one, from customers to orders

16
Choose one
1 point

In an online shop, each order may include several products, and each product may appear in several different orders. How should the relationship between orders and products be classified?

  1. A

    One-to-one

  2. B

    One-to-many, from orders to products

  3. C

    Many-to-many

  4. D

    Many-to-one, from orders to products

17
Choose one
1 point

A team is reviewing the identifier for a table of shipment records. Which pair of properties must the chosen primary key have?

  1. A

    It uniquely identifies each row and cannot be null.

  2. B

    It may be null as long as its values are usually different.

  3. C

    It must contain a copy of every non-key attribute.

  4. D

    It identifies a row only when combined with a foreign key from another table.

18
Choose one
1 point

A customers table stores many customers, with fields such as email and full_name. Which interpretation correctly describes the table, a row, and a column?

  1. A

    The table is one customer, a row is an attribute, and a column is the full customer collection.

  2. B

    The table is one attribute, a row is the full customer collection, and a column is one customer.

  3. C

    The table is the full customer collection, a row is an attribute, and a column is one customer.

  4. D

    The table is the customer collection, a row is one customer, and a column is one attribute.

19
Written response
1 point

A table is keyed by (order_id, product_id). According to the definition of 2NF, a non-key attribute must depend on the ____ key.

20
Written response
1 point

In the shop schema, an insert omits the ordered_at column for a new order. What SQL expression supplies the column’s default value?