Database Design: Modeling Customer, Order, Product, and Supplier Data

This assignment focuses on designing a database for a mail-order company, encompassing customer, order, product, supplier, and warehouse data. The solution includes the creation of a relational schema to organize the data effectively. The design must account for multiple suppliers offering different wholesale prices for the same product and the storage of product quantities in warehouses. The assignment involves creating an entity-relationship diagram (ERD) to visualize the database structure and relationships between entities such as customers, orders, products, suppliers, and warehouses. The solution also considers the use of unique identifiers for customers, products, and suppliers, as well as the management of order details including order numbers, dates, and quantities. The design also caters to the storage of warehouse locations and contact information. The assignment also includes the use of bibliographical sources.

A small mail-order company must maintain the following
The firm should keep track of all its client’s identities, physical addresses, and the date and time
of their requests/ orders. A unique client identification number must be assigned and assigned to
every customer.
An order placed by a client can have multiple order tags/ lines. Each tag/ line may be used by the
client to order a product for a quantity. The date of the order should also be recorded. A client
can assign order numbers to the orders requested. Orders from various customers might contain
similar order numbers.
Every product contains a product identification number, the product name and the recommended
retail price (Bagui & Earp, 2011).
The online mail order data processing
A product may be supplied by multiple suppliers. Every supplier has a unique number identifier.
Different suppliers might offer a similar product at differing wholesale prices. The supplier can
supply and distribute multiple products. The concurrent name and physical addresses of all the
suppliers should be kept in record.
A company may be in ownership of multiple warehouses. Every warehouse must be identified as
per the name of the city located. Each warehouse telephone number must be kept in record. The
amount of each product in stock must be logged. The warehouse may stock a variety of products
(Elmasri, Ramez, B. Shamkant, Navathe, 2000). However, every warehouse should be stocked in
at most one warehouse.

Relational Schema
Customer(C#, name, address, since)
(2) Order(C#, O#, Date)
(3) For(C#, O#, P#, quantity)
(4) Product(P#, name, retailPrice, city, quantity)
(5) Supplier(S#, name, address)
(6) Supply(S#, P#, wholeSalePrice)
(7) Warehouse(city, telephone#)
The Use Case Diagram
Bibliography

Bagui, S. & Earp, R., 2011. Database Design Using Entity-Relationship Diagrams , s.l.: CRC Press.
Elmasri, Ramez, B. Shamkant, Navathe, 2000. Fundamentals of Database Systems, third ed.,, Addison-
Wesley, Menlo Park, CA, USA,: s.n.