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

START TRANSACTION;



--
-- Database: `bpickfor_schedule`
--

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

--
-- Stand-in structure for view `AllCourses`
-- (See below for the actual view)
--
CREATE TABLE `AllCourses` (
`Day` date
,`StartTime` time
,`EndTime` time
,`Room` varchar(10)
,`ClassType` varchar(25)
,`CourseNum` varchar(10)
,`CourseName` varchar(60)
,`Professor` varchar(25)
);

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

--
-- Stand-in structure for view `All_Week1`
-- (See below for the actual view)
--
CREATE TABLE `All_Week1` (
`Day` date
,`StartTime` time
,`EndTime` time
,`Room` varchar(10)
,`ClassType` varchar(25)
,`CourseNum` varchar(10)
,`CourseName` varchar(60)
,`Professor` varchar(25)
);

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

--
-- Table structure for table `ClassTypes`
--

CREATE TABLE `ClassTypes` (
  `ClassType_ID` int(11) NOT NULL,
  `ClassType` varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ClassTypes`
--

INSERT INTO `ClassTypes` (`ClassType_ID`, `ClassType`) VALUES
(1, 'Lecture'),
(2, 'Lecture/Lab'),
(3, 'Discussion');

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

--
-- Table structure for table `Courses`
--

CREATE TABLE `Courses` (
  `CourseNum_ID` int(11) NOT NULL,
  `CourseNum` varchar(10) NOT NULL,
  `CourseName` varchar(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Courses`
--

INSERT INTO `Courses` (`CourseNum_ID`, `CourseNum`, `CourseName`) VALUES
(1, 'ACAD-181', 'Disruptive Innovation'),
(2, 'SPAN-290', 'Introduction of Latin American and Iberian Studies'),
(3, 'ACAD-174', 'Innovators Forum'),
(4, 'PHED-102A', 'Weight Lifting'),
(5, 'ACAD-276', 'Dev II'),
(6, 'ACAD-240', 'Materials: Denim to Nanotubes');

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

--
-- Table structure for table `Main`
--

CREATE TABLE `Main` (
  `UniqueMeet_ID` int(11) NOT NULL,
  `CourseNum_ID` int(11) NOT NULL,
  `Day` date NOT NULL,
  `Room_ID` int(11) NOT NULL,
  `ClassType_ID` int(11) NOT NULL,
  `Professor_ID` int(11) NOT NULL,
  `StartTime` time NOT NULL,
  `EndTime` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Main`
--

INSERT INTO `Main` (`UniqueMeet_ID`, `CourseNum_ID`, `Day`, `Room_ID`, `ClassType_ID`, `Professor_ID`, `StartTime`, `EndTime`) VALUES
(1, 1, '2023-09-04', 1, 1, 1, '09:00:00', '10:50:00'),
(2, 1, '2023-09-11', 1, 1, 1, '09:00:00', '10:50:00'),
(3, 1, '2023-09-18', 1, 1, 1, '09:00:00', '10:50:00'),
(4, 1, '2023-09-25', 1, 1, 1, '09:00:00', '10:50:00'),
(5, 2, '2023-09-04', 2, 1, 2, '12:00:00', '13:50:00'),
(6, 2, '2023-09-11', 2, 1, 2, '12:00:00', '13:50:00'),
(7, 2, '2023-09-18', 2, 1, 2, '12:00:00', '13:50:00'),
(8, 2, '2023-09-25', 2, 1, 2, '12:00:00', '13:50:00'),
(9, 3, '2023-09-04', 1, 1, 3, '17:00:00', '19:50:00'),
(10, 3, '2023-09-11', 1, 1, 3, '17:00:00', '19:50:00'),
(11, 3, '2023-09-18', 1, 1, 3, '17:00:00', '19:50:00'),
(12, 3, '2023-09-25', 1, 1, 3, '17:00:00', '19:50:00'),
(13, 4, '2023-09-05', 3, 1, 1, '13:00:00', '13:50:00'),
(14, 4, '2023-09-12', 3, 1, 4, '13:00:00', '13:50:00'),
(15, 4, '2023-09-19', 3, 1, 4, '13:00:00', '13:50:00'),
(16, 4, '2023-09-26', 3, 1, 4, '13:00:00', '13:50:00'),
(17, 5, '2023-09-05', 1, 2, 5, '14:00:00', '16:50:00'),
(18, 5, '2023-09-12', 1, 2, 5, '14:00:00', '16:50:00'),
(19, 5, '2023-09-19', 1, 2, 5, '14:00:00', '16:50:00'),
(20, 5, '2023-09-26', 1, 2, 5, '14:00:00', '16:50:00'),
(21, 6, '2023-09-05', 4, 1, 6, '18:00:00', '19:50:00'),
(22, 6, '2023-09-12', 4, 1, 6, '18:00:00', '19:50:00'),
(23, 6, '2023-09-19', 4, 1, 6, '18:00:00', '19:50:00'),
(24, 6, '2023-09-26', 4, 1, 6, '18:00:00', '19:50:00'),
(25, 1, '2023-09-06', 1, 1, 1, '09:00:00', '10:50:00'),
(26, 1, '2023-09-13', 1, 1, 1, '09:00:00', '10:50:00'),
(27, 1, '2023-09-20', 1, 1, 1, '09:00:00', '10:50:00'),
(28, 1, '2023-09-27', 1, 1, 1, '09:00:00', '10:50:00'),
(29, 2, '2023-09-06', 2, 1, 2, '12:00:00', '13:50:00'),
(30, 2, '2023-09-13', 2, 1, 2, '12:00:00', '13:50:00'),
(31, 2, '2023-09-20', 2, 1, 2, '12:00:00', '13:50:00'),
(32, 2, '2023-09-27', 2, 1, 2, '12:00:00', '13:50:00'),
(33, 4, '2023-09-07', 3, 1, 4, '13:00:00', '13:50:00'),
(34, 4, '2023-09-14', 3, 1, 4, '13:00:00', '13:50:00'),
(35, 4, '2023-09-21', 3, 1, 4, '13:00:00', '13:50:00'),
(36, 4, '2023-09-28', 3, 1, 4, '13:00:00', '13:50:00'),
(37, 5, '2023-09-07', 1, 2, 5, '14:00:00', '16:50:00'),
(38, 5, '2023-09-14', 1, 2, 5, '14:00:00', '16:50:00'),
(39, 5, '2023-09-21', 1, 2, 5, '14:00:00', '16:50:00'),
(40, 5, '2023-09-28', 1, 2, 5, '14:00:00', '16:50:00'),
(41, 6, '2023-09-07', 4, 1, 6, '18:00:00', '19:50:00'),
(42, 6, '2023-09-14', 4, 1, 6, '18:00:00', '19:50:00'),
(43, 6, '2023-09-21', 4, 1, 6, '18:00:00', '19:50:00'),
(44, 6, '2023-09-28', 4, 1, 6, '18:00:00', '19:50:00'),
(45, 2, '2023-09-08', 5, 3, 2, '10:00:00', '10:50:00'),
(46, 2, '2023-09-15', 5, 3, 2, '10:00:00', '10:50:00'),
(47, 2, '2023-09-22', 5, 3, 2, '10:00:00', '10:50:00'),
(48, 2, '2023-09-29', 5, 3, 2, '10:00:00', '10:50:00');

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

--
-- Table structure for table `Professors`
--

CREATE TABLE `Professors` (
  `Professor_ID` int(11) NOT NULL,
  `Professor` varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Professors`
--

INSERT INTO `Professors` (`Professor_ID`, `Professor`) VALUES
(1, 'Chris Swain'),
(2, 'Ronald Mendoza De Jesus'),
(3, 'Doug Thomas'),
(4, 'Jino Andrew De Castro'),
(5, 'Patrick Dent'),
(6, 'Adam Hughes');

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

--
-- Table structure for table `Rooms`
--

CREATE TABLE `Rooms` (
  `Room_ID` int(11) NOT NULL,
  `Room` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Rooms`
--

INSERT INTO `Rooms` (`Room_ID`, `Room`) VALUES
(1, 'IYH210/211'),
(2, 'SGM101'),
(3, 'PED210'),
(4, 'IYH212/213'),
(5, 'WPH203');

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

--
-- Structure for view `AllCourses`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bpickfor`@`localhost` SQL SECURITY DEFINER VIEW `AllCourses`  AS SELECT `Main`.`Day` AS `Day`, `Main`.`StartTime` AS `StartTime`, `Main`.`EndTime` AS `EndTime`, `Rooms`.`Room` AS `Room`, `ClassTypes`.`ClassType` AS `ClassType`, `Courses`.`CourseNum` AS `CourseNum`, `Courses`.`CourseName` AS `CourseName`, `Professors`.`Professor` AS `Professor` FROM ((((`Main` join `Rooms`) join `ClassTypes`) join `Courses`) join `Professors`) WHERE ((`Main`.`Room_ID` = `Rooms`.`Room_ID`) AND (`Main`.`ClassType_ID` = `ClassTypes`.`ClassType_ID`) AND (`Main`.`CourseNum_ID` = `Courses`.`CourseNum_ID`) AND (`Main`.`Professor_ID` = `Professors`.`Professor_ID`)) ;

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

--
-- Structure for view `All_Week1`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`bpickfor`@`localhost` SQL SECURITY DEFINER VIEW `All_Week1`  AS SELECT `AllCourses`.`Day` AS `Day`, `AllCourses`.`StartTime` AS `StartTime`, `AllCourses`.`EndTime` AS `EndTime`, `AllCourses`.`Room` AS `Room`, `AllCourses`.`ClassType` AS `ClassType`, `AllCourses`.`CourseNum` AS `CourseNum`, `AllCourses`.`CourseName` AS `CourseName`, `AllCourses`.`Professor` AS `Professor` FROM `AllCourses` WHERE ((`AllCourses`.`Day` > '2023-09-03') AND (`AllCourses`.`Day` < '2023-09-09')) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ClassTypes`
--
ALTER TABLE `ClassTypes`
  ADD PRIMARY KEY (`ClassType_ID`);

--
-- Indexes for table `Courses`
--
ALTER TABLE `Courses`
  ADD PRIMARY KEY (`CourseNum_ID`);

--
-- Indexes for table `Main`
--
ALTER TABLE `Main`
  ADD PRIMARY KEY (`UniqueMeet_ID`),
  ADD KEY `CourseNum_ID` (`CourseNum_ID`),
  ADD KEY `Room_ID` (`Room_ID`),
  ADD KEY `ClassType_ID` (`ClassType_ID`),
  ADD KEY `Professor_ID` (`Professor_ID`);

--
-- Indexes for table `Professors`
--
ALTER TABLE `Professors`
  ADD PRIMARY KEY (`Professor_ID`);

--
-- Indexes for table `Rooms`
--
ALTER TABLE `Rooms`
  ADD PRIMARY KEY (`Room_ID`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

--
-- AUTO_INCREMENT for table `Main`
--
ALTER TABLE `Main`
  MODIFY `UniqueMeet_ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=49;

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `Main`
--
ALTER TABLE `Main`
  ADD CONSTRAINT `Main_ibfk_1` FOREIGN KEY (`ClassType_ID`) REFERENCES `ClassTypes` (`ClassType_ID`),
  ADD CONSTRAINT `Main_ibfk_2` FOREIGN KEY (`CourseNum_ID`) REFERENCES `Courses` (`CourseNum_ID`),
  ADD CONSTRAINT `Main_ibfk_3` FOREIGN KEY (`Professor_ID`) REFERENCES `Professors` (`Professor_ID`),
  ADD CONSTRAINT `Main_ibfk_4` FOREIGN KEY (`Room_ID`) REFERENCES `Rooms` (`Room_ID`);
COMMIT;

