DROP TABLE IF EXISTS Bands;
DROP TABLE IF EXISTS Albums;
DROP TABLE IF EXISTS Rating;

PRAGMA foreign_keys = ON;

CREATE TABLE Bands (
  bandID	INTEGER,
  bandName	TEXT,
  numberOfMembers  INTEGER,
  formedYear	INTEGER,
  PRIMARY KEY (bandID)
);

CREATE TABLE Albums (
  albumID     INTEGER,
  albumName     TEXT,
  releaseDate     date,
  genres     TEXT,
  bandID      INTEGER,
  PRIMARY KEY (albumID),
  FOREIGN KEY (bandID) REFERENCES Bands
);

CREATE TABLE Rating (
  ratingID    INTEGER,
  albumID     INTEGER,
  numberOfVotes     INTEGER,
  averageRating     real,
  PRIMARY KEY (ratingID),
  FOREIGN KEY (albumID) REFERENCES Albums
);

-- Bands
INSERT INTO Bands VALUES (0, 'Pink Floyd', 5, 1965);
INSERT INTO Bands VALUES (1, 'Radiohead', 5, 1985);
INSERT INTO Bands VALUES (2, 'The Beatles', 9, 1960);
INSERT INTO Bands VALUES (3, 'Nirvana', 3, 1987);

-- Albums
INSERT INTO Albums VALUES (0, 'Ok Computer', '1997-06-16', 'Alternative Rock', 1);
INSERT INTO Albums VALUES (1, 'The Dark Side of the Moon', '1973-03-23', 'Art Rock', 0);
INSERT INTO Albums VALUES (2, 'Kid A', '2000-10-3', 'Art Rock', 1);
INSERT INTO Albums VALUES (3, 'Animals', '1977-01-21', 'Progressive Rock', 0);
INSERT INTO Albums VALUES (4, 'Wish You Were Here', '1975-09-12', 'Progressive Rock', 0);
INSERT INTO Albums VALUES (5, 'Revolver', '1966-08-05', 'Pop Rock', 2);
INSERT INTO Albums VALUES (6, 'Abbey Road', '1969-09-26', 'Pop Rock', 2);
INSERT INTO Albums VALUES (7, 'Never Mind', '1991-09-24', 'Alternative Rock', 3);
INSERT INTO Albums VALUES (8, 'In utero', '1993-09-21', 'Alternative Rock', 3);
INSERT INTO Albums VALUES (9, 'Roubber Soul', '1965-12-03', 'Pop Rock', 2);

-- Rating
INSERT INTO Rating VALUES (0, 0, 53675, 4.23);
INSERT INTO Rating VALUES (1, 1, 45634, 4.22);
INSERT INTO Rating VALUES (2, 2, 43720, 4.21);
INSERT INTO Rating VALUES (3, 3, 27722, 4.11);
INSERT INTO Rating VALUES (4, 4, 36908, 4.28);
INSERT INTO Rating VALUES (5, 5, 33953, 4.26);
INSERT INTO Rating VALUES (6, 6, 34328, 4.27);
INSERT INTO Rating VALUES (7, 7, 33701, 3.91);
INSERT INTO Rating VALUES (8, 8, 24989, 3.95);
INSERT INTO Rating VALUES (9, 9, 26486, 4.07);