clear screen

prompt _____________________________
Prompt
Prompt Leegmaken van alle tabellen
Prompt
Prompt _____________________________

REM dropt alle tabellen

DROP TABLE performance CASCADE CONSTRAINTS;
DROP TABLE Performance_Actor CASCADE CONSTRAINTS;
DROP TABLE actor CASCADE CONSTRAINTS;
DROP TABLE performance_date CASCADE CONSTRAINTS;
DROP TABLE reservation CASCADE CONSTRAINTS;
DROP TABLE subscriber CASCADE CONSTRAINTS;
DROP TABLE reservation_spectator CASCADE CONSTRAINTS;
DROP TABLE rehearsal_actor CASCADE CONSTRAINTS;
DROP TABLE zip_code CASCADE CONSTRAINTS;
DROP TABLE location CASCADE CONSTRAINTS;
DROP TABLE reservation_performance CASCADE CONSTRAINTS;
DROP TABLE spectator CASCADE CONSTRAINTS;
DROP TABLE rehearsal CASCADE CONSTRAINTS;
Prompt _______________________________
Prompt
Prompt alle tabellen zijn nu leeg.
Prompt
Prompt _____________________________
Prompt
Prompt
Prompt
Prompt _____________________________

Prompt Aanmaken van tabel Performance
CREATE TABLE performance(
    theatre_performance             NUMBER(6, 0) ,
    season                          VARCHAR2(9) CONSTRAINT perf_season_NN NOT NULL,
    producer                        VARCHAR2(30) CONSTRAINT perf_producer_NN NOT NULL,
    description                     VARCHAR2(255),
    price_ticket                    NUMBER(5, 2),
        CONSTRAINT perf_theatre_perf_PK PRIMARY KEY(theatre_performance, season)
);

Prompt
Prompt
Prompt tabel Performance AANGEMAAKT
Prompt ________________________________





Prompt Aanmaken van tabel performance_actor
CREATE TABLE Performance_Actor(
    theatre_performance             NUMBER(6,0),
    season                          VARCHAR(9),
    actor_id                        NUMBER(2,0),
        CONSTRAINT actor_perf_PK PRIMARY KEY(theatre_performance, season, actor_id),
        CONSTRAINT performance_actors_fk foreign KEY(theatre_performance, season) references performance (theatre_performance, season)
);

prompt aanmaken van tabel_actor
prompt ___________________________________
prompt

CREATE TABLE actor(
    actor_id                        VARCHAR2(20)            CONSTRAINT actor_id_PK      PRIMARY KEY,
    actor_name                      VARCHAR2(20)            CONSTRAINT actor_name_nn    NOT NULL,
    actor_lastname                  VARCHAR2(20)            CONSTRAINT   actor_lastname_nn  NOT NULL,
    known_from                      LONG
);

prompt aanmaken van de tabel performance_date
prompt__________________________________________
prompt
prompt

CREATE TABLE performance_date(
    theatre_performance             NUMBER(6, 0),
    date_time                       DATE,
    season                          VARCHAR2(9),
        CONSTRAINT performance_date_fk                      foreign KEY(theatre_performance, season) references performance (theatre_performance, season),
        CONSTRAINT performance_dates_PK                     PRIMARY KEY(theatre_performance, date_time)
);

prompt zip_code
prompt ____________________
prompt
prompt

CREATE TABLE zip_code(
    zip_code                        NUMBER(4),
    city                            VARCHAR2(20)            CONSTRAINT city_NN NOT NULL,
        CONSTRAINT zip_code_PK                              PRIMARY KEY(zip_code)
);

prompt Aanmaken tabel subscriber
prompt ___________________________________
prompt
prompt
CREATE TABLE subscriber(
    subscriber_id                   NUMBER(5,0),
    subscriber_name                 VARCHAR(10),
    subscriber_firstname            VARCHAR(10),
    address                         VARCHAR(51),
    zip_code                        NUMBER(4,0),
    telephone                       VARCHAR(10),
    email                           VARCHAR(51),
        CONSTRAINT sub_email_NOTVALID                       CHECK( email LIKE '%@%.%'),
        CONSTRAINT sub_subscriber_id_PK                     PRIMARY KEY(subscriber_id),
        CONSTRAINT zipc_zipcode                             FOREIGN KEY(zip_code)
        REFERENCES zip_code(zip_code)
);

prompt aanmaken van de tabel Reservation
prompt__________________________________________
prompt
prompt



CREATE TABLE Reservation(
    reservation_id                  NUMBER(5, 0),
    subscriber_id                   NUMBER(5,0),
    comments LONG ,
        CONSTRAINT resv_resv_id_PK                          PRIMARY KEY (reservation_id),
        CONSTRAINT resv_subsc_id_FK                         foreign KEY (subscriber_id) REFERENCES subscriber (subscriber_id)
);

prompt aanmaken van spectators
prompt ___________________________________
prompt



CREATE TABLE spectator(
spectator_id                        NUMBER(5)               CONSTRAINT spec_id_PK           PRIMARY KEY,
spectator_name                      VARCHAR2(20)            CONSTRAINT spec_name_nn         NOT NULL,
spectator_firstname                 VARCHAR2(20)            CONSTRAINT spec_firstname_nn    NOT NULL
);


prompt aanmaken reservation_spectator
prompt __________________________________
prompt
prompt

CREATE TABLE reservation_spectator(
    reservation_id                  NUMBER(5),
    spectator_id                    NUMBER(5),
        CONSTRAINT reservation_spectators_PK                PRIMARY KEY (reservation_id, spectator_id),
        CONSTRAINT reservation_id_FK                        foreign KEY (reservation_id) references reservation(reservation_id),
        CONSTRAINT spectator_id_FK                          foreign KEY (spectator_id) references spectator(spectator_id)
);







prompt aanmaken reservation_performance
prompt
prompt _____________________________________
prompt

CREATE TABLE reservation_performance (
    reservation_id                  NUMBER(5, 0),
    theatre_performance             NUMBER(5,0),
        CONSTRAINT reservation_performance_fk               foreign KEY (reservation_id)                    references reservation (reservation_id),
        date_time                   DATE                    CONSTRAINT date_time_res_perf_nn                NOT NULL,
        CONSTRAINT res_th_perf_dt_FK                        foreign KEY(theatre_performance, date_time)     references performance_date (theatre_performance, date_time),
        CONSTRAINT reservation_performance_PK               PRIMARY KEY (reservation_id, theatre_performance)
);
prompt
prompt_________________________________________
prompt
prompt aanmaken location
prompt_________________________________________








prompt
CREATE TABLE location(
    location_id                     NUMBER(5)               CONSTRAINT location_id_pk           PRIMARY KEY,
    adress                          VARCHAR2(50)            CONSTRAINT adress_location_nn       NOT NULL,
    zip_code                        NUMBER(4,0),
        CONSTRAINT zip_code_locations_fk                    foreign KEY(zip_code)               references zip_code (zip_code)
);
prompt
prompt_________________________________________
prompt


prompt Aanmaken rehearsal
prompt
prompt_________________________________________
prompt

CREATE TABLE rehearsal(
    theatre_performance             NUMBER(5,0),
    date_starttime                  DATE,
    date_endtime                    DATE,
    location_id                     NUMBER(2,0),
        CONSTRAINTS reh_theatre_performance_pk              PRIMARY KEY (theatre_performance, date_starttime)
);




prompt rehearsal_actor
prompt ____________________
prompt
prompt
CREATE TABLE rehearsal_actor(
    theatre_performance         NUMBER(5,0),
    date_starttime              DATE,
    actor_id                    NUMBER(2,0),
        CONSTRAINTS rehearsal_actor_pk                      PRIMARY KEY (date_starttime, actor_id)
);
prompt_________________________________________
prompt Inserten van gegevens
INSERT INTO performance VALUES(
    1,
    '2015-2016',
    'Steven Hawking',
    'Een tonneel over Steven Hawking by Steven Hawking',
    23.50
);

INSERT INTO performance VALUES(
    2,
    '2016-2017',
    'Barak Obama',
    'How to build a great wall by Donald Trump.',
    21.03
);

INSERT INTO performance_date VALUES(
    1,
    TO_DATE('18-03-2015', 'dd-mm-yyyy'),
    '2015-2016'
);

INSERT INTO performance_date VALUES(
    2,
    TO_DATE('16-07-2016', 'dd-mm-yyyy'),
    '2016-2017'
);

INSERT INTO actor VALUES(
    1,
    'Ted',
    'Mosby',
    'How I met your mother'
);

INSERT INTO actor VALUES(
    2,
    'Rowan',
    'Atkinson',
    'Black Adder'
);

INSERT INTO actor VALUES(
    3,
    'Kerstin',
    'Nys',
    'Fun with Data'
);

INSERT INTO spectator VALUES(
    1,
    'Es',
    'Jurgen'
);

INSERT INTO spectator VALUES(
    2,
    'Van der Stappen',
    'Niels'
);

INSERT INTO spectator VALUES(
    3,
    'Adriaensen',
    'Thijs'
);

INSERT INTO spectator VALUES(
    4,
    'Kallen',
    'Robin'
);

INSERT INTO spectator VALUES(
    5,
    'Diesel',
    'Vin'
);

INSERT INTO spectator VALUES(
    6,
    'The Rock',
    'Johnson'
);

INSERT INTO spectator VALUES(
    7,
    'Walker',
    'Paul'
);

INSERT INTO spectator VALUES(
    8,
    'Khalifa',
    'Mia'
);
INSERT INTO zip_code VALUES(
    3500,
    'Hasselt'
);

INSERT INTO subscriber VALUES(
    1,
    'De Bouwer',
    'Bob',
    'bouwlaan 1',
    '3500',
    '0471234598',
    'bobdebouwer@bouwfirma.be'
);


INSERT INTO zip_code VALUES(
    6000,
    'Ghent'
);
INSERT INTO subscriber VALUES(
    2,
    'Neassens',
    'Willy',
    'zwembadenlaan 14',
    '6000',
    '0481234568',
    'willy@naessens.be'
);

INSERT INTO reservation VALUES(
    1,
    1,
    'Slecht ziend, liefst een plaats van voor.'
);

INSERT INTO reservation VALUES(
    2,
    2,
    NULL
);

INSERT INTO reservation VALUES(
    3,
    2,
    NULL
);

INSERT INTO reservation_spectator VALUES(
    1,
    2
);

INSERT INTO reservation_spectator VALUES(
    1,
    4
);

INSERT INTO reservation_spectator VALUES(
    1,
    6
);

INSERT INTO reservation_spectator VALUES(
    1,
    8
);

INSERT INTO reservation_spectator VALUES(
    2,
    1
);

INSERT INTO reservation_spectator VALUES(
    2,
    3
);

INSERT INTO reservation_spectator VALUES(
    2,
    5
);

INSERT INTO reservation_spectator VALUES(
    2,
    7
);

INSERT INTO reservation_performance VALUES(
    1,
    1,
    TO_DATE('18-03-2015', 'dd-mm-yyyy')
);

INSERT INTO reservation_performance VALUES(
    1,
    2,
    TO_DATE('16-07-2016', 'dd-mm-yyyy')
);

INSERT INTO reservation_performance VALUES(
    2,
    1,
    TO_DATE('18-03-2015', 'dd-mm-yyyy')
);

INSERT INTO reservation_performance VALUES(
    3,
    1,
    TO_DATE('18-03-2015', 'dd-mm-yyyy')
);

INSERT INTO location VALUES(
    1,
    'grote Ring 24b',
    3500
);

INSERT INTO location VALUES(
    2,
    'Sint-widostraat 4',
    6000
);

INSERT INTO rehearsal VALUES(
    1,
    TO_DATE('01-03-2015', 'dd-mm-yyyy'),
    TO_DATE('02-03-2015', 'dd-mm-yyyy'),
    1
);

INSERT INTO rehearsal VALUES(
    2,
    TO_DATE('03-03-2015', 'dd-mm-yyyy'),
    TO_DATE('04-03-2015', 'dd-mm-yyyy'),
    1
);

INSERT INTO rehearsal VALUES(
    1,
    TO_DATE('07-03-2015', 'dd-mm-yyyy'),
    TO_DATE('08-03-2015', 'dd-mm-yyyy'),
    1
);

INSERT INTO rehearsal VALUES(
    2,
    TO_DATE('01-09-2015', 'dd-mm-yyyy'),
    TO_DATE('02-10-2015', 'dd-mm-yyyy'),
    1
);

INSERT INTO rehearsal VALUES(
    2,
    TO_DATE('10-09-2015', 'dd-mm-yyyy'),
    TO_DATE('12-10-2015', 'dd-mm-yyyy'),
    1
);

INSERT INTO rehearsal_actor VALUES(
    1,
    TO_DATE('01-09-2015', 'dd-mm-yyyy'),
    2
);

INSERT INTO rehearsal_actor VALUES(
    1,
    TO_DATE('01-09-2015', 'dd-mm-yyyy'),
    3
);

INSERT INTO performance_actor VALUES(
    1,
    '2015-2016',
    1
);

INSERT INTO performance_actor VALUES(
    1,
    '2015-2016',
    2
);

INSERT INTO performance_actor VALUES(
    1,
    '2015-2016',
    3
);