aLJnVS1N

1/*
2	DDL 
3    by Frge18
4*/
5
6-- Drop tables
7DROP TABLE IF EXISTS Lager;
8DROP TABLE IF EXISTS Produktkategori;
9DROP TABLE IF EXISTS Produktregister;
10DROP TABLE IF EXISTS Plocklista;
11DROP TABLE IF EXISTS Hylla;
12DROP TABLE IF EXISTS Logg;
13DROP TABLE IF EXISTS Faktura;
14DROP TABLE IF EXISTS E_Order;
15DROP TABLE IF EXISTS Produkt;
16DROP TABLE IF EXISTS Kundregister;
17DROP TABLE IF EXISTS Kund;
18
19-- Create tables
20CREATE TABLE Kund
21(
22person_nr DATE PRIMARY KEY NOT NULL, 
23namn VARCHAR(10) NOT NULL,
24telefon_nr INT (10) NOT NULL,
25antal int
26);
27
28CREATE TABLE Kundregister (
29    register_id INT PRIMARY KEY NOT NULL,
30    kund_id DATE NOT NULL,
31    FOREIGN KEY (kund_id)
32        REFERENCES Kund (person_nr)
33);
34
35CREATE TABLE Produkt (
36    id INT PRIMARY KEY NOT NULL,
37    namn VARCHAR(20) NOT NULL,
38    beskrivning VARCHAR(100) NOT NULL
39);
40
41CREATE TABLE E_Order (
42    id INT PRIMARY KEY AUTO_INCREMENT,
43    kund_id DATE NOT NULL,
44    produkt_id INT NOT NULL,
45    antal INT NOT NULL,
46    FOREIGN KEY (kund_id)
47        REFERENCES Kundregister (kund_id),
48    FOREIGN KEY (produkt_id)
49        REFERENCES Produkt (id)
50);
51
52CREATE TABLE Faktura (
53    id INT PRIMARY KEY AUTO_INCREMENT,
54    order_id INT NOT NULL,
55    kund_id DATE NOT NULL,
56    summa DECIMAL NOT NULL,
57    FOREIGN KEY (order_id)
58        REFERENCES E_Order (id),
59    FOREIGN KEY (kund_id)
60        REFERENCES Kundregister (kund_id)
61);
62
63CREATE TABLE Logg (
64    id INT PRIMARY KEY AUTO_INCREMENT,
65    datum DATE NOT NULL,
66    beskrivning VARCHAR(400) NOT NULL,
67    tid TIME NOT NULL
68);
69
70CREATE TABLE Hylla (
71    id INT PRIMARY KEY NOT NULL,
72    namn VARCHAR(20) NOT NULL
73);
74
75CREATE TABLE Plocklista (
76    id INT PRIMARY KEY AUTO_INCREMENT,
77    order_id INT NOT NULL,
78    produkt_id INT NOT NULL,
79    order_antal INT NOT NULL,
80    hylla_id INT NOT NULL,
81    FOREIGN KEY (order_id)
82        REFERENCES E_Order (id),
83    FOREIGN KEY (produkt_id)
84        REFERENCES E_Order (produkt_id),
85    FOREIGN KEY (hylla_id)
86        REFERENCES Hylla (id)
87);
88
89CREATE TABLE Produktregister (
90    id INT PRIMARY KEY AUTO_INCREMENT,
91    produkt_id INT NOT NULL,
92    pris INT NOT NULL,
93    FOREIGN KEY (produkt_id)
94        REFERENCES E_Order (produkt_id)
95);
96
97CREATE TABLE Produktkategori (
98    namn VARCHAR(20) PRIMARY KEY NOT NULL
99);
100
101CREATE TABLE Lager (
102    namn VARCHAR(20) PRIMARY KEY NOT NULL,
103    id_hyllplats INT NOT NULL,
104    antal INT NOT NULL,
105    FOREIGN KEY (id_hyllplats)
106        REFERENCES Hylla (id)
107);