thecoded.prof
CMU Coding Bootcamp
1CREATE SCHEMA SUPPLY_CHAIN;
2
3CREATE TABLE SUPPLY_CHAIN.SUPPLIER (
4 supnr CHAR(4) PRIMARY KEY,
5 supname VARCHAR(40) NOT NULL,
6 supaddress VARCHAR(50),
7 supcity VARCHAR(20),
8 supstatus SMALLINT CHECK (supstatus >= 0 AND supstatus <= 100),
9 supcategory VARCHAR(10) DEFAULT 'SILVER' NOT NULL
10);
11
12CREATE TYPE SUPPLY_CHAIN.product_type AS ENUM ('white', 'red', 'rose', 'sparkling');
13
14CREATE TABLE SUPPLY_CHAIN.PRODUCT (
15 prodnr CHAR(6) PRIMARY KEY,
16 prodname VARCHAR(60) UNIQUE NOT NULL,
17 prodtype SUPPLY_CHAIN.product_type,
18 available_quantity INTEGER,
19 prodimage BYTEA
20);
21
22CREATE TABLE SUPPLY_CHAIN.SUPPLIES (
23 supnr CHAR(4)
24 NOT NULL
25 REFERENCES SUPPLY_CHAIN.SUPPLIER(supnr)
26 ON DELETE CASCADE
27 ON UPDATE CASCADE,
28 prodnr CHAR(6)
29 NOT NULL
30 REFERENCES SUPPLY_CHAIN.PRODUCT(prodnr)
31 ON DELETE CASCADE
32 ON UPDATE CASCADE,
33 purchase_price DECIMAL(8,2),
34 deliv_period TIME,
35 PRIMARY KEY (supnr, prodnr)
36);
37
38COMMENT ON COLUMN SUPPLY_CHAIN.SUPPLIES.purchase_price IS 'in EUR';
39COMMENT ON COLUMN SUPPLY_CHAIN.SUPPLIES.deliv_period IS 'in days';
40
41CREATE TABLE SUPPLY_CHAIN.PURCHASE_ORDER (
42 ponr CHAR(7) PRIMARY KEY,
43 podate DATE,
44 supnr CHAR(4)
45 NOT NULL
46 REFERENCES SUPPLY_CHAIN.SUPPLIER(supnr)
47 ON DELETE CASCADE
48 ON UPDATE CASCADE
49);
50
51CREATE TABLE SUPPLY_CHAIN.PO_LINE (
52 ponr CHAR(7)
53 NOT NULL
54 REFERENCES SUPPLY_CHAIN.PURCHASE_ORDER(ponr)
55 ON DELETE CASCADE
56 ON UPDATE CASCADE,
57 prodnr CHAR(6)
58 NOT NULL
59 REFERENCES SUPPLY_CHAIN.PRODUCT(prodnr)
60 ON DELETE CASCADE
61 ON UPDATE CASCADE,
62 quantity INTEGER,
63 PRIMARY KEY (ponr, prodnr)
64);