99D22yqg

1-- Oppgave A
2
3DROP TABLE IF EXISTS Onske;
4DROP TABLE IF EXISTS Oppbygging;
5DROP TABLE IF EXISTS Del;
6DROP TABLE IF EXISTS Gave;
7DROP TABLE IF EXISTS Person;
8
9CREATE TABLE IF NOT EXISTS Person
10(
11Pnr INTEGER,
12Fornavn VARCHAR(45),
13Etternavn VARCHAR(45),
14fDato DATE,
15
16PRIMARY KEY(Pnr)
17)ENGINE = InnoDB;
18
19CREATE TABLE IF NOT EXISTS Gave
20(
21gnr INTEGER,
22navn VARCHAR(20) UNIQUE,
23prod_tid INTEGER NOT NULL,
24
25PRIMARY KEY(gnr)
26)ENGINE = InnoDB;
27
28CREATE TABLE IF NOT EXISTS Del
29(
30dnr INTEGER,
31navn VARCHAR(45),
32lager_ant INTEGER NOT NULL,
33
34PRIMARY KEY(dnr)
35)ENGINE = InnoDB;
36
37CREATE TABLE IF NOT EXISTS Oppbygging
38(
39gnr INTEGER,
40dnr INTEGER,
41ant INTEGER,
42
43PRIMARY KEY(gnr, dnr),
44FOREIGN KEY(gnr) REFERENCES Gave(gnr),
45FOREIGN KEY(dnr) REFERENCES Del(dnr)
46)ENGINE = InnoDB;
47
48CREATE TABLE IF NOT EXISTS Onske
49(
50onr INTEGER,
51pnr INTEGER,
52gnr INTEGER,
53prioritet INTEGER,
54Ferdig INTEGER,
55
56PRIMARY KEY(onr),
57FOREIGN KEY(pnr) REFERENCES Person(pnr),
58FOREIGN KEY(gnr) REFERENCES Gave(gnr)
59)ENGINE = InnoDB;