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

START TRANSACTION;



--
-- Database: `dellasmi_schedule`
--

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

--
-- Stand-in structure for view `allClassView`
-- (See below for the actual view)
--
CREATE TABLE `allClassView` (
`className` varchar(35)
,`classType` varchar(25)
,`location` varchar(25)
,`date` date
,`startTime` time
,`endTime` time
);

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

--
-- Table structure for table `classes`
--

CREATE TABLE `classes` (
  `ID` int(11) NOT NULL,
  `className` varchar(35) NOT NULL,
  `startTime` time NOT NULL,
  `endTime` time NOT NULL,
  `classType` varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classes`
--

INSERT INTO `classes` (`ID`, `className`, `startTime`, `endTime`, `classType`) VALUES
(1, 'ACAD 245: Product Design', '12:00:00', '13:50:00', 'Lecture'),
(2, 'BUAD 310: Applied Business Stats', '09:30:00', '10:40:00', 'Lecture'),
(3, 'BUAD 310 D: Applied Business Stats', '08:00:00', '09:30:00', 'Discussion'),
(4, 'ACAD 276: Dev II', '14:00:00', '15:50:00', 'Lecture'),
(5, 'IDSN 599: Special Topics', '14:00:00', '15:50:00', 'Lecture'),
(6, 'ACAD 174: Innovators Forum', '12:00:00', '13:50:00', 'Lecture');

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

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

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

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

INSERT INTO `locations` (`ID`, `location`) VALUES
(1, 'IYH 110'),
(2, 'IYH 210'),
(3, 'ONLINE'),
(4, 'JFFLL 105'),
(5, 'JFF 328'),
(6, 'JFFLL 125');

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

--
-- Table structure for table `meetings`
--

CREATE TABLE `meetings` (
  `ID` int(11) NOT NULL,
  `classID` int(11) NOT NULL,
  `date` date NOT NULL,
  `locationID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `meetings`
--

INSERT INTO `meetings` (`ID`, `classID`, `date`, `locationID`) VALUES
(2, 1, '2025-09-15', 1),
(3, 2, '2025-09-16', 4),
(4, 4, '2025-09-16', 2),
(5, 1, '2025-09-17', 1),
(6, 5, '2025-09-17', 3),
(7, 3, '2025-09-18', 5),
(8, 2, '2025-09-18', 4),
(9, 6, '2025-09-19', 6),
(10, 1, '2025-09-22', 1),
(11, 2, '2025-09-23', 4),
(12, 4, '2025-09-23', 2),
(13, 1, '2025-09-24', 1),
(14, 5, '2025-09-24', 3),
(15, 3, '2025-09-25', 5),
(16, 2, '2025-09-25', 4),
(17, 4, '2025-09-25', 2),
(18, 6, '2025-09-26', 6),
(19, 1, '2025-09-29', 1),
(20, 2, '2025-09-30', 4),
(21, 4, '2025-09-30', 2),
(22, 1, '2025-10-01', 2),
(23, 5, '2025-10-01', 3),
(24, 3, '2025-10-02', 5),
(25, 2, '2025-10-02', 4),
(26, 4, '2025-10-02', 2),
(27, 6, '2025-10-03', 6),
(28, 1, '2025-10-06', 1),
(29, 2, '2025-10-07', 4),
(30, 4, '2025-10-07', 2),
(31, 1, '2025-10-08', 1),
(32, 5, '2025-10-08', 3),
(33, 3, '2025-10-09', 5),
(34, 2, '2025-10-09', 4),
(35, 4, '2025-10-09', 2),
(36, 6, '2025-10-10', 6);

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

--
-- Stand-in structure for view `scheduleView2`
-- (See below for the actual view)
--
CREATE TABLE `scheduleView2` (
`classID` int(11)
,`locationID` int(11)
,`ID` int(11)
,`className` varchar(35)
,`classType` varchar(25)
,`location` varchar(25)
,`date` date
,`startTime` time
,`endTime` time
);

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

--
-- Structure for view `allClassView`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`dellasmi`@`localhost` SQL SECURITY DEFINER VIEW `allClassView`  AS SELECT `classes`.`className` AS `className`, `classes`.`classType` AS `classType`, `locations`.`location` AS `location`, `meetings`.`date` AS `date`, `classes`.`startTime` AS `startTime`, `classes`.`endTime` AS `endTime` FROM ((`classes` join `locations`) join `meetings`) WHERE ((`meetings`.`classID` = `classes`.`ID`) AND (`meetings`.`locationID` = `locations`.`ID`)) ;

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

--
-- Structure for view `scheduleView2`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`dellasmi`@`localhost` SQL SECURITY DEFINER VIEW `scheduleView2`  AS SELECT `meetings`.`classID` AS `classID`, `meetings`.`locationID` AS `locationID`, `meetings`.`ID` AS `ID`, `classes`.`className` AS `className`, `classes`.`classType` AS `classType`, `locations`.`location` AS `location`, `meetings`.`date` AS `date`, `classes`.`startTime` AS `startTime`, `classes`.`endTime` AS `endTime` FROM ((`classes` join `locations`) join `meetings`) WHERE ((`meetings`.`classID` = `classes`.`ID`) AND (`meetings`.`locationID` = `locations`.`ID`)) ;

--
-- Indexes for dumped tables
--

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

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

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

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `meetings`
--
ALTER TABLE `meetings`
  ADD CONSTRAINT `meetings_ibfk_1` FOREIGN KEY (`classID`) REFERENCES `classes` (`ID`),
  ADD CONSTRAINT `meetings_ibfk_2` FOREIGN KEY (`locationID`) REFERENCES `locations` (`ID`);
COMMIT;

