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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
|
/*
This file should be executed to create the tables. It contains the constraints
for foreign keys and composite keys.
This file is going to be used to generate the JPA stuffs.
Changes to this file can come at any time.
*/
CREATE TABLE IF NOT EXISTS student (
id SERIAL PRIMARY KEY,
user_name VARCHAR (32) NOT NULL UNIQUE,
pwd VARCHAR (256) NOT NULL,
/* name */
name_first VARCHAR (32) NOT NULL,
name_last VARCHAR (32) NOT NULL,
class VARCHAR (32) /* This could be a foreign key :-) */
/* address */
/*
address_country VARCHAR (32) NOT NULL,
address_city VARCHAR (32) NOT NULL,
address_street VARCHAR (32) NOT NULL,
address_postal_code VARCHAR (32) NOT NULL,
birthday DATE NOT NULL
*/
);
CREATE TABLE IF NOT EXISTS staff_type (
id SERIAL PRIMARY KEY,
name VARCHAR (32) UNIQUE
);
/* Add staff types to database ************************************************/
INSERT INTO staff_type (id, name) VALUES (0, 'NONE'); /* We need to have the
zero:th value be none
so we don't get caught
in the 'null trap'.
*/
INSERT INTO staff_type (name) VALUES ('Admin');
INSERT INTO staff_type (name) VALUES ('Teacher');
/******************************************************************************/
CREATE TABLE IF NOT EXISTS staff (
id SERIAL PRIMARY KEY,
user_name VARCHAR (32) NOT NULL UNIQUE,
pwd VARCHAR (256) NOT NULL,
staff_type BIGINT,
/* name */
name_first VARCHAR (32) NOT NULL,
name_last VARCHAR (32) NOT NULL,
/*
employee_nr VARCHAR (32) NOT NULL UNIQUE,
*/
CONSTRAINT staff_type_kf FOREIGN KEY (staff_type)
REFERENCES staff_type (id)
);
CREATE TABLE IF NOT EXISTS course (
id SERIAL PRIMARY KEY,
name VARCHAR (30) UNIQUE,
description TEXT
);
CREATE TABLE IF NOT EXISTS lecture (
course BIGINT NOT NULL,
start_time TIME NOT NULL,
end_time TIME NOT NULL,
CONSTRAINT course_fk FOREIGN KEY (course)
REFERENCES course (id),
CONSTRAINT lecture_pk PRIMARY KEY (course, start_time, end_time)
);
CREATE TABLE IF NOT EXISTS teaches (
course BIGINT NOT NULL,
staff BIGINT NOT NULL,
CONSTRAINT course_fk FOREIGN KEY (course)
REFERENCES course (id),
CONSTRAINT staff_fk FOREIGN KEY (staff)
REFERENCES course (id),
CONSTRAINT teaches_pk PRIMARY KEY (course, staff)
);
CREATE TABLE IF NOT EXISTS student_attendance (
lecture_course BIGINT NOT NULL,
lecture_start_time TIME NOT NULL,
lecture_end_time TIME NOT NULL,
student BIGINT NOT NULL,
late_time INTEGER,
CONSTRAINT lecture_fk FOREIGN KEY (lecture_course,
lecture_start_time,
lecture_end_time)
REFERENCES lecture (course,
start_time,
end_time),
CONSTRAINT student_fk FOREIGN KEY (student)
REFERENCES student (id),
CONSTRAINT student_attendance_pk PRIMARY KEY (lecture_course,
lecture_start_time,
lecture_end_time,
student)
);
|