-- db initialization script (project IIS)
-- By Kamil Dudka, 2006
-- ATTENTION: This script erase all data in db.
-- See UML model of db for more information.
DROP TABLE cart;
DROP TABLE order_items;
DROP TABLE orders;
DROP TABLE tickets;
DROP TABLE routes;
DROP TABLE users;
DROP TABLE transporters;
DROP TABLE dealers;
DROP TABLE firms;
CREATE TABLE firms (
ico INT PRIMARY KEY,
name VARCHAR(32),
address VARCHAR(64),
tel VARCHAR(32),
www VARCHAR(64),
email VARCHAR(32)
);
INSERT INTO firms VALUES (0, 'T-shop s.r.o.', 'Husitská 612, Brno', '+420 654987321', 'www.t-shop.com', 'info@t-shop.com');
CREATE TABLE dealers (
ico INT PRIMARY KEY -- FK (firms) --
);
INSERT INTO firms VALUES (17216217, 'Trafika KrPole', 'Skácelova 63, Brno', NULL, NULL, 'trkrp@seznamcz');
INSERT INTO dealers VALUES (17216217);
INSERT INTO firms VALUES (17216227, 'Listovy koleje', 'Kounicova 46/48, Brno', '+420 156324798', 'www.kn.vutbr.cz', NULL);
INSERT INTO dealers VALUES (17216227);
CREATE TABLE transporters (
ico INT PRIMARY KEY -- FK (firms) --
);
INSERT INTO firms VALUES (14722920, 'Autobusy Brno a.s.', 'Komín 34, Brno', '+420 159375654', 'www.autobusy.net', 'objednavky@autobusy.net');
INSERT INTO transporters VALUES (14722920);
INSERT INTO firms VALUES (17216237, 'Rychlo-doprava s.r.o.', 'Stodolní 70, Ostrava', '+420 984351648', 'www.stodolni-bus.cz', 'test@stodolni-bus.cz');
INSERT INTO transporters VALUES (17216237);
CREATE TABLE users (
user VARCHAR(16) PRIMARY KEY,
passwd VARCHAR(41),
name VARCHAR(64),
expiration INT,
last_access INT,
ico INT -- FK (firms) --
);INSERT INTO tickets SET route_id=6, seat_id=30, price=108;
INSERT INTO users VALUES ('root', password('proj2iis'), 'Výchozí administrátorský účet', 0, 0, 0);
INSERT INTO users VALUES ('admin', password('proj2iis'), 'Uživatelem administrátorský účet', 0, 0, 0);
INSERT INTO users VALUES ('prodejce1', password('proj2iis'), 'Testovací prodejce 1', 0, 0, 17216217);
INSERT INTO users VALUES ('prodejce2', password('proj2iis'), 'Testovací prodejce 2', 0, 0, 17216227);
INSERT INTO users VALUES ('test', password('proj2iis'), 'Testovací uživatel (prodejce 2)', 0, 0, 17216227);
INSERT INTO users VALUES ('dopravce1', password('proj2iis'), 'Testovací dopravce 1', 0, 0, 14722920);
INSERT INTO users VALUES ('dopravce2', password('proj2iis'), 'Testovací dopravce 2', 0, 0, 17216237);
CREATE TABLE routes (
route_id INT PRIMARY KEY AUTO_INCREMENT,
ico INT, -- FK (transporters) --
route_date TIMESTAMP,
route_from VARCHAR(64),
route_to VARCHAR(64),
duration INT
);
INSERT INTO routes SET
ico=14722920,
route_date='2007-02-02 14:54:00',
route_from='Brno',
route_to='Praha',
duration=120;
INSERT INTO routes SET
ico=17216237,
route_date='2007-03-07 23:54:00',
route_from='Ostrava',
route_to='Český Ťešín',
duration=25;
INSERT INTO routes SET
ico=14722920,
route_date='2007-02-06 11:10:00',
route_from='Brno',
route_to='Ostrava',
duration=108;
INSERT INTO routes SET
ico=17216237,
route_date='2007-03-10 10:37:00',
route_from='Ostrava',
route_to='Olomouc',
duration=80;
INSERT INTO routes SET
ico=14722920,
route_date='2007-02-06 16:48:00',
route_from='Ostrava',
route_to='Brno',
duration=112;
INSERT INTO routes SET
ico=14722920,
route_date='2007-03-03 10:00:00',
route_from='Praha',
route_to='Brno',
duration=200;
CREATE TABLE tickets (
route_id INT, -- FK (routes) --
seat_id INT,
price INT,
PRIMARY KEY (route_id, seat_id)
);
INSERT INTO tickets SET route_id=1, seat_id=1, price=150;
INSERT INTO tickets SET route_id=1, seat_id=2, price=150;
INSERT INTO tickets SET route_id=1, seat_id=3, price=150;
INSERT INTO tickets SET route_id=1, seat_id=4, price=150;
INSERT INTO tickets SET route_id=1, seat_id=5, price=150;
INSERT INTO tickets SET route_id=1, seat_id=6, price=150;
INSERT INTO tickets SET route_id=1, seat_id=7, price=120;
INSERT INTO tickets SET route_id=1, seat_id=8, price=120;
INSERT INTO tickets SET route_id=1, seat_id=9, price=120;
INSERT INTO tickets SET route_id=1, seat_id=10, price=120;
INSERT INTO tickets SET route_id=1, seat_id=11, price=120;
INSERT INTO tickets SET route_id=1, seat_id=12, price=120;
INSERT INTO tickets SET route_id=1, seat_id=13, price=120;
INSERT INTO tickets SET route_id=1, seat_id=14, price=120;
INSERT INTO tickets SET route_id=1, seat_id=15, price=80;
INSERT INTO tickets SET route_id=1, seat_id=16, price=80;
INSERT INTO tickets SET route_id=1, seat_id=17, price=80;
INSERT INTO tickets SET route_id=1, seat_id=18, price=80;
INSERT INTO tickets SET route_id=2, seat_id=1, price=18;
INSERT INTO tickets SET route_id=2, seat_id=2, price=18;
INSERT INTO tickets SET route_id=2, seat_id=3, price=18;
INSERT INTO tickets SET route_id=2, seat_id=4, price=18;
INSERT INTO tickets SET route_id=2, seat_id=5, price=18;
INSERT INTO tickets SET route_id=2, seat_id=6, price=18;
INSERT INTO tickets SET route_id=2, seat_id=7, price=18;
INSERT INTO tickets SET route_id=2, seat_id=8, price=18;
INSERT INTO tickets SET route_id=3, seat_id=1, price=100;
INSERT INTO tickets SET route_id=3, seat_id=2, price=100;
INSERT INTO tickets SET route_id=3, seat_id=3, price=100;
INSERT INTO tickets SET route_id=3, seat_id=4, price=100;
INSERT INTO tickets SET route_id=3, seat_id=5, price=100;
INSERT INTO tickets SET route_id=3, seat_id=6, price=100;
INSERT INTO tickets SET route_id=3, seat_id=7, price=100;
INSERT INTO tickets SET route_id=3, seat_id=8, price=100;
INSERT INTO tickets SET route_id=3, seat_id=9, price=100;
INSERT INTO tickets SET route_id=3, seat_id=10, price=100;
INSERT INTO tickets SET route_id=3, seat_id=11, price=100;
INSERT INTO tickets SET route_id=3, seat_id=12, price=100;
INSERT INTO tickets SET route_id=3, seat_id=13, price=100;
INSERT INTO tickets SET route_id=3, seat_id=14, price=100;
INSERT INTO tickets SET route_id=3, seat_id=15, price=100;
INSERT INTO tickets SET route_id=3, seat_id=16, price=100;
INSERT INTO tickets SET route_id=3, seat_id=17, price=80;
INSERT INTO tickets SET route_id=3, seat_id=18, price=80;
INSERT INTO tickets SET route_id=3, seat_id=19, price=80;
INSERT INTO tickets SET route_id=3, seat_id=20, price=80;
INSERT INTO tickets SET route_id=3, seat_id=21, price=80;
INSERT INTO tickets SET route_id=3, seat_id=22, price=80;
INSERT INTO tickets SET route_id=3, seat_id=23, price=80;
INSERT INTO tickets SET route_id=3, seat_id=24, price=80;
INSERT INTO tickets SET route_id=3, seat_id=25, price=80;
INSERT INTO tickets SET route_id=4, seat_id=1, price=80;
INSERT INTO tickets SET route_id=4, seat_id=2, price=80;
INSERT INTO tickets SET route_id=4, seat_id=3, price=80;
INSERT INTO tickets SET route_id=4, seat_id=4, price=80;
INSERT INTO tickets SET route_id=4, seat_id=5, price=80;
INSERT INTO tickets SET route_id=4, seat_id=6, price=80;
INSERT INTO tickets SET route_id=4, seat_id=7, price=80;
INSERT INTO tickets SET route_id=4, seat_id=8, price=80;
INSERT INTO tickets SET route_id=4, seat_id=9, price=80;
INSERT INTO tickets SET route_id=4, seat_id=10, price=80;
INSERT INTO tickets SET route_id=4, seat_id=11, price=80;
INSERT INTO tickets SET route_id=4, seat_id=12, price=60;
INSERT INTO tickets SET route_id=4, seat_id=13, price=60;
INSERT INTO tickets SET route_id=4, seat_id=14, price=60;
INSERT INTO tickets SET route_id=4, seat_id=15, price=60;
INSERT INTO tickets SET route_id=4, seat_id=16, price=60;
INSERT INTO tickets SET route_id=4, seat_id=17, price=60;
INSERT INTO tickets SET route_id=4, seat_id=18, price=60;
INSERT INTO tickets SET route_id=4, seat_id=19, price=60;
INSERT INTO tickets SET route_id=4, seat_id=20, price=60;
INSERT INTO tickets SET route_id=4, seat_id=21, price=60;
INSERT INTO tickets SET route_id=4, seat_id=22, price=60;
INSERT INTO tickets SET route_id=4, seat_id=23, price=60;
INSERT INTO tickets SET route_id=5, seat_id=1, price=150;
INSERT INTO tickets SET route_id=5, seat_id=2, price=150;
INSERT INTO tickets SET route_id=5, seat_id=3, price=150;
INSERT INTO tickets SET route_id=5, seat_id=4, price=150;
INSERT INTO tickets SET route_id=5, seat_id=5, price=150;
INSERT INTO tickets SET route_id=5, seat_id=6, price=150;
INSERT INTO tickets SET route_id=5, seat_id=7, price=120;
INSERT INTO tickets SET route_id=5, seat_id=8, price=120;
INSERT INTO tickets SET route_id=5, seat_id=9, price=120;
INSERT INTO tickets SET route_id=5, seat_id=10, price=120;
INSERT INTO tickets SET route_id=5, seat_id=11, price=120;
INSERT INTO tickets SET route_id=5, seat_id=12, price=120;
INSERT INTO tickets SET route_id=5, seat_id=13, price=120;
INSERT INTO tickets SET route_id=5, seat_id=14, price=120;
INSERT INTO tickets SET route_id=5, seat_id=15, price=80;
INSERT INTO tickets SET route_id=5, seat_id=16, price=80;
INSERT INTO tickets SET route_id=5, seat_id=17, price=80;
INSERT INTO tickets SET route_id=5, seat_id=18, price=80;
INSERT INTO tickets SET route_id=6, seat_id=1, price=108;
INSERT INTO tickets SET route_id=6, seat_id=2, price=108;
INSERT INTO tickets SET route_id=6, seat_id=3, price=108;
INSERT INTO tickets SET route_id=6, seat_id=4, price=108;
INSERT INTO tickets SET route_id=6, seat_id=5, price=108;
INSERT INTO tickets SET route_id=6, seat_id=6, price=108;
INSERT INTO tickets SET route_id=6, seat_id=7, price=108;
INSERT INTO tickets SET route_id=6, seat_id=8, price=108;
INSERT INTO tickets SET route_id=6, seat_id=9, price=108;
INSERT INTO tickets SET route_id=6, seat_id=10, price=108;
INSERT INTO tickets SET route_id=6, seat_id=11, price=108;
INSERT INTO tickets SET route_id=6, seat_id=12, price=108;
INSERT INTO tickets SET route_id=6, seat_id=13, price=108;
INSERT INTO tickets SET route_id=6, seat_id=14, price=108;
INSERT INTO tickets SET route_id=6, seat_id=15, price=108;
INSERT INTO tickets SET route_id=6, seat_id=16, price=108;
INSERT INTO tickets SET route_id=6, seat_id=17, price=108;
INSERT INTO tickets SET route_id=6, seat_id=18, price=108;
INSERT INTO tickets SET route_id=6, seat_id=19, price=108;
INSERT INTO tickets SET route_id=6, seat_id=20, price=108;
INSERT INTO tickets SET route_id=6, seat_id=21, price=108;
INSERT INTO tickets SET route_id=6, seat_id=22, price=108;
INSERT INTO tickets SET route_id=6, seat_id=23, price=108;
INSERT INTO tickets SET route_id=6, seat_id=24, price=108;
INSERT INTO tickets SET route_id=6, seat_id=25, price=108;
INSERT INTO tickets SET route_id=6, seat_id=26, price=108;
INSERT INTO tickets SET route_id=6, seat_id=27, price=108;
INSERT INTO tickets SET route_id=6, seat_id=28, price=108;
INSERT INTO tickets SET route_id=6, seat_id=29, price=108;
INSERT INTO tickets SET route_id=6, seat_id=30, price=108;
INSERT INTO tickets SET route_id=6, seat_id=31, price=108;
INSERT INTO tickets SET route_id=6, seat_id=32, price=108;
INSERT INTO tickets SET route_id=6, seat_id=33, price=108;
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user VARCHAR(16), -- FK (users) --
ctime TIMESTAMP,
maturity TIMESTAMP,
paid_date TIMESTAMP
);
INSERT INTO orders SET
user='prodejce2',
ctime='2006-03-10 10:37:00',
maturity='2006-04-01 10:37:00',
paid_date= '2006-03-17 17:46:17';
CREATE TABLE order_items (
order_id INT, -- FK (orders) --
route_id INT, -- FK (tickets) --
seat_id INT, -- FK (tickets) --
PRIMARY KEY (order_id, route_id, seat_id)
);
INSERT INTO order_items VALUES (1,2,1);
INSERT INTO order_items VALUES (1,2,2);
INSERT INTO order_items VALUES (1,2,3);
INSERT INTO order_items VALUES (1,2,4);
INSERT INTO order_items VALUES (1,2,5);
INSERT INTO order_items VALUES (1,2,6);
INSERT INTO order_items VALUES (1,2,7);
INSERT INTO order_items VALUES (1,4,10);
INSERT INTO order_items VALUES (1,4,11);
INSERT INTO order_items VALUES (1,4,12);
INSERT INTO order_items VALUES (1,4,13);
INSERT INTO order_items VALUES (1,4,14);
INSERT INTO order_items VALUES (1,4,15);
INSERT INTO order_items VALUES (1,4,16);
INSERT INTO order_items VALUES (1,4,17);
INSERT INTO order_items VALUES (1,4,18);
INSERT INTO order_items VALUES (1,5,10);
INSERT INTO order_items VALUES (1,5,11);
INSERT INTO order_items VALUES (1,5,12);
INSERT INTO order_items VALUES (1,5,13);
INSERT INTO order_items VALUES (1,5,14);
INSERT INTO order_items VALUES (1,5,15);
INSERT INTO order_items VALUES (1,5,16);
INSERT INTO order_items VALUES (1,5,17);
INSERT INTO order_items VALUES (1,5,18);
INSERT INTO order_items VALUES (1,5,19);
INSERT INTO order_items VALUES (1,5,20);
INSERT INTO order_items VALUES (1,5,21);
INSERT INTO order_items VALUES (1,1,14);
INSERT INTO order_items VALUES (1,1,15);
INSERT INTO order_items VALUES (1,1,16);
INSERT INTO order_items VALUES (1,1,17);
INSERT INTO order_items VALUES (1,1,18);
CREATE TABLE cart (
user VARCHAR(16), -- FK (users) --
route_id INT, -- FK (tickets) --
seat_id INT, -- FK (tickets) --
ctime TIMESTAMP,
PRIMARY KEY (user, route_id, seat_id)
);
Back to main page
xdudka00.sql [Download]