-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `gjzhou_schedule`
--

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

--
-- Table structure for table `ClassTable`
--

CREATE TABLE `ClassTable` (
  `ClassID` int(11) NOT NULL,
  `Class` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ClassTable`
--

INSERT INTO `ClassTable` (`ClassID`, `Class`) VALUES
(1, 'ACAD 324'),
(2, 'ITP 116'),
(3, 'WRIT 340'),
(4, 'ACAD 276'),
(5, 'ACAD 240');

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

--
-- Table structure for table `LocationTable`
--

CREATE TABLE `LocationTable` (
  `LocationID` int(11) NOT NULL,
  `Location` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `LocationTable`
--

INSERT INTO `LocationTable` (`LocationID`, `Location`) VALUES
(1, 'IYA 212'),
(2, 'JFF 313'),
(3, 'THH 116'),
(4, 'IYA 210');

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

--
-- Table structure for table `MainSchedule`
--

CREATE TABLE `MainSchedule` (
  `ID` int(11) NOT NULL,
  `Start` time NOT NULL,
  `End` time NOT NULL,
  `Date` date NOT NULL,
  `Location` int(11) NOT NULL,
  `Type` int(11) NOT NULL,
  `ClassName` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `MainSchedule`
--

INSERT INTO `MainSchedule` (`ID`, `Start`, `End`, `Date`, `Location`, `Type`, `ClassName`) VALUES
(1, '09:00:00', '11:50:00', '2023-09-25', 1, 1, 1),
(2, '15:00:00', '15:50:00', '2023-09-25', 3, 1, 2),
(3, '11:00:00', '12:20:00', '2023-09-26', 2, 1, 3),
(4, '14:00:00', '16:50:00', '2023-09-26', 4, 1, 4),
(5, '18:00:00', '19:50:00', '2023-09-26', 1, 1, 5),
(6, '09:00:00', '11:50:00', '2023-09-27', 1, 1, 1),
(7, '15:00:00', '15:50:00', '2023-09-27', 3, 1, 2),
(8, '11:00:00', '12:20:00', '2023-09-28', 2, 1, 3),
(9, '14:00:00', '16:50:00', '2023-09-28', 4, 1, 4),
(10, '18:00:00', '19:50:00', '2023-09-28', 1, 1, 5),
(11, '09:00:00', '11:50:00', '2023-10-02', 1, 1, 1),
(12, '15:00:00', '15:50:00', '2023-10-02', 3, 1, 2),
(13, '11:00:00', '12:20:00', '2023-10-03', 2, 1, 3),
(14, '14:00:00', '16:50:00', '2023-10-03', 4, 1, 4),
(15, '18:00:00', '19:50:00', '2023-10-03', 1, 1, 5),
(16, '09:00:00', '11:50:00', '2023-10-04', 1, 1, 1),
(17, '15:00:00', '15:50:00', '2023-10-04', 3, 1, 2),
(18, '11:00:00', '12:20:00', '2023-10-05', 2, 1, 3),
(19, '14:00:00', '16:50:00', '2023-10-05', 4, 1, 4),
(20, '18:00:00', '19:50:00', '2023-10-05', 1, 1, 5),
(21, '09:00:00', '11:50:00', '2023-10-09', 1, 1, 1),
(22, '15:00:00', '15:50:00', '2023-10-09', 3, 1, 2),
(23, '11:00:00', '12:20:00', '2023-10-10', 2, 1, 3),
(24, '14:00:00', '16:50:00', '2023-10-10', 4, 1, 4),
(25, '18:00:00', '19:50:00', '2023-10-10', 1, 1, 5),
(26, '09:00:00', '11:50:00', '2023-10-11', 1, 1, 1),
(27, '09:00:00', '11:50:00', '2023-10-11', 1, 1, 1),
(28, '15:00:00', '15:50:00', '2023-10-11', 3, 1, 2),
(29, '11:00:00', '12:20:00', '2023-10-12', 2, 1, 3),
(30, '14:00:00', '16:50:00', '2023-10-12', 4, 1, 4),
(31, '18:00:00', '19:50:00', '2023-10-12', 1, 1, 5),
(32, '09:00:00', '11:50:00', '2023-10-16', 1, 1, 1),
(33, '15:00:00', '15:50:00', '2023-10-16', 3, 1, 2),
(34, '11:00:00', '12:20:00', '2023-10-17', 2, 1, 3),
(35, '14:00:00', '16:50:00', '2023-10-17', 4, 1, 4),
(36, '18:00:00', '19:50:00', '2023-10-17', 1, 1, 5),
(37, '09:00:00', '11:50:00', '2023-10-18', 1, 1, 1),
(38, '15:00:00', '15:50:00', '2023-10-18', 3, 1, 2),
(39, '11:00:00', '12:20:00', '2023-10-19', 2, 1, 3),
(40, '14:00:00', '16:50:00', '2023-10-19', 4, 1, 4),
(41, '18:00:00', '19:50:00', '2023-10-19', 1, 1, 5);

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

--
-- Stand-in structure for view `ScheduleView`
-- (See below for the actual view)
--
CREATE TABLE `ScheduleView` (
`ID` int(11)
,`Date` date
,`End` time
,`Start` time
,`Location` varchar(30)
,`LocationID` int(11)
,`Class` varchar(30)
,`ClassID` int(11)
,`Type` varchar(30)
,`TypeID` int(11)
);

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

--
-- Table structure for table `TypeTable`
--

CREATE TABLE `TypeTable` (
  `TypeID` int(11) NOT NULL,
  `Type` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `TypeTable`
--

INSERT INTO `TypeTable` (`TypeID`, `Type`) VALUES
(1, 'Lecture'),
(2, 'Lab');

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

--
-- Structure for view `ScheduleView`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`gjzhou`@`localhost` SQL SECURITY DEFINER VIEW `ScheduleView`  AS SELECT `MainSchedule`.`ID` AS `ID`, `MainSchedule`.`Date` AS `Date`, `MainSchedule`.`End` AS `End`, `MainSchedule`.`Start` AS `Start`, `LocationTable`.`Location` AS `Location`, `LocationTable`.`LocationID` AS `LocationID`, `ClassTable`.`Class` AS `Class`, `ClassTable`.`ClassID` AS `ClassID`, `TypeTable`.`Type` AS `Type`, `TypeTable`.`TypeID` AS `TypeID` FROM (((`MainSchedule` join `LocationTable`) join `ClassTable`) join `TypeTable`) WHERE ((`MainSchedule`.`Location` = `LocationTable`.`LocationID`) AND (`MainSchedule`.`ClassName` = `ClassTable`.`ClassID`) AND (`MainSchedule`.`Type` = `TypeTable`.`TypeID`)) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ClassTable`
--
ALTER TABLE `ClassTable`
  ADD PRIMARY KEY (`ClassID`);

--
-- Indexes for table `LocationTable`
--
ALTER TABLE `LocationTable`
  ADD PRIMARY KEY (`LocationID`);

--
-- Indexes for table `MainSchedule`
--
ALTER TABLE `MainSchedule`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `TypeTable`
--
ALTER TABLE `TypeTable`
  ADD PRIMARY KEY (`TypeID`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

--
-- AUTO_INCREMENT for table `MainSchedule`
--
ALTER TABLE `MainSchedule`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=42;

--
-- AUTO_INCREMENT for table `TypeTable`
--
ALTER TABLE `TypeTable`
  MODIFY `TypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

