Example schema for CSC343S (2014) Assignment 1.

sportsball.sql

CREATE TABLE Team (
	country TEXT PRIMARY KEY,
	coach TEXT UNIQUE
);

CREATE TABLE Player (
	PID SERIAL PRIMARY KEY,
	fname TEXT,
	lname TEXT,
	position TEXT,
	goals INTEGER,
	country TEXT REFERENCES Team
);

CREATE TABLE Stadium (
	SID SERIAL PRIMARY KEY,
	capacity INTEGER,
	city TEXT
);

CREATE TABLE Match (
	MID SERIAL PRIMARY KEY,
	date DATE,
	time TIME,
	SID SERIAL REFERENCES Stadium
);

CREATE TABLE Ticket (
	TID SERIAL PRIMARY KEY,
	dateIssued DATE,
	timeIssued TIME,
	MID SERIAL REFERENCES Match
);

CREATE TABLE Competes (
	MID SERIAL REFERENCES Match,
	country1 TEXT REFERENCES Team,
	country2 TEXT REFERENCES Team,
	goals1 INTEGER,
	goals2 INTEGER
);

INSERT INTO Team VALUES('Canada', 'Harper');
INSERT INTO Team VALUES('USA', 'Obama');
INSERT INTO Team VALUES('England', 'Elizabeth');

INSERT INTO Player(fname, lname, position, goals, country) VALUES('Eh', 'One', 'Left', 4, 'Canada');
INSERT INTO Player(fname, lname, position, goals, country) VALUES('Bee', 'Two', 'Right', 8, 'Canada');
INSERT INTO Player(fname, lname, position, goals, country) VALUES('See', 'Three', 'Left', 1, 'USA');
INSERT INTO Player(fname, lname, position, goals, country) VALUES('Dee', 'Four', 'Right', 7, 'USA');
INSERT INTO Player(fname, lname, position, goals, country) VALUES('Eff', 'Five', 'Left', 5, 'England');
INSERT INTO Player(fname, lname, position, goals, country) VALUES('Ach', 'Six', 'Right', 0, 'England');

INSERT INTO Stadium(capacity, city) VALUES(10000, 'Toronto');
INSERT INTO Stadium(capacity, city) VALUES(12000, 'New York');
INSERT INTO Stadium(capacity, city) VALUES(9000, 'London');

INSERT INTO Match(date, time, SID) VALUES ('2014-01-08', '15:00', (SELECT SID FROM Stadium WHERE city = 'Toronto'));
INSERT INTO Match(date, time, SID) VALUES ('2014-01-12', '15:00', (SELECT SID FROM Stadium WHERE city = 'Toronto'));
INSERT INTO Match(date, time, SID) VALUES ('2014-01-16', '17:00', (SELECT SID FROM Stadium WHERE city = 'New York'));
INSERT INTO Match(date, time, SID) VALUES ('2014-01-18', '17:00', (SELECT SID FROM Stadium WHERE city = 'New York'));
INSERT INTO Match(date, time, SID) VALUES ('2014-01-20', '12:00', (SELECT SID FROM Stadium WHERE city = 'London'));
INSERT INTO Match(date, time, SID) VALUES ('2014-01-22', '12:00', (SELECT SID FROM Stadium WHERE city = 'London'));

INSERT INTO Ticket(dateIssued, timeIssued, MID) VALUES('2014-01-07', '11:00', (SELECT MID FROM Match WHERE date = '2014-01-08'));
INSERT INTO Ticket(dateIssued, timeIssued, MID) VALUES('2014-01-08', '9:00', (SELECT MID FROM Match WHERE date = '2014-01-08'));
INSERT INTO Ticket(dateIssued, timeIssued, MID) VALUES('2014-01-11', '10:30', (SELECT MID FROM Match WHERE date = '2014-01-12'));
INSERT INTO Ticket(dateIssued, timeIssued, MID) VALUES('2014-01-12', '12:30', (SELECT MID FROM Match WHERE date = '2014-01-12'));
INSERT INTO Ticket(dateIssued, timeIssued, MID) VALUES('2014-01-15', '19:45', (SELECT MID FROM Match WHERE date = '2014-01-16'));
INSERT INTO Ticket(dateIssued, timeIssued, MID) VALUES('2014-01-14', '13:15', (SELECT MID FROM Match WHERE date = '2014-01-16'));
INSERT INTO Ticket(dateIssued, timeIssued, MID) VALUES('2014-01-15', '14:35', (SELECT MID FROM Match WHERE date = '2014-01-18'));
INSERT INTO Ticket(dateIssued, timeIssued, MID) VALUES('2014-01-16', '10:15', (SELECT MID FROM Match WHERE date = '2014-01-18'));
INSERT INTO Ticket(dateIssued, timeIssued, MID) VALUES('2014-01-17', '21:55', (SELECT MID FROM Match WHERE date = '2014-01-20'));
INSERT INTO Ticket(dateIssued, timeIssued, MID) VALUES('2014-01-18', '7:10', (SELECT MID FROM Match WHERE date = '2014-01-20'));
INSERT INTO Ticket(dateIssued, timeIssued, MID) VALUES('2014-01-19', '23:10', (SELECT MID FROM Match WHERE date = '2014-01-22'));
INSERT INTO Ticket(dateIssued, timeIssued, MID) VALUES('2014-01-20', '18:20', (SELECT MID FROM Match WHERE date = '2014-01-22'));

INSERT INTO Competes(MID, country1, country2, goals1, goals2) VALUES((SELECT MID FROM Match WHERE date = '2014-01-08'), 'Canada', 'USA', 3, 2);
INSERT INTO Competes(MID, country1, country2, goals1, goals2) VALUES((SELECT MID FROM Match WHERE date = '2014-01-12'), 'USA', 'Canada', 2, 5);
INSERT INTO Competes(MID, country1, country2, goals1, goals2) VALUES((SELECT MID FROM Match WHERE date = '2014-01-16'), 'Canada', 'England', 2, 1);
INSERT INTO Competes(MID, country1, country2, goals1, goals2) VALUES((SELECT MID FROM Match WHERE date = '2014-01-18'), 'Canada', 'England', 2, 2);
INSERT INTO Competes(MID, country1, country2, goals1, goals2) VALUES((SELECT MID FROM Match WHERE date = '2014-01-20'), 'USA', 'England', 4, 1);
INSERT INTO Competes(MID, country1, country2, goals1, goals2) VALUES((SELECT MID FROM Match WHERE date = '2014-01-22'), 'England', 'Canada', 1, 4);