A Simple DB Structure for a Shopping Cart This is a little tricky! In order to m
ID: 3735425 • Letter: A
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);