Week 11

April 3

Explain order-of-join example?

    Facebook timeline and denormalization:
    https://www.facebook.com/note.php?note_id=10150468255628920

    My transactions DB:

• customer(custid serial, custname varchar(20))
• part (partnum serial, price decimal, quan_in_stock integer)
• invoice (invnum serial, ordertime timestamp, custid integer, totalamt decimal)
• invitem(invoicenum integer, partnum integer, quantity integer)

    Typical transaction based on input custid, part1, quan1, part2, quan2:

insert into invoice(ordertime, custid) values (now(), custid);
insert into invitem values (invnum, part1, quan1);
update part set quan_in_stock = quan_in_stock - quan1 where partnum = part1;
insert into invitem values (invnum, part2, quan2);
update part set quan_in_stock = quan_in_stock - quan2 where partnum = part2;

    After inserting a new invoice, how do I get its invnum?
    postgres: insert into invoice(custid) values (1234) returning invnum;
    mysql: insert into invoice(custid) values(1234); select last_insert_id();

    transaction notes