-
Notifications
You must be signed in to change notification settings - Fork 0
/
petshop032017.sql
78 lines (63 loc) · 1.7 KB
/
petshop032017.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
set echo on
connect system/amakal
--You may change the password 'amakal' according to your computer's oracle password.
drop user petshop cascade;
create user petshop identified by kankaloo;
grant connect, resource to petshop;
alter user petshop default tablespace users temporary tablespace temp account unlock;
connect petshop/kankaloo;
create table admin (
admin_username varchar2(40) primary key,
admin_password varchar2(50)
);
create table customer (
cust_id number primary key,
cust_user varchar2(40) not null,
cust_pass varchar2(20) not null,
cust_email varchar2(50) not null,
cust_lname varchar2(50) not null,
cust_fname varchar2(50) not null,
cust_mi varchar2(3),
cust_contact varchar2(20),
cust_contact2 varchar2(20),
cust_bdate varchar(10),
cust_gender varchar(6),
cust_region varchar(30),
cust_city varchar(30),
cust_zip varchar(10),
cust_image varchar(200)
);
create table pets (
pet_id number primary key,
pet_title varchar2(150) not null,
pet_desc varchar2(400),
pet_image varchar(200),
pet_stockqty number(5,0),
pet_price number(10,2)
);
create table cart_item (
citem_id number primary key,
citem_totalcost number(10,2),
citem_qty number(5,0)
);
create table cart (
cart_id number primary key,
cart_totalcost number(10,2)
);
create table payment (
payment_id number primary key,
payment_totalamount number(10,2),
payment_shipfee number(10,2)
);
CREATE SEQUENCE customer_sequence;
CREATE OR REPLACE TRIGGER "customer_on_insert"
BEFORE INSERT ON customer
FOR EACH ROW
BEGIN
SELECT customer_sequence.nextval
INTO :new.cust_id
FROM dual;
END;
/
ALTER TRIGGER "customer_on_insert" ENABLE
/