DB
gcreate table grade (
id BIGSERIAL NOT NULL PRIMARY KEY,
grade_name varchar(30) NOT NULL,
id_ctype bigint NOT NULL foreign key on update cascade on delete cascade
);
create table ctype (
id BIGSERIAL NOT NULL PRIMARY KEY,
ctype_name varchar(30) NOT NULL
);
create table roast (
id BIGSERIAL NOT NULL PRIMARY KEY,
roast_name varchar(30) NOT NULL
);
create table plantation (
id BIGSERIAL NOT NULL PRIMARY KEY,
id_country bigint NOT NULL foreign key on update cascade on delete cascade,
id_area bigint NOT NULL foreign key on update cascade on delete cascade
);
create table area (
id BIGSERIAL NOT NULL PRIMARY KEY,
area_name varchar(30) NOT NULL
);
create table country (
id BIGSERIAL NOT NULL PRIMARY KEY,
country_name varchar(30) NOT NULL
);
create table processing_way (
id BIGSERIAL NOT NULL PRIMARY KEY,
proc_name varchar(30) NOT NULL
);
create table coffe (
id BIGSERIAL NOT NULL PRIMARY KEY,
coffe_name varchar(30) NOT NULL ,
price numeric(20,3) not null,
id_dealer bigint NOT NULL foreign key on update cascade on delete cascade,
id_grades bigint NOT NULL foreign key on update cascade on delete cascade,
id_roast bigint NOT NULL foreign key on update cascade on delete cascade,
id_plantation bigint NOT NULL foreign key on update cascade on delete cascade,
id_processing_way bigint NOT NULL foreign key on update cascade on delete cascade
);
create table dealer (
id BIGSERIAL NOT NULL PRIMARY KEY,
dealer_name varchar(30) NOT NULL ,
contact varchar(30) NOT NULL ,
adress varchar(100) NOT NULL
);
create table b_order (
id BIGSERIAL NOT NULL PRIMARY KEY,
date_of_order DATE NOT NULL ,
prepay numeric(20,5) not null
id_coffe bigint NOT NULL foreign key on update cascade on delete cascade,
id_buyer bigint NOT NULL foreign key on update cascade on delete cascade,
id_contrib bigint NOT NULL foreign key on update cascade on delete cascade
);
create table contrib (
id BIGSERIAL NOT NULL PRIMARY KEY,
full_name varchar(50) NOT NULL ,
date_of_b DATE not null,
contact_p numeric(11) not null
id_post bigint NOT NULL foreign key on update cascade on delete cascade,
id_shop bigint NOT NULL foreign key on update cascade on delete cascade,
);
create table post (
id BIGSERIAL NOT NULL PRIMARY KEY,
post_name varchar(30) NOT NULL
);
create table shop (
id BIGSERIAL NOT NULL PRIMARY KEY,
adress varchar(100) NOT NULL ,
contact_p numeric(11) not null
);
create table buyer (
id BIGSERIAL NOT NULL PRIMARY KEY,
full_name varchar(50) NOT NULL ,
contact_p numeric(11) not null,
email varchar(50) NOT NULL
);
insert into ctype (ctype_name) values
('Arabica '),
('Canephora '),
('Charrieriana'),
('Liberica'),
('Benghalensis');
insert into grade (grade_name) values
('tipica'),
('Geisha'),
('Cattura'),
('Bourbon'),
('Nanu'),
('Kuilu'),
('Java Inneak'),
('India Anohki');
insert into roast (roast_name) values
('Low'),
('Medium'),
('Hight');
insert into area (area_name) values
('Brazil'),
('Peru'),
('Colombia'),
('Honduras'),
('Mexico'),
('Guatemala'),
('Vietnam'),
('Indonesia.');
insert into country (country_name) values
('Central America'),
('South America'),
('Asia'),
('Africa');
insert into processing_way (proc_name) values
('Human_processing'),
('manufacturing_processing');
insert into coffe (coffe_name, price) values
('americano', '200.2'),
('mokachino' ,'300.3'),
('latte', '400.2'),
('espresso', '600.2'),
('ice coffe', '200.21');
insert into dealer (dealer_name, contact, adress) values
('Sweet Bro', '79275426741', '4046 Isaacs Creek Road'),
('Big Bro', '31920432851', '4650 Losh Lane'),
('Just Man', '12094827461', '2203 Stratford Park');
insert into b_order (date_of_order, prepay) values
('2000-12-12', '150'),
('2020-11-12', '100'),
('2000-11-12', '50');
insert into buyer (full_name, contact_p, email) values
('Dylan Davis', '1287346240', 'ss1@gmail.com'),
('Richard Wilson', '1782345128', 'Rw1@gmail.com'),
('Brenda Nelson', '1248712652', 'sBsN1@gmail.com');
insert into contrib (full_name, date_of_b, contact_p) values
('David Birch', '1999-12-12', '2091235451'),
('Angela Robinson', '1982-09-12', '219342512'),
('Joseph Fulton', '1999-03-01', '1332546980');
insert into post (post_name) values
('Admin'),
('Seller'),
('Cleaner');
insert into shop (adress, contact_p) values
('4113 Arbutus Drive', '12874667234'),
('3581 Parkway Drive', '1092428572'),
('1151 Village View Drive', '1098254725');
drop table area, b_order, buyer, coffe, contrib, country, ctype, dealer, grade, plantation, post, processing_way, roast, shop