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
|
-- Your SQL goes here
create table customer (
id integer primary key auto_increment,
username varchar(255) not null unique,
password varchar(255) not null,
phone_number varchar(10) not null,
email_id varchar(255) not null,
address text(500)
);
create table product (
id integer primary key auto_increment,
name varchar(255) not null,
kind varchar(255),
price float not null,
description varchar(255)
);
create table cart_items (
cart_id integer,
product_id integer,
constraint cart_items_pk primary key (cart_id, product_id),
foreign key (cart_id) references customer(id),
foreign key (product_id) references product(id)
);
create table rating (
id integer primary key auto_increment,
comment_text text(500),
comment_date date default curdate(),
product_id integer,
customer_id integer,
stars integer check (stars >= 0 AND stars <= 5),
foreign key (customer_id) references customer(id),
foreign key (product_id) references product(id)
);
create table transaction (
id integer primary key auto_increment,
payment_type varchar(255) not null,
amount float not null,
customer_id integer,
foreign key (customer_id) references customer(id)
);
|
