---------------------------------------------------------------- -- Installation of the Database BIKE for Oracle -- -- to use with SQL Developer -- E. Schicker, June 2026 ---------------------------------------------------------------- -- Some hints to install the BIKE database on your own computer !!!! -- What you need: -- - Oracle 18c Express Edition or newer must be installed -- - Oracle SQL Developer must be installed -- -- 1. Start Oracle SQL Developer. -- Create a new connection (click the "+" on the left) with the following Admin data: -- -- Name of connection: OracleXEAdmin (or similar, freely selectable) -- Authentication type: Default -- Username: sys as sysdba -- Password: Admin Password (assigned during installation) -- Role: SYSDBA (!!!) -- Connection type: Basic -- Hostname: localhost -- Port: 1521 -- Servicename: xepdb1 (it means: XE pluggable database 1) -- -- Test this connection. Click save when successful -- -- 2. Start the Admin connection by double clicking on OracleXEAdmin and create a new standard user. -- Use the following commands (PASSWORD is freely selectable): -- -- Create User BIKE Identified By PASSWORD; (use your own password!!!) -- Grant Connect, Resource, Create View, Unlimited Tablespace To BIKE; (These are the access rights we need) -- -- 3. Create another connection (click the '+' on the left) with the following data: -- -- Name of connection: OracleXEBike (freely selectable) -- Username: bike -- Password: (the password from above) -- Role: default (!!!!) -- Connection type: Basic -- Hostname: localhost -- Port: 1521 -- Servicename: xepdb1 -- -- 4. Start your new connection OracleXEBike by double clicking. -- -- 5. Open this script (bikeEnOracle.sql) -- -- 6. Execute this script by clicking F5 -- Choose OracleXEBike" as connection. -- After a few seconds is the English version of the BIKE database installed -- SET TERMOUT ON PROMPT Installation of Database BIKE. Please wait SET TERMOUT OFF SET FEEDBACK OFF PROMPT -- Existing tables will be deleted DROP TABLE reserveditems; DROP TABLE orderitem; DROP TABLE orders; DROP TABLE itemstructure; DROP TABLE store; DROP TABLE delivery; DROP TABLE item; DROP TABLE staff; DROP TABLE customer; DROP TABLE supplier; ------------------------------------ -- Create tables: ------------------------------------ CREATE TABLE supplier ( id INTEGER Constraint PK_supplier PRIMARY KEY, name CHAR(30) NOT NULL, zip INTEGER, place CHAR(25), locks CHAR (1) ); CREATE TABLE customer ( id INTEGER Constraint PK_customer PRIMARY KEY, name CHAR(30) NOT NULL, zip INTEGER, place CHAR(20), locks CHAR (1) ); CREATE TABLE staff ( staffid INTEGER Constraint PK_staff PRIMARY KEY, name ChAR(30) NOT NULL, zip INTEGER, place CHAR(20), birthdate DATE NOT NULL, status CHAR(10), supervisor INTEGER Constraint FK_staff_staff REFERENCES staff ON DELETE SET NULL, salary NUMERIC(8,2), evaluation CHAR(1), role CHAR(25) ); CREATE TABLE item ( id INTEGER Constraint PK_item PRIMARY KEY, name CHAR(35), net NUMERIC(7,2), tax NUMERIC(7,2), price NUMERIC(7,2), color CHAR(10), dimension CHAR(15), unit CHAR(2) NOT NULL, made CHAR(1) NOT NULL ); CREATE TABLE itemstructure ( itemid INTEGER Constraint FK_itemid_item REFERENCES item ON DELETE CASCADE, compid INTEGER Constraint FK_compid_item REFERENCES item, quantity SMALLINT, unit CHAR(2), Constraint PK_itemstructure PRIMARY KEY (itemid, compid) ); CREATE TABLE store ( itemid INTEGER Constraint PK_store PRIMARY KEY, location CHAR(6), stock SMALLINT NOT NULL, minimum SMALLINT, reserved SMALLINT, ordered SMALLINT, Constraint FK_store_item FOREIGN KEY (itemid) REFERENCES item ON DELETE CASCADE ); CREATE TABLE delivery ( id INTEGER Constraint FK_delivery_item REFERENCES item ON DELETE CASCADE, suppid INTEGER Constraint FK_delivery_supplier REFERENCES supplier ON DELETE CASCADE, deliverytime SMALLINT, netprice NUMERIC(7,2), ordered SMALLINT, Constraint PK_delivery PRIMARY KEY (id, suppid) ); CREATE TABLE orders ( ordid INTEGER Constraint PK_order PRIMARY KEY, orderdate DATE, custid INTEGER NOT NULL Constraint FK_order_customer REFERENCES customer, staffid INTEGER Constraint FK_order_staff REFERENCES staff ON DELETE SET NULL ); CREATE TABLE orderitem ( posid INTEGER Constraint PK_orderitem PRIMARY KEY, ordid INTEGER NOT NULL Constraint FK_orderitem_order REFERENCES orders ON DELETE CASCADE, itemid INTEGER NOT NULL Constraint FK_orderitem_item REFERENCES item, quantity SMALLINT, totalprice NUMERIC(7,2), Constraint AK_orderitem UNIQUE (ordid, itemid) ); CREATE TABLE reserveditems ( posid INTEGER Constraint FK_reserveditems_order REFERENCES orderitem, itemid INTEGER Constraint FK_reserveditems_item REFERENCES item, quantity SMALLINT, Constraint PK_reserveditems PRIMARY KEY (posid, itemid) ); -- The tables will be filled with data now INSERT INTO supplier VALUES ( 1, 'Schmidt GmbH' , 22089, 'Hamburg ' , '0' ); INSERT INTO supplier VALUES ( 2, 'Smoky Cycle KG' , 90402, 'Nuremberg ' , '0' ); INSERT INTO supplier VALUES ( 3, 'Shimano KG' , NULL , 'Tokyo' , '0' ); INSERT INTO supplier VALUES ( 4, 'Suntour LTD' , NULL , 'London' , '0' ); INSERT INTO supplier VALUES ( 5, 'MSM GmbH' , 10963, 'Berlin' , '0' ); INSERT INTO customer VALUES ( 1, 'Biker Shop' , 93059, 'Regensburg' , '0' ); INSERT INTO customer VALUES ( 2, 'Bicycle Centre' , 44267, 'Dortmund' , '0' ); INSERT INTO customer VALUES ( 3, 'Muller Ingrid' , 93055, 'Regensburg' , '1' ); INSERT INTO customer VALUES ( 4, 'Rafa - Seger KG' , 10247, 'Berlin' , '0' ); INSERT INTO customer VALUES ( 5, 'Biker Corner' , 22087, 'Hamburg' , '0' ); INSERT INTO customer VALUES ( 6, 'Bicycle Hammer' , 81739, 'Munich' , '0' ); INSERT INTO staff VALUES ( 1, 'Mary Foster' , 93051, 'Regensburg' , DATE '1999-07-05', 'married' , NULL, 4800.00 , '2' , 'Manager' ); INSERT INTO staff VALUES ( 2, 'Anne Kruse' , 93047, 'Regensburg' , DATE '1995-07-09', 'single' , 1 , 2300.00 , '3' , 'Sales representative' ); INSERT INTO staff VALUES ( 4, 'Josef Rolle' , 90455, 'Nuremberg' , DATE '1977-10-12', 'single' , 1 , 3300.00 , '3' , 'Secretary' ); INSERT INTO staff VALUES ( 5, 'Johanna Koster' , 90427, 'Nuremberg' , DATE '2004-02-07', 'divorced' , 1 , 2100.00 , '5' , 'Sales representative' ); INSERT INTO staff VALUES ( 6, 'Marian Lambert' , 80335, 'Munich' , DATE '1994-05-22', 'married' , NULL, 4100.00 , '1' , 'Master craftsman' ); INSERT INTO staff VALUES ( 3, 'Ursula Rank' , 60594, 'Frankfurt' , DATE '1987-09-04', 'married' , 6 , 2700.00 , '1' , 'Skilled worker' ); INSERT INTO staff VALUES ( 7, 'Thomas Noster' , 93047, 'Regensburg' , DATE '1992-09-17', 'married' , 6 , 2500.00 , '5' , 'Employee' ); INSERT INTO staff VALUES ( 8, 'Renate Wolters' , 01097, 'Dresden' , DATE '1999-07-14', 'single' , 1 , 3300.00 , '4' , 'Administrative Clerk' ); INSERT INTO staff VALUES ( 9, 'Michael Patch' , 69117, 'Heidelberg' , DATE '2007-03-29', 'single' , 6 , 800.00 , NULL, 'Trainee' ); INSERT INTO item VALUES ( 100001, 'Men''s City Bike' , 588.24, 111.76, 700.00, 'blue', '26 inch', 'PC' , 'F'); INSERT INTO item VALUES ( 100002, 'Women''s City Bike' , 546.22, 103.78, 650.00, 'red', '26 inch', 'PC' , 'F'); INSERT INTO item VALUES ( 200001, 'Men''s City Frame sprayed' , 336.13, 63.87, 400.00, 'blue', NULL , 'PC' , 'A'); INSERT INTO item VALUES ( 200002, 'Women''s City Frame sprayed' , 336.13, 63.87, 400.00, 'red', NULL , 'PC' , 'A'); INSERT INTO item VALUES ( 300001, 'Men''s City Frame welded' , 310.92, 59.08, 370.00, NULL, NULL , 'PC' , 'A'); INSERT INTO item VALUES ( 300002, 'Women''s City Frame welded' , 310.92, 59.08, 370.00, NULL, NULL , 'PC' , 'A'); INSERT INTO item VALUES ( 400001, 'Wheel' , 58.82, 11.18, 70.00, NULL, '26 inch', 'PC' , 'A'); INSERT INTO item VALUES ( 500001, 'Tube 25CrMo4 9mm' , 6.30, 1.20, 7.50, NULL, '9mm' , 'CM', 'X'); INSERT INTO item VALUES ( 500002, 'Saddle' , 42.02, 7.98, 50.00, NULL, NULL , 'PC' , 'X'); INSERT INTO item VALUES ( 500003, 'Deore LX group' , 5.88, 1.12, 7.00, NULL, 'LX' , 'PC' , 'X'); INSERT INTO item VALUES ( 500004, 'Deore XT group' , 5.04, 0.96, 6.00, NULL, 'XT' , 'PC' , 'X'); INSERT INTO item VALUES ( 500005, 'XC-LTD group' , 6.72, 1.28, 8.00, NULL, 'XC-LTD' , 'PC' , 'X'); INSERT INTO item VALUES ( 500006, 'Rim set' , 33.61, 6.39, 40.00, NULL, '26 inch', 'PC' , 'X'); INSERT INTO item VALUES ( 500007, 'Schwalbe tyres' , 16.81, 3.19, 20.00, NULL, '26 inch', 'PC' , 'X'); INSERT INTO item VALUES ( 500008, 'Handlebars' , 78.99, 15.01, 94.00, NULL, NULL , 'PC' , 'X'); INSERT INTO item VALUES ( 500009, 'Seat post' , 4.62, 0.88, 5.50, NULL, NULL , 'PC' , 'X'); INSERT INTO item VALUES ( 500010, 'Pedal set' , 33.61, 6.39, 40.00, NULL, NULL , 'PC' , 'X'); INSERT INTO item VALUES ( 500011, 'Tube 34CrMo4 2.1' , 3.36, 0.64, 4.00, NULL, '2,1mm' , 'CM', 'X'); INSERT INTO item VALUES ( 500012, 'Tube 34CrMo3 2.4' , 3.61, 0.69, 4.30, NULL, '2,4mm' , 'CM', 'X'); INSERT INTO item VALUES ( 500013, 'Bottom bracket' , 25.21, 4.79, 30.00, NULL, NULL , 'PC' , 'X'); INSERT INTO item VALUES ( 500014, 'Fork set' , 10.08, 1.92, 12.00, NULL, NULL , 'PC' , 'X'); INSERT INTO item VALUES ( 500015, 'Tube' , 6.72, 1.28, 8.00, NULL, '26 inch', 'PC' , 'X'); INSERT INTO store VALUES ( 100001, '001002', 3, 0, 2, 0 ); INSERT INTO store VALUES ( 100002, '001001', 6, 0, 3, 0 ); INSERT INTO store VALUES ( 200001, NULL , 0, 0, 0, 0 ); INSERT INTO store VALUES ( 200002, '004004', 2, 0, 0, 0 ); INSERT INTO store VALUES ( 300001, NULL , 0, 0, 0, 0 ); INSERT INTO store VALUES ( 300002, '002001', 7, 0, 2, 0 ); INSERT INTO store VALUES ( 500001, '003005', 8050, 6000, 184, 0 ); INSERT INTO store VALUES ( 500002, '002002', 19, 20, 2, 10 ); INSERT INTO store VALUES ( 500003, '001003', 15, 10, 0, 0 ); INSERT INTO store VALUES ( 500004, '004001', 18, 10, 0, 0 ); INSERT INTO store VALUES ( 500005, '003002', 2, 0, 0, 0 ); INSERT INTO store VALUES ( 400001, '005001', 1, 0, 0, 0 ); INSERT INTO store VALUES ( 500006, '003004', 21, 20, 0, 0 ); INSERT INTO store VALUES ( 500007, '002003', 62, 40, 0, 0 ); INSERT INTO store VALUES ( 500008, '003003', 39, 20, 1, 0 ); INSERT INTO store VALUES ( 500009, '002007', 23, 20, 0, 0 ); INSERT INTO store VALUES ( 500010, '001006', 27, 20, 1, 0 ); INSERT INTO store VALUES ( 500011, '001007', 3250, 3000, 161, 0 ); INSERT INTO store VALUES ( 500012, '004002', 720, 600, 20, 0 ); INSERT INTO store VALUES ( 500013, '005002', 20, 20, 2, 0 ); INSERT INTO store VALUES ( 500014, '005003', 27, 20, 1, 0 ); INSERT INTO store VALUES ( 500015, '002004', 55, 40, 0, 0 ); INSERT INTO itemstructure VALUES ( 100001, 200001, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 100001, 500002, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 100001, 500003, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 100001, 400001, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 100001, 500008, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 100001, 500009, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 100001, 500010, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 100002, 200002, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 100002, 500002, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 100002, 500004, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 100002, 400001, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 100002, 500008, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 100002, 500009, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 100002, 500010, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 200001, 300001, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 200002, 300002, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 300001, 500001, 180 , 'CM' ); INSERT INTO itemstructure VALUES ( 300001, 500011, 161 , 'CM' ); INSERT INTO itemstructure VALUES ( 300001, 500012, 20 , 'CM' ); INSERT INTO itemstructure VALUES ( 300001, 500013, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 300001, 500014, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 300002, 500001, 360 , 'CM' ); INSERT INTO itemstructure VALUES ( 300002, 500011, 106 , 'CM' ); INSERT INTO itemstructure VALUES ( 300002, 500012, 20 , 'CM' ); INSERT INTO itemstructure VALUES ( 300002, 500013, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 300002, 500014, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 400001, 500007, 2 , 'PC' ); INSERT INTO itemstructure VALUES ( 400001, 500006, 1 , 'PC' ); INSERT INTO itemstructure VALUES ( 400001, 500015, 2 , 'PC' ); INSERT INTO delivery VALUES ( 500001, 5, 1, 5.50, 0); INSERT INTO delivery VALUES ( 500002, 2, 4, 36.60, 10); INSERT INTO delivery VALUES ( 500002, 1, 5, 35.10, 0); INSERT INTO delivery VALUES ( 500003, 3, 6, 5.60, 0); INSERT INTO delivery VALUES ( 500003, 4, 5, 5.45, 0); INSERT INTO delivery VALUES ( 500004, 3, 2, 4.70, 0); INSERT INTO delivery VALUES ( 500004, 4, 3, 4.50, 0); INSERT INTO delivery VALUES ( 500005, 4, 5, 5.70, 0); INSERT INTO delivery VALUES ( 500006, 1, 1, 26.00, 0); INSERT INTO delivery VALUES ( 500007, 1, 2, 15.50, 0); INSERT INTO delivery VALUES ( 500008, 1, 4, 73.00, 0); INSERT INTO delivery VALUES ( 500009, 1, 2, 4.00, 0); INSERT INTO delivery VALUES ( 500009, 2, 1, 4.10, 0); INSERT INTO delivery VALUES ( 500010, 1, 3, 29.90, 0); INSERT INTO delivery VALUES ( 500011, 5, 1, 2.60, 0); INSERT INTO delivery VALUES ( 500012, 5, 1, 3.20, 0); INSERT INTO delivery VALUES ( 500013, 1, 4, 21.00, 0); INSERT INTO delivery VALUES ( 500014, 1, 5, 9.00, 0); INSERT INTO delivery VALUES ( 500015, 1, 1, 6.00, 0); INSERT INTO orders VALUES ( 1, DATE '2025-01-04', 1, 2); INSERT INTO orders VALUES ( 2, DATE '2025-01-06', 3, 5); INSERT INTO orders VALUES ( 3, DATE '2025-01-07', 4, 2); INSERT INTO orders VALUES ( 4, DATE '2025-01-18', 6, 5); INSERT INTO orders VALUES ( 5, DATE '2025-02-03', 1, 2); INSERT INTO orderitem VALUES ( 101, 1, 200002, 2, 800.00); INSERT INTO orderitem VALUES ( 201, 2, 100002, 3, 1950.00); INSERT INTO orderitem VALUES ( 202, 2, 200001, 1, 400.00); INSERT INTO orderitem VALUES ( 301, 3, 100001, 1, 700.00); INSERT INTO orderitem VALUES ( 302, 3, 500002, 2, 100.00); INSERT INTO orderitem VALUES ( 401, 4, 100001, 1, 700.00); INSERT INTO orderitem VALUES ( 402, 4, 500001, 4, 30.00); INSERT INTO orderitem VALUES ( 403, 4, 500008, 1, 94.00); INSERT INTO orderitem VALUES ( 501, 5, 500010, 1, 40.00); INSERT INTO orderitem VALUES ( 502, 5, 500013, 1, 30.00); INSERT INTO reserveditems VALUES ( 101, 300002, 2 ); INSERT INTO reserveditems VALUES ( 201, 100002, 3 ); INSERT INTO reserveditems VALUES ( 202, 500001, 180 ); INSERT INTO reserveditems VALUES ( 202, 500011, 161 ); INSERT INTO reserveditems VALUES ( 202, 500012, 20 ); INSERT INTO reserveditems VALUES ( 202, 500013, 1 ); INSERT INTO reserveditems VALUES ( 202, 500014, 1 ); INSERT INTO reserveditems VALUES ( 301, 100001, 1 ); INSERT INTO reserveditems VALUES ( 302, 500002, 2 ); INSERT INTO reserveditems VALUES ( 401, 100001, 1 ); INSERT INTO reserveditems VALUES ( 402, 500001, 4 ); INSERT INTO reserveditems VALUES ( 403, 500008, 1 ); INSERT INTO reserveditems VALUES ( 501, 500010, 1 ); INSERT INTO reserveditems VALUES ( 502, 500013, 1 ); SET TERMOUT ON SET FEEDBACK ON PROMPT Example Database BIKE installed