SQL Database for a toothbrush company
Natural Language Description: An eco-friendly wooden toothbrush company uses a relational
database for the management of its information system. Customers place orders that contain
toothbrushes made up of materials supplied by a supplier. More specifically, we are interested in the
following notions:
Customers: Each customer can be identified by their customer number. For each customer, we also
know their name and email. One customer can place zero or more orders, as a customer may have
registered but has not yet placed an order.
Orders: Each order can be identified by an order number. An order has a date and total price.
Multiple orders can be placed by a single customer. One order contains zero or more toothbrushes,
as an order can contain other products instead.
Toothbrushes: Each toothbrush is identified by a toothbrush number. A toothbrush has a model,
colour, and price. One toothbrush uses one or more materials.
Materials: Each material is identified by its material number. A material also has a name. One
material can be used in zero or more toothbrushes as the material may be currently unused. A
material may have zero or one current supplier, as we can imagine we use one supplier for each
material, and we may not currently have a supplier for a certain material.
Suppliers: Each supplier is identified by their supplier number. We also know each supplier’s name
and address. A supplier may supply zero or more materials, as one supplier may supply many
materials, or they currently do not supply any materials.
UML Diagram:
(The blue box represents the name translation rules)
Relational Model:
Customers (customer_number, customer_name, customer_email)
Orders (order_number, order_date, order_total_price, customer_number, product_number)
Having product_number is more generic than having toothbrush_number
Toothbrushes (toothbrush_number, toothbrush_model, toothbrush_colour, toothbrush_price)
Materials (material_number, material_name)
ToothbrushesMaterials (toothbrush_number, material_number) following the many-to-many
association
Suppliers (supplier_number, supplier_name, supplier_address)
Orders[customer_number] ⊆ Customers[customer_number]
ToothbrushesMaterials[toothbrush_number] ⊆ Toothbrushes[toothbrush_number]
ToothbrushesMaterials[material_number] ⊆ Materials[material_number]
As orders can contain products other than toothbrushes, we don’t need to constrain
product_number