Yqbmdb9D

1drop database if exists traderjonesdb;
2create database traderjonesdb;
3use traderjonesdb;
4
5create table login_info(
6login_email varchar(255) not null,
7login_password varchar(255) not null,
8primary key(login_email)
9);
10
11create table customer_info (
12C_Fname varchar(255) not null,
13C_Lname varchar(255) not null,
14C_phonenum decimal (10),
15C_last4digitsofCC decimal (4),
16C_email varchar(255) not null,
17C_address varchar(255) not null,
18foreign key (C_email) references login_info(login_email) on delete cascade,
19primary key (C_email)
20);
21
22create table item_info(
23item_ID decimal (11) not null,
24item_name varchar (1024) not null,
25quantity tinyint not null,
26price int not null,
27item_desc text,
28primary key(item_ID)
29);
30
31create table customer_cart(
32cc_email varchar(255) not null,
33cc_item_ID decimal (11) not null,
34cc_item_name varchar (1024) not null,
35cc_quantity tinyint not null,
36foreign key (cc_email) references login_info(login_email) on delete cascade,
37foreign key (cc_item_ID) references item_info(item_ID) on delete cascade,
38primary key (cc_email, cc_item_ID)
39);
40
41create table purchase_history(
42ph_email varchar (255) not null,
43ph_item_ID decimal (11) not null,
44ph_date_purchased date not null,
45ph_price int not null,
46foreign key (ph_email) references login_info(login_email) on delete cascade,
47foreign key (ph_item_ID) references item_info(item_ID) on delete cascade,
48primary key (ph_email)
49);