CREATE TABLE employee (
  id number(10) NOT NULL,
  first_name varchar2(20) NOT NULL,
  last_name varchar2(20) NOT NULL,
  login varchar2(20) NOT NULL,
  password varchar2(20) NOT NULL,
  CONSTRAINT employee_pk PRIMARY KEY(id)
) ;

CREATE TABLE student (
  PESEL number(11) NOT NULL,
  first_name varchar2(20) NOT NULL,
  last_name varchar2(20) NOT NULL,
  address varchar2(50) NOT NULL,
  osk_course_status number(1) NOT NULL,
  login varchar2(20) DEFAULT NULL,
  password varchar2(20) DEFAULT NULL,
  CONSTRAINT student_pk PRIMARY KEY(PESEL)
) ;
CREATE TABLE t_exam_dates (
  exam_time timestamp(0) NOT NULL,
  free_places number(10) NOT NULL,
  CONSTRAINT t_exam_dates_pk PRIMARY KEY(exam_time)
) ;

CREATE TABLE p_exam_dates (
  exam_time timestamp(0) NOT NULL,
  free_places number(10) NOT NULL,
  CONSTRAINT p_exam_dates_pk PRIMARY KEY(exam_time)
) ;







CREATE TABLE signed_exams (
  id number(10) NOT NULL,
  first_name varchar2(20) NOT NULL,
  last_name varchar2(20) NOT NULL,
  PESEL number(11) NOT NULL,
  exam_time timestamp(0) NOT NULL,
  examiner_id number(10) NOT NULL,
  vehicle_id number(10) NOT NULL,
  exam_type varchar2(2) NOT NULL,
  CONSTRAINT signed_exams_pk PRIMARY KEY(id)
) ;

CREATE TABLE signed_exams_t (
  id number(10) NOT NULL,
  first_name varchar2(20) NOT NULL,
  last_name varchar2(20) NOT NULL,
  PESEL number(11) NOT NULL,
  exam_time timestamp(0) NOT NULL,
  score number(3) NOT NULL,
  CONSTRAINT signed_exams_t_pk PRIMARY KEY(id)
) ;

CREATE TABLE examiner (
  id number(10) NOT NULL,
  first_name varchar2(20) NOT NULL,
  last_name varchar2(20) NOT NULL,
  PESEL number(19) NOT NULL,
  address varchar2(50) NOT NULL,
  CONSTRAINT examiner_pk PRIMARY KEY(id)
) ;
CREATE TABLE vehicle (
  id number(10) NOT NULL,
  brand varchar2(15) NOT NULL,
  model varchar2(15) NOT NULL,
  plate_number varchar2(8) NOT NULL,
  type varchar2(15) NOT NULL,
  CONSTRAINT vehicle_pk PRIMARY KEY(id)
) ;




CREATE TABLE yes_no_questions (
  id_question number(10) NOT NULL,
  question_content varchar2(250) NOT NULL,
  correct_answer number(2) NOT NULL,
  CONSTRAINT yes_no_questions_pk PRIMARY KEY(id_question)
) ;

CREATE TABLE abc_questions (
  id_question number(10) NOT NULL,
  question_content varchar2(200) NOT NULL,
  answer_A varchar2(150) NOT NULL,
  answer_B varchar2(150) NOT NULL,
  answer_C varchar2(150) NOT NULL,
  correct_answer number(10) NOT NULL,
  CONSTRAINT abc_questions_pk PRIMARY KEY(id_question)
) ;

CREATE TABLE payment_p (
  id number(4) NOT NULL,
  p_exam_id number(10) NOT NULL,
  PESEL number(11) NOT NULL,
  amount number(5) NOT NULL,
  pay_type number(2) NOT NULL,
  CONSTRAINT payment_p_pk PRIMARY KEY(id)
) ;

CREATE TABLE payment_t (
  id number(4) NOT NULL,
  t_exam_id number(10) NOT NULL,
  PESEL number(11) NOT NULL,
  amount number(5) NOT NULL,
  pay_type number(2) NOT NULL,
  CONSTRAINT payment_t_pk PRIMARY KEY(id)
) ;

CREATE TABLE payment_type (
  id number(2) NOT NULL,
  type varchar2(20) NOT NULL,
  CONSTRAINT payment_type_pk PRIMARY KEY(id)
) ;