-- 
-- Datenbank: GMCD
-- 

-- --------------------------------------------------------

-- create database GMCD CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- use GMCD;

-- 
-- Tabellenstruktur fuer Tabelle CD
-- 

DROP TABLE IF EXISTS CD;
CREATE TABLE IF NOT EXISTS CD (
ID int(10) unsigned NOT NULL auto_increment,
Datum date NOT NULL,
Beschreibung varchar(40) NOT NULL,
PRIMARY KEY  (ID),
KEY DatumIndex (Datum)
) ENGINE=InnoDB;

-- 
-- Daten fuer Tabelle CD
-- 

INSERT INTO CD (ID, Datum, Beschreibung) VALUES 
(1, '1968-01-13', 'Yellow Submarine'),
(2, '1975-01-24', 'The Koeln Concert'),
(3, '2009-09-04', 'Dresden');

-- --------------------------------------------------------

-- 
-- Tabellenstruktur fuer Tabelle CDStueck
-- 

DROP TABLE IF EXISTS CDStueck;
CREATE TABLE IF NOT EXISTS CDStueck (
CDID int(10) unsigned NOT NULL,
StueckID int(10) unsigned NOT NULL,
PRIMARY KEY  (CDID,StueckID)
) ENGINE=InnoDB;

-- 
-- Daten fuer Tabelle CDStueck
-- 

INSERT INTO CDStueck (CDID, StueckID) VALUES 
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(1, 6),
(2, 7),
(2, 8),
(2, 9),
(2, 10),
(3, 11),
(3, 12),
(3, 13),
(3, 14),
(3, 15),
(3, 16);

-- --------------------------------------------------------

-- 
-- Tabellenstruktur fuer Tabelle Instrument
-- 

DROP TABLE IF EXISTS Instrument;
CREATE TABLE IF NOT EXISTS Instrument (
ID int(10) unsigned NOT NULL auto_increment,
Typ varchar(20) NOT NULL,
Name varchar(20) NOT NULL,
PRIMARY KEY  (ID)
) ENGINE=InnoDB;

-- 
-- Daten fuer Tabelle Instrument
-- 

INSERT INTO Instrument (ID, Typ, Name) VALUES 
(1, 'Saiteninstrument', 'Gitarre'),
(2, 'Saiteninstrument', 'Bass'),
(3, 'Stimme', 'Gesang'),
(4, 'Schlagzeug', 'Schlagzeug'),
(5, 'Tasteninstrument', 'Klavier'),
(6, 'Blasinstrument', 'Saxophone');

-- --------------------------------------------------------

-- 
-- Tabellenstruktur fuer Tabelle Musiker
-- 

DROP TABLE IF EXISTS Musiker;
CREATE TABLE IF NOT EXISTS Musiker (
ID int(10) unsigned NOT NULL auto_increment,
Vorname varchar(20) NOT NULL,
Name varchar(20) NOT NULL,
PRIMARY KEY  (ID),
KEY MusikerName (Name(4))
) ENGINE=InnoDB;

-- 
-- Daten fuer Tabelle Musiker
-- 

INSERT INTO Musiker (ID, Vorname, Name) VALUES 
(1, 'George', 'Harrison'),
(2, 'John', 'Lennon'),
(3, 'Paul', 'McCartney'),
(4, 'Ringo', 'Starr'),
(5, 'Keith', 'Jarrett'),
(6, 'Jan', 'Garbarek');

-- --------------------------------------------------------

-- 
-- Tabellenstruktur fuer Tabelle MusikerInstrument
-- 

DROP TABLE IF EXISTS MusikerInstrument;
CREATE TABLE IF NOT EXISTS MusikerInstrument (
MusikerID int(10) unsigned NOT NULL,
InstrumentID int(10) unsigned NOT NULL,
PRIMARY KEY  (MusikerID,InstrumentID)
) ENGINE=InnoDB;

-- 
-- Daten fuer Tabelle MusikerInstrument
-- 

INSERT INTO MusikerInstrument (MusikerID, InstrumentID) VALUES 
(1, 1),
(2, 1),
(3, 2),
(4, 4),
(1, 3),
(2, 3),
(3, 3),
(4, 3),
(5, 5),
(6, 6);

-- --------------------------------------------------------

-- 
-- Tabellenstruktur fuer Tabelle Stueck
-- 

DROP TABLE IF EXISTS Stueck;
CREATE TABLE IF NOT EXISTS Stueck (
ID int(10) unsigned NOT NULL auto_increment,
Titel varchar(40) NOT NULL,
AufnahmeDatum date NOT NULL,
Dauer int(10) unsigned NOT NULL COMMENT '[Min]',
PRIMARY KEY (ID)
) ENGINE=InnoDB;

-- 
-- Daten fuer Tabelle Stueck
-- 

INSERT INTO Stueck (ID, Titel, AufnahmeDatum, Dauer) VALUES 
(1, 'Yellow Submarine', '1968-01-13', 140),
(2, 'Only a Northern Song', '1968-01-13', 300),
(3, 'All Together Now', '1968-01-13', 250),
(4, 'Hey Bulldog', '1968-01-13', 450),
(5, 'Its All Too Much', '1968-01-13', 140),
(6, 'All You Need Is Love', '1968-01-13', 98),
(7, 'Part I', '1975-01-24', 45),
(8, 'Part II a', '1975-01-24', 78),
(9, 'Part II b' , '1975-01-24', 450),
(10, 'Part II c' , '1975-01-24', 370),
(11, 'Paper Nut', '2009-09-04', 45),
(12, 'The Tall Tear Trees', '2009-09-04', 45),
(13, 'Heitor', '2009-09-04', 45),
(14, 'Twelve Moons', '2009-09-04', 45),
(15, 'Rondo Amoroso', '2009-09-04', 45),
(16, 'Milagre Dos Peixes', '2009-09-04', 45);

-- --------------------------------------------------------

-- 
-- Tabellenstruktur fuer Tabelle StueckMusiker
-- 

DROP TABLE IF EXISTS StueckMusiker;
CREATE TABLE IF NOT EXISTS StueckMusiker (
StueckID int(10) unsigned NOT NULL,
MusikerID int(10) unsigned NOT NULL,
PRIMARY KEY  (StueckID,MusikerID)
) ENGINE=InnoDB;

-- 
-- Daten fuer Tabelle StueckMusiker
-- 

INSERT INTO StueckMusiker (StueckID, MusikerID) VALUES 
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 1),
(1, 2),
(2, 2),
(3, 2),
(4, 2),
(5, 2),
(6, 2),
(1, 3),
(2, 3),
(3, 3),
(4, 3),
(5, 3),
(6, 3),
(1, 4),
(2, 4),
(3, 4),
(4, 4),
(5, 4),
(6, 4),
(7, 5),
(8, 5),
(9, 5),
(10, 5),
(11, 6),
(12, 6),
(13, 6),
(14, 6),
(15, 6),
(16, 6);

