DROP TABLE ADMIN;
DROP TABLE CIRCUL;
DROP TABLE RESERVATION;
DROP TABLE USERS;
DROP TABLE LOCATION;
DROP TABLE COPY;
DROP TABLE MARCBOOKS;
DROP TABLE BOOKS;



CREATE TABLE BOOKS (
    LKEY varchar2(16),
    X001 varchar2(16), 
    X010 varchar2(16), 
    X01X varchar2(32), 
    ISBN varchar2(50) PRIMARY KEY,
    X020 varchar2(200), 
    X049 varchar2(4), 
    X050 varchar2(24), 
    X082 varchar2(24), 
    X092 varchar2(40), 
    AUTHOR varchar2(100), 
    X1XX varchar2(500),
    TITLE varchar2(500),
    X245 varchar2(500), 
    X250 varchar2(40), 
    X260 varchar2(500), 
    X300 varchar2(80), 
    X500 varchar2(500), 
    X504 varchar2(120), 
    X505 varchar2(1500),
    X520 varchar2(500),
    X590 varchar2(120), 
    X6XX varchar2(500), 
    X700 varchar2(500), 
    X710 varchar2(500), 
    X740 varchar2(500), 
    X8XX varchar2(500), 
    PRICE NUMBER(10,2),
    DATA_IN DATE,
    YEAR_PUB VARCHAR2(4),
    PAGE  VARCHAR2(4),
    COMMENTS VARCHAR2(500),
    STATUS VARCHAR2(1),
    FLAG  NUMBER,
    NO_COPY NUMBER
);

CREATE TABLE MARCBOOKS (
    X001 varchar2(16), 
    X010 varchar2(16), 
    X01X varchar2(32), 
    ISBN varchar2(50) REFERENCES BOOKS(ISBN),
    X020 varchar2(200), 
    X049 varchar2(4), 
    X050 varchar2(24), 
    X082 varchar2(24), 
    X092 varchar2(40), 
    X1XX varchar2(500),
    TITLE varchar2(500),
    X245 varchar2(500), 
    X250 varchar2(40), 
    X260 varchar2(500), 
    X300 varchar2(80), 
    X500 varchar2(500), 
    X504 varchar2(120), 
    X505 varchar2(1500),
    X520 varchar2(500),
    X590 varchar2(120), 
    X6XX varchar2(500), 
    X700 varchar2(500), 
    X710 varchar2(500), 
    X740 varchar2(500), 
    X8XX varchar2(500), 
    PRIMARY KEY(ISBN)
);

CREATE TABLE USERS
(
    USER_ID VARCHAR2(14) PRIMARY KEY,
    SSN VARCHAR2(9),
    DATE_OPEN  DATE,
    DATE_END   DATE,
    MR_MS  VARCHAR2(2),
    FIRSTNAME  VARCHAR2(16),
    MI  VARCHAR2(2),
    LASTNAME  VARCHAR2(16),
    TITLE  VARCHAR2(32),
    COMPANY  VARCHAR2(32),
    STREET1  VARCHAR2(32),
    STREET2  VARCHAR2(32),
    CITY     VARCHAR2(16),
    STATE    VARCHAR2(2),
    ZIP      VARCHAR2(90),
    COUNTRY  VARCHAR2(32),
    CATALOG  VARCHAR2(1),
    H_TEL    VARCHAR2(10),
    B_TEL    VARCHAR2(16),
    FAX      VARCHAR2(10),
    PAGER    VARCHAR2(10),
    LOAN_PD  NUMBER(10, 2),
    E_MAIL   VARCHAR2(80),
    NOTE     VARCHAR2(500)
);


CREATE TABLE RESERVATION
(
    ISBN VARCHAR2(50) REFERENCES BOOKS(ISBN),
    USER_ID VARCHAR2(14) REFERENCES USERS(USER_ID),
    LOG_DATE DATE,
    REMARKS VARCHAR2(200),
    PRIMARY KEY(ISBN, USER_ID)
);

CREATE TABLE COPY
(
    BOOK_ID VARCHAR2(14) PRIMARY KEY,
    ISBN    VARCHAR2(50) REFERENCES BOOKS(ISBN),
    COPY_ID NUMBER,
    ONLOAN  VARCHAR2(20)
);


CREATE TABLE LOCATION
(
    BOOK_ID  VARCHAR2(14) REFERENCES COPY(BOOK_ID),
    BUILDING VARCHAR2(10),
    ROOM     VARCHAR2(10),
    SHELF    VARCHAR2(10),
    PRIMARY KEY ( BOOK_ID)
);



CREATE TABLE CIRCUL
(
    USER_ID    VARCHAR2(14) REFERENCES USERS(USER_ID),
    BOOK_ID    VARCHAR2(14) REFERENCES COPY(BOOK_ID),
    DATE_OUT   DATE,
    DATE_DUE   DATE,
    DATE_BACK  DATE,
    DATE_RENEW DATE,
    RETURNED   VARCHAR2(1),
    FINE       NUMBER(10,2),
    PRIMARY KEY(USER_ID, BOOK_ID)
);

CREATE TABLE ADMIN
(
    FIRSTNAME  VARCHAR2(16),
    LASTNAME   VARCHAR2(16),
    PASSWORD   VARCHAR2(16),
    STATUS     VARCHAR2(16),
    PRIMARY KEY(FIRSTNAME,LASTNAME,PASSWORD)
);

insert into users values('123456','123456789',to_date('01/01/1997','mm/dd/yyyy'),'','mr','bob','k','smith','','','12 broadway','','chicago','il','60000','usa','y','5551234','','','',0,'','');
insert into users values('456789','123449999',to_date('11/01/1998','mm/dd/yyyy'),'','ms','jane','k','carter','','','134 main st.','','skokie','il','60076','usa','y','6791234','','','',0,'jane@nomail.com','');
insert into users values('999999','444556666',to_date('12/01/1999','mm/dd/yyyy'),'','mr','jack','a','tripper','','','999 Prospect st.','','evanston','il','60011','usa','n','6796676','','','',10,'jack@yaha.com','');
commit;


INSERT INTO BOOKS(LKEY,X001,X010,X01X,ISBN,X020,X049,X050,X082,X092,AUTHOR,X1XX,TITLE,X245,X250,X260,X300,X500,X504,X505,X520,X590,X6XX,X700,X710,X740,X8XX,PRICE,DATA_IN,YEAR_PUB,PAGE,COMMENTS,STATUS,FLAG,NO_COPY)VALUES(null,'ocm42791249 ','99056039 ',null,'0792377362','0792377362','OCLC','QA76.575 .C43 2000','006.7/6 21',null,'Chang, S. K. (Shi Kuo), 1944-',null,'Multimedia software engineering /by Shi-Kuo Chang.','Multimedia software engineering /by Shi-Kuo Chang.',null,'Boston : Kluwer Academic, 2000.','238 p. c25 cm.',null,'Includes bibliographical references and index.',null,null,null,'Software engineering. Multimedia systems.',null,null,null,null,null,null,null,null,null,null, 2, 1);
INSERT INTO BOOKS(LKEY,X001,X010,X01X,ISBN,X020,X049,X050,X082,X092,AUTHOR,X1XX,TITLE,X245,X250,X260,X300,X500,X504,X505,X520,X590,X6XX,X700,X710,X740,X8XX,PRICE,DATA_IN,YEAR_PUB,PAGE,COMMENTS,STATUS,FLAG,NO_COPY)VALUES(null,'ocm29184813 ','93033697 ',null,'0132261197','0132261197','OCLC','QA76.73.C15 D44 1994','005.13/3 20',null,'Deitel, Harvey M., 1945-',null,'C :how to program /H.M. Deitel, P.J. Deitel..','C :how to program /H.M. Deitel, P.J. Deitel..','2nd ed.','Englewood Cliffs, N.J.','xliv, 926 p. :ill. ;24 cm.','Includes index.',null,'Computing concepts -- Introduction to C Programming -- Structured program development -- Program control -- Functions -- Arrays -- Pointers -- Characters and strings -- Formatted input/output -- Structures, unions, bit manipulations, and enumerations -- File processing -- Data structures -- The preprocessor -- Advanced topics -- C++ as a "better C" -- Classes and data abstraction -- Classes: part 2 -- Operator overloading -- Inheritance -- Virtual functions and polymorphism -- C++ stream input/output -- C syntax.',null,null,'C (Cmputer program language)','Deitel, Paul J.',null,null,null,null,null,null,null,null,null,2,1);
INSERT INTO BOOKS(LKEY,X001,X010,X01X,ISBN,X020,X049,X050,X082,X092,AUTHOR,X1XX,TITLE,X245,X250,X260,X300,X500,X504,X505,X520,X590,X6XX,X700,X710,X740,X8XX,PRICE,DATA_IN,YEAR_PUB,PAGE,COMMENTS,STATUS,FLAG,NO_COPY)VALUES(null,'ocm40638347',null,null,'0444880755 0444880747','0444880755 0444880747','OCLC',null,null,'004 HAN',null,null,'Handbook of theoretical computer science. Volume B Formal models and semantics /edited by Jan van Leeuwen.','Handbook of theoretical computer science. Volume B Formal models and semantics /edited by Jan van Leeuwen.',null,'Amsterdam : Elsevier ; Cambridge, Mass. : MIT Press, c1990.','xiv, 1273 p. ;25 cm.',null,'Notes bibliogr. et index.',null,null,null,'Informatique Guides','Leeuwen, J. van',null,null,null,null,null,null,null,null,null,2,1);
INSERT INTO BOOKS(LKEY,X001,X010,X01X,ISBN,X020,X049,X050,X082,X092,AUTHOR,X1XX,TITLE,X245,X250,X260,X300,X500,X504,X505,X520,X590,X6XX,X700,X710,X740,X8XX,PRICE,DATA_IN,YEAR_PUB,PAGE,COMMENTS,STATUS,FLAG,NO_COPY)VALUES(null,'ocm40636067',null,null,'0444880755 0444880712','0444880755 0444880712','OCLC',null,null,'004 HAN',null,null,'Handbook of theoretical computer science. Volume A, Algorithms and complexity /edited by Jan van Leeuwen.','Handbook of theoretical computer science. Volume A, Algorithms and complexity /edited by Jan van Leeuwen.',null,'Amsterdam : Elsevier ; MIT Press, c1990.','ix, 996 p. ;25 cm.',null,'Notes bibliogr. et index.',null,null,null,'Informatique Guides','Leeuwen, J. van',null,null,null,null,null,null,null,null,null,2,1);
INSERT INTO BOOKS(LKEY,X001,X010,X01X,ISBN,X020,X049,X050,X082,X092,AUTHOR,X1XX,TITLE,X245,X250,X260,X300,X500,X504,X505,X520,X590,X6XX,X700,X710,X740,X8XX,PRICE,DATA_IN,YEAR_PUB,PAGE,COMMENTS,STATUS,FLAG,NO_COPY)VALUES(null,'ocm31516509','94039843 //r95',null,'0135945321','0135945321','OCLC','TK5102.9 .C557 1995','621.382/23 20',null,'Cohen, Leon.',null,'Time-frequency analysis / Leon Cohen.','Time-frequency analysis / Leon Cohen.',null,'Englewood Cliffs, N.J','xv, 299 p. : ill. ;25 cm.',null,'Includes bibliographical references (p. 263-289)',null,null,null,'Frequency spectra. Time-series analysis.',null,null,null,null,null,null,null,null,null,null,2,1);
commit;

INSERT INTO MARCBOOKS(X001,X010,X01X,ISBN,X020,X049,X050,X082,X092,X1XX,TITLE,X245,X250,X260,X300,X500,X504,X505,X520,X590,X6XX,X700,X710,X740,X8XX)VALUES('ocm42791249 ','99056039 ',null,'0792377362','0792377362','OCLC','QA76.575 .C43 2000','006.7/6 21',null,null,'Multimedia software engineering /by Shi-Kuo Chang.','Multimedia software engineering /by Shi-Kuo Chang.',null,'Boston : Kluwer Academic, 2000.','238 p. c25 cm.',null,'Includes bibliographical references and index.',null,null,null,'Software engineering. Multimedia systems.',null,null,null,null);
INSERT INTO MARCBOOKS(X001,X010,X01X,ISBN,X020,X049,X050,X082,X092,X1XX,TITLE,X245,X250,X260,X300,X500,X504,X505,X520,X590,X6XX,X700,X710,X740,X8XX)VALUES('ocm29184813 ','93033697 ',null,'0132261197','0132261197','OCLC','QA76.73.C15 D44 1994','005.13/3 20',null,null,'C :how to program /H.M. Deitel, P.J. Deitel..','C :how to program /H.M. Deitel, P.J. Deitel..','2nd ed.','Englewood Cliffs, N.J.','xliv, 926 p. :ill. ;24 cm.','Includes index.',null,'Computing concepts -- Introduction to C Programming -- Structured program development -- Program control -- Functions -- Arrays -- Pointers -- Characters and strings -- Formatted input/output -- Structures, unions, bit manipulations, and enumerations -- File processing -- Data structures -- The preprocessor -- Advanced topics -- C++ as a "better C" -- Classes and data abstraction -- Classes: part 2 -- Operator overloading -- Inheritance -- Virtual functions and polymorphism -- C++ stream input/output -- C syntax.',null,null,'C (Cmputer program language)','Deitel, Paul J.',null,null,null);
INSERT INTO MARCBOOKS(X001,X010,X01X,ISBN,X020,X049,X050,X082,X092,X1XX,TITLE,X245,X250,X260,X300,X500,X504,X505,X520,X590,X6XX,X700,X710,X740,X8XX)VALUES('ocm40638347',null,null,'0444880755 0444880747','0444880755 0444880747','OCLC',null,null,'004 HAN',null,'Handbook of theoretical computer science. Volume B Formal models and semantics /edited by Jan van Leeuwen.','Handbook of theoretical computer science. Volume B Formal models and semantics /edited by Jan van Leeuwen.',null,'Amsterdam : Elsevier ; Cambridge, Mass. : MIT Press, c1990.','xiv, 1273 p. ;25 cm.',null,'Notes bibliogr. et index.',null,null,null,'Informatique Guides','Leeuwen, J. van',null,null,null);
INSERT INTO MARCBOOKS(X001,X010,X01X,ISBN,X020,X049,X050,X082,X092,X1XX,TITLE,X245,X250,X260,X300,X500,X504,X505,X520,X590,X6XX,X700,X710,X740,X8XX)VALUES('ocm40636067',null,null,'0444880755 0444880712','0444880755 0444880712','OCLC',null,null,'004 HAN',null,'Handbook of theoretical computer science. Volume A, Algorithms and complexity /edited by Jan van Leeuwen.','Handbook of theoretical computer science. Volume A, Algorithms and complexity /edited by Jan van Leeuwen.',null,'Amsterdam : Elsevier ; MIT Press, c1990.','ix, 996 p. ;25 cm.',null,'Notes bibliogr. et index.',null,null,null,'Informatique Guides','Leeuwen, J. van',null,null,null);
INSERT INTO MARCBOOKS(X001,X010,X01X,ISBN,X020,X049,X050,X082,X092,X1XX,TITLE,X245,X250,X260,X300,X500,X504,X505,X520,X590,X6XX,X700,X710,X740,X8XX)VALUES('ocm31516509','94039843 //r95',null,'0135945321','0135945321','OCLC','TK5102.9 .C557 1995','621.382/23 20',null,null,'Time-frequency analysis / Leon Cohen.','Time-frequency analysis / Leon Cohen.',null,'Englewood Cliffs, N.J','xv, 299 p. : ill. ;25 cm.',null,'Includes bibliographical references (p. 263-289)',null,null,null,'Frequency spectra. Time-series analysis.',null,null,null,null);
commit;


INSERT INTO ADMIN VALUES('ellen','bryant','123456',null);
COMMIT;