databaseSH

USE master;
DROP DATABASE Studenterhuset;
CREATE DATABASE Studenterhuset;

GO

USE Studenterhuset;

CREATE TABLE Performers(
    code VARCHAR(4),
    name VARCHAR(50) NOT NULL,
    genre VARCHAR(50) NOT NULL,
    [description] VARCHAR(50),
    nationality VARCHAR(50),
    link VARCHAR(255),
    PRIMARY KEY (code)
);

CREATE TABLE Zip(
    zipCode VARCHAR(50),
    city VARCHAR(50) NOT NULL,
    PRIMARY KEY (zipCode)
);

CREATE TABLE Place(
    id INT,
    name VARCHAR(50) NOT NULL,
    street VARCHAR(100),
    NUMBER INT,
    country VARCHAR(50),
    zipCode VARCHAR(50),
    PRIMARY KEY (id),
    FOREIGN KEY (zipCode) REFERENCES Zip(zipCode)
        ON UPDATE cascade
        ON DELETE cascade
);

CREATE TABLE Sponsors(
    code VARCHAR(4),
    name VARCHAR(50) NOT NULL,
    [description] VARCHAR(255),
    voucherCode VARCHAR(4),
    PRIMARY KEY (code),
);

CREATE TABLE Vouchers(
    code VARCHAR(4),
    name VARCHAR(50) NOT NULL,
    [description] VARCHAR(255),
    price INT NOT NULL,
    VALUE VARCHAR(50),
    overAge VARCHAR(10),
    sponsorCode VARCHAR(4),
    PRIMARY KEY (code),
    FOREIGN KEY (sponsorCode) REFERENCES Sponsors(code)
        ON UPDATE cascade
        ON DELETE cascade
);

CREATE TABLE Poll(
    id INT,
    genreA VARCHAR(50) NOT NULL,
    voteA INT NOT NULL,
    genreB VARCHAR(50) NOT NULL,
    voteB INT NOT NULL,
    RESULT VARCHAR(50),
    PRIMARY KEY (id)
);

CREATE TABLE [Event](
    id INT,
    name VARCHAR(50) NOT NULL,
    [description] VARCHAR(255) NOT NULL,
    category VARCHAR(50),
    price INT NOT NULL,
    [DATE] DATE NOT NULL,
    [FROM] VARCHAR(5),
    [TO] VARCHAR(5),
    placeId INT,
    vouchersCode VARCHAR(4),
    pollId INT,
    PRIMARY KEY (id),
    FOREIGN KEY (vouchersCode) REFERENCES Vouchers (code)
        ON UPDATE cascade
        ON DELETE cascade,
    FOREIGN KEY (pollId) REFERENCES Poll (id)
        ON UPDATE cascade
        ON DELETE cascade,
);

CREATE TABLE EventPerformers(
    eventId INT,
    performersCode VARCHAR(4),
    FOREIGN KEY (eventId) REFERENCES [Event] (id)
        ON UPDATE cascade
        ON DELETE cascade,
    FOREIGN KEY (performersCode) REFERENCES Performers(code)
        ON UPDATE cascade
        ON DELETE cascade
);

CREATE TABLE EventSponsors(
    eventId INT,
    sponsorsCode VARCHAR(4),
    FOREIGN KEY (eventId) REFERENCES [Event] (id)
        ON UPDATE cascade
        ON DELETE cascade,
    FOREIGN KEY (sponsorsCode) REFERENCES Sponsors(code)
        ON UPDATE no action
        ON DELETE no action
);

CREATE TABLE Customer(
    id INT,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(50),
    age INT NOT NULL,
    studies VARCHAR(50),
    nationality VARCHAR(50),
    phoneNo VARCHAR(50),
    email VARCHAR(50),
    PRIMARY KEY(id)
);

CREATE TABLE EventCustomer(
    eventId INT,
    customerId INT,
    FOREIGN KEY (eventId) REFERENCES [Event] (id)
        ON UPDATE cascade
        ON DELETE cascade,
    FOREIGN KEY (customerId) REFERENCES Customer(id)
        ON UPDATE no action
        ON DELETE no action
);


INSERT INTO Performers VALUES ('TTD', 'Taste The Doom', 'Doom Metal', 'as the Doom pairs seven drams of small-batch and single-malt whiskies with doom metal tracks about 15 minutes long', '', 'http://tastethedoom.com/');
INSERT INTO Performers VALUES ('AA', 'Abdu Ali', 'Mixt', 'In terms of genre, Ali’s take on baltimore club contains elements as diverse as noise rap, afro futurism, punk and ballroom. But most of all it’s energy. A wild, unapologetic energy that will in no way be limited by genres, rules or opinions.', ' Baltimore, USA', 'http://abduali.com/');
INSERT INTO Performers VALUES ('IRAH', 'IRAH', 'Rock', 'IRAH makes alternative pop music inspired by both traditional ceremony music to modern melodic songwriting. It is a real magic hearing them all together performing.', ' Baltimore, USA', 'http://irahmusic.com/');
INSERT INTO Performers VALUES ('PK', 'Pligten Kalder', 'Rock', 'Pligten Kalder is a Danish music trio consisting of journalist Torben Steno (elastikoptrukket optigan organ), rock singer Johan Olsen known from Power Corridors (vocals) and saxisten Peter Bech-Jessen.', ' DK', 'http://www.pligtenkalder.dk/');
INSERT INTO Performers VALUES ('TRSF', 'The Rumour Said Fire', 'Folk/Indie', 'The Rumor Said Fire is a Danish music group, formed in 2008 by singer, songwriter and guitarist Jesper Lidang.', ' DK', 'http://therumoursaidfire.com/');
INSERT INTO Performers VALUES ('CMS', 'Communions', 'Rock', 'Communions entered the Danish music scene, and in the same breath entered the critics by storm. The quartet won with their first two epics and intense live shows quickly the hearts of the audience.', ' DK', 'http://www.communionsband.com/');
INSERT INTO Performers VALUES ('FH', 'First Hate', 'Indie', 'Danish First Hate is ready for the first visit to the Student House with a universe of lavish syntheses and intense melodies.', ' DK', 'http://www.firsthate.com/');
INSERT INTO Performers VALUES ('FD', 'Fribytterdrømme', 'Universal', 'Danish Band.', ' Baltimore, DK ', 'https://fribytterdroemme.lnk.to/Superego');
INSERT INTO Performers VALUES ('SPP', 'Sleepy Party People', 'Dream pop/Slowcore', 'Sleep Party People is the alias of musician Brian Batz, hailing from Denmark.', ' DK', 'https://www.facebook.com/SLEEPPARTYPEOPLE/');

INSERT INTO Zip VALUES ('9000', 'Aalborg');
INSERT INTO Zip VALUES ('9200', 'Aalborg SV');
INSERT INTO Zip VALUES ('9100', 'Aalborg');
INSERT INTO Zip VALUES ('9220', 'Aalborg Øst');
INSERT INTO Zip VALUES ('9210', 'Aalborg SØ');

INSERT INTO Place VALUES (1, 'Studenterhuset', 'Square SH', 3, 'Denmark', '9000');
INSERT INTO Place VALUES (2, 'Studenterhuset2', 'Square Music', 5, 'Denmark', '9000');
INSERT INTO Place VALUES (3, 'Aalborg Bibliotekerne', 'Rendsburggade ', 2 , 'Denmark', '9000');
INSERT INTO Place VALUES (4, 'Bilka', 'Hobrovej', 450, 'Denmark', '9200');


INSERT INTO Sponsors VALUES ('TBRG', 'Tuborg', 'Tuborg is typical and cheep beer', NULL);
INSERT INTO Sponsors VALUES ('COKE', 'Coca-Cola', 'Buy Happiness', NULL);
INSERT INTO Sponsors VALUES ('Studiecykel', 'Studiecykel.dk', 'Gratie Studiecykel? Timeld dig her!', NULL);
INSERT INTO Sponsors VALUES ('RB', 'Red Bull', 'Giving people wings', NULL);
INSERT INTO Sponsors VALUES ('SNPS', 'Snaps', 'Aalborg local drink', NULL);



INSERT INTO Vouchers VALUES ('2x1B', '2x1 beer', '2 beers for the price of 1', 25, '50%', 'yes', 'TBRG');
INSERT INTO Vouchers VALUES ('75%E', '75% Entrance', '75% Entrance using mobile app', 60, '75%', 'no', NULL);

INSERT INTO Poll VALUES (1, 'Vocal', 1, 'Rock', 2, 'Dance Music');
INSERT INTO Poll VALUES (2, 'Classical Music', 2, 'Reggaeton', 1, 'Vocal');
INSERT INTO Poll VALUES (3, 'Indie Rock', 2, 'Pop', 3, 'Alternative Rock, Slowcore');
INSERT INTO Poll VALUES (3, 'Alternative Music', 2, 'Electronic Music', 3, 'Metal');


INSERT INTO [Event] VALUES (1, 'TASTE THE DOOM', 'Doom metal and malt whisky', 'Music Concert', 210, '2017-06-03','21:00', '23:59', 1, '75%E', 1);
INSERT INTO [Event] VALUES (2, 'JUVENTUS VS REAL MADRID', 'Showing the grand final match on a big screen in our Café. May the best win.', 'Entertainment', 0, '2017-06-03', '20:45', '23:45', 1, '2x1B', 2);
INSERT INTO [Event] VALUES (3, 'ABDU ALI', 'Abdu Ali does club music with elements as diverse rap sounds, afro futurism, punk and the balroom. A wild, unapologetic energy that will in no way be limited by genres, rules or opinions.', 'Music Concert', 90, '2017-06-10', '21:00', '23:59', 1, '2x1B', 1);
INSERT INTO [Event] VALUES (4, 'IRAH', 'IRAH makes alternative pop music inspired by both traditional ceremony music to modern melodic songwriting. It is a real magic hearing them all together performing.', 'Music Concert', 85, '2017-09-28','21:00', '23:59', 1, '75%E', 2);
INSERT INTO [Event] VALUES (5, 'PLIGTEN KALDER', 'Pligten Kalder present original and fearful Danish music. Duty Kalder makes serious and black songs with subtle underflows, but you do not have to be sad and you have fun while you are in.', 'Music Concert', 135, '2017-10-05','21:00', '23:59', 1, '75%E', 2);
INSERT INTO [Event] VALUES (6, 'THE RUMOUR SAID FIRE', '+ Support', 'Music Concert', 135, '2017-10-07','21:00', '23:59', 1, '2x1B', 1);
INSERT INTO [Event] VALUES (7, 'COMMUNIONS + FIRST HATE', 'Communions delivers intense and almost disturbing rock, inspired by British legends like Happy Mondays and The Stone Roses.', 'Music Concert', 85, '2017-10-12','21:00', '23:59', 1, '2x1B', 1);
INSERT INTO [Event] VALUES (8, 'Fribytterdrømme', 'Expected fans can really enjoy experiencing one of Denmarks most promising and talented music names when the mentioned psych group wakes the songs from their most notorious second album to live in venues across the country + SUPPORT.', 'Music Concert', 85, '2017-10-13','21:00', '23:59', 1, '2x1B', 1);
INSERT INTO [Event] VALUES (9, 'VELVET VOLUME', 'Three young and talented musicians with their amazing music.', 'Music Concert', 155, '2017-10-14','21:00', '23:59', 1, '75%E', 1);
INSERT INTO [Event] VALUES (10, 'SLEEP PARTY PEOPLE', 'Brian Batz and his five-man live band are performing on stage wearing the characteristic rabbit masks in early December.', 'Music Concert', 100, '2017-12-02','21:00', '23:59', 1, '2x1B', 1);





INSERT INTO EventPerformers VALUES (1,'TTD');
INSERT INTO EventPerformers VALUES (2,'AA');
INSERT INTO EventPerformers VALUES (3,'IRAH');
INSERT INTO EventPerformers VALUES (4,'PK');
INSERT INTO EventPerformers VALUES (5,'TRSF');
INSERT INTO EventPerformers VALUES (6,'CMS');
INSERT INTO EventPerformers VALUES (7,'FH');
INSERT INTO EventPerformers VALUES (8,'FD');
INSERT INTO EventPerformers VALUES (9,'SPP');


INSERT INTO EventSponsors VALUES (1,'TBRG');
INSERT INTO EventSponsors VALUES (2,'COKE');
INSERT INTO EventSponsors VALUES (3,'Studiecykel');
INSERT INTO EventSponsors VALUES (4,'RB');
INSERT INTO EventSponsors VALUES (5, 'SNPS');


INSERT INTO Customer VALUES (1, 'Ignasi', 'M', 22, 'Computer Science', 'ES', '+34123456789', 'ignasi@mail.com');
INSERT INTO Customer VALUES (2, 'David', 'M', 22, 'Computer Science', 'ES', '+34123456789', 'david@mail.com');
INSERT INTO Customer VALUES (3, 'Libor', 'M', 20, 'Computer Science', 'SLK', '+34123456789', 'libor@mail.com');
INSERT INTO Customer VALUES (4, 'Ionut', 'M', 19, 'Computer Science', 'RO', '+40729073831', 'ionut@mail.com');
INSERT INTO Customer VALUES (5, 'Søren Kierkegaard', 'M', 42, 'Philosophy', 'DK ', '+4550654839', 'Kierkegaard@gmail.com');
INSERT INTO Customer VALUES (6, 'Bjarne Stroustrup', 'M', 66, 'Computer Science/Engineering', 'DK ', '+4550239476', 'bjarne@stroustrup.com');
INSERT INTO Customer VALUES (7, 'Emily Ratajkowski', 'F', 25, 'Acting', 'UK ', '+15417543010', 'Emily@ratajkowski.com');



INSERT INTO EventCustomer VALUES (1,1);
INSERT INTO EventCustomer VALUES (1,2);
INSERT INTO EventCustomer VALUES (1,4);
INSERT INTO EventCustomer VALUES (2,1);
INSERT INTO EventCustomer VALUES (2,3);
INSERT INTO EventCustomer VALUES (2,4);
INSERT INTO EventCustomer VALUES (3, 1);
INSERT INTO EventCustomer VALUES (3, 2);
INSERT INTO EventCustomer VALUES (4, 1);
INSERT INTO EventCustomer VALUES (4, 2);
INSERT INTO EventCustomer VALUES (5, 1);
INSERT INTO EventCustomer VALUES (5, 2);
INSERT INTO EventCustomer VALUES (6, 1);
INSERT INTO EventCustomer VALUES (6, 2);
INSERT INTO EventCustomer VALUES (7, 1);
INSERT INTO EventCustomer VALUES (7, 2);