----------------------------------------------------------------
-- Installation of the Database BIKE for SQL Server 
--
--            to use with SQL Server Management Studio 
--                         E. Schicker, July 2026
----------------------------------------------------------------
-- In SQL Server, cascading delete is prohibited if the
-- same table can appear again in the cascade.
-- Therefore, ON DELETE NO ACTION is set
-- in the Staff and OrderItem relations. 
-- However, this means that some examples of cascading deletes in the book
-- cannot be reproduced.
--
-- Installation notes (in Management Studio):
--
-- 1. Create a new database (schema) named bike:
--    In the Object Explorer, under Databases: Right-click - New Database
--    and enter the database name BIKE
--
-- 2. Open this script: bikeEnSQLServer.sql
--
-- 3. Click EXECUTE
--
-- The database Bike is used.
USE BIKE;

SELECT 'Installation of database BIKE. Please be patient';

BEGIN TRANSACTION;

-- 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 NO ACTION ON UPDATE NO ACTION,
-- On Delete Cascade is not allowed in SQL Server when referencing the same table
     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 NO ACTION ON UPDATE NO ACTION,
                             -- again On Delete Cascade is not allowed here
     compid      INTEGER    Constraint FK_compid_item REFERENCES item
                             ON DELETE NO ACTION ON UPDATE CASCADE,
     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 ON UPDATE CASCADE
);

CREATE TABLE delivery (
     id          INTEGER    Constraint FK_delivery_item  REFERENCES item
                             ON DELETE CASCADE ON UPDATE CASCADE,
     suppid      INTEGER    Constraint FK_delivery_supplier REFERENCES supplier
                             ON DELETE CASCADE ON UPDATE 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
                                       ON DELETE NO ACTION ON UPDATE CASCADE,
     staffid     INTEGER    Constraint FK_order_staff REFERENCES staff
                                       ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE orderitem (
     posid       INTEGER    Constraint PK_orderitem PRIMARY KEY,
     ordid       INTEGER    NOT NULL Constraint FK_orderitem_order REFERENCES orders
                                       ON DELETE CASCADE ON UPDATE CASCADE,
     itemid      INTEGER    NOT NULL Constraint FK_orderitem_item REFERENCES item
                                       ON DELETE NO ACTION ON UPDATE CASCADE,
     quantity    SMALLINT,
     totalprice  NUMERIC(7,2),
     Constraint AK_orderitem UNIQUE (ordid, itemid)
);

CREATE TABLE reserveditems (
     posid       INTEGER    Constraint FK_reserveditems_order REFERENCES orderitem
                                       ON DELETE NO ACTION ON UPDATE CASCADE,
     itemid      INTEGER    Constraint FK_reserveditems_item REFERENCES item
                                       ON DELETE NO ACTION ON UPDATE NO ACTION,

     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' , '1999-07-05', 'married'  , NULL, 4800.00 , '2' , 'Manager'    );
INSERT INTO staff
  VALUES ( 2, 'Anne Kruse'      , 93047, 'Regensburg' , '1995-07-09', 'single'   , 1   , 2300.00 , '3' , 'Sales representative'  );
INSERT INTO staff
  VALUES ( 4, 'Josef Rolle'     , 90455, 'Nuremberg'  , '1977-10-12', 'single'   , 1   , 3300.00 , '3' , 'Secretary'  );
INSERT INTO staff
  VALUES ( 5, 'Johanna Koster'  , 90427, 'Nuremberg'  , '2004-02-07', 'divorced' , 1   , 2100.00 , '5' , 'Sales representative'  );
INSERT INTO staff
  VALUES ( 6, 'Marian Lambert'  , 80335, 'Munich'     , '1994-05-22', 'married'  , NULL, 4100.00 , '1' , 'Master craftsman'    );
INSERT INTO staff
  VALUES ( 3, 'Ursula Rank'     , 60594, 'Frankfurt'  , '1987-09-04', 'married'  , 6   , 2700.00 , '1' , 'Skilled worker' );
INSERT INTO staff
  VALUES ( 7, 'Thomas Noster'   , 93047, 'Regensburg' , '1992-09-17', 'married'  , 6   , 2500.00 , '5' , 'Employee'   );
INSERT INTO staff
  VALUES ( 8, 'Renate Wolters'  , 01097, 'Dresden'    , '1999-07-14', 'single'   , 1   , 3300.00 , '4' , 'Administrative Clerk'  );
INSERT INTO staff
  VALUES ( 9, 'Michael Patch'   , 69117, 'Heidelberg' , '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, '2025-01-04', 1, 2);
INSERT INTO orders
  VALUES ( 2, '2025-01-06', 3, 5);
INSERT INTO orders
  VALUES ( 3, '2025-01-07', 4, 2);
INSERT INTO orders
  VALUES ( 4, '2025-01-18', 6, 5);
INSERT INTO orders
  VALUES ( 5, '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 );

COMMIT;

SELECT 'Example Database BIKE installed';
