--Set deferred segment creation setting to false for the sequences to work correctly ALTER SYSTEM SET deferred_segment_creation=false; --Create a user in order to get a schema for the tables CREATE USER bookdb IDENTIFIED BY abc123; --Grant permissions to the schema so that the tables can be created GRANT RESOURCE TO bookdb; GRANT UNLIMITED TABLESPACE TO bookdb; --Change the default schema to bookdb ALTER SESSION SET CURRENT_SCHEMA=bookdb; CREATE SEQUENCE author_id_seq START WITH 1 INCREMENT BY 1; CREATE TABLE author ( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR2(20) NOT NULL UNIQUE, info XMLTYPE NOT NULL ); INSERT INTO author VALUES (author_id_seq.nextval,'John Craft','jc@jc.comEngland1948'); INSERT INTO author VALUES (author_id_seq.nextval,'Arnie Bastoft','bastoft@frei.atAustria1971'); INSERT INTO author VALUES (author_id_seq.nextval,'Meg Gilmand','megil@archeo.orgAustralia1968'); INSERT INTO author VALUES (author_id_seq.nextval,'Chris Ryan','chris@egypt.egFrance1944'); INSERT INTO author VALUES (author_id_seq.nextval,'Alan Griff','ag@mit.eduUSA1972'); INSERT INTO author VALUES (author_id_seq.nextval,'Marty Faust','marty@nyu.eduUSA1970'); INSERT INTO author VALUES (author_id_seq.nextval,'Celine Biceau','celine.biceau@tok.cnCanada1969'); INSERT INTO author VALUES (author_id_seq.nextval,'Carl Sagan','carlsagan@nasa.govUSA1913'); INSERT INTO author VALUES (author_id_seq.nextval,'Leslie Brenner','leslie@yahoo.comUSA1945'); INSERT INTO author VALUES (author_id_seq.nextval,'Jakob Hanson','jh22@hotmail.comSweden1946'); INSERT INTO author VALUES (author_id_seq.nextval,'Mimi Pappas','mimi@music.comUSA1972'); INSERT INTO author VALUES (author_id_seq.nextval,'Marie Franksson','franksson@bok.seSweden1937'); INSERT INTO author VALUES (author_id_seq.nextval,'Pierre Zargone','zargone@fans.beBelgium1968'); INSERT INTO author VALUES (author_id_seq.nextval,'Carl George','cg@eu.intFrance1939'); INSERT INTO author VALUES (author_id_seq.nextval,'Peter Feldon','pf@eu.intEngland1917'); INSERT INTO author VALUES (author_id_seq.nextval,'Lilian Carrera','lc@eu.intSpain1922'); INSERT INTO author VALUES (author_id_seq.nextval,'Auna Gonzales Perre','agp@eu.intPortugal1942'); INSERT INTO author VALUES (author_id_seq.nextval,'Kostas Andrianos','ka@eu.intGreece1936'); INSERT INTO author VALUES (author_id_seq.nextval,'Andreas Shultz','as@eu.intAustria1919'); INSERT INTO author VALUES (author_id_seq.nextval,'Antje Liedderman','al@eu.intGermany1950'); INSERT INTO author VALUES (author_id_seq.nextval,'Christina Ohlsen','co@eu.intNorway1933'); INSERT INTO author VALUES (author_id_seq.nextval,'Sam Davis','sd@music.comMexico1939'); INSERT INTO author VALUES (author_id_seq.nextval,'Alicia Bing','bing@bing.beBelgium1952'); INSERT INTO author VALUES (author_id_seq.nextval,'Linda Evans','levans@ocean.uot.eduUSA1966'); INSERT INTO author VALUES (author_id_seq.nextval,'Chuck Morrisson','morrisson@home.toEngland1963'); INSERT INTO author VALUES (author_id_seq.nextval,'Kay Morrisson','morrisson@home.toEngland1969'); INSERT INTO author VALUES (author_id_seq.nextval,'James Patterson','jp@hotmail.comUSA1955'); INSERT INTO author VALUES (author_id_seq.nextval,'Peter de Jonge','pj@mail.comUSA1961'); INSERT INTO author VALUES (author_id_seq.nextval,'Franc Desteille','franc@chateau.frFrance1925'); CREATE SEQUENCE book_id_seq START WITH 1 INCREMENT BY 1; CREATE TABLE book ( id INTEGER NOT NULL PRIMARY KEY, title VARCHAR2(50) NOT NULL UNIQUE, originallanguage VARCHAR2(20) NOT NULL, genre VARCHAR2(20) ); INSERT INTO book VALUES (book_id_seq.nextval,'Misty Nights','English','Thriller'); INSERT INTO book VALUES (book_id_seq.nextval,'Archeology in Egypt','English','Educational'); INSERT INTO book VALUES (book_id_seq.nextval,'Database Systems in Practice','English','Educational'); INSERT INTO book VALUES (book_id_seq.nextval,'Contact','English','Science Fiction'); INSERT INTO book VALUES (book_id_seq.nextval,'The Fourth Star','English','Science Fiction'); INSERT INTO book VALUES (book_id_seq.nextval,'Våren vid sjön','Swedish','Novel'); INSERT INTO book VALUES (book_id_seq.nextval,'Dödliga Data','Swedish','Thriller'); INSERT INTO book VALUES (book_id_seq.nextval,'Music Now and Before','English','Educational'); INSERT INTO book VALUES (book_id_seq.nextval,'Midsommar i Lund','Swedish','Novel'); INSERT INTO book VALUES (book_id_seq.nextval,'Encore une fois','French',NULL); INSERT INTO book VALUES (book_id_seq.nextval,'European History','English','Educational'); INSERT INTO book VALUES (book_id_seq.nextval,'Musical Instruments','English','Educational'); INSERT INTO book VALUES (book_id_seq.nextval,'Oceans on Earth','English','Educational'); INSERT INTO book VALUES (book_id_seq.nextval,'The Beach House','English','Novel'); INSERT INTO book VALUES (book_id_seq.nextval,'Le chateau de mon pere','French',NULL); INSERT INTO book VALUES (book_id_seq.nextval,'Oceanography for Dummies','English','Educational'); CREATE SEQUENCE edition_id_seq START WITH 1 INCREMENT BY 1; CREATE TABLE edition ( id INTEGER NOT NULL PRIMARY KEY, year INTEGER NOT NULL, price INTEGER NOT NULL, translations XMLTYPE NOT NULL, book INTEGER NOT NULL, UNIQUE (book, year), FOREIGN KEY (book) REFERENCES book (id) ); INSERT INTO edition VALUES (edition_id_seq.nextval, 1987, 120, '', (SELECT id FROM book WHERE title = 'Misty Nights')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1992, 250, '', (SELECT id FROM book WHERE title = 'Archeology in Egypt')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1994, 280, '', (SELECT id FROM book WHERE title = 'Archeology in Egypt')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1999, 280, '', (SELECT id FROM book WHERE title = 'Archeology in Egypt')); INSERT INTO edition VALUES (edition_id_seq.nextval, 2000, 430, '', (SELECT id FROM book WHERE title = 'Database Systems in Practice')); INSERT INTO edition VALUES (edition_id_seq.nextval, 2002, 440, '', (SELECT id FROM book WHERE title = 'Database Systems in Practice')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1988, 140, '', (SELECT id FROM book WHERE title = 'Contact')); INSERT INTO edition VALUES (edition_id_seq.nextval, 2001, 210, '', (SELECT id FROM book WHERE title = 'The Fourth Star')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1982, 45, '', (SELECT id FROM book WHERE title = 'Våren vid sjön')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1989, 48, '', (SELECT id FROM book WHERE title = 'Våren vid sjön')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1993, 120, '', (SELECT id FROM book WHERE title = 'Dödliga Data')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1997, 300, '', (SELECT id FROM book WHERE title = 'Music Now and Before')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1999, 315, '', (SELECT id FROM book WHERE title = 'Music Now and Before')); INSERT INTO edition VALUES (edition_id_seq.nextval, 2001, 335, '', (SELECT id FROM book WHERE title = 'Music Now and Before')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1988, 75, '', (SELECT id FROM book WHERE title = 'Midsommar i Lund')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1997, 120, '', (SELECT id FROM book WHERE title = 'Encore une fois')); INSERT INTO edition VALUES (edition_id_seq.nextval, 2001, 150, '', (SELECT id FROM book WHERE title = 'Encore une fois')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1998, 650, '', (SELECT id FROM book WHERE title = 'European History')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1991, 300, '', (SELECT id FROM book WHERE title = 'Musical Instruments')); INSERT INTO edition VALUES (edition_id_seq.nextval, 2001, 500, '', (SELECT id FROM book WHERE title = 'Musical Instruments')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1996, 400, '', (SELECT id FROM book WHERE title = 'Oceans on Earth')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1998, 420, '', (SELECT id FROM book WHERE title = 'Oceans on Earth')); INSERT INTO edition VALUES (edition_id_seq.nextval, 2001, 460, '', (SELECT id FROM book WHERE title = 'Oceans on Earth')); INSERT INTO edition VALUES (edition_id_seq.nextval, 2002, 500, '', (SELECT id FROM book WHERE title = 'Oceans on Earth')); INSERT INTO edition VALUES (edition_id_seq.nextval, 2002, 180, '', (SELECT id FROM book WHERE title = 'The Beach House')); INSERT INTO edition VALUES (edition_id_seq.nextval, 1964, 65, '', (SELECT id FROM book WHERE title = 'Le chateau de mon pere')); INSERT INTO edition VALUES (edition_id_seq.nextval, 2004, 190, '', (SELECT id FROM book WHERE title = 'Oceanography for Dummies')); INSERT INTO edition VALUES (edition_id_seq.nextval, 2009, 220, '', (SELECT id FROM book WHERE title = 'Oceanography for Dummies')); CREATE TABLE authorship ( book INTEGER NOT NULL, author INTEGER NOT NULL, PRIMARY KEY (book, author), FOREIGN KEY (book) REFERENCES book (id), FOREIGN KEY (author) REFERENCES author (id) ); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Misty Nights'), (SELECT id FROM author WHERE name = 'John Craft')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Archeology in Egypt'), (SELECT id FROM author WHERE name = 'Arnie Bastoft')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Archeology in Egypt'), (SELECT id FROM author WHERE name = 'Meg Gilmand')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Archeology in Egypt'), (SELECT id FROM author WHERE name = 'Chris Ryan')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Database Systems in Practice'), (SELECT id FROM author WHERE name = 'Alan Griff')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Database Systems in Practice'), (SELECT id FROM author WHERE name = 'Marty Faust')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Database Systems in Practice'), (SELECT id FROM author WHERE name = 'Celine Biceau')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Contact'), (SELECT id FROM author WHERE name = 'Carl Sagan')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'The Fourth Star'), (SELECT id FROM author WHERE name = 'Leslie Brenner')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Våren vid sjön'), (SELECT id FROM author WHERE name = 'Marie Franksson')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Dödliga Data'), (SELECT id FROM author WHERE name = 'Jakob Hanson')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Music Now and Before'), (SELECT id FROM author WHERE name = 'Sam Davis')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Music Now and Before'), (SELECT id FROM author WHERE name = 'Mimi Pappas')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Midsommar i Lund'), (SELECT id FROM author WHERE name = 'Marie Franksson')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Encore une fois'), (SELECT id FROM author WHERE name = 'Pierre Zargone')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Carl George')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Peter Feldon')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Lilian Carrera')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Auna Gonzales Perre')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Kostas Andrianos')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Andreas Shultz')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Antje Liedderman')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'European History'), (SELECT id FROM author WHERE name = 'Christina Ohlsen')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Musical Instruments'), (SELECT id FROM author WHERE name = 'Sam Davis')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Musical Instruments'), (SELECT id FROM author WHERE name = 'Alicia Bing')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Oceans on Earth'), (SELECT id FROM author WHERE name = 'Linda Evans')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Oceans on Earth'), (SELECT id FROM author WHERE name = 'Chuck Morrisson')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Oceans on Earth'), (SELECT id FROM author WHERE name = 'Kay Morrisson')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Oceanography for Dummies'), (SELECT id FROM author WHERE name = 'Linda Evans')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'The Beach House'), (SELECT id FROM author WHERE name = 'James Patterson')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'The Beach House'), (SELECT id FROM author WHERE name = 'Peter de Jonge')); INSERT INTO authorship VALUES ((SELECT id FROM book WHERE title = 'Le chateau de mon pere'), (SELECT id FROM author WHERE name = 'Franc Desteille')); CREATE TABLE publisher ( name VARCHAR2(30) NOT NULL PRIMARY KEY, street VARCHAR2(30) NOT NULL, city VARCHAR2(20) NOT NULL, postalcode VARCHAR2(10) NOT NULL, country VARCHAR2(20) NOT NULL ); INSERT INTO publisher VALUES ('ABC International','7th Bear St.','Berlin','44500','Germany'); INSERT INTO publisher VALUES ('Addison','2nd Monet St.','Toulouse','98700','France'); INSERT INTO publisher VALUES ('Aurora Publ.','3rd Uffizi Rd.','Florence','49112','Italy'); INSERT INTO publisher VALUES ('Benton Inc','4th Queen Ave.','London','34565','England'); INSERT INTO publisher VALUES ('Bästa Bok','9th Bokgatan','Stockholm','12309','Sweden'); INSERT INTO publisher VALUES ('EU Publishing','45th Beer St.','Brussels','34990','Belgium'); INSERT INTO publisher VALUES ('KLC','1st Upplandsv.','Uppsala','34067','Sweden'); INSERT INTO publisher VALUES ('Kingsly','98th Waltz St.','Vienna','12369','Austria'); INSERT INTO publisher VALUES ('Pels And Jafs','32nd Lake Ave.','Edinburg','87300','Scotland'); INSERT INTO publisher VALUES ('RP','34th Red Square','Saint Petersburg','34005','Russia'); INSERT INTO publisher VALUES ('SCB','3rd Odengatan','Stockholm','10405','Sweden'); INSERT INTO publisher VALUES ('Shou-Ling','12th Great Wall St.','Shanghai','12333','China'); INSERT INTO publisher VALUES ('Suomi Bookkii','4th Yksi Rd.','Helsinki','12003','Finland'); INSERT INTO publisher VALUES ('Turk And Turk','45th Otoman Ave.','Ankara','23000','Turkey');