SQL STUDYHELPER

-- phpMyAdmin SQL Dump
-- version 4.7.7
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: May 31, 2018 at 07:49 AM
-- Server version: 5.6.38
-- PHP Version: 7.2.1

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

--
-- Database: `studyHelper`
--

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

--
-- Table structure for table `Admin`
--

CREATE TABLE `Admin` (
  `id` int(11) NOT NULL,
  `email` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_swedish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

--
-- Dumping data for table `Admin`
--

INSERT INTO `Admin` (`id`, `email`, `password`) VALUES
(1, 'admin@studiehjalpen.com', 'admin');

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

--
-- Table structure for table `Availability`
--

CREATE TABLE `Availability` (
  `day` varchar(7) COLLATE utf8_swedish_ci NOT NULL,
  `coachId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

--
-- Dumping data for table `Availability`
--

INSERT INTO `Availability` (`day`, `coachId`) VALUES
('Fredag', 2),
('Måndag', 2),
('Onsdag', 2),
('Tisdag', 2),
('Torsdag', 2),
('Fredag', 3),
('Onsdag', 3),
('Tisdag', 3),
('Tisdag', 4);

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

--
-- Table structure for table `Booking`
--

CREATE TABLE `Booking` (
  `day` varchar(7) COLLATE utf8_swedish_ci NOT NULL,
  `subject` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `studentId` int(11) NOT NULL,
  `coachId` int(11) NOT NULL,
  `bookingId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

--
-- Dumping data for table `Booking`
--

INSERT INTO `Booking` (`day`, `subject`, `studentId`, `coachId`, `bookingId`) VALUES
('Måndag', 'Matematik', 10, 3, 6),
('Torsdag', 'Matematik', 10, 3, 8),
('Måndag', 'Objektorienterad programmering', 17, 4, 10),
('Torsdag', 'Objektorienterad programmering', 15, 4, 11),
('Onsdag', 'Objektorienterad programmering', 16, 4, 12);

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

--
-- Table structure for table `CoachSubjects`
--

CREATE TABLE `CoachSubjects` (
  `subjectName` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `coachId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

--
-- Dumping data for table `CoachSubjects`
--

INSERT INTO `CoachSubjects` (`subjectName`, `coachId`) VALUES
('Algoritmer och datastrukturer', 2),
('Matematik', 3),
('Objektorienterad programmering', 4);

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

--
-- Table structure for table `Days`
--

CREATE TABLE `Days` (
  `dayOrder` int(1) NOT NULL,
  `name` varchar(7) COLLATE utf8_swedish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

--
-- Dumping data for table `Days`
--

INSERT INTO `Days` (`dayOrder`, `name`) VALUES
(5, 'Fredag'),
(1, 'Måndag'),
(3, 'Onsdag'),
(2, 'Tisdag'),
(4, 'Torsdag');

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

--
-- Table structure for table `Student`
--

CREATE TABLE `Student` (
  `name` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `phoneNr` int(20) NOT NULL,
  `studentId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

--
-- Dumping data for table `Student`
--

INSERT INTO `Student` (`name`, `email`, `password`, `phoneNr`, `studentId`) VALUES
('Mathilda Carlsson', 'mathilda.carlsson@hotmail.com', 'hej', 735143323, 10),
('Karl Forlund', 'karl.forlund@hotmail.com', 'hej', 736472341, 15),
('Maja Gustavsson', 'maja.gustavsson@hotmail.com', 'hej', 736172988, 16),
('Kajsa Kavat', 'kajsa.kavat@hotmail.com', 'hej', 797263351, 17);

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

--
-- Table structure for table `StudyCoach`
--

CREATE TABLE `StudyCoach` (
  `name` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `description` varchar(255) COLLATE utf8_swedish_ci DEFAULT NULL,
  `coachId` int(11) NOT NULL,
  `phoneNr` int(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

--
-- Dumping data for table `StudyCoach`
--

INSERT INTO `StudyCoach` (`name`, `email`, `password`, `description`, `coachId`, `phoneNr`) VALUES
('Lovisa Jansson', 'lovisa.jansson@hotmail.com', 'hej', 'Tidigare lärarpraktikant. Läser för tillfället till ekonom. Flera års erfarenhet inom algoritmer och datastrukturer.', 2, 726453386),
('Anton Bäckström', 'anton.backstrom@hotmail.com', 'hej', 'Personliga förmågor: tålmodig, pedagogisk och noggrann. Läser för tillfället finansiell matematik. Erhållit stipendier tack vare sina höga betyg på gymnasiet.', 3, 737284471),
('Mattias Falk', 'mattias.falk@hotmail.com', 'hej', 'Mycket goda kunskaper inom objektorienterad programmering. Masterutbildning i nationalekonomi samt läst kurser inom strategisk ekonomistyrning och business development. Diverse anställningar inom bank och finans. ', 4, 738990062);

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

--
-- Table structure for table `Subjects`
--

CREATE TABLE `Subjects` (
  `name` varchar(255) COLLATE utf8_swedish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

--
-- Dumping data for table `Subjects`
--

INSERT INTO `Subjects` (`name`) VALUES
('Algoritmer och datastrukturer'),
('Matematik'),
('Objektorienterad programmering');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `Admin`
--
ALTER TABLE `Admin`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `Availability`
--
ALTER TABLE `Availability`
  ADD PRIMARY KEY (`day`,`coachId`),
  ADD KEY `coachId` (`coachId`),
  ADD KEY `day` (`day`);

--
-- Indexes for table `Booking`
--
ALTER TABLE `Booking`
  ADD PRIMARY KEY (`bookingId`),
  ADD KEY `day` (`day`),
  ADD KEY `subject` (`subject`),
  ADD KEY `studentId` (`studentId`),
  ADD KEY `coachId` (`coachId`);

--
-- Indexes for table `CoachSubjects`
--
ALTER TABLE `CoachSubjects`
  ADD PRIMARY KEY (`subjectName`,`coachId`),
  ADD KEY `subjectName` (`subjectName`),
  ADD KEY `coachId` (`coachId`);

--
-- Indexes for table `Days`
--
ALTER TABLE `Days`
  ADD PRIMARY KEY (`name`);

--
-- Indexes for table `Student`
--
ALTER TABLE `Student`
  ADD PRIMARY KEY (`studentId`);

--
-- Indexes for table `StudyCoach`
--
ALTER TABLE `StudyCoach`
  ADD PRIMARY KEY (`coachId`);

--
-- Indexes for table `Subjects`
--
ALTER TABLE `Subjects`
  ADD PRIMARY KEY (`name`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `Admin`
--
ALTER TABLE `Admin`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `Booking`
--
ALTER TABLE `Booking`
  MODIFY `bookingId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;

--
-- AUTO_INCREMENT for table `Student`
--
ALTER TABLE `Student`
  MODIFY `studentId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=139;

--
-- AUTO_INCREMENT for table `StudyCoach`
--
ALTER TABLE `StudyCoach`
  MODIFY `coachId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `Availability`
--
ALTER TABLE `Availability`
  ADD CONSTRAINT `availability_ibfk_1` FOREIGN KEY (`day`) REFERENCES `Days` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `availability_ibfk_2` FOREIGN KEY (`coachId`) REFERENCES `StudyCoach` (`coachId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `Booking`
--
ALTER TABLE `Booking`
  ADD CONSTRAINT `booking_ibfk_1` FOREIGN KEY (`day`) REFERENCES `Days` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `booking_ibfk_2` FOREIGN KEY (`subject`) REFERENCES `Subjects` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `booking_ibfk_3` FOREIGN KEY (`studentId`) REFERENCES `Student` (`studentId`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `booking_ibfk_4` FOREIGN KEY (`coachId`) REFERENCES `StudyCoach` (`coachId`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `CoachSubjects`
--
ALTER TABLE `CoachSubjects`
  ADD CONSTRAINT `coachsubjects_ibfk_1` FOREIGN KEY (`subjectName`) REFERENCES `Subjects` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `coachsubjects_ibfk_2` FOREIGN KEY (`coachId`) REFERENCES `StudyCoach` (`coachId`) ON DELETE CASCADE ON UPDATE CASCADE;