-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Sep 19, 2024 at 03:04 PM
-- Server version: 5.7.44
-- PHP Version: 8.1.29

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
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 utf8mb4 */;

--
-- Database: `nepo_schedule`
--

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

--
-- Stand-in structure for view `allSchedules`
-- (See below for the actual view)
--
CREATE TABLE `allSchedules` (
`date` date
,`start` time
,`end` time
,`class` varchar(50)
,`location` varchar(50)
,`type` varchar(50)
);

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

--
-- Table structure for table `Classes`
--

CREATE TABLE `Classes` (
  `classID` int(11) NOT NULL,
  `class` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Classes`
--

INSERT INTO `Classes` (`classID`, `class`) VALUES
(1, 'devII'),
(2, 'products'),
(3, 'PoD'),
(4, 'makeSmartDevices'),
(5, 'hipHopCulture');

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

--
-- Table structure for table `locations`
--

CREATE TABLE `locations` (
  `locationID` int(11) NOT NULL,
  `location` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `locations`
--

INSERT INTO `locations` (`locationID`, `location`) VALUES
(1, 'THH202'),
(2, 'IYH210'),
(3, 'IYH213'),
(4, 'ONLINE'),
(5, 'IYH102');

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

--
-- Table structure for table `schedules`
--

CREATE TABLE `schedules` (
  `scheduleID` int(11) NOT NULL,
  `date` date NOT NULL,
  `start` time NOT NULL,
  `end` time NOT NULL,
  `classID` int(11) NOT NULL,
  `locationID` int(11) NOT NULL,
  `typeID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `schedules`
--

INSERT INTO `schedules` (`scheduleID`, `date`, `start`, `end`, `classID`, `locationID`, `typeID`) VALUES
(1, '2024-09-10', '09:00:00', '12:00:00', 3, 3, 3),
(2, '2024-09-10', '14:00:00', '17:00:00', 1, 2, 3),
(3, '2024-09-10', '18:00:00', '20:00:00', 5, 1, 2),
(4, '2024-09-11', '09:00:00', '12:00:00', 2, 5, 3),
(5, '2024-09-11', '12:00:00', '14:00:00', 4, 4, 4),
(6, '2024-09-12', '09:00:00', '12:00:00', 3, 3, 3),
(7, '2024-09-12', '12:00:00', '17:00:00', 1, 2, 3),
(8, '2024-09-12', '18:00:00', '20:00:00', 5, 1, 2),
(9, '2024-09-16', '09:00:00', '12:00:00', 2, 5, 3),
(10, '2024-09-16', '12:00:00', '14:00:00', 4, 4, 2),
(11, '2024-09-17', '09:00:00', '12:00:00', 3, 3, 3),
(12, '2024-09-17', '14:00:00', '17:00:00', 1, 2, 3),
(13, '2024-09-17', '18:00:00', '20:00:00', 5, 1, 2),
(14, '2024-09-18', '09:00:00', '12:00:00', 2, 5, 3),
(15, '2024-09-18', '12:00:00', '14:00:00', 4, 4, 2),
(16, '2024-09-19', '09:00:00', '12:00:00', 3, 3, 3),
(17, '2024-09-19', '14:00:00', '17:00:00', 1, 2, 3),
(18, '2024-09-19', '18:00:00', '20:00:00', 5, 1, 2),
(19, '2024-09-23', '09:00:00', '12:00:00', 2, 5, 3),
(20, '2024-09-23', '12:00:00', '14:00:00', 4, 4, 2),
(21, '2024-09-24', '09:00:00', '12:00:00', 3, 3, 3),
(22, '2024-09-24', '14:00:00', '17:00:00', 1, 2, 3),
(23, '2024-09-24', '18:00:00', '20:00:00', 5, 1, 2),
(24, '2024-09-25', '09:00:00', '12:00:00', 2, 5, 3),
(25, '2024-09-25', '12:00:00', '14:00:00', 4, 4, 2),
(26, '2024-09-26', '09:00:00', '12:00:00', 3, 3, 3),
(27, '2024-09-26', '14:00:00', '17:00:00', 1, 2, 3),
(28, '2024-09-26', '18:00:00', '20:00:00', 5, 1, 2),
(29, '2024-09-30', '09:00:00', '12:00:00', 2, 5, 3),
(30, '2024-09-30', '12:00:00', '14:00:00', 4, 4, 2),
(31, '2024-10-01', '09:00:00', '12:00:00', 3, 3, 3),
(32, '2024-10-01', '14:00:00', '17:00:00', 1, 2, 3),
(33, '2024-10-01', '18:00:00', '20:00:00', 5, 1, 2),
(34, '2024-10-02', '09:00:00', '12:00:00', 2, 5, 3),
(35, '2024-10-02', '12:00:00', '14:00:00', 4, 4, 2),
(36, '2024-10-03', '09:00:00', '12:00:00', 3, 3, 3),
(37, '2024-10-03', '14:00:00', '17:00:00', 1, 2, 3),
(38, '2024-10-03', '18:00:00', '20:00:00', 5, 1, 22),
(39, '2024-10-07', '09:00:00', '12:00:00', 2, 5, 3),
(40, '2024-10-07', '12:00:00', '14:00:00', 4, 4, 2);

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

--
-- Stand-in structure for view `schedulesAll`
-- (See below for the actual view)
--
CREATE TABLE `schedulesAll` (
`date` date
,`start` time
,`end` time
,`class` varchar(50)
,`location` varchar(50)
,`type` varchar(50)
);

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

--
-- Table structure for table `types`
--

CREATE TABLE `types` (
  `typeID` int(11) NOT NULL,
  `type` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `types`
--

INSERT INTO `types` (`typeID`, `type`) VALUES
(1, 'lab'),
(2, 'lecture'),
(3, 'lecture/lab');

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

--
-- Structure for view `allSchedules`
--
DROP TABLE IF EXISTS `allSchedules`;

CREATE ALGORITHM=UNDEFINED DEFINER=`nepo`@`localhost` SQL SECURITY DEFINER VIEW `allSchedules`  AS SELECT `schedules`.`date` AS `date`, `schedules`.`start` AS `start`, `schedules`.`end` AS `end`, `Classes`.`class` AS `class`, `locations`.`location` AS `location`, `types`.`type` AS `type` FROM (((`Classes` join `schedules`) join `locations`) join `types`) WHERE ((`Classes`.`classID` = `schedules`.`classID`) AND (`locations`.`locationID` = `schedules`.`locationID`) AND (`types`.`typeID` = `schedules`.`typeID`)) ;

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

--
-- Structure for view `schedulesAll`
--
DROP TABLE IF EXISTS `schedulesAll`;

CREATE ALGORITHM=UNDEFINED DEFINER=`nepo`@`localhost` SQL SECURITY DEFINER VIEW `schedulesAll`  AS SELECT `schedules`.`date` AS `date`, `schedules`.`start` AS `start`, `schedules`.`end` AS `end`, `Classes`.`class` AS `class`, `locations`.`location` AS `location`, `types`.`type` AS `type` FROM (((`Classes` join `schedules`) join `locations`) join `types`) WHERE ((`Classes`.`classID` = `schedules`.`classID`) AND (`locations`.`locationID` = `schedules`.`locationID`) AND (`types`.`typeID` = `schedules`.`typeID`)) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `Classes`
--
ALTER TABLE `Classes`
  ADD PRIMARY KEY (`classID`);

--
-- Indexes for table `locations`
--
ALTER TABLE `locations`
  ADD PRIMARY KEY (`locationID`);

--
-- Indexes for table `schedules`
--
ALTER TABLE `schedules`
  ADD PRIMARY KEY (`scheduleID`),
  ADD KEY `classID` (`classID`),
  ADD KEY `locationID` (`locationID`);

--
-- Indexes for table `types`
--
ALTER TABLE `types`
  ADD PRIMARY KEY (`typeID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `Classes`
--
ALTER TABLE `Classes`
  MODIFY `classID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `locations`
--
ALTER TABLE `locations`
  MODIFY `locationID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `schedules`
--
ALTER TABLE `schedules`
  MODIFY `scheduleID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=41;

--
-- AUTO_INCREMENT for table `types`
--
ALTER TABLE `types`
  MODIFY `typeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `schedules`
--
ALTER TABLE `schedules`
  ADD CONSTRAINT `schedules_ibfk_1` FOREIGN KEY (`classID`) REFERENCES `Classes` (`classID`),
  ADD CONSTRAINT `schedules_ibfk_2` FOREIGN KEY (`locationID`) REFERENCES `locations` (`locationID`);
COMMIT;

/*!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 */;