gnurn6q2

1USE gc200368636;
2
3DROP TABLE IF EXISTS product_author;
4DROP TABLE IF EXISTS authors;
5DROP TABLE IF EXISTS categories_products;
6DROP TABLE IF EXISTS categories;
7DROP TABLE IF EXISTS products_orders;
8DROP TABLE IF EXISTS products;
9DROP TABLE IF EXISTS orders;
10DROP TABLE IF EXISTS customers;
11
12
13
14CREATE TABLE customers (
15customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
16last_name VARCHAR(50) NOT NULL,
17first_name VARCHAR(50) NOT NULL,
18email VARCHAR(100) NOT NULL,
19username VARCHAR(20) NOT NULL,
20password VARCHAR(20) NOT NULL);
21
22CREATE TABLE orders (
23order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
24date DATE NOT NULL,
25total DEC(10,2),
26customer_id INT,
27FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
28);
29
30CREATE TABLE products (
31product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
32name1 VARCHAR(100) NOT NULL,
33description VARCHAR(8000),
34price DECIMAL(10,2)
35);
36
37/*make junction table for products and orders*/
38CREATE TABLE products_orders(
39products_orders_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
40quantity INT,
41product_id INT,
42order_id INT,
43FOREIGN KEY(product_id) REFERENCES products(product_id),
44FOREIGN KEY(order_id) REFERENCES orders(order_id)
45);
46
47CREATE TABLE categories (
48category_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
49Name VARCHAR(100) NOT NULL);
50
51/*Junction table for products and category*/
52CREATE TABLE categories_products (
53categories_products_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
54product_id INT NOT NULL,
55category_id INT NOT NULL,
56FOREIGN KEY (product_id) REFERENCES products(product_id),
57FOREIGN KEY (category_id) REFERENCES categories(category_id));
58
59CREATE TABLE authors (
60author_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
61name VARCHAR(40)
62);
63
64/*Junction table for authors and products*/
65CREATE TABLE product_author (
66product_author_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
67product_id INT NOT NULL,
68author_id INT NOT NULL,
69FOREIGN KEY (product_id) REFERENCES products(product_id),
70FOREIGN KEY (author_id) REFERENCES authors(author_id)
71);