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

START TRANSACTION;



--
-- Database: `plittle_schedule`
--

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

--
-- Table structure for table `classname`
--

CREATE TABLE `classname` (
  `classname_id` int(11) NOT NULL,
  `classnameName` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classname`
--

INSERT INTO `classname` (`classname_id`, `classnameName`) VALUES
(1, 'ACAD 324: The Practice of Design'),
(2, 'ACAD 276: Dev II'),
(3, 'PHIL 260: Ethical Theory and Practice'),
(4, 'MEDS 220: Healthcare Foundations'),
(5, 'ACAD 240: Materials');

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

--
-- Table structure for table `classschedule`
--

CREATE TABLE `classschedule` (
  `schedule_id` int(11) NOT NULL,
  `classname_id` int(45) NOT NULL,
  `classtype_id` int(11) NOT NULL,
  `roomnumber_id` int(11) NOT NULL,
  `starttime` time NOT NULL,
  `endtime` time NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classschedule`
--

INSERT INTO `classschedule` (`schedule_id`, `classname_id`, `classtype_id`, `roomnumber_id`, `starttime`, `endtime`, `date`) VALUES
(1, 1, 1, 1, '09:00:00', '11:50:00', '2023-09-11'),
(2, 2, 1, 2, '14:00:00', '16:50:00', '2023-09-12'),
(3, 3, 1, 3, '17:30:00', '18:50:00', '2023-09-11'),
(4, 3, 2, 4, '10:00:00', '10:50:00', '2023-09-12'),
(5, 4, 1, 5, '12:00:00', '13:50:00', '2023-09-11'),
(6, 5, 1, 1, '18:00:00', '19:50:00', '2023-09-12'),
(7, 1, 1, 1, '09:00:00', '11:50:00', '2023-09-13'),
(8, 2, 1, 2, '14:00:00', '16:50:00', '2023-09-14'),
(9, 3, 1, 3, '17:30:00', '18:50:00', '2023-09-13'),
(10, 5, 1, 1, '18:00:00', '19:50:00', '2023-09-14'),
(11, 1, 1, 1, '09:00:00', '11:50:00', '2023-09-18'),
(12, 2, 1, 2, '14:00:00', '16:50:00', '2023-09-19'),
(13, 3, 1, 3, '17:30:00', '18:50:00', '2023-09-18'),
(14, 3, 2, 4, '10:00:00', '10:50:00', '2023-09-19'),
(15, 4, 1, 5, '12:00:00', '13:50:00', '2023-09-18'),
(16, 5, 1, 1, '18:00:00', '19:50:00', '2023-09-19'),
(17, 1, 1, 1, '09:00:00', '11:50:00', '2023-09-20'),
(18, 2, 1, 2, '14:00:00', '16:50:00', '2023-09-21'),
(19, 3, 1, 3, '17:30:00', '18:50:00', '2023-09-20'),
(20, 5, 1, 1, '18:00:00', '19:50:00', '2023-09-21'),
(21, 1, 1, 1, '09:00:00', '11:50:00', '2023-09-25'),
(22, 2, 1, 2, '14:00:00', '16:50:00', '2023-09-26'),
(23, 3, 1, 3, '17:30:00', '18:50:00', '2023-09-25'),
(24, 3, 2, 4, '10:00:00', '10:50:00', '2023-09-26'),
(25, 4, 1, 5, '12:00:00', '13:50:00', '2023-09-25'),
(26, 5, 1, 1, '18:00:00', '19:50:00', '2023-09-26'),
(27, 1, 1, 1, '09:00:00', '11:50:00', '2023-09-27'),
(28, 2, 1, 2, '14:00:00', '16:50:00', '2023-09-28'),
(29, 3, 1, 3, '17:30:00', '18:50:00', '2023-09-27'),
(30, 5, 1, 1, '18:00:00', '19:50:00', '2023-09-28'),
(31, 1, 1, 1, '09:00:00', '11:50:00', '2023-10-02'),
(32, 2, 1, 2, '14:00:00', '16:50:00', '2023-10-03'),
(33, 3, 1, 3, '17:30:00', '18:50:00', '2023-10-02'),
(34, 3, 2, 4, '10:00:00', '10:50:00', '2023-10-03'),
(35, 4, 1, 5, '12:00:00', '13:50:00', '2023-10-02'),
(36, 5, 1, 1, '18:00:00', '19:50:00', '2023-10-03'),
(37, 1, 1, 1, '09:00:00', '11:50:00', '2023-10-04'),
(38, 2, 1, 2, '14:00:00', '16:50:00', '2023-10-05'),
(39, 3, 1, 3, '17:30:00', '18:50:00', '2023-10-04'),
(40, 5, 1, 1, '18:00:00', '19:50:00', '2023-10-05');

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

--
-- Table structure for table `classtype`
--

CREATE TABLE `classtype` (
  `classtype_id` int(11) NOT NULL,
  `classtypeName` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classtype`
--

INSERT INTO `classtype` (`classtype_id`, `classtypeName`) VALUES
(1, 'Lecture'),
(2, 'Discussion');

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

--
-- Table structure for table `roomnumber`
--

CREATE TABLE `roomnumber` (
  `roomnumber_id` int(11) NOT NULL,
  `roomnumberName` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `roomnumber`
--

INSERT INTO `roomnumber` (`roomnumber_id`, `roomnumberName`) VALUES
(1, 'IYH 212/213'),
(2, 'IYH 210/211'),
(3, 'THH 102'),
(4, 'WPH 205'),
(5, 'THH 301');

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

--
-- Stand-in structure for view `scheduleview1`
-- (See below for the actual view)
--
CREATE TABLE `scheduleview1` (
`classnameName` varchar(45)
,`roomnumberName` varchar(30)
,`classtypeName` varchar(30)
,`date` date
,`starttime` time
,`endtime` time
);

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

--
-- Structure for view `scheduleview1`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`plittle`@`localhost` SQL SECURITY DEFINER VIEW `scheduleview1`  AS SELECT `classname`.`classnameName` AS `classnameName`, `roomnumber`.`roomnumberName` AS `roomnumberName`, `classtype`.`classtypeName` AS `classtypeName`, `classschedule`.`date` AS `date`, `classschedule`.`starttime` AS `starttime`, `classschedule`.`endtime` AS `endtime` FROM (((`classschedule` join `classname`) join `classtype`) join `roomnumber`) WHERE ((`classschedule`.`classname_id` = `classname`.`classname_id`) AND (`classschedule`.`roomnumber_id` = `roomnumber`.`roomnumber_id`) AND (`classschedule`.`classtype_id` = `classtype`.`classtype_id`)) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `classname`
--
ALTER TABLE `classname`
  ADD PRIMARY KEY (`classname_id`);

--
-- Indexes for table `classschedule`
--
ALTER TABLE `classschedule`
  ADD PRIMARY KEY (`schedule_id`),
  ADD KEY `classname_id` (`classname_id`),
  ADD KEY `classtype_id` (`classtype_id`),
  ADD KEY `roomnumber_id` (`roomnumber_id`);

--
-- Indexes for table `classtype`
--
ALTER TABLE `classtype`
  ADD PRIMARY KEY (`classtype_id`);

--
-- Indexes for table `roomnumber`
--
ALTER TABLE `roomnumber`
  ADD PRIMARY KEY (`roomnumber_id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

--
-- AUTO_INCREMENT for table `classtype`
--
ALTER TABLE `classtype`
  MODIFY `classtype_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `classschedule`
--
ALTER TABLE `classschedule`
  ADD CONSTRAINT `classschedule_ibfk_1` FOREIGN KEY (`classname_id`) REFERENCES `classname` (`classname_id`),
  ADD CONSTRAINT `classschedule_ibfk_2` FOREIGN KEY (`classtype_id`) REFERENCES `classtype` (`classtype_id`),
  ADD CONSTRAINT `classschedule_ibfk_3` FOREIGN KEY (`roomnumber_id`) REFERENCES `roomnumber` (`roomnumber_id`);
COMMIT;

