SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;


CREATE TABLE IF NOT EXISTS `kierunki` (
  `id_kierunku` int(11) NOT NULL,
  `nazwa_kierunku` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  PRIMARY KEY (`id_kierunku`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

INSERT INTO `kierunki` (`id_kierunku`, `nazwa_kierunku`) VALUES
(1, 'dietetyka'),
(2, 'pedagogika'),
(3, 'historia'),
(4, 'politologia'),
(5, 'chemia'),
(6, 'matematyka'),
(7, 'informatyka'),
(8, 'biologia'),
(9, 'rolnictwo'),
(10, 'administracja'),
(11, 'zarządzanie'),
(12, 'logistyka');

CREATE TABLE IF NOT EXISTS `przyznane_stypendia` (
  `id_przyznane_stypendia` int(11) NOT NULL,
  `id_studenta` int(11) DEFAULT NULL,
  `id_stypendium` int(11) DEFAULT NULL,
  `wysokosc_stypendium` varchar(45) COLLATE utf8_polish_ci NOT NULL,
  PRIMARY KEY (`id_przyznane_stypendia`),
  KEY `id_stypendium` (`id_stypendium`),
  KEY `id_studenta` (`id_studenta`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

INSERT INTO `przyznane_stypendia` (`id_przyznane_stypendia`, `id_studenta`, `id_stypendium`, `wysokosc_stypendium`) VALUES
(1, 12, 2, '400'),
(2, 2, 3, '120'),
(3, 3, 1, '200'),
(4, 3, 2, '350'),
(5, 4, 4, '500'),
(6, 5, 3, '190'),
(7, 5, 1, '150'),
(8, 5, 2, '300'),
(9, 7, 2, '450'),
(10, 8, 2, '660'),
(11, 9, 1, '120'),
(12, 9, 2, '300'),
(13, 10, 3, '100'),
(14, 11, 4, '200'),
(15, 1, 2, '550'),
(16, 14, 2, '450'),
(17, 14, 1, '100'),
(18, 15, 1, '200'),
(19, 15, 2, '600'),
(20, 17, 2, '450'),
(21, 18, 2, '400'),
(22, 20, 4, '230'),
(23, 20, 3, '400');

CREATE TABLE IF NOT EXISTS `studenci` (
  `id_studenta` int(11) NOT NULL DEFAULT '0',
  `imie` varchar(100) COLLATE utf8_polish_ci DEFAULT NULL,
  `nazwisko` varchar(45) COLLATE utf8_polish_ci NOT NULL,
  `data_urodzenia` datetime DEFAULT NULL,
  `id_wydzialu` int(11) DEFAULT NULL,
  `id_kierunku` int(11) DEFAULT NULL,
  `rok_studiow` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_studenta`),
  KEY `id_wydzialu` (`id_wydzialu`),
  KEY `id_kierunku` (`id_kierunku`),
  KEY `ddd` (`imie`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

INSERT INTO `studenci` (`id_studenta`, `imie`, `nazwisko`, `data_urodzenia`, `id_wydzialu`, `id_kierunku`, `rok_studiow`) VALUES
(1, 'Piotr', 'Malinowski', '1965-01-01 00:00:00', 1, 2, 1),
(2, 'Marian', 'Goławski', '1975-10-05 00:00:00', 2, 5, 2),
(3, 'Tomasz', 'Gała', '1986-05-15 00:00:00', 4, 12, 1),
(4, 'Tomasz', 'Rutkowski', '1999-04-20 00:00:00', 3, 9, 1),
(5, 'Joanna', 'Nowak', '1989-03-10 00:00:00', 1, 4, 2),
(6, 'Grzegorz', 'Kowal', '1958-12-03 00:00:00', 2, 1, 1),
(7, 'Paweł', 'Hrabota', '1989-01-03 00:00:00', 3, 4, 3),
(8, 'Sebastian', 'Motyl', '1955-04-05 00:00:00', 2, 1, 2),
(9, 'Robert', 'Fliciak', '1998-06-23 00:00:00', 1, 3, 1),
(10, 'Damian', 'Gosiąrowski', '1978-07-15 00:00:00', 2, 7, 2),
(11, 'Anna', 'Wednak', '1976-09-12 00:00:00', 1, 8, 3),
(12, 'Sylwia', 'Rostek', '1986-12-24 00:00:00', 4, 6, 2),
(13, 'Edwin', 'Wasiluk', '1999-02-04 00:00:00', 3, 3, 3),
(14, 'Ewa', 'Bednarzak', '1968-06-24 00:00:00', 2, 10, 3),
(15, 'Anika', 'Wernowska', '1977-11-28 00:00:00', 3, 9, 1),
(16, 'Wiesław', 'Dobrzyński', '1956-08-03 00:00:00', 1, 1, 2),
(17, 'Jerzy', 'Goławski', '1997-04-21 00:00:00', 3, 11, 2),
(18, 'Bartosz', 'Modejuk', '1956-03-28 00:00:00', 2, 8, 1),
(19, 'Maria', 'Kostrzewska', '1985-02-21 00:00:00', 4, 5, 3),
(20, 'Monika', 'Dumała', '1995-10-10 00:00:00', 1, 6, 2),
(21, 'Anna', 'Bednarzak', '1967-01-10 00:00:00', 1, 1, 2);

CREATE TABLE IF NOT EXISTS `stypendia` (
  `id_stypendium` int(11) NOT NULL,
  `nazwa_stypendium` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `maksymalna_wysokosc` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  PRIMARY KEY (`id_stypendium`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

INSERT INTO `stypendia` (`id_stypendium`, `nazwa_stypendium`, `maksymalna_wysokosc`) VALUES
(1, 'socjalne', '400'),
(2, 'rektora', '700'),
(3, 'specjalne', '200'),
(4, 'zapomoga', '500');

CREATE TABLE IF NOT EXISTS `wydzialy` (
  `id_wydzialu` int(11) NOT NULL,
  `nazwa_wydzialu` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  PRIMARY KEY (`id_wydzialu`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

INSERT INTO `wydzialy` (`id_wydzialu`, `nazwa_wydzialu`) VALUES
(1, 'humanistyczny'),
(2, 'nauk ścisłych'),
(3, 'przyrodniczy'),
(4, 'nauk ekonomicznych i prawnych');


ALTER TABLE `przyznane_stypendia`
  ADD CONSTRAINT `przyznane_stypendia_ibfk_1` FOREIGN KEY (`id_stypendium`) REFERENCES `stypendia` (`id_stypendium`),
  ADD CONSTRAINT `przyznane_stypendia_ibfk_2` FOREIGN KEY (`id_studenta`) REFERENCES `studenci` (`id_studenta`);

ALTER TABLE `studenci`
  ADD CONSTRAINT `studenci_ibfk_2` FOREIGN KEY (`id_wydzialu`) REFERENCES `wydzialy` (`id_wydzialu`),
  ADD CONSTRAINT `studenci_ibfk_3` FOREIGN KEY (`id_kierunku`) REFERENCES `kierunki` (`id_kierunku`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;