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

START TRANSACTION;



--
-- Database: `emiliedu_schedule`
--

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

--
-- Table structure for table `className`
--

CREATE TABLE `className` (
  `ID` int(11) NOT NULL,
  `className` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `className`
--

INSERT INTO `className` (`ID`, `className`) VALUES
(1, 'ACAD 324 g The practice of Design'),
(2, ' Psych 100 lg'),
(3, 'ACAD276 DEVII'),
(4, 'CTPR454  Acting for Film and Television'),
(5, 'Psyc-100lg lab'),
(6, ' CTPR409 Trojan Vision, Live Television Production');

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

--
-- Table structure for table `classroom`
--

CREATE TABLE `classroom` (
  `ID` int(11) NOT NULL,
  `roomName` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classroom`
--

INSERT INTO `classroom` (`ID`, `roomName`) VALUES
(1, 'IYH212/213'),
(2, 'THH101'),
(3, 'IYH210'),
(4, 'RZCSTGA'),
(5, 'GFS223'),
(6, 'RZCSTGC');

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

--
-- Table structure for table `classSchedule`
--

CREATE TABLE `classSchedule` (
  `ID` int(11) NOT NULL,
  `Date` date NOT NULL,
  `startTime` time NOT NULL,
  `endTime` time NOT NULL,
  `classID` int(11) NOT NULL,
  `roomID` int(11) NOT NULL,
  `classTypeID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classSchedule`
--

INSERT INTO `classSchedule` (`ID`, `Date`, `startTime`, `endTime`, `classID`, `roomID`, `classTypeID`) VALUES
(1, '2025-09-08', '10:00:00', '11:50:00', 1, 1, 1),
(2, '2025-09-10', '10:00:00', '11:50:00', 1, 1, 1),
(3, '2025-09-15', '10:00:00', '11:50:00', 1, 1, 1),
(4, '2025-09-17', '10:00:00', '11:50:00', 1, 1, 1),
(5, '2025-09-22', '10:00:00', '11:50:00', 1, 1, 1),
(6, '2025-09-24', '10:00:00', '11:50:00', 1, 1, 1),
(7, '2025-09-29', '10:00:00', '11:50:00', 1, 1, 1),
(8, '2025-10-01', '10:00:00', '11:50:00', 1, 1, 1),
(9, '2025-09-09', '09:30:00', '10:50:00', 2, 2, 1),
(10, '2025-09-11', '09:30:00', '10:50:00', 2, 2, 1),
(11, '2025-09-16', '09:30:00', '10:50:00', 2, 2, 1),
(12, '2025-09-18', '09:30:00', '10:50:00', 2, 2, 1),
(13, '2025-09-23', '09:30:00', '10:50:00', 2, 2, 1),
(14, '2025-09-25', '09:30:00', '10:50:00', 2, 2, 1),
(15, '2025-09-30', '09:30:00', '10:50:00', 2, 2, 1),
(16, '2025-10-02', '09:30:00', '10:50:00', 2, 2, 1),
(17, '2025-09-09', '14:00:00', '15:50:00', 3, 3, 1),
(18, '2025-09-11', '14:00:00', '15:50:00', 3, 3, 1),
(19, '2025-09-16', '14:00:00', '15:50:00', 3, 3, 1),
(20, '2025-09-18', '14:00:00', '15:50:00', 3, 3, 1),
(21, '2025-09-23', '14:00:00', '15:50:00', 3, 3, 1),
(22, '2025-09-25', '14:00:00', '15:50:00', 3, 3, 1),
(23, '2025-09-30', '14:00:00', '15:50:00', 3, 3, 1),
(24, '2025-10-02', '14:00:00', '15:50:00', 3, 3, 1),
(25, '2025-09-10', '13:00:00', '15:50:00', 4, 4, 1),
(26, '2025-09-17', '13:00:00', '15:50:00', 4, 4, 1),
(27, '2025-09-24', '13:00:00', '15:50:00', 4, 4, 1),
(28, '2025-10-01', '13:00:00', '15:50:00', 4, 4, 1),
(29, '2025-09-10', '16:00:00', '17:00:00', 5, 5, 2),
(30, '2025-09-17', '16:00:00', '17:00:00', 5, 5, 2),
(31, '2025-09-24', '16:00:00', '17:00:00', 5, 5, 2),
(32, '2025-10-01', '16:00:00', '17:00:00', 5, 5, 2),
(33, '2025-09-11', '16:30:00', '18:20:00', 6, 6, 1),
(34, '2025-09-18', '16:30:00', '18:20:00', 6, 6, 1),
(35, '2025-09-25', '16:30:00', '18:20:00', 6, 6, 1),
(36, '2025-10-02', '16:30:00', '18:20:00', 6, 6, 1);

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

--
-- Table structure for table `classType`
--

CREATE TABLE `classType` (
  `ID` int(11) NOT NULL,
  `classType` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classType`
--

INSERT INTO `classType` (`ID`, `classType`) VALUES
(1, 'Lecture'),
(2, 'Lab'),
(3, 'Lecture/Lab');

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

--
-- Stand-in structure for view `classView`
-- (See below for the actual view)
--
CREATE TABLE `classView` (
`ID` int(11)
,`Date` date
,`startTime` time
,`endTime` time
,`classID` int(11)
,`roomID` int(11)
,`classTypeID` int(11)
,`roomName` varchar(50)
,`classType` varchar(50)
,`className` varchar(50)
);

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

--
-- Stand-in structure for view `myClassSchedule`
-- (See below for the actual view)
--
CREATE TABLE `myClassSchedule` (
`Date` date
,`startTime` time
,`endTime` time
,`className` varchar(50)
,`roomName` varchar(50)
,`classType` varchar(50)
);

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

--
-- Structure for view `classView`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`emiliedu`@`localhost` SQL SECURITY DEFINER VIEW `classView`  AS SELECT `classSchedule`.`ID` AS `ID`, `classSchedule`.`Date` AS `Date`, `classSchedule`.`startTime` AS `startTime`, `classSchedule`.`endTime` AS `endTime`, `classSchedule`.`classID` AS `classID`, `classSchedule`.`roomID` AS `roomID`, `classSchedule`.`classTypeID` AS `classTypeID`, `classroom`.`roomName` AS `roomName`, `classType`.`classType` AS `classType`, `className`.`className` AS `className` FROM (((`classType` join `className`) join `classroom`) join `classSchedule`) WHERE ((`classType`.`ID` = `classSchedule`.`classTypeID`) AND (`classroom`.`ID` = `classSchedule`.`roomID`) AND (`className`.`ID` = `classSchedule`.`classID`)) ;

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

--
-- Structure for view `myClassSchedule`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`emiliedu`@`localhost` SQL SECURITY DEFINER VIEW `myClassSchedule`  AS SELECT `classSchedule`.`Date` AS `Date`, `classSchedule`.`startTime` AS `startTime`, `classSchedule`.`endTime` AS `endTime`, `className`.`className` AS `className`, `classroom`.`roomName` AS `roomName`, `classType`.`classType` AS `classType` FROM (((`classSchedule` join `classroom`) join `className`) join `classType`) WHERE ((`classSchedule`.`classID` = `className`.`ID`) AND (`classSchedule`.`roomID` = `classroom`.`ID`) AND (`classSchedule`.`classTypeID` = `classType`.`ID`)) ;

--
-- Indexes for dumped tables
--

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

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

--
-- Indexes for table `classSchedule`
--
ALTER TABLE `classSchedule`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `classID` (`classID`),
  ADD KEY `classTypeID` (`classTypeID`),
  ADD KEY `roomID` (`roomID`);

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

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `classSchedule`
--
ALTER TABLE `classSchedule`
  ADD CONSTRAINT `classSchedule_ibfk_1` FOREIGN KEY (`classID`) REFERENCES `className` (`ID`),
  ADD CONSTRAINT `classSchedule_ibfk_2` FOREIGN KEY (`classTypeID`) REFERENCES `classType` (`ID`),
  ADD CONSTRAINT `classSchedule_ibfk_3` FOREIGN KEY (`roomID`) REFERENCES `classroom` (`ID`);
COMMIT;

