gPG8Xded

1
2
3drop table if exists  orderItem;
4drop table if exists  order;
5drop table if exists  catalogitem;
6drop table if exists  customer;
7
8
9create table  customer (
10	id bigserial not null, 
11	firstName text not null, 
12	lastName text not null, 
13	email text not null, 
14	primary key (id)
15)   
16
17/*
18 * Table: CatalogItem
19 * Description: Contains the catalog item
20 */
21create table  catalogitem (
22	id bigserial not null, 
23	itemNumber text not null,
24	itemName text not null, 
25	itemType text not null, 
26	primary key (id)
27)   
28
29
30/*
31 * Table: Order
32 * Description: Contains base order details
33 */
34create table  order (
35	id bigserial not null, 
36	customer_id bigint not null,
37	orderNumber text not null, 
38	timeOrderPlaced timestamp not null,
39	lastUpdate timestamp not null, 
40	status text not null, 
41	primary key (id)
42)   
43
44alter table  order add constraint orders_fk_1 foreign key (customer_id) references  customer (id);
45
46create table  orderItem (
47	id bigserial not null, 
48	order_id bigint not null, 
49	catalogitem_id bigint not null, 
50	status text not null, 
51	price decimal(20,5), 
52	lastUpdate timestamp not null, 
53	quantity integer not null, 
54	primary key (id)
55)   
56
57alter table  orderItem add constraint orderItem_fk_1 foreign key (order_id) references  order (id);
58alter table  orderItem add constraint orderItem_fk_2 foreign key (catalogitem_id) references  catalogitem (id);