Untitled
unknown
plain_text
4 years ago
2.0 kB
3
Indexable
--one to one => either one of the existing tables --many to one => many side takes one's primary key -- many to many => new relationship table CREATE TABLE Customer( user_ID int PRIMARY KEY, phone_num int, e_mail varchar(55), name_surn varchar(55), reg_date date, birthday date, user_pass varchar(12), address varchar(255) ); CREATE TABLE Shipper( ship_ID int PRIMARY KEY, compName varchar(255) ); CREATE TABLE Orderings( order_ID int PRIMARY KEY, orderDate date, shippingDate date, ship_ID int NOT NULL, FOREIGN KEY(ship_ID) REFERENCES Shipper(ship_ID), user_ID int NOT NULL, FOREIGN KEY(user_ID) REFERENCES Customer(user_ID) ); CREATE TABLE Cart( cart_ID int not null PRIMARY KEY, total_price int, num_of_items int, exp_Date date, order_ID int NOT NULL, FOREIGN KEY(order_ID) REFERENCES Orderings(order_ID), user_ID int NOT NULL, FOREIGN KEY(user_ID) REFERENCES Customer(user_ID) ); CREATE TABLE Supplier( supp_ID int NOT NULL PRIMARY KEY, comp_name varchar(255), supp_name varchar(55), product_ID varchar(15) NOT NULL, FOREIGN KEY(product_ID) REFERENCES Product(product_ID) ); CREATE TABLE Product( product_ID varchar(15) not null primary key, rate int, price int, star int, stock int, product_name varchar(255), cart_ID int NOT NULL, FOREIGN KEY(cart_ID) REFERENCES Cart(cart_ID) ); CREATE TABLE Category( category_ID int not null, category_name varchar(50) not null, product_ID varchar(15) not null, primary key(product_ID, category_ID), foreign key(product_ID) references Product(product_ID) ); CREATE TABLE Payment_Method( transaction_ID int not null, payment_type varchar(255), user_ID int NOT null, primary key (user_ID, transaction_ID), foreign key (user_ID) references Customer(user_ID) ); CREATE TABLE Ships( ship_ID int, order_ID int, PRIMARY KEY (ship_ID,order_ID), FOREIGN KEY (ship_ID) REFERENCES Shipper(ship_ID), FOREIGN KEY (order_ID) REFERENCES Orderings(order_ID) );
Editor is loading...