Database.sql

BEGIN TRANSACTION;
CREATE TABLE member (
	quest_id	TEXT,
	name	TEXT,
	email	TEXT,
	faculty	TEXT,
	major	TEXT,
	paid	TEXT CHECK(paid IN ( 'Y' , 'N' )),
	card	TEXT CHECK(card IN ( 'Y' , 'N' )),
	PRIMARY KEY(quest_id)
);

INSERT INTO member (quest_id,name,email,faculty,major,paid,card) VALUES ('darrell','Darrell Aucoin','darrell.aucoin@gmail.com','Math','Stats','Y','Y'),
 ('fred','Fred E. Finch','fred@uwaterloo.ca','Math','Pure Math','Y','Y'),
 ('ryan','Ryan T. Luby','ryan@uwaterloo.ca','Math','Stats','Y','Y'),
 ('billy','Billy L. Hunter','billy@uwaterloo.ca','Math','CS','Y','Y'),
 ('john','John J. Oquendo','john@uwaterloo.ca','Math','Applied Math','Y','Y'),
 ('step','Stephanie R. Matthews','step@uwaterloo.ca','Math','Act Sci','Y','Y'),
 ('robert','Robert B. Williams','robert@uwaterloo.ca','Math','Stats','Y','Y'),
 ('austin','Austin K. Gilliard','austin@uwaterloo.ca','Math','Act Sci','Y','Y'),
 ('james','James M. Eddings','james@uwaterloo.ca','Math','CS','Y','Y'),
 ('elaine','Elaine S. Ott','elaine@uwaterloo.ca','Math','Stats','Y','Y'),
 ('james.fox','James A. Foxt','james.fox@uwaterloo.ca','Math','Act Sci','Y','Y'),
 ('daniel','Daniel J. Moore','daniel@uwaterloo.ca','Math','Stats','Y','Y'),
 ('kelly','Kelly S. Ferguson','kelly@uwaterloo.ca','Math','Stats','Y','Y'),
 ('joseph','Joseph L. Wood','joseph@uwaterloo.ca','Math','C & O','Y','Y'),
 ('vivian','Vivian R. Donley','vivian@uwaterloo.ca','Math','Stats','Y','Y'),
 ('frances','Frances A. Miller','frances@uwaterloo.ca','Math','Stats','Y','Y'),
 ('mina','Mina W. Lawrence','mina@uwaterloo.ca','Math','Stats','Y','Y'),
 ('phillip','Phillip C. Mascarenas','phillip@uwaterloo.ca','Math','Act Sci','Y','Y'),
 ('jeff','Jeff M. Wright','jeff@uwaterloo.ca','Math','Stats','Y','Y'),
 ('deborah','Deborah D. Helfrich','deborah@uwaterloo.ca','Math','Act Sci','Y','Y'),
 ('nancy','Nancy P. Jackson','nancy@uwaterloo.ca','Math','Stats','Y','Y'),
 ('bobbie','Bobbie D. Mathews','bobbie@uwaterloo.ca','Math','Act Sci','Y','Y'),
 ('arnold','Arnold J. Fuller','arnold@uwaterloo.ca','Math','CS','Y','Y'),
 ('melvin','Melvin O. Martin','melvin@uwaterloo.ca','Math','C & O','Y','Y'),
 ('ralph','Ralph L. Waldrop','ralph@uwaterloo.ca','Math','Act Sci','Y','Y'),
 ('mildred','Mildred F. Hottinger','mildred@uwaterloo.ca','Art','Econ','Y','Y'),
 ('tameika','Tameika M. McMaster','tameika@uwaterloo.ca','Math','Stats','Y','Y'),
 ('melissa','Melissa R. Anderson','melissa@uwaterloo.ca','Math','Act Sci','Y','Y'),
 ('janelle','Janelle T. Smith','janelle@uwaterloo.ca','Math','Applied Math','Y','Y'),
 ('ann','Ann W. McLaughlin','ann@uwaterloo.ca','Math','Stats','Y','Y'),
 ('judith','Judith B. Gibbons','judith@uwaterloo.ca','Math','Act Sci','Y','Y'),
 ('ruben','Ruben Lamb','ruben@uwaterloo.ca','Math','Act Sci','Y','Y'),
 ('dominick','Dominick Byrd','dominick@uwaterloo.ca','Math','Pure Math','Y','Y'),
 ('patrick','Patrick Robertson','patrick@uwaterloo.ca','Math','Stats','Y','Y');

 CREATE TABLE exec_positions (
	position	TEXT,
	PRIMARY KEY(position)
);


INSERT INTO exec_positions (position) VALUES 
('Events'),
 ('Finance'),
 ('President'),
 ('Senior Advisor'),
 ('Technology');

 
CREATE TABLE exec_position (
	position	TEXT,
	duties	TEXT,
	PRIMARY KEY(position,duties),
	foreign key(position) references exec_positions(position) on update cascade on delete cascade
);

INSERT INTO exec_position (position,duties) 
VALUES ('Events','To assist the president and other vice-presidents in administrative duties and events.'),
 ('Events','To chair the organization and promotion of least one event per semester, and ensure its success.'),
 ('Finance','To ensure membership fees are collected and maintain a list of all past and current members.'),
 ('Finance','To keep an up-to-date record of financial transactions and the purpose of expenditures, and to present this record to any club member or MathSoc Council member upon request.'),
 ('Finance','To prepare a summary of the financial records at the end of the academic term.'),
 ('Finance','To prepare the budget at the beginning of term.'),
 ('Finance','To volunteer as president in the absence of the president.'),
 ('President','To be aware of MathSoc''s Policies and Bylaws in regards to the club''s management and activities.'),
 ('President','To call and preside over general meetings.'),
 ('President','To manage the executive team and the strategic direction of the club.'),
 ('President','To post announcements of all club meetings, and to send notice of the same to MathSocs President and MathSocs Director of Internal Affairs'),
 ('Senior Advisor','Have previous club management experience in order to advise the president and executive team on club matters.'),
 ('Senior Advisor','To be aware of MathSoc''s Policies and Bylaws in regards to the club''s management and activities.'),
 ('Technology','Maintain and update the club website.'),
 ('Technology','Maintain any hardware, software, or technology the club may possess.'),
 ('Technology','Perform the duties of a Vice President - Events if possible.');
 
CREATE TABLE exec (
	stud_num	INTEGER,
	name	TEXT,
	questid	TEXT NOT NULL,
	position	TEXT,
	email	TEXT,
	phone	TEXT,
	key_fob	TEXT,
	PRIMARY KEY(questid),
	FOREIGN KEY(position) REFERENCES exec_positions(position) on update cascade on delete cascade
);

INSERT INTO exec (stud_num, name, questid, position, email, phone, key_fob) 
VALUES (20434557,'Darrell Aucoin','darrell','President','darrell.aucoin@gmail.com','519-555-1424','Y'),
 (20334532,'Judith B. Gibbons','judith','Events','judith@uwaterloo.ca','519-555-2343','N'),
 (20345524,'Lamar Roy','lamar','Finance','lamar@uwaterloo.ca','519-555-3432','Y'),
 (20565526,'Gilberto Cross','gilberto','Events','gilberto@uwaterloo.ca','519-555-3453','Y'),
 (20344527,'Melba Lane','melba','President','melba@uwaterloo.ca','519-555-2322','Y'),
 (20554329,'Ruben Lamb','ruben','Technology','ruben@uwaterloo.ca','519-555-5232','Y'),
 (20893434,'Hannah Mason','hannah','SeniorAdvisor','hannah@uwaterloo.ca','519-555-2342','N'),
 (20343532,'Patrick Robertson','patrick','Events','patrick@uwaterloo.ca','519-555-2312','N'), 
 (20334321,'Dominick Byrd','dominick','Events','dominick@uwaterloo.ca','519-555-2325','N');
 
 
CREATE TABLE example (
	number	INTEGER,
	floating	REAL,
	string	TEXT
);

INSERT INTO example (number,floating,string) VALUES (1,23.23,'this'),
 (3232,-21.23,'is'),
 (11,-2.0,'a'),
 (-23,54.0,'string'),
 (2,NULL,'concatenated');
CREATE TABLE event (
	name	TEXT NOT NULL,
	type	TEXT CHECK(type IN ('educational','socal',NULL)),
	presenter	TEXT,
	organizer	TEXT,
	poster	TEXT,
	start_time	TEXT,
	end_time	TEXT,
	location	TEXT,
	budget	TEXT,
	Description	TEXT,
	PRIMARY KEY(name),
	FOREIGN KEY(organizer) REFERENCES exec(questid)  on update cascade on delete set null,
	FOREIGN KEY(poster) REFERENCES exec(questid) on update cascade on delete set null
);

INSERT INTO event (name,type,presenter,organizer,poster,start_time,end_time,location,budget,Description) VALUES ('BOT','social',NULL,'judith','judith','2015-01-28 19:00:00','2015-01-28 22:00:00','C & D','90.0','Come and play games with your fellow stats Club members. Food and snacks provided.'),
 ('EOT','social',NULL,'dominick','dominick',NULL,NULL,NULL,'160.0','End of Term social at a local Pub. A joint event with Act Sci.'),
 ('Intro to Hadoop','educational','darrell','darrell','gilberto','2015-03-25 14:30:00','2015-03-25 16:00:00','M3-3103','90.0','Hadoop is a distributed computing system designed to process information beyond the processing limitations to SQL databases: Tetrabyes, Petrabytes, etc. Hadoop and related tools are the data analysis platform used for "big data". Such companies that use Hadoop include: Facebook, Amazon, ebay, Google, LinkedIn, Twitter.'),
 ('Intro to SQL','educational','darrell','darrell','patrick','2015-02-05 18:00:00','2015-02-05 19:30:00','MC-3003','90.0','SQL is a relational database language and along with R and Python is among the most popular data analysis tools and one of the most in-demand skills for 2014.

The talk will touch on the theory behind relational databases and various ways of querying a SQL database with short quizzes in between. Food will be available to Stats Club Members ($2).'),
 ('Prof Talk','educational','Chong Zhang','patrick','dominick','2015-03-03 15:00:00','2015-03-03 16:00:00','M3-2134','90.0','Machine Learning and Data Mining: How We can Change the World'),
 ('Prof Talk 2','educational',NULL,'judith','judith',NULL,NULL,NULL,'90.0',NULL),
 ('Intro to SQL: Basic Queries','educational','darrell','darrell','darrell','2015-03-09 18:00:00','2015-03-09 19:30:00','MC-3003','100.0','SQL is a relational database language and along with R and Python is among the most popular data analysis tools and one of the most in-demand skills for 2014.

Hands-on practice with SQL. Please have SQLite Browser installed if you’re using your own laptop.

Available to Stats Club Members ($2 for membership for UW students).

Free food after presentation (MC Comfy).'),
 ('Intro to SQL: Advanced Queries','educational','darell','darrell','darrell','2015-03-12 18:00:00','2015-03-12 19:30:00','MC-3003','100.0','SQL is a relational database language and along with R and Python is among the most popular data analysis tools and one of the most in-demand skills for 2014.

Hands-on practice with SQL. Please have SQLite Browser installed if you’re using your own laptop.

Available to Stats Club Members ($2 for membership for UW students).

Free food after presentation (MC Comfy).');

CREATE TABLE expenses (
	event	TEXT,
	expense	TEXT,
	price	REAL,
	PRIMARY KEY(event,expense),
	FOREIGN KEY(event) REFERENCES event(name)  on update cascade on delete set null
);
INSERT INTO expenses (event,expense,price) VALUES ('Intro to SQL','pizza',87.43),
 ('Intro to SQL','pop',15.34),
 ('BOT','pop',13.23),
 ('BOT','pizza',45.34),
 ('EOT','pop',23.23),
 ('EOT','veggie platter',25.23),
 ('EOT','fries',21.21),
 ('Intro to Hadoop','coffee',23.12),
 ('Intro to Hadoop','water',10.23),
 ('Intro to Hadoop','donuts',53.23),
 ('Intro to Hadoop','cookies',10.23),
 ('Intro to SQL: Basic Queries','cookies',10.23),
 ('Intro to SQL: Basic Queries','donuts',20.34),
 ('Intro to SQL: Basic Queries','pop',21.54),
 ('Intro to SQL: Basic Queries','water',10.52),
 ('Prof Talk','pop',20.31),
 ('Prof Talk','pizza',62.56),
 ('Prof Talk 2','pizza',61.56),
 ('Prof Talk 2','pop',15.65),
 ('EOT','meals',90.98),
 ('Intro to SQL: Advanced Queries','cookies',10.23),
 ('Intro to SQL: Advanced Queries','donuts',20.34),
 ('Intro to SQL: Advanced Queries','pop',21.54),
 ('Intro to SQL: Advanced Queries','water',10.52);

CREATE TABLE attendance (
	member	TEXT,
	event	TEXT,
	PRIMARY KEY(member,event),
	FOREIGN KEY(member) REFERENCES member(quest_id)  on update cascade on delete cascade,
	FOREIGN KEY(event) REFERENCES event(name)  on update cascade on delete cascade
);
INSERT INTO attendance (member,event) VALUES ('fred','Intro to SQL'),
 ('billy','Intro to SQL'),
 ('step','Intro to SQL'),
 ('robert','Intro to SQL'),
 ('austin','Intro to SQL'),
 ('elaine','Intro to SQL'),
 ('daniel','Intro to SQL'),
 ('kelly','Intro to SQL'),
 ('joseph','Intro to SQL'),
 ('frances','Intro to SQL'),
 ('mina','Intro to SQL'),
 ('jeff','Intro to SQL'),
 ('deborah','Intro to SQL'),
 ('bobbie','Intro to SQL'),
 ('arnold','Intro to SQL'),
 ('melvin','Intro to SQL'),
 ('mildred','Intro to SQL'),
 ('tameika','Intro to SQL'),
 ('melissa','Intro to SQL'),
 ('ann','Intro to SQL'),
 ('ruben','Intro to SQL'),
 ('patrick','Intro to SQL'),
 ('patrick','BOT'),
 ('dominick','BOT'),
 ('ruben','BOT'),
 ('janelle','BOT'),
 ('tameika','BOT'),
 ('melvin','BOT'),
 ('joseph','BOT'),
 ('kelly','BOT'),
 ('frances','BOT'),
 ('daniel','BOT'),
 ('elaine','BOT'),
 ('austin','BOT'),
 ('austin','EOT'),
 ('fred','EOT'),
 ('ryan','EOT'),
 ('step','EOT'),
 ('robert','EOT'),
 ('james','EOT'),
 ('daniel','EOT'),
 ('kelly','EOT'),
 ('vivian','EOT'),
 ('mina','EOT'),
 ('jeff','EOT'),
 ('deborah','EOT'),
 ('nancy','EOT'),
 ('bobbie','EOT'),
 ('patrick','EOT'),
 ('dominick','EOT'),
 ('ruben','EOT'),
 ('judith','EOT'),
 ('fred','Intro to Hadoop'),
 ('patrick','Intro to Hadoop'),
 ('dominick','Intro to Hadoop'),
 ('judith','Intro to Hadoop'),
 ('ann','Intro to Hadoop'),
 ('melissa','Intro to Hadoop'),
 ('mildred','Intro to Hadoop'),
 ('ralph','Intro to Hadoop'),
 ('bobbie','Intro to Hadoop'),
 ('jeff','Intro to Hadoop'),
 ('frances','Intro to Hadoop'),
 ('joseph','Intro to Hadoop'),
 ('daniel','Intro to Hadoop'),
 ('elaine','Intro to Hadoop'),
 ('austin','Intro to Hadoop'),
 ('step','Intro to Hadoop'),
 ('billy','Intro to Hadoop'),
 ('fred','Intro to SQL: Basic Queries'),
 ('ryan','Intro to SQL: Basic Queries'),
 ('billy','Intro to SQL: Basic Queries'),
 ('step','Intro to SQL: Basic Queries'),
 ('robert','Intro to SQL: Basic Queries'),
 ('james','Intro to SQL: Basic Queries'),
 ('elaine','Intro to SQL: Basic Queries'),
 ('daniel','Intro to SQL: Basic Queries'),
 ('kelly','Intro to SQL: Basic Queries'),
 ('joseph','Intro to SQL: Basic Queries'),
 ('vivian','Intro to SQL: Basic Queries'),
 ('frances','Intro to SQL: Basic Queries'),
 ('phillip','Intro to SQL: Basic Queries'),
 ('deborah','Intro to SQL: Basic Queries'),
 ('nancy','Intro to SQL: Basic Queries'),
 ('arnold','Intro to SQL: Basic Queries'),
 ('ralph','Intro to SQL: Basic Queries'),
 ('mildred','Intro to SQL: Basic Queries'),
 ('tameika','Intro to SQL: Basic Queries'),
 ('janelle','Intro to SQL: Basic Queries'),
 ('judith','Intro to SQL: Basic Queries'),
 ('ruben','Intro to SQL: Basic Queries'),
 ('patrick','Intro to SQL: Basic Queries'),
 ('patrick','Prof Talk'),
 ('fred','Prof Talk'),
 ('ryan','Prof Talk'),
 ('billy','Prof Talk'),
 ('step','Prof Talk'),
 ('robert','Prof Talk'),
 ('elaine','Prof Talk'),
 ('daniel','Prof Talk'),
 ('joseph','Prof Talk'),
 ('frances','Prof Talk'),
 ('phillip','Prof Talk'),
 ('jeff','Prof Talk'),
 ('deborah','Prof Talk'),
 ('nancy','Prof Talk'),
 ('arnold','Prof Talk'),
 ('ralph','Prof Talk'),
 ('tameika','Prof Talk'),
 ('ann','Prof Talk'),
 ('judith','Prof Talk'),
 ('dominick','Prof Talk'),
 ('patrick','Prof Talk 2'),
 ('dominick','Prof Talk 2'),
 ('judith','Prof Talk 2'),
 ('janelle','Prof Talk 2'),
 ('tameika','Prof Talk 2'),
 ('ralph','Prof Talk 2'),
 ('arnold','Prof Talk 2'),
 ('nancy','Prof Talk 2'),
 ('deborah','Prof Talk 2'),
 ('phillip','Prof Talk 2'),
 ('vivian','Prof Talk 2'),
 ('daniel','Prof Talk 2'),
 ('elaine','Prof Talk 2'),
 ('robert','Prof Talk 2'),
 ('john','Prof Talk 2'),
 ('billy','Prof Talk 2'),
 ('fred','Intro to SQL: Advanced Queries'),
 ('ryan','Intro to SQL: Advanced Queries'),
 ('billy','Intro to SQL: Advanced Queries'),
 ('step','Intro to SQL: Advanced Queries'),
 ('robert','Intro to SQL: Advanced Queries'),
 ('james','Intro to SQL: Advanced Queries'),
 ('elaine','Intro to SQL: Advanced Queries'),
 ('daniel','Intro to SQL: Advanced Queries'),
 ('kelly','Intro to SQL: Advanced Queries'),
 ('joseph','Intro to SQL: Advanced Queries'),
 ('vivian','Intro to SQL: Advanced Queries'),
 ('frances','Intro to SQL: Advanced Queries'),
 ('phillip','Intro to SQL: Advanced Queries'),
 ('deborah','Intro to SQL: Advanced Queries'),
 ('nancy','Intro to SQL: Advanced Queries'),
 ('arnold','Intro to SQL: Advanced Queries'),
 ('ralph','Intro to SQL: Advanced Queries'),
 ('mildred','Intro to SQL: Advanced Queries'),
 ('tameika','Intro to SQL: Advanced Queries'),
 ('janelle','Intro to SQL: Advanced Queries'),
 ('judith','Intro to SQL: Advanced Queries'),
 ('ruben','Intro to SQL: Advanced Queries'),
 ('patrick','Intro to SQL: Advanced Queries');
COMMIT;