Klausur A, Datenbanksysteme SS 2012

--
-- Tabellenstruktur für Tabelle `Cup`
--

CREATE TABLE IF NOT EXISTS `Cup` (
  `cup_id` INT(11) NOT NULL AUTO_INCREMENT,
  `cup_name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`cup_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Daten für Tabelle `Cup`
--

INSERT INTO `Cup` (`cup_id`, `cup_name`) VALUES
(1, 'EM 2008'),
(2, 'WM 2010'),
(3, 'EM 2012');

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

--
-- Stellvertreter-Struktur des Views `GoalsPerPlayer`
--
CREATE TABLE IF NOT EXISTS `GoalsPerPlayer` (
`player_id` INT(11)
,`name` VARCHAR(255)
,`team` VARCHAR(3)
,`sum_goals` DECIMAL(32,0)
);
-- --------------------------------------------------------

--
-- Stellvertreter-Struktur des Views `GoalsPerTeam`
--
CREATE TABLE IF NOT EXISTS `GoalsPerTeam` (
`team` VARCHAR(3)
,`sum_goals` DECIMAL(54,0)
);
-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `Participation`
--

CREATE TABLE IF NOT EXISTS `Participation` (
  `player_id` INT(11) DEFAULT NULL,
  `cup_id` INT(11) DEFAULT NULL,
  `goals` INT(11) DEFAULT NULL,
  KEY `player_id` (`player_id`),
  KEY `cup_id` (`cup_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Daten für Tabelle `Participation`
--

INSERT INTO `Participation` (`player_id`, `cup_id`, `goals`) VALUES
(1, 1, 1),
(1, 2, 0),
(1, 3, 1),
(2, 1, 2),
(2, 2, 4),
(2, 3, 1),
(4, 1, 4),
(4, 2, 5),
(5, 1, 2);

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

--
-- Tabellenstruktur für Tabelle `Player`
--

CREATE TABLE IF NOT EXISTS `Player` (
  `player_id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `team` VARCHAR(3) NOT NULL,
  PRIMARY KEY (`player_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Daten für Tabelle `Player`
--

INSERT INTO `Player` (`player_id`, `name`, `team`) VALUES
(1, 'Lahm', 'GER'),
(2, 'Klose', 'GER'),
(3, 'Odonkor', 'GER'),
(4, 'Villa', 'SPA'),
(5, 'Van Nistelrooy', 'NED');

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

--
-- Struktur des Views `GoalsPerPlayer`
--
DROP TABLE IF EXISTS `GoalsPerPlayer`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `GoalsPerPlayer` AS (SELECT `p1`.`player_id` AS `player_id`,`p1`.`name` AS `name`,`p1`.`team` AS `team`,SUM(`pa`.`goals`) AS `sum_goals` FROM (`Player` `p1` LEFT JOIN `Participation` `pa` ON((`p1`.`player_id` = `pa`.`player_id`))) GROUP BY `p1`.`player_id`,`p1`.`name`,`p1`.`team`);

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

--
-- Struktur des Views `GoalsPerTeam`
--
DROP TABLE IF EXISTS `GoalsPerTeam`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `GoalsPerTeam` AS (SELECT `GoalsPerPlayer`.`team` AS `team`,SUM(`GoalsPerPlayer`.`sum_goals`) AS `sum_goals` FROM `GoalsPerPlayer` GROUP BY `GoalsPerPlayer`.`team`);

--
-- Constraints der exportierten Tabellen
--

--
-- Constraints der Tabelle `Participation`
--
ALTER TABLE `Participation`
  ADD CONSTRAINT `Participation_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `Player` (`player_id`),
  ADD CONSTRAINT `Participation_ibfk_2` FOREIGN KEY (`cup_id`) REFERENCES `Cup` (`cup_id`);