--Initialize tables
DROP TABLE ema_house;
CREATE TABLE ema_house (address text, phone int, CONSTRAINT house_key PRIMARY KEY(address));

DROP TABLE ema_musicians
CREATE TABLE ema_musicians (ssn int, name text, address text, CONSTRAINT has_house
						 	FOREIGN KEY (address) REFERENCES ema_house);

DROP TABLE ema_instruments;
CREATE TABLE ema_instruments (name text, key char, CONSTRAINT inst_pri PRIMARY KEY (name, key));

DROP TABLE ema_plays;
CREATE TABLE ema_plays (ssn int, name text, key char, 
						CONSTARINT who FOREIGN KEY (ssn) REFERENCES ema_musicians,
						CONSTRAINT which_inst FOREIGN KEY (name, key) REFERENCES ema_instruments);

DROP TABLE ema_songs;
CREATE TABLE ema_songs (sid int, title text, author text, 
						CONSTRAINT songid PRIMARY KEY (sid);

DROP TABLE ema_performs;
CREATE TABLE ema_performs (ssn int, sid int,
						  CONSTARINT singer FOREIGN KEY (ssn) REFERENCES ema_musicians,
						  CONSTARINT sings FOREIGN KEY (sid) REFERENCES ema_songs);
DROP TABLE ema_albums;
CREATE TABLE ema_albums (aid int, copyw date, format text, title text, producer text,
						CONSTRAINT produced FOREIGN KEY (producer) REFERENCE ema_musicians);

DROP TABLE ema_is_on;
CREATE TABLE ema_is_on (aid int, sid int, CONSTRAINT track PRIMARY KEY(aid, sid));



--Populate relations

INSERT INTO ema_house(address, phone) VALUES ('Three45',7659397511);
INSERT INTO ema_house(address, phone) VALUES ('Plantation',7659390548);
INSERT INTO ema_house(address, phone) VALUES ('sw3rd',7659356292);

INSERT INTO ema_musicians(ssn, name, address) VALUES (111223333,'Tim','Three45');
INSERT INTO ema_musicians(ssn, name, address) VALUES (222334444,'Reid','Plantation');
INSERT INTO ema_musicians(ssn, name, address) VALUES (333445555,'Adam','sw3rd');
INSERT INTO ema_musicians(ssn, name, address) VALUES (444556666,'Alex','Plantation');

INSERT INTO ema_instruments(name, key) VALUES ('Bass',g);
INSERT INTO	ema_instruments(name, key) VALUES ('guitar', e);
INSERT INTO ema_instruments(name, key) VALUES ('trombone', c);

INSERT INTO ema_plays(ssn, name, key) VALUES (111223333, 'guitar', e);
INSERT INTO ema_plays(ssn, name, key) VALUES (222334444, 'Bass', g);
INSERT INTO ema_plays(ssn, name, key) VALUES (333445555, 'Bass', g);
INSERT INTO ema_plays(ssn, name, key) VALUES (444556666, 'trombone',c);

INSERT INTO ema_songs(sid, title, author) VALUES (001, 'i like ice cream', 'timix');
INSERT INTO ema_songs(sid, title, author) VALUES (002, 'my car', 'creedon');
INSERT INTO ema_songs(sid, title, author) VALUES (003, 'hoe's and dough', 'Big dog');
INSERT INTO ema_songs(sid, title, author) VALUES (004, 'life in the slowlane', 'Al Greene');

INSERT INTO ema_performs(ssn, sid) VALUES (111223333, 001);
INSERT INTO ema_performs(ssn, sid) VALUES (222334444, 002);
INSERT INTO ema_performs(ssn, sid) VALUES (333445555, 003); 
INSERT INTO ema_performs(ssn, sid) VALUES (444556666, 004);

INSERT INTO ema_albums(aid, copyw, format, title, producer) 
			   VALUES (1, 20020828, 'cd', 'earth's rightful ruler', 'Tim');
			   
INSERT INTO ema_albums(aid, copyw, format, title, producer)
			   VALUES (2, 20020101, 'lp', 'original rockers', 'Jah');
			   
INSERT INTO ema_is_on(aid, sid) VALUES (1,002); 
INSERT INTO ema_is_on(aid, sid) VALUES (1,004);
INSERT INTO ema_is_on(aid, sid) VALUES (2,001);  
INSERT INTO ema_is_on(aid, sid) VALUES (1,003);