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

START TRANSACTION;



--
-- Database: `altamush_schedule`
--

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

--
-- Table structure for table `classLocations`
--

CREATE TABLE `classLocations` (
  `classLocation_id` int(11) NOT NULL,
  `classLocation` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classLocations`
--

INSERT INTO `classLocations` (`classLocation_id`, `classLocation`) VALUES
(1, 'JFF 233'),
(2, 'DMC 157'),
(3, 'IYH 213'),
(4, 'IYH 210'),
(5, 'KDC 236');

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

--
-- Table structure for table `classNames`
--

CREATE TABLE `classNames` (
  `className_id` int(11) NOT NULL,
  `className` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `classNames`
--

INSERT INTO `classNames` (`className_id`, `className`) VALUES
(1, 'BUAD-280: Introduction to Financial Accounting'),
(2, 'ITP-165: Introduction to C++ Programming'),
(3, 'ACAD-324g: The Practice of Design: Ideation to Innovation'),
(4, 'ACAD-276: Dev 2'),
(5, 'ITP-310: Design for User Experience');

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

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

CREATE TABLE `classSchedule` (
  `class_id` int(11) NOT NULL,
  `classDate` date NOT NULL,
  `classStartTime` time NOT NULL,
  `classEndTime` time NOT NULL,
  `className_id` int(11) NOT NULL,
  `classLocation_id` int(11) NOT NULL,
  `classType_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `classSchedule` (`class_id`, `classDate`, `classStartTime`, `classEndTime`, `className_id`, `classLocation_id`, `classType_id`) VALUES
(1, '2024-09-18', '08:00:00', '09:20:00', 1, 1, 1),
(2, '2024-09-18', '12:00:00', '12:50:00', 2, 2, 2),
(3, '2024-09-18', '17:00:00', '21:00:00', 5, 5, 1),
(4, '2024-09-19', '09:00:00', '11:50:00', 3, 3, 2),
(5, '2024-09-24', '14:00:00', '17:00:00', 4, 4, 2),
(6, '2024-09-23', '08:00:00', '09:20:00', 1, 1, 1),
(7, '2024-09-23', '12:00:00', '12:50:00', 2, 2, 2),
(8, '2024-09-24', '09:00:00', '11:50:00', 3, 3, 2),
(9, '2024-09-24', '14:00:00', '17:00:00', 4, 4, 2),
(10, '2024-09-25', '08:00:00', '09:20:00', 1, 1, 1),
(11, '2024-09-25', '12:00:00', '12:50:00', 2, 2, 2),
(12, '2024-09-25', '17:00:00', '21:00:00', 5, 5, 1),
(13, '2024-09-26', '09:00:00', '11:50:00', 3, 3, 2),
(14, '2024-09-26', '14:00:00', '17:00:00', 4, 4, 2),
(15, '2024-09-30', '08:00:00', '09:20:00', 1, 1, 1),
(16, '2024-09-30', '12:00:00', '12:50:00', 2, 2, 2),
(17, '2024-10-01', '09:00:00', '11:50:00', 3, 3, 2),
(18, '2024-10-01', '14:00:00', '17:00:00', 4, 4, 2),
(19, '2024-10-02', '08:00:00', '09:20:00', 1, 1, 1),
(20, '2024-10-02', '12:00:00', '12:50:00', 2, 2, 2),
(21, '2024-10-02', '17:00:00', '21:00:00', 5, 5, 1),
(22, '2024-10-03', '09:00:00', '11:50:00', 3, 3, 2),
(23, '2024-10-03', '14:00:00', '17:00:00', 4, 4, 2),
(24, '2024-10-07', '08:00:00', '09:20:00', 1, 1, 1),
(25, '2024-10-07', '12:00:00', '12:50:00', 2, 2, 2),
(26, '2024-10-08', '09:00:00', '11:50:00', 3, 3, 2),
(27, '2024-10-08', '14:00:00', '17:00:00', 4, 4, 2),
(28, '2024-10-09', '08:00:00', '09:20:00', 1, 1, 1),
(29, '2024-10-09', '12:00:00', '12:50:00', 2, 2, 2),
(30, '2024-10-09', '17:00:00', '21:00:00', 5, 5, 1),
(31, '2024-10-10', '09:00:00', '11:50:00', 3, 3, 2),
(32, '2024-10-10', '14:00:00', '17:00:00', 4, 4, 2),
(33, '2024-10-14', '08:00:00', '09:20:00', 1, 1, 1),
(34, '2024-10-14', '12:00:00', '12:50:00', 2, 2, 2),
(35, '2024-10-15', '09:00:00', '11:50:00', 3, 3, 2),
(36, '2024-10-15', '14:00:00', '17:00:00', 4, 4, 2),
(37, '2024-10-16', '08:00:00', '09:20:00', 1, 1, 1),
(38, '2024-10-16', '12:00:00', '12:50:00', 2, 2, 2),
(39, '2024-10-16', '17:00:00', '21:00:00', 5, 5, 1),
(40, '2024-10-17', '09:00:00', '11:50:00', 3, 3, 2),
(41, '2024-10-17', '14:00:00', '17:00:00', 4, 4, 2);

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

--
-- Table structure for table `classTypes`
--

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

--
-- Dumping data for table `classTypes`
--

INSERT INTO `classTypes` (`classType_id`, `classType`) VALUES
(1, 'Lecture'),
(2, 'Lecture/Lab');

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

--
-- Stand-in structure for view `mainView`
-- (See below for the actual view)
--
CREATE TABLE `mainView` (
`class_id` int(11)
,`classDate` date
,`classStartTime` time
,`classEndTime` time
,`className` varchar(100)
,`classLocation` varchar(11)
,`classType` varchar(50)
);

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

--
-- Structure for view `mainView`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`altamush`@`localhost` SQL SECURITY DEFINER VIEW `mainView`  AS SELECT `classSchedule`.`class_id` AS `class_id`, `classSchedule`.`classDate` AS `classDate`, `classSchedule`.`classStartTime` AS `classStartTime`, `classSchedule`.`classEndTime` AS `classEndTime`, `classNames`.`className` AS `className`, `classLocations`.`classLocation` AS `classLocation`, `classTypes`.`classType` AS `classType` FROM (((`classSchedule` join `classNames` on((`classSchedule`.`className_id` = `classNames`.`className_id`))) join `classLocations` on((`classSchedule`.`classLocation_id` = `classLocations`.`classLocation_id`))) join `classTypes` on((`classSchedule`.`classType_id` = `classTypes`.`classType_id`))) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `classLocations`
--
ALTER TABLE `classLocations`
  ADD PRIMARY KEY (`classLocation_id`);

--
-- Indexes for table `classNames`
--
ALTER TABLE `classNames`
  ADD PRIMARY KEY (`className_id`);

--
-- Indexes for table `classSchedule`
--
ALTER TABLE `classSchedule`
  ADD PRIMARY KEY (`class_id`),
  ADD KEY `className_id` (`className_id`),
  ADD KEY `classLocation_id` (`classLocation_id`),
  ADD KEY `classType_id` (`classType_id`);

--
-- Indexes for table `classTypes`
--
ALTER TABLE `classTypes`
  ADD PRIMARY KEY (`classType_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `classNames`
--
ALTER TABLE `classNames`
  MODIFY `className_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `classSchedule`
--
ALTER TABLE `classSchedule`
  ADD CONSTRAINT `classSchedule_ibfk_1` FOREIGN KEY (`classType_id`) REFERENCES `classTypes` (`classType_id`),
  ADD CONSTRAINT `classSchedule_ibfk_2` FOREIGN KEY (`classLocation_id`) REFERENCES `classLocations` (`classLocation_id`),
  ADD CONSTRAINT `classSchedule_ibfk_3` FOREIGN KEY (`className_id`) REFERENCES `classNames` (`className_id`);
COMMIT;

