dancho_krasi_kude

CREATE TABLE library_admin.author (
    author_id     INTEGER NOT NULL,
    author_name   VARCHAR2(100)
);

ALTER TABLE library_admin.author ADD CONSTRAINT author_pk PRIMARY KEY ( author_id );

CREATE TABLE library_admin.book (
    book_id                  INTEGER NOT NULL,
    book_name                VARCHAR2(100),
    book_year                INTEGER,
    author_author_id         INTEGER NOT NULL,
    genre_genre_id           INTEGER NOT NULL,
    publisher_publisher_id   INTEGER NOT NULL
);

ALTER TABLE library_admin.book ADD CONSTRAINT book_pk PRIMARY KEY ( book_id );

CREATE TABLE library_admin.employee (
    employee_id              INTEGER NOT NULL,
    employee_name            VARCHAR2(100),
    employee_pnum            VARCHAR2(20),
    possition_possition_id   INTEGER NOT NULL
);

ALTER TABLE library_admin.employee ADD CONSTRAINT employee_pk PRIMARY KEY ( employee_id );

CREATE TABLE library_admin.genre (
    genre_id     INTEGER NOT NULL,
    genre_name   VARCHAR2(100)
);

ALTER TABLE library_admin.genre ADD CONSTRAINT genre_pk PRIMARY KEY ( genre_id );

CREATE TABLE library_admin.loan (
    loan_id                INTEGER NOT NULL,
    loan_date              DATE,
    loan_deadend           DATE,
    book_book_id           INTEGER NOT NULL,
    employee_employee_id   INTEGER NOT NULL,
    reader_reader_id       INTEGER NOT NULL
);

ALTER TABLE library_admin.loan ADD CONSTRAINT loan_pk PRIMARY KEY ( loan_id );

CREATE TABLE library_admin.possition (
    possition_id     INTEGER NOT NULL,
    possition_name   VARCHAR2(100)
);

ALTER TABLE library_admin.possition ADD CONSTRAINT possition_pk PRIMARY KEY ( possition_id );

CREATE TABLE library_admin.publisher (
    publisher_id     INTEGER NOT NULL,
    publisher_name   VARCHAR2(100)
);

ALTER TABLE library_admin.publisher ADD CONSTRAINT publisher_pk PRIMARY KEY ( publisher_id );

CREATE TABLE library_admin.reader (
    reader_id     INTEGER NOT NULL,
    reader_name   VARCHAR2(100),
    reader_pnum   VARCHAR2(20)
);

ALTER TABLE library_admin.reader ADD CONSTRAINT reader_pk PRIMARY KEY ( reader_id );

ALTER TABLE library_admin.book
    ADD CONSTRAINT book_author_fk FOREIGN KEY ( author_author_id )
        REFERENCES library_admin.author ( author_id );

ALTER TABLE library_admin.book
    ADD CONSTRAINT book_genre_fk FOREIGN KEY ( genre_genre_id )
        REFERENCES library_admin.genre ( genre_id );

ALTER TABLE library_admin.book
    ADD CONSTRAINT book_publisher_fk FOREIGN KEY ( publisher_publisher_id )
        REFERENCES library_admin.publisher ( publisher_id );

ALTER TABLE library_admin.employee
    ADD CONSTRAINT employee_possition_fk FOREIGN KEY ( possition_possition_id )
        REFERENCES library_admin.possition ( possition_id );

ALTER TABLE library_admin.loan
    ADD CONSTRAINT loan_book_fk FOREIGN KEY ( book_book_id )
        REFERENCES library_admin.book ( book_id );

ALTER TABLE library_admin.loan
    ADD CONSTRAINT loan_employee_fk FOREIGN KEY ( employee_employee_id )
        REFERENCES library_admin.employee ( employee_id );

ALTER TABLE library_admin.loan
    ADD CONSTRAINT loan_reader_fk FOREIGN KEY ( reader_reader_id )
        REFERENCES library_admin.reader ( reader_id );



        INSERT INTO library_admin.author (AUTHOR_ID,AUTHOR_NAME)
VALUES (1,'Ivan Vazov');
INSERT INTO library_admin.author VALUES(2,'Aleko Konstantinov');
INSERT INTO library_admin.author VALUES(3,'Dimcho debelqnov');
INSERT INTO library_admin.author VALUES(4,'Hristo botev');
INSERT INTO library_admin.author VALUES(5,'J.K. Rowling');
INSERT INTO library_admin.author VALUES(6,'Elin Pelin');
INSERT INTO library_admin.author VALUES(7,'Anton Donchev');

INSERT INTO library_admin.genre(GENRE_ID,GENRE_NAME)
VALUES (1,'Roman');
INSERT INTO library_admin.genre VALUES(2,'Povest');
INSERT INTO library_admin.genre VALUES(3,'Razkaz');
INSERT INTO library_admin.genre VALUES(4,'Balada');
INSERT INTO library_admin.genre VALUES(5,'Oda');
INSERT INTO library_admin.genre VALUES(6,'Poema');
INSERT INTO library_admin.genre VALUES(7,'Stihotvorenie');

INSERT INTO library_admin.reader(READER_ID,READER_NAME,READER_PNUM)
VALUES (1,'Yordan','0887798723');
INSERT INTO library_admin.reader VALUES(2,'Ivan','0887398122');
INSERT INTO library_admin.reader VALUES(3,'Sasha','0863318122');
INSERT INTO library_admin.reader VALUES(4,'Pasha','0863318322');
INSERT INTO library_admin.reader VALUES(5,'Kaloqn','0863328122');
INSERT INTO library_admin.reader VALUES(6,'Kristina','0863118122');
INSERT INTO library_admin.reader VALUES(7,'Hristiqn','0865518122');

INSERT INTO library_admin.publisher(PUBLISHER_ID,publisher_name)
VALUES (1,'Prosveta');
INSERT INTO library_admin.publisher VALUES(2,'Faber');
INSERT INTO library_admin.publisher VALUES(3,'Anubis');
INSERT INTO library_admin.publisher VALUES(4,'Matematik');
INSERT INTO library_admin.publisher VALUES(5,'Astronom');
INSERT INTO library_admin.publisher VALUES(6,'Izdatel');
INSERT INTO library_admin.publisher VALUES(7,'Random');

INSERT INTO library_admin.possition(possition_id,possition_name)
VALUES (1,'Prodavach');
INSERT INTO library_admin.possition VALUES(2,'Chistach');
INSERT INTO library_admin.possition VALUES(3,'Pomoshnik');
INSERT INTO library_admin.possition VALUES(4,'Manager');
INSERT INTO library_admin.possition VALUES(5,'Shef');
INSERT INTO library_admin.possition VALUES(6,'Sobstvenik');
INSERT INTO library_admin.possition VALUES(7,'Konsultant');




INSERT INTO library_admin.employee(employee_id,employee_name,employee_pnum, possition_possition_id)
VALUES(1,'Bogdan','0887666231',4);
INSERT INTO library_admin.employee VALUES(2,'Vladislav','0872333121',3);
INSERT INTO library_admin.employee VALUES(3,'Konstadin','0872456121',4);
INSERT INTO library_admin.employee VALUES(4,'Vladimir','0872132121',1);
INSERT INTO library_admin.employee VALUES(5,'Alex','0882331121',1);
INSERT INTO library_admin.employee VALUES(6,'Kristian','0872333144',7);


INSERT INTO library_admin.book(book_id,book_name,book_year,author_author_id , publisher_publisher_id,genre_genre_id)
VALUES(1,'Edna bulgarka','2005',1,3,3);
INSERT INTO library_admin.book VALUES(2,'Pod igoto',2007,1,3,3);
INSERT INTO library_admin.book VALUES(3,'Proba 3',2007,1,2,2);
INSERT INTO library_admin.book VALUES(4,'Radndom 2',2007,1,5,2);
INSERT INTO library_admin.book VALUES(5,'Proizvedenie 6',2007,1,6,1);
INSERT INTO library_admin.book VALUES(6,'Proizvedenie 7',2007,1,7,3);

INSERT INTO library_admin.loan(loan_id,book_book_id,reader_reader_id,employee_employee_id,loan_date,loan_deadend)
VALUES(1,1,1,1,'08-JUL-15','15-JUL-15');
INSERT INTO library_admin.loan(loan_id,book_book_id,reader_reader_id,employee_employee_id,loan_date,loan_deadend)
VALUES(2,2,1,2,'16-JUL-16','16-JUL-26');
INSERT INTO library_admin.loan(loan_id,book_book_id,reader_reader_id,employee_employee_id,loan_date,loan_deadend)
VALUES(3,3,3,2,'15-MAR-21','15-MAR-26');
INSERT INTO library_admin.loan(loan_id,book_book_id,reader_reader_id,employee_employee_id,loan_date,loan_deadend)
VALUES(4,2,1,3,'16-JUL-19','16-JUL-24');
INSERT INTO library_admin.loan(loan_id,book_book_id,reader_reader_id,employee_employee_id,loan_date,loan_deadend)
VALUES(5,1,4,2,'16-MAR-11','16-MAR-20');
INSERT INTO library_admin.loan(loan_id,book_book_id,reader_reader_id,employee_employee_id,loan_date,loan_deadend)
VALUES(6,3,1,3,'16-MAR-06','16-MAR-12');
INSERT INTO library_admin.loan(loan_id,book_book_id,reader_reader_id,employee_employee_id,loan_date,loan_deadend)
VALUES(7,5,1,2,'16-JUL-03','16-JUL-13');

CREATE SEQUENCE library_admin.author_auto
START WITH 8
INCREMENT BY 1
CACHE 10;

CREATE OR REPLACE TRIGGER library_admin.t_authid
BEFORE INSERT
ON library_admin.author
FOR EACH ROW
WHEN (NEW.AUTHOR_ID IS NULL)
BEGIN
 :NEW.AUTHOR_ID:= library_admin.author_auto.NEXTVAL;
 END;

INSERT INTO library_admin.author (AUTHOR_NAME)
VALUES ('Yancho Shterev');

CREATE SEQUENCE library_admin.reader_auto
START WITH 8
INCREMENT BY 1
CACHE 10;

CREATE OR REPLACE TRIGGER library_admin.t_readid
BEFORE INSERT
ON library_admin.reader
FOR EACH ROW
WHEN (NEW.READER_ID IS NULL)
BEGIN
 :NEW.READER_ID:= library_admin.reader_auto.NEXTVAL;
 END;

INSERT INTO library_admin.reader (READER_NAME,READER_PNUM)
VALUES ('Ivancho Ivanov',0381238128);

CREATE SEQUENCE library_admin.publisher_auto
START WITH 8
INCREMENT BY 1
CACHE 10;

CREATE OR REPLACE TRIGGER library_admin.t_pubid
BEFORE INSERT
ON library_admin.publisher
FOR EACH ROW
WHEN (NEW.PUBLISHER_ID IS NULL)
BEGIN
 :NEW.PUBLISHER_ID:= library_admin.publisher_auto.NEXTVAL;
 END;

INSERT INTO library_admin.publisher (PUBLISHER_NAME)
VALUES ('Sashun Sashev');

CREATE SEQUENCE library_admin.possition_auto
START WITH 8
INCREMENT BY 1
CACHE 10;

CREATE OR REPLACE TRIGGER library_admin.t_possid
BEFORE INSERT
ON library_admin.possition
FOR EACH ROW
WHEN (NEW.POSSITION_ID IS NULL)
BEGIN
 :NEW.POSSITION_ID:= library_admin.possition_auto.NEXTVAL;
 END;

INSERT INTO library_admin.possition (POSSITION_NAME)
VALUES ('Novak');

CREATE SEQUENCE library_admin.genre_auto
START WITH 8
INCREMENT BY 1
CACHE 10;

CREATE OR REPLACE TRIGGER library_admin.t_genid
BEFORE INSERT
ON library_admin.genre
FOR EACH ROW
WHEN (NEW.GENRE_ID IS NULL)
BEGIN
 :NEW.GENRE_ID:= library_admin.genre_auto.NEXTVAL;
 END;

INSERT INTO library_admin.genre (GENRE_NAME)
VALUES ('Novela');

CREATE SEQUENCE library_admin.employee_auto
START WITH 7
INCREMENT BY 1
CACHE 10;

CREATE OR REPLACE TRIGGER library_admin.t_empid
BEFORE INSERT
ON library_admin.employee
FOR EACH ROW
WHEN (NEW.EMPLOYEE_ID IS NULL)
BEGIN
 :NEW.EMPLOYEE_ID:= library_admin.employee_auto.NEXTVAL;
 END;

INSERT INTO library_admin.employee (EMPLOYEE_NAME,EMPLOYEE_PNUM,possition_possition_id)
VALUES ('Kikoo','03312312313',5);

CREATE SEQUENCE library_admin.book_auto
START WITH 7
INCREMENT BY 1
CACHE 10;

CREATE OR REPLACE TRIGGER library_admin.t_bkid
BEFORE INSERT
ON library_admin.book
FOR EACH ROW
WHEN (NEW.BOOK_ID IS NULL)
BEGIN
 :NEW.BOOK_ID:= library_admin.book_auto.NEXTVAL;
 END;

INSERT INTO library_admin.book (BOOK_NAME,book_year,author_author_id,genre_genre_id,publisher_publisher_id)
VALUES ('The Maze Runner',2012,8,1,7);

CREATE SEQUENCE library_admin.loan_auto
START WITH 8
INCREMENT BY 1
CACHE 10;

CREATE OR REPLACE TRIGGER library_admin.l_bkid
BEFORE INSERT
ON library_admin.loan
FOR EACH ROW
WHEN (NEW.LOAN_ID IS NULL)
BEGIN
 :NEW.LOAN_ID:= library_admin.loan_auto.NEXTVAL;
 END;

INSERT INTO library_admin.loan (LOAN_DATE,LOAN_DEADEND,BOOK_BOOK_ID,EMPLOYEE_EMPLOYEE_ID,READER_READER_ID)
VALUES ('08-JUL-15','15-JUL-15',5,3,1);