CREATE TABLE address (
id integer NOT NULL,
street VARCHAR(64),
house_number VARCHAR(10) NOT NULL,
flat_number VARCHAR(10),
town VARCHAR(64) NOT NULL,
postal_code VARCHAR(6) NOT NULL,
post_office varchar(64) not null
);
ALTER TABLE address ADD CONSTRAINT address_pk PRIMARY KEY (id);
CREATE TABLE users (
id integer NOT NULL,
username VARCHAR(32) not null,
pass_sha256 char(64) not null,
pass_salt char(64) not null,
code_sha256 char(64),
email varchar(128) not null,
phone varchar(16),
mobile varchar(16),
status integer not null,
first_name VARCHAR(64),
last_name VARCHAR(64),
pesel char(11),
birth_date DATE,
nationality VARCHAR(64),
home_address integer not null,
mailing_address integer,
register_date timestamp,
last_login_date timestamp
);
ALTER TABLE users ADD CONSTRAINT users_pk PRIMARY KEY (id);
ALTER TABLE users ADD CONSTRAINT users_home_address_fk FOREIGN KEY (home_address)
REFERENCES address (id);
ALTER TABLE users ADD CONSTRAINT users_mailing_address_fk FOREIGN KEY (mailing_address)
REFERENCES address (id);
CREATE TABLE "payments_status" (
"id" integer NOT NULL,
"description" varchar(200) NOT NULL
);
ALTER TABLE "payments_status" ADD CONSTRAINT "payments_status_pk" PRIMARY KEY ("id");
CREATE TABLE "students" (
"user_id" integer NOT NULL
);
ALTER TABLE "students" ADD CONSTRAINT "students_pk" PRIMARY KEY ("user_id");
ALTER TABLE "students" ADD CONSTRAINT "students_user_id_fk" FOREIGN KEY ("user_id")
REFERENCES "users" ("id");
CREATE TABLE "payments" (
"id" integer NOT NULL,
"amount" numeric(9,2) NOT NULL,
"date" date NOT NULL,
"student_id" integer NOT NULL,
"accepted_by" integer NOT NULL,
"status" integer NOT NULL,
"due_date" date,
"description" integer
);
ALTER TABLE "payments" ADD CONSTRAINT "payments_pk" PRIMARY KEY ("id");
ALTER TABLE "payments" ADD CONSTRAINT "payments_student_id_fk" FOREIGN KEY ("student_id")
REFERENCES "student" ("user_id");
ALTER TABLE "payments" ADD CONSTRAINT "payments_status_fk" FOREIGN KEY ("status")
REFERENCES "payments_status" ("id");
CREATE TABLE "categories" (
"category_code" varchar(4) NOT NULL,
"category_name" varchar NOT NULL
);
ALTER TABLE "categories" ADD CONSTRAINT "categories_pk" PRIMARY KEY ("category_code");
CREATE TABLE "student_categories" (
"student_id" integer NOT NULL,
"category_code" varchar(4) NOT NULL
);
ALTER TABLE "student_categories" ADD CONSTRAINT "student_categories_pk" PRIMARY KEY ("student_id","category_code");
ALTER TABLE "student_categories" ADD CONSTRAINT "student_categories_student_id_fk" FOREIGN KEY ("student_id")
REFERENCES "students" ("user_id");
ALTER TABLE "student_categories" ADD CONSTRAINT "student_categories_category_code_fk" FOREIGN KEY ("category_code")
REFERENCES "categories" ("category_code");
CREATE TABLE "courses" (
"id" integer NOT NULL,
"start_date" date NOT NULL,
"type" varchar(3) NOT NULL,
"practical_training" integer NOT NULL,
"theoretical_training" integer NOT NULL
);
ALTER TABLE "courses" ADD CONSTRAINT "courses_pk" PRIMARY KEY ("id");
CREATE TABLE "cars" (
"id" integer NOT NULL,
"registration_number" varchar(7) NOT NULL,
"brand" varchar(50) NOT NULL,
"model" varchar(50) NOT NULL,
"colour" varchar(50) NOT NULL,
"year" integer NOT NULL,
"examination_date" date,
"insurance_date" date,
"category" varchar(4),
"disctance" integer
);
ALTER TABLE "cars" ADD CONSTRAINT "cars_pk" PRIMARY KEY ("id");
ALTER TABLE "cars" ADD CONSTRAINT "cars_category_fk" FOREIGN KEY ("category")
REFERENCES "categories" ("category_code");
CREATE TABLE "student_courses" (
"course_id" integer NOT NULL,
"student_id" integer NOT NULL,
"medical_certificate" bool,
"instructor" integer,
"certificate_number" varchar(10),
"end_date" date
);
ALTER TABLE "student_courses" ADD CONSTRAINT "student_courses_pk" PRIMARY KEY ("course_id","student_id");
ALTER TABLE "student_courses" ADD CONSTRAINT "student_courses_course_id_fk" FOREIGN KEY ("course_id")
REFERENCES "courses" ("id");
ALTER TABLE "student_courses" ADD CONSTRAINT "student_courses_student_id_fk" FOREIGN KEY ("student_id")
REFERENCES "students" ("user_id");
ALTER TABLE "student_courses" ADD CONSTRAINT "student_courses_instructor_fk" FOREIGN KEY ("instructor")
REFERENCES "instructors" ("user_id");
CREATE TABLE "internal_exams" (
"id" integer NOT NULL,
"student_id" integer NOT NULL,
"course_id" integer NOT NULL,
"type" char(1) NOT NULL,
"result" bool NOT NULL DEFAULT False,
"date" date NOT NULL,
"instructor" integer NOT NULL,
"errors" varchar(500)
);
ALTER TABLE "internal_exams" ADD CONSTRAINT "internal_exams_pk" PRIMARY KEY("id","student_id","course_id");
ALTER TABLE "internal_exams" ADD CONSTRAINT "internal_exams_student_course_id_fk" FOREIGN KEY ("student_id", "course_id")
REFERENCES "student_courses"("student_id", "course_id");
ALTER TABLE "internal_exams" ADD CONSTRAINT "internal_exams_instructor_fk" FOREIGN KEY ("instructor")
REFERENCES "instructors" ("user_id");
CREATE TABLE "status" (
"id" integer NOT NULL,
"name" varchar(100)
);
ALTER TABLE "status" ADD CONSTRAINT "status_pk" PRIMARY KEY("id");
CREATE TABLE "driving_lessons" (
"id" integer NOT NULL,
"student_id" integer NOT NULL,
"instructor_id" integer NOT NULL,
"start_time" timestamp NOT NULL,
"end_time" timestamp NOT NULL,
"course_id" integer NOT NULL,
"status" integer NOT NULL,
"distance" integer,
"car_id" integer NOT NULL
);
ALTER TABLE "driving_lessons" ADD CONSTRAINT "driving_lessons_pk" PRIMARY KEY("id");
ALTER TABLE "driving_lessons" ADD CONSTRAINT "driving_lessons_student_id_fk" FOREIGN KEY ("student_id")
REFERENCES "students" ("user_id");
ALTER TABLE "driving_lessons" ADD CONSTRAINT "driving_lessons_instructor_id_fk" FOREIGN KEY ("instructor_id")
REFERENCES "instructors" ("user_id");
ALTER TABLE "driving_lessons" ADD CONSTRAINT "driving_lessons_course_id_fk" FOREIGN KEY ("course_id")
REFERENCES "courses" ("id");
ALTER TABLE "driving_lessons" ADD CONSTRAINT "driving_lessons_status_fk" FOREIGN KEY ("status")
REFERENCES "status" ("id");
ALTER TABLE "driving_lessons" ADD CONSTRAINT "driving_lessons_car_id_fk" FOREIGN KEY ("car_id")
REFERENCES "cars" ("id");
CREATE TABLE "theoretical_lessons" (
"id" integer NOT NULL,
"course_id" integer,
"instuctor_id" integer,
"subject" varchar(200),
"date" timestamp NOT NULL,
"hours" integer NOT NULL,
"class" varchar(5)
);
ALTER TABLE "theoretical_lessons" ADD CONSTRAINT "theoretical_lessons_pk" PRIMARY KEY("id");
ALTER TABLE "theoretical_lessons" ADD CONSTRAINT "theoretical_lessons_course_id_fk" FOREIGN KEY ("course_id")
REFERENCES "courses" ("id");
ALTER TABLE "theoretical_lessons" ADD CONSTRAINT "theoretical_lessons_instructor_id_fk" FOREIGN KEY ("instuctor_id")
REFERENCES "instructors" ("user_id");
CREATE TABLE "comments" (
"id" integer NOT NULL,
"place_id" integer,
"comment" varchar(1024) NOT NULL,
"author" integer,
"date" date,
"grade" integer
);
ALTER TABLE comments ADD CONSTRAINT comments_pk PRIMARY KEY (id);
CREATE TABLE "interesting_places" (
"id" integer NOT NULL,
"lattitude" float8 NOT NULL,
"longitude" float8 NOT NULL,
"type_id" integer,
"description" varchar(512),
"accepted" bool NOT NULL,
"city" varchar(32) NOT NULL,
"author" varchar(32) NOT NULL,
"date" date NOT NULL
);
ALTER TABLE interesting_places ADD CONSTRAINT interesting_places_pk PRIMARY KEY (id);
CREATE TABLE "types" (
"id" integer NOT NULL,
"name" varchar(256) NOT NULL,
"description" varchar(1024),
PRIMARY KEY("id")
);
ALTER TABLE "types" ADD CONSTRAINT types_pk PRIMARY KEY (id);
ALTER TABLE "comments" ADD CONSTRAINT "comments_users_fk" FOREIGN KEY ("author") REFERENCES "users"("id")
ALTER TABLE "comments" ADD CONSTRAINT "comments_interesting_places_fk" FOREIGN KEY ("place_id") REFERENCES "interesting_places"("id")
ALTER TABLE "interesting_places" ADD CONSTRAINT "interesting_places_types_fk" FOREIGN KEY ("type_id") REFERENCES "types"("id")
ALTER TABLE "interesting_places" ADD CONSTRAINT "interesting_places_users_fk" FOREIGN KEY ("author") REFERENCES "users"("id")