Question

You are to create a appropriate database design for a database supporting an e-commerce website for a local Pet Store – Jaguar Pets. The database design should be normalized to BCNF (unless an appropriate exception is provided).

There are 4 main areas to include in your database:
Customer
Products/Inventory
Orders
Suppliers
Here are a few business rules that must be enforced:
Customers must register with the system to place orders. Be sure to collect the necessary information to process and send the order.
Customers can purchase multiple products, customer can by more than one item at a time and more than one of any individual item. Products can be purchased by multiple customers.
Products can come from multiple suppliers, suppliers can supply multiple products.
Products will have a retail price and a cost (from the supplier)
Your ERD must include Primary Keys, Foreign Keys, Identify Relationships, Minimum Cardinality and Maximum Cardinality.

Answer 1

An appropriate database design for the Jaguar Pets e-commerce site should include tables for Customers, Products/Inventory, Orders, Suppliers, OrderDetails, and ProductSuppliers, along with their relationships and cardinalities, normalized to BCNF.

Step-by-step explanation:

Database Design for Jaguar Pets E-Commerce WebsiteTo create an appropriate database design for an e-commerce website called Jaguar Pets, we need to adhere to several business rules and normalize the database to at least Boyce-Codd Normal Form (BCNF). The four primary areas of the database will be Customers, Products/Inventory, Orders, and Suppliers. Here is a simplified Entity-Relationship Diagram (ERD) design:Customers: Should contain CustomerID (Primary Key), Name, Email, Address, Password, etcProducts/Inventory: Should include ProductID (Primary Key), Name, Description, RetailPrice, Cost, etcOrders: Should consist of OrderID (Primary Key), CustomerID (Foreign Key), OrderDate, TotalAmount, etcOrderDetails: To record multiple products in one order it includes OrderID and ProductID as composite Primary Key, Quantity, LineTotal, etcSuppliers: Should hold SupplierID (Primary Key), Name, ContactInfo, etc.

ProductSuppliers: To represent many-to-many relationship between Products and Suppliers, it includes ProductID and SupplierID as composite Primary Key.In this ERD, relationships are very important to identify:Each Customer can have multiple Orders, but each Order is linked to one Customer.Each Order can contain multiple Products through OrderDetails, and each Product can appear in multiple OrdersEach Product can be supplied by many Suppliers, and each Supplier can supply many Products, which is represented in the ProductSuppliers table.The ERD would also define minimum and maximum cardinality in these relationships, ensuring that we capture details like a customer must register to place an order, and a product can come from multiple suppliers.Overall, the database design will facilitate the operation of the Jaguar Pets e-commerce site by ensuring data is stored efficiently and relationships are clearly defined, maintaining database integrity and performance.