Project

Profile

Help

HostedRedmine.com has moved to the Planio platform. All logins and passwords remained the same. All users will be able to login and use Redmine just as before. Read more...

Task #711537 » books_shop_create.sql

Лапыгин Вадим, 2017-11-14 10:57 PM

 
-- Created by Vertabelo (http://vertabelo.com)
-- Last modification date: 2017-11-14 22:17:50.047

-- tables
-- Table: Book
CREATE TABLE Book (
barcode_book int NOT NULL,
section_literature varchar(20) NOT NULL,
name varchar(30) NOT NULL,
authors varchar(100) NOT NULL,
publisher varchar(100) NOT NULL,
year_of_publication date NOT NULL,
price int NOT NULL,
Shopping_cart_order_code int NOT NULL,
CONSTRAINT Book_pk PRIMARY KEY (barcode_book)
);

-- Table: Courier
CREATE TABLE Courier (
code_carrier int NOT NULL,
ful_name varchar(50) NOT NULL,
date_of_birth date NOT NULL,
employment_date date NOT NULL,
working_shift varchar(20) NOT NULL,
Order_order_code int NOT NULL,
Order_2_order_code int NOT NULL,
CONSTRAINT Courier_pk PRIMARY KEY (code_carrier)
);

-- Table: Customer
CREATE TABLE Customer (
customer_code int NOT NULL,
organization varchar(50) NOT NULL,
full_name varchar(50) NOT NULL,
phone int NOT NULL,
email_adress varchar(50) NOT NULL,
mailing_address int NOT NULL,
Order_order_code int NOT NULL,
CONSTRAINT Customer_pk PRIMARY KEY (customer_code)
);

-- Table: Order
CREATE TABLE `Order` (
order_code int NOT NULL COMMENT '(8 digits)',
customer_code int NOT NULL,
form_of_payment char NOT NULL,
order_date date NOT NULL,
delivery_date date NOT NULL,
type_of_delivery varchar(20) NOT NULL,
delivery_price int NOT NULL,
code_carrier int NOT NULL,
delivery_address varchar(20) NOT NULL,
Shopping_cart_order_code int NOT NULL,
CONSTRAINT Order_pk PRIMARY KEY (order_code)
);

-- Table: Shopping_cart
CREATE TABLE Shopping_cart (
order_code int NOT NULL COMMENT '(8 digits)',
barcode_book int NOT NULL,
amount int NOT NULL,
CONSTRAINT Shopping_cart_pk PRIMARY KEY (order_code)
);

-- foreign keys
-- Reference: Book_Shopping_cart (table: Shopping_cart)
ALTER TABLE Shopping_cart ADD CONSTRAINT Book_Shopping_cart FOREIGN KEY Book_Shopping_cart (barcode_book)
REFERENCES Book (barcode_book);

-- Reference: Courier_Order (table: Order)
ALTER TABLE `Order` ADD CONSTRAINT Courier_Order FOREIGN KEY Courier_Order (code_carrier)
REFERENCES Courier (code_carrier);

-- Reference: Customer_Order (table: Order)
ALTER TABLE `Order` ADD CONSTRAINT Customer_Order FOREIGN KEY Customer_Order (customer_code)
REFERENCES Customer (customer_code);

-- Reference: Order_Shopping_cart (table: Order)
ALTER TABLE `Order` ADD CONSTRAINT Order_Shopping_cart FOREIGN KEY Order_Shopping_cart (order_code)
REFERENCES Shopping_cart (order_code);

-- End of file.

    (1-1/1)