dbg2/store.sql at main · thomaspara/dbg2 · GitHub

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
48
49
50
51
52
53
54
55
56
57
58
CREATE DATABASE db_market;
USE db_market;

CREATE TABLE Customer (
  customer_id INT NOT NULL,
  f_name TEXT,
  l_name TEXT,
  user_password TEXT,
  email TEXT,
  PRIMARY KEY (customer_id)
);

CREATE TABLE BillingInfo (
  bill_id INT NOT NULL,
  bank_num INT,
  customer_id INT NOT NULL,
  PRIMARY KEY (bill_id),
  FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);

CREATE TABLE Seller (
  seller_id INT NOT NULL,
  seller_name TEXT,
  seller_description TEXT,
  PRIMARY KEY (seller_id)
);

CREATE TABLE Products (
  product_id INT NOT NULL,
  product_name TEXT,
  price DOUBLE,
  product_description TEXT,
  seller_id INT NOT NULL,
  PRIMARY KEY (product_id),
  FOREIGN KEY (seller_id) REFERENCES Seller(seller_id)
);

CREATE TABLE Transaction (
  transaction_id INT NOT NULL,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  seller_id INT NOT NULL,
  total_cost DOUBLE,
  PRIMARY KEY (transaction_id),
  FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id),
  FOREIGN KEY (seller_id) REFERENCES Seller(seller_id)
);

CREATE TABLE Cart (
  cart_id INT NOT NULL,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT,
  PRIMARY KEY (cart_id),
  FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);