CREATE DATABASE 0906012880_Lokaverkefni;

CREATE TABLE notandi
(
	ID INTEGER AUTO_INCREMENT PRIMARY KEY,
    nafn VARCHAR(55),
    faedingadagur DATE,
    tolvupostfang VARCHAR(50),
    heimili VARCHAR (40)
);

CREATE TABLE flytjandi
(
	ID INTEGER AUTO_INCREMENT PRIMARY KEY,
    nafn VARCHAR(55),
    stofndagur YEAR,
    lokadagur DATE,
    lysing VARCHAR(30)
);

CREATE TABLE utgefandi
(
	ID INTEGER AUTO_INCREMENT PRIMARY KEY,
    nafn VARCHAR(55),
    netfang VARCHAR(30),
    stadsetning VARCHAR(30)
);

CREATE TABLE tegund
(
	ID INTEGER AUTO_INCREMENT PRIMARY KEY,
    nafn VARCHAR(30)
);

CREATE TABLE lagalisti
(
	ID INTEGER AUTO_INCREMENT PRIMARY KEY,
    nafn VARCHAR(30),
    dags_stofnadur DATE,
    notandi_id INT(11),
    FOREIGN KEY(notandi_id) REFERENCES notandi(ID)
);

CREATE TABLE lag
(
	ID INTEGER AUTO_INCREMENT PRIMARY KEY,
    heiti VARCHAR(30),
    lengd DECIMAL(5,2),
    texti text(10000),
    utgafudagur DATE,
    flytjandi_id INT,
    utgefandi_id INT,
    tegund_id INT,
    FOREIGN KEY(flytjandi_id) REFERENCES flytjandi(ID),
    FOREIGN KEY(utgefandi_id) REFERENCES utgefandi(ID),
    FOREIGN KEY(tegund_id) REFERENCES tegund(ID)
);


CREATE TABLE listalog
(
	ID INTEGER AUTO_INCREMENT PRIMARY KEY,
    rod INT,
    lag_id INT,
    lagalisti_id INT,
    FOREIGN KEY(lag_id) REFERENCES lag(ID),
    FOREIGN KEY(lagalisti_id) REFERENCES lagalisti(ID)
);

INSERT INTO flytjandi(nafn, stofndagur, lokadagur, lysing)
VALUES
("Snail Mail", 2015, NULL, "Söngkona"),
("Courtney Barnett", 2009, NULL, "Söngkona"),
("Anderson .Paak", 2009, NULL, "Söngvari"),
("Leon Bridges", 2014, NULL, "Söngvari"),
("Kacey Musgraves", 2006, NULL, "Söngkona"),
("Janelle Monae", 2003, NULL, "Söngkona"),
("Superchunk", 1989, NULL, "Hljómsveit"),
("Sob x Rbe", 2017, NULL, "Hópur"),
("U.S. Girls", 2007, NULL, "Hljómsveit"),
("JPEGMafia", 2015, NULL, "Söngvari");

INSERT INTO notandi(nafn, faedingadagur, tolvupostfang, heimili)
VALUES
("Jón Jónsson", '1997-10-12', "jonjonsson@gmail.com", "Kolbeinsmýri 12"),
("Sindri Einarsson", '2002-06-15', "sindri.einars@gmail.com", "Háaleitisbraut 147"),
("Jóna Geirsdóttir", '1989-11-27', "jonag@gmail.com", "Bólstaðarhlíð 26"),
("Steinunn Gunnarsdóttir", '1999-05-19', "sgunn@gmail.com", "Auðarstræti 15"),
("Daníel Bragason", '2001-06-04', "danniellbje@gmail.com", "Granaskjól 4"),
("Davíð Guðmundsson", '1991-07-14', "davidgud@gmail.com", "Fjölnisvegur 12"),
("Daði Pálsson", '1972-02-14', "dadip@gmail.com", "Laufásvegur 7");