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
|
create schema if not exists vmdb;
CREATE TABLE vmdb.Users (
CIP CHAR(8) NOT NULL,
Name VARCHAR NOT NULL,
Surname VARCHAR NOT NULL,
PRIMARY KEY (CIP)
);
CREATE TABLE vmdb.Class (
ClassNb CHAR(6) NOT NULL,
Title VARCHAR NOT NULL,
PRIMARY KEY (ClassNb)
);
CREATE TABLE vmdb.VMs (
vmID INT NOT NULL,
imageName VARCHAR NOT NULL,
Name VARCHAR NOT NULL,
OS VARCHAR NOT NULL,
Description VARCHAR NOT NULL,
ForExams BOOLEAN NOT NULL,
PRIMARY KEY (vmID),
UNIQUE (imageName)
);
CREATE TABLE vmdb.UserAttendance (
CIP CHAR(8) NOT NULL,
ClassNb CHAR(6) NOT NULL,
PRIMARY KEY (CIP, ClassNb),
FOREIGN KEY (CIP) REFERENCES vmdb.Users(CIP),
FOREIGN KEY (ClassNb) REFERENCES vmdb.Class(ClassNb)
);
CREATE TABLE vmdb.BelongsTo (
ID INT NOT NULL,
ClassNb CHAR(6) NOT NULL,
PRIMARY KEY (ID, ClassNb),
FOREIGN KEY (ID) REFERENCES vmdb.VMs(vmID),
FOREIGN KEY (ClassNb) REFERENCES vmdb.Class(ClassNb)
);
|
