A Simple DB Structure for a Shopping Cart This is a little tricky! In order to m

Question

A Simple DB Structure for a Shopping Cart

This is a little tricky!
In order to model a shopping cart, let's first understand why our knowledge in SQL isn't quite
enough (well, actually, it's enough, but we need to apply it smarter).

What doesn't work, first:
Placing a foreign key into the Products table into a Customer doesn't really help: That only
allows each customer to have one product in the shopping cart – and it doesn't even allow for
more than one item of this product .
The other way around also doesn't work – a foreign key into a Customer in a product allowsa
for only one customer to order it – bad...

So, here's the trick:
Create a table SHOPPING_CART with three fields
1. a Customer ID (FK into the Customers table)
2. a Product ID (FK into the Products table)
3. a number (how many of this product are in the cart)
The primary key of this is the combination of Customer ID and Product ID – figure out how to
do that!). That's already about all you need to do.

The scenarios then are:
A. To add n items of product p to the shopping cart of customer c, you insert the tuple (c,p,n)
into the SHOPPING_CART table.
B. To loop over the contents of the shopping cart of customer c, you use something like
SELECT ProductID, Number FROM SHOPPING_CART
WHERE CustomerID = c

Exercise:
I provided you with my solution of the Categories/Products/Customer Schema from project 2.
Please, use this from now on.
Add the SHOPPING_CART table as described.
Manually place for customer “Gerald Bostock” 2 DULL 122 and one jPET 12 into his shopping
cart – using INSERTs.
Drop it all into the drop box.

Explanation / Answer

create table SHOPPING_CART
(
cust_id   varchar2(10) REFERENCES customers(cust_id),
prod_id    varchar2(10) REFERENCES products(prod_id),
quantity number(3)
);

insert into SHOPPING_CART values('c001','p101',5);

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.