API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 29th, 2020
107
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 8.27 KB | None | 0 0
raw download clone embed print report
  1. DROP TABLE person CASCADE CONSTRAINTS;
  2. DROP TABLE receptionist CASCADE CONSTRAINTS;
  3. DROP TABLE instructor CASCADE CONSTRAINTS;
  4. DROP TABLE client CASCADE CONSTRAINTS;
  5. DROP TABLE member_card CASCADE CONSTRAINTS;
  6. DROP TABLE course CASCADE CONSTRAINTS;
  7. DROP TABLE lesson CASCADE CONSTRAINTS;
  8. DROP TABLE hall CASCADE CONSTRAINTS;
  9. DROP TABLE register_to_lesson CASCADE CONSTRAINTS;
  10. DROP TABLE register_to_course CASCADE CONSTRAINTS;
  11.  
  12. DROP SEQUENCE person_sequence;
  13.  
  14. CREATE SEQUENCE person_sequence START WITH 1 INCREMENT BY 1 CACHE 20;
  15.  
  16. CREATE TABLE person(
  17.     person_id INT NOT NULL,
  18.     TYPE VARCHAR(64) NOT NULL,
  19.     first_name VARCHAR(64) NOT NULL,
  20.     last_name VARCHAR(64) NOT NULL,
  21.     date_of_birth DATE NOT NULL,
  22.     sex VARCHAR(64) NOT NULL,
  23.     CONSTRAINT persons_pk PRIMARY KEY (person_id),
  24.     CONSTRAINT sex_check CHECK(sex='male' OR sex='female'),
  25.     CONSTRAINT type_check CHECK (TYPE='receptionist' OR TYPE='instructor' OR TYPE='client'),
  26.     CONSTRAINT id_check CHECK (person_id>=1 AND person_id<=999999)
  27. );
  28.  
  29. INSERT INTO person(person_id, TYPE, first_name, last_name, date_of_birth, sex) VALUES (person_sequence.NEXTVAL, 'receptionist', 'Patrik', 'Tomov', TO_DATE('1999/06/08 14:30:25', 'YYYY/MM/DD HH24:MI:SS'), 'male');
  30. INSERT INTO person(person_id, TYPE, first_name, last_name, date_of_birth, sex) VALUES (person_sequence.NEXTVAL, 'receptionist', 'Martina', 'Studena', TO_DATE('1985/12/24 10:15:11', 'YYYY/MM/DD HH24:MI:SS'), 'female');
  31. INSERT INTO person(person_id, TYPE, first_name, last_name, date_of_birth, sex) VALUES (person_sequence.NEXTVAL, 'instructor', 'Sona', 'Vrbova', TO_DATE('1990/01/08 08:55:32', 'YYYY/MM/DD HH24:MI:SS'), 'female');
  32. INSERT INTO person(person_id, TYPE, first_name, last_name, date_of_birth, sex) VALUES (person_sequence.NEXTVAL, 'instructor', 'Martin', 'Hij', TO_DATE('1975/02/28 00:30:25', 'YYYY/MM/DD HH24:MI:SS'), 'male');
  33. INSERT INTO person(person_id, TYPE, first_name, last_name, date_of_birth, sex) VALUES (person_sequence.NEXTVAL, 'client', 'Stefan', 'Biely', TO_DATE('1987/04/04 11:11:32', 'YYYY/MM/DD HH24:MI:SS'), 'male');
  34. INSERT INTO person(person_id, TYPE, first_name, last_name, date_of_birth, sex) VALUES (person_sequence.NEXTVAL, 'client', 'Andrej', 'Maly', TO_DATE('1989/10/01 00:15:25', 'YYYY/MM/DD HH24:MI:SS'), 'male');
  35. INSERT INTO person(person_id, TYPE, first_name, last_name, date_of_birth, sex) VALUES (person_sequence.NEXTVAL, 'client', 'Marek', 'Velky', TO_DATE('1993/06/06 15:15:05', 'YYYY/MM/DD HH24:MI:SS'), 'male');
  36.  
  37.  
  38. CREATE TABLE receptionist(
  39.     receptionist_id INT NOT NULL,
  40.     CONSTRAINT recep_pk PRIMARY KEY (receptionist_id),
  41.     CONSTRAINT recep_fk FOREIGN KEY (receptionist_id) REFERENCES person(person_id)
  42. );
  43.  
  44. INSERT INTO receptionist(receptionist_id) VALUES (1);
  45. INSERT INTO receptionist(receptionist_id) VALUES (2);
  46.  
  47. CREATE TABLE instructor(
  48.     instructor_id INT NOT NULL,
  49.     CONSTRAINT instructor_pk PRIMARY KEY (instructor_id),
  50.     CONSTRAINT instruktor_fk FOREIGN KEY (instructor_id) REFERENCES person(person_id)
  51. );
  52.  
  53. INSERT INTO instructor(instructor_id) VALUES (3);
  54. INSERT INTO instructor(instructor_id) VALUES (4);
  55.  
  56. CREATE TABLE client(
  57.     client_id INT NOT NULL,
  58.     card_id INT,
  59.     CONSTRAINT clinet_pk PRIMARY KEY (client_id),
  60.     CONSTRAINT client_fk FOREIGN KEY (client_id) REFERENCES person(person_id)
  61.  
  62. );
  63.  
  64. INSERT INTO client(client_id, card_id) VALUES (5, 100);
  65. INSERT INTO client(client_id, card_id) VALUES (6, 101);
  66. INSERT INTO client(client_id, card_id) VALUES (7, NULL);
  67.  
  68. CREATE TABLE member_card(
  69.     card_id INT NOT NULL,
  70.     expiration_date TIMESTAMP NOT NULL,
  71.     receptionist_id INT NOT NULL,
  72.     CONSTRAINT cards_pk PRIMARY KEY (card_id),
  73.     CONSTRAINT member_card_fk FOREIGN KEY (receptionist_id) REFERENCES receptionist(receptionist_id),
  74.     CONSTRAINT card_check CHECK (card_id>=1 AND card_id<=999999)
  75. );
  76.  
  77. INSERT INTO member_card(card_id, expiration_date, receptionist_id) VALUES (100, TIMESTAMP '2020-05-20 9:00:25', 1);
  78. INSERT INTO member_card(card_id, expiration_date, receptionist_id) VALUES (101, TIMESTAMP '2020-08-25 13:30:55', 1);
  79.  
  80. ALTER TABLE client
  81. ADD FOREIGN KEY (card_id)
  82. REFERENCES member_card(card_id);
  83.  
  84. CREATE TABLE course(
  85.     course_id INT NOT NULL,
  86.     difficulty VARCHAR(64) NOT NULL CHECK (difficulty='easy' OR difficulty='medium' OR difficulty='hard'),
  87.     lenght_of_course_in_lessons INT NOT NULL,
  88.     name VARCHAR(64) NOT NULL,
  89.     TYPE VARCHAR(64) NOT NULL,
  90.     price INT NOT NULL,
  91.     instructor_id INT NOT NULL,
  92.     CONSTRAINT courses_pk PRIMARY KEY (course_id),
  93.     CONSTRAINT course_fk FOREIGN KEY (instructor_id) REFERENCES instructor(instructor_id),
  94.     CONSTRAINT course_check CHECK (course_id>=1 AND course_id<=999999)
  95. );
  96.  
  97. INSERT INTO course(course_id, difficulty, lenght_of_course_in_lessons, name, TYPE, price, instructor_id ) VALUES (1000, 'easy', 3, 'Yoga', 'relax', 150, 3);
  98. INSERT INTO course(course_id, difficulty, lenght_of_course_in_lessons, name, TYPE, price, instructor_id) VALUES (1001, 'medium', 2, 'Run-Time', 'endurance', 50, 3);
  99. INSERT INTO course(course_id, difficulty, lenght_of_course_in_lessons, name, TYPE, price, instructor_id) VALUES (1002, 'hard', 4, 'Strong-course', 'strong', 300, 3);
  100.  
  101. CREATE TABLE lesson(
  102.     lesson_id INT NOT NULL,
  103.     maximum_capacity INT NOT NULL,
  104.     price INT NOT NULL,
  105.     course_id INT,
  106.     instructor_id INT NOT NULL,
  107.     dateAndTime TIMESTAMP NOT NULL,
  108.     CONSTRAINT lessons_pk PRIMARY KEY (lesson_id),
  109.     CONSTRAINT lessons_course_fk FOREIGN KEY (course_id) REFERENCES course (course_id),
  110.     CONSTRAINT lessons_instructor_fk FOREIGN KEY (instructor_id) REFERENCES instructor(instructor_id),
  111.     CONSTRAINT lesson_check CHECK (lesson_id>=1 AND lesson_id<=999999)
  112. );
  113.  
  114. CREATE TABLE hall(
  115.     hall_name VARCHAR(64) NOT NULL,
  116.     maximum_capacity INT NOT NULL,
  117.     FLOOR INT NOT NULL,
  118.     number_of_hall INT NOT NULL,
  119.     address_name VARCHAR(64) NOT NULL,
  120.     address_number INT NOT NULL,
  121.     lesson_id INT,
  122.     CONSTRAINT halls_pk PRIMARY KEY (hall_name),
  123.     CONSTRAINT lessons_fk FOREIGN KEY (lesson_id) REFERENCES lesson(lesson_id)
  124. );
  125.  
  126. INSERT INTO lesson(lesson_id, maximum_capacity, price, course_id, instructor_id, dateAndTime) VALUES (100, 20, 10, 1000, 3, TIMESTAMP '2020-06-20 9:00:00');
  127. INSERT INTO lesson(lesson_id, maximum_capacity, price, course_id, instructor_id, dateAndTime) VALUES (101, 20, 30, 1000, 3, TIMESTAMP '2020-06-22 9:00:00');
  128. INSERT INTO lesson(lesson_id, maximum_capacity, price, course_id, instructor_id, dateAndTime) VALUES (102, 50, 100, NULL, 4, TIMESTAMP '2020-05-20 10:30:00');
  129.  
  130.  
  131. INSERT INTO hall(hall_name, maximum_capacity, FLOOR, number_of_hall, address_name, address_number, lesson_id) VALUES ('Hall 1', 100, 1, 1, 'Street', 10, NULL);
  132. INSERT INTO hall(hall_name, maximum_capacity, FLOOR, number_of_hall, address_name, address_number, lesson_id) VALUES ('Hall 2', 150, 2, 2, 'Street', 10, 100);
  133. INSERT INTO hall(hall_name, maximum_capacity, FLOOR, number_of_hall, address_name, address_number, lesson_id) VALUES ('Hall 4', 150, 3, 4, 'Street', 20, 102);
  134. INSERT INTO hall(hall_name, maximum_capacity, FLOOR, number_of_hall, address_name, address_number, lesson_id) VALUES ('Hall 10', 150, 5, 10, 'Street', 20, 101);
  135.  
  136.  
  137. CREATE TABLE register_to_lesson(
  138.      client_id INT NOT NULL,
  139.      lesson_id INT NOT NULL,
  140.      CONSTRAINT register1_pk PRIMARY KEY (client_id, lesson_id),
  141.      CONSTRAINT client_reg1_fk FOREIGN KEY (client_id) REFERENCES client(client_id),
  142.      CONSTRAINT lesson__reg_fk FOREIGN KEY (lesson_id) REFERENCES lesson(lesson_id)
  143. );
  144.  
  145. CREATE TABLE register_to_course(
  146.      client_id INT NOT NULL,
  147.      course_id INT NOT NULL,
  148.      CONSTRAINT register2_pk PRIMARY KEY (client_id, course_id),
  149.      CONSTRAINT client_reg2_fk FOREIGN KEY (client_id) REFERENCES client(client_id),
  150.      CONSTRAINT course_reg_fk FOREIGN KEY (course_id) REFERENCES course(course_id)
  151. );
  152.  
  153. INSERT INTO register_to_lesson(client_id, lesson_id) VALUES (5, 100);
  154. INSERT INTO register_to_lesson(client_id, lesson_id) VALUES (5, 101);
  155. INSERT INTO register_to_lesson(client_id, lesson_id) VALUES (6, 102);
  156. INSERT INTO register_to_lesson(client_id, lesson_id) VALUES (6, 100);
  157. INSERT INTO register_to_lesson(client_id, lesson_id) VALUES (6, 101);
  158.  
  159. INSERT INTO register_to_course(client_id, course_id) VALUES (6, 1000);
  160. INSERT INTO register_to_course(client_id, course_id) VALUES (5, 1000);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!