connect to cs348

drop table author
create table author ( \
    aid         integer not null, \
    name        char(22) not null, \
    url         char(42), \
    primary key (aid) )
-------------------------------------------------++++++++++++++++++++++----------
INSERT INTO author (aid, name) VALUES (1, 	'Ajit_A._Diwan')
INSERT INTO author (aid, name) VALUES (2, 	'Ch._Sobhan Babu')
INSERT INTO author (aid, name) VALUES (34,	'Ben_Chin_Ooi')
INSERT INTO author (aid, name) VALUES (3, 	'Daniel_Deutch')
INSERT INTO author (aid, name) VALUES (4, 	'David_DeHaan')
INSERT INTO author (aid, name) VALUES (5, 	'Dongyan_Zhao')
INSERT INTO author (aid, name) VALUES (31, 	'Gang_Chen')
INSERT INTO author (aid, name) VALUES (6, 	'Goetz_Graefe')
INSERT INTO author (aid, name) VALUES (7, 	'Gunes_Aluc')
INSERT INTO author (aid, name) VALUES (8, 	'Gustavo_Alonso')
INSERT INTO author (aid, name) VALUES (9, 	'Harumi_A._Kuno')
INSERT INTO author (aid, name) VALUES (32,	'Hoang_Tam_Vo')
INSERT INTO author (aid, name) VALUES (10, 	'Ivan_T._Bowman')
INSERT INTO author (aid, name) VALUES (11, 	'Jens_Teubner')
INSERT INTO author (aid, name) VALUES (12, 	'Jinghui_Mo')
INSERT INTO author (aid, name) VALUES (13, 	'K._Georgoulas')
INSERT INTO author (aid, name) VALUES (14, 	'Lei_Chen')
INSERT INTO author (aid, name) VALUES (15, 	'Lei_Zou')
INSERT INTO author (aid, name) VALUES (16, 	'M._Tamer_Ozsu')
INSERT INTO author (aid, name) VALUES (17, 	'Markus_Kirchberg')
INSERT INTO author (aid, name) VALUES (18, 	'Muhammad_A._Cheema')
INSERT INTO author (aid, name) VALUES (19, 	'Patrick_Valduriez')
INSERT INTO author (aid, name) VALUES (20, 	'R._Guravannavar')
INSERT INTO author (aid, name) VALUES (21, 	'Rene_Muller')
INSERT INTO author (aid, name) VALUES (33,	'Sai_Wu')
INSERT INTO author (aid, name) VALUES (22, 	'S._Sudarshan')
INSERT INTO author (aid, name) VALUES (23, 	'Sebastian_Link')
INSERT INTO author (aid, name) VALUES (24, 	'Sven_Hartmann')
INSERT INTO author (aid, name) VALUES (25, 	'Tova_Milo')
INSERT INTO author (aid, name) VALUES (26, 	'Wenjie_Zhang')
INSERT INTO author (aid, name) VALUES (27, 	'Xuefei_Li')
INSERT INTO author (aid, name) VALUES (28, 	'Xuemin_Lin')
INSERT INTO author (aid, name) VALUES (29, 	'Yannis_Kotidis')
INSERT INTO author (aid, name) VALUES (30, 	'Ying_Zhang')
INSERT INTO author (aid, name) VALUES (35,	'Yingying_Tao')

drop table publication
create table publication ( \
    pubid       char(10) not null, \
    title       char(70) not null, \
    primary key (pubid) )

----------++++++++++-----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++----------
INSERT INTO publication (pubid, title) VALUES \
	('MullerTA12', 	'Sorting networks on FPGAs'), \
	('GeorgouK12', 	'Distributed similarity estimation using derived dimensions'), \
	('DeutchM12',	'Type inference and type checking for queries over execution traces'), \
	('CheeZLZL12',	'Continuous reverse k nearest neighbors queries in Euclidean space...'), \
	('ZouCOZ12', 	'Answering pattern match queries in large graph databases via graph...'), \
	('HartmaKL12', 	'Design by example for SQL table definitions with functional...'), \
	('GuravSDB12', 	'Which sort orders are interesting'), \
	('AlucDB12', 	'Parametric plan caching using density-based clustering'), \
	('Graefe11',	'Robust query processing'), \
	('GraefeK11',	'Modern B-tree techniques'), \
	('ZouMCOZ11',	'gStore: Answering SPARQL Queries via Subgraph Matching'), \
	('CheVWOO11',	'A Framework for Supporting DBMS-like Indexes in the Cloud'), \
	('TaoO09a', 	'Mining frequent itemsets in time-varying data streams')

drop table wrote
create table wrote ( \
    aid         integer not null, \
    pubid       char(10) not null, \
    aorder      integer not null, \
    primary key (aid, pubid), \
    foreign key (aid) references author (aid), \
    foreign key (pubid) references publication (pubid) )

INSERT INTO wrote (aid, pubid, aorder) VALUES \
	(7, 'AlucDB12', 1), \
	(4, 'AlucDB12', 2), \
	(10, 'AlucDB12', 3)

INSERT INTO wrote (aid, pubid, aorder) VALUES \
	(6, 'Graefe11', 1)

INSERT INTO wrote (aid, pubid, aorder) VALUES \
	(6, 'GraefeK11', 1), \
	(9, 'GraefeK11', 2)

INSERT INTO wrote (aid, pubid, aorder) VALUES \
	(15, 'ZouMCOZ11', 1), \
	(12, 'ZouMCOZ11', 2), \
	(14, 'ZouMCOZ11', 3), \
	(16, 'ZouMCOZ11', 4), \
	(5, 'ZouMCOZ11', 5)

INSERT INTO wrote (aid, pubid, aorder) VALUES \
	(21, 'MullerTA12', 1), \
	(11, 'MullerTA12', 2), \
	(8, 'MullerTA12', 3)
INSERT INTO wrote (aid, pubid, aorder) VALUES \
	(13, 'GeorgouK12', 1), \
	(29, 'GeorgouK12', 2)
INSERT INTO wrote (aid, pubid, aorder) VALUES \
	(3, 'DeutchM12', 1), \
	(25, 'DeutchM12', 2)
INSERT INTO wrote (aid, pubid, aorder) VALUES \
	(18, 'CheeZLZL12', 1), \
	(26, 'CheeZLZL12', 2), \
	(28, 'CheeZLZL12', 3), \
	(30, 'CheeZLZL12', 4), \
	(27, 'CheeZLZL12', 5)
INSERT INTO wrote (aid, pubid, aorder) VALUES \
	(15, 'ZouCOZ12', 1), \
	(14, 'ZouCOZ12', 2), \
	(16, 'ZouCOZ12', 3), \
	(5, 'ZouCOZ12', 4)
INSERT INTO wrote (aid, pubid, aorder) VALUES \
	(24, 'HartmaKL12', 1), \
	(17, 'HartmaKL12', 2), \
	(23, 'HartmaKL12', 3)
INSERT INTO wrote (aid, pubid, aorder) VALUES \
	(20, 'GuravSDB12', 1), \
	(22, 'GuravSDB12', 2), \
	(1, 'GuravSDB12', 3), \
	(2, 'GuravSDB12', 4)
INSERT INTO wrote (aid, pubid, aorder) VALUES \
        (31, 'CheVWOO11', 1), \
        (32, 'CheVWOO11', 2), \
        (33, 'CheVWOO11', 3), \
        (34, 'CheVWOO11', 4), \
	(16, 'CheVWOO11', 5)
INSERT INTO wrote (aid, pubid, aorder) VALUES \
	(35, 'TaoO09a', 1), \
	(16, 'TaoO09a', 2)

drop table proceedings
create table proceedings ( \
    pubid       char(10) not null, \
    year        integer not null, \
    primary key (pubid), \
    foreign key (pubid) references publication (pubid) )

----------++++++++++-----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++----------
INSERT INTO publication (pubid, title) VALUES \
	('ICDE2011', 	'Proc. IEEE 28th International Conference on Data Engineering'), \
	('ICDE2012', 	'Proc. IEEE 29th International Conference on Data Engineering'), \
	('CIKM2009', 	'Proc. 18th ACM Conference on Information and Knowledge Management')

INSERT INTO proceedings (pubid, year) VALUES \
	('ICDE2011', 2011), \
	('ICDE2012', 2012), \
	('CIKM2009', 2009)

drop table journal
create table journal ( \
    pubid       char(10) not null, \
    volume      integer not null, \
    number      integer not null, \
    year        integer not null, \
    primary key (pubid), \
    foreign key (pubid) references publication (pubid) )

----------++++++++++-----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++----------
INSERT INTO publication (pubid, title) VALUES \
	('VLDBJ21_1', 	'VLDB Journal'), \
	('PVLDB4_8', 	'Proceedings of the VLDB Endowment'), \
	('PVLDB4_11',	'Proceedings of the VLDB Endowment')

INSERT INTO journal (pubid, volume, number, year) VALUES \
	('VLDBJ21_1', 21, 1, 2012), \
	('PVLDB4_8', 4, 8, 2011), \
	('PVLDB4_11', 4, 11, 2011)

drop table book
create table book ( \
    pubid       char(10) not null, \
    publisher   char(50) not null, \
    year        integer not null, \
    primary key (pubid), \
    foreign key (pubid) references publication (pubid) )

----------++++++++++-----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++----------
INSERT INTO publication (pubid, title) VALUES \
	('0029498', 	'Principles of Distributed Database Systems, Third Edition')

INSERT INTO wrote (aid, pubid, aorder) VALUES \
	(16, '0029498', 1), \
	(19, '0029498', 2)

INSERT INTO book (pubid, publisher, year) VALUES \
	('0029498', 'Springer', 2011)

drop table article
create table article ( \
    pubid       char(10) not null, \
    appearsin   char(10) not null, \
    startpage   integer not null, \
    endpage     integer not null, \
    primary key (pubid), \
    foreign key (pubid) references publication (pubid), \
    foreign key (appearsin) references publication (pubid) )

INSERT INTO article (pubid, appearsin, startpage, endpage) VALUES \
	('MullerTA12', 'VLDBJ21_1', 1, 23), \
	('GeorgouK12', 'VLDBJ21_1', 25, 50), \
	('DeutchM12', 'VLDBJ21_1', 51, 68), \
	('CheeZLZL12', 'VLDBJ21_1', 69, 95), \
	('ZouCOZ12', 'VLDBJ21_1', 97, 120), \
	('HartmaKL12', 'VLDBJ21_1', 121, 144), \
	('GuravSDB12', 'VLDBJ21_1', 145, 165), \
	('AlucDB12', 'ICDE2012', 402, 413), \
	('Graefe11', 'ICDE2011', 1361, 1361), \
	('GraefeK11', 'ICDE2011', 1370, 1373), \
	('ZouMCOZ11', 'PVLDB4_8', 482, 493), \
	('CheVWOO11', 'PVLDB4_11', 702, 713), \
	('TaoO09a', 'CIKM2009', 1521, 1524)

commit work

